. NET to realize the conversion of DataTable and Excel file through CSV file

In a project, you need to import data from an Excel file and then datagridview it, and you need to save the data as an Excel file when you right-click DataGridView, and then write the two tool methods. This article provides two methods for translating between Excel and DataTable.

1, import from Excel file, CSV file to DataTable:

public static DataTable csvtodatatable (string file) {string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data S Ource= "+ file +"; Extended properties= ' Excel 8.0; '; Excel file if (file. EndsWith (". csv")) strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + path + "; Extended properties= ' TEXT; Hdr=yes; fmt=delimited; ' ";  
     CSV file:hdr=yes--is header oledbconnection oleconn = new OleDbConnection (strconn);  
     Oleconn.open ();  
     DataTable sheets = oleconn.getoledbschematable (OleDbSchemaGuid.Tables, NULL); if (sheets = NULL | | sheets.  
     Rows.Count < 1) {return null; String fileName = sheets. rows[0]["table_name"]. ToString (); Sheets. Rows[0]--The sheet of Excel if (file. EndsWith (". csv")) FileName = file. Substring (file.  
     LastIndexOf ("/"));  
     String olestr = "SELECT * FROM [" + FileName + "]"; if (file. EndsWith (". csv")) OLESTR = "SELECT * FROM [" + FileName +] ";  
     OleDbCommand Olecomm = new OleDbCommand (OLESTR, oleconn);  
     Olecomm.connection = Oleconn;  
     OleDbDataAdapter Oleda = new OleDbDataAdapter ();  
     Oleda.selectcommand = Olecomm;  
     DataSet ds = new DataSet ();  
     Oleda.fill (DS);  
     Oleconn.close (); Return DS.  
 Tables[0]; }

2,datatable to CSV file:

public static void Datatabletocsv (DataTable table, string file) {string title = "";  
            FileStream fs = new FileStream (file, FileMode.Create);  
     
            StreamWriter sw = New StreamWriter (new BufferedStream (FS), System.Text.Encoding.Default); for (int i=0; i<table. Columns.count; i++) {title + table. Columns[i].  
            ColumnName + ","; title = title. Substring (0, title.  
            Length-1) + "\ n"; Sw.  
     
            Write (title); foreach (DataRow row in table).  
                Rows) {String line = ""; for (int i = 0; i < table. Columns.count; i++) {line + = Row[i].  
                ToString () + ","; line = line. Substring (0, line.  
     
                Length-1) + "\ n"; Sw.  
            Write (line); } SW.  
            Close (); Fs.  
Close ();        }  
    }