Excel and SQL Server data access to each other

      #region filed dataset DataSet; #endregion #region Excel Export to database///<summary>//import from Excel to DataSet//</summary        >//<param name= "filePath" >excel file path </param> public void Importtodatabase (string filePath)            {Getexceldata (FilePath);        Writetodataset (); }///<summary>///Get data from Excel///</summary>//<param name= "FilePath" >exc El file path </param> void Getexceldata (String filePath) {string Strcon = "Provider=microsoft.ace . oledb.12.0; "+" Data Source = "+ FilePath +"; "+" Extended properties= ' E Xcel 8.0;            HDR = Yes;imex = 2 ' ";            String strSQL = "SELECT * FROM [sheet1$]";            OleDbConnection oledbconnection = new OleDbConnection (Strcon);      OleDbDataAdapter OleDbDataAdapter = new OleDbDataAdapter (strSQL, OleDbConnection);      DataSet = new DataSet ();            Oledbconnection.open ();            OleDbDataAdapter.Fill (DataSet);            Oledbconnection.close ();            Dataset=null; Gc.        Collect ();        }///<summary>//writes data from DataSet to database///</summary> void Writetodataset ()            {StringBuilder strbsql = new StringBuilder ();            String Strcon = @ "Data source=panlee-pc\mssqlserver_2;initial catalog=dbimage;integrated security=true";            SqlConnection SqlConnection = new SqlConnection (Strcon);            SqlCommand SqlCommand = new SqlCommand ();            Sqlcommand.connection = sqlConnection;            Sqlconnection.open (); try {string sqlcreate = @ "Create Table Students (Study number varchar (5                                   0), name varchar (50), professional class varchar (50),  Mobile number varchar (50),                                 )";                Sqlcommand.commandtext = sqlcreate;            SqlCommand.ExecuteNonQuery (); } catch {} for (int i = 0; i < dataset.tables[0]. Rows.Count;                i++) {strbsql.append ("insert into Students (school number, name, professional class, mobile number) values ('"); for (int j = 0; J < 3; J + +) {strbsql.append (dataset.tables[0]. Rows[i]. ITEMARRAY[J].                ToString () + "', '"); } strbsql.append (Dataset.tables[0]. Rows[i]. ITEMARRAY[3].                ToString () + "')");                String strSQL = Strbsql.tostring ();                Sqlcommand.commandtext = strSQL;                SqlCommand.ExecuteNonQuery ();            Strbsql.remove (0, strbsql.length);        } sqlconnection.close ();            #endregion #region Database is exported to Excel public void Importtoexcel (string savefilename) {            Getdatabasedata ();Createexcel (Savefilename);        Writetoexcel (Savefilename); }///<summary>///Get data from Database to DataSet///</summary> public void Getdatabaseda            Ta () {String strSQL = "SELECT * from Students";            String Strcon = @ "Data source=panlee-pc\mssqlserver_2;initial catalog=dbimage;integrated security=true";            SqlConnection SqlConnection = new SqlConnection (Strcon);            SqlDataAdapter SqlDataAdapter = New SqlDataAdapter (strSQL, Strcon);             DataSet = new DataSet ();            Sqlconnection.open ();            Sqldataadapter.fill (DataSet);            Sqlconnection.close ();            DataSet = null; Gc.        Collect (); }///<summary>//Create Excel///</summary>//<param name= "Savefilename" &GT;&L t;/param> private void Createexcel (string savefilename) {Microsoft.Office.Interop.Excel.Appli cation Excel = new MicrosofT.office.interop.excel.application (); Microsoft.Office.Interop.Excel.Workbook Wbook = Excel.            APPLICATION.WORKBOOKS.ADD (Missing.Value); Excel.            Visible = true; Excel.            DisplayAlerts = false; Excel.            Alertbeforeoverwriting = true;            Wbook.saveas (Savefilename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Mi Ssing.            Value, Missing.Value);            Wbook = null; Excel.            Quit ();        Excel = null; }///<summary>///write data to Excel///</summary>//<param name= "Savefilename" ;</param> private void Writetoexcel (String savefilename) {string Strcon = "Provider=micros Oft. Ace. oledb.12.0; "+" Data Source = "+ Savefilename +"; "+" Extended propertieS= ' Excel 8.0;            HDR = Yes;imex = 2 ' ";            StringBuilder strbsql = new StringBuilder ();            OleDbConnection oledbconnection = new OleDbConnection (Strcon);            OleDbCommand oledbcom = new OleDbCommand ();            Oledbcom.connection = OleDbConnection;  Oledbconnection.open ();//school number, name, professional class, mobile number string strSQL = "CREATE Table Sheet1 (number char (50), name char (50), professional class            CHAR (50), mobile number char (50)) ";            Oledbcom.commandtext = strSQL;            Oledbcom.executenonquery (); for (int i = 0; i < dataset.tables[0]. Rows.Count;                i++) {strbsql.append ("INSERT into [sheet1$] values ('"); for (int j = 0; J < 3; J + +) {strbsql.append (dataset.tables[0]. Rows[i]. ITEMARRAY[J].                ToString () + "', '"); } strbsql.append (Dataset.tables[0]. Rows[i]. ITEMARRAY[3].                ToString () + "')");                strSQL = Strbsql.tostring (); Oledbcom.commandtext = strSQL;                Oledbcom.executenonquery ();            Strbsql.remove (0, strbsql.length);        } oledbconnection.close (); } #endregion

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Excel and SQL Server data access to each other