Bug 56635 - Wrong getLastCellNum returns wrong result when deleting cells in Excel
Summary: Wrong getLastCellNum returns wrong result when deleting cells in Excel
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-06-17 14:14 UTC by Simon
Modified: 2014-06-18 05:37 UTC (History)
0 users

xls file to examine (60.00 KB, application/vnd.ms-excel)
2014-06-17 14:14 UTC, Simon
Test Application (1.27 KB, text/plain)
2014-06-17 14:15 UTC, Simon

Note You need to log in before you can comment on or make changes to this bug.
Description Simon 2014-06-17 14:14:17 UTC
Created attachment 31722 [details]
xls file to examine

The method getLastCellNum for (excel) row 20 returns the correct value (9). Removing the cell 20:j, shifting cells left, the method getLastCellNum returns 31 instead of 1

Use the following code to test:

import java.io.File;
import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


 * @author simonbue
public class CellCount {

	public CellCount(){
		File excelFile = new File("c:/temp/ETH_template.xls");
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(excelFile);
		} catch (InvalidFormatException e) {
			// TODO Auto-generated catch block
		} catch (IOException e) {
			// TODO Auto-generated catch block
		Sheet sheet = wb.getSheet("samplelist");
		Row row = sheet.getRow(19);
		 short minColIx = row.getFirstCellNum();
		 short maxColIx = row.getLastCellNum();
		 for(short colIx=minColIx; colIx<maxColIx; colIx++) {
		   Cell cell = row.getCell(colIx);
		   if(cell == null) {
			 System.out.println("Cell " + colIx + " is null");
			 System.out.println("Cell type " + colIx + " is " + cell.getCellType());		   
	 * @param args
	public static void main (String[] args) 
		new CellCount();


Comment 1 Simon 2014-06-17 14:15:56 UTC
Created attachment 31723 [details]
Test Application
Comment 2 Nick Burch 2014-06-17 21:01:11 UTC
POI just reports the value that Excel has stored in the file, Excel doesn't always do the "sensible" thing especially when it comes to styled but un-used cells. Not really a lot that POI can do if Excel puts slightly duff info in the file, sorry
Comment 3 Simon 2014-06-18 05:37:06 UTC
So this means that I have to find my own solution. Might it be possible to mention this in the documentation as I think this can also confuse others.