When exporting to excel....

Discussion in 'ASP General' started by Sonnich, Aug 1, 2006.

  1. Sonnich

    Sonnich Guest

    ....is it the posssible to:

    format a field as "currency" or "number"?
    format a field as a formula?

    I create my sheet as a table
    Response.ContentType = "application/vnd.ms-excel"
    response.write('<tr><td>blahblah</td></tr>');

    is there any other way?

    BR
    Sonnich
     
    Sonnich, Aug 1, 2006
    #1
    1. Advertising

  2. Sonnich

    Mike Brind Guest

    Sonnich wrote:
    > ...is it the posssible to:
    >
    > format a field as "currency" or "number"?
    > format a field as a formula?
    >
    > I create my sheet as a table
    > Response.ContentType = "application/vnd.ms-excel"
    > response.write('<tr><td>blahblah</td></tr>');
    >
    > is there any other way?
    >
    >


    Yes. Create an .xls file containing the various formats and formulas
    you want, then save it as a web page. Open it in an html editor (or
    even notepad) and pick about among the various css declarations, paying
    attention to things like this: x:num x:fmla="=SUM(A1/B1)" to be found
    in td styles. You're sure to find that bits that control formatting
    and formulas that way.

    When you response.Write your asp, you need to keep all the html that
    Excel produces, just making the cell values (or formulas) dynamic.
    However, you can run into problems, as I don't believe this produces a
    true .xls file. For example, while it appears to act as an .xls file
    most of the time, I've had problems in the past getting the Mail Merge
    functionality in Word 2003 to recognise it as a genuine .xls file
    (although Word 2000 seems to like it :-\).

    --
    Mike Brind
     
    Mike Brind, Aug 1, 2006
    #2
    1. Advertising

  3. Sonnich

    Larry Bud Guest

    Sonnich wrote:
    > ...is it the posssible to:
    >
    > format a field as "currency" or "number"?
    > format a field as a formula?
    >
    > I create my sheet as a table
    > Response.ContentType = "application/vnd.ms-excel"
    > response.write('<tr><td>blahblah</td></tr>');
    >
    > is there any other way?


    Yes. Here are the styles that I've built up for exactly this purpose:


    ..currency {mso-number-format:Currency}
    ..currencybold { mso-number-format:Currency;font-weight:700}

    ..number0dec {mso-number-format:"0"}
    ..number2dec {mso-number-format: Fixed;}
    ..number4dec {mso-number-format:"0\.0000"}
    ..number6dec {mso-number-format:"0\.000000"}
    ..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;}

    ..MMYY {mso-number-format:"MM/YY"}

    ..MMDDYY {mso-number-format:"m/d/yy"}

    ..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}
     
    Larry Bud, Aug 1, 2006
    #3
  4. Sonnich

    Larry Bud Guest

    Sonnich wrote:
    > ...is it the posssible to:
    >
    > format a field as "currency" or "number"?
    > format a field as a formula?
    >
    > I create my sheet as a table
    > Response.ContentType = "application/vnd.ms-excel"
    > response.write('<tr><td>blahblah</td></tr>');
    >
    > is there any other way?


    I didn't answer all of your questions. For the formula, you don't need
    to use the fmla tag. Just put the formula as the cell contents, e.g.

    <td>=sum(a1:a4)</td>

    Microsoft had documentation on all of this, but when I just searched, I
    cannot find it. It's called "Microsoft Office 2000 HTML and XML
    Reference".
     
    Larry Bud, Aug 1, 2006
    #4
  5. Sonnich

    Sonnich Guest

    Larry Bud wrote:
    > Sonnich wrote:
    > > ...is it the posssible to:
    > >
    > > format a field as "currency" or "number"?
    > > format a field as a formula?
    > >
    > > I create my sheet as a table
    > > Response.ContentType = "application/vnd.ms-excel"
    > > response.write('<tr><td>blahblah</td></tr>');
    > >
    > > is there any other way?

    >
    > Yes. Here are the styles that I've built up for exactly this purpose:
    >
    > .number0dec {mso-number-format:"0"}
    > .number2dec {mso-number-format: Fixed;}
    > .number4dec {mso-number-format:"0\.0000"}
    > .number6dec {mso-number-format:"0\.000000"}
    > .number4decbold {mso-number-format:"0\.0000"; font-weight:700}
    > .number2decbold {mso-number-format: Fixed; font-weight:700;}


    Thanks a lot for the other answer, the almost completed my current
    task.

    At first I did not get this :) But I got it at last

    <STYLE TYPE="text/css"><!--
    .number2dec {mso-number-format: Fixed;}
    --></STYLE>

    and

    "<tr><td class=""number2dec"">5,666 blahblah

    Thanks a bunch
    Sonnich
     
    Sonnich, Aug 2, 2006
    #5
    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:
    774
    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:
    434
  3. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,397
    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,541
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. Doogie
    Replies:
    1
    Views:
    456
    sloan
    Nov 19, 2008
Loading...

Share This Page