<img alt="Liferay" height="36" src="http://cdn.www.liferay.com/osb-www-theme/images/custom/heading.png" width="146" />

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.

RE: Manipulate Excel

2012. augusztus 14. 6:36

Válasz

David H Nebinger

Rangsorolás: Liferay Legend

Hozzászólások: 4496

Csatlakozás dátuma: 2006. szeptember 1.

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.

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 14. 15:07

Válasz

Hitoshi Ozawa

Rangsorolás: Liferay Legend

Hozzászólások: 8000

Csatlakozás dátuma: 2010. március 23.

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.

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 14. 15:23

Válasz

Joel Peterson

Rangsorolás: New Member

Hozzászólások: 22

Csatlakozás dátuma: 2010. február 18.

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}

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 14. 15:37

Válasz

Hitoshi Ozawa

Rangsorolás: Liferay Legend

Hozzászólások: 8000

Csatlakozás dátuma: 2010. március 23.

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.

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 14. 15:45

Válasz

Joel Peterson

Rangsorolás: New Member

Hozzászólások: 22

Csatlakozás dátuma: 2010. február 18.

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.

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 14. 15:54

Válasz

Hitoshi Ozawa

Rangsorolás: Liferay Legend

Hozzászólások: 8000

Csatlakozás dátuma: 2010. március 23.

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. :-)

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 14. 16:03

Válasz

Joel Peterson

Rangsorolás: New Member

Hozzászólások: 22

Csatlakozás dátuma: 2010. február 18.

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.

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 14. 16:08

Válasz

Jack Bakker

Rangsorolás: Expert

Hozzászólások: 298

Csatlakozás dátuma: 2010. január 3.

aside from the push/pull question, I've been using http://ostermiller.org/utils/ to produce

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 16. 0:52

Válasz

Sơn Tuấn Nguyễn

Rangsorolás: Junior Member

Hozzászólások: 37

Csatlakozás dátuma: 2010. március 1.

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.

Megjelölés válaszként

RE: Manipulate Excel

2012. augusztus 18. 8:56

Válasz

Jack Bakker

Rangsorolás: Expert

Hozzászólások: 298

Csatlakozás dátuma: 2010. január 3.

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