You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by ap...@charter.net on 2008/09/18 19:21:40 UTC
Fwd: Deprecate HSSFWorkbook.getSSTString(int) ?
I sent this email about a year ago when I noticed that HSSFWorkbook.getSSTString(int) was deprecated. I got no responce so I thought I would give it a shot , again.
My problem still exists in that going through the rows and cells using HSSFRow and HSSFCell do not always return all the data in the spreadsheet. Data that is only in the SST can be displayed in Excel but not be accessible by the preferred method of accessing the data.
Can anyone offer advice on this?
> Date: Thu, 16 Aug 2007 9:38:48 -0400
> From: <ap...@charter.net>
> To: user@poi.apache.org
> Subject: Deprecate HSSFWorkbook.getSSTString(int) ?
>
> Why was HSSFWorkbook.getSSTString(int) deprecated? I parse spreadsheets that are uploaded via a web application and there are times I get spreadsheets that look like they have data but when the normal methods of getting data out of the cells are used, it does not return any data. The only way I can get the data out of the spreadsheet is to use the getSSTString method. When I saw it was deprecated in 3.0, I was hopeful that the issue was corrected, but I just tried it and I am still required to use getSSTString to get the data.
>
> A code snippet form the code I use to get the data from the spreadsheet is as follows and I have attached an example spreadsheet that has this issue. Note that if you open it and save it, it will not have the issue anymore. If you are going to try it out, just save it directly from the email.
>
> Thanks for any help. If more information is needed, I will be happy to provide it.
>
> int rowCnt = sheet.getLastRowNum();
> for (int rowNum = 0; rowNum <= rowCnt; rowNum++)
> {
> boolean isBlankRow = true;
> HSSFRow row = sheet.getRow(rowNum);
> if (row == null)
> {
> System.out.println("Row " + rowNum + " was NULL.");
> continue;
> }
>
> Collection errors = new ArrayList();
> Object voObject = null;
> try
> {
> voObject = voClass.newInstance();
> } catch (IllegalAccessException ex)
> {
> ex.printStackTrace();
> throw new RwtException(ExceptionConstants.GENERAL_EX,
> RwtException.SYSTEM_EXCEPTION,
> RwtException.FATAL_ERROR, ex);
> } catch (InstantiationException ex)
> {
> ex.printStackTrace();
> throw new RwtException(ExceptionConstants.GENERAL_EX,
> RwtException.SYSTEM_EXCEPTION,
> RwtException.FATAL_ERROR, ex);
> }
>
> int cellCnt = row.getLastCellNum();
> Collection mapColumns = fileMap.getColumns();
> if (hasHeader && rowNum == 0)
> {
> System.out.println("Validating the header!!!");
> Collection headerErrors = validateHeader(row, mapColumns,
> file.getFileName(), rowNum);
> if (headerErrors.size() > 0)
> {
> System.out.println("Found error in header.");
> throw new InvalidHeaderException(headerErrors);
> }
> } else
> {
> Collection cellValues = new ArrayList();
> for (short cellNum = 0; cellNum < cellCnt; cellNum++)
> {
> HSSFCell cell = row.getCell(cellNum);
> if (cell != null
> && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK)
> {
> // Get the value of the cell and approximate its type
> int cellType = cell.getCellType();
> Object cellValue = null;
> if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
> {
> double cellDoubleValue = cell
> .getNumericCellValue();
> if (HSSFDateUtil.isCellDateFormatted(cell))
> {
> cellValue = new Timestamp(HSSFDateUtil
> .getJavaDate(cellDoubleValue)
> .getTime());
> } else
> {
> long cellLongValue = (long) cellDoubleValue;
> if (cellLongValue == cellDoubleValue)
> {
> cellValue = new Long(cellLongValue);
> } else
> {
> cellValue = new Double(cellDoubleValue);
> }
> }
> } else if (cellType == HSSFCell.CELL_TYPE_STRING)
> {
> // WR-1579 begin
> HSSFRichTextString richText = cell.getRichStringCellValue();
> cellValue = richText.getString();
> // WR-1579 end
> }
>
> foundRowData = true;
> cellValues.add(cellValue);
> } else
> {
> cellValues.add("");
> }
> }
>
> /* If no valid cell has been found
> * to this point in the spreadsheet
> * try to get a value from the Shared
> * String Table at the row's position.
> *
> * See page 389 in the
> * Excel 97 Developer's Kit
> */
> if (!foundRowData)
> {
> System.out
> .println("No cell data has been found for row: "
> + rowNum);
>
> try
> {
> /* If and SST value is found then the cellValues
> * Collection must be cleared to remove any empty
> * Strings added in the cell loop, above.
> */
> System.out
> .println("Looking for cell data from Shared String Table: "
> + sstPosition); // WR-0964
> String sstString = workbook
> .getSSTString(sstPosition);
> cellValues = new ArrayList();
> cellValues.add(sstString);
> System.out
> .println("Set cell data from Shared String Table: "
> + sstPosition); // WR-0964
> } catch (Exception e)
> {
> System.out
> .println("Failed to get Shared String Table String: "
> + sstPosition); // WR-0964
> }
> sstPosition++;
> }
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Fwd: Deprecate HSSFWorkbook.getSSTString(int) ?
Posted by ap...@charter.net.
Sure. I can do that.
I discovered the issue when some users of a web application of mine started uploaded spreadsheets into which they had cut and pasted some data. The data shows up on the spreadsheet when you open it, but only exists in the SST table when you parse it.
I'll create the bug report.
Thanks.
---- Yegor Kozlov <ye...@dinom.ru> wrote:
> Can you create a bug in bugzilla and attach the problem file and sample code to reproduce the problem?
>
> SST is a cache of strings in a workbook. It MAY contain stale data for those cells that were deleted.
> Direct access of the SST container via HSSFWorkbook.getSSTString(int) is not a good idea. You may get wrong results.
>
> Yegor
>
> > I sent this email about a year ago when I noticed that HSSFWorkbook.getSSTString(int) was deprecated. I got no responce so I thought I would give it a shot , again.
> >
> > My problem still exists in that going through the rows and cells using HSSFRow and HSSFCell do not always return all the data in the spreadsheet. Data that is only in the SST can be displayed in Excel but not be accessible by the preferred method of accessing the data.
> >
> > Can anyone offer advice on this?
> >
> >> Date: Thu, 16 Aug 2007 9:38:48 -0400
> >> From: <ap...@charter.net>
> >> To: user@poi.apache.org
> >> Subject: Deprecate HSSFWorkbook.getSSTString(int) ?
> >>
> >> Why was HSSFWorkbook.getSSTString(int) deprecated? I parse spreadsheets that are uploaded via a web application and there are times I get spreadsheets that look like they have data but when the normal methods of getting data out of the cells are used, it does not return any data. The only way I can get the data out of the spreadsheet is to use the getSSTString method. When I saw it was deprecated in 3.0, I was hopeful that the issue was corrected, but I just tried it and I am still required to use getSSTString to get the data.
> >>
> >> A code snippet form the code I use to get the data from the spreadsheet is as follows and I have attached an example spreadsheet that has this issue. Note that if you open it and save it, it will not have the issue anymore. If you are going to try it out, just save it directly from the email.
> >>
> >> Thanks for any help. If more information is needed, I will be happy to provide it.
> >>
> >> int rowCnt = sheet.getLastRowNum();
> >> for (int rowNum = 0; rowNum <= rowCnt; rowNum++)
> >> {
> >> boolean isBlankRow = true;
> >> HSSFRow row = sheet.getRow(rowNum);
> >> if (row == null)
> >> {
> >> System.out.println("Row " + rowNum + " was NULL.");
> >> continue;
> >> }
> >>
> >> Collection errors = new ArrayList();
> >> Object voObject = null;
> >> try
> >> {
> >> voObject = voClass.newInstance();
> >> } catch (IllegalAccessException ex)
> >> {
> >> ex.printStackTrace();
> >> throw new RwtException(ExceptionConstants.GENERAL_EX,
> >> RwtException.SYSTEM_EXCEPTION,
> >> RwtException.FATAL_ERROR, ex);
> >> } catch (InstantiationException ex)
> >> {
> >> ex.printStackTrace();
> >> throw new RwtException(ExceptionConstants.GENERAL_EX,
> >> RwtException.SYSTEM_EXCEPTION,
> >> RwtException.FATAL_ERROR, ex);
> >> }
> >>
> >> int cellCnt = row.getLastCellNum();
> >> Collection mapColumns = fileMap.getColumns();
> >> if (hasHeader && rowNum == 0)
> >> {
> >> System.out.println("Validating the header!!!");
> >> Collection headerErrors = validateHeader(row, mapColumns,
> >> file.getFileName(), rowNum);
> >> if (headerErrors.size() > 0)
> >> {
> >> System.out.println("Found error in header.");
> >> throw new InvalidHeaderException(headerErrors);
> >> }
> >> } else
> >> {
> >> Collection cellValues = new ArrayList();
> >> for (short cellNum = 0; cellNum < cellCnt; cellNum++)
> >> {
> >> HSSFCell cell = row.getCell(cellNum);
> >> if (cell != null
> >> && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK)
> >> {
> >> // Get the value of the cell and approximate its type
> >> int cellType = cell.getCellType();
> >> Object cellValue = null;
> >> if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
> >> {
> >> double cellDoubleValue = cell
> >> .getNumericCellValue();
> >> if (HSSFDateUtil.isCellDateFormatted(cell))
> >> {
> >> cellValue = new Timestamp(HSSFDateUtil
> >> .getJavaDate(cellDoubleValue)
> >> .getTime());
> >> } else
> >> {
> >> long cellLongValue = (long) cellDoubleValue;
> >> if (cellLongValue == cellDoubleValue)
> >> {
> >> cellValue = new Long(cellLongValue);
> >> } else
> >> {
> >> cellValue = new Double(cellDoubleValue);
> >> }
> >> }
> >> } else if (cellType == HSSFCell.CELL_TYPE_STRING)
> >> {
> >> // WR-1579 begin
> >> HSSFRichTextString richText = cell.getRichStringCellValue();
> >> cellValue = richText.getString();
> >> // WR-1579 end
> >> }
> >>
> >> foundRowData = true;
> >> cellValues.add(cellValue);
> >> } else
> >> {
> >> cellValues.add("");
> >> }
> >> }
> >>
> >> /* If no valid cell has been found
> >> * to this point in the spreadsheet
> >> * try to get a value from the Shared
> >> * String Table at the row's position.
> >> *
> >> * See page 389 in the
> >> * Excel 97 Developer's Kit
> >> */
> >> if (!foundRowData)
> >> {
> >> System.out
> >> .println("No cell data has been found for row: "
> >> + rowNum);
> >>
> >> try
> >> {
> >> /* If and SST value is found then the cellValues
> >> * Collection must be cleared to remove any empty
> >> * Strings added in the cell loop, above.
> >> */
> >> System.out
> >> .println("Looking for cell data from Shared String Table: "
> >> + sstPosition); // WR-0964
> >> String sstString = workbook
> >> .getSSTString(sstPosition);
> >> cellValues = new ArrayList();
> >> cellValues.add(sstString);
> >> System.out
> >> .println("Set cell data from Shared String Table: "
> >> + sstPosition); // WR-0964
> >> } catch (Exception e)
> >> {
> >> System.out
> >> .println("Failed to get Shared String Table String: "
> >> + sstPosition); // WR-0964
> >> }
> >> sstPosition++;
> >> }
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Fwd: Deprecate HSSFWorkbook.getSSTString(int) ?
Posted by Yegor Kozlov <ye...@dinom.ru>.
Can you create a bug in bugzilla and attach the problem file and sample code to reproduce the problem?
SST is a cache of strings in a workbook. It MAY contain stale data for those cells that were deleted.
Direct access of the SST container via HSSFWorkbook.getSSTString(int) is not a good idea. You may get wrong results.
Yegor
> I sent this email about a year ago when I noticed that HSSFWorkbook.getSSTString(int) was deprecated. I got no responce so I thought I would give it a shot , again.
>
> My problem still exists in that going through the rows and cells using HSSFRow and HSSFCell do not always return all the data in the spreadsheet. Data that is only in the SST can be displayed in Excel but not be accessible by the preferred method of accessing the data.
>
> Can anyone offer advice on this?
>
>> Date: Thu, 16 Aug 2007 9:38:48 -0400
>> From: <ap...@charter.net>
>> To: user@poi.apache.org
>> Subject: Deprecate HSSFWorkbook.getSSTString(int) ?
>>
>> Why was HSSFWorkbook.getSSTString(int) deprecated? I parse spreadsheets that are uploaded via a web application and there are times I get spreadsheets that look like they have data but when the normal methods of getting data out of the cells are used, it does not return any data. The only way I can get the data out of the spreadsheet is to use the getSSTString method. When I saw it was deprecated in 3.0, I was hopeful that the issue was corrected, but I just tried it and I am still required to use getSSTString to get the data.
>>
>> A code snippet form the code I use to get the data from the spreadsheet is as follows and I have attached an example spreadsheet that has this issue. Note that if you open it and save it, it will not have the issue anymore. If you are going to try it out, just save it directly from the email.
>>
>> Thanks for any help. If more information is needed, I will be happy to provide it.
>>
>> int rowCnt = sheet.getLastRowNum();
>> for (int rowNum = 0; rowNum <= rowCnt; rowNum++)
>> {
>> boolean isBlankRow = true;
>> HSSFRow row = sheet.getRow(rowNum);
>> if (row == null)
>> {
>> System.out.println("Row " + rowNum + " was NULL.");
>> continue;
>> }
>>
>> Collection errors = new ArrayList();
>> Object voObject = null;
>> try
>> {
>> voObject = voClass.newInstance();
>> } catch (IllegalAccessException ex)
>> {
>> ex.printStackTrace();
>> throw new RwtException(ExceptionConstants.GENERAL_EX,
>> RwtException.SYSTEM_EXCEPTION,
>> RwtException.FATAL_ERROR, ex);
>> } catch (InstantiationException ex)
>> {
>> ex.printStackTrace();
>> throw new RwtException(ExceptionConstants.GENERAL_EX,
>> RwtException.SYSTEM_EXCEPTION,
>> RwtException.FATAL_ERROR, ex);
>> }
>>
>> int cellCnt = row.getLastCellNum();
>> Collection mapColumns = fileMap.getColumns();
>> if (hasHeader && rowNum == 0)
>> {
>> System.out.println("Validating the header!!!");
>> Collection headerErrors = validateHeader(row, mapColumns,
>> file.getFileName(), rowNum);
>> if (headerErrors.size() > 0)
>> {
>> System.out.println("Found error in header.");
>> throw new InvalidHeaderException(headerErrors);
>> }
>> } else
>> {
>> Collection cellValues = new ArrayList();
>> for (short cellNum = 0; cellNum < cellCnt; cellNum++)
>> {
>> HSSFCell cell = row.getCell(cellNum);
>> if (cell != null
>> && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK)
>> {
>> // Get the value of the cell and approximate its type
>> int cellType = cell.getCellType();
>> Object cellValue = null;
>> if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
>> {
>> double cellDoubleValue = cell
>> .getNumericCellValue();
>> if (HSSFDateUtil.isCellDateFormatted(cell))
>> {
>> cellValue = new Timestamp(HSSFDateUtil
>> .getJavaDate(cellDoubleValue)
>> .getTime());
>> } else
>> {
>> long cellLongValue = (long) cellDoubleValue;
>> if (cellLongValue == cellDoubleValue)
>> {
>> cellValue = new Long(cellLongValue);
>> } else
>> {
>> cellValue = new Double(cellDoubleValue);
>> }
>> }
>> } else if (cellType == HSSFCell.CELL_TYPE_STRING)
>> {
>> // WR-1579 begin
>> HSSFRichTextString richText = cell.getRichStringCellValue();
>> cellValue = richText.getString();
>> // WR-1579 end
>> }
>>
>> foundRowData = true;
>> cellValues.add(cellValue);
>> } else
>> {
>> cellValues.add("");
>> }
>> }
>>
>> /* If no valid cell has been found
>> * to this point in the spreadsheet
>> * try to get a value from the Shared
>> * String Table at the row's position.
>> *
>> * See page 389 in the
>> * Excel 97 Developer's Kit
>> */
>> if (!foundRowData)
>> {
>> System.out
>> .println("No cell data has been found for row: "
>> + rowNum);
>>
>> try
>> {
>> /* If and SST value is found then the cellValues
>> * Collection must be cleared to remove any empty
>> * Strings added in the cell loop, above.
>> */
>> System.out
>> .println("Looking for cell data from Shared String Table: "
>> + sstPosition); // WR-0964
>> String sstString = workbook
>> .getSSTString(sstPosition);
>> cellValues = new ArrayList();
>> cellValues.add(sstString);
>> System.out
>> .println("Set cell data from Shared String Table: "
>> + sstPosition); // WR-0964
>> } catch (Exception e)
>> {
>> System.out
>> .println("Failed to get Shared String Table String: "
>> + sstPosition); // WR-0964
>> }
>> sstPosition++;
>> }
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org