File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Other Open Source Projects and the fly likes excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "excel " Watch "excel " New topic
Author

excel

phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Can anyone help me, how to open the existing excel file which is already formated with column names. I need to update the values for the corresponding coulmns.

Please give some examples.

Thanks a lot.
Jeremy Wilson
Ranch Hand

Joined: Feb 18, 2003
Posts: 166
Take a look at the poi open source library for reading excel files. The hssf interface allows for reading and writing to cells.


Jeremy Wilson
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Could you please give some examples? That would be really helpful.

thanks
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
I need to generate a excel report based on the values.

Is it possible? Can you give som examples please


Thanks,
Lakshmi
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39544
    
  27
The Quick Guide contains lots of examples showing many aspects of POI in action. You should be able to create XSL files in no time.


Ping & DNS - updated with new look and Ping home screen widget
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Yes,I saw poi, but It doesn't have any examples which modifies the existing template

For example here is the template of empl.xsl.I don't know How should i post it. If I want to modify in Q2 using java. How would I do it?



[ March 14, 2008: Message edited by: lakshmi manepally ]

[ March 14, 2008: Message edited by: lakshmi manepally ]

[ March 14, 2008: Message edited by: lakshmi manepally ]

[ March 14, 2008: Message edited by: lakshmi manepally ]
[ March 14, 2008: Message edited by: lakshmi manepally ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39544
    
  27
Assuming that by "template" you mean an XLS file, then the quick guide has plenty of examples on how to work with them. It shows to open a file, how to get a sheet in the file, how to get a row of records in a sheet, how to get a cell in a row, how to read and wrote the values and formulas in a cell, and how to save the file. Maybe not all in one example, but it's all there. What else would you need to know?
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Here is one more question .when I was trying to open the quetrly.xls which is 3.24mb size. I am getting Java heap size error. How would I resolve that?I am using Myeclipse and JBOSS.

Here is the code I am running.


Thansk a lot.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39544
    
  27
POI does use a lot of memory. Try increasing the amount of memory the servlet container has available; the default may be rather too little. (That could be a MyEclipse thing, I don't know.)

} catch ( Exception ex ) {
}

This is never a good idea when dealing with I/O. Plenty of things could be going wrong that you need to know about.

Also, JSPs are not well suited for this kind of code. Consider using a servlet or a backing bean instead.
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
The below code is for reading and write a excel file using poi.jar. I want to do the same thing using jxl.jar. Can anyone please send me one example




thanks
[ March 15, 2008: Message edited by: lakshmi manepally ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39544
    
  27
You may have overlooked the examples linked on the jExcelApi home page. They show all that in action.
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
I can see only reading the file, but I want to write also on some file.

Please post some related example
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18115
    
    8

Then follow the link that Ulf Dittmer posted already, and click on the link that says "Copying and modifying a spreadsheet".
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Here is the code for reading a file and write to another file using Jexcel.
Suppose If want to modify the col in the excel sheet automatically through database.How would I do it?

<code>

response.setContentType("application/vnd.ms-excel");
File fp = new File("D:\\excel\\Quarterly2007.xls");
try {
Workbook wb = Workbook.getWorkbook(fp);
WritableWorkbook copy = Workbook.createWorkbook(new File("d:\\excel\\output.xls"), wb);
WritableSheet sheet2 = copy.getSheet(1);
WritableCell cell = sheet2.getWritableCell(1, 2);

if (cell.getType() == CellType.LABEL)
{
Label l = (Label) cell;
l.setString("modified cell");


System.out.println(" ssss" );
}
Label label = new Label(0, 2, "New label record");
sheet2.addCell(label);
copy.write();
copy.close();

</code>

Could you please anybody help me ?

Thanks a lot
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9317
    
109

Suppose If want to modify the col in the excel sheet automatically through database


Can you please tell us what, "automatically modifying a column in an excel file through database" mean?

Also, while posting the code use the "Code" button in the message editor window to wrap the contents in a code block. I see that you have used <code> tags while posting, but the code tags here, are actually wrapped using [CODE].


[My Blog] [JavaRanch Journal]
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
when I open and read a xsl file which is a large file(3.324kb). I am getting java out of memory. This xsl file contains charts also.Is there any way I can convert to another format and read it.(including data and graphs)

Please someone help me in this case.
[ March 18, 2008: Message edited by: lakshmi manepally ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39544
    
  27
If neither POI nor jExcelApi work the options are limited. OpenOffice can open all kinds files -XLS included-, and save them to various other formats, and it does have a Java API. It also has much more of a learning curve than the pure Excel APIs, though.
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
I don't find anything in the google.Could you please send me some links?

Also one question. How is it possible if I open same excel into (because of the memory issue) htm and data should be added from the database on the htm ? Later I convert into excel inside the code to generate corresponding graphs or charts and open again as a htm page.

Is it possible?

Please can you answer my question.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39544
    
  27
Please don't post the same question multiple times. Let's continue the discussion in this duplicate thread.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: excel
 
Similar Threads
Problem in retrieving Cell values from Excel file.
Record Export?
WA #1.....word association
JasperReports -- excel issue
OutOfMemory error