Tribune

Home » Liferay Portal » English » 3. Development

Vista Combinata Vista Piatta Vista ad Albero
Discussioni [ Precedente | Successivo ]
toggle
Sơn Tuấn Nguyễn
Manipulate Excel
13 agosto 2012 21.46
Risposta

Sơn Tuấn Nguyễn

Punteggio: Junior Member

Messaggi: 37

Data di Iscrizione: 1 marzo 2010

Messaggi recenti

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 agosto 2012 6.36
Risposta

David H Nebinger

Punteggio: Liferay Legend

Messaggi: 7152

Data di Iscrizione: 1 settembre 2006

Messaggi recenti

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 agosto 2012 15.07
Risposta

Hitoshi Ozawa

Punteggio: Liferay Legend

Messaggi: 7990

Data di Iscrizione: 23 marzo 2010

Messaggi recenti

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 agosto 2012 15.23
Risposta

Joel Peterson

Punteggio: New Member

Messaggi: 22

Data di Iscrizione: 18 febbraio 2010

Messaggi recenti

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 agosto 2012 15.37
Risposta

Hitoshi Ozawa

Punteggio: Liferay Legend

Messaggi: 7990

Data di Iscrizione: 23 marzo 2010

Messaggi recenti

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 agosto 2012 15.45
Risposta

Joel Peterson

Punteggio: New Member

Messaggi: 22

Data di Iscrizione: 18 febbraio 2010

Messaggi recenti

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 agosto 2012 15.54
Risposta

Hitoshi Ozawa

Punteggio: Liferay Legend

Messaggi: 7990

Data di Iscrizione: 23 marzo 2010

Messaggi recenti

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 agosto 2012 16.03
Risposta

Joel Peterson

Punteggio: New Member

Messaggi: 22

Data di Iscrizione: 18 febbraio 2010

Messaggi recenti

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 agosto 2012 16.08
Risposta

Jack Bakker

Punteggio: Liferay Master

Messaggi: 613

Data di Iscrizione: 3 gennaio 2010

Messaggi recenti

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 agosto 2012 0.52
Risposta

Sơn Tuấn Nguyễn

Punteggio: Junior Member

Messaggi: 37

Data di Iscrizione: 1 marzo 2010

Messaggi recenti

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 agosto 2012 8.56
Risposta

Jack Bakker

Punteggio: Liferay Master

Messaggi: 613

Data di Iscrizione: 3 gennaio 2010

Messaggi recenti

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