Excel with Java

[Bob Taboy]

Hello! I am new at this webpage and I don't know if its ok to leave this question here. I need to do a program in which the manage of data is essential, my client actually use excel as the way to manage information so I want to keep this. He won't buy access or SQL and using txt files or .dat files will generete too many of them so excel seems to me my best option. The problem y is that I haven't use it from Java, can someone tell me of libraries that can help with my problem? I have read about JExcel and another one but which is better? advantages/disadvantages? And some advices that you can tell me? thanks

[Tony Docherty]

Welcome to the Ranch.

Which one is better really depends on your requirements. For example: does it have to be open source or are you willing to pay for a license; is it simply a case of populating cells with values and/or formula or is it more complex eg do you need to generate pivot tables, graphs etc; does the excel workbook need to contain macros; which version(s) of Excel do you need to support etc etc.

[Bob Taboy]

I want to manipulate the 2010 excel files (which are the same as 2007), I haven't considered to use formulas pretty well but seems a good idea in the program, that will make it faster. create graphs and be able to show them in the program will be awesome, but not sure if that's possible.

What I really really need is to be able to position at diferent cells, like in a random access file, to use the same kind of search to get to the data (multiplying the register length times the number of register, plus the desired cel). And if there is a method that focus on the las cell I really need it. For example if I had a colum with information and need to write in the next one or read that cell, to be able to access it directly (like on several Java comands "lastIndexOf()"). I plan to access data in a similar way from a random access file, and need excel for the 2d array of informations

[Tony Docherty]

otob boto wrote:I want to manipulate the 2010 excel files (which are the same as 2007), I haven't considered to use formulas pretty well but seems a good idea in the program, that will make it faster. create graphs and be able to show them in the program will be awesome, but not sure if that's possible.

My reference to formula and graphs was in the Excel spreadsheet and not your program.

otob boto wrote:What I really really need is to be able to position at diferent cells, like in a random access file, to use the same kind of search to get to the data (multiplying the register length times the number of register, plus the desired cel). And if there is a method that focus on the las cell I really need it. For example if I had a colum with information and need to write in the next one or read that cell, to be able to access it directly (like on several Java comands "lastIndexOf()"). I plan to access data in a similar way from a random access file, and need excel for the 2d array of informations

Are you writing data or just reading it? If you are just reading data from cells then any of the free libraries should be easily able to do that. Look at the features they have and decide which most closely suits your needs.

[Ganesh Malaichamy]

Apache POI has good api for handling the excel document.This might be helpful in your case
http://poi.apache.org/spreadsheet/index.html

[Bob Taboy]

I need to read and write data along many excel documents, to give a better example one excel document per client, and I didn't knew that graphs could just be placed in excel, that's why I asked that.
And as I haven't used any library, can you tell me your experiencies with the libraries? I have read about POI, does it have any problems that I should consider?

[Ganesh Malaichamy]

Check the limitations documented in this link http://poi.apache.org/spreadsheet/limitations.html.

If you are planning to create charts you might need to use JFreeChart or templating.
http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html#.Ue3RCmUo7ug

I have used this library to create test reports. It works fine for me.

[Bob Taboy]

Thanks to all of you, your guide had been very helpful, but I still have some issues to solve that I haven't considered. Apache POI is made to manage information by row and then access the colums, but I need in several cases to manage information by colum and then by row, is there an efficient way to do that? Because I'm not very convinced for the methods I have seen. Any guide in a webpage you can lend me? I will thank you a lot.

[Ulf Dittmer]

No, the POI API doesn't have that. But it should be easy to write a few convenience methods that do that.