Sample code details for the C # EXCEL Import Export Class (OLE DB method)

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)!

