为什么我不能阻止Apache POI修改源文件？

Question

我使用Apache POI工作簿在Java中打开一个Excel文件（源代码），更改某一组单元格中的数据，将工作簿保存到一个单独的文件中，然后关闭工作簿（因为文档中规定关闭工作簿，即使它是只读的）。

POI每次都会更改源Excel文件中的数据。根据POI文档中的建议，我尝试了几种不同的方法来防止这种情况，但这些方法都失败了。

这里有两种尝试在理论上应该有效，但没有。

尝试1-将源文件设置为只读

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

file.setReadOnly();

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

在WorkbookFactory中抛出“访问被拒绝”的FileNotFoundException。创建（文件）：

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)

源文件存在，并且是有效的只读文件。

尝试2-使用允许显式设置只读的POI API构造函数

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

在写调用期间抛出一个InvalidSwitationExctive：

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)

"操作不允许，文档以只读模式打开！"。当然它被设置为只读；我不想要源写入，我只想让所有数据都去一个新目标。

在使用POI时，我可以设置或更改什么以不改变源？

我们目前的解决方法是创建一个重复的源文件，但这不是一个好的解决方案。

Answer 1

我必须处理XSSF和HSSF；这就是它是如何实现的：

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

Answer 2

你需要有两本工作手册，一本是你从中获取数据（阅读）的，另一本是你写的。

听着，伙计，几个月前我就是这样做的，请注意我使用了。在第二个工作簿（hssfWorkbookNew）上写（），不是我用来读取数据的工作簿，请仔细阅读。此代码仅用于获取XLS excel的第一张工作表并将其复制到新文件中。

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

Answer 3

我有同样的问题，并通过使用FileInputStream而不是File来解决它。

Workbook workbook = WorkbookFactory.create(file);

变成：

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