ASP. NET export excel/csv text Format data

Just started to do Excel related projects, so the problems encountered regardless of size are given to record

Accidental opportunity to add data when the full change to the number, the output of the time automatically turned into a scientific counting method, which is the powerful function of Excel, can be automatic recognition of numbers and strings, too clever, but some trouble, like if you enter the ID card (18 digits) then it is not. More than 11 people, and we've got some information to summarize the solution:

Method 1: Add a single quotation mark to the front of the data when adding data to Excel, mine is this method of solving

such as: Sheet.cells[irow, icol] = "'" + ds. Tables[0]. Rows[irow-3][itm.id]. ToString ();

Method 2: Add a style to the style:: <td style= "vnd.ms-excel.numberformat:@" >

such as: foreach (DataRow row in TB. Rows) {Data + = "<tr>"; foreach (DataColumn column in TB. Columns) {if (column. Columnname.equals ("ID number") | | Column. Columnname.equals ("registration number")) Data + = "<td style=\" vnd.ms-excel.numberformat:@\ ">" + Row[column ". ToString () + "</td>"; Elsedata + = "<td>" + row[column]. ToString () + "</td>";}

Method 3:

Ado. NET reads Excel, it is based on the first n rows of data in the column (the number of n is from the registry hkey_local_machine/software/microsoft/jet/4.0/engines/excel/typeguessrows). Judging the type of the column based on the contents of these rows of data, this type is independent of the type of the column in Excel (the text type should be preceded by a '). If the data in this n row has text with a number, that is, a mixed type, the value is based on Hkey_local_machine/software/microsoft/jet/4.0/engines/excel/importmixedtype. If the Importmixedtype value is text, the column literal, or if the value is majority type, the type of the data is more.

In order to correctly read a column with a number in the text, we need to set the Importmixedtype entry in the registry to Text,typeguessrows to 0 (indicating that all data will be read to determine if it is a mixed type)

It is also important to note that the value of IMEX affects whether the Importmixedtype and MaxScanRows in the registry are used, if imex=1 is used, if 0 or 2 is not used. While Imex=1 is an open read-only connection, it is only read-only if it is read correctly.

ASP. NET export excel/csv text Format data