Export DataGridView, DataTable to Excel

Recently encountered the export DataGridView to Excel slow problem, the amount of data is small, but there are a few columns of the field is very large (man 2000 or so), check the online many methods are either still slow,

either not functioning properly; Modified, debugging passes, the export is much faster than before, and will not be stuck again. Tidy up as follows:

Design the table you want to generate with Excel:

Save as XML table:

Open the saved XML with a text editor:

Locate the table node and remove the node's ss:expandedrowcount= "2":

You'll see a column heading down:

Here is the data, delete the data row and replace it with {0}:

Main code:

1 protected Override voidBtn_exprot_click (Objectsender, EventArgs e)2         {3 Buildwhere ();4 5             //Dgv_details.datasource = controller. Getareasiteinfo (Strwhere.tostring ()). Tables[0];6 7DataTable dt = controller. Getareasiteinfo (Strwhere.tostring ()). tables[0];//Get Data8             stringRow =@"<Row>9 <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{0}</data></cell> ;Ten <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{1}</data></cell> ; One <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{2}</data></cell> ; A <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{3}</data></cell> ; - <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{4}</data></cell> ; - <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{5}</data></cell> ; the <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{6}</data></cell> ; - <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{7}</data></cell> ; - <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{8}</data></cell> ; - <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{9}</data></cell> ; + <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{10}</DATA></CELL&G t; - <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{11}</DATA></CELL&G t; + <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{12}</DATA></CELL&G t; A <cell ss:styleid= "" S21 "" ><data ss:type= "" String "" >{13}</DATA></CELL&G t; at </Row>"; -  -list<string> Rows =Newlist<string>(); -             foreach(DataRow Drinchdt. Rows) -             { -Rows.Add (string. Format (Row, dr[0],dr[1],dr[2],dr[3],dr[4],dr[5],dr[6],dr[7],dr[8],dr[9],dr[Ten],dr[ One],dr[ A],dr[ -])); in             } -  toStreamReader reader =NewStreamReader (Application.startuppath +"\ \ Zone query export. XML"); +  -SaveFileDialog Diag =NewSaveFileDialog (); theDiag. Filter ="Excel File (*.xls) |*.xls"; *Diag. Restoredirectory =true; $             stringPath ="";Panax Notoginseng  -             if(Diag. ShowDialog () = =DialogResult.OK) the             { +Path =Diag. Filename.tostring (); AStreamWriter writer =NewStreamWriter (path); theWriter. Write (reader. ReadToEnd (), String.Join ("\ r \ n", Rows.toarray ())); + writer. Flush (); - writer. Close (); $             } $ Reader. Close (); -             if(Convertexcel (path)) -Fuihelper.showdialog ( This,"Export Success!! ","Tips", messageicon.information); the             //dgv_details.exporttoexcel ("area table Query"); -         }Wuyi  the         Private BOOLConvertexcel (stringSavepath) -         { Wu             //convert an XML file to a standard Excel format -Object nothing = System.Reflection.Missing.Value;//because many values of yongcom components need to be replaced with Missing.Value AboutMicrosoft.Office.Interop.Excel.Application Exclapp =NewMicrosoft.Office.Interop.Excel.Application ();//Initialize $Microsoft.Office.Interop.Excel.Workbook Excldoc = ExclApp.Workbooks.Open (Savepath, nothing, nothing, nothing, nothing , nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing);//Open the excl working thin -             Try -             { -Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal;//Get excl 2007 file Format xlWorkbookNormal AExclapp.displayalerts =false; +Excldoc.saveas (Savepath, format, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);//Save As Excl 2007 format the             } -             Catch(Exception ex) $             { the                 return false; the             } the Excldoc.close (Nothing, nothing, nothing); the exclapp.quit (); -             return true; in}

View Code

Export DataGridView, DataTable to Excel