Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Sơn Tuấn Nguyễn
Manipulate Excel
August 13, 2012 9:46 PM
Answer

Sơn Tuấn Nguyễn

Rank: Junior Member

Posts: 37

Join Date: March 1, 2010

Recent Posts

I have simple case of putting data in a predefined excel file(nothing special, just write data to specified cells).

Wonder what is the best solution with Liferay so far, as there're plenty of api(s) around.

Thanks for advices.
David H Nebinger
RE: Manipulate Excel
August 14, 2012 6:36 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 6261

Join Date: September 1, 2006

Recent Posts

Any one will work that supports the functionality you need.

I've had a lot of luck with JExcel (I think that's the name of it). Your biggest challenge would be finding a lib that's compatible with the latest version of Excel, if that's a requirement.
Hitoshi Ozawa
RE: Manipulate Excel
August 14, 2012 3:07 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

Do you want to pull data from liferay into your excel or do you want to push data from liferay to excel. David suggestion is to push the data from liferay to excel.

I usually do the opposite of pulling data from liferay because more users are comfortable using excel than liferay. In this situation, I'm just calling liferay's web service or json service from excel VBA.
Joel Peterson
RE: Manipulate Excel
August 14, 2012 3:23 PM
Answer

Joel Peterson

Rank: New Member

Posts: 22

Join Date: February 18, 2010

Recent Posts

Liferay includes the Apache POI project which provides for creating and/or modifying Microsoft Office documents. Here is a brief code example of creating a new XSLX Excel document. This works for us in Liferay 6.0.

 1
 2//import various classes from org.apache.poi...
 3
 4//create the Excel Workbook object
 5Workbook wb = new XSSFWorkbook();
 6
 7//create a worksheet
 8Sheet sheet = wb.createSheet(title);
 9
10//create a row for column headers
11Row headerRow = sheet.createRow(0);
12Cell primCell = headerRow.createCell(0);
13
14//now create some data rows
15primCell.setCellValue("Primary Key");
16for (int j=1;j<10;j++) {
17    Row dataRow= sheet.createRow(j);
18    Cell dataCell = dataRow.createCell(0);
19    dataCell .setCellValue("some data");
20}
Hitoshi Ozawa
RE: Manipulate Excel
August 14, 2012 3:37 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

I have simple case of putting data in a predefined excel file(nothing special, just write data to specified cells).


It's a little bit more complicated because it a "predefined" excel file. To process it on liferay, it has to be uploaded or pre-stored in something like document & media library.
After filling in the data, it has to be downloaded to stored in document & media library (under a different name?). Saving files also required setting permissions and operational task such as deleting them when they become unnecessary.

In all, it is much easier to just pull data from liferay into excel.
Joel Peterson
RE: Manipulate Excel
August 14, 2012 3:45 PM
Answer

Joel Peterson

Rank: New Member

Posts: 22

Join Date: February 18, 2010

Recent Posts

Hitoshi Ozawa:

It's a little bit more complicated because it a "predefined" excel file.


Yeah, I agree that it will be a little more complicated. If it is just a case where the user fills out a form and then expects to download the file right away you could skip storing the resulting Excel documents in Liferay and serve them up immediately for download. In that case you would only need to keep the pre-defined file somewhere in Liferay so that you could open it with the POI apis and fill in the specific cells that need the data. If the user needs the filled out Excel document to be available later then storage and permissions would need to be considered.

I just gave the example of creating a new document because I already had that code available.
Hitoshi Ozawa
RE: Manipulate Excel
August 14, 2012 3:54 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

I thought you were just generating the excel sheet on the fly. We had a similar requirement but an additional requirement that the user wanted to modify the excel sheet layout themselves periodically. Well, just to say there are many ways to skin a cat. :-)
Joel Peterson
RE: Manipulate Excel
August 14, 2012 4:03 PM
Answer

Joel Peterson

Rank: New Member

Posts: 22

Join Date: February 18, 2010

Recent Posts

The example that I posted does generate it on the fly but the api also supports loading an existing Excel document and then modifying it. Unfortunately I have not ever had to do that so I don't have any sample code lying around.

There may be other better libraries out there and the only reason I used that one is that Liferay already includes it and I could use their source code as a reference if I needed it.
Jack Bakker
RE: Manipulate Excel
August 14, 2012 4:08 PM
Answer

Jack Bakker

Rank: Liferay Master

Posts: 511

Join Date: January 3, 2010

Recent Posts

aside from the push/pull question, I've been using http://ostermiller.org/utils/ to produce
Sơn Tuấn Nguyễn
RE: Manipulate Excel
August 16, 2012 12:52 AM
Answer

Sơn Tuấn Nguyễn

Rank: Junior Member

Posts: 37

Join Date: March 1, 2010

Recent Posts

Thank you guys for your support. I think I will be fine with Apache POI.
I tried reading a xlsx then write it down to client side using <portlet:resourceURL> and ResourceRequest, ResourceResponse.

However I found another problem. Because serveResource(ResourceRequest req, ResourceResponse res) is differ from report(ActionRequest actionRequest, ActionResponse actionResponse), one is action handler, one is resource handler. I can't get parameters from actionRequest anymore. Those parameters are required to generate the excel report exactly.

For example: User inputs his name into input text field then I write that name in the pre-defined form in excel file, then give them the file. Reading and writing is ok but without the name input by user. Because there's no actionRequest in serveResource method.

OutputStream out = res.getPortletOutputStream();

res is ResourceResponse. This is how I get the output stream to write the file back to render phase. But in an action handler method, i dont have ResourceResponse, just ActionResponse, seem no way to get output stream.


Any help is appreciated. Thanks in advance.
Jack Bakker
RE: Manipulate Excel
August 18, 2012 8:56 AM
Answer

Jack Bakker

Rank: Liferay Master

Posts: 511

Join Date: January 3, 2010

Recent Posts

another problem might be better in another/new forum post ? (you'll get better attention if subject/thread is focused)