Manipulate Excel

[Son NT, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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.

//import various classes from org.apache.poi...

//create the Excel Workbook object
Workbook wb = new XSSFWorkbook();

//create a worksheet
Sheet sheet = wb.createSheet(title);

//create a row for column headers
Row headerRow = sheet.createRow(0);
Cell primCell = headerRow.createCell(0);

//now create some data rows
primCell.setCellValue("Primary Key");
for (int j=1;j<10;j++) {
	Row dataRow= sheet.createRow(j);
	Cell dataCell = dataRow.createCell(0);
	dataCell .setCellValue("some data");
}

[Hitoshi Ozawa, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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

[Son NT, módosítva 11 év-val korábban]

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, módosítva 11 év-val korábban]

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