616

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

<h3>Question</h3>

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
<h3>Answer1:</h3>

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 <em>createDateCell</em> 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

来源:https://stackoverflow.com/questions/62067539/styles-xml-breaking-password-protected-xssfworkbook-apache-poi-v3-16-save-proc

Recommend

  • styles.xml breaking password-protected XSSFWorkbook (Apache POI v3.16) save process [SOLVED]
  • Linux 脚本----PXE脚本
  • ASP.NET MVC passing Model *together* with files back to controller
  • R- Special Characters are not inserting in mysql
  • What is the use of @ symbol in c language
  • How to remove specific value from comma separated string in oracle
  • Multiline textbox in nativescript
  • Does pybtex support accent/special characters in .bib file?
  • Java - Use Input and OutputStream of ProcessBuilder continuously
  • as.data.frame flattens nested list into single row instead of creating row for each record [duplicat
  • How to put a value in flash when testing an action
  • I cannot see the .SVN folders anymore?
  • How to make my website fit on all screen resolutions [closed]
  • How to determine if a coordinate is inside SVG Close Path?
  • WooCommerce, how to remove downloadable products permission for order
  • Google maps error during visualization
  • How do I use RestSharp to POST a login and password to an API?
  • Using Java runtime to add registry key, cause process reg.exe to run forever
  • COM reference or Command tool for ClearTeam Explorer
  • Backbone - Create Multiple Models in Collection - serverside
  • Unable to connect to AWS RDS through PDO
  • Is possible having two COM STA instances of the same component?
  • Showing image on a acro text field position
  • Checking for valid enum types from protobufs
  • How to control xtics in gnuplot
  • Haskell program that can handle any arbitrary deterministic finite automaton
  • Splitting ReportLab table across PDF page (side by side)?
  • C++ Is “const void” As Return Value More Const-Correct Than “void”? [duplicate]
  • How to turn off notice reporting in xampp?
  • `$http:badreq Bad Request Configuration` - from angular post method, what is wrong here?
  • Firebase: How to read from external DB?