为什么我不能阻止 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 values

Sheet 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&nbsp;workbook&nbsp;=&nbsp;WorkbookFactory.create(file);变成：Workbook&nbsp;workbook&nbsp;=&nbsp;WorkbookFactory.create(new&nbsp;FileInputStream(file));

Answer 2

您需要有两本工作簿，一本用于获取（读取）数据，另一本用于写入数据。伙计，这就是我几个月前的做法，请注意我在第二个工作簿 (hssfWorkbookNew) 上使用 .write()，而不是我用来读取数据的那个，请仔细阅读。此代码仅用于获取 XLS excel 的第一张纸并将其复制到新文件。// this method generates a new excelFile based on the excelFile he receivespublic void generarXLS(File excelFile, File excelNewFile) {&nbsp; &nbsp; &nbsp; &nbsp; InputStream excelStream = null;&nbsp; &nbsp; &nbsp; &nbsp; OutputStream excelNewOutputStream = null;&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; excelStream = new FileInputStream(excelFile);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; excelNewOutputStream = new FileOutputStream(excelNewFile);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Representation of highest level of excel sheet.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HSSFWorkbook hssfWorkbook = new HSSFWorkbook(excelStream);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HSSFWorkbook hssfWorkbookNew = new HSSFWorkbook();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Chose the sheet that we pass as parameter.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Create new sheet we are gonna use.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HSSFSheet hssfSheetNew = hssfWorkbookNew.createSheet("Copy-Copia");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Create new sheet where we will copy the data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Object that allow us to read a row from the sheet and extract the data from the cells&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HSSFRow hssfRow;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HSSFRow hssfRowNew; // for hssfSheetNew&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Initialize the object that reads value of cell&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HSSFCell cellNew;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Get number of rows of the sheet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int rows = hssfSheet.getLastRowNum();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String cellValue;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Style of the cell border, color background and pattern (fill pattern) used.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CellStyle style = hssfWorkbookNew.createCellStyle();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Definition of the font of the cell.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Iterate trhough all rows to get the cells and copy them to the new sheet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (Row row : hssfSheet) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; hssfRowNew = hssfSheetNew.createRow(row.getRowNum());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (row.getRowNum() > 999999) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (Cell cell : row) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cellValue = (cell.getCellType() == CellType.STRING) ? cell.getStringCellValue()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : (cell.getCellType() == CellType.NUMERIC) ? "" + cell.getNumericCellValue()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : (cell.getCellType() == CellType.BOOLEAN) ? "" + cell.getBooleanCellValue()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : (cell.getCellType() == CellType.BLANK) ? ""&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : (cell.getCellType() == CellType.FORMULA) ? "FORMULA"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : (cell.getCellType() == CellType.ERROR) ? "ERROR" : "";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cellNew = hssfRowNew.createCell(cell.getColumnIndex(), CellType.STRING);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cellNew.setCellValue(cellValue);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // NOTICE how I write to the new workbook&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; hssfWorkbookNew.write(excelNewOutputStream);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; hssfWorkbook.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; hssfWorkbookNew.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; excelNewOutputStream.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOptionPane.showMessageDialog(null, Constantes.MSG_EXITO, "Informacion", 1);&nbsp; &nbsp; &nbsp; &nbsp; } catch (FileNotFoundException fileNotFoundException) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOptionPane.showMessageDialog(null, "file not found", "Error", 0);&nbsp; &nbsp; &nbsp; &nbsp; } catch (IOException ex) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOptionPane.showMessageDialog(null, "Error processing the file", "Error", 0);&nbsp; &nbsp; &nbsp; &nbsp; } finally {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; excelStream.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } catch (IOException ex) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Error processing the file after closing it): " + ex);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }

Answer 3

我必须处理 XSSF 和 HSSF；这是它是如何完成的：void handle(File inFile, File outFile) throws IOException {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; Workbook workbook = WorkbookFactory.create(inFile);&nbsp; &nbsp; workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);&nbsp; // LINE NOT REQUIRED&nbsp; &nbsp; if (workbook instanceof XSSFWorkbook) {&nbsp; &nbsp; &nbsp; &nbsp; handleXSSF((XSSFWorkbook) workbook, outFile);&nbsp; &nbsp; } else if (workbook instanceof HSSFWorkbook) {&nbsp; &nbsp; &nbsp; &nbsp; handleHSSF((HSSFWorkbook) workbook, outFile);&nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; throw new IOException("Unrecognized Workbook Type " + workbook.getClass().getName());&nbsp; &nbsp; }}void handleHSSF(HSSFWorkbook hWorkbook, File outFile) throws IOException {&nbsp; &nbsp; FileOutputStream fos = null;&nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; fos = new FileOutputStream(outFile);&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; hWorkbook.write(fos);&nbsp; &nbsp; &nbsp; &nbsp; fos.close();&nbsp; &nbsp; } finally {&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; hWorkbook.close();&nbsp; &nbsp; &nbsp; &nbsp; } catch (Exception ignore) {}&nbsp; &nbsp; }}void handleXSSF(XSSFWorkbook xWorkbook, File outFile) throws IOException {&nbsp; &nbsp; SXSSFWorkbook sWorkbook = new SXSSFWorkbook(xWorkbook, 100);&nbsp; &nbsp; FileOutputStream fos = null;&nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; fos = new FileOutputStream(outFile);&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; sWorkbook.write(fos);&nbsp; &nbsp; &nbsp; &nbsp; fos.close();&nbsp; &nbsp; } finally {&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sWorkbook.close();&nbsp; &nbsp; &nbsp; &nbsp; } catch (Exception ignore) {}&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sWorkbook.dispose();&nbsp; &nbsp; &nbsp; &nbsp; } catch (Exception ignore) {}&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xWorkbook.close();&nbsp; &nbsp; &nbsp; &nbsp; } catch (Exception ignore) {}&nbsp; &nbsp; }}

Answer 4

也许你也可以只使用创建签名Workbook workbook = WorkbookFactory.create(new File("//server/path/file.ext"), null, true);要求POI以只读方式打开电子表格？