Hi Andy, I'm really sorry to have bothered you!
I became suspicious when I was unable to reproduce the problem in a simpler piece of code. The server side application I'm working on is getting the file and storing it in the session, and then at a later point retreiving it and running the code I attached earlier. I've not entirely understood why I got the wrong cell values but now I'm storing the excel data on disk instead and it works perfectly. The reason that I earlier suspected POI was at fault was because the file (even though it was garbled in some way) was still readable. So no new bug, at least that is something! thanks /Emil -----Original Message----- From: news [mailto:news@;main.gmane.org]On Behalf Of Andrew C. Oliver Sent: den 29 oktober 2002 18:43 To: [EMAIL PROTECTED] Subject: Re: Getting the wrong cellvalue Please attach this to a bug in bugzilla per the instructions here: http://jakarta.apache.org/poi/getinvolved/index.html (see the submitting a patch part) Instead of this source, attach the shortest least complex piece of code necessary to reproduce the problem along with a sheet containing known correct values. Meaning if you KNOW cell 0,0 contains "ROGER" have the code print when it doesn't. -Andy Emil Breding wrote: > 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> -- To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org> For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org>
