Friday, July 20, 2007

ofbiz to Excel

Export results from ofbiz to Excel:

There is an example of how to do this in the Facilities application.
the example is at https://localhost:8443/facility/control/ViewFacilityInventoryByProduct?facilityId=WebStoreWarehouse

Excel can easily open a well formed XML document and preserve the color and style of the original report.
To output in XML: In the view-map tag in controller.xml change "type=screen" to "type=screenxml" and you will get xml output!

All your FTL code must be in valid XML. That means that <link>
<link rel='stylesheet' href='/assets/css/style.css' type='text/css'> 
is not valid but
<link rel='stylesheet' href='/assets/css/style.css' type='text/css' /> 
is valid.
Other tags to check:
  • area
  • input
  • img
  • param
  • is not valid. Use &160;instead
then, change the attribute content-type="text/xml" to content-type="application/ms-excel"
and users will be prompted to download a file instead of viewing it in the browser. if you change the link and the view-map to control/myPage.xls instead of control/myPage, then windows will offer to open the file with MS Excel.
Using a https to do this in Internet Explorer may return the message that "Internet explorer cannot download [yourReport] from localhost". If that is the case, you can turn off https for this request. If that is not an option, you must add a header to the response somehow. The view-map tag does not support other headers besides content-type, so this header gets set in the beanshell file with lines like:
fileName = "courseSetCompletionReportsExcel.xls";

// the following instruction prompts the user with option to open report in Excel (instead of in the browser):

response.setHeader( "Content-disposition", "attachment; filename=\"" + fileName + "\"");

//the following seems to have no effect, probably because my headers are set this way somewhere else:
response.setHeader("Cache-Control", "cache");

// next two lines fixed the refusal to download issue:
response.setHeader("Cache-Control", "must-revalidate");
response.setHeader( "Pragma", "public" );

No comments: