Access to add and change pages is restricted. See: https://cwiki.apache.org/confluence/display/OFBIZ/Wiki+access

  • Created by Unknown User (jleroux), last updated by Unknown User (mbrohl) on Aug 16, 2018 2 minute read

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
  • &nbsp;

    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

  • No labels

3 Comments

  1. Unknown User (bjfree@free-man.net)

    Jul 29, 2008

    Note: those people using office 2000 don't have the ability to import xml.
    so a well formed cvs file is necessary.

    1. Unknown User (bjfree@free-man.net)

      Jul 29, 2008

      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.

  2. chris snow

    Apr 28, 2009

    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();