When exporting to excel....


S

Sonnich

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

Advertisements

M

Mike Brind

Sonnich said:
...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 :-\).
 
L

Larry Bud

Sonnich said:
...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}
 
L

Larry Bud

Sonnich said:
...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".
 
Ad

Advertisements

S

Sonnich

Larry said:
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
 

Top