Excel doesn't apply mso-number-format from HTML

html excel formatting cell

19,201

Ok, I've finally found the solution.

For unknown reason, Excel doesn't understand proper ISO 8601 format when retrieving refreshed data from HTML. All datetimes should be passed in yyyy-MM-dd / yyyy-MM-dd HH:mm:ss / HH:mm:ss formats with correct mso-number-format parameter defined in styles section (mso-number-format:"mm\\/dd\\/yyyy" in my case).

19,201

Author by

Vladimir Panchenko

Updated on June 04, 2022

Comments

Vladimir Panchenko about 2 years

Here is my situation: I have simple Excel OOXML file with Web Query connection to my server. All cells with dates have "General" horizontal alignment (no alignment) and MM/DD/YYYY format. It looks like this:

Before refresh

On refresh server responses with pretty straightforward HTML:

<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 id="Leads_style">
        table
        {
            mso-displayed-decimal-separator:"\.";
            mso-displayed-thousand-separator:"\,";
        } 
        .cs0 { mso-number-format:\@; }
        .cs1 { mso-number-format:mm\/dd\/yyyy; }
      </style>
   </head>
   <body>
      <table id="tbl_0_Leads" x:publishsource="Excel">
         <tr>
            <td>Title:</td>
            <td colspan="2" style="white-space:nowrap">Leads</td>
         </tr>
         <tr>
            <td>Date:</td>
            <td align="left" colspan="2">27 Aug 2014 08:02 AM +0:00 GMT</td>
         </tr>
         <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
         </tr>
      </table>
      <table id="tbl_3_Leads" x:publishsource="Excel">
         <tr>
            <td>Display Name</td>
            <td>Created Date</td>
            <td>Last Modified Date</td>
         </tr>
         <tr>
            <td class="cs0" x:str="Darrow Mag, Mrs.">Darrow Mag, Mrs.</td>
            <td class="cs1">04/23/2009</td>
            <td class="cs1">08/06/2014</td>
         </tr>
         <tr>
            <td class="cs0" x:str="q q, Prof.">q q, Prof.</td>
            <td class="cs1">06/04/2014</td>
            <td class="cs1">08/06/2014</td>
         </tr>
         <tr>
            <td class="cs0" x:str="dasd dsa, Dr.">dasd dsa, Dr.</td>
            <td class="cs1">06/16/2014</td>
            <td class="cs1">08/06/2014</td>
         </tr>
         <tr>
            <td class="cs0" x:str="Bouat Jerome, Dr.">Bouat Jerome, Dr.</td>
            <td class="cs1">08/12/2014</td>
            <td class="cs1">08/12/2014</td>
         </tr>
      </table>
   </body>
</html>

Notice that .cs1 class in style section has mso-number-format:mm\/dd\/yyyy, so all dates in Excel document should look like 08/27/2014.

For unknown reason, Excel applies wrong format (MM.DD.YYYY) to date cells: After refresh

Two cells that seem normal also have MM.DD.YYYY format, but Excel shows them as MM/DD/YYYY with left alignment because DD value is much more than maximum of MM value (yeah, that's really weird too).

Please help. What am I doing wrong?