C#winform two ways to directly export DataGridView data to an Excel table

method One, using Microsoft's Excel Operation class

References: using Excel = Microsoft.Office.Interop.Excel;

The code is as follows :

         #region导出数据表:excle  (Microsoft's excel  operation Class)          ///<summary>        ///Export Data Sheet: Excle         ///</summary>         ///<param name= "MYDGV" ></param>        public  void _toexcel (DATAGRIDVIEW&NBSP;MYDGV)         {             string path =  "";             SaveFileDialog saveDialog = new  SaveFileDialog ();             savedialog.defaultext  =  "xlsx";             savedialog.filter  =  "ExcelDocument |*.xlsx ";             savedialog.showdialog ();             path = saveDialog.FileName;             if  (path. IndexOf (":")  < 0)  return; //decide whether to click Cancel              try            {                 excel.application  xlapp = new excel.application ();                 if  (xlapp == null)                  {                 &nbSp;   messagebox.show ("Unable to create Excel object, may not have Excel installed on your machine");                     return;                 }                 Excel.Workbooks workbooks =  xlapp.workbooks;                 excel.workbook workbook = workbooks. ADD (Excel.XlWBATemplate.xlWBATWorksheet);                 Excel.Worksheet worksheet =  (excel.worksheet) workbook. worksheets[1];//Get sheet1                 int colindex = 0;                //Write title                  for  (int i = 0; i <  mydgv.columncount; i++)                  {                     if  (Mydgv.columns[i]. Visible)//used to not export hidden columns                      {                         colIndex++;                          worksheet. Cells[1, colindex] = mydgv.columns[i]. Headertext;                         //worksheet. Cells[1, i] = mydgv.columns[i]. headertext;                     }                 }                / /write Values                 for  (int r = 0; r < mydgv.rows.count - 1; r++)                  {                     colindex =  0;                    for  (int i =  0; i < mydgv.columncount; i++)                      {                         if   (Mydgv.columns[i]. Visible)                          {                             colindex ++;                             worksheet. Cells[r + 2, colindEX]&NBSP;=&NBSP;MYDGV.ROWS[R]. Cells[i]. value;                         }                     }                     system.windows.forms.application.doevents ();                 }                 worksheet. Columns.EntireColumn.AutoFit ();//Column width adaptive                  if  (path !=  "")                  {                    try                     {                          Workbook. saved = true;                         workbook. SaveCopyAs (PATH);                         //fileSaved = true;                     }                      catch  (Exception ex) &NBSP;&NBSP;&NBSP;&NBSP;&NBsp;               {                          //fileSaved = false;                         messagebox.show (" An error occurred while exporting the file, the file may be being opened! \n\r " + ex. Message);                     }                 }                 xlApp.Quit ();                 Gc. Collect ();//forcibly Destroy                 // if  (Filesaved && system.io.file.exists (saveFileName))   System.Diagnostics.Process.Start (savefilename);  //Open excel                  messagebox.show (path +  ", Export succeeded",  "System Prompt",  messageboxbuttons.ok);            }             catch  (Exception ex)              {                 messagebox.show (ex. Message);            }          }         #endregion

method Two, IO underlying stream Operation class

References: using System.IO;

Using System.Threading;

The code is as follows:

         #region导出数据表:excle  (IO Stream operation Class)          ///<summary>        ///Export Data Table:excle  ( IO stream operation Class)         ///</summary>         ///<param name= "MYDGV" ></param>         Public void _toexcel2 (DATAGRIDVIEW&NBSP;MYDGV)         {             string path =  "";             SaveFileDialog saveDialog =  New savefiledialog ();             savedialog.defaultext =  "xls";             saveDialog.Filter =  "Excel97-2003  (*.xls) |*.xls| all files  (*. *) |*.* ";             Savedialog.showdialog ();             path =  saveDialog.FileName;            if  ( Path. IndexOf (":")  < 0)  return; //decide whether to click Cancel              try            {                 thread.sleep (1000);                 streamwriter  sw = new streamwriter (path, false, encoding.getencoding ("gb2312"));                 stringbuilder sb&nbSp;= new stringbuilder ();                 //Write title                  for  (int k = 0; k < mydgv.columns.count; k++)                 {                     if   (Mydgv.columns[k]. Visible)//export the viewable caption                      {                         //"\ t" is equal to the Keyboard tab, add a "\ t" means:  After filling, go to the next cell .                          sb. Append (Mydgv.columns[k]. Headertext.tostring (). Trim ()  +  "\ T");                     }                 }               &NBSP;&NBSP;SB. Append (Environment.NewLine);//NewLine                  //Write per line value                  for  (int i = 0; i < mydgv.rows.count - 1; i++ )                 {                      System.Windows.Forms.AppLication. DoEvents ();                     for  (int j = 0; j < mydgv.columns.count; j+ +)                      {                         if  (Mydgv.columns[j]. Visible)//export the viewable cells                          {                              //Note that the cell has a certain number of byte limits, if exceeded, it will appear that the contents of the two cells are identical .                             //specific limit is how many bytes, did not make in-depth study .                         &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SB. Append (Mydgv.rows[i]. CELLS[J]. Value.tostring (). Trim ()  +  "\ T");                         }                     }       &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SB. Append (Environment.NewLine);  //Line Wrap                  }               &NBSP;&NBSP;SW. Write (sb.) ToString ()); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SW. Flush ();                 sw. Close ();                 MessageBox.Show (path +  ", export successful",  "system hint",  messageboxbuttons.ok);             }             catch  (Exception ex)              {                 MessageBox.Show (ex. Message);            }          }         #endregion

Two ways to compare, the fastest way to export to an Excel table with an IO base stream . In particular , more than the above data, but also to reflect its speed.

This article is from the "9212950" blog, please be sure to keep this source http://9222950.blog.51cto.com/9212950/1607070

C#winform two ways to directly export DataGridView data to an Excel table