Reputation: 5072
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
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
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
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
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