Export data from the datagridview to excel using the clipboard

There are many ways to export data in the datagridview to excel. The common method is to use the office COM component to copy the data in the datagridview to the Excel Cell Object cyclically, and then save the entire Excel Workbook. However, if the data volume is too large, such as tens of thousands of rows of data or multiple Excel sheet needs to be exported at the same time, the efficiency will be relatively low. You can try asynchronous operations or multithreading to solve the problem of UI deadlock. Reference http://www.cnblogs.com/jaxu/archive/2011/08/03/2126497.html

This section describes how to export data from the datagridview to excel using the Windows clipboard.CodeAs follows:

 1   Using  System;  2   Using  System. Collections. Generic;  3   Using  System. componentmodel;  4  Using  System. Data;  5   Using  System. drawing;  6   Using  System. LINQ;  7   Using  System. text;  8   Using  System. Windows. forms;  9   Using Excel =Microsoft. Office. InterOP. Excel;  10   Using  System. reflection;  11   Using  Microsoft. Office. InterOP. Excel;  12   13   Namespace  Windowsformsapplication1  14   {  15       Public   Partial  Class  Form1: Form  16   {  17           Public  Form1 ()  18   {  19   Initializecomponent ();  20               This . Savefiledialog1.filter = "  Excel Workbook | *. XLSX | Excel macro-enabled workbook | *. xlsm | Excel 97-2003 workbook | *. xls "  ;  21               This . Savefiledialog1.filename = "  Demo.xlsx  "  ;  22   23   Loaddata ();  24   }  25   26           Private   Void Loaddata ()  27   {  28 Bindinglist <car> cars = New Bindinglist <car> ();  29   30 Cars. Add ( New Car ( "  Ford  " , "  Mustang  " , 1967  ));  31 Cars. Add ( New Car ( "  Shelby AC  " , "  Cobra  " , 1965  ));  32 Cars. Add ( New Car ( " Chevrolet  " , "  Corvette sting ray  " , 1965  ));  33   34               This . Datagridview1.datasource = Cars;  35   }  36   37           Private  Void Toolstripbutton#click ( Object  Sender, eventargs E)  38   {  39               String Filepath = String  . Empty;  40               If ( This . Savefiledialog1.showdialog () = System. Windows. Forms. dialogresult. OK)  41  {  42 Filepath = This  . Savefiledialog1.filename;  43   }  44               Else  45   {  46                   Return  ;  47   }  48  49               This  . Datagridview1.selectall ();  50 Clipboard. setdataobject ( This  . Datagridview1.getclipboardcontent ());  51   52 Excel. Application objexcel = Null  ;  53 Excel. Workbook objworkbook = Null  ;  54 Excel. worksheet objsheet = Null  ;  55               Try  56   {  57 Objexcel = New  Microsoft. Office. InterOP. Excel. Application ();  58 Objworkbook = Objexcel. workbooks. Add (missing. value );  59 Objsheet =(Excel. worksheet) objworkbook. activesheet;  60 Objexcel. Visible = False  ;  61   62 Objexcel. get_range ( "  A1  "  , System. type. Missing). pastespecial (xlpastetype. xlpasteall, xlpastespecialoperation. xlpastespecialoperationnone, type. Missing, type. Missing );  63 Objsheet. Name = " Demo  "  ;  64                   //  Set table Properties  65 Objexcel. cells. entirecolumn. autofit (); //  Auto column width  66 Objexcel. cells. verticalalignment = Microsoft. Office. InterOP. Excel. constants. xlcenter;  67 Objexcel. cells. horizontalalignment =Microsoft. Office. InterOP. Excel. constants. xlleft;  68 Objexcel. errorcheckingoptions. backgroundchecking = False  ;  69   70                   //  Save File  71   Objworkbook. saveas (filepath, missing. Value, missing. value,  72   Missing. Value, Excel. xlsaveasaccessmode. xlshared, missing. Value, missing. Value, missing. value, 73   Missing. Value, missing. value );  74   }  75               Catch  (Exception error)  76   {  77 MessageBox. Show (error. Message, "  Error  "  , Messageboxbuttons. OK, messageboxicon. Error ); 78                   Return  ;  79   }  80               Finally  81   {  82                   //  Dispose the Excel related objects  83                   If (Objworkbook! = Null  ) 84   {  85   Objworkbook. Close (missing. Value, missing. Value, missing. value );  86   }  87                   If (Objexcel. workbooks! = Null  )  88   {  89   Objexcel. workbooks. Close (); 90   }  91                   If (Objexcel! = Null  )  92   {  93   Objexcel. Quit ();  94   }  95   96 Objsheet = Null ;  97 Objworkbook = Null  ;  98 Objexcel = Null  ;  99 GC. Collect (); //  Force final cleanup.  100   }  101   }  102  }  103   104       Public   Class  Car  105   {  106           Private   String  _ Make;  107           Private   String  _ Model;  108          Private   Int  _ Year;  109   110           Public Car ( String Make, String Model, Int  Year)  111   {  112 _ Make = Make;  113 _ Model =Model;  114 _ Year = Year;  115   }  116   117           Public   String  Make  118   {  119               Get { Return  _ Make ;} 120               Set {_ Make = Value ;}  121   }  122   123           Public   String  Model  124   {  125               Get { Return  _ Model ;}  126              Set {_ Model = Value ;}  127   }  128   129           Public   Int  Year  130   {  131               Get { Return  _ Year ;}  132               Set {_ Year = Value ;}  133   }  134   }  135 }

To export data to excel, In the event toolstripbutton#click, lines 49th and 50 of the Code copy the currently selected row of the dview to the system clipboard, row 62 paste the content in the clipboard to the A1 cell of the Excel default sheet. Excel will automatically format the pasted content, for example.

Using the clipboard to export data is relatively simple, eliminating the need to traverse and Operate Excel objects. The disadvantage is that you cannot set the format and style of the exported data. If you need to set the style of the exported data, you can try to use openxml to modify the style of the Excel file. Refer to this article.ArticleHttp://www.cnblogs.com/jaxu/archive/2012/05/11/2495426.html. You can modify the style in the file stream.

Download the code: windowsformsapplication1.zip