Asp.net: How to export data in Excel/Csv text format.

Asp.net: How to export data in Excel/Csv text format.

I just started to work on Excel-related projects, so I should record all the problems regardless of the size.

By chance, when adding data, all the data is converted into numbers, and the result output is automatically converted into scientific notation. This is a powerful excel function that can automatically recognize numbers and strings, it's too clever, but it's a little troublesome, just like if you enter your ID card (18 digits. After more than 11 digits, I checked some information and summarized the solution:

Method 1: When adding data to excel, add single quotation marks before the data. My solution is this method.

For example, Sheet. Cells [iRow, iCol] = "'" + ds. Tables [0]. Rows [iRow-3] [itm. ID]. ToString ();

Method 2: add style: <td style = "vnd. ms-excel.numberformat: @">

For example:
Copy codeThe Code is as follows:
Foreach (DataRow row in tb. rows) {data + = "<tr>"; foreach (DataColumn column in tb. columns) {if (column. columnName. equals ("ID number") | column. columnName. equals ("Registration No.") data + = "<td style = \" vnd. ms-excel.numberformat: @ \ ">" + row [column]. toString () + "</td>"; elsedata + = "<td>" + row [column]. toString () + "</td> ";}


Method 3:

ADO. NET when reading Excel, it will be based on the first n rows of data in the column (n numbers from the Registry HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows ), determine the type of the column based on the data of these rows. This type is irrelevant to the type of the column in Excel (the text type should be a 'before the data '). If the data in the n rows contains numbers in the text, that is, the hybrid type, the value is determined by HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedType. If the ImportMixedType value is Text, the column is Text. If the value is Majority Type, multiple data types are used.

In order to read the columns with numbers and texts correctly, we need to set ImportMixedType in the Registry to text and TypeGuessRows to 0 (indicating that all data needs to be read before determining whether the data type is mixed)

In addition, the IMEX value affects whether to use ImportMixedType and MAXSCANROWS in the registry. If IMEX is set to 1, it is used. If it is set to 0 or 2, it is not used. IMEX = 1 is a read-only connection opened. Therefore, to read the connection correctly, you can only use the read-only method.

Text numbers cannot be saved in CSV format converted from EXCEL !!!

My solution is not to save it as CSV format, because this format will be automatically recognized for opening and saved as TXT text. When opening it, you will be asked how to differentiate it, you can separate them by commas. Then, you will be asked in what format 06666 is to be saved. You can select the text format.

In addition, if you have saved the csvfile, you can change the extension name to ". txt.

How can I convert the data in an EXCEL table exported by UFIDA to a value or a regular format for calculation?

Select the column and click "data"> "sort ". You can.

If you still don't understand it, please consult with yonyou Forum
Reference: bbs.iufida.com