public static class Excelhelper {#region import//<summary>//import Excel (default sheet)/// </summary>//<param name= "FileName" ></param>//<returns></returns> public static System.Data.DataTable Impexceldt (string filename) {return Impexceldt (fileName, "Sheet1") ; }///<summary>//Excel Import///</summary>//<param name= "FileName" ></ param>//<param name= "SheetName" ></param>///<returns></returns> Public Static System.Data.DataTable Impexceldt (String fileName, String sheetname) {try { if (! File.exists (FileName)) {return null; } string Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + FileName + "; Extended properties= ' Excel 8.0; Hdr=false;imex=1 ' "; OleDbConnection myconn = new OleDbConnection (Strcon); String strcom = "SELECT * FROM [" + SheetName + "$]"; MyConn.Open (); OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn); DataSet myDataSet = new DataSet (); Mycommand.fill (myDataSet, "[" + SheetName + "$]"); Myconn.close (); System.Data.DataTable dt = mydataset.tables[0]; return DT; } catch (Exception ex) {throw ex; }} #endregion #region Export to Excel///<summary>//Export data to the specified Excel file///&L t;/summary>//<param name= "ListView" >system.windows.forms.listview, specifying the data source to export </param>///&L T;param name= "destFileName" > Specify destination file path </param>//<param name= "tableName" > table name to Export to </param> <param name= "OverWrite" > Specify whether to overwrite tables that already exist </param> <returns> number of rows exported records </returns> public static int exportToExcel (System.Data.DataTable dt, string de Stfilename, String tableName) {if (file.exists (destFileName)) {File.delete ( destFileName); }//Get field name string szfields = ""; String szvalues = ""; for (int i = 0; i < dt. Columns.count; i++) {szfields + = "[" + dt. Columns[i] + "],"; } szfields = Szfields.trimend (', '); Define data connection OleDbConnection connection = new OleDbConnection (); Connection. ConnectionString = GetConnectionString (destFileName); OleDbCommand command = new OleDbCommand (); Command. Connection = Connection; Command.commandtype = CommandType.Text; Open the database connection try {connection. Open (); } catch {throw new EXception ("Destination file path error. "); }//Create database table try {command. CommandText = Getcreatetablesql ("[" + TableName + "]", Szfields.split (', ')); Command. ExecuteNonQuery (); } catch (Exception ex) {//If overwrite is allowed delete existing data throw ex; } try {//Loop data------------------------------------------int RECORDC Ount = 0; for (int i = 0; i < dt. Rows.Count; i++) {szvalues = ""; for (int j = 0; j < dt. Columns.count; J + +) {szvalues + = "'" + dt. ROWS[I][J] + "',"; } szvalues = Szvalues.trimend (', '); Combine SQL statements and execute string szsql = "INSERT into [" + TableName + "] (" + Szfields + ") VALUES (" + szvalues + ") )"; Command.commandtext = szSQL; RecordCount + = command. ExecuteNonQuery (); } connection. Close (); return recordCount; } catch (Exception ex) {throw ex; }}//Get connection string private static string GetConnectionString (String fullPath) {string s Zconnection; Szconnection = "Provider=Microsoft.Jet.OLEDB.4.0; Extended properties=excel 8.0;data source= "+ fullPath; return szconnection; }//Get SQL statement to create TABLE private static string Getcreatetablesql (String tableName, string[] fields) { String szSQL = "CREATE TABLE" + TableName + "("; for (int i = 0; i < fields. Length; i++) {szSQL + = Fields[i] + "VARCHAR (200),"; } szSQL = Szsql.trimend (', ') + ")"; return szsql; } #endregion}
The above is the content of sample code details for C # EXCEL Import Export Class (OLE DB), more about topic.alibabacloud.com (www.php.cn)!