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

/**
 * 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++;
    }
}
 

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);
    }
}
 

/**
 * 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);
    }
}
 

/**
 * シートの最大列数を取得する。
 * <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;
}
 

/**
 * 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();
}
 

/**
 * 先判断删除,省得影响效率
 * 
 * @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("");
				}
			}
		}
	}
}
 

/**
 * 获取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;
}
 

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));
					}
				}
			}
		}
	}
}
 

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());
    }
}
 

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);
}
 

/**
 * 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;
}
 

/**
 * 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));
	}
}
 

/**
 * 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));
    }
}
 

/**
 * 获取表中所有数据
 */
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;
}
 

/**
     * 读取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;

    }
 

/***
 * 构建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;
}
 

@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());
    }
}
 

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;
}
 

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", "");

	}
 

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;
}