Foren

Home » Liferay Portal » English » 3. Development

Kombinierte Ansicht Flache Ansicht Baumansicht
Threads [ Zurück | Nächste ]
toggle
Sơn Tuấn Nguyễn
Manipulate Excel
13. August 2012 21:46
Antwort

Sơn Tuấn Nguyễn

Rang: Junior Member

Nachrichten: 37

Eintrittsdatum: 1. März 2010

Neue Beiträge

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
14. August 2012 06:36
Antwort

David H Nebinger

Rang: Liferay Legend

Nachrichten: 5739

Eintrittsdatum: 1. September 2006

Neue Beiträge

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
14. August 2012 15:07
Antwort

Hitoshi Ozawa

Rang: Liferay Legend

Nachrichten: 7990

Eintrittsdatum: 23. März 2010

Neue Beiträge

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
14. August 2012 15:23
Antwort

Joel Peterson

Rang: New Member

Nachrichten: 22

Eintrittsdatum: 18. Februar 2010

Neue Beiträge

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
14. August 2012 15:37
Antwort

Hitoshi Ozawa

Rang: Liferay Legend

Nachrichten: 7990

Eintrittsdatum: 23. März 2010

Neue Beiträge

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
14. August 2012 15:45
Antwort

Joel Peterson

Rang: New Member

Nachrichten: 22

Eintrittsdatum: 18. Februar 2010

Neue Beiträge

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
14. August 2012 15:54
Antwort

Hitoshi Ozawa

Rang: Liferay Legend

Nachrichten: 7990

Eintrittsdatum: 23. März 2010

Neue Beiträge

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
14. August 2012 16:03
Antwort

Joel Peterson

Rang: New Member

Nachrichten: 22

Eintrittsdatum: 18. Februar 2010

Neue Beiträge

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
14. August 2012 16:08
Antwort

Jack Bakker

Rang: Expert

Nachrichten: 457

Eintrittsdatum: 3. Januar 2010

Neue Beiträge

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
16. August 2012 00:52
Antwort

Sơn Tuấn Nguyễn

Rang: Junior Member

Nachrichten: 37

Eintrittsdatum: 1. März 2010

Neue Beiträge

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
18. August 2012 08:56
Antwort

Jack Bakker

Rang: Expert

Nachrichten: 457

Eintrittsdatum: 3. Januar 2010

Neue Beiträge

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