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.Conten tType = "applicatio n/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@yahoo.com]

Re: Exporting to excel

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]

Re: Exporting to excel

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.

<michdoh@yahoo. com> wrote in message
news:1138096205 .081836.32580@o 13g2000cwo.goog legroups.com...[color=blue]
> 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
>[/color]

[Tom Kaminski [MVP]]

Re: Exporting to excel

"Kevin Humphreys" <khumphreys@hot mail.com> wrote in message
news:%23rqwWUNI GHA.3904@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> 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?[/color]

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/windowsserver2003/community/centers/iis/

http://mvp.support.microsoft.com/

http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS

[michdoh@yahoo.com]

Re: Exporting to excel

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]

Re: Exporting to excel


Kevin Humphreys wrote:[color=blue]
> Hi There,
>
> I am trying to export a recordset to an excel file using the Content Type
> below in the header.
>
> Response.Conten tType = "applicatio n/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?[/color]

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("tablen ame") 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("orient ation") 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.CacheC ontrol = "no-cache"
Response.AddHea der "Pragma", "no-cache"
Response.Expire s = -1

Response.Conten tType = "applicatio n/vnd.ms-excel"
Response.AddHea der "Content-Disposition","f ilename=" &
session("tablen ame") &".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="la ndscape"
lmargin=.25
rmargin=.25
tmargin=.25
bmargin=.75
startrepeatrow= 1
freezerows=0


def "fitheight"
def "fitwidth"
def "orientatio n"
def "lmargin"
def "rmargin"
def "tmargin"
def "bmargin"
def "startrepeatrow "
def "freezerows "

%>
<html xmlns:v="urn:sc hemas-microsoft-com:vml"
xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
xmlns:x="urn:sc hemas-microsoft-com:office:exce l"
xmlns="http://www.w3.org/TR/REC-html40">

<style>
@page
{margin:<%=lmar gin%>in <%=rmargin%>i n <%=bmargin%>i n <%=tmargin%>i n;
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 "}
..number4decbol d {mso-number-format:"0\.0000 "; font-weight:700}
..number2decbol d {mso-number-format: Fixed; font-weight:700;}
..text {mso-number-format:General; text-align:general;w hite-space:
normal;mso-spacerun: yes }
..textnowrap {mso-number-format:General; text-align:general;w hite-space:
nowrap; mso-spacerun: yes }
..num2text {mso-style-parent:text; mso-number-format:"\@";whi te-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_bor der{border-top-style: solid; border-top-width: .5pt}
..teds_cell_med gray {background-color: #C0C0C0}

</style>
<xml>
<x:ExcelWorkboo k>
<x:ExcelWorkshe ets>
<x:ExcelWorkshe et>
<x:Name>Sheet 1</x:Name>
<x:WorksheetOpt ions>
<% if freezerows>0 then%>
<x:FreezePane s/>
<x:FrozenNoSpli t/>
<x:SplitHorizon tal><%=freezero ws%></x:SplitHorizont al>
<x:TopRowBottom Pane><%=freezer ows%></x:TopRowBottomP ane>
<x:ActivePane>2 </x:ActivePane>
<%end if%>
<x:DoNotDisplay Gridlines/>
<x:CodeName>She et1</x:CodeName>
<x:FitToPage/>
<x:Print>
<x:ValidPrinter Info/>
<x:Scale>10</x:Scale>
<%if fitheight<>"" then%><x:FitHei ght><%=fitheigh t%></x:FitHeight><%e nd
if%>
<x:FitWidth><%= fitwidth%></x:FitWidth>
<x:HorizontalRe solution>600</x:HorizontalRes olution>
<x:VerticalReso lution>600</x:VerticalResol ution>
</x:Print>
</x:WorksheetOpti ons>
<x:AutoFormatWi dth/>
</x:ExcelWorkshee t>
<x:AutoFormatWi dth/>
</x:ExcelWorkshee ts>
</x:ExcelWorkbook >
<%if session("rowsto repeat")>0 then%>
<x:ExcelName>
<x:Name>Print_T itles</x:Name>
<x:SheetIndex>1 </x:SheetIndex>

<x:Formula>=She et1!$<%=startre peatrow%>:$<%=s ession("rowstor epeat")+startre peatrow-1%></x:Formula>
</x:ExcelName>
<%end if%>
</xml>

[jackster]

RE: Exporting to excel

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