I am trying to export a table as xlsx using Apache POI XSSF. The table has 5 columns.
First row has title block merging 5 columns. In the second row, 5 headings for the table. Remaining rows are data. I want to set width of the column as max width of each heading blocks.
I added a condition and tried mySheet.autoSizeColumn(colnum) when adding the header values so that width of the column will be of same as header data. But when I do that, All the columns are getting hidden on the exported xlsx sheet.
These are the styling elements that I use:
public CellStyle createHeadingStyle(XSSFWorkbook myWorkBook){
XSSFCellStyle style = myWorkBook.createCellStyle();
Font fontHeader = myWorkBook.createFont();
fontHeader.setBoldweight((short)4);
fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontHeader.setFontHeightInPoints((short)8);
fontHeader.setFontName("GE Inspira Pitch");
style.setFont(fontHeader);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setHidden(false);
//HSSFPalette palette = myWorkBook.getCustomPalette();
//palette.setColorAtIndex(new Byte((byte) 41), new Byte((byte) 153), new Byte((byte) 204), new Byte((byte) 255));
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(41,153,204,255)));
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
return style;
}
public CellStyle createTitleStyle(XSSFWorkbook myWorkBook){
CellStyle style = myWorkBook.createCellStyle();
Font fontHeader = myWorkBook.createFont();
fontHeader.setBoldweight((short)5);
fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontHeader.setFontHeightInPoints((short)10);
fontHeader.setColor(HSSFColor.RED.index);
fontHeader.setFontName("GE Inspira Pitch");
style.setFont(fontHeader);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setHidden(false);
return style;
}
public CellStyle createDataStyle(XSSFWorkbook myWorkBook){
CellStyle style = myWorkBook.createCellStyle();
Font font = myWorkBook.createFont();
font.setFontName("GE Inspira Pitch");
font.setFontHeightInPoints((short)10);
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setHidden(false);
return style;
}
This is the code block for creating xlsx:
titleLenght = 2;
cell1.setCellValue(excelTitle);
cell1.setCellStyle(titleCellStyle);
boolean headerStyle = true;
for (int j = 0; j < list.size(); j++) {
Row row = mySheet.createRow(j+2+titleLength);
List<String> l2= list.get(j);
for(int k=0; k<l2.size(); k++)
{
Cell cell = row.createCell(k);
cell.setCellValue(l2.get(k));
if(headerStyle){
mySheet.autoSizeColumn(k);
cell.setCellStyle(headingCellStyle);
}else{
cell.setCellStyle(dataCellStyle);
}
}
headerStyle = false;
}
I made the following change to the code and it worked.
titleLenght = 2;
cell1.setCellValue(excelTitle);
boolean headerStyle = true;
for (int j = 0; j < list.size(); j++) {
Row row = mySheet.createRow(j+2+titleLength);
List<String> l2= list.get(j);
for(int k=0; k<l2.size(); k++)
{
Cell cell = row.createCell(k);
cell.setCellValue(l2.get(k));
if(headerStyle){
**cell.setCellStyle(headingCellStyle);
int origColWidth = mySheet.getColumnWidth(k);
mySheet.autoSizeColumn(k);
// Making sure col width is not going less than the header width
if (origColWidth > mySheet.getColumnWidth(k)) {
mySheet.setColumnWidth(k, origColWidth);
}**
}else{
cell.setCellStyle(dataCellStyle);
}
}
headerStyle = false;
}
// Set title style element at last so the first column will not take
// width of title field
cell1.setCellStyle(titleCellStyle);
In my previous code, for the header, autoSizeColumn(K) was returning zero since there is no style associated to it. To be specific no font was assigned since I set used autoSizeColumn(k) before setting style. Hence the width was getting returned as zero. This made all the columns getting hidden.
Now I have added a condition in such a way that width wont become zero at any case. I have moved the title styling to end so that It don't affect the width of the first header column.
Conclusion: