HTML XML to Excel via PHP - Convert to Number

Question

I am trying to convert html to excel without using any library.

I just encountered one problem.... I have the classes ready etc etc (basically I exported an excel to html) and is now doing it vice versa [just changing the content].

I am formatting my currency/totals as numbers with 2 decimals. (when you right click them it says so at least) but in the excel it still shows as a Text and in order to work with them it requires me to press Convert to Number

At the moment I have this in my loop. Do you think its enough ?

<td class=xl112 align=right style='border-top:none;border-left:none'>2,033.00
</td>

and in the css I have

.xl112
    {mso-style-parent:style0;
    mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\\-\#\,\#\#0\.00\\ ";
    vertical-align:middle;
    border-top:.5pt solid windowtext;
    border-right:1.0pt solid windowtext;
    border-bottom:.5pt solid windowtext;
    border-left:1.0pt solid windowtext;
    background:#FFFF99;
    mso-pattern:auto none;}

Marc B · Accepted Answer · 2011-09-06 15:35:45Z

2

You're using HTML as the "excel" file - everything in HTML is automatically text. This is especially true when you're "pretty printing" your numbers with thousands separators.

If you want your numbers treated as numbers, use a real Excel file, generated by PHPExcel. There you specify EXACTLY what the data type in a cell is. You say you don't want to use a library, but you'd be better off if you actually did.

answered Sep 6, 2011 at 15:35

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BoqBoq Over a year ago

you are right I should use PHPEXcel but I cannot since its a standard from where I work so I have to stick to the traditional way

Tim Williams · Accepted Answer · 2011-09-06 16:48:48Z

1

This worked for me. Not sure why you have those backslashes in your number format style?

See here: http://codesnipers.com/?q=excel-compatible-html

Full reference: http://msdn.microsoft.com/en-us/library/Aa155477(office.10).aspx

<html xmlns:o="urn:schemas-microsoft-com:office:office" 
      xmlns:x="urn:schemas-microsoft-com:office:excel" 
      xmlns="http://www.w3.org/TR/REC-html40">

<head>
    <style>
        .s1 {mso-number-format:"#,##0.00_ ;[Red]-#,##0.00";}
    </style>
</head>



 <body>
  <table>
      <tr>
        <td class="s1" x:num="2033">2033</td>
      </tr>
  </table>
 </body>
</html>

answered Sep 6, 2011 at 16:48

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

HTML XML to Excel via PHP - Convert to Number

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related