. Net export massive data to the execl File

It usually takes a long time to export a large number of Execl files. The following personal favorites method can export massive data

Protected void CreateExecl (string swhere, string title ){
String saveFileName = Server. MapPath ("http://www.cnblogs.com/uploads/file/" + title );
Bool fileSaved = false;

Microsoft. Office. Interop. Excel. Application xlApp = new Microsoft. Office. Interop. Excel. Application ();
If (xlApp = null ){
Return;
}

Microsoft. Office. Interop. Excel. Workbooks workbooks = xlApp. Workbooks;
Microsoft. Office. Interop. Excel. Workbook workbook = workbooks. Add (Microsoft. Office. Interop. Excel. XlWBATemplate. xlWBATWorksheet );
Microsoft. Office. Interop. Excel. Worksheet worksheet = (Microsoft. Office. Interop. Excel. Worksheet) workbook. Worksheets [1]; // get sheet1
System. Data. DataTable table = new System. Data. DataTable ();
Table. Columns. Add ("tagno", typeof (string ));
Table. Columns. Add ("AddDate", typeof (string ));
SqlDataReader dr = mydb. ExecuteReaderSQL ("SELECT tagno, AddDate FROM tagno WHERE" + swhere + "order by tagnoid ASC", null );
While (dr. Read ()){
Table. Rows. Add (dr ["tagno"]. ToString (), dr ["AddDate"]. ToString ());
}
Dr. Close ();

Long rows = table. Rows. Count;
Int32 _ pagecount = 60000;
If (rows & gt; 60000 ){
Long pageRows = _ pagecount; // defines the number of lines displayed on each page. The number of lines must be smaller
Int scount = (int) (rows/pageRows );
If (scount * pageRows <table. Rows. Count) // when the total number of Rows is not divisible by pageRows, the number of pages may not be allowed after rounding
{
Scount = scount + 1;
}
For (int SC = 1; SC <= scount; SC ++ ){
If (SC> 1 ){
Object missing = System. Reflection. Missing. Value;
Worksheet = (Microsoft. Office. InterOP. Excel. worksheet) Workbook. worksheets. Add (missing, missing); // Add a sheet
}
Else {
Worksheet = (Microsoft. Office. InterOP. Excel. worksheet) Workbook. worksheets [SC]; // get sheet1
}
String [,] datas = new string [pagerows + 1, 2];
Datas [0, 0] = "tag number ";
Datas [0, 1] = "generation time ";

Microsoft. Office. InterOP. Excel. Range = worksheet. get_range (worksheet. cells [1, 1], Worksheet. cells [1, 2]);
Range. Interior. colorindex = 15; // 15 indicates gray
Range. Font. Bold = true;
Range. Font. size = 9;
Int init = int. parse (SC-1) * pagerows). tostring ());
Int r = 0;
Int Index = 0;
Int32 result;
If (pagerows * SC> = table. Rows. Count ){
Result = table. Rows. count;
}
Else {
Result = int. Parse (pageRows * SC). ToString ());
}

For (r = init; r <result; r ++ ){
Index = index + 1;
For (int I = 0; I <2; I ++ ){
Object obj = table. Rows [r] [I];
Datas [index, I] = obj = null? "": "'" + Obj. ToString (). Trim ();
}
}
Microsoft. Office. Interop. Excel. Range fchR = worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [index + 1, 2]);
FchR. Value2 = datas;
Worksheet. Columns. EntireColumn. AutoFit (); // adaptive column width.
Range = worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [index + 1, 2]);

// 15 indicates gray
Range. Font. Size = 9;
Range. RowHeight = 14.25;
Range. Borders. LineStyle = 1;
Range. HorizontalAlignment = 1;
}
}
Else {
String [,] datas = new string [table. Rows. Count + 2, 2];
Datas [0, 0] = "tag number ";
Datas [0, 1] = "generation time ";

Microsoft. Office. Interop. Excel. Range range = worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [1, 2]);
Range. Interior. ColorIndex = 15; // 15 indicates gray
Range. Font. Bold = true;
Range. Font. Size = 9;

Int r = 0;
For (r = 0; r <table. Rows. Count; r ++ ){
For (int I = 0; I <2; I ++ ){
// If (gridview. getVisibleColumn (I ). columnType = typeof (string) | gridview. getVisibleColumn (I ). columnType = typeof (Decimal) | gridview. getVisibleColumn (I ). columnType = typeof (DateTime ))
//{
Object obj = table. Rows [r] [I];
Datas [r + 1, I] = obj = null? "": "'" + Obj. ToString (). Trim ();
//}
}
}
Microsoft. Office. Interop. Excel. Range fchR = worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [table. Rows. Count + 2, 2]);

FchR. Value2 = datas;
Worksheet. Columns. EntireColumn. AutoFit (); // adaptive column width.
Range = worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [table. Rows. Count + 1, 2]);

// 15 indicates gray
Range. Font. Size = 9;
Range. RowHeight = 14.25;
Range. Borders. LineStyle = 1;
Range. HorizontalAlignment = 1;
}

If (saveFileName! = ""){
// Try
//{
Workbook. Saved = true;
Workbook. SaveCopyAs (saveFileName );
FileSaved = true;
//}
// Catch (Exception ex)
//{
// FileSaved = false;
//}
}
Else {
FileSaved = false;
}

XlApp. Quit ();
GC. Collect (); // forcibly destroy
If (fileSaved & System. IO. File. Exists (saveFileName) System. Diagnostics. Process. Start (saveFileName); // open EXCEL
}