Hi,
I'm using POI to extract data from an Excel sheet to insert into a database.
During the process of extraction it seems that row.getCell((short)i); gives
me the wrong cell sometimes.
So far I've only been able to reproduce it with one Excel-file but no matter
what I do with it the problem reamains. (I've tried copying just the
cellvalues to a new Excel file, tried changing the cells that seem to cause
the problem etc).
In the Excel file I'm testing on the same cellvalue is repeated 5 times in
totaly different locations. If I make a change in the cell that is replaced
and then resave the Excelfile the problem just moves to another cell! The
Excel file has 62 rows and 12 columns. 20% of the cells are EMPTY.
During testing I've just put the current column number, row number, value
and the cells .getCellNum() to System.out to check that the cellNum
coincides with the column and it does. But the value is not correct. No
exceptions are thrown.
Has anyone else seen anything like what's happening to me?
Thanks for any suggestions and help!
/Emil Breding
Below is the main part of the code that extracts the data (I can send the
Excel file to if needed):
public VirtualExcelFile getVirtualExcelObject(java.io.InputStream is)
{
Hashtable values = new Hashtable();
String[] columns;
try
{
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
int noofRows = sheet.getLastRowNum();
LOG.debug("noof rows: " + noofRows);
// Get the top row containing column names
HSSFRow row = sheet.getRow((short)0);
short lastCellNo = row.getLastCellNum();
columns = new String[lastCellNo];
Hashtable nameHolder = new Hashtable();
for (int i = 0; i < lastCellNo; i++)
{
LOG.debug("Adding column: " + i);
HSSFCell cell = row.getCell((short)i);
String columnNameOrg = cell.getStringCellValue();
String columnName = columnNameOrg;
int counter = 1;
while (nameHolder.containsKey(columnName))
{
LOG.debug("Found duplicate name: " + columnName);
LOG.debug("Trying index: " + counter);
columnName = columnNameOrg + "_" + counter;
counter++;
}
nameHolder.put(columnName, columnName);
columns[i] = columnName;
LOG.debug("Column name: " + columns[i]);
}
// Initiales the hashtable with arrays
for (int i = 0; i < lastCellNo; i++)
{
values.put(columns[i], new String[noofRows]);
}
// Get the rest of the row values
for (int r = 1; r <= noofRows; r++)
{
//LOG.debug("Row: " + r);
row = sheet.getRow((short)r);
for (int i = 0; i < lastCellNo; i++)
{
//LOG.debug("\tCol: " + i);
HSSFCell cell = row.getCell((short)i);
short cellNum = (short)0;
// Some excel files seem to have null cells
// Can't have that!
String value = "";
if (cell != null)
{
int cellType = cell.getCellType();
cellNum = cell.getCellNum();
if (cellType == HSSFCell.CELL_TYPE_STRING)
value = cell.getStringCellValue();
else if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
{
double val = cell.getNumericCellValue();
// If integer strip anyting after the .
double valFloor = Math.floor(val);
if ( (val - valFloor) > 0 )
value = val + "";
else
value = (int)valFloor + "";
}
else
{
LOG.warn("Cell is neither String or numeric, skipped it!");
LOG.warn("The cell is: row: " + r + " col: " + i);
}
}
else
{
LOG.warn("Cell is NULL, skipped it!");
LOG.warn("The cell is: row: " + r + " col: " + i);
}
// Add the value to the hashtable
String[] col = (String[])values.get(columns[i]);
col[r-1] = value;
System.out.println("(" + i + ", " + (r-1) + ") " + value + "
cellnum: " + cellNum);
}
}
}
catch (Exception e)
{
LOG.error("Exception : " + e.getMessage(), e);
return null;
}
VirtualExcelFile vExcel = new VirtualExcelFile(values);
return vExcel;
}
--
To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org>
