CODEWITHSUNDEEP
javaexcelapache-poi
Timmus-1
Timmus-1

Reputation: 5

POI XSSF overwriting specific cells in excel file

I am trying to write out to an existing excel file. I don't want to create new rows or cells, I just want to write out the value from my array into the value at row x column y. Every time I have tried this so far I can only get it to work if I create a new row. Please help!!!

      Integer columns = DataImport.columns_in_sheet[0];
        Integer rowNum = learnerRow + 2;

        try {
            FileInputStream inp = new FileInputStream("D:/location/update.xlsx");
            XSSFWorkbook wb = null;
            wb = (XSSFWorkbook) WorkbookFactory.create(inp);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row = sheet.getRow(18);//places the start row
            XSSFCell cell = null;//places the start column
            cell = row.getCell(0);

//#########################################################################################

//#########################################################################################
            for (int j = 0; j < exportData.length; j++) {
                //sheet.createRow(rowNum+j);
                //row = sheet.getRow(rowNum+j);

                //row = sheet.getRow(rowNum+j);
                for (int i=0; i < columns;i++){

                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(exportData[j][i]);
                }
            }

            // Write the output to a file
            FileOutputStream fileOut = new FileOutputStream("D:/location/update.xlsx");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

this code throws a null pointer because of row being null, I can only seem to get rid of the error by creating new rows. I am using XSSF formatting.

Upvotes: 0

Views: 629

Answers (1)

Axel Richter
Axel Richter

Reputation: 61945

The logic of your code snippet is not clear. It looks not logically to me.

But to avoid NPE while using rows and cells from present sheets, one always needs check whether the row or cell was present already or needs to be new created. This is necessary because for not present rows Sheet.getRow will return null. Also Row.getCell will return null for not present cells.

So we can do:

Sheet sheet = ...
Row row = sheet.getRow(rowIdx); if (row == null) row = sheet.createRow(rowIdx); 
Cell cell = row.getCell(cellIdx); if (cell == null) cell = row.createCell(cellIdx);

Now row either is a row which was already present or it is a new created row. And cell either is a cell which was already present or it is a new created cell. Neither row nor cell will be null. And at first present rows/cells will be got before they were new created if not present. So present rows and cells will not be destroyed.

The same is needed in loops:

Sheet sheet = ...
Row row;
Cell cell;
for (int rowIdx = 0; rowIdx < 10; rowIdx++) {
 row = sheet.getRow(rowIdx); if (row == null) row = sheet.createRow(rowIdx);
 for (int cellIdx = 0; cellIdx < 10; cellIdx++) {
  cell = row.getCell(cellIdx); if (cell == null) cell = row.createCell(cellIdx);
  // do something with cell
 }
}

Upvotes: 1

Related Questions