469,632 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

Exporting to excel

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.
Jan 24 '06 #1
6 12132
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

Jan 24 '06 #2
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.

<mi*****@yahoo.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
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

Jan 24 '06 #3
"Kevin Humphreys" <kh********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.

--
Tom Kaminski IIS MVP
http://www.microsoft.com/windowsserv...y/centers/iis/
http://mvp.support.microsoft.com/
http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS
Jan 24 '06 #4
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

Jan 24 '06 #5

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>

Jan 24 '06 #6
Remember in Excel it will drop a leading 0 (zero) unless the cell is formatted as text.
That is probably the simplest way to correct the issue in Excel.

From http://www.developmentnow.com/g/62_2...g-to-excel.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Aug 4 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Neil | last post: by
2 posts views Thread by Mustufa Baig | last post: by
1 post views Thread by Mustufa Baig | last post: by
4 posts views Thread by Tom | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.