Apache POI style getting applied to all cells

问题:

    Cell cell = row.createCell(1);
    cell.setCellValue(rdf.getEffectiveDate());
    cell.getCellStyle().setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));

    cell = row.createCell(2);
    cell.setCellValue(rdf.getExpiryDate());
    cell.getCellStyle().setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));

    row.createCell(3).setCellValue(rdf.getPremium());
    row.createCell(4).setCellValue(rdf.getAccountNumber());
    row.createCell(5).setCellValue(rdf.getLedgerName());

I wanted to apply Date Format on two of the above columns. But it is getting applied to all the cells. How can I prevent this.

回答1:

As the documentation states, Cell.getCellStyle() will never return null.

https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#getCellStyle()

When no cell style has been explicitly set for a Cell then it will return the default cell style which is initially shared among all cells in the workbook. Changing this then will obviously affect all cells not having an explictly assigned style.

You need to create a new CellStyle and then assign this to the relevant cells.

From the POI developer guide:

https://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells

Workbook wb = new HSSFWorkbook();
    //Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(0);

    // Create a cell and put a date value in it.  The first cell is not styled
    // as a date.
    Cell cell = row.createCell(0);
    cell.setCellValue(new Date());

    // we style the second cell as a date (and time).  It is important to
    // create a new cell style from the workbook otherwise you can end up
    // modifying the built in style and effecting not only this cell but other cells.
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(
        createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
    cell = row.createCell(1);
    cell.setCellValue(new Date());
    cell.setCellStyle(cellStyle);

    //you can also set date as java.util.Calendar
    cell = row.createCell(2);
    cell.setCellValue(Calendar.getInstance());
    cell.setCellStyle(cellStyle);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

回答2:

Try creating a new cell style. I think you may be changing the default style. So something like this...

CellStyle dateTimeCS = wb.createCellStyle();
dateTimeCS.setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));
cell.setCellStyle(dateTimeCS);

回答3:

use RegionUtil to apply borders to a range of cells

https://poi.apache.org/apidocs/org/apache/poi/ss/util/RegionUtil.html

looks like it was added in version 3.15