Why can't I prevent Apache POI from altering the source file?

Question

I am opening an Excel file (the source) in Java using an Apache POI Workbook, altering data in a certain set of cells, saving the Workbook to a separate file, then closing the Workbook (because the documentation states to close the Workbook, even if it is read-only).

POI alters the data in the source Excel file every time. I have tried a few different methods to prevent this according to recommendations from the POI documentation, but these methods fail.

Here are two attempts that should work in theory, but do not.

Attempt 1 - set the source file to read only

File file = new File("{path-to-existing-source-file}");

file.setReadOnly();

Workbook workbook = WorkbookFactory.create(file); // throws a FileNotFoundException

A FileNotFoundException for "Access is denied" is thrown at WorkbookFactory.create(file):

java.io.FileNotFoundException: {path-to-source-file-that-exists} (Access is denied)
at java.io.RandomAccessFile.open0(Native Method)
at java.io.RandomAccessFile.open(RandomAccessFile.java:316)
at java.io.RandomAccessFile.<init>(RandomAccessFile.java:243)
at org.apache.poi.poifs.nio.FileBackedDataSource.newSrcFile(FileBackedDataSource.java:158)
at org.apache.poi.poifs.nio.FileBackedDataSource.<init>(FileBackedDataSource.java:60)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:224)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:172)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:298)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:271)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:252)
at com.stackoverflow.MyClass(MyClass.java:71)

The source file exists, and it is validly read-only.

Attempt 2 - use the POI API constructor which allows read-only to be explicitly set

File file = new File("{path-to-existing-source-file}");
Workbook workbook = WorkbookFactory.create(file, null, true);  // true is read-only

// dataBean is just a container bean with the appropriate reference values
Sheet sheet = workbook.getSheet(dataBean.getSheetName());
Row row = sheet.getRow(dataBean.getRowNumber());
Cell cell = row.getCell(dataBean.getColumnNumber());
cell.setCellValue(dataBean.getValue());

// target is another File reference
OutputStream outStream = new FileOutputStream(new File("path-to-target-file"));
workbook.write(outStream);   // throws InvalidOperationException

An InvalidOperationException is thrown during the write call:

Caused by: org.apache.poi.openxml4j.exceptions.InvalidOperationException: 
Operation not allowed, document open in read only mode!
at org.apache.poi.openxml4j.opc.OPCPackage.throwExceptionIfReadOnly(OPCPackage.java:551)
at org.apache.poi.openxml4j.opc.OPCPackage.removePart(OPCPackage.java:955)
at org.apache.poi.openxml4j.opc.PackagePart.getOutputStream(PackagePart.java:531)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.commit(XSSFWorkbook.java:1770)
at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:463)
at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:236)
at com.stackoverflow.MyClass(MyClass.java:90)

"Operation not allowed, document open in read only mode!". Of course it is set to read-only; I don't want the source written to, I just want all the data to go to a new target.

What can I set or change to not alter the source when using POI?

Our current workaround is to create a duplicate source file, but that is not a good solution.

You need to open a second workbook and write to that one mate, I did something like that a few months ago I will check — BugsForBreakfast, Jun 28 '19 at 15:57
What do you mean by that; create a second workbook using all the data from the original as a source, and then manipulate the second workbook? Can you provide a code sample? I'll see if I can find anything online regarding it. — JoshDM, Jun 28 '19 at 15:58
The usage of `File` for creating `XSSFWorkbook` has that disadvantages. This is known issue. See https://stackoverflow.com/questions/55929312/apache-poi-appending-data-to-xlsx-file-when-task-ran-twice/55937539#55937539 and https://stackoverflow.com/questions/46146161/apache-poi-fileinputstream-works-file-object-fails-nullpointerexception/46149469#46149469 and https://stackoverflow.com/questions/45708114/why-we-need-to-create-a-workbook-before-reading-the-excel-file-using-apache-poi/45709920#45709920. Do using `FileInputStream` instead of `File` for creating the `XSSFWorkbook`. — Axel Richter, Jun 28 '19 at 15:59
@JoshDM yes, create a second workbout which is the one that is gonna call .write(), the other one is just for reading brother, check out my answer carefully I commented — BugsForBreakfast, Jun 28 '19 at 16:48
@AxelRichter - I was informed that the FileInputStream process was attempted and it altered the original; however when we retried it, wrapped in a BufferedInputStream, it succeeded and will be our code going forward. — JoshDM, Jun 28 '19 at 18:13

score 6 · Answer 1 · answered Feb 03 '20 at 15:17

6

I had the same problem and solved it by using a FileInputStream instead of a File.

Workbook workbook = WorkbookFactory.create(file);

becomes:

Workbook workbook = WorkbookFactory.create(new FileInputStream(file));

answered Feb 03 '20 at 15:17

Bastien Jansen

8,756
2
35
53

This is the only answser that works for me. – Bohao LI Dec 29 '22 at 21:42

BugsForBreakfast · Answer 2 · 2019-06-28T16:56:54.893

You need to have two workbooks, one where you get the datafrom (read) and another one you write to.

Look man, this is how I did a few months ago, please notice that I use .write() on the second workbook (hssfWorkbookNew), not the one im using to read the data from, read it carefully. This code is just for getting first sheet of a XLS excel and copying it to a new file.

// this method generates a new excelFile based on the excelFile he receives

public void generarXLS(File excelFile, File excelNewFile) {
        InputStream excelStream = null;
        OutputStream excelNewOutputStream = null;
        try {
            excelStream = new FileInputStream(excelFile);
            excelNewOutputStream = new FileOutputStream(excelNewFile);
            // Representation of highest level of excel sheet.
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(excelStream);
            HSSFWorkbook hssfWorkbookNew = new HSSFWorkbook();

            // Chose the sheet that we pass as parameter.
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

            // Create new sheet we are gonna use.
            HSSFSheet hssfSheetNew = hssfWorkbookNew.createSheet("Copy-Copia");

            // Create new sheet where we will copy the data

            // Object that allow us to read a row from the sheet and extract the data from the cells
            HSSFRow hssfRow;
            HSSFRow hssfRowNew; // for hssfSheetNew
            // Initialize the object that reads value of cell
            HSSFCell cellNew;
            // Get number of rows of the sheet
            int rows = hssfSheet.getLastRowNum();
            String cellValue;

            // Style of the cell border, color background and pattern (fill pattern) used.
            CellStyle style = hssfWorkbookNew.createCellStyle();
            // Definition of the font of the cell.

            // Iterate trhough all rows to get the cells and copy them to the new sheet
            for (Row row : hssfSheet) {
                hssfRowNew = hssfSheetNew.createRow(row.getRowNum());

                if (row.getRowNum() > 999999) {
                    break;
                }

                for (Cell cell : row) {

                    cellValue = (cell.getCellType() == CellType.STRING) ? cell.getStringCellValue()
                            : (cell.getCellType() == CellType.NUMERIC) ? "" + cell.getNumericCellValue()
                                    : (cell.getCellType() == CellType.BOOLEAN) ? "" + cell.getBooleanCellValue()
                                            : (cell.getCellType() == CellType.BLANK) ? ""
                                                    : (cell.getCellType() == CellType.FORMULA) ? "FORMULA"
                                                            : (cell.getCellType() == CellType.ERROR) ? "ERROR" : "";

                    cellNew = hssfRowNew.createCell(cell.getColumnIndex(), CellType.STRING);
                    cellNew.setCellValue(cellValue);

                }
            }
            // NOTICE how I write to the new workbook
            hssfWorkbookNew.write(excelNewOutputStream);
            hssfWorkbook.close();
            hssfWorkbookNew.close();
            excelNewOutputStream.close();

            JOptionPane.showMessageDialog(null, Constantes.MSG_EXITO, "Informacion", 1);

        } catch (FileNotFoundException fileNotFoundException) {
            JOptionPane.showMessageDialog(null, "file not found", "Error", 0);

        } catch (IOException ex) {
            JOptionPane.showMessageDialog(null, "Error processing the file", "Error", 0);

        } finally {
            try {
                excelStream.close();
            } catch (IOException ex) {
                System.out.println("Error processing the file after closing it): " + ex);
            }
        }
    }

Notice that I use one HSSFWorkbook for reading and another one for writing, you have to do like that — BugsForBreakfast, Jun 28 '19 at 16:04
I did something similar, but a lot shorter: ByteArrayOutputStream os = new ByteArrayOutputStream(4096); workbook.write(os); workbook.close(); workbook = WorkbookFactory.create(new ByteArrayInputStream(os.toByteArray())); However, we'll be using the BufferedInputStream create call suggested in another comment to solve this. — JoshDM, Jun 28 '19 at 18:15

JoshDM · Accepted Answer · 2020-02-05T03:21:43.983

I had to handle XSSF and HSSF; this is how it was accomplished:

void handle(File inFile, File outFile) throws IOException {    

    Workbook workbook = WorkbookFactory.create(inFile);
    workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);  // LINE NOT REQUIRED

    if (workbook instanceof XSSFWorkbook) {

        handleXSSF((XSSFWorkbook) workbook, outFile);

    } else if (workbook instanceof HSSFWorkbook) {

        handleHSSF((HSSFWorkbook) workbook, outFile);

    } else {

        throw new IOException("Unrecognized Workbook Type " + workbook.getClass().getName());
    }
}

void handleHSSF(HSSFWorkbook hWorkbook, File outFile) throws IOException {

    FileOutputStream fos = null;

    try {

        fos = new FileOutputStream(outFile);    
        hWorkbook.write(fos);
        fos.close();

    } finally {

        try { 

            hWorkbook.close();

        } catch (Exception ignore) {}
    }
}

void handleXSSF(XSSFWorkbook xWorkbook, File outFile) throws IOException {

    SXSSFWorkbook sWorkbook = new SXSSFWorkbook(xWorkbook, 100);

    FileOutputStream fos = null;

    try {

        fos = new FileOutputStream(outFile);    
        sWorkbook.write(fos);
        fos.close();

    } finally {

        try { 

            sWorkbook.close();

        } catch (Exception ignore) {}

        try { 

            sWorkbook.dispose();

        } catch (Exception ignore) {}

        try { 

            xWorkbook.close();

        } catch (Exception ignore) {}
    }
}

score 0 · Answer 4 · answered Mar 06 '20 at 17:00

0

Perhaps you could also just use the create signature

Workbook workbook = WorkbookFactory.create(new File("//server/path/file.ext"), null, true);

to ask POI to open the spreadsheet read only?

answered Mar 06 '20 at 17:00

beaudet

886
1
10
13

Why can't I prevent Apache POI from altering the source file?

4 Answers4