• 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

Excel using Apache POI

 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm trying to write a simple code to write to an excel file. I'm getting the error message below. I've also pasted my code. Any help would be appreciated, thanks.


Exception in thread "main" org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:181)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:140)
at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:302)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:405)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:386)
at Excel.Write_Excel.main(Write_Excel.java:30)
 
Bartender
Posts: 3323
86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the Ranch.

Please UseCodeTags (← click) when posting code as it makes it easier for people to read your code and the easier it is for people to read your code the more likely you are to get an answer.
I've added them for you this time.

It looks like the file you are attempting to open is not a valid .xls file. Where did you get the file from? Can you open it in Excel?

 
Ranch Hand
Posts: 460
6
Netbeans IDE Oracle Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try use this code:





Read the documentation about the class HSSFWorkbook.
I received the same message when I try to read Excel file.

 
Dana Ucaed
Ranch Hand
Posts: 460
6
Netbeans IDE Oracle Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please see:

https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/

for the complete example.

 
Anthony Johnson
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tony Docherty wrote:Welcome to the Ranch.

Please UseCodeTags (← click) when posting code as it makes it easier for people to read your code and the easier it is for people to read your code the more likely you are to get an answer.
I've added them for you this time.

It looks like the file you are attempting to open is not a valid .xls file. Where did you get the file from? Can you open it in Excel?


I created the file and yes I can open it.
 
Tony Docherty
Bartender
Posts: 3323
86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your problem could be because you are opening a FileOutputStream to the file you are about to read in and as you aren't appending to the file it could be setting the file length to 0.

Try moving FileOutputStream fout = new FileOutputStream(test); to just before you write the file.
 
Anthony Johnson
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dana Ucaed wrote:Try use this code:





Read the documentation about the class HSSFWorkbook.
I received the same message when I try to read Excel file.



Thanks, this finally worked for me.
 
Anthony Johnson
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm getting this error when I try to create a for loop to fill in some cells?



Exception in thread "main" java.lang.NullPointerException
at Excel.Write_Excel.main(Write_Excel.java:40)
 
Dana Ucaed
Ranch Hand
Posts: 460
6
Netbeans IDE Oracle Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do you have values in these rows?

Do you have 11 values?

Your for is from 0 to 10 inclusive.

Apache POI is very sensible to null values.

 
Anthony Johnson
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dana Ucaed wrote:Do you have values in these rows?

Do you have 11 values?

Your for is from 0 to 10 inclusive.

Apache POI is very sensible to null values.



I cleared out the workbook to have nothing in it. What do you mean by me having 11 values.
 
Dana Ucaed
Ranch Hand
Posts: 460
6
Netbeans IDE Oracle Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What do you want to do?

Do you want to create null cells without values?

 
Tony Docherty
Bartender
Posts: 3323
86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When you have a compiler or runtime error please copy and paste the error details into your post as it generally gives us a lot of information about the problem.
[EDIT: Sorry just spotted you had included the details at the end of your post.]

Do you have a worksheet called "SheetSample" in file "test - Copy.xls", if not the call to getSheet("SheetSample") will return null and the following line will throw an NPE.

 
Anthony Johnson
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So after playing with it today. I notice that the code only works if there are values in the cell. Why is that and how do I fix it. I'm trying to start from a blank document.
 
Tony Docherty
Bartender
Posts: 3323
86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's several years since I used POI but from memory if your worksheet is blank then you have to create a row before you try to create a cell in that row.
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's not POI as such.
It's sort of how Excel works.
Rows and cells don't exist until you put data into some cells.  Though, in Excel, it looks like there's loads of rows, there aren't really any on a blank sheet (at least not when saved).
POI mirrors this.
 
reply
    Bookmark Topic Watch Topic
  • New Topic