Ashwini Arunachalam Ashwini Arunachalam - 3 months ago 22
Java Question

An Exception occurs when writing to the xlsx document in iteration using apache poi 3.9

enter image description hereAm trying to update the values in excel during iterations.
For example say value of Cell5 ,i'm trying to update it over iteration.
It worked fine for

.xls(HSSFWorkbook)


But when trying to do the same with
XSSFWorkbook(.xlsx)


But following exception occurs:


org.apache.xmlbeans.impl.values.XmlValueDisconnectedException


Referencing to previous post on this exception i tried to include this piece of code

FileOutputStream out= new FileOutputStream(new File("E:\\Ash\\poi\\res.xlsx"));
workbook.write(out);
out.close();
workbook = new XSSFWorkbook(new FileInputStream("E:\\Ash\\poi\\res.xlsx"));


including exception is not occuring but,only one iteration data is getting updated in the excel,set of them are blank.

This is my code:
public class poiTestxlsx {

public static void main(String[] args) throws IOException {
String excelFilePath = "E:\\Ash\\poi\\poiread.xlsx";
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0);
int rowCnt=firstSheet.getLastRowNum();

for (int i = 1; i <=rowCnt ; i++) {
Row r = firstSheet.getRow(i);
int res=i;
Cell cell=null;
if(cell==null){
cell=r.createCell(5);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(res);
}
FileOutputStream out= new FileOutputStream(new File("E:\\Ash\\poi\\res.xlsx"));
workbook.write(out);
out.close();
workbook = new XSSFWorkbook(new FileInputStream("E:\\Ash\\poi\\res.xlsx"));
}
System.out.println("done");
}
}


The same code worked for
HSSFWorkbook
, the result sheet had iterated values been updated.
Please let me know how to proceed.

Thanks

Answer

Move the saving part outside your for loop and you're all set :)

I took your code and altered it a bit, not much though. Just changed from sheet to to XSSFSheet and row to XSSFrow for consistancy. Then moved the saving part outside of for loop. looks like this.

    public static void main(String[] args) throws IOException{
    String excelFilePath = "C:\\Users\\gotpist1\\Desktop\\SRBNOI.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    XSSFSheet firstSheet = workbook.getSheetAt(0);
    int rowCnt = firstSheet.getLastRowNum();
    inputStream.close();
    for(int i = 1; i <= rowCnt; i++){
        inputStream = new FileInputStream(new File(excelFilePath));
        workbook = new XSSFWorkbook(inputStream);
        firstSheet = workbook.getSheetAt(0);
        XSSFRow r = firstSheet.getRow(i);
        int res = i;
        Cell cell = null;
        if(cell == null){
            cell = r.createCell(5);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(res);

        }
        FileOutputStream out = new FileOutputStream(new File("C:\\Users\\gotpist1\\Desktop\\SRBNOI.xlsx"));
        workbook.write(out);
        out.close();


    }
    inputStream.close();
    System.out.println("done");
}