Styles.xml Breaking Password-protected Xssfworkbook (apache Poi V3.16) Save Process

Currently using Apache POI 3.16 on Java version 1.7.0-251 (Unix) Taking a leaf out of the example explained by @Aniruddh Chandegra (How to create and edit a password protect excel

Solution 1:

To create date style cells, you need to do this:

var wb = new XSSFWorkbook();
var createHelper = wb.getCreationHelper();
var dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));

You only need to do it once. By setting the cellStyle at the top, you've populated /xl/styles.xml only once.

When populating the cells, you simply add the dateStyle to the function:

createDateCell(row, colNum++, tables.SHE_SOUTH.DOB.value, dateStyle);

The function createDateCell simply add the cellStyle:

functioncreateDateCell(row,colNum,value, cellStyle){
    var cell;
    if(value){
        cell = row.createCell(colNum, Cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }
    else
    {
        cell = row.createCell(colNum, Cell.CELL_TYPE_BLANK);
    }
    return cell;
}

That way the /xl/styles.xml doesn't get bloated as before. Which allows the encryption of the workbook. Works a treat, with credit to Axel Richter