Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export a Formatted Table to Excel using HTML

Usefull Functions & Procedures

Export a Formatted Table to Excel using HTML

by  baltman  Posted    (Edited  )
Export a Table to Excel with Formatting using HTML

Supports Numeric, Date and Character fields, formats number fields wider than 4 with commas and auto decimals based on data table. Just replace "MyTable" with yours.

You can define your own data formats too. To get the definition(s), save a pre-formatted Excel file as HTML and open with notepad and 'borrow' the definition.

Brian

Code: 
table_2_excel_with_html([MyTable])

PROCEDURE table_2_excel_with_html
LPARAMETERS lcTargetTable

IF USED(lcTargetTable)=.f.
USE (lcTargetTable) IN 0
ENDIF

SELECT (lcTargetTable)

lcDir=JUSTPATH(FULLPATH(lcTargetTable))
lcTextMergeFile=JUSTSTEM(lcTargetTable)+[.html]
lcTextMergeFileFull=lcDir + [\] + lcTextMergeFile

SET CONSOLE OFF
SET TEXTMERGE ON TO [&lcTextMergeFile] NOSHOW

\<HTML>
\<HEAD>
\<META http-EQUIV='Content-Type' CONTENT='text/html; charset=windows-1252'>
\<META NAME='Generator' CONTENT='VFP'>
\<TITLE>a</TITLE>
\</HEAD>
\<BODY>
\<TABLE CELLSPACING=0 BORDER=0 CELLPADDING=3 WIDTH=264>
\<TR><TD WIDTH='100%' VALIGN='TOP'>
\    <style>
\<!--table

&&Characters
\.xlChr
\ {mso-style-parent:style0;
\ font-family:Arial, sans-serif;
\ mso-font-charset:0;
\ text-align:right;
\ white-space:normal;}

&& number no dec
\.xlN0
\ {mso-style-parent:style0;
\ mso-number-format:0;
\ white-space:normal;}

&& number 1 dec
\.xlN1
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.0<<CHR(34)>>;
\ white-space:normal;}

&& number 2 dec
\.xlN2
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.00<<CHR(34)>>;
\ white-space:normal;}

&& number 3 dec
\.xlN3
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.000<<CHR(34)>>;
\ white-space:normal;}

&& number 4 dec
\.xlN4
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.0000<<CHR(34)>>;
\ white-space:normal;}


&& number 5 dec
\.xlN5
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.00000<<CHR(34)>>;
\ white-space:normal;}

&& number 6 dec
\.xlN6
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.000000<<CHR(34)>>;
\ white-space:normal;}

&& number 7 dec
\.xlN7
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.0000000<<CHR(34)>>;
\ white-space:normal;}

&& number 8 dec
\.xlN8
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.00000000<<CHR(34)>>;
\ white-space:normal;}

&& number 9 dec
\.xlN9
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>0\.000000000<<CHR(34)>>;
\ white-space:normal;}

&& number 0 dec and comma
\.xlNC0
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0<<CHR(34)>>;
\ white-space:normal;}

&& number 1 dec and comma
\.xlNC1
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.0<<CHR(34)>>;
\ white-space:normal;}

&& number 2 dec and comma
\.xlNC2
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.00<<CHR(34)>>;
\ white-space:normal;}

&& number 3 dec and comma
\.xlNC3
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.000<<CHR(34)>>;
\ white-space:normal;}

&& number 4 dec and comma
\.xlNC4
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.0000<<CHR(34)>>;
\ white-space:normal;}

&& number 5 dec and comma
\.xlNC5
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.00000<<CHR(34)>>;
\ white-space:normal;}

&& number 6 dec and comma
\.xlNC6
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.000000<<CHR(34)>>;
\ white-space:normal;}

&& number 7 dec and comma
\.xlNC7
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.0000000<<CHR(34)>>;
\ white-space:normal;}

&& number 8 dec and comma
\.xlNC8
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.00000000<<CHR(34)>>;
\ white-space:normal;}

&& number 9 dec and comma
\.xlNC9
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\#\,\#\#0\.000000000<<CHR(34)>>;
\ white-space:normal;}

&& currency 0 dec
\.xlC0
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\0022$\0022\#\,\#\#0<<CHR(34)>>;}

&& currency 1 dec
\.xlC1
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\0022$\0022\#\,\#\#0\.0<<CHR(34)>>;}

&& currency 2 dec
\.xlC2
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\0022$\0022\#\,\#\#0\.00<<CHR(34)>>;}

&&.xlC3 is currency 3 dec
\.xlC3
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\0022$\0022\#\,\#\#0\.000<<CHR(34)>>;}

&&.xlC4 is currency 4 dec
\.xlC4
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>\0022$\0022\#\,\#\#0\.0000<<CHR(34)>>;}

\.xlDate
\ {mso-style-parent:style0;
\ mso-number-format:<<CHR(34)>>mm\/dd\/yyyy<<CHR(34)>>;}
\ -->
\ </style>
\\<BODY><TABLE>
\<tr>

FOR lnField = 1 to AFIELDS(laFields) &&headers
 \<td class=xlChr><<laFields(m.lnField,1)>><\td>
ENDFOR
\</tr>

SCAN
  FOR lnField = 1 to AFIELDS(laFields)
 
 DO CASE &&Add Formats
  CASE (laFields(m.lnField,2)=[N] OR laFields(m.lnField,2)=[I] OR ;
   laFields(m.lnField,2)=[B]) AND laFields(m.lnField,3)<=3&&Number/Decimal
   \<td class=xlN<<laFields(m.lnField,4)>>>
  
  CASE (laFields(m.lnField,2)=[N] OR laFields(m.lnField,2)=[I] OR ;
   laFields(m.lnField,2)=[B]) AND laFields(m.lnField,3)>3&&Commas/Decimal
   \<td class=xlNC<<laFields(m.lnField,4)>>>
     
  CASE laFields(m.lnField,2)=[D] OR laFields(m.lnField,2)=[T]&&Date
   \<td class=xlDate>

  CASE laFields(m.lnField,2)=[Y] &&Currency (US)
   \<td class=xlC2>

  OTHERWISE &&Character
   \<td class=xlChr>

 ENDCASE
  &&STRTRAN allows html tags to work
      \\<<RTRIM(TRANSFORM(EVALUATE(FIELD(m.lnField))))>><\td>
   
  ENDFOR
\</tr>
ENDSCAN
\</table></body></html>
SET TEXTMERGE OFF
SET TEXTMERGE TO
SET CONSOLE ON

&&Open HTML w/excel
lcOldError = On([ERROR])
On Error loExcel = .Null.
loExcel = Createobject([Excel.Application])
On Error &lcOldError

With loExcel
.WorkBooks.Open(lcTextMergeFileFull)
.ActiveWindow.DisplayGridlines = .t.
.visible=.t.
ENDWITH
ENDPROC
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top