bc14
Reputation: 39
Write to excel using Apache POI. FileNotFoundException:(The requested operation cannot be performed on a file with a user-mapped section open)
Can anyone help shine any light into this? I am trying to write an array of data to excel to the next empty line from a selenium test script. My code works for generating the excel the first execution, writes the data properly, but then on second execution (in the same application run), and follow-up executions throws the following:
java.io.FileNotFoundException: TestResultData2.xls (The requested operation cannot be performed on a file with a user-mapped section open)
After some hours of attempts and searching I found this may be related to a Windows issue (see: https://bz.apache.org/bugzilla/show_bug.cgi?id=58480) but am not sure if I am missing something remedial.. Most other searches show that this may be caused from not closing the FileOutputStream but it is so I am confused there. Please see the following exportDataToExcel method. Note I have tried many different methods but this is my most recent/most tested attempt.
public static void exportDataToExcel(String fileName, String tabName, String[][] data) throws FileNotFoundException, IOException, EncryptedDocumentException, InvalidFormatException, Exception
{
//Create new workbook and tab
Workbook wb;
File newFile = new File(fileName);
Sheet sheet = null;
if (newFile.exists()) {
// Load existing
wb = WorkbookFactory.create(newFile);
}else {
if (newFile.getName().endsWith(".xls")) {
wb = new HSSFWorkbook();
}else {
throw new IllegalArgumentException("Unknown file type. Please use .xls");
}
}
FileOutputStream fileOut = new FileOutputStream(newFile);
// Check if the workbook is empty or not
if (wb.getNumberOfSheets() != 0) {
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
if (wb.getSheetName(i).equals(tabName)) {
sheet = wb.getSheet(tabName);
}else sheet = wb.createSheet(tabName);
}
}else {
// Create new sheet to the workbook if empty
sheet = wb.createSheet(tabName);
}
//Create 2D Cell Array
Row[] row = new Row[data.length];
Cell[][] cell = new Cell[row.length][];
//Define and Assign Cell Data from Given
for(int i = 0; i < row.length; i ++)
{
row[i] = sheet.createRow(i);
cell[i] = new Cell[data[i].length];
for(int j = 0; j < cell[i].length; j ++)
{
cell[i][j] = row[i].createCell(j);
cell[i][j].setCellValue(data[i][j]);
}
}
//Export Data
wb.write(fileOut);
fileOut.close();
wb.close();
System.out.println("File exported successfully");
}
I am calling the method via:
String data[][]= {{cpAppFN, cpAppLN, cpAppDOB, cpAppSSN}};
exportDataToExcel("TestResultData2.xls", "Results2", data);
Stacktrace below, for reference.
Test_ConsumerPortal Line 210 is FileOutputStream fileOut = new FileOutputStream(newFile); and Line 142 is exportDataToExcel("TestResultData2.xls", "Results2", data); within the test itself.
java.io.FileNotFoundException: TestResultData2.xls (The requested operation cannot be performed on a file with a user-mapped section open)
at java.io.FileOutputStream.open0(Native Method)
at java.io.FileOutputStream.open(Unknown Source)
at java.io.FileOutputStream.<init>(Unknown Source)
at java.io.FileOutputStream.<init>(Unknown Source)
at tests.Test_ConsumerPortal.exportDataToExcel(Test_ConsumerPortal.java:210)
at tests.Test_ConsumerPortal.Execution(Test_ConsumerPortal.java:142)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:124)
at org.testng.internal.Invoker.invokeMethod(Invoker.java:580)
at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:716)
at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:988)
at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)
at org.testng.TestRunner.privateRun(TestRunner.java:648)
at org.testng.TestRunner.run(TestRunner.java:505)
at org.testng.SuiteRunner.runTest(SuiteRunner.java:455)
at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:450)
at org.testng.SuiteRunner.privateRun(SuiteRunner.java:415)
at org.testng.SuiteRunner.run(SuiteRunner.java:364)
at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:84)
at org.testng.TestNG.runSuitesSequentially(TestNG.java:1208)
at org.testng.TestNG.runSuitesLocally(TestNG.java:1137)
at org.testng.TestNG.runSuites(TestNG.java:1049)
at org.testng.TestNG.run(TestNG.java:1017)
at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:114)
at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)
java.io.FileNotFoundException: TestResultData2.xls (The requested operation cannot be performed on a file with a user-mapped section open)
at java.io.FileOutputStream.open0(Native Method)
at java.io.FileOutputStream.open(Unknown Source)
at java.io.FileOutputStream.<init>(Unknown Source)
at java.io.FileOutputStream.<init>(Unknown Source)
at tests.Test_ConsumerPortal.exportDataToExcel(Test_ConsumerPortal.java:210)
at tests.Test_ConsumerPortal.Execution(Test_ConsumerPortal.java:142)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:124)
at org.testng.internal.Invoker.invokeMethod(Invoker.java:580)
at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:716)
at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:988)
at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)
at org.testng.TestRunner.privateRun(TestRunner.java:648)
at org.testng.TestRunner.run(TestRunner.java:505)
at org.testng.SuiteRunner.runTest(SuiteRunner.java:455)
at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:450)
at org.testng.SuiteRunner.privateRun(SuiteRunner.java:415)
at org.testng.SuiteRunner.run(SuiteRunner.java:364)
at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:84)
at org.testng.TestNG.runSuitesSequentially(TestNG.java:1208)
at org.testng.TestNG.runSuitesLocally(TestNG.java:1137)
at org.testng.TestNG.runSuites(TestNG.java:1049)
at org.testng.TestNG.run(TestNG.java:1017)
at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:114)
at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)
If there is any other information needed please let me know.
Upvotes: 3
Views: 1875
Answers (1)
StrikerVillain
Reputation: 3776
When the wb = WorkbookFactory.create(newFile) is called, internally a FileInputStream is created. Without closing the FileInputStream, you are calling calling FileOutputStream in FileOutputStream fileOut = new FileOutputStream(newFile);.
When you are creating the workbook, WorkbookFactory.create(newFile)` does not come into play and so the first attempt passes. It is in subsequent attempts that the mentioned code path is hit and the code breaks.
The solution is to open a FileInputStream if the file exists and then pass it onto create(). After you are done updating the values in the excel sheet, close the FileInputStream and open the FileOutputStream for writing the new data.
Below code should work as is.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ExcelDataUpdater {
public static void exportDataToExcel(String fileName, String tabName, String[][] data)
throws FileNotFoundException, IOException, EncryptedDocumentException, InvalidFormatException, Exception {
// Create new workbook and tab
FileInputStream inputStream = null;
Workbook wb;
Sheet sheet = null;
// Check the file extension
if (!fileName.endsWith(".xls")) {
throw new IllegalArgumentException("Unknown file type. Please use .xls");
}
File newFile = new File(fileName);
// If file not exists we create a new workbook
if (!newFile.exists()) {
wb = new HSSFWorkbook();
} else {
// If file exists, we open an input stream channel to it
inputStream = new FileInputStream(newFile);
// Provide the input stream to WorkbookFactory
wb = WorkbookFactory.create(inputStream);
}
// Check if the workbook is empty or not
boolean isSheetFound = false;
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
if (wb.getSheetName(i).equals(tabName)) {
sheet = wb.getSheet(tabName);
isSheetFound = true;
}
}
if (!isSheetFound) {
sheet = wb.createSheet(tabName);
}
// Create 2D Cell Array
Row[] row = new Row[data.length];
Cell[][] cell = new Cell[row.length][];
// Define and Assign Cell Data from Given
for (int i = 0; i < row.length; i++) {
row[i] = sheet.createRow(i);
cell[i] = new Cell[data[i].length];
for (int j = 0; j < cell[i].length; j++) {
cell[i][j] = row[i].createCell(j);
cell[i][j].setCellValue(data[i][j]);
}
}
// If file already exists, we had opened an input stream.
// We will close this here
if (inputStream != null)
inputStream.close();
FileOutputStream outputStream = new FileOutputStream(newFile);
// Export Data
wb.write(outputStream);
outputStream.close();
wb.close();
System.out.println("File exported successfully");
}
public static void main(String[] args)
throws InvalidFormatException, EncryptedDocumentException, FileNotFoundException, IOException, Exception {
String data1[][] = { { "A", "B", "C", "D" } };
ExcelDataUpdater.exportDataToExcel("TestResultData2.xls", "Results1", data1);
String data2[][] = { { "X", "Y", "Z", "W" } };
ExcelDataUpdater.exportDataToExcel("TestResultData2.xls", "Results2", data2);
}
}
I have also optimized the code for checking whether the sheet exists and creating sheet.
Upvotes: 2
Related Questions
- POI - Cannot write to file while it is opened in Excel?
- Apache POI: Writing into excel with Java: opening Workbook, java.io.IOException: Cannot write data, document seems to have been closed already
- I want to write data into excel(.xlsx file) using Apache poi
- NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file
- Trouble writing to Excel file (Apache POI)
- Unable to write to an excel (Selenium WebDriver) - Apache POI
- Unable to write in the Excel file using Java and Apache POI
- Not able to write to excel at run time using POI
- poi cannot write data into .xlsx
- How to write in excel using java apche POI :showing java.io.IOException: Stream Closed error