aspose 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
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: 42919
    
  68
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.
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: 42919
    
  68
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: 42919
    
  68
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: 42919
    
  68
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: 18987
    
    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: 10288
    
168

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: 42919
    
  68
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: 42919
    
  68
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