I want to arrange entire cells in specific column,

Question

I used POI and tried to arrange one entire column. But only the way I found is arrange individual cell. Although I found sheet.setDefaultColumnStyle() and tried to use this function, it doesn't work at all. could you let me know the way of using setDefaultColumnStyle() or another way.

below code is my code to arrange individual cell.

    xlsxFile = new File("data.xlsx");
    wb = new XSSFWorkbook();

    cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    row = sheet1.createRow(0);
    cell = row.createCell(1);
    cell.setCellValue("name");
    cell.setCellStyle(cellStyle);

My english skill is a little awkward. Thank you for reading. If there is anything weird, please let me know.

Answer 1

This seems to be an bug in Apache POI. There are two issues:

First: After using Sheet.setDefaultColumnStyle with a style which defines alignments, POI does not set applyAlignment="true" in the xf element's tag in styles.xml. But it should, because only that will cause Excel to apply the alignments from that style to new cells.

Second: POI itself does not apply this style to new cells in that column. It should set s="1", where 1 is the style number, in the corresponding c tag of Sheet1.xml.

So we have to workaround:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;

class CenteredColumn {

 public static void main(String[] args) {
  try {

   Workbook wb = new XSSFWorkbook();

   Sheet sheet = wb.createSheet("Sheet1");

   CellStyle cellStyle = wb.createCellStyle();
   cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

   sheet.setDefaultColumnStyle(1, cellStyle);

   //Workaround 1: We set setApplyAlignment(true) into the `xf` element's tag in styles.xml.
   //This causes Excel applying alignments from this style to new cells in that column.
   for (int i = 0; i < ((XSSFWorkbook)wb).getStylesSource().getNumCellStyles(); i++) {
    if (((XSSFWorkbook)wb).getStylesSource().getStyleAt(i).equals(cellStyle)) {
     ((XSSFWorkbook)wb).getStylesSource().getCellXfAt(i).setApplyAlignment(true);
    }
   }

   Row row = sheet.getRow(0);
   if (row == null) row = sheet.createRow(0);

   Cell cell = row.getCell(1);
   if (cell == null) cell = row.createCell(1);
   cell.setCellValue("name");
   //Workaround 2: We set the cellStyle to the new cell because POI will not do this itself.
   cell.setCellStyle(cellStyle);

   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
   wb.write(fileOut);

  } catch (IOException ioex) {
  }
 }
}