java.lang.IllegalArgumentException: Attempting to write a row[1] in the range [0,1] that is already written to disk

Question

While Download xlsx using apache poi version 3.15 in ubuntu it is giving me:

java.lang.IllegalArgumentException: Attempting to write a row[1] in the range [0,1] that is already written to disk at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:133),
at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:62)

String fileName = "myDownloads"+".xlsx";
String sourceFolderPath = "/home/user/sampleFile/";
FileInputStream fileInputStream = new 
FileInputStream(sourceFolderPath+"SampleFile.xlsx");
XSSFWorkbook wb_template = new XSSFWorkbook(fileInputStream);
fileInputStream.close();
String destinationFolderPath = "/home/user/downloads";
File dir = new File(destinationFolderPath);
if (!dir.exists()) {
    dir.mkdirs();
}
SXSSFWorkbook workbook = new SXSSFWorkbook(wb_template);
workbook.setCompressTempFiles(true);
SXSSFSheet workSheet = (SXSSFSheet) workbook.getSheetAt(0);
workSheet.setRandomAccessWindowSize(100000);
SXSSFSheet workSheet1 = (SXSSFSheet) workbook.getSheetAt(1);
workSheet1.setRandomAccessWindowSize(100000);
List<Student> studentList = studnetDao.getStudentListByName("kumar");
if(CollectionUtils.isNotEmpty(studentList)) {
    Integer rowIndex = 1;
    for(Student s : studentList) {
        Row row = workSheet.getRow(rowIndex);
        if (row == null) {
            row = workSheet.createRow(rowIndex); 
        }
    }
}

Error is occuring at row = workSheet.createRow(rowIndex) Line — Ankit, May 31 '19 at 08:06
[The SXSSFWorkbook(XSSFWorkbook workbook) API documentation](https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html#SXSSFWorkbook-org.apache.poi.xssf.usermodel.XSSFWorkbook-) clearly states what is not supported using `SXSSFWorkbook`. If there is row index 1 already in the `SampleFile.xlsx` then this row neither can be got not can be new created using `SXSSFWorkbook`. Only **new** rows can be appended. — Axel Richter, May 31 '19 at 10:38

score 1 · Answer 1 · edited Mar 18 '22 at 13:47

The stream mode doesn't support overriding or accessing existing rows. you're using a template to create your workbook that write the second row automatically.

To solve this, you can use simple XSSWorkbook to load the template and remove the existing ones; then switch to stream mode.

Your code will be like this:

// -- create XSSFWorkbook from the template
XSSFWorkbook xssfworkbook = new XSSFWorkbook(wb_template);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(1);
xssfSheet.removeRow(xssfSheet.getRow(1));
// -- after removing the first row; switch to stream mode. now we can start from index=1
SXSSFWorkbook workbook = new SXSSFWorkbook(xssfworkbook);
workbook.setCompressTempFiles(true);
SXSSFSheet workSheet = (SXSSFSheet) workbook.getSheetAt(0);
workSheet.setRandomAccessWindowSize(100000);
SXSSFSheet workSheet1 = (SXSSFSheet) workbook.getSheetAt(1);
workSheet1.setRandomAccessWindowSize(100000);
List<Student> studentList = studnetDao.getStudentListByName("kumar");
if(CollectionUtils.isNotEmpty(studentList)) {
    Integer rowIndex = 1;
    for(Student s : studentList) {
        Row row = workSheet.getRow(rowIndex);
        if (row == null) {
            row = workSheet.createRow(rowIndex); 
        }
    }
}

score 0 · Answer 2 · answered May 11 '23 at 14:31

In my case this issue was related to the SXSSFWorkbook.DEFAULT_WINDOW_SIZE which is 100. I increased the value to 120 and this solved my problem.

The new workbook creation now looks like this.

SXSSFWorkbook workbook = new SXSSFWorkbook(null, 120);

This increases the amount of rows which are cached before being flushed to the disk. I hope this helps somehow.

java.lang.IllegalArgumentException: Attempting to write a row[1] in the range [0,1] that is already written to disk

2 Answers2