Asp.net exports Excel and Csv data in text format

Just started to work on Excel related projects, so make a note of any problems you encounter, no matter how big or small

This is a powerful function of excel. It can automatically recognize numbers and strings. It is too clever but some trouble, just like if you input ID card (18 digits), it will not work. More than 11, the following information to summarize the solution:

Method 1: When adding data to excel, place single quotes around the data. Mine is solved with this method

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(" The registration number "))data += "<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>";elsedata += "<td>" + row[column].ToString() + "</td>";}

ADO NET read Excel, according to former n row in the column number from the registry (n HKEY_LOCAL_MACHINE/Software/Microsoft/Jet / 4.0 / Engines/Excel/TypeGuessRows), in accordance with the contents of this a few rows of data to determine the type of the column, the type has nothing to do with the type of the column in the Excel (text types should be data in front of a '). If this n line of data in digital text, which is mixed type, depending on the HKEY_LOCAL_MACHINE/Software/Microsoft/Jet / 4.0 / Engines/Excel/ImportMixedType values. If the value ImportMixedType is Text, this is listed as text; If the value is Majority, Type, then take the data-rich type.

In order to read columns with text and numbers correctly, we need to set the ImportMixedType entry in the registry to text and TypeGuessRows to 0 (means to read all the data before checking whether it is mixed type).

Also note that the value of IMEX will affect whether the registry entries ImportMixedType and MAXSCANROWS are used. If IMEX=1, it will be used. If IMEX is 0 or 2, it will not be used. IMEX=1 is an open read-only connection, so to read it correctly, you must use read-only mode.