Asp.net exports Excel/Csv text format data, csv text format

Asp.net exports Excel/Csv text format data, 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, 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.

How can I write the code to import and export CSV files in Aspnet?

For a piece of code, refer:
// TO CSV

Protected void Button5_Click (object sender, EventArgs e)

{

DataTable dt = this. GetDataTable ();

HttpContext. Current. Response. Clear ();

System. IO. StringWriter sw = new System. IO. StringWriter ();

Int iColCount = dt. Columns. Count;

For (int I = 0; I <iColCount; I ++)

{

Sw. Write ("\" "+ dt. Columns [I] + "\"");

If (I <iColCount-1)

{

Sw. Write (",");

}

}

Sw. Write (sw. NewLine );

Foreach (DataRow dr in dt. Rows)

{

For (int I = 0; I <iColCount; I ++)

{

If (! Convert. IsDBNull (dr [I])

Sw. Write ("\" "+ dr [I]. ToString () + "\"");

Else

Sw. Write ("\"\"");

If (I <iColCount-1)

{

Sw. Write (",");

}

}

Sw. Write (sw. NewLine );

}

Sw. Close ();

HttpContext. Current. Response. AddHeader ("Content-Disposition", "attachment; filename=ss.csv ");

HttpContext. Current. Response. ContentType = "application/vnd. ms-excel ";

HttpContext. Current. Response. ContentEncoding = System. Text. Encoding. GetEncoding ("GB2312 ");

HttpContext. Current. Response. Write (sw );

HttpContext. Current. Response. End ();

}

// FROM CSV

Protected void Button6_Click (object sender, EventArgs e)

{

String filepath = Server. MapPath ("ss.csv ");

If (! File. Exists (filepath ))

{

Page. RegisterClientScriptBlock ("msg & quot ...... remaining full text>

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