I am generating an .xlsx file using Apache POI 5.2.5, sending the byte array to the client side and downloading the file locally on the client's machine.
When trying to open the file, I'm getting "We found a problem with some content in *.xlsx. Do you want us to try to recover it as much as we can" error. Clicking yes the file opens both on Windows and on Mac and all the data is present, but not as a table object. Trying to open the file using google sheets works without issues and the table object has a border.
The issue persisted also when I tried to save it directly on the server's machine, so I don't think the problem is on the client side.
The code generating the byte array:
private byte[] generateExcelBytes(List<DATA_OBJECT> data) {
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
XSSFSheet sheet = workbook.createSheet("Data");
CellStyle headerStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerStyle.setFont(headerFont);
CellStyle dataStyle = workbook.createCellStyle();
Font dataFont = workbook.createFont();
dataFont.setFontHeightInPoints((short) 12);
dataStyle.setFont(dataFont);
createTable(sheet, 0, 0, headerStyle, dataStyle, data);
// Write the workbook content to a ByteArrayOutputStream
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
return outputStream.toByteArray();
} catch (IOException e) {
logger.error("Exception when creating xlsx file for account {} CDI status tables", account.getId(), e);
}
return null;
}
private void createTable(XSSFSheet sheet, int startRow, int startCol, CellStyle headerStyle, CellStyle dataStyle, List<DATA_OBJECT> data) {
// Create sample data (replace with your actual data generation logic)
Row headerRow = sheet.createRow(startRow);
// for each header
createCell(headerRow, startCol + X, HEADER_NAME, headerStyle);
// Create data rows
for (int i = 0; i < data.size(); i++) {
DATA_OBJECT data_object = data.get(i);
Row dataRow = sheet.createRow(startRow + i + 1);
// for each data object
createCell(dataRow, startCol, data_object.get_relevant_value, dataStyle);
}
int endRow = startRow + baseHoldings.size();
int endCol = startCol + NUM_OF_COLUMNS;
XSSFAutoFilter autoFilter = sheet.setAutoFilter(new CellRangeAddress(startRow, endRow, startCol, endCol));
for (int i = startCol; i <= startCol + NUM_OF_COLUMNS; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 1000);
}
// Create Excel table
XSSFTable table = sheet.createTable(null);
CTTable cttable = table.getCTTable();
cttable.setDisplayName("Data Table");
cttable.setId(1);
cttable.setName("Data_Table");
cttable.setRef(new CellRangeAddress(startRow, endRow, startCol, endCol).formatAsString());
}
private void createCell(Row row, int columnIndex, String value, CellStyle style) {
Cell cell = row.createCell(columnIndex);
cell.setCellValue(value);
cell.setCellStyle(style);
}
Opening the file on Mac, I managed to see an actual error:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>Repair Result to <FILE_NAME>0.xml</logFileName>
<summary>Errors were detected in file '<FILE_PATH>.xlsx'</summary>
<removedParts summary="Following is a list of removed parts:">
<removedPart>Removed Part: /xl/tables/table1.xml part with XML error. (Table) Xml parsing error Line 2, column 70.</removedPart>
</removedParts>
</recoveryLog>
/xl/tables/table1.xml file contents:
<?xml version="1.0" encoding="UTF-8"?>
<table id="1" displayName="Data Table" name="Data_Table" ref="A1:H3" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"/>
I don't know what causes the XML parsing error, or how to avoid it when generating the file on the server side. Any help will be appreciated.
Multiple issues here.
Do not use areaReference == null in XSSFTable constructor. Instead define the data range for the table as AreaReference.
Avoid using CTTable. Apache POI 5.2.5 provides most of all necessary in XSSFTable. In your case, you forgot creating the table columns which needs correspond to the sheet cells. This is done by XSSFTable table = sheet.createTable(areaReference) properly when areaReference is not null but a correct AreaReference.
Display name must not contain spaces.
And there cannot be sheet filter for a table. Tables use their own filters. Unfortunatelly not provided by XSSFTable up to now. So CTTable must be used for this: table.getCTTable().addNewAutoFilter().setRef(areaReference.formatAsString());.
Not really sure about your List<DATA_OBJECT> data, so I don't really be sure whether you are calculating endRow and endCol correct. Of course the table cannot start at startRow as there is the header, which is outside the table, isn't it?
Complete example using most of your approach but a Object[][] data and a result-file for simplicity:
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFAutoFilter;
public class CreateTableExample {
static void createTable(XSSFSheet sheet, int startRow, int startCol, CellStyle headerStyle, CellStyle dataStyle, Object[][] data) {
Row headerRow = sheet.createRow(startRow);
// for each header
createCell(headerRow, startCol, "HEADER_NAME", headerStyle);
int headerRows = 1; // how many rows outside table?
int endRow = startRow + headerRows + data.length - 1;
int endCol = startCol + data[0].length - 1;
// Merge cells for Header
sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, startCol, endCol));
// Create data rows
for (int i = 0; i < data.length; i++) {
Object[] data_object = data[i];
Row dataRow = sheet.createRow(startRow + headerRows + i);
int c = 0;
for (Object value : data_object) {
createCell(dataRow, startCol + c++, value, dataStyle);
}
}
//XSSFAutoFilter autoFilter = sheet.setAutoFilter(new CellRangeAddress(startRow + headerRows, endRow, startCol, endCol)); // No sheet filter for Table!
for (int i = startCol; i <= endCol; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 1000);
}
// Define the data range for the table
AreaReference areaReference = new AreaReference(new CellReference(startRow + headerRows, startCol), new CellReference(endRow, endCol), SpreadsheetVersion.EXCEL2007);
// Create Excel table
//XSSFTable table = sheet.createTable(null); // do not use areaReference == null!
XSSFTable table = sheet.createTable(areaReference);
// Set the table style
table.getCTTable().addNewTableStyleInfo();
table.getCTTable().getTableStyleInfo().setName("TableStyleLight12");
table.setName("Data Table");
table.setDisplayName("Data_Table"); // display name must not contain spaces!
table.getCTTable().addNewAutoFilter().setRef(areaReference.formatAsString()); // set AutoFilter in table
}
static void createCell(Row row, int columnIndex, Object value, CellStyle style) {
Cell cell = row.createCell(columnIndex);
if (value instanceof String) {
cell.setCellValue((String)value);
} else if (value instanceof Number) {
cell.setCellValue(((Number)value).doubleValue());
}
cell.setCellStyle(style);
}
public static void main(String[] args) {
// Create sample data (replace with your actual data generation logic)
Object[][] data = new Object[][] {
new Object[] {"Name", "Value", "Class", "Amount"},
new Object[] {"Name 1", 123, "Class 1", 123.45},
new Object[] {"Name 2", 456, "Class 2", 456.78},
new Object[] {"Name 3", 789, "Class 3", 789.01},
new Object[] {"Name 4", 123, "Class 4", 123.45},
new Object[] {"Name 5", 456, "Class 5", 456.78},
//...
};
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Sheet1");
CellStyle headerStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerStyle.setFont(headerFont);
CellStyle dataStyle = workbook.createCellStyle();
Font dataFont = workbook.createFont();
dataFont.setFontHeightInPoints((short) 12);
dataStyle.setFont(dataFont);
createTable((XSSFSheet)sheet, 0, 0, headerStyle, dataStyle, data);
// Save the workbook
try (FileOutputStream fileOut = new FileOutputStream("./workbook_with_table.xlsx")) {
workbook.write(fileOut);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}