Search code examples
excelexport-to-excelexcel-2013

Using html for excel 2013 with Custom format for currency

I am exporting data from my site to excel, using HTML.

For currency, i am using the following class:

.DDD {
            mso-number-format: '\0022$\0022\#\,\#\#0\.00';
        }

When I open it in excel 2007-2010 the behavior is OK. (i see the number with the currency, but when i edit the cell, you can see it is a number - so you can use function like Sum() on it) BUT when I open it in excel 2013 I can't use functions, and I see the cell value is "$10.00" (and not 10.00) - so it is NOT a number.

I'm using "$" just for this example, the actual currency is NIS ("₪")

Is there a way to make it work for all excels?

My relevant HTML was:

<table>
 <tr>
  <td class="DDD">
    <span>$</span>
    <span>1234.56</span>
  </td>
 <tr>
</table>

Eventually I removed the span with the currency, and it works fine on all excels.

Solution

  • Can you please show the HTML to this CSS?

    Because if I have:

    <meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">

<style>
.DDD {
 mso-number-format:"\0022₪\0022\#,\#\#0\.00";
}
</style>

<table>
 <tr>
  <td class="DDD">1234.56</td>
 <tr>
</table>

    then the Excel looks like:

    enter image description here

    in Excel 2016. This is a German Excel. So 1234,56 is a number.

    I don't believe that Excel 2013 is as much different with this.

    If the need is also to have the format in browser view, then you could have:

    <meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">

<style>
.DDD {
 mso-number-format: "\0022₪\0022#,##0.00";
 font-size: 2em;
}
.DDD::before {
 content: "₪";
}
.DDD .thousand::before {
 content: ",";
}
</style>

<table>
 <tr>
  <td class="DDD">1<span class="thousand">234</span>.56</td>
 <tr>
</table>

    But better approach will be, strictly separate view requirements from data export requirements. So to have different formats for view and data export. And in conclusion of all considerations, not to use HTML for data export but XML or better the needed format - Excel in this case - directly.