DataGridView How to quickly export excel

Export Excel files from DataGridView or DataTable, in order to format cells by data type, Excel is slow to export, and there is no good way to find it.

Finally find a solution from the foreign language website, using Ws.get_range (Excelrange, Type.Missing). The Value2 method sets the value of a cell in bulk. The detailed code is as follows:

     Public voidDatatoexcelfast (DataGridView DGV,stringfName) {          Try{excel.application Excel=NewExcel.Application (); Excel.Workbook WB= Excel. Workbooks.Add (true); Excel.Worksheet ws=(Excel.Worksheet) WB.              ActiveSheet; Ws. Name=FName; stringSFile =string.              Empty; SaveFileDialog Dialog=NewSaveFileDialog (); Dialog. Title="Save Export File"; Dialog. Filter="execl File (*.xlsx) |. xlsx"; Dialog. FileName=FName; Dialog. FilterIndex=1; if(dialog. ShowDialog () = =DialogResult.OK) {stringFileName =dialog.                  FileName; if(file.exists (filename)) file.delete (filename); SFile=FileName; Excel. Visible=false; Excel. DisplayAlerts=false; Excel. Calculation=Excel.XlCalculation.xlCalculationManual; ArrayList cols=NewArrayList (); #regioncolumn headings and format Excel columns based on the data type of the columnintVisiblecolumncount =0; foreach(DataGridViewColumn ColinchDGV. Columns) {if(Col. Visible) {Visiblecolumncount++; Ws. cells[1, Visiblecolumncount]. Value2 =Col.                          Headertext.tostring (); Ws. cells[1, Visiblecolumncount]. Font.Bold =true; Cols. Add (Col.                          Name); if(Col. ValueType! =NULL)                          {                              if(Col. ValueType.Name.Equals ("DateTime") ) {ws. Columns[visiblecolumncount]. NumberFormatLocal=@"YYYY-MM-DD hh:mm"; Ws. Columns[visiblecolumncount]. Font.Color=System.Drawing.ColorTranslator.ToOle (Color.Blue); }                              Else if(Col. ValueType.Name.Equals ("Decimal")                                   || Col. ValueType.Name.Equals ("Double") ) {ws. Columns[visiblecolumncount]. NumberFormat="0.00"; }                              Else if(Col. ValueType.Name.Equals ("Int32") ) {ws. Columns[visiblecolumncount]. NumberFormat="0"; }                              Else{ws. Columns[visiblecolumncount]. NumberFormatLocal=@"@"; }                          }                          Elsews. Columns[visiblecolumncount]. NumberFormatLocal=@"@"; }                  }                  #endregion                  #regionWrite rows//Copy each DataTable//Copy the DataTable to an object array                Object[,] RawData =New Object[DGV. Rows.Count, cols.                  Count]; //Copy The column names to the first row of the object array                 for(intCol =0; Col < cols. Count; col++) {rawdata[0, col] =Cols[col]; }                  //Copy The values to the object array                 for(intCol =0; Col < cols. Count; col++)                  {                       for(introw =0; Row < DGV. Rows.Count; row++) {rawdata[row, col]=DGV. Rows[row]. Cells[cols[col]. ToString ()].                      Value; }                  }                  //Calculate The final column letter                stringFinalcolletter =string.                  Empty; stringColcharset ="abcdefghijklmnopqrstuvwxyz"; intColcharsetlen =colcharset.length; if(cols. Count >Colcharsetlen) {Finalcolletter=colcharset.substring (cols. Count-1)/Colcharsetlen-1,1); } finalcolletter+=colcharset.substring (cols. Count-1)% Colcharsetlen,1); //Fast Data export to Excel                stringExcelrange =string. Format ("A2:{0}{1}", Finalcolletter, DGV. Rows.Count+1); Ws.get_range (Excelrange, Type.Missing). Value2=RawData; #endregion  Excel. Calculation=Excel.XlCalculation.xlCalculationAutomatic; //51 for xlsx in 2007-2010 formatWs. SaveAs (FileName,Wuyi, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office .                  Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); Wb. Close (true, Type.Missing, Type.Missing); Excel.                  Quit (); //Safe Recycling ProcessSystem.GC.GetGeneration (Excel); if(MessageBox.Show ("already exported Excel, do you want to open it? ","Tips", Messageboxbuttons.yesno)==dialogresult.yes) System.Diagnostics.Process.Start (FileName); }          }          Catch(Exception ex) {MessageBox.Show ("Error encountered while exporting Excel \ r \ n"+Ex. Message,"Error", Messageboxbuttons.ok,messageboxicon.error); }      } 

DataGridView How to quickly export excel