JoshDM
JoshDM

Reputation: 5072

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

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.

Upvotes: 3

Views: 1727

Answers (4)

beaudet
beaudet

Reputation: 948

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?

Upvotes: 0

JoshDM
JoshDM

Reputation: 5072

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) {}
    }
}

Upvotes: 1

Bastien Jansen
Bastien Jansen

Reputation: 8846

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));

Upvotes: 7

BugsForBreakfast
BugsForBreakfast

Reputation: 815

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);
            }
        }
    }

Upvotes: 2

Related Questions