Phenomenon:
Yes, a company system that adds a new feature that allows users to select his prefer time format when a page is generated, with the following four time formats to choose from:
Default OPTION:YYYY-MM-DD
Descriptive (1-jan-07)
Short Date:mm/dd/yy
Short Date:dd/mm/yy
In addition, the user can select whether the report is in page form or exported as an Excel file. The original processing displayed a time format on the page without any problems, but yesterday found that if you export Excel, the time format is wrong: (
Reason:
After this half a day of groping, finally have some clue, the following first posted the information I found on this:
---------------------------I am the legend of the gorgeous split line lala ~ ~ ~ ~----------------------------
When we take data from a Web page into Excel, sometimes our data needs to be presented in a specific format, and we need to add some style specifications to the cell.
First, let's take a look at how Excel derives from a Web page. When we send this data to the client, we want the client program (browser) to read it in Excel format, so the MIME type is set to: application/ Vnd.ms-excel, when Excel reads a file, it renders the data in the format of each cell, and if the cell does not have a specified format, Excel renders the cell's data in the default format. This gives us the space to customize the data format, and of course we have to use the format that Excel supports. Here's a list of some of the most commonly used formats:
1) Text: vnd.ms-excel.numberformat:@
2) Date: VND.MS-EXCEL.NUMBERFORMAT:YYYY/MM/DD
3) Number: vnd.ms-excel.numberformat:#,# #0.00
4) Currency: vnd.ms-excel.numberformat:￥#,# #0.00
5) Percent: Vnd.ms-excel.numberformat: #0 0%
These formats you can also customize, such as years you can define as: yy-mm and so on. So how do you add these formats to the cell when you know these formats? Quite simply, we just need to add the style to the corresponding label pair (that is, the closing tag). such as <td></td>, add a style to the label <td></td>, as follows:
<TD style= "vnd.ms-excel.numberformat:@" >410522198402161833</td>
Also, we can add styles to <div></div> and add styles to <tr></tr>,<table></table>, which introduces a problem Did you notice? First look at the following code:
<table style= ' vnd.ms-excel.numberformat:#,# #0 ' >
<tr>
<td>542</td>
<td style= ' Vnd.ms-excel.numberformat: #0.00% ' >0.25</td>
</tr>
</table>
Yes, when we add a style to both the parent tag pair and the sub tag pair, which style does the data render? After testing, it is presented in a way that is closest to the data, which is also in line with our wishes (as if it were a proverb: The magistrate is inferior to the present). This allows us to change the way the data is rendered in Excel by changing the style (which you can add to the foreground page or to the corresponding controls such as the DataGrid) in the background code. If your application is simple, then this is enough to meet your needs. But if your application is more complex, you can also take a way to achieve different data rendering effects. Next, I'll give you a slightly more complicated application.
For example: Your data to be presented to different countries and regions of the user view, so that the format of the data will be presented differently, so how do we solve this problem? Of course, you can do these data by hand. But this is not the best way, because if we add a user to another country or region, then we need to process all the data in a customer-requested format, which is a heavy and tedious task when the volume of data is large. So how exactly should we solve such a problem? Let me say, my own opinion: The formatted information is extracted into an XML file, which reads different formatted information according to different customers and then dynamically adds the formatted information to our data, so that when we add a user to another country or region, We only need to add an additional XML file, the corresponding format information to the XML file, and then when the user of this country or region to view, the corresponding format of information read out to apply to the data can be.
The above example is a sudden thought, I believe that multinational companies will encounter similar problems, the solution is only to provide you with a train of thought, hoping to play a good effect.
Datagrid:
asp.net webform when the DataGrid is exported, within the ItemDataBound
if (E.item.itemtype = = ListItemType.Item | | e.item.itemtype = = listitemtype.alternatingitem)
{
E.item.cells[0]. Attributes.Add ("Style", "vnd.ms-excel.numberformat:@");
}
If within the WinForm you can
Excel.Range Range = (excel.range) worksheet. cells[1,1];
Range. NumberFormat = Excel.XlParameterDataType.xlParamTypeUnknown;
Vb:
If E.item.itemtype = ListItemType.Item Or e.item.itemtype = ListItemType.AlternatingItem Then
E.item.cells (1). Attributes.Add ("Style", "vnd.ms-excel.numberformat:@")
End If
If it is a label, add it in the <td> style:
<TD style= "HEIGHT:22PX; vnd.ms-excel.numberformat:@ "bgcolor=" #eff3fb ">
<asp:label id= "Label2" runat= "Server" text= "Label" ></asp:Label></td>
If it is a GridView, (07.12.13 Update)
You need to add two words to the Print spooler Click event Code:
More Codes
string style = @ "<style>. text {mso-number-format:\@;} </script>";
Response.Write (style);
Response.Output.Write (Ostringwriter.tostring ());
---------------------------------------------------------------
Let's start with the process of dealing with this problem:
First, add the style attribute to the <td></td> tab, as described in the previous article, and set it according to the time format that the user chooses:
<TD valign=top style= "vnd.ms-excel.numberformat:<%=dateformat%>" >...</td>
Then start testing, the results of the first three time format is no problem, the last one: Dd/mm/yy, some time right, some wrong, the wrong time is dd>12 those. Why so, after repeated testing and pondering, finally understand: Originally, when we use the Export data to Excel, Excel will be a spanner! Not only that, we can try to enter the time in Excel, no matter what format you enter, it will force this time into the mm/dd/yyyy format. I passed the date (for example: 30/10/08) to its side, it is silly, because it does not know, and then in the cell display, the honest Show as 30/10/08, also count fluke (in fact, is wrong). But what if it's 1/10/08 (this is the DD/MM/YY format that I've converted)? It does not matter that this (1/10/08) is mm/dd/yy format, no matter 3,721, converted to 1/10/2008, note that this (1/10/2008) means that January 10, 2008, has completely deviated from my will: (
Forgot to say one thing, when we retrieve date data from a database, the format is based on the formats that the user chooses (the 4 formats listed above). Then I think of a trick, that is, if the user chooses the DD/MM/YY format, I will not format date as Dd/mm/yy, but mm/dd/yy, so that when the data arrives in Excel it can be distinguished, and as I specify the <td> Style property, Excle will be displayed strictly in that format (regardless of the time format displayed on Excel, when you edit the time, you will see that the format you can edit is always mm/dd/yyyy).
So far, the problem has been satisfactorily resolved.
