Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Apply template to Excel with HTMLFORMTYPE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Apply template to Excel with HTMLFORMTYPE
 Login/Join
 
Teri Newton
Member
posted
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? Confused

Thanks!

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



 
Posts: 23 | Location: Cincinnati, OH | Registered: September 25, 2013Report This Post
BabakNYC
Virtuoso
posted Hide Post
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,


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
jgelona
Master
posted Hide Post
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.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Teri Newton
Member
posted Hide Post
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.



 
Posts: 23 | Location: Cincinnati, OH | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Apply template to Excel with HTMLFORMTYPE

Copyright © 1996-2020 Information Builders