C # export database data through streaming and write it into excel

From the Internet, we can see that the data inserted by excel is very slow. Someone writes data to excel through a stream, which is very fast. The detailed principle is as follows:

1. StreamWriter sw = new StreamWriter (path, false, Encoding. GetEncoding ("gb2312"); generate the file
2. Use StringBuilder sb = new StringBuilder (); Class to combine the queried data into an ultra-long string and insert it into excel at a time,
Sb. Append (ds. Tables [0]. Columns [k]. ColumnName. ToString () + "\ t ");
Note that "\ t" cannot be missed. This is very important! Because c # "\ t" is equivalent to the Tab on the keyboard [friends can try: open a new txt file, input 1 and press Tab, input 2 and press Tab, input 3 and press Tab to save, then open the excel file and pull the saved txt file into it. Then you will find the original file. In this case, 1 2 3 will be written on each cell. So the above uses "\ t" to connect the heap of data from the database. In this way, the data will be imported at one time, and they will be filled according to each cell!
To be referenced:
Using System. Threading;
Using System. IO;
The method is as follows:
Private void button#click (object sender, EventArgs e)
{
SaveFileDialog1.Title = "saved excel files ";
SaveFileDialog1.InitialDirectory = "c :\\";
SaveFileDialog1.Filter = "Excel97-2003 (*. xls) | *. xls | Excel07-2010 (*. xlsx) | *. xlsx;
SaveFileDialog1.ShowDialog ();
If (saveFileDialog1.FileName = "" | saveFileDialog1.FileName = null)
{
MessageBox. Show ("the file name cannot be blank! ");
Return;
}
String path = saveFileDialog1.FileName;
String constr = "Data Source =.; Initial Catalog = Exhibition; User ID = sa; Password = ";
String SQL = GetStrSql ();
DataSet ds = new DataSet ();
Using (SqlConnection con = new SqlConnection (constr ))
{
SqlDataAdapter da = new SqlDataAdapter (SQL, con );
Da. Fill (ds );
}
If (ds = null)
{
MessageBox. Show ("Data Acquisition Error! ");
Return;
}
WriteExcel (ds, path );
}
 
Public void WriteExcel (DataSet ds, string path)
{
Try
{
Long totalCount = ds. Tables [0]. Rows. Count;
LblTip. Text = "Total" + totalCount + "data records. ";
Thread. Sleep (1000 );
Long rowRead = 0;
Float percent = 0;
 
StreamWriter sw = new StreamWriter (path, false, Encoding. GetEncoding ("gb2312 "));
StringBuilder sb = new StringBuilder ();
For (int k = 0; k <ds. Tables [0]. Columns. Count; k ++)
{
Sb. Append (ds. Tables [0]. Columns [k]. ColumnName. ToString () + "\ t ");
}
Sb. Append (Environment. NewLine );
 
For (int I = 0; I <ds. Tables [0]. Rows. Count; I ++)
{
RowRead ++;
Percent = (float) (100 * rowRead)/totalCount;
Pbar. Maximum = (int) totalCount;
Pbar. Value = (int) rowRead;
LblTip. Text = "writing data of [" + percent. ToString ("0.00") + "% ";
System. Windows. Forms. Application. DoEvents ();
 
For (int j = 0; j <ds. Tables [0]. Columns. Count; j ++)
{
Sb. Append (ds. Tables [0]. Rows [I] [j]. ToString () + "\ t ");
}
Sb. Append (Environment. NewLine );
}
Sw. Write (sb. ToString ());
Sw. Flush ();
Sw. Close ();
MessageBox. Show ("the specified Excel file has been generated! ");
}
Catch (Exception ex)
{
MessageBox. Show (ex. Message );
}
}
 
Public string GetStrSql ()
{
String strSql = "select a from B ";
Return strSql;
}