Export to excel - from multiple listgrid in to different excel sheets

[shrikanth]

Hi,

I am using smartGwt4.1 power version, wanted to export from 3 different listgrid data into a excel file with 3 different sheets. Searched the showcase and other sites.. but nowhere it is mentioned how to export into different sheets.

Is there a way to do this?

Regards,
Shri

[sanhdir]

Same question

Same problem here, did you gel it resolved ?
Thanks

[ami10309]

Hi, was this resolved or did you guys find any alternatives?

[tece321]

Using:
SmartClient Version: v9.1p_2015-02-07/PowerEdition Deployment (built 2015-02-07)

Is the answer to this problem to simply specify the name in setExportFilename() as a string like this:

"Workbook.xlsx#Sheetname"

?

[Isomorphic]

No - we're not sure how that could result in a multi-sheet export ..?

Instead, you may be able to take the POI Spreadsheet objects we generate, which are available via the server-side API DSResponse.getExportObject(), and use POI APIs to combine multiple such exports into a single Spreadsheet.

[tece321]

No - we're not sure how that could result in a multi-sheet export ..?

Instead, you may be able to take the POI Spreadsheet objects we generate, which are available via the server-side API DSResponse.getExportObject(), and use POI APIs to combine multiple such exports into a single Spreadsheet.

Thanks for response. Figured out eventually that we will have to use the POI library to achieve our goal, and that simple method won't work.

How about using the POI to create a FileStream object, that is referencing a workbook and a specific worksheet, and using the Server side DSRequest.setExportTo( filestream ) to output one sheet of data. then closing the filestream, and re-opening it on a new sheet, appending, closing etc in a loop? So much of the work would be done in the DMI.

The bottom line is that we need to export many Grids ( list, tree ) all with different row/column definitions to one file. Of course we could ask the user to record each individual grid to a different workbook, but that would be unacceptable for 4 or more grids.

[Isomorphic]

We think you may have misread our suggestion - you say "that simple method won't work", but don't give a reason, then go on to describe an approach where "much of the work would be done in the DMI", which is what we were already suggesting..

To elaborate, we are suggesting that you would issue a series a export DSRequests, gather the POI objects produced by each via DSResponse.getExportObject(), then use POI APIs to combine the default exports into a single SpreadSheet.

What you seem to be suggesting is appending multiple complete binary files together. We would expect that to be just be treated as a corrupt file.

[tece321]

We think you may have misread our suggestion - you say "that simple method won't work", but don't give a reason, then go on to describe an approach where "much of the work would be done in the DMI", which is what we were already suggesting..

To elaborate, we are suggesting that you would issue a series a export DSRequests, gather the POI objects produced by each via DSResponse.getExportObject(), then use POI APIs to combine the default exports into a single SpreadSheet.

What you seem to be suggesting is appending multiple complete binary files together. We would expect that to be just be treated as a corrupt file.

I meant simply exporting to an Excel "book.xlxs#sheet1" one grid, closing it, then repeating for the next grid export to "book.xlxs#sheet2" and so on. The intended result is a work book named book.xlxs, with one sheet per grid.

Are you suggesting that we use a session variable to accumulate each export object, and to use the POI APIs to output the session export object arraylist to a single Spreadsheet in another DMI call?

OR

are you suggesting that on each getExportObject() call, one outputs the object to the single Spreadsheet in an append mode using the POI APIs, where the name of the common Spreadsheet is passed in the original request DSRequest?

OR

something else I'm missing?

[Isomorphic]

I meant simply exporting to an Excel "book.xlxs#sheet1" one grid, closing it, then repeating for the next grid export to "book.xlxs#sheet2" and so on. The intended result is a work book named book.xlxs, with one sheet per grid.

Still baffled by this. Maybe you mean this as a suggestion for a Feature Sponsorship for how this could be accomplished in a future version? Because there is no current documentation or API that suggests that the naming convention you're talking about would be treated specially.

Our suggestion is not quite either of your guesses. Here's another try: we are suggesting that you would issue a series a export DSRequests as a queue, gather the POI objects produced by each via DSResponse.getExportObject() in memory during a single HTTPRequest processing cycle (eg as request attributes; no need for session storage), then use POI APIs to combine the default exports into a single SpreadSheet, then simply stream that as the response by using RPCManager.doCustomResponse() to take over response processing.

[tece321]

Still baffled by this. Maybe you mean this as a suggestion for a Feature Sponsorship for how this could be accomplished in a future version? Because there is no current documentation or API that suggests that the naming convention you're talking about would be treated specially.

Yes it is likely a Feature Sponsorship.

The original idea was to use setExportFilename(), hoping the the underlying POI API would accept the filename format of "book.xlxs#Sheet1" to open a SpreadSheet named book.xlxs and create a sheet named Sheet1 onto which the grid would be exported. The form of "book.xlxs#Sheet1" is a Microsoft construct of specifying Excel SpreadSheets used in VBA and C# libs.

I will attempt to follow your suggested way.

[tece321]

This may be out of scope of your support but I thought I would ask.

I've created my XSSFWorkbook on the server following your suggestions.
What is happening of course is that the EXCEL file is created on the server file system. I want it to write/exist on the client file system.

Is there a way I can get the OutputStream of the exportData request, the one that writes/creates to the client file system and

a) output the XSSFWorkbook to that stream?
b) suppress the generation of the default "Results.xlsx" to the client system ?


If I achieve a) will b) fall into place as result?

Or is there a way to transfer the XSSFWorkbook back to client in the DSResponse?

[Isomorphic]

RPCManager.doCustomResponse() is how you tell SmartGWT that you want to take over the output stream. Having called this, you can access HttpServletResponse.getOutputStream() as normal and stream back your own xlsx in place of the default export.

Alternatively, you could write the file to the filesystem or DB, and arrange a *second* call to the server that downloads it, following this sample of a custom download. One advantage here is that, if any errors can result from the download process, you can report them normally instead of being in the situation that the browser is expecting a file, and having to report all errors vs somehow turning them into a downloadable file.

[tece321]

RPCManager.doCustomResponse() is how you tell SmartGWT that you want to take over the output stream. Having called this, you can access HttpServletResponse.getOutputStream() as normal and stream back your own xlsx in place of the default export.

Your suggestion of using the HttpServletResponse OutputStream seems to be meeting our needs. Thanks for the help.