导入Java POI时忽略导出到excel的空行Java POI

Question

public static TempFile createExcelFile(String sheetTitle, String title, String headerSummary, List<String> header, List<LinkedHashMap<String, String>> data)
{
    TempFile result = new TempFile(FileKeyFactory.getFileKey(ExcelReporter.class).getFullPathKey());
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet spreadSheet;
    DataFormat format = null;
    int rowIndex = 0;

    // Get a DataFormat object and use it to create a CellStyle object
    // with the following format set for the cells @. The @ or ampersand
    // sets the format so that the cell will hold text.
    format = wb.createDataFormat();

    if (sheetTitle != null)
    {
        spreadSheet = wb.createSheet(sheetTitle);
    }
    else
    {
        spreadSheet = wb.createSheet();
    }

    if (title != null)
    {
        // Title
        HSSFFont titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setItalic(true);
        HSSFRow titleRow = spreadSheet.createRow(rowIndex++);
        HSSFCell cell = titleRow.createCell(0);
        cell.setCellValue(title);
        cell.getCellStyle().setFont(titleFont);
        spreadSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 100));
    }
    else
    {
        spreadSheet = wb.createSheet();
    }

    HSSFCellStyle style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("@"));

    if (headerSummary != null)
    {
        HSSFFont headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 9);
        headerFont.setItalic(false);

        HSSFCellStyle headerCellStyle = wb.createCellStyle();
        headerCellStyle.setDataFormat(format.getFormat("@"));
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setWrapText(true);

        HSSFRow headerRow = spreadSheet.createRow(rowIndex++);
        HSSFCell headerCell = headerRow.createCell(0);

        headerCell.setCellValue(headerSummary);
        headerCell.setCellStyle(headerCellStyle);
        //increase row height to accommodate two lines of text
        headerRow.setHeightInPoints((9 * spreadSheet.getDefaultRowHeightInPoints()));

        //adjust column width to fit the content
        spreadSheet.autoSizeColumn((short) 1);
        spreadSheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 100));
    }


    /*
     * Data table header
     */
    HSSFRow dateRow = spreadSheet.createRow(rowIndex++);
    Calendar calendar = Calendar.getInstance();
    HSSFCreationHelper createHelper = wb.getCreationHelper();
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
    HSSFCell cell = dateRow.createCell(0);
    cell.setCellValue("Created: " + DateParser.toString(calendar, DateParser.MONTH_WORD_DATE_FORMAT));
    cell.setCellStyle(cellStyle);

    HSSFFont headerTableFont = wb.createFont();
    headerTableFont.setFontHeightInPoints((short) 10);
    headerTableFont.setColor(IndexedColors.WHITE.getIndex());

    HSSFCellStyle tableHeaderStyle = wb.createCellStyle();
    tableHeaderStyle.setDataFormat(format.getFormat("@"));
    tableHeaderStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    tableHeaderStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    tableHeaderStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    tableHeaderStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    tableHeaderStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    tableHeaderStyle.setFont(headerTableFont);
    tableHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    HSSFRow tableHeaderRowRow = spreadSheet.createRow(rowIndex++);

    for (int i = 0; i < header.size(); i++)
    {
        HSSFCell ipHeaderCell = tableHeaderRowRow.createCell(i);
        ipHeaderCell.setCellStyle(tableHeaderStyle);
        ipHeaderCell.setCellValue(header.get(i));
        spreadSheet.autoSizeColumn(i);
    }

    /*
     * ********************** DATA *******************
     */
    HSSFCellStyle dataCellStyle = wb.createCellStyle();
    dataCellStyle.setDataFormat(format.getFormat("@"));
    dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    //spreadSheet.createRow(rowIndex++);
    for (LinkedHashMap<String, String> row : data)
    {
        HSSFRow currentRow = spreadSheet.createRow(rowIndex++);
        for (int i = 0; i < header.size(); i++)
        {
            HSSFCell dataCell = currentRow.createCell(i);
            dataCell.setCellStyle(dataCellStyle);
            dataCell.setCellType(Cell.CELL_TYPE_STRING);

            String celVal = row.get(header.get(i));
            dataCell.setCellValue(celVal);
        }
    }

    // Resize columns automatically.
    for (int i = 0; i < header.size(); i++)
    {
        spreadSheet.setDefaultColumnStyle(i, style);
        spreadSheet.autoSizeColumn(i);
    }

    FileOutputStream resultOutStream = null;
    try
    {
        resultOutStream = new FileOutputStream(result);
        wb.write(resultOutStream);
        resultOutStream.flush();
    }
    catch (Exception e)
    {
        Logger.ERROR("File path: " + result.getAbsolutePath() + File.separator + result.getName(), e);
        throw new RuntimeException("File path: " + result.getAbsolutePath() + File.separator + result.getName(), e);
    }
    finally
    {
        try
        {
            if (resultOutStream != null)
            {
                resultOutStream.close();
            }
        }
        catch (Exception ignore)
        {

        }
    }

    return result;
}

public void import()
{
    ...
    InputStream input = new BufferedInputStream(new FileInputStream(file));
    POIFSFileSystem fs = new POIFSFileSystem(input);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);

    Iterator rows = sheet.rowIterator();

    rows.next(); // skip title
    rows.next(); // skip empty row
    List<String> header = getLine((HSSFRow) rows.next());
    ...
}

private List<String> getLine(HSSFRow row)
{
    List<String> header = new ArrayList<String>();
    Iterator<Cell> cellIterator = row.cellIterator();

    while(cellIterator.hasNext())
    {
        HSSFCell cell = (HSSFCell)cellIterator.next();
        cell.setCellType(Cell.CELL_TYPE_STRING);
        header.add(cell.toString());
    }

    return header;
} 

知道为什么在导出并再次导入后，调用sheet.rowiterator（）时会忽略空行吗？

Answer 1

这是预期的行为。在Javadocs中：

返回物理行的迭代器。这意味着第3个元素可能不是第三行，比如说，第二行没有定义。

如果您查看关于在行和单元格上进行迭代的Apache POI文档，您将看到更详细的描述，以及如何强制在一个范围内的所有行上进行迭代的说明，如果这是您真正想要的。您的代码应该类似于（摘自Apache POI文档）

// Decide which rows to process
int rowStart = Math.min(15, sheet.getFirstRowNum());
int rowEnd = Math.max(1400, sheet.getLastRowNum());

for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
   Row r = sheet.getRow(rowNum);
   if (r == null) {
      // Handle empty row
      continue;
   }

   int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);

   for (int cn = 0; cn < lastColumn; cn++) {
      Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
      if (c == null) {
         // The spreadsheet is empty in this cell
      } else {
         // Do something useful with the cell's contents
      }
   }
}