• 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

accessing excel file from java

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
can any one tell me how to code in java so that i can access the excel sheet and do manipulations in the specific column

say if i have to read the data from the column 7 in excel sheet and find the average of the 7th column
is that possible to do

please help me
 
Ranch Hand
Posts: 874
Android VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bharadwaz,

Check out Apache POI
 
baaru so
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hmm yea i have seen those files and tried to get the result from it but my case is different i am not creating the excel file now

i have the file and i need to retrive the columns of the file and find the average of the particular column
could it be done
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your case is different than what? You can certainly use Apache POI to read Excel documents and extract data from them, I have done it myself. That's your requirement too.
 
baaru so
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
oh im sorry i forgot to mention it

the excel file i am trying to load is of about 26 mb with over 3lakh lines and it raises the exception to load that file
i thought i mentioned it

i get ::::: jxl.read.biff.BiffException: Unable to recognize OLE stream exception when i run the code


 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So JExcelApi can't deal with it - that doesn't mean that POI can't deal with it, either.
 
baaru so
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yea i even tried it with POI package and now i get another exception saying

::::: java.io.IOException: Invalid header signature; read 1688935826934608, expected -2226271756974174256



 
Ranch Hand
Posts: 120
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I am not mistaken you are using Excel 2007. Support for that format is still in beta. Try POI 3.5 beta 5 according to POI webpage.
 
baaru so
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ya now i get the values out


but is there a way to store the values which are retrieved from excel sheet into an array
so that i can sort them

when i try to bring an array on

it says :::::::: Exception in thread "main" java.lang.NullPointerException


i used this part for it

Cell a1[]=null;
try
{
workbook=jxl.Workbook.getWorkbook(f);
for(int i=0;i<30;i++){
sheet=workbook.getSheet(0);
a1[i]= sheet.getCell(6,i);
System.out.println(a1[i].getContents());

}
workbook.close();
}catch(IOException ex){

and if i wont initialize the array i get an error saying not initialized.....

what can i do now
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The first step is to find out which object that you are dereferencing is null.
 
baaru so
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
the line
a1[i]= sheet.getCell(6,i);


is giving null exception
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Assuming that "sheet" is null, you now need to find out why the "getSheet" method returns a null object.
 
baaru so
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
actually if i wont use array there i get the result

but when i make array then i get null exception
i have checked it first initializing sheet to null and cell to null
 
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You never initialize a1, so you are trying to put something into null.
 
baaru so
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
a1 is of type Cell
and i want to parse the values of a1 to some array a of type integer and do sorting cal mx and min values

i initialized the array cell a1 to null first
 
Rob Spoor
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But you can't access (read, write) any array element if that array reference is null. You must first initialize the Cell array:
 
reply
    Bookmark Topic Watch Topic
  • New Topic