.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

how to Import Excel data to database using c#.net

Posted By:Pravin       Posted Date: August 27, 2012    Points: 40    Category: ADO.NET    URL: http://www.dotnetspark.com  

Import Excel data to database using c#.net, Interop.Microsoft.Office.Core.dll Microsoft.Office.Interop.Excel.dll office.dll
 

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Threading;
using System.Text;
using System.Collections;




private void Excel()
{
System.Data.DataTable dt = null;
                string strExcelConn = string.Empty;
                string strSheet1 = txtSheetName.Text;

                if (!string.IsNullOrEmpty(txtSheetName.Text))
                {
                    //string strSheet2 = txtSheetName2.Text.Trim();
                    if (Path.GetExtension(txtFilePath.Text) == ".xlsm" || Path.GetExtension(txtFilePath.Text) == ".xlsb")
                        strExcelConn = "Provider=Microsoft.ACE.OLEDB.14.0;Data Sourgce=" + txtFilePath.Text + ";Extended Properties=" + "'" + "Excel 14.0 Xml;HDR=Yes;IMEX=1" + "';";
                    else if (Path.GetExtension(txtFilePath.Text) == ".xlsx")
                        strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFilePath.Text + ";Extended Properties='Excel 12.0;HDR=YES;'";
                    //strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Sourgce=" + txtFilePath.Text + ";Extended Properties=" + "'" + "Excel 12.0 Xml;HDR=Yes;IMEX=1" + "';";
                    else if (Path.GetExtension(txtFilePath.Text) == ".xls")
                        strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFilePath.Text + ";Extended Properties=" + "'" + "Excel 8.0;HDR=Yes;IMEX=1" + "';";
                    else
                        MessageBox.Show("This file version is not currently supported.", "Call Process Manager", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    if (strExcelConn == string.Empty)
                        blnExcelFormat = false;
                    else
                    {
                        OleDbConnection exConn = new OleDbConnection(strExcelConn);
                        exConn.Open();
                        dt = exConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                        SQLDAL objSqlDal = new SQLDAL();
                        SqlCommand objComm = new SqlCommand();
                        //objSqlDal.BeginTransaction();
                        //SqlTransaction objSqlTran = objSqlDal.GetTransaction();
                        if (dt == null)
                            blnExcelFormat = false;
                        else
                        {
                            if (dt.Rows.Count >= 1)
                            {
                                OleDbCommand objOleDbCommand = null;
                                OleDbDataAdapter objOleDbDataAdapter = null;
                                DataSet dsInput = null;
                                objOleDbCommand = new OleDbCommand("SELECT * FROM [" + strSheet1 + "$]", exConn);
                                objOleDbDataAdapter = new OleDbDataAdapter();
                                objOleDbDataAdapter.SelectCommand = objOleDbCommand;
                                dsInput = new DataSet();
                                objOleDbDataAdapter.Fill(dsInput, "XLData");
                                                                if (dsInput != null)
                                {
                                    int intTotCount = dsInput.Tables[0].Rows.Count;

                                    string strValue = intTotCount.ToString();

                                    int intValue = strValue.Length - 1;
                                    strValue = strValue.Insert(intValue, ".");

                                    //intValue = Convert.ToInt32(strValue);
                                   // double intDivide =Convert.ToInt32(Math.Round(Convert.ToDouble(intTotCount/100),2));
                                    int intDivide = (intTotCount / 100) + 1;
                               
                                    
                                    for (int c = 0; c < intTotCount; c++)
                                    {
                                        objComm = objSqlDal.CreateCommand(objComm, CommandType.StoredProcedure, "InsertAgentsPages");
                                        objSqlDal.AddParameter(objComm, "@AgentName", SqlDbType.NVarChar, ParameterDirection.Input, 500, dsInput.Tables[0].Rows[c]["AgentName"]);
                                        objSqlDal.AddParameter(objComm, "@Date", SqlDbType.Date, ParameterDirection.Input, 150, dsInput.Tables[0].Rows[c]["Date"]);
                                        objSqlDal.AddParameter(objComm, "@PPD", SqlDbType.BigInt, ParameterDirection.Input, 500, dsInput.Tables[0].Rows[c]["PPD"]);
                                        objSqlDal.ExecuteQuery(objComm);
                                        backgroundWorker1.ReportProgress(c / intDivide);             
                                    }
                                    blnExcelFormat = true;
                                    MessageBox.Show("Excel details imported successfully");                                   
                                }

}


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend