• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem in reading an excel using POI

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

In an excel when the cell format type is Accounting(with decimal values) and when the cell data is blank, then POI returns the cell object as null.

The cell object is returned as null only for Accounting type. For all other format type when cell does not have any value poi returns blank cell type.

Can you please suggest me a solution for this. The cell object should not be null even if it has blank value.

Or is there any way to identify the accounting format type in POI
 
Ranch Hand
Posts: 32
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is not at all unusual because Excel will only store a record in the file for a cell if that cell actually contains something (crudely, it is a little more complex than this of course!). If the cell is indeed blank, then it is highly likely that there will be no cell record for it in the file and that POI is correct in returning blank.

There are a couple of ways to check whether this is truly the case, which one you use depends upon the file type.

If you are working with an OOXML format file (.xlsx) then this is simply zipped xml and you can use a simple tool such as PKUnxip or WinRAR to unzip the archive. Next, you can navigate through the folders until you find the xml markup for the sheets - likely to be in a file with a name like sheetn.xml where n is the number of the sheet, so 1, 2, 3, etc. Open the xml with an editor and look for the cll record that corresponds to the cell you think is POI is miss-reporting.

Option 2 relates to the older binary format (.xls) files and does rely on another POI tool called BiffViewer - http://poi.apache.org/apidocs/org/apache/poi/hssf/dev/BiffViewer.html - which can be used to view the contents of the file. Again, you should be able to check to ensure that the cell records are indeed there.
 
Whatever you say buddy! And I believe this tiny ad too:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic