• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

excel

 
phani kon
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 166
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Take a look at the poi open source library for reading excel files. The hssf interface allows for reading and writing to cells.
 
phani kon
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could you please give some examples? That would be really helpful.

thanks
 
phani kon
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to generate a excel report based on the values.

Is it possible? Can you give som examples please


Thanks,
Lakshmi
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may have overlooked the examples linked on the jExcelApi home page. They show all that in action.
 
phani kon
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can see only reading the file, but I want to write also on some file.

Please post some related example
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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].
 
phani kon
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please don't post the same question multiple times. Let's continue the discussion in this duplicate thread.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic