Excel不会从HTML应用mso-number-format [英] Excel doesn't apply mso-number-format from HTML
问题描述
p>
使用非常简单的HTML刷新服务器响应：
< html xmlns ：o =urn：schemas-microsoft-com：office：officexmlns：x =urn：schemas-microsoft-com：office：excelxmlns =http://www.w3.org/TR/REC- HTML40\" >
< head>
< style id =Leads_style>
表
{
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_Leadsx：publishsource =Excel>
< tr>
< td>标题：< / td>
< td colspan =2style =white-space：nowrap> Leads< / td>
< / tr>
< tr>
< td>日期：< / td>
< td align =leftcolspan =2> 2014年8月27日08:02上午+0：00 GMT< / td>
< / tr>
< tr>
< td>& nbsp;< / td>
< td>& nbsp;< / td>
< td>& nbsp;< / td>
< / tr>
< / table>
< table id =tbl_3_Leadsx：publishsource =Excel>
< tr>
< td>显示名称< / td>
< td>创建日期< / td>
< td>上次修改日期< / td>
< / tr>
< tr>
< td class =cs0x：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 =cs1> 08/06/2014< / td>
< / tr>
< tr>
< td class =cs0x：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 =cs0x：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>
请注意，style部分中的.cs1类具有 mso-number-format： mm \ / dd \ / yyyy ，所以Excel文档中的所有日期应该像 08/27/2014 。
由于不明原因，Excel对日期单元格应用了错误的格式（ MM.DD.YYYY ）：
看起来正常的两个单元也有 MM.DD.YYYY 格式，但Excel显示为 MM / DD / YYYY 左对齐，因为DD值远远大于最大值的MM值（是的，这真的很奇怪）。
请帮助。我在做什么错了？
好的，我终于找到了解决方案。 b $ b
由于未知的原因，当从HTML中检索刷新的数据时，Excel不理解适当的ISO 8601格式。所有日期时间应该在 yyyy-MM-dd / yyyy-MM-dd HH：mm：ss / HH：mm：ss 具有正确 mso-number-format code>部分（ mso-number-format：mm \\ / dd\\ / yyyy在我的情况中）。
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?
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).
这篇关于Excel不会从HTML应用mso-number-format的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
