Datatableexport to the Excel solution (.csv) File

Currently, a developed system needs to export data from the database to excel.

I have been using

Set the gridviewSource codeOutput method, but today we find that the xls file output in this way, when you open the file in Excel, a prompt is displayed, "the format of the file xxx you are trying to open is inconsistent with the file extension specified .... Whether to open ".

 

So I decided to export it in another way.

Some people in the garden use Microsoft. Office. InterOP. Excel. However, I tried to export about 7-entries per minute. The speed is really unbearable.

 

Therefore, the CSV file can be normally opened in Excel, without any impact, and can be converted into XLS format.

 

In addition, the followingCode"," (Comma) to "\ t" is the standard xls file format.

 

Public   Void Setexcelfromdata (system. Data. datatable DT, String Filename)
{
Stringwriter SW =   New Stringwriter ();
Stringbuilder sb =   New Stringbuilder ();
For ( Int I =   0 ; I < DT. Columns. Count; I ++ )
{
SB. append (Dt. Columns [I]. columnname );
If (I ! = DT. Columns. Count -   1 )
{
SB. append ( " , " );
}
}
Sw. writeline (sb. tostring ());
Foreach (Datarow Dr In DT. Rows)
{
For ( Int I =   0 ; I < DT. Columns. Count; I ++ )
{
Sw. Write (Dr [I]);
If (I ! = DT. Columns. Count - 1 )
{
Sw. Write ( " , " );
}

}
Sw. writeline ( "" );
}
Sw. Close ();
Response. Clear ();
Response. addheader ( " Content-Disposition " , " Attachment; filename = "   + Context. server. urlencode (filename) +   " . CSV " );
Response. contenttype =   " Application/MS-Excel " ;
Response. contentencoding = System. Text. encoding. getencoding ( " UTF-8 " );
Response. Write (SW );
Response. End ();
}