Issue 307: Could we just create an excel export without showing the table on jsp?
Status:  Fixed
Owner: ----
Closed:  Mar 2011
Reported by shrividh...@gmail.com, Mar 11, 2011
Hello,

One of the client req is to get a logging report in xls.
We are having a similar table in our app.
However, we were trying to use Jmesa code to create an xls output even when we dont show the table?

Is this possible as I feel it should be and pretty easy to do.
Could you give me a path that I can follow to achieve this?

(BTW, the clients think Jmesa rocks! They just cant get enough of it ;))
Mar 11, 2011
Project Member #1 jeff.johnston.mn@gmail.com
I did that at work awhile back and then wrote up a tutorial for it. The only thing you have to do is create a Limit object and set it on the TableModel (or TableFacade < 3.0). Other than that it is like doing any other export.

https://code.google.com/p/jmesa/wiki/ManualTableExport
https://code.google.com/p/jmesa/wiki/ExportTutorialV3

This should still work for JMesa 3.0, although the Limit will look more like this:

Limit limit = new Limit(CONTESTS);
int size = contestEntries.size();
RowSelect rowSelect = new RowSelect(1, size, size);
limit.setRowSelect(rowSelect);
limit.setExportType(ExportType.JEXCEL);
limit.setSortSet(new SortSet()); // fixed in upcoming 3.0.4 so do not have to set
limit.setFilterSet(new FilterSet()); // fixed in upcoming 3.0.4 so do not have to set
return limit;

The only thing that you cannot do with JMesa 3.0 using the TableModel and this approach is have a custom filename like the one example shows. Although by default the table title is used so you should be able to just change that.

What I would do (if on JMesa 3.0) is follow the ExportTutorialV3 and remember to set the Limit on the TableModel. If you have problems let me know...I want this use case to work really well!

Glad to hear that JMesa is working out for you!




Mar 14, 2011
#2 shrividh...@gmail.com
Thank You very much for your response.
Could you tell me which version of jmesa this code reflects?
I am trying it in 2.3.4 and there is no renderExport(ExportType exportType,
View view)  method in ExcelViewExporter.
I also checked in 2.5 and 3.0

The view does not get set in my tableFacade.
Dont know where its going wrong.

I am trying to tweak this code to write out the export into a file in a dir
and not into response.

Could you please help?

Thanks.
*

public* String render() {

Limit l = getLimit();

View view = getView();

*try* {

*if* (l.getExportType() == ExportType.*EXCEL*) {

*new* CustomExcelViewExporter(view, "", response).export();

*return* "export successful";

} *else* {

*return* *super*.render();

//return "export successful";

}

} *catch* (Exception e) {

//logger.error("Not able to perform the " + exportType + " export.", e);

e.printStackTrace();

*return* e.getMessage();

}

}
Mar 14, 2011
Project Member #3 jeff.johnston.mn@gmail.com
Ok, try this.

Instead of doing (from the example):

tableFacade.render(); 

Try doing:

View view = tableFacade.getView(); 
byte[] bytes = view.getBytes();



Mar 14, 2011
#4 shrividh...@gmail.com
Instead I tried this :
*

public* *void* export(String fileName)

*throws* Exception

{

HSSFWorkbook workbook = (HSSFWorkbook)getView().render();

// responseHeaders(response);

// workbook.write(response.getOutputStream());

// File *temp* = new File("C:\\DevWork\\Releases\\*nci*
-release-3_9\\Project404\\target\\web-*app*\\exports\\manualExport.xls");

File temp = *new* File((fileName!=*null* ? fileName : "manualExport")+".xls"
);

*try* {

FileOutputStream out = *new* FileOutputStream (temp);

out.write(workbook.getBytes());

out.close();

} *catch* (IOException e) {

}

 }
I do get the xls files in the dir. However, when I open them, the export is
not rendered properly.
It says, Microsoft Exccel as to recver this file. After a couple of times of
'recovering' the file is displayed with the data.

Any thoughts?
Mar 14, 2011
#5 shrividh...@gmail.com
I am doing it this way now.
Still the Recovevry error.

File temp = *new* File((fileName!=*null* ? fileName : "manualExport")+".xls"
);

*try* {

FileOutputStream out = *new* FileOutputStream (temp);

out.write(getView().getBytes());

out.close();

} *catch* (IOException e) {

}
Mar 14, 2011
Project Member #6 jeff.johnston.mn@gmail.com
Strange...but the data does show up correctly?

Maybe try a Google search and see if you can find anything out?

You could also try JExcel...that would just require you to put the jar file in and then change the export type.
Mar 14, 2011
#7 shrividh...@gmail.com
Yes.
The data does show up correctly after 'recovering it twice'

Let me try the JEXCEL too.
Mar 14, 2011
#8 shrividh...@gmail.com
Could it be because I am giving the fileName while writing and also the
extension as .xls?
Mar 14, 2011
#9 shrividh...@gmail.com
I figured it out.
I did this and it works fine now.

*

try* {

FileOutputStream out = *new* FileOutputStream (temp);

workbook.write(out);

//out.write(workbook.getBytes());

out.close();

} *catch* (IOException e) {

}
Many thanks!
Mar 15, 2011
Project Member #10 jeff.johnston.mn@gmail.com
Awesome!
Status: Fixed