Exporting to excel

Discussion in 'ASP General' started by Kevin Humphreys, Jan 24, 2006.

  1. 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.
     
    Kevin Humphreys, Jan 24, 2006
    #1
    1. Advertising

  2. Kevin Humphreys

    Guest

    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, 2006
    #2
    1. Advertising

  3. 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.

    <> wrote in message
    news:...
    > 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, Jan 24, 2006
    #3
  4. "Kevin Humphreys" <> wrote in message
    news:%...
    > 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/windowsserver2003/community/centers/iis/
    http://mvp.support.microsoft.com/
    http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS
     
    Tom Kaminski [MVP], Jan 24, 2006
    #4
  5. Kevin Humphreys

    Guest

    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, 2006
    #5
  6. Kevin Humphreys

    Larry Bud Guest

    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:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
    xmlns:x="urn:schemas-microsoft-com:eek:ffice: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%>:$<%=session("rowstorepeat")+startrepeatrow-1%></x:Formula>
    </x:ExcelName>
    <%end if%>
    </xml>
     
    Larry Bud, Jan 24, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ravindra
    Replies:
    0
    Views:
    787
    Ravindra
    Dec 14, 2005
  2. Marshal Antony

    Re: Exporting to Excel using CSS

    Marshal Antony, Jan 12, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    446
  3. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,431
    Carl Prothman [MVP]
    Jan 12, 2005
  4. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,602
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. Doogie
    Replies:
    1
    Views:
    483
    sloan
    Nov 19, 2008
Loading...

Share This Page