{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 何必那么认真 的问题《Excel doesn't apply mso-number-format from HTM》','https://www.manongdao.com/q-886174.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
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:
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> </td>
<td> </td>
<td> </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:
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?
Just FYI, I have done this and it works great.
you can probably try any number format this way as well.
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:ssformats with correctmso-number-formatparameter defined instylessection (mso-number-format:"mm\\/dd\\/yyyy"in my case).I've had the same problem with numerical formats. Apparently excel does not recognize more than one class for formatting purpose, so if you specify
<td class="cls1 cls2">123</td>and you have<style>.cls1 {mso-number-format:"\#\.00"}</style>– that won't work. You need to use<td class="cls1">123</td>, withoutcls2. That was a good enough solution for me, you might also want to try<td style='mso-number-format:"\#\.00"' class='cls2'>123</td>, not sure if that'll work.