Exporting to excel

[Kevin Humphreys]

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?

Thanks,
Kevin.

 

[michdoh]

HI

This is how Excel deals with numbers. You could save this value as
text, Excel should allow you to convert this manually if you need a
numerical value. I doubt if you will be able to format the column
however putting a single quote (one of these ' ) before a numerical
value will save it as text and keep the leading zero.

Mikey

 

[Kevin Humphreys]

Hi Mikey,
I tried that. However the asp page is writing out the results into a table
and this is then exporting to the excel file.
When I use the (') before the zero the column value is displayed as
('01760942 ) instead of (01760942 ).

Anything else I can try?

Thanks,
Kevin.

 

[Tom Kaminski [MVP]]

Hi Mikey,
I tried that. However the asp page is writing out the results into a table
and this is then exporting to the excel file.
When I use the (') before the zero the column value is displayed as
('01760942 ) instead of (01760942 ).

Anything else I can try?

I like to use the "reverse engineering" method. Create the spreadsheet in
Excel first, formatted the way you want, then save it as HTML. Look at the
resulting HTML code to figure out what you need to do in your ASP to get the
desired results.

 

[michdoh]

Hi kevin

I know its a total excel hack here but if you want to remove the '
after the file is imported

A simple way to do it is to create a new column and add this formula
=upper(A2) (or whichever is your starting cell).
As there is no upper balue for the ' it's removed, as this is text the
leading value is retained.
Double click the fill handle to complete the column.
Copy the column and use the edit-paste special feature to paste the
values (not the formula) of this calculation back on top of themselves.
(This leaves the string)
Delete the original column ans the spreadsheet is good to go.

NOT eligant and I'm sure you'll get a dot net solution but it would
work in the interim

Mikey

 

[Larry Bud]

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="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice: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%>
<xoNotDisplayGridlines/>
<x:CodeName>Sheet1</x:CodeName>
<x:FitToPage/>
<xrint>
<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>
</xrint>
</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%>:$<%=session("rowstorepeat")+startrepeatrow-1%></x:Formula>
</x:ExcelName>
<%end if%>
</xml>