为什么我不能阻止 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“访问被拒绝”的AFileNotFoundException抛出在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)源文件存在，并且有效只读。尝试 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 valuesSheet sheet = workbook.getSheet(dataBean.getSheetName());Row row = sheet.getRow(dataBean.getRowNumber());Cell cell = row.getCell(dataBean.getColumnNumber());cell.setCellValue(dataBean.getValue());“不允许操作，文档以只读模式打开！”。当然设置为只读；我不想写入源，我只想将所有数据发送到新目标。使用 POI 时，我可以设置或更改什么以不改变来源？我们目前的解决方法是创建一个重复的源文件，但这不是一个好的解决方案。

Answer 1

我遇到了同样的问题并通过使用 aFileInputStream而不是 a解决了它File。

Workbook workbook = WorkbookFactory.create(file);

变成：

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

Answer 2

您需要有两本工作簿，一本用于获取（读取）数据，另一本用于写入数据。

伙计，这就是我几个月前的做法，请注意我在第二个工作簿 (hssfWorkbookNew) 上使用 .write()，而不是我用来读取数据的那个，请仔细阅读。此代码仅用于获取 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

我必须处理 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 4

也许你也可以只使用创建签名

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

要求POI以只读方式打开电子表格？