[CLOSED] Apply template to Excel with HTMLFORMTYPE

February 01, 2016, 01:10 PM

Teri Newton

I have an Excel worksheet with 3 tables and 1 chart that was created using HTMLFORMTYPE. I also have an Excel template (.mht) that formats column widths and positions the graphic with macros. How do I apply the template to the worksheet?

 
SET HTMLFORMTYPE=XLS

TABLE FILE CAR
SUM 
     DEALER_COST
     RETAIL_COST
BY  CAR
ON TABLE SET PAGE-NUM OFF 
ON TABLE HOLD AS TBL1 FORMAT HTMTABLE
END

GRAPH FILE CAR
SUM CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
BY CAR.COMP.CAR
ON GRAPH HOLD AS baseapp/cht1 FORMAT PNG
END

TABLE FILE CAR
PRINT 
     LENGTH
     WIDTH
     HEIGHT
     WEIGHT
BY CAR
ON TABLE SET PAGE-NUM OFF 
ON TABLE HOLD FORMAT HTMTABLE AS TBL2
END

TABLE FILE CAR
PRINT 
     WHEELBASE
     FUEL_CAP
     RPM
     MPG
BY CAR
ON TABLE SET PAGE-NUM OFF 
ON TABLE HOLD FORMAT HTMTABLE AS TBL3
END

-HTMLFORM BEGIN
<html>
<head></head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
   <td colspan="3"><h2 align="center">This is the Title </h2></td>
</tr><tr>
   <td width="50%">!IBI.FIL.TBL1;</td>
   <td></td>
   <td width="50%">[IMG]/ibi_apps/WFServlet?IBIF_adhocfex=EX%20EDAGET%20PNG,baseapp/cht1.png,b[/IMG]</td>
</tr><tr>
   <td></td>
   <td></td>
   <td></td>
</tr><tr>
   <td>!IBI.FIL.TBL2;</td>
   <td></td>
   <td>!IBI.FIL.TBL3;</td>
</tr>
</table>
</body>
</html>
-HTMLFORM END

Is it even possible?

Thanks!

This message has been edited. Last edited by: Teri Newton,

February 01, 2016, 03:39 PM

BabakNYC

I haven't tried this but could you use something like this in your example?

ON TABLE PCHOLD FORMAT XLSX TEMPLATE template_name SHEETNUMBER n

where:

template_name

Is the name of the Excel template file (workbook), including the file name and extension, residing on the WebFOCUS Reporting Server application directory search path. For example, IPOLICY.XLTX, PRINTSHEETS.XLTM, or DASHBOARD.XLSM. If the extension is not provided, it defaults to .XLTX.

n

Is the number of the Excel worksheet being replaced in the template file (workbook).

Another way you could do this is by using a Document containing each of these reports/charts. If the document output type is Excel, you'll end up with one Excel file with multiple tabs containing each of the reports.

This message has been edited. Last edited by: BabakNYC,

February 02, 2016, 08:57 AM

jgelona

Assuming you have a report1.mht file built with Excel 2003, here's how you do it:
[code}
FILEDEF TMP1 DISK tmp1.mht
FILEDEF TMP2 DISK tmp2.mht
...
TABLE FILE XYZ
ON TABLE HOLD AS TMP1 FORMAT EXL2K TEMPLATE report1 SHEETNUMBER 1
...
END
TABLE FILE ABC
ON TABLE HOLD AS TMP2 FORMAT EXL2K TEMPLATE tmp1 SHEETNUMBER 2
...
END
TABLE FILE DEF
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE tmp2 SHEETNUMBER 3
...
END
[/code]

Be sure report1.mht is in the path. We generally put our templates in baseapp.

The process is slightly different with Excel 2007 and higher.

We probably have 100 reports or more that use Excel templates in 2003 and 2010. Depending on how much data one has there are significant performance issues with multi-sheet reports using templates with Excel 2007 and higher, which is why everything hasn't been converted to Excel 2010.

February 03, 2016, 09:08 AM

Teri Newton

Thank you both for your suggestions. Unfortunately, this must be on one page.

I started with a document of type Excel, but upon run, the charts and reports go to separate worksheets. Using HTMLFORMTYPE=XLS gets it all on one page.

I have successfully used FORMAT EXL2K TEMPLATE in the past so I created a macro that would setup page layout for printing and fix some column widths and row heights. I was really hoping for something like...

 SET HTMLFORMTYPE = XLS FORMAT EXL2K TEMPLATE 'template' SHEETNUMBER 1

From reading the forum, I did learn how to do the page layout in the HTML code.

<html xmlns:v="urn:schemas-microsoft-com:vml"
      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<style>
<!--table
@page
  {margin:.25in .25in .25in .25in;
   mso-header-margin:0in;
   mso-footer-margin:0in;
   mso-page-orientation:landscape;}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet 1</x:Name>
    <x:WorksheetOptions>
     <x:FitToPage/>
     <x:Print>
      <x:FitWidth>1</x:FitWidth>
      <x:FitHeight>1</x:FitHeight>
      <x:ValidPrinterInfo/>
      <x:PaperSizeIndex>1</x:PaperSizeIndex>
     </x:Print>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>

The users will just have to do the rest of the formatting themselves. It is time to use WEBFOCUS for what it does well instead of forcing it to do the impossible.