Kevin Humphreys wrote:
Hi There,
I am trying to export a recordset to an excel file using the Content Type
below in the header.
Response.ContentType = "application/vnd.ms-excel"
Which works fine however the problem is that when I have a number in a
column with a leading zero the zero gets dropped.
E.G. 01760942 will be displayed as 1760942
How can I rectify this?
Kevin, you need to create a library of CSS that Excel understands, and
include that in your page.
Below is the entire file I created called "makeexcel.asp" (WATCH FOR
WORD WRAP). To use it, create your file in HTML and verify everything
works in it. Everything should be in ONE table, merging cells where
appropriate. Don't try to put a table into a cell, or multiple merged
cells, as you can't predict the Excel output.
Right before the <table> tag in your file, put
server.execute("makeexcel.asp")
There are several session variables and styles used in this file, and
I'm not going to go over all of them. Any competent developer should
be able to figure most of it out, but here are some highlights:
session("tablename") contains the Excel filename that is generated
There are a bunch of default values which can be overridden by settings
a session variable to a value of the same name. For example,
session("orientation") defaults to landscape, but you could set it to
portrait
Check out all of the defined style sheets. I add to this file as I
need different formats for a cell. The quick way to figure out what
they need to be is to generate a 1 celled Excel file, format the value
however you want it, then do a "save as web page" and examine the
results. Once you get the hang of it, it's pretty easy to figure out
what's going on.
To use the style sheets, you must put them in the <TD> tag of the cell
you want to format... ie. <td class="currency">100</td>
will format 100 as $100.00
Finally, and VERY important, in your ASP file that executes
makeexcel.asp, STRIP OUT ALL <HTML> AND <BODY> tags, otherwise the
style sheets below will be ignored by Excel.
<%
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","filename=" &
session("tablename") &".xls"
function def(val)
if session(val)<>"" then
' response.write(val &" " & session(val) &"<BR>")
execute(val &"=" & session(val))
session(val)=""
end if
end function
'-------------default settings-----------------------------------
fitheight=999
fitwidth=1
orientation="landscape"
lmargin=.25
rmargin=.25
tmargin=.25
bmargin=.75
startrepeatrow=1
freezerows=0
def "fitheight"
def "fitwidth"
def "orientation"
def "lmargin"
def "rmargin"
def "tmargin"
def "bmargin"
def "startrepeatrow"
def "freezerows"
%>
<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<style>
@page
{margin:<%=lmargin%>in <%=rmargin%>in <%=bmargin%>in <%=tmargin%>in;
mso-footer-data:"&LPrinted on &D &T&RPage &P of &N";
mso-header-margin:.25in;
mso-footer-margin:.25in;
mso-page-orientation:<%=orientation%>;
mso-horizontal-page-align:center;}
td {font-size:10px;white-space:normal;}
br {mso-data-placement:same-cell;}
tr {mso-height-source:auto;}
..currency { mso-number-format:"_ $ #,##0.00"}
..currencybold {mso-number-format:"_ $ #,##0.00";font-weight:700;}
..number2dec {mso-number-format: Fixed;}
..number4dec {mso-number-format:"0\.0000"}
..number4decbold {mso-number-format:"0\.0000"; font-weight:700}
..number2decbold {mso-number-format: Fixed; font-weight:700;}
..text {mso-number-format:General; text-align:general;white-space:
normal;mso-spacerun: yes }
..textnowrap {mso-number-format:General; text-align:general;white-space:
nowrap; mso-spacerun: yes }
..num2text {mso-style-parent:text; mso-number-format:"\@";white-space:
normal}
..shortdate{mso-number-format:"Short Date"; white-space:normal;}
..monthyear {mso-number-format:"\[ENG\]\[$-409\]mmm\\ yyyy\;\@";}
..rotateup {mso-rotate:90}
..excel_bottom_border{border-bottom-style: solid; border-bottom-width:
..5pt}
..excel_top_border{border-top-style: solid; border-top-width: .5pt}
..teds_cell_medgray {background-color: #C0C0C0}
</style>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<% if freezerows>0 then%>
<x:FreezePanes/>
<x:FrozenNoSplit/>
<x:SplitHorizontal><%=freezerows%></x:SplitHorizontal>
<x:TopRowBottomPane><%=freezerows%></x:TopRowBottomPane>
<x:ActivePane>2</x:ActivePane>
<%end if%>
<x:DoNotDisplayGridlines/>
<x:CodeName>Sheet1</x:CodeName>
<x:FitToPage/>
<x:Print>
<x:ValidPrinterInfo/>
<x:Scale>10</x:Scale>
<%if fitheight<>"" then%><x:FitHeight><%=fitheight%></x:FitHeight><%end
if%>
<x:FitWidth><%=fitwidth%></x:FitWidth>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>
</x:Print>
</x:WorksheetOptions>
<x:AutoFormatWidth/>
</x:ExcelWorksheet>
<x:AutoFormatWidth/>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
<%if session("rowstorepeat")>0 then%>
<x:ExcelName>
<x:Name>Print_Titles</x:Name>
<x:SheetIndex>1</x:SheetIndex>
<x:Formula>=Sheet1!$<%=startrepeatrow%>:$<%=sessio n("rowstorepeat")+startrepeatrow-1%></x:Formula>
</x:ExcelName>
<%end if%>
</xml>
