Asp.net optimal scheme for exporting data in Excel/Csv format (C #)

Source: Internet
Author: User

I haven't written anything for a long time, maybe it's too busy. After one year, I have accumulated a lot of good things and will share them with you when I have the opportunity.

Well, let's get down to the truth.
Exporting data to an Excel/Csc file is not difficult, so there are many methods: the control directly Render, output the DataSet into a String, and then Write it out, (Of course, if you call the Excel program library file, you can also use a stronger method to directly operate Excel, but this method is too demanding for Web Services: you must have the Web server install a specified version of Excel or its supporting library files ). The hosts file is ready.

Excel is so powerful that its own format, Csv format, Tab separator format, and webpage Table format can be opened well. But it is so smart that it can automatically recognize numbers and strings, and automatically convert more than 11 digits into scientific notation format, you try to enter "123456789012 ", when I leave that cell, it becomes "123457E + 11". It's smart, but sometimes it makes us feel inconvenient, Because I entered my ID card number, there is no "X" on the tail. It was a good 15-digit number, and now it has to become such a notation. Then I will change the display format to text. However, the country's ID card has been upgraded and the number has changed to 18 digits. I entered it to the database and exported it. I used the method just now. The 18 digits are correct, but the last three digits are all zero! Excel has done a lot for us, whether it is appropriate or not.

How can this problem be solved? See the Code:

Public static string ExportTable (DataSet ds)
{
String data = "";
// Data = ds. DataSetName + "\ n ";

Foreach (DataTable tb in ds. Tables)
{
// Data + = tb. TableName + "\ n ";
Data + = "<table cellspacing = \" 0 \ "cellpadding = \" 5 \ "rules = \" all \ "border = \" 1 \ "> ";
// Write the column name
Data + = "<tr style = \" font-weight: bold; white-space: nowrap; \ "> ";
Foreach (DataColumn column in tb. Columns)
{
Data + = "<td>" + column. ColumnName + "</td> ";
}
Data + = "</tr> ";

// Write data
Foreach (DataRow row in tb. Rows)
{
Data + = "<tr> ";
Foreach (DataColumn column in tb. Columns)
{
If (column. ColumnName. Equals ("credential No.") | column. ColumnName. Equals ("Registration No "))
Data + = "<td style = \" vnd. ms-excel.numberformat: @ \ ">" + row [column]. ToString () + "</td> ";
Else
Data + = "<td>" + row [column]. ToString () + "</td> ";
}
Data + = "</tr> ";
}
Data + = "</table> ";
}

Return data;
}

Public static void ExportDsToXls (Page page, string SQL)
{
ExportDsToXls (page, "FileName", SQL );
}
Public static void ExportDsToXls (Page page, string fileName, string SQL)
{
DataSet ds = DBUtil. GetDataSet (SQL );
If (ds! = Null) ExportDsToXls (page, fileName, ds );
}
Public static void ExportDsToXls (Page page, DataSet ds)
{
ExportDsToXls (page, "FileName", ds );
}
Public static void ExportDsToXls (Page page, string fileName, DataSet ds)
{
Page. Response. Clear ();
Page. Response. Buffer = true;
Page. Response. Charset = "GB2312 ";
// Page. Response. Charset = "UTF-8 ";
Page. response. appendHeader ("Content-Disposition", "attachment; filename =" + fileName + System. dateTime. now. toString ("_ yyMMdd_hhmm") + ". xls ");
Page. Response. ContentEncoding = System. Text. Encoding. GetEncoding ("GB2312"); // set the output stream to simplified Chinese
Page. Response. ContentType = "application/ms-excel"; // set the output file type to excel.
Page. EnableViewState = false;
Page. Response. Write (ExportTable (ds ));
Page. Response. End ();
}
// Style = "vnd. ms-excel.numberformat: @" can remove the trouble of automatic scientific notation
// The output is Table, which can minimize the impact of data in the field on the generated file format. In this case, I have not processed HTML tags in the data on the background of the page, in this way, you can:

Protected void lbtnToExcel_Click (object sender, EventArgs e)
{
String strWhere = BuildSearchWhereString ();
String strOrder = this. hidOrderString. Value;
String SQL = "SELECT Registration No., credential No., name, exam site, exam type ,"
+ "Administrative Division name: AS administrative division, organization name: AS work unit, graduation school name, graduation major name: AS graduation major, graduation year ,"
+ "Mailing address, gender"
+ "From [VW Registration]";
If (! String. IsNullOrEmpty (strWhere) SQL + = "where" + strWhere;
If (! String. IsNullOrEmpty (strOrder) SQL + = "order by" + strOrder;
Else SQL + = "order by [applicant type]";
PageExport. ExportDsToXls (this. Page, "BaoMing", SQL );
DataBind ();
}

Write it here for now and rest.
Updated on:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.