When writing business software, we often encounter the manipulation of Excel tables, the most common is the DataGridView Control import/Export Excel table. I also met the same. The use of their own knowledge, coupled with the "majority" on the Internet, the source of the outdated, I wrote these two methods, of course, and binding database, all use the Databel class.
Import:
private string FileName;///<summary>Import data from DataGridView into Excel///</summary>///<param name= name of "DGV" >datagridview</param>Public System.Data.DataTableImportexcel (String path) {DataSet ds =New DataSet ();String strconn ="";if (path.getextension (Path) = =". xls") {strconn =String. Format ("Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}; Extended Properties=excel 8.0; ", path); }else {strconn =String. Format ("Provider=microsoft.ace.oledb.12.0;data source={0}; Extended Properties=excel 8.0; ", path); }using (var oledbconn =New OleDbConnection (strconn)) {Oledbconn.open ();var sheetname = oledbconn.getoledbschematable (OleDbSchemaGuid.Tables,New[] {NullNullNull"Table"});var sheet =NewString[sheetname.rows.count];for (int i =0, j = sheetName.Rows.Count; I < J; i++) {Sheet[i] = sheetname.rows[i]["table_name"]. ToString (); }var adapter =New OleDbDataAdapter (String. Format ( "select * from [{0}]", Sheet[0]), oledbconn); Adapter. Fill (DS); } return ds. Tables[0];} public void daoru () {string FilePath = new OpenFileDialog (); openfiledialog1.filter = "Excel file |*.xls"; Openfiledialog1.filterindex = 2; openfiledialog1.restoredirectory = true; if (openfiledialog1.showdialog () = = DialogResult.OK) {FilePath = Openfiledialog1.filename ; System.Data.DataTable dt = Importexcel (FilePath); this.datagridview1.datasource = DT;} }
Export:
public static void Exportexcel (String fileName, DataGridView mydgv) {if (MYDGV. Rows. Count >0) {String savefilename =""; BOOL filesaved = False; SaveFileDialog Savedialog = new SaveFileDialog (); Savedialog. DEFAULTEXT ="XLS"; Savedialog. Filter ="Excel file |*.xls"; Savedialog. filename = filename; Savedialog. ShowDialog (); Savefilename = Savedialog. FileName; if (savefilename. IndexOf (":") <0) return; was ordered to cancel the Microsoft. Office. Interop. Excel. Application xlapp = new Microsoft. Office. Interop. Excel. Application (); if (xlapp = = null) {MessageBox. Show ("Unable to create Excel object, may not have Excel installed on your machine"); Return; } Microsoft. Office. Interop. Excel. Workbooks Workbooks = xlapp. Workbooks; Microsoft. Office. Interop. Excel. Workbook Workbook = workbooks. ADD (Microsoft. Office. Interop. Excel. XlWBATemplate. Xlwbatworksheet); Microsoft. Office. Interop. Excel. Worksheet Worksheet = (Microsoft. Office. Interop. Excel. Worksheet) Workbook. worksheets[1];//Get Sheet1//write title for (int i =0; i < Mydgv.columncount; i++) {Worksheet. cells[1, i +1] = MYDGV. Columns[i]. HeaderText; }//write value for (int r =0; R < MyDGV.Rows.Count; r++) {for (int i =0; i < Mydgv.columncount; i++) {Worksheet. Cells[r +2, i +1] = MYDGV. ROWS[R]. Cells[i]. Value; } System. Windows. Forms. Application. DoEvents (); } Worksheet. Columns. EntireColumn. AutoFit ();//Column width adaptive/if (Microsoft. Office. Interop. cmbxtype. Text! ="Notification")//{//Excel. Range RG = Worksheet. Get_range (Worksheet. cells[2,2], worksheet. Cells[ds. tables[0]. Rows. Count +1,2]); Rg. NumberFormat ="00000000"; } if (Savefilename! ="") {try {workbook. Saved = True; Workbook. SaveCopyAs (Savefilename); Filesaved = True; } catch (Exception ex) {//filesaved = False; MessageBox. Show ("An error occurred while exporting the file, the file may be being opened!" \ n "+ ex. Message); }}//else//{//filesaved = False; } xlapp. Quit (); Gc;//forcibly destroyed//if (filesaved && system "Export file succeeded", "hint", Messageboxbuttons "hint", Messageboxbuttons
Don't forget to add references before you use code snippets:
Using System; using System. Collections. Generic; using System. ComponentModel; using System. Data; using System. Drawing; using System. Linq; using System. Text; using System. Windows. Forms; using System. Data. OLE DB; using System. IO; using System. Linq; using System. diagnostics; using Microsoft. Office. Interop. Excel;
(⊙o⊙) Oh, yes, don't forget to quote this!
C # DataGridView Control import/Export Excel table