Java POI Export to excel with empty string is ignored on import
I have a java program to export / import excel files (.xls) using Apache POI lib (I am using HSSF prefix classes for old excel.xls files).
My excel format:
Line 1: Title
Line 2: Empty
Line 3: Table with title and content
Suppose my table has 5 rows including the title, thus 7 rows in total. I am exporting data to excel file, file size 6k. Without changing anything in the file, I import it, and when debugging the import, I realized that the number of lines is 6! which means that for some reason it ignores the blank line which I don't understand.
The following script, I export a file, open it with MS Excel saving, the file size changes to 24k. I do the import and it works, the total row size is 7 again.
Export and start import code below:
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;
}
Any idea why, after export and import, again the empty string is ignored when calling sheet.rowIterator ()?
source to share
This is expected behavior. From the Javadocs :
Returns an iterator of PHYSICAL strings. The value of the 3rd element may not be the third string if, for example, the second string is undefined.
If you look at the Apache POI documentation on iterating over rows and cells , you can see what is described in more detail, as well as instructions on how to force iterate over all rows in a range, if that's what you really want. Your code will want to be something like (taken from the Apache POI docs)
// 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 contents
}
}
}
source to share
I have never experienced this problem, but I am using sheet.getRow (row) instead of rowIterator (). I am guessing it might be a bug in the implementation of rowIterator in POI (which you should inform them).
So, can you try to change your code:
Iterator rows = sheet.rowIterator();
rows.next(); // skip title
rows.next(); // skip empty row
List<String> header = getLine((HSSFRow) rows.next());
To:
int row = 0;
++row; // skip title;
++row; // skip empty row
List<String> header = getLine((HSSFRow)sheet.getRow(row)); ++row;
Hope it works.
Note: you must also use "Workbook", "Sheet" and "Row" instead of "HSSFWorkbook", "HSSFSheet" and "HSSFRow". This will save you the trouble of typing, and your code will be ready in case it takes you a day to process any XLSX file!
source to share