[SOLVED] HTMLFORMTYPE=XLS and COLSPAN

May 06, 2010, 02:28 PM

Hi,
I have the code below, and my goal is to have the header extend over the 3 columns of the report. However, I also need to use the HTMLFORMTYPE=XLS to do some formatting for the output (landscape, draw line, footers) without using an Excel template. If I just have a regular EXL2K output format, the header looks perfect, but once I add the HTMLFORMTYPE piece then the heading is in column 1, it doesn't span. Anybody else has encountered this and any ideas on how to fix it?

Thanks,
Luminita

TABLE FILE CAR
PRINT
  CAR
  COUNTRY
  MODEL
BY CAR NOPRINT
-*
HEADING
"THIS IS A PRETTY LONG TEST HEADING WHICH SHOULD MAKE COLUMN 1 REALLY WIDE OR FORCE IT TO WRAP"
-*
ON TABLE HOLD AS NTF_OUT FORMAT HTMTABLE
-*ON TABLE PCHOLD FORMAT EXL2K
-*
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-*
TYPE=HEADING, LINE=1, JUSTIFY=CENTER, COLSPAN=3, SIZE=14, $
END
-*
-IF &RECORDS GT 0 GOTO SHOWRPT;
-MRNOEDIT -HTMLFORM cp_standard/NODATA
-GOTO DONE;
-SHOWRPT
-RUN
SET HTMLFORMTYPE=XLS
-HTMLFORM BEGIN
<html xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
 <head>
 <meta http-equiv=Content-Type content="text/html;">
 <meta name=ProgId content=Excel.Sheet>
 <meta name=Generator content="Microsoft Excel 10">
 < !--[if gte mso 9]><xml>
  <o:OfficeDocumentSettings>
-*  <o:ReadOnlyRecommended/>
  <o:DownloadComponents/>
  <o:LocationOfComponents HRef=3D"file:///\\"/>
  </o:OfficeDocumentSettings>
 </xml>< ![endif]-->
<style>
< !--table
	{
	mso-displayed-decimal-separator:"\x";
	mso-number-format:"\.00";
	mso-displayed-thousand-separator:"\,";}
@page
	{mso-footer-data : '&|L Date: &|D Time: &|T &|C Confidential Information &|R Page &|P of &|N';
	margin:.5in .25in .5in .25in;
	mso-header-margin:.25in;
	mso-footer-margin:.25in;
	mso-page-orientation:landscape;
	mso-horizontal-page-align:center; padding-top:0px;
	mso-number-format:"\#\#\,\#\#0\.00";}
-->
	</style>
< !--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>TEST</x:Name>
    <x:WorksheetOptions>
    <x:FitToPage/>
     <x:Print>
           <x:FitWidth>1</x:FitWidth>
           <x:FitHeight>99</x:FitHeight>
      <x:ValidPrinterInfo/>
      <x:Scale>80</x:Scale>
      <x:HorizontalResolution>600</x:HorizontalResolution>
      <x:VerticalResolution>600</x:VerticalResolution>
    </x:Print>
    <x:PageBreakZoom>100</x:PageBreakZoom>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
    </x:WorksheetOptions>
    <x:PageBreaks>
     <x:RowBreaks>0</x:RowBreaks>
    </x:PageBreaks>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:AcceptLabelsInFormulas/>
 <x:WindowHeight>11535</x:WindowHeight>
 <x:WindowWidth>19140</x:WindowWidth>
 <x:WindowTopX>120</x:WindowTopX>
 <x:WindowTopY>120</x:WindowTopY>
 </x:ExcelWorkbook>
 <x:SupBook>
   <x:Path>TEST</x:Path>
   <x:SheetName>TEST</x:SheetName>
  </x:SupBook>
</xml>
</head>
!IBI.FIL.NTF_OUT;
-HTMLFORM END
-DONE

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

May 06, 2010, 03:15 PM

Francis Mariani

Try adding

TYPE=HEADING, HEADALIGN=BODY, $

which is required when you want to heading or footing components to span columns in HTML "(and for Excel 2000 output, which uses HTML alignment)".

May 06, 2010, 04:58 PM

Dan Satchell

Unfortunately, HEADALIGN doesn't seem to help. The code below comes close, but, for some reason I cannot decipher, generates an extra, non-spanned column to the left of the spanned heading.

DEFINE FILE CAR
 HEAD1/A150 =
  '<'|'th colspan="4">THIS IS A PRETTY LONG TEST HEADING WHICH SHOULD MAKE COLUMN 1 REALLY WIDE OR FORCE IT TO WRAP<'|'/th>';
END
-*
TABLE FILE CAR
 SUM SEATS
 BY COUNTRY
 BY CAR
 BY MODEL
 HEADING
  "<HEAD1 "
 ON TABLE HOLD AS NTF_OUT FORMAT HTMTABLE
 ON TABLE SET PAGE-NUM OFF
 ON TABLE SET HTMLCSS ON
END
-*
SET HTMLFORMTYPE=XLS
-RUN
-*
-HTMLFORM BEGIN
!IBI.FIL.NTF_OUT ;
-HTMLFORM END

May 06, 2010, 05:43 PM

Francis Mariani

There were some misplaced HTML comment tags in Luminata's example, so I may have misinterpreted the code, but I think this works:

TABLE FILE CAR
PRINT
  CAR
  COUNTRY
  MODEL
BY CAR NOPRINT

HEADING
"THIS IS A PRETTY LONG TEST HEADING WHICH SHOULD MAKE COLUMN 1 REALLY WIDE OR FORCE IT TO WRAP"

ON TABLE HOLD AS NTF_OUT FORMAT HTMTABLE
-*ON TABLE PCHOLD FORMAT EXL2K

ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-*
TYPE=HEADING, HEADALIGN=BODY, $
TYPE=HEADING, LINE=1, JUSTIFY=CENTER, COLSPAN=3, SIZE=14, $
END
-RUN

-IF &RECORDS GT 0 GOTO SHOWRPT;
-MRNOEDIT -HTMLFORM cp_standard/NODATA
-GOTO DONE;
-SHOWRPT
-RUN

SET HTMLFORMTYPE=XLS
-RUN

-HTMLFORM BEGIN
<html xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
 <head>
 <meta http-equiv=Content-Type content="text/html;">
 <meta name=ProgId content=Excel.Sheet>
 <meta name=Generator content="Microsoft Excel 10">
 < !--[if gte mso 9]><xml>
  <o:OfficeDocumentSettings>
-*  <o:ReadOnlyRecommended/>
  <o:DownloadComponents/>
  <o:LocationOfComponents HRef=3D"file:///\\"/>
  </o:OfficeDocumentSettings>
 </xml>< ![endif]-->
<style>
<!--table
  {
  mso-displayed-decimal-separator:"\x";
  mso-number-format:"\.00";
  mso-displayed-thousand-separator:"\,";}
@page
  {mso-footer-data : '&|L Date: &|D Time: &|T &|C Confidential Information &|R Page &|P of &|N';
  margin:.5in .25in .5in .25in;
  mso-header-margin:.25in;
  mso-footer-margin:.25in;
  mso-page-orientation:landscape;
  mso-horizontal-page-align:center; padding-top:0px;
  mso-number-format:"\#\#\,\#\#0\.00";}
-->
  </style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>TEST</x:Name>
    <x:WorksheetOptions>
    <x:FitToPage/>
     <x:Print>
           <x:FitWidth>1</x:FitWidth>
           <x:FitHeight>99</x:FitHeight>
      <x:ValidPrinterInfo/>
      <x:Scale>80</x:Scale>
      <x:HorizontalResolution>600</x:HorizontalResolution>
      <x:VerticalResolution>600</x:VerticalResolution>
    </x:Print>
    <x:PageBreakZoom>100</x:PageBreakZoom>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
    </x:WorksheetOptions>
    <x:PageBreaks>
     <x:RowBreaks>0</x:RowBreaks>
    </x:PageBreaks>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:AcceptLabelsInFormulas/>
 <x:WindowHeight>11535</x:WindowHeight>
 <x:WindowWidth>19140</x:WindowWidth>
 <x:WindowTopX>120</x:WindowTopX>
 <x:WindowTopY>120</x:WindowTopY>
 </x:ExcelWorkbook>
 <x:SupBook>
   <x:Path>TEST</x:Path>
   <x:SheetName>TEST</x:SheetName>
  </x:SupBook>
</xml>
-->
</head>
!IBI.FIL.NTF_OUT;
-HTMLFORM END
-DONE