Export DataGridView, DataTable to Excel, datatable to export excel

Export DataGridView, DataTable to Excel, datatable to export excel

 

Recently, the speed of exporting a dview to Excel is slow, and the data volume is small. However, the number of columns of fields is large (about 2000). I checked many methods on the Internet to check whether it is slow or not,

Or it cannot run normally. After modification, it passes debugging and the export process is much faster than before, and it will not be stuck. As follows:

 

Design the table to be generated in Excel:

 

Save as XML table:

 

 

Open the saved xml in a text editor:

 

Find the Table node and delete the ss: ExpandedRowCount = "2" of the node:

 

The column title is displayed below:

 

The following figure shows the data. Delete the data Row and replace it with {0 }:

 

Main Code:

1 protected override void btn_exprot_Click (object sender, EventArgs e) 2 {3 BuildWhere (); 4 5 // dgv_Details.DataSource = controller. getAreaSiteInfo (strWhere. toString ()). tables [0]; 6 7 DataTable dt = controller. getAreaSiteInfo (strWhere. toString ()). tables [0]; // get Data 8 string Row = @ "<Row> 9 <Cell ss: StyleID =" "s21" "> <Data ss: type = "" String "" >{0 }</Data> </Cell> 10 <Cell ss: StyleID = "" s21 ""> <Data ss: Type = "" String ""> {1} </Data> </Cell> 11 <Cell ss: StyleID = "" s21 ""> <Data ss: type = "" String "" >{2} </Data> </Cell> 12 <Cell ss: StyleID = "" s21 ""> <Data ss: type = "" String "" >{3} </Data> </Cell> 13 <Cell ss: StyleID = "" s21 ""> <Data ss: type = "" String "" >{4} </Data> </Cell> 14 <Cell ss: StyleID = "" s21 ""> <Data ss: type = "" String "" >{5} </Data> </Cell> 15 <Cell ss: StyleID = "" s21 ""> <Data ss: type = "" String "" >{6} </Data> </Cell> 16 <Cell ss: StyleID = "" s2 1 ""> <Data ss: Type = "" String "" >{7} </Data> </Cell> 17 <Cell ss: styleID = "" s21 ""> <Data ss: Type = "" String "" >{8} </Data> </Cell> 18 <Cell ss: styleID = "" s21 ""> <Data ss: Type = "" String "" >{9 }</Data> </Cell> 19 <Cell ss: styleID = "" s21 ""> <Data ss: Type = "" String "" >{10 }</Data> </Cell> 20 <Cell ss: styleID = "" s21 ""> <Data ss: Type = "" String "" >{11 }</Data> </Cell> 21 <Cell ss: styleID = "" s21 ""> <Data ss: Type = "" String "" >{12} </Data> </Cell> 2 2 <Cell ss: StyleID = "" s21 ""> <Data ss: type = "" String "" >{13 }</Data> </Cell> 23 </Row> "; 24 25 List <string> Rows = new List <string> (); 26 foreach (DataRow dr in dt. rows) 27 {28 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 [10], dr [11], dr [12], dr [13]); 29} 30 31 StreamReader reader = new StreamReader (Application. startupPath + "\ Region Query and export. xml "); 32 33 SaveFileDialog dia G = new SaveFileDialog (); 34 diag. filter = "Excel file (*. xls) | *. xls "; 35 diag. restoreDirectory = true; 36 string path = ""; 37 38 if (diag. showDialog () = DialogResult. OK) 39 {40 path = diag. fileName. toString (); 41 StreamWriter writer = new StreamWriter (path); 42 writer. write (reader. readToEnd (), String. join ("\ r \ n", Rows. toArray (); 43 writer. flush (); 44 writer. close (); 45} 46 reader. close (); 47 if (ConvertExce L (path) 48 FUIHelper. ShowDialog (this, "exported successfully !! "," Prompt ", MessageIcon. information); 49 // dgv_Details.ExportToExcel ("region Table query"); 50} 51 52 private bool ConvertExcel (string savePath) 53 {54 // convert the xml file to the standard Excel format 55 Object Nothing = System. reflection. missing. value; // due to many yongCOM component values, Missing is required. value replaces 56 Microsoft. office. interop. excel. application ExclApp = new Microsoft. office. interop. excel. application (); // initialize 57 Microsoft. office. interop. excel. workbook ExclDoc = ExclApp. workbooks. open (savePath, Nothing, Nothing ); // open the Excl working thin 58 try59 {60 Object format = Microsoft. office. interop. excel. xlFileFormat. xlWorkbookNormal; // obtain the Excl 2007 file format xlWorkbookNormal 61 ExclApp. displayAlerts = false; 62 ExclDoc. saveAs (savePath, format, Nothing, Microsoft. office. interop. excel. xlSaveAsAccessMode. xlExclusive, Nothing, Nothing); // Save As Excl 2007 format 63} 64 catch (Exception ex) 65 {66 return false; 67} 68 ExclDoc. close (Nothing, Nothing, Nothing); 69 ExclApp. quit (); 70 return true; 71}View Code

 

C # How to export the datagridview to excel

Here we have
Www.2cto.com/kf/201209/152319.html

Do not reference the Excel Com component, c # Winform datatable, dataset, or DataGridView to export Excel

Public void ExportDataGridViewToExcel (DataGridView datagridatagridatagri1)
{
SaveFileDialog saveFileDialog = new SaveFileDialog ();
SaveFileDialog. Filter = "Execl files (*. xls) | *. xls ";
SaveFileDialog. FilterIndex = 0;
SaveFileDialog. RestoreDirectory = true;
SaveFileDialog. CreatePrompt = true;
SaveFileDialog. Title = "Export an Excel file ";

DateTime now = DateTime. Now;
SaveFileDialog. fileName = now. year. toString (). padLeft (2) + now. month. toString (). padLeft (2, '0') + now. day. toString (). padLeft (2, '0') + "-" + now. hour. toString (). padLeft (2, '0') + now. minute. toString (). padLeft (2, '0') + now. second. toString (). padLeft (2, '0 ');
SaveFileDialog. ShowDialog ();

Stream myStream;
MyStream = saveFileDialog. OpenFile ();
StreamWriter sw = new StreamWriter (myStream, System. Text. Encoding. GetEncoding ("gb2312 "));
String str = "";
Try
{
// Write the title
For (int I = 0; I <dataGridview1.ColumnCount; I ++)
{
If (I> 0)
{
Str + = "\ t ";
}
Str + = maid [I]. HeaderText;
}
Sw. WriteLine (str );
// Write content
... The remaining full text>