Using Excel Templates

How to:


Report Painter provides Microsoft® Excel templates that are populated with data from WebFOCUS.

With this feature, you can integrate WebFOCUS with complex Excel workbooks that may contain macros, graphs, or Visual Basic applications. You can also supply ad hoc and end users with more advanced Excel functionality, such as filters, subtotals, page and print settings, and so on. This feature also provides you with more advanced styling options for Excel-based reports.

Note: You must have Excel 2002 or higher to use this feature.

Top of page

Procedure: How to Create an Excel Template for Project-Based Development
  1. Add a blank worksheet to a workbook in Microsoft Excel. This blank worksheet will contain the WebFOCUS report.
  2. Save the workbook as a Web archive (.mht extension) file on your WebFOCUS Reporting Server application directory.
  3. Create your WebFOCUS procedure in the Projects area of Developer Studio.
  4. In Report Painter, click Format from the Report menu.
  5. Ensure that Excel 2007 (XSLX), Excel 2000 (EXL2K), Excel 2007 Formula (XLSX FORMULA), Excel 2000 Formula (EXL2K FORMULA), Excel 2000 PivotTable (EXL2K PIVOT), Excel 97 (EXL97), or User (Excel) is selected as the output format.
  6. Click Use template to activate the Excel template options.

  7. Select the template file (Workbook Name) and the Worksheet to populate. The Worksheet to populate is the number of the worksheet in the workbook. For example, if you have five worksheets in your workbook and you designated the first worksheet for the WebFOCUS report, then you would enter 1.


    • In order for your workbook to appear in the Workbook Name drop-down list, it must be in your WebFOCUS Reporting Server application directory.
    • If you use the Excel 2007 output format, you will be able to choose templates with the .xltx, .xlsx, .xltm, and .xlsm formats. For all other Excel output formats, you will only be able to use templates with the .xltx format.

  8. Click OK in the Report Options dialog box.
  9. Save and run your report.

Top of page

Reference: Notes on Saving an Excel File as a Web Archive

Excel saves template files that contain only a single worksheet in its standard HTML file format rather than a Web archive (even if you select the .mht format in the Save-As dialog box, and it saves the file with a suffix of .mht). To ensure that you get a true Web archive file, your workbook must have at least two sheets, each of which contains content. The content can be a single blank in a single cell.

If your template file is not a true Web archive file, you receive the following message:

(FOC3290) EXL2K: Template file is not a valid WebArchive file

To verify that an .mht file is a valid Web archive, open the file in Notepad. The second line of the file should read:

X-Document-Type: Workbook

A non-Web archive file says Worksheet instead of Workbook.