THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
There is an example of how to do this in the Facilities application.
The example is at https://demo-trunk.ofbiz.apache.org/facility/control/ViewFacilityInventoryByProduct?facilityId=WebStoreWarehouse.
You need to generate the list by using the "Find" button before exporting using the "Export" button.
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 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.
Note:
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" );
A tip from the blog Ofbiz Trail Guide
3 Comments
Unknown User (bjfree@free-man.net)
Note: those people using office 2000 don't have the ability to import xml.
so a well formed cvs file is necessary.
Unknown User (bjfree@free-man.net)
for got to mention to put .xls on the cvs file and it will open right in to excel.
this also works on Open office spreadsheet.
chris snow
I have managed to get the following working with POI 3.2 and OpenOffice, but the spreadsheet is not opening with Excel for some reason?
<request-map uri="ClearanceReportExport.xls">
<security https="true" auth="true"/>
<event type="bsf" path="" invoke="mypath/ExportXls.bsh"/>
<response name="success" type="view" value="ClearanceReportExport"/>
</request-map>
<view-map name="ClearanceReportExport" type="screenxml" page="component://mycomponent/widget/mycomponent/MainScreens.xml#ClearanceReportExport" content-type="application/vnd.ms-excel"/>
// mypath/ExportXls.bsh :
import org.ofbiz.base.util.*;
import org.ofbiz.entity.*;
import org.ofbiz.security.*;
import org.ofbiz.service.*;
import org.ofbiz.entity.model.*;
import org.ofbiz.entity.condition.*;
import org.ofbiz.base.util.Debug;
import javax.servlet.*;
import javax.servlet.http.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
fileName = "ClearanceReport.xls";
response.setHeader("Content-disposition", "attachment; filename=\"" + fileName + "\"");
response.setHeader("Cache-Control", "cache");
response.setHeader("Cache-Control", "must-revalidate");
response.setHeader("Pragma", "public" );
String controlPath=(String)request.getAttribute("CONTROL_PATH");
String serverRootUrl=(String)request.getAttribute("SERVER_ROOT_URL");
GenericDelegator delegator = (GenericDelegator)request.getAttribute("delegator");
conditionList = new ArrayList();
data = delegator.findAll ("ClearanceReport", conditions, null, null);
HSSFWorkbook wb = new HSSFWorkbook();
sheet = wb.createSheet("Clearance Report");
// Setup hyperlink styles
HSSFCellStyle hlink_style = wb.createCellStyle();
HSSFFont hlink_font = wb.createFont();
hlink_font.setUnderline(HSSFFont.U_SINGLE);
hlink_font.setColor(HSSFColor.BLUE.index);
hlink_style.setFont(hlink_font);
row = sheet.createRow((short)0);
row.createCell(0).setCellValue("ingUplNumber");
row.createCell(1).setCellValue("HpiStolen");
row.createCell(2).setCellValue("HpiScrapMarker");
row.createCell(3).setCellValue("strIsrCode");
dataItr = data.iterator();
rowNum = 1;
while ( dataItr.hasNext() ) {
record = (Map) dataItr.next();
row = sheet.createRow(rowNum);
ingUplNumber = ((Long)record.get("ingUplNumber")).toString();
String url = serverRootUrl + controlPath + "/Selected?UpliftNumber=" + ingUplNumber + "&action=UpliftDetails";
cell = row.createCell(0);
cell.setCellValue(ingUplNumber);
HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
link.setAddress(url);
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
row.createCell(1).setCellValue(record.get("HpiStolen"));
row.createCell(2).setCellValue(record.get("HpiScrapMarker"));
row.createCell(3).setCellValue(record.get("strIsrCode"));
rowNum++;
}
row = sheet.createRow((short)rowNum+1);
row.createCell(0).setCellValue("Etc..");
// TODO write to temporary file instead to reduce memory consumption
OutputStream baos = new ByteArrayOutputStream();
wb.write(baos);
int size = baos.size();
Debug.logInfo("Spreadsheet size = " + size + " bytes", "");
response.setHeader("size", Integer.toString(baos.size()));
UtilHttp.streamContentToBrowser(response, baos.toByteArray(), "application/vnd.ms-excel");
baos.close();