How can I open an existing *.xlsx file, make modifications, then save as new file (leaving original file untouched?)

Question

I need to open an existing *.xlsx Excel file, make some modifications, and then save it as a new file (or stream it to the frontend without saving). The original file must remain unchanged.

For Memory reasons, I avoid using FileInputStream (as described here: http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream )

// XSSFWorkbook, File
OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(pkg);
....
pkg.close();

Copying the file first really seems to work better for me than Streaming without saving. — Robert, Feb 22 '17 at 18:05

score 0 · Answer 1 · answered Feb 21 '17 at 16:44

0

JFileChooser fileOpen = new JFileChooser();
fileOpen.showOpenDialog(theFrame); // gives you an open file dialog
readFile(fileOpen.getSelectedFile()); // write you reading content in reaFile method

JFileChooser fileSave = new JFileChooser();
fileSave.showSaveDialog(Frame); //gives you a dialog box for saving 
saveFile(fileSave.getSelectedFile()); // write your saving content in saveFile method

answered Feb 21 '17 at 16:44

Ram Satya

11
6

This is specific to Swing only. – Robert Feb 22 '17 at 18:06

score 0 · Answer 2 · edited May 23 '17 at 12:31

Here is how this can be done when using OPCPackage to read (try/catch/finally ommitted for readibility):

OPCPackage pkg = OPCPackage.open("existingFile.xlsx");
XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(pkg);


make your modifications... XSSFSheet sheet = wb.getSheetAt(0); ...
fos = new FileOutputStream("outputFileName.xlsx");
wb.write(fos);
pkg.close();
fos.close();
Faces.sendFile(new File(outputFileName)

In order for this to work, it is important to use different file path for Input and for Output.

The last line sends the File to your Browser using Omnifaces.

See this question for more Information: enter link description here

score 0 · Accepted Answer · answered Feb 23 '17 at 17:21

Here is my final solution, which I ended up using. This has the advantage, that no Files are saved anywhere. I also added the following line:

XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

which will ensure that all formulas are updated. Again, I ommitted all the try/catch/finally.

OPCPackage pkg = OPCPackage.open("existingFile.xlsx");
XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(pkg);

// make your modifications... 
XSSFSheet sheet = wb.getSheetAt(0);
//
XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
pkg.close();
os.close();
Faces.sendFile(os.toByteArray(), "file.xlsx", true);

In order for this to work, it is important to use different file path for Input and for Output.

The last line sends the File to your Browser using Omnifaces.

How can I open an existing *.xlsx file, make modifications, then save as new file (leaving original file untouched?)

3 Answers3