CODEWITHSUNDEEP
javaexcelapache-poi
Dhruv Chadha
Dhruv Chadha

Reputation: 1177

Writing to an Excel cell erases all other existing data in that particular row

I have an existing Excel file, in which I need to update some cell data.

I am reading the xlsx file like this:

File file = Paths.get("location/template.xlsx").toFile();
Workbook workbook = WorkbookFactory.create(file);

Then I am updating the cell like this:

Sheet sheet = workbook.getSheet(sheetName);
Row row = sheet.createRow(cellRowNum); //because sheet.getRow(cellRowNum) was returning null.
Cell cell = row.createCell(cellColumnNum);
cell.setCellValue(value);

So now, the value is correctly visible on the cell, but all other existing data in that row is erased/overwritten.

Please help me figure this out? I need to use Apache POI only.

Upvotes: 0

Views: 650

Answers (2)

Dhruv Chadha
Dhruv Chadha

Reputation: 1177

Correct Answer -> I wasn't aware that POI will return null on empty rows, but will return the row when there is some value. It looks obvious, but there are some many workbooks (SXSSF, XSSF, HSSF etc), that I got confused. Thanks for the help anyway.

Upvotes: 0

magicmn
magicmn

Reputation: 1914

Looking at the documentation we have 3 methods. (https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Row.html)

  • Cell createCell(int column) Use this to create new cells within the row and return it.
  • Cell getCell(int cellnum) Get the cell representing a given column (logical cell) 0-based. If you ask for a cell that is not defined....you get a null.
  • Cell getCell(int cellnum, Row.MissingCellPolicy policy) Returns the cell at the given (0 based) index, with the specified Row.MissingCellPolicy

The last one combines the best of both.

Upvotes: 1

Related Questions