HSSF cell style applied only after clicking it in Excel

Question

As article states - created style applies only after i open created *.xls file and double click formatted cell.

 public HSSFWorkbook makeWorkbookExc(List<String[]> allValues, List<String> captions, Integer[] order,
        List<Integer> numTypeColumns,List<Integer> dateTypeColumns, final container container, final List<ErrorContainer> errors) {
    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFFont fontBold = workbook.createFont();
    fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontBold.setFontHeightInPoints((short) 11);
    HSSFCellStyle styleBold = workbook.createCellStyle();
    styleBold.setFont(fontBold);

    HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 11);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(font);
    style.setWrapText(true);
    HSSFDataFormat dataFormat = workbook.createDataFormat();
    HSSFCellStyle dateStyle = ((HSSFWorkbook)workbook).createCellStyle();
    dateStyle.setDataFormat(dataFormat.getFormat("dd.mm.yyyy hh:mm"));
    HSSFSheet baseDataSheet = workbook.createSheet();
    int rowNr = 0;

    Row row = baseDataSheet.createRow(rowNr++);


    for(int i=0; i< allValues.size(); i++) {

        row = baseDataSheet.createRow(rowNr++);
        String[] dataFields = allValues.get(i);

        for (int index = 0 ; index < order.length; index++){
            Cell nmrCell = row.createCell(index);   
            String value = dataFields[order[index]];
            if(value.contains("<br>")){
                //listagg spaces fix
                String trimSpaces = value.trim().replaceAll(" +"," ");
                value = trimSpaces;
                String replace = value.trim().replaceAll("<br> ","\n\n");
                value = replace;
            }

            if (!numTypeColumns.isEmpty() && !"".equals(value) && numTypeColumns.contains(index+1)){
                double valueDouble = Double.parseDouble(value);
                nmrCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                nmrCell.setCellValue(valueDouble);
            } else if(!numTypeColumns.isEmpty() && !"".equals(value) && dateTypeColumns.contains(index+1)){
                nmrCell.setCellStyle(dateStyle);
                nmrCell.setCellValue(value);
            }else
                nmrCell.setCellStyle(style);
                nmrCell.setCellValue(value);


        }

    }

    return workbook;
}

Is there any solution?

Lorem ipsum dolor sit amet, habeo aliquam definitionem qui eu, ut voluptua mandamus ius. Sint aliquam nam at. In eam fastidii inimicus similique. Ne cum viderer diceret, appetere liberavisse sea in. Eam suas brute in, est simul debitis te, falli elitr has id. Sale errem vis no, eu vis case habeo.

Eam ne quidam semper adversarium, vim lorem ridens tractatos ei, vivendum sententiae vix ut. Eros aliquam vivendo ei sea. Te singulis deserunt expetenda cum. Causae petentium nec ne. Ea adhuc graeci est, eos no tritani mnesarchum. Per suavitate torquatos disputationi eu, augue epicuri nec et.

Where's the bit where you create the styles? Also should there be a `{` after the else? — Gagravarr, Dec 04 '15 at 14:47
What version of Apache POI are you using? And if it isn't the latest, what happens when you upgrade? — Gagravarr, Dec 04 '15 at 15:52
Updated to latest stable .13 version and it didn't change a thing. Iam thinking that its somehow linked with default cell style (richtext) before i change it to dateStyle. — mAtsea, Dec 07 '15 at 10:24
I've just noticed that for numbers, you're setting the cell value as a `double` (good!), but for dates it looks like you might be setting the value as a `String` rather than as a `Date` - is that the case? — Gagravarr, Dec 07 '15 at 11:02
yes, thank you I've noticed it too now and added a simpleDateFormat parse :) — mAtsea, Dec 07 '15 at 12:04

score 0 · Accepted Answer · answered Dec 07 '15 at 12:08

0

So the answer was adding date parse, because I initially was getting value as string

SimpleDateFormat date = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
                try {
                    Date dateVal = date.parse(value);
                    nmrCell.setCellStyle(dateStyle);
                    nmrCell.setCellValue(dateVal);
                } catch (ParseException e) {
                    nmrCell.setCellValue(value);
                }

answered Dec 07 '15 at 12:08

mAtsea

47
5

i tried above solution but it didnt worked.Still formatting is getting applied only after clicking over excel cell only. – Prem Prakash Feb 17 '17 at 18:44
I am also facing the same issue. – Madan Mar 16 '17 at 20:24

score 0 · Answer 2 · edited Jun 25 '20 at 10:08

0

Please check the data type of cell.SetCellValue. Example for currency dataformatter the data type will be double cell.SetCellValue(double) or date it will be date. cell.SetCellValue(String) might cause issue

edited Jun 25 '20 at 10:08

mate00

2,727
5
26
34

answered Jun 25 '20 at 05:45

Avijit Ghosh

1

HSSF cell style applied only after clicking it in Excel

2 Answers2