Java Code Examples for org.apache.poi.ss.usermodel.Sheet#getLastRowNum()

The following examples show how to use org.apache.poi.ss.usermodel.Sheet#getLastRowNum() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: AddCellService.java    From cs-actions with Apache License 2.0 8 votes vote down vote up
/**
 * Inserts rows at the specified indexes in the worksheet
 *
 * @param worksheet    Worksheet where rows will be inserted
 * @param rowIndexList List of row indexes where rows will be inserted
 */
public static void shiftRows(final Sheet worksheet, final List<Integer> rowIndexList) {
    int insertPoint;
    int nRows;
    int i = 0;
    while (i < rowIndexList.size()) {
        insertPoint = rowIndexList.get(i);
        nRows = 1;
        while (i < rowIndexList.size() - 1 && (insertPoint + nRows == rowIndexList.get(i + 1))) {
            nRows++;
            i++;
        }
        if (insertPoint > worksheet.getLastRowNum()) {
            for (int j = insertPoint; j < insertPoint + nRows; j++) {
                worksheet.createRow(j);
            }
        } else {
            worksheet.shiftRows(insertPoint, worksheet.getLastRowNum(), nRows, false, true);
        }
        i++;
    }
}
 
Example 2
Source File: NodeProcessServiceImpl.java    From cymbal with Apache License 2.0 6 votes vote down vote up
private List<Node> getNodesFromExcelFile(final String excelFilePath) {
    List<Node> nodes = new ArrayList<>();
    try (Workbook workbook = new XSSFWorkbook(new FileInputStream(excelFilePath))) {
        Sheet sheet = workbook.getSheetAt(0);

        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row currentRow = sheet.getRow(rowNum);
            if (Objects.isNull(currentRow.getCell(0)) || Strings
                    .isNullOrEmpty(currentRow.getCell(0).getStringCellValue())) {
                break;
            }
            nodes.add(getNodeFromExcelRow(currentRow));
        }

        if (nodes.isEmpty()) {
            throw new ParseExcelFileException("No node info in uploaded excel file.");
        }
        return nodes;
    } catch (final IOException | IllegalArgumentException e) {
        throw new ParseExcelFileException(e);
    }
}
 
Example 3
Source File: ExcelServiceImpl.java    From cs-actions with Apache License 2.0 6 votes vote down vote up
/**
 * Constructs a list of indexes where the data will be added in the worksheet
 *
 * @param index           A list of indexes
 * @param worksheet       The worksheet where the data will be added
 * @param rowData         Data that will be added to the worksheet
 * @param rowDelimiter    rowData row delimiter
 * @param columnDelimiter rowData column delimiter
 * @param isRow           true - if the index list (param) contains row indexes
 * @return List of indexes where data will be added in the worksheet
 */
public static List<Integer> processIndex(final String index, final Sheet worksheet, final String rowData, final String rowDelimiter,
                                         final String columnDelimiter, final boolean isRow, final boolean hasHeader) {
    final String[] rows = rowData.split(rowDelimiter);
    String[] indexArray = null;
    if (!StringUtils.isBlank(index)) {
        indexArray = index.split(",");
    }
    int sheetLastRowIndex = worksheet.getLastRowNum();
    if (sheetLastRowIndex > 0) {
        sheetLastRowIndex++;
    }
    final int dataRows = rows.length;
    final int dataColumns = rows[0].split(columnDelimiter).length;
    int headerOffset = 0;
    if (hasHeader) {
        headerOffset = 1;
    }
    if (isRow) {
        return processIndexWithOffset(indexArray, headerOffset, sheetLastRowIndex, sheetLastRowIndex + dataRows);
    } else {
        return processIndexWithOffset(indexArray, 0, 0, dataColumns);
    }
}
 
Example 4
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 6 votes vote down vote up
/**
 * シートの最大列数を取得する。
 * <p>{@literal jxl.Sheet.getColumns()}</p>
 * @param sheet シートオブジェクト
 * @return 最大列数
 * @throws IllegalArgumentException {@literal sheet == null.}
 */
public static int getColumns(final Sheet sheet) {
    ArgUtils.notNull(sheet, "sheet");

    int minRowIndex = sheet.getFirstRowNum();
    int maxRowIndex = sheet.getLastRowNum();
    int maxColumnsIndex = 0;
    for(int i = minRowIndex; i <= maxRowIndex; i++) {
        final Row row = sheet.getRow(i);
        if(row == null) {
            continue;
        }

        final int column = row.getLastCellNum();
        if(column > maxColumnsIndex) {
            maxColumnsIndex = column;
        }
    }

    return maxColumnsIndex;
}
 
Example 5
Source File: JSONConverter.java    From carbon-identity-framework with Apache License 2.0 6 votes vote down vote up
/**
 * Converts xls sheet to json format.
 * Currently considering the username.
 *
 * @param sheet : The XLS sheet that needs to be converted.
 * @return : Json string which represents the sheet.
 */
public String xlsToJSON(Sheet sheet) {
    int limit = sheet.getLastRowNum();
    users = new JsonArray();

    if (log.isDebugEnabled()) {
        log.debug("Converting XLS sheet to json.");
    }

    for (int i = 1; i < limit + 1; i++) {
        Row row = sheet.getRow(i);
        Cell cell = row.getCell(0);
        String name = cell.getStringCellValue();
        JsonPrimitive userJson = new JsonPrimitive(name);
        users.add(userJson);
    }
    content.add(UserMgtConstants.USERS, users);
    return content.toString();
}
 
Example 6
Source File: ExcelExportOfTemplateUtil.java    From autopoi with Apache License 2.0 6 votes vote down vote up
/**
 * 先判断删除,省得影响效率
 * 
 * @param sheet
 * @param map
 * @throws Exception
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
	Row row = null;
	Cell cell = null;
	int index = 0;
	while (index <= sheet.getLastRowNum()) {
		row = sheet.getRow(index++);
		if (row == null) {
			continue;
		}
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			cell = row.getCell(i);
			if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				String text = cell.getStringCellValue();
				if (text.contains(IF_DELETE)) {
					if (Boolean.valueOf(eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map).toString())) {
						PoiSheetUtility.deleteColumn(sheet, i);
					}
					cell.setCellValue("");
				}
			}
		}
	}
}
 
Example 7
Source File: ImportExcelUtils.java    From MicroCommunity with Apache License 2.0 6 votes vote down vote up
/**
 * 获取Sheet页内容
 *
 * @param sheet
 * @return
 */
public static final List<Object[]> listFromSheet(Sheet sheet) {

    List<Object[]> list = new ArrayList<Object[]>();
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        Row row = sheet.getRow(r);
        if (row == null || row.getPhysicalNumberOfCells() == 0) continue;
        Object[] cells = new Object[row.getLastCellNum()];
        for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell == null) continue;
            //判断是否为日期类型
            cells[c] = getValueFromCell(cell);
        }
        list.add(cells);
    }
    return list;
}
 
Example 8
Source File: PersonSheetConfigurator.java    From o2oa with GNU Affero General Public License v3.0 5 votes vote down vote up
public PersonSheetConfigurator(XSSFWorkbook workbook, Sheet sheet) {
	this.sheetIndex = workbook.getSheetIndex(sheet);
	Row row = sheet.getRow(sheet.getFirstRowNum());
	this.firstRow = sheet.getFirstRowNum() + 1;
	this.lastRow = sheet.getLastRowNum();
	memoColumn = row.getLastCellNum() + 1;
	for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);
		if (null != cell) {
			String str = this.getCellStringValue(cell);
			if (StringUtils.isNotEmpty(str)) {
				if (nameItems.contains(str)) {
					this.nameColumn = i;
				} else if (uniqueItems.contains(str)) {
					this.uniqueColumn = i;
				} else if (employeeItems.contains(str)) {
					this.employeeColumn = i;
				} else if (mobileItems.contains(str)) {
					this.mobileColumn = i;
				} else if (mailItems.contains(str)) {
					this.mailColumn = i;
				} else if (genderTypeItems.contains(str)) {
					this.genderTypeColumn = i;
				} else {
					Matcher matcher = attributePattern.matcher(str);
					if (matcher.matches()) {
						String attribute = matcher.group(1);
						this.attributes.put(attribute, new Integer(i));
					}
				}
			}
		}
	}
}
 
Example 9
Source File: DeleteCellService.java    From cs-actions with Apache License 2.0 5 votes vote down vote up
public static Map<String, String> deleteCell(@NotNull final DeleteCellInputs deleteCellInputs) {
    try {
        final String excelFileName = deleteCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, deleteCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int firstColumnIndex = 0;
        final int lastRowIndex = worksheet.getLastRowNum();
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(deleteCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(deleteCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        if (rowIndexList.size() != 0 && columnIndexList.size() != 0) {
            final int deleteCellResult = deleteCell(worksheet, rowIndexList, columnIndexList);
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row r : worksheet) {
                for (Cell c : r) {
                    if (c.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
            return getSuccessResultsMap(String.valueOf(deleteCellResult));

        } else {
            return getSuccessResultsMap("0");
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}
 
Example 10
Source File: DefaultExcelReader.java    From myexcel with Apache License 2.0 5 votes vote down vote up
private void readThenConsume(Sheet sheet, Map<Integer, Field> fieldMap, Consumer<T> consumer, Function<T, Boolean> function) {
    long startTime = System.currentTimeMillis();
    final int firstRowNum = sheet.getFirstRowNum();
    final int lastRowNum = sheet.getLastRowNum();
    log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
    if (lastRowNum < 0) {
        log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
        return;
    }
    DataFormatter formatter = new DataFormatter();
    for (int i = firstRowNum; i <= lastRowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            log.info("Row of {} is null,it will be ignored.", i);
            continue;
        }
        boolean noMatchResult = rowFilter.negate().test(row);
        if (noMatchResult) {
            log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
            continue;
        }
        int lastColNum = row.getLastCellNum();
        if (lastColNum < 0) {
            continue;
        }
        T obj = instanceObj(fieldMap, formatter, row);
        if (beanFilter.test(obj)) {
            if (consumer != null) {
                consumer.accept(obj);
            } else if (function != null) {
                Boolean noStop = function.apply(obj);
                if (!noStop) {
                    break;
                }
            }
        }
    }
    log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
}
 
Example 11
Source File: XsRow.java    From excel-io with MIT License 5 votes vote down vote up
/**
 * Create new row.
 * @param sheet Sheet
 * @return Row Row
 */
private Row createRow(final Sheet sheet) {
    Row row;
    if (this.index == -1) {
        final int num = sheet.getLastRowNum();
        row = sheet.createRow(num);
        sheet.createRow(num + 1);
    } else {
        row = sheet.getRow(this.index - 1);
        if (row == null) {
            row = sheet.createRow(this.index - 1);
        }
    }
    return row;
}
 
Example 12
Source File: PoiSheetUtility.java    From autopoi with Apache License 2.0 5 votes vote down vote up
/**
 * Given a sheet, this method deletes a column from a sheet and moves all
 * the columns to the right of it to the left one cell.
 * 
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
	int maxColumn = 0;
	for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
		Row row = sheet.getRow(r);

		// if no row exists here; then nothing to do; next!
		if (row == null)
			continue;

		// if the row doesn't have this many columns then we are good; next!
		int lastColumn = row.getLastCellNum();
		if (lastColumn > maxColumn)
			maxColumn = lastColumn;

		if (lastColumn < columnToDelete)
			continue;

		for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
			Cell oldCell = row.getCell(x - 1);
			if (oldCell != null)
				row.removeCell(oldCell);

			Cell nextCell = row.getCell(x);
			if (nextCell != null) {
				Cell newCell = row.createCell(x - 1, nextCell.getCellType());
				cloneCell(newCell, nextCell);
			}
		}
	}

	// Adjust the column widths
	for (int c = 0; c < maxColumn; c++) {
		sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
	}
}
 
Example 13
Source File: PoiSheetUtility.java    From easypoi with Apache License 2.0 5 votes vote down vote up
/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * 
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = 0; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}
 
Example 14
Source File: PoiUtil.java    From dk-fitting with Apache License 2.0 5 votes vote down vote up
/**
 * 获取表中所有数据
 */
public List<List> getDataSheet(int sheetNumber)
        throws FileNotFoundException {
    Sheet sheet = workbook.getSheetAt( sheetNumber );
    List<List> result = new ArrayList<List>();
    // 获取数据总行数,编号是从0开始的
    int rowcount = sheet.getLastRowNum() + 1;
    if (rowcount < 1) {
        return result;
    }
    // 逐行读取数据
    for (int i = 0; i < rowcount; i++) {
        // 获取行对象
        Row row = sheet.getRow( i );
        if (row != null) {
            List<Object> rowData = new ArrayList<Object>();
            // 获取本行中单元格个数
            int column = row.getLastCellNum();
            // 获取本行中各单元格的数据
            for (int cindex = 0; cindex < column; cindex++) {
                Cell cell = row.getCell( cindex );
                // 获得指定单元格中的数据
                 //getCellString( cell );
                rowData.add( cell.getStringCellValue() );
            }
            result.add( rowData );
        }
    }
    return result;
}
 
Example 15
Source File: ExcelUtil.java    From autopoi with Apache License 2.0 4 votes vote down vote up
/**
     * 读取excel文件
     * @param wb
     * @param sheetIndex sheet页下标:从0开始
     * @param startReadLine 开始读取的行:从0开始
     * @param tailLine 去除最后读取的行
     */
    public static ArrayList<Map<String,String>> readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        ArrayList<Map<String,String>> result = new ArrayList<Map<String,String>>();
        for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {

            row = sheet.getRow(i);
            Map<String,String> map = new HashMap<String,String>();
            for(Cell c : row) {
                String returnStr = "";

                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                //判断是否具有合并单元格
                if(isMerge) {
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
//                    System.out.print(rs + "------ ");
                    returnStr = rs;
                }else {
//                    System.out.print(c.getRichStringCellValue()+"++++ ");
                    returnStr = c.getRichStringCellValue().getString();
                }
                if(c.getColumnIndex()==0){
                    map.put("id",returnStr);
                }else if(c.getColumnIndex()==1){
                    map.put("base",returnStr);
                }else if(c.getColumnIndex()==2){
                    map.put("siteName",returnStr);
                }else if(c.getColumnIndex()==3){
                    map.put("articleName",returnStr);
                }else if(c.getColumnIndex()==4){
                    map.put("mediaName",returnStr);
                }else if(c.getColumnIndex()==5){
                    map.put("mediaUrl",returnStr);
                }else if(c.getColumnIndex()==6){
                    map.put("newsSource",returnStr);
                }else if(c.getColumnIndex()==7){
                    map.put("isRecord",returnStr);
                }else if(c.getColumnIndex()==8){
                    map.put("recordTime",returnStr);
                }else if(c.getColumnIndex()==9){
                    map.put("remark",returnStr);
                }

            }
            result.add(map);
//            System.out.println();

        }
        return result;

    }
 
Example 16
Source File: DataSrcExcel.java    From xresloader with MIT License 4 votes vote down vote up
/***
 * 构建macro表cache,由于macro表大多数情况下都一样,所以加缓存优化
 */
HashMap<String, String> init_macro_with_cache(List<SchemeConf.DataInfo> src_list) {
    LinkedList<HashMap<String, String>> data_filled = new LinkedList<HashMap<String, String>>();

    IdentifyDescriptor column_ident = new IdentifyDescriptor();

    // 枚举所有macro表信息
    for (SchemeConf.DataInfo src : src_list) {
        String file_path = "";
        if (false == src.file_path.isEmpty()) {
            file_path = src.file_path;
        }
        String fp_name = file_path + "/" + src.table_name;

        // 优先读缓存
        MacroFileCache res = macro_cache.cache.getOrDefault(fp_name, null);
        if (null != res) {
            if (res.file.file_path.equals(file_path) && res.file.table_name.equals(src.table_name)
                    && res.file.data_row == src.data_row && res.file.data_col == src.data_col) {
                data_filled.add(res.macros);
                continue;
            } else {
                ProgramOptions.getLoger().warn(
                        "try to open macro source \"%s:%s\" (row=%d,col=%d) but already has cache \"%s:%s\" (row=%d,col=%d). the old macros will be covered",
                        file_path, src.table_name, src.data_row, src.data_col, res.file.file_path,
                        res.file.table_name, res.file.data_row, res.file.data_col);
            }
        }
        res = new MacroFileCache(src, file_path);

        if (file_path.isEmpty() || src.table_name.isEmpty() || src.data_col <= 0 || src.data_row <= 0) {
            ProgramOptions.getLoger().warn("macro source \"%s\" (%s:%d,%d) ignored.", file_path, src.table_name,
                    src.data_row, src.data_col);
            continue;
        }

        Sheet tb = ExcelEngine.openSheet(file_path, src.table_name);
        if (null == tb) {
            ProgramOptions.getLoger().warn("open macro source \"%s\" or sheet %s failed.", file_path,
                    src.table_name);
            continue;
        }

        FormulaEvaluator evalor = tb.getWorkbook().getCreationHelper().createFormulaEvaluator();

        int row_num = tb.getLastRowNum() + 1;
        for (int i = src.data_row - 1; i < row_num; ++i) {
            Row row = tb.getRow(i);
            column_ident.index = src.data_col - 1;
            DataContainer<String> data_cache = getStringCache("");
            ExcelEngine.cell2s(data_cache, row, column_ident);
            String key = data_cache.get();

            column_ident.index = src.data_col;
            data_cache = getStringCache("");
            ExcelEngine.cell2s(data_cache, row, column_ident, evalor);

            String val = data_cache.get();
            if (null != key && null != val && !key.isEmpty() && !val.isEmpty()) {
                if (res.macros.containsKey(key)) {
                    ProgramOptions.getLoger().warn("macro key \"%s\" is used more than once.", key);
                }
                res.macros.put(key, val);
            }
        }

        macro_cache.cache.put(fp_name, res);
        data_filled.add(res.macros);
    }

    // 空对象特殊处理
    if (data_filled.isEmpty()) {
        return macro_cache.empty;
    }

    // 只有一个macro项,则直接返回
    if (1 == data_filled.size()) {
        return data_filled.getFirst();
    }

    HashMap<String, String> ret = new HashMap<String, String>();
    for (HashMap<String, String> copy_from : data_filled) {
        ret.putAll(copy_from);
    }

    return ret;
}
 
Example 17
Source File: ModifyCellService.java    From cs-actions with Apache License 2.0 4 votes vote down vote up
@NotNull
public static Map<String, String> modifyCell(@NotNull final ModifyCellInputs modifyCellInputs) {
    try {
        final String excelFileName = modifyCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, modifyCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int lastRowIndex = worksheet.getLastRowNum();
        final int firstColumnIndex = 0;
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);
        final String columnDelimiter = modifyCellInputs.getColumnDelimiter();
        final String newValue = modifyCellInputs.getNewValue();

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(modifyCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(modifyCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        final List<String> dataList = getDataList(newValue, columnIndexList, columnDelimiter);

        incompleted = false;
        final int modifyCellDataResult = modifyCellData(worksheet, rowIndexList, columnIndexList, dataList);

        if (modifyCellDataResult != 0) {
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row row : worksheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(cell);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
        }

        if (modifyCellDataResult == rowIndexList.size() && !incompleted) {
            return getSuccessResultsMap(String.valueOf(modifyCellDataResult));
        } else {
            return getFailureResultsMap(String.valueOf(modifyCellDataResult));
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}
 
Example 18
Source File: GetRowIndexByConditionService.java    From cs-actions with Apache License 2.0 4 votes vote down vote up
private static String getRowIndex(final Sheet worksheet,
                                  final int firstRow,
                                  final String input,
                                  final int columnIndex,
                                  final String operator) {
    String result = "";
    double cellValueNumeric;
    String cellFormat;

    double inputNumeric = processValueInput(input);

    for (int i = firstRow; i <= worksheet.getLastRowNum(); i++) {
        Row row = worksheet.getRow(i);
        if (row == null) {
            row = worksheet.createRow(i);
        }
        if (row != null) {
            Cell cell = row.getCell(columnIndex);
            if (cell == null) {
                cell = row.createCell(columnIndex);
            }
            if (cell != null) {
                CellType cellType = cell.getCellType();
                if (cellType != CellType.ERROR) {
                    cellFormat = getCellType(cell);
                    //string comparison
                    if (cellFormat.equalsIgnoreCase("string") && inputFormat.equalsIgnoreCase("string")) {
                        DataFormatter aFormatter = new DataFormatter();
                        String aCellString = aFormatter.formatCellValue(cell);
                        if (compareStringValue(aCellString, input, operator)) {
                            result += i + ",";
                        }
                    }
                    //value input is empty, and the cell in the worksheet is in numeric type
                    else if (!cellFormat.equalsIgnoreCase(inputFormat))
                    //((cellType != CellType.STRING && inputFormat.equalsIgnoreCase("string"))||
                    //(cellType != CellType.NUMERIC && !inputFormat.equalsIgnoreCase("string")))
                    {
                        if (operator.equals("!=")) {
                            result += i + ",";
                        }
                    }

                    //numeric comparison
                    else if (cellType == CellType.NUMERIC && !inputFormat.equalsIgnoreCase("string")) {
                        cellValueNumeric = cell.getNumericCellValue();
                        //both are date or time
                        if ((cellFormat.equalsIgnoreCase("date") && inputFormat.equalsIgnoreCase("date")) ||
                                (cellFormat.equalsIgnoreCase("time") && inputFormat.equalsIgnoreCase("time")) ||
                                (cellFormat.equalsIgnoreCase("num") && inputFormat.equalsIgnoreCase("num"))) {
                            if (compareNumericValue(cellValueNumeric, inputNumeric, operator)) {
                                result += i + ",";
                            }
                        }
                    }
                }
            }
        }
    }
    if (!result.isEmpty()) {
        final int index = result.lastIndexOf(',');
        result = result.substring(0, index);
    }

    return result;
}
 
Example 19
Source File: TrpXlsxBuilder.java    From TranskribusCore with GNU General Public License v3.0 4 votes vote down vote up
private void handleContinuedTags(String continued, Sheet tagnameSheet, String tagname){
		//handles continued tags over several lines
		Sheet overviewSheet;
		if (wb.getSheet(overview) != null){
			overviewSheet = wb.getSheet(overview);
		}
		else{
			return;
		}
		
		int lastRowIdxOfFirstSheet = overviewSheet.getLastRowNum();
		if (lastRowIdxOfFirstSheet == 0){
			return;
		}
		
		int lastRowIdxOfTagnameSheet = tagnameSheet.getLastRowNum();
		if (lastRowIdxOfTagnameSheet == 0){
			return;
		}
		
		Row prevRowOfOverviewSheet = overviewSheet.getRow(lastRowIdxOfFirstSheet);
		String allAttributes = prevRowOfOverviewSheet.getCell(prevRowOfOverviewSheet.getLastCellNum()-1).getStringCellValue();
		
		int i = 1;
		while (!allAttributes.contains(tagname) && lastRowIdxOfFirstSheet>i){
			prevRowOfOverviewSheet = overviewSheet.getRow(lastRowIdxOfFirstSheet-i);
			allAttributes = prevRowOfOverviewSheet.getCell(prevRowOfOverviewSheet.getLastCellNum()-1).getStringCellValue();
			i++;
			if(allAttributes.contains(tagname)){
				logger.debug("same tagname found");
				break;
			}
		}
		
		Row prevRowOfTagnameSheet = tagnameSheet.getRow(lastRowIdxOfTagnameSheet);
		
		String currValue = prevRowOfOverviewSheet.getCell(0).getStringCellValue();
		String lastChar = currValue.substring(currValue.length()-1);
		logger.debug("last char is " + lastChar);
		
		if (lastChar.matches("[\\\u00AD\\\u002D\\\u00AC\\\u003D]")){
			logger.debug("last char is soft hyphen, minus, not sign, equal sign");
			lastChar = lastChar.replaceAll("[\\\u00AD\\\u002D\\\u00AC\\\u003D]", "");
		}
		else{
			lastChar = lastChar.concat(" ");
		}
		
		currValue = currValue.substring(0, currValue.length()-1).concat(lastChar).concat(continued);
		logger.debug("value to store " + currValue);
		prevRowOfOverviewSheet.getCell(0).setCellValue(currValue);
		
		
		String currValueTag = prevRowOfTagnameSheet.getCell(0).getStringCellValue();
		String lastCharTag = currValueTag.substring(currValueTag.length()-1);
		logger.debug("last char is " + lastCharTag);
		
		if (lastCharTag.matches("[\\\u00AD\\\u002D\\\u00AC\\\u003D]")){
			logger.debug("last char is soft hyphen, minus, not sign, equal sign");
			lastCharTag = lastCharTag.replaceAll("[\\\u00AD\\\u002D\\\u00AC\\\u003D]", "");
		}
		else{
			lastCharTag = lastCharTag.concat(" ");
		}
		
		currValueTag = currValueTag.substring(0, currValueTag.length()-1).concat(lastCharTag).concat(continued);
		logger.debug("value to store " + currValueTag);
		prevRowOfTagnameSheet.getCell(0).setCellValue(currValueTag);
		
//		//soft hyphen
//		currValue = currValue.replaceAll("\u00AD", "");
//		//minus
//		currValue = currValue.replaceAll("\u002D", "");
//		//not sign
//		currValue = currValue.replaceAll("\u00AC", "");
//		//= equal sign
//		currValue = currValue.replaceAll("\u003D", "");

	}
 
Example 20
Source File: AbstractExtractorTest.java    From TomboloDigitalConnector with MIT License 4 votes vote down vote up
protected Workbook makeWorkbook(String sheetName, List<List<Object>> rowSpecs){
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(sheetName);

    for (List<Object> rowSpec : rowSpecs) {
        int rowId = (sheet.getPhysicalNumberOfRows()==0)?0:sheet.getLastRowNum()+1;
        Row row = sheet.createRow(rowId);

        for (Object cellSpec : rowSpec) {
            // Note that sheet.getLastRowNum() and row.getLastCellNum() do not behave alike
            int cellId = (row.getPhysicalNumberOfCells()==0)?0:row.getLastCellNum();
            if (cellSpec == null) {
                row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
                continue;
            }
            switch (cellSpec.getClass().getCanonicalName()) {
                case "java.lang.Integer":
                    row.createCell(cellId).setCellValue((Integer)cellSpec);
                    break;
                case "java.lang.String":
                    row.createCell(cellId).setCellValue((String)cellSpec);
                    break;
                case "java.lang.Double":
                    row.createCell(cellId).setCellValue((Double)cellSpec);
                    break;
                case "java.lang.Boolean":
                    row.createCell(cellId).setCellValue((Boolean)cellSpec);
                    break;
                case "java.util.Date":
                    row.createCell(cellId).setCellValue((Date)cellSpec);
                    break;
                case "java.util.Calendar":
                    row.createCell(cellId).setCellValue((Calendar)cellSpec);
                    break;
                case "org.apache.poi.ss.formula.Formula":
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_FORMULA);
                    Cell cell = row.getCell(row.getLastCellNum());
                    cell.setCellFormula(((Formula)cellSpec).toString());
                    break;
                default:
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
            }
        }
    }
    return workbook;
}