excelapacheapache-poipoi-hssfhssf

HSSF cell style applied only after clicking it in Excel


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.


Solution

  • 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);
                    }