CODEWITHSUNDEEP
javaselenium-webdriverapache-poi
Ashwini Arunachalam
Ashwini Arunachalam

Reputation: 57

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

Upvotes: 0

Views: 2893

Answers (1)

SwissArmyKnife
SwissArmyKnife

Reputation: 210

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. /** * The ExcelTest class */

public class ExcelTest{

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();
    try{
        for(int i = 1; i <= rowCnt; i++){
            if(i == 3)
                break;
            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("f" + res);
            }
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        FileOutputStream out = new FileOutputStream(new File("C:\\Users\\gotpist1\\Desktop\\SRBNOI.xlsx"));
        workbook.write(out);
        out.close();
        inputStream.close();
        System.out.println("done");
    }

}

}

Output

Upvotes: 2

Related Questions