styles.xml breaking password-protected XSSFWorkbook (Apache POI v3.16) save process

Question

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 sheet using Apache POI 3.14?)

[EDIT - Added the below code to show what I'm doing: creates XSSFWorkbook and extracts data, then encrypts]

Note: I'm running the code on - server-side Javascript - using Mozilla Rhino v1.7R3 which provides support for nearly all of ECMAScript Edition 5 plus a few features from Mozilla Javascript 1.8.

var wb = new XSSFWorkbook();
var createHelper = wb.getCreationHelper();

// Begin filling in rows/cells
addMostRecentSheet(wb);

var filepath = [hidden]
var fileOut = new java.io.FileOutputStream(filepath);
wb.write(fileOut);
fileOut.close();

var fs = new POIFSFileSystem();

var info = new EncryptionInfo(EncryptionMode.agile, CipherAlgorithm.aes192, HashAlgorithm.sha384, -1, -1, null);
var enc = info.getEncryptor();
enc.confirmPassword("password");

var os = enc.getDataStream(fs);
opc.saveImpl(os);  //<<-----Crash there - unable to save /x1/styles.xml
opc.close();

var fos = new java.io.FileOutputStream(filepath);
fs.writeFilesystem(fos);
fos.close();

I eventually managed to save password protected xlsx but I had to remove the styling of Date columns.

Below is the code to format cells into Date celltype:

function createDateCell(row, colNum, value)
{
    var cell;
    if (value)
    {
        cell = row.createCell(colNum);
        cell.setCellValue(value);
        var cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
        cell.setCellStyle(cellStyle)
    }
    else
    {
        cell = row.createCell(colNum, Cell.CELL_TYPE_BLANK);
    }
    return cell;
}

Yet when running the program, I keep getting this error, is there a workaround to keep the Date column type? The error message:

org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: 
Fail to save: an error occurs while saving the package : 
The part /xl/styles.xml failed to be saved in the stream with marshaller
org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@216fb8e

Why are you using such an old version of Apache POI? What happens when you upgrade to the latest stable supported version? — Gagravarr
@Gagravarr - fair question - that's the reason why I mentioned the Java version 1.7.0-251, the system I'm adopting Apache POI is fixed to that java version. The infrastructure doesn't allow me to upgrade the Java to match the latest version Apache POI is offering. — Joshua
I'd try it on another box with the latest Java and Apache POI. Try that. That'll tell you if it is an Apache POI issue that's still present, or a known-fix you'll need to backport or workaround — Gagravarr
Your createDateCell creates a new CellStyle for each cell. This is wrong. CellStyles are on workbook level and are stored in /xl/styles.xml. If your createDateCell creates thousand date cells, then it also creates thousand times the same CellStyle. Thus the /xl/styles.xml contains thousand times the same XML for that CellStyle. Don't do that. — Axel Richter
Instead do var cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); only once on workbook level. Then hand over that one CellStyle to the cells creator function, like function createDateCell(row, colNum, value, cellStyle). — Axel Richter

Joshua Joshua · Accepted Answer · 2020-06-07T07:19:50

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:

function createDateCell(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

styles.xml breaking password-protected XSSFWorkbook (Apache POI v3.16) save process

1 Answers