Uploading an Excel sheet and importing the data into SQL Server database

Question

I am developing this simple application to upload an Excel file (.xlsx) and import the data present in that Excel worksheet into a SQL Server Express database in .NET

I'm using the following code on click of the import button after browsing and selecting the file to do it.

protected void Button1_Click(object sender, EventArgs e)
{
        String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";
        //file upload path
        string path = FileUpload1.PostedFile.FileName;
        //string path="C:\\ Users\\ Hemant\\Documents\\example.xlsx";
        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection);
        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "Excel_table";
        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();
    }

But the code doesn't run when I use

string path = FileUpload1.PostedFile.FileName;`

and even

string path="C:\ Users\ Hemant\Documents\example.xlsx";`

The dReader is unable to take the path in this format.

It is only able to take path in the following format

string path="C:\\ Users\\ Hemant\\Documents\\example.xlsx";

i.e. with the the \\ in the path.For which I have to hard code the path but we have to browse the file.

So,can any one please suggest a solution to use the path taken by the FileUpload1 to import the data?

Look at the connection string used for SQLExpress. You don't have space before the users folder. Why you are putting spaces in the path to the excel file? — Steve, May 04 '12 at 10:37

dash · Accepted Answer · 2012-05-04T11:09:30.837

You are dealing with a HttpPostedFile; this is the file that is "uploaded" to the web server. You really need to save that file somewhere and then use it, because...

...in your instance, it just so happens to be that you are hosting your website on the same machine the file resides, so the path is accessible. As soon as you deploy your site to a different machine, your code isn't going to work.

Break this down into two steps:

1) Save the file somewhere - it's very common to see this:

string saveFolder = @"C:\temp\uploads"; //Pick a folder on your machine to store the uploaded files

string filePath = Path.Combine(saveFolder, FileUpload1.FileName); 

FileUpload1.SaveAs(filePath);

Now you have your file locally and the real work can be done.

2) Get the data from the file. Your code should work as is but you can simply write your connection string this way:

string excelConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 12.0";", filePath);

You can then think about deleting the file you've just uploaded and imported.

To provide a more concrete example, we can refactor your code into two methods:

    private void SaveFileToDatabase(string filePath)
    {
        String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";

        String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
        //Create Connection to Excel work book 
        using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
        {
            //Create OleDbCommand to fetch data from Excel 
            using (OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection))
            {
                excelConnection.Open();
                using (OleDbDataReader dReader = cmd.ExecuteReader())
                {
                    using(SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                    {
                        //Give your Destination table name 
                        sqlBulk.DestinationTableName = "Excel_table";
                        sqlBulk.WriteToServer(dReader);
                    }
                }
            }
        } 
    }


    private string GetLocalFilePath(string saveDirectory, FileUpload fileUploadControl)
    {


        string filePath = Path.Combine(saveDirectory, fileUploadControl.FileName);

        fileUploadControl.SaveAs(filePath);

        return filePath;

    }

You could simply then call SaveFileToDatabase(GetLocalFilePath(@"C:\temp\uploads", FileUpload1));

Consider reviewing the other Extended Properties for your Excel connection string. They come in useful!

Other improvements you might want to make include putting your Sql Database connection string into config, and adding proper exception handling. Please consider this example for demonstration only!

Old subject, but it is very useful. I tried you demo code. It gave me an error. The error is: "Could not find a part of the path 'C:\'. I wrote the file to C: drive. Any idea ? — Haminteu, Oct 26 '15 at 03:11
@Haminteu the process that is trying to access the file might not have permission to read from that location? I'd also just double check the file path. — dash, Oct 27 '15 at 11:34

score 2 · Answer 2 · answered May 04 '12 at 10:38

2

Not sure why the file path is not working, I have some similar code that works fine. But if with two "\" it works, you can always do path = path.Replace(@"\", @"\\");

answered May 04 '12 at 10:38

Dante

3,833
4
38
55

score 1 · Answer 3 · edited May 30 '19 at 07:10

1

Try Using

string filename = Path.GetFileName(FileUploadControl.FileName);

Then Save the file at specified location using:

FileUploadControl.PostedFile.SaveAs(strpath + filename);

edited May 30 '19 at 07:10

Tharif

13,794
9
55
77

answered May 04 '12 at 10:44

Pratik

1,472
7
20
36

score 1 · Answer 4 · answered Feb 26 '16 at 19:00

You can use OpenXml SDK for *.xlsx files. It works very quickly. I made simple C# IDataReader implementation for this sdk. See here. Now you can easy import excel file to sql server database using SqlBulkCopy. It uses small memory because it reads by SAX(Simple API for XML) method (OpenXmlReader)

Example:

private static void DataReaderBulkCopySample()
{            
    using (var reader = new ExcelDataReader(@"test.xlsx"))
    {
        var cols = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetName(i)).ToArray();
        DataHelper.CreateTableIfNotExists(ConnectionString, TableName, cols);

        using (var bulkCopy = new SqlBulkCopy(ConnectionString))
        {
            // MSDN: When EnableStreaming is true, SqlBulkCopy reads from an IDataReader object using SequentialAccess, 
            // optimizing memory usage by using the IDataReader streaming capabilities
            bulkCopy.EnableStreaming = true;

            bulkCopy.DestinationTableName = TableName;
            foreach (var col in cols)
                bulkCopy.ColumnMappings.Add(col, col);

            bulkCopy.WriteToServer(reader);
        }
    }
}

score 0 · Answer 5 · answered Aug 17 '15 at 14:47

    public async Task<HttpResponseMessage> PostFormDataAsync()    //async is used for defining an asynchronous method
    {
        if (!Request.Content.IsMimeMultipartContent())
        {
            throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
        }
        var fileLocation = "";
        string root = HttpContext.Current.Server.MapPath("~/App_Data");
        MultipartFormDataStreamProvider provider = new MultipartFormDataStreamProvider(root);  //Helps in HTML file uploads to write data to File Stream
        try
        {
            // Read the form data.
        await Request.Content.ReadAsMultipartAsync(provider);

            // This illustrates how to get the file names.
            foreach (MultipartFileData file in provider.FileData)
            {
                Trace.WriteLine(file.Headers.ContentDisposition.FileName); //Gets the file name
                var filePath = file.Headers.ContentDisposition.FileName.Substring(1, file.Headers.ContentDisposition.FileName.Length - 2); //File name without the path
                File.Copy(file.LocalFileName, file.LocalFileName + filePath); //Save a copy for reading it
                fileLocation = file.LocalFileName + filePath; //Complete file location
            }
    HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, recordStatus);
            return response;
}
catch (System.Exception e)
    {
            return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, e);
    }
}
public void ReadFromExcel()
{
try
        {
            DataTable sheet1 = new DataTable();
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = fileLocation;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
            string selectSql = @"SELECT * FROM [Sheet1$]";
            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
            {
                connection.Open();
                adapter.Fill(sheet1);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }          
}

Arulshankar R · Answer 6 · 2017-12-28T05:38:51.680

A proposed solution will be:   


protected void Button1_Click(object sender, EventArgs e)
{
        try
        {
            CreateXMLFile();



        SqlConnection con = new SqlConnection(constring);
        con.Open();

        SqlCommand cmd = new SqlCommand("bulk_in", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@account_det", sw_XmlString.ToString ());

       int i= cmd.ExecuteNonQuery();
            if(i>0)
            {
                Label1.Text = "File Upload successfully";
            }
            else
            {
                Label1.Text = "File Upload unsuccessfully";
                return;

            }


        con.Close();
            }
        catch(SqlException ex)
        {
            Label1.Text = ex.Message.ToString();
        }




    }
     public void CreateXMLFile()
        {

          try
            {
                M_Filepath = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
                fileExtn = Path.GetExtension(M_Filepath);
                strGuid = System.Guid.NewGuid().ToString();
                fNameArray = M_Filepath.Split('.');
                fName = fNameArray[0];

                xlRptName = fName + "_" + strGuid + "_" + DateTime.Now.ToShortDateString ().Replace ('/','-');
                 fileName =  xlRptName.Trim()  + fileExtn.Trim() ;



                 FileUpload1.PostedFile.SaveAs(ConfigurationManager.AppSettings["ImportFilePath"]+ fileName);



                strFileName = Path.GetFileName(FileUpload1.PostedFile.FileName).ToUpper() ;
                if (((strFileName) != "DEMO.XLS") && ((strFileName) != "DEMO.XLSX"))
                {
                    Label1.Text = "Excel File Must be DEMO.XLS or DEMO.XLSX";
                }
               FileUpload1.PostedFile.SaveAs(System.Configuration.ConfigurationManager.AppSettings["ImportFilePath"] + fileName);
               lstrFilePath = System.Configuration.ConfigurationManager.AppSettings["ImportFilePath"] + fileName;
               if (strFileName == "DEMO.XLS")
                {

                    strConn = "Provider=Microsoft.JET.OLEDB.4.0;" + "Data Source=" + lstrFilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";

                } 

                if (strFileName == "DEMO.XLSX")
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + lstrFilePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;'";

                }

                strSQL = " Select [Name],[Mobile_num],[Account_number],[Amount],[date_a2] FROM [Sheet1$]";



                OleDbDataAdapter mydata = new OleDbDataAdapter(strSQL, strConn);

                mydata.TableMappings.Add("Table", "arul");
                mydata.Fill(dsExcl);
                dsExcl.DataSetName = "DocumentElement";
                intRowCnt = dsExcl.Tables[0].Rows.Count;
                intColCnt = dsExcl.Tables[0].Rows.Count;

                if(intRowCnt <1)
                {

                    Label1.Text = "No records in Excel File";
                    return;
                }
                if  (dsExcl==null)
                {

                }
                else
                    if(dsExcl.Tables[0].Rows.Count >= 1000 )
                    {

                        Label1.Text = "Excel data must be in less than 1000  ";
                    }


                for (intCtr = 0; intCtr <= dsExcl.Tables[0].Rows.Count - 1; intCtr++)
                {

                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Name"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Name"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Name should not be empty";
                        return;

                    }
                    else
                    {
                        strValid = "";
                    }



                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Mobile_num"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Mobile_num"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Mobile_num should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }

                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Account_number"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Account_number"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Account_number should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }





                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Amount"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Amount"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Amount should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }



                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["date_a2"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["date_a2"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "date_a2 should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }
                }


            }
         catch 
            {

            }

         try
         {
             if(dsExcl.Tables[0].Rows.Count >0)
             {

                 dr = dsExcl.Tables[0].Rows[0];
             }
             dsExcl.Tables[0].TableName = "arul";
             dsExcl.WriteXml(sw_XmlString, XmlWriteMode.IgnoreSchema);

         }
         catch
         {

         }
}`enter code here`

score 0 · Answer 7 · answered Mar 23 '19 at 14:31

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

protected void Button1_Click(object sender, EventArgs e)

{

    //Upload and save the file

    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);

    FileUpload1.SaveAs(excelPath);



    string conString = string.Empty;

    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

    switch (extension)

    {

        case ".xls": //Excel 97-03

            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

            break;

        case ".xlsx": //Excel 07 or higher

            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;

            break;



    }

    conString = string.Format(conString, excelPath);

    using (OleDbConnection excel_con = new OleDbConnection(conString))

    {

        excel_con.Open();

        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();

        DataTable dtExcelData = new DataTable();



        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.

        dtExcelData.Columns.AddRange(new DataColumn[2] { new DataColumn("Id", typeof(int)),

            new DataColumn("Name", typeof(string)) });



        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))

        {

            oda.Fill(dtExcelData);

        }

        excel_con.Close();



        string consString = ConfigurationManager.ConnectionStrings["dbcn"].ConnectionString;

        using (SqlConnection con = new SqlConnection(consString))

        {

            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

            {

                //Set the database table name

                sqlBulkCopy.DestinationTableName = "dbo.Table1";



                //[OPTIONAL]: Map the Excel columns with that of the database table

                sqlBulkCopy.ColumnMappings.Add("Sl", "Id");

                sqlBulkCopy.ColumnMappings.Add("Name", "Name");

                con.Open();

                sqlBulkCopy.WriteToServer(dtExcelData);

                con.Close();

            }

        }

    }

}

Copy this in web config

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

you can also refer this link : https://athiraji.blogspot.com/2019/03/how-to-upload-excel-fle-to-database.html

score 0 · Answer 8 · answered Sep 09 '20 at 05:53

 protected void btnUpload_Click(object sender, EventArgs e)
    {
        divStatusMsg.Style.Add("display", "none");
        divStatusMsg.Attributes.Add("class", "alert alert-danger alert-dismissable");
        divStatusMsg.InnerText = "";
        ViewState["Fuletypeidlist"] = "0";
        grdExcel.DataSource = null;
        grdExcel.DataBind();

        if (Page.IsValid)
        {
            bool logval = true;
            if (logval == true)
            {
                String img_1 = fuUploadExcelName.PostedFile.FileName;
                String img_2 = System.IO.Path.GetFileName(img_1);
                string extn = System.IO.Path.GetExtension(img_1);

                string frstfilenamepart = "";
                frstfilenamepart = "DateExcel" + DateTime.Now.ToString("ddMMyyyyhhmmss"); ;
                UploadExcelName.Value = frstfilenamepart + extn;
                fuUploadExcelName.SaveAs(Server.MapPath("~/Emp/DateExcel/") + "/" + UploadExcelName.Value);
                string PathName = Server.MapPath("~/Emp/DateExcel/") + "\\" + UploadExcelName.Value;
                GetExcelSheetForEmp(PathName, UploadExcelName.Value);
               
                if ((grdExcel.HeaderRow.Cells[0].Text.ToString() == "CODE") && grdExcel.HeaderRow.Cells[1].Text.ToString() == "SAL")
                {
                    GetExcelSheetForEmployeeCode(PathName);
                }
                else
                {
                    divStatusMsg.Style.Add("display", "");
                    divStatusMsg.Attributes.Add("class", "alert alert-danger alert-dismissable");
                    divStatusMsg.InnerText = "ERROR !!...Please Upload Excel Sheet in header Defined Format ";
                }

            }
        }


    }

    private void GetExcelSheetForEmployeeCode(string filename)
    {
        int count = 0;
        int selectedcheckbox = 0;
        string empcodeexcel = "";
        string empcodegrid = "";
        string excelFile = "Employee/DateExcel" + filename;
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {
            DataSet ds = new DataSet();
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Extended Properties=Excel 12.0 Xml;Data Source=" + filename;
            // Create connection. 
            objConn = new OleDbConnection(connString);
            // Opens connection with the database. 
            if (objConn.State == ConnectionState.Closed)
            {
                objConn.Open();
            }
            // Get the data table containing the schema guid, and also sheet names. 
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            // Add the sheet name to the string array. 
            // And respective data will be put into dataset table 
            foreach (DataRow row in dt.Rows)
            {
                if (i == 0)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    OleDbCommand cmd = new OleDbCommand("SELECT DISTINCT * FROM [" + excelSheets[i] + "]", objConn);
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    oleda.Fill(ds, "TABLE");
                    if (ds.Tables[0].ToString() != null)
                    {
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            for (int k = 0; k < GrdEmplist.Rows.Count; k++)
                            {
                                empcodeexcel = ds.Tables[0].Rows[j][0].ToString();
                                date.Value = ds.Tables[0].Rows[j][1].ToString();

                                Label lbl_EmpCode = (Label)GrdEmplist.Rows[k].FindControl("lblGrdCode");
                                empcodegrid = lbl_Code.Text;
                                CheckBox chk = (CheckBox)GrdEmplist.Rows[k].FindControl("chkSingle");
                                TextBox txt_Sal = (TextBox)GrdEmplist.Rows[k].FindControl("txtSal");


                                if ((empcodegrid == empcodeexcel) && (date.Value != ""))
                                {
                                    chk.Checked = true;
                                    txt_Sal.Text = date.Value;
                                    selectedcheckbox = selectedcheckbox + 1;
                                    lblSelectedRecord.InnerText = selectedcheckbox.ToString();
                                    count++;
                                }
                                if (chk.Checked == true)
                                {

                                }
                            }

                        }

                    }
                }
                i++;
            }

        }
        catch (Exception ex)
        {
            ShowMessage(ex.Message.ToString(), 0);
        }
        finally
        {
            // Clean up. 
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

    private void GetExcelSheetForEmp(string PathName, string UploadExcelName)
    {
        string excelFile = "DateExcel/" + PathName;
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {

            DataSet dss = new DataSet();
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Extended Properties=Excel 12.0 Xml;Data Source=" + PathName;
            objConn = new OleDbConnection(connString);
            objConn.Open();
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (i == 0)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + excelSheets[i] + "]", objConn);
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    oleda.Fill(dss, "TABLE");

                }
                i++;
            }
            grdExcel.DataSource = dss.Tables[0].DefaultView;
            grdExcel.DataBind();
            lblTotalRec.InnerText = Convert.ToString(grdExcel.Rows.Count);

        }

        catch (Exception ex)
        {
            ViewState["Fuletypeidlist"] = "0";
            grdExcel.DataSource = null;
            grdExcel.DataBind();
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }

    }

score -1 · Answer 9 · answered Oct 29 '18 at 13:00

 protected void btnUpload_Click(object sender, EventArgs e)
    {
          if (Page.IsValid)
            {
                bool logval = true;
                if (logval == true)
                {
                    String img_1 = fuUploadExcelName.PostedFile.FileName;
                    String img_2 = System.IO.Path.GetFileName(img_1);
                    string extn = System.IO.Path.GetExtension(img_1);

                    string frstfilenamepart = "";
                    frstfilenamepart = "Emp" + DateTime.Now.ToString("ddMMyyyyhhmmss"); ;
                    UploadExcelName.Value = frstfilenamepart + extn;
                    fuUploadExcelName.SaveAs(Server.MapPath("~/Emp/EmpExcel/") + "/" + UploadExcelName.Value);
                    string PathName = Server.MapPath("~/Emp/EmpExcel/") + "\\" + UploadExcelName.Value;
                    GetExcelSheetForEmp(PathName, UploadExcelName.Value);

                }
            }


    }

    private void GetExcelSheetForEmp(string PathName, string UploadExcelName)
    {
        string excelFile = "EmpExcel/" + PathName;
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {

            DataSet dss = new DataSet();
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Extended Properties=Excel 12.0 Xml;Data Source=" + PathName;
            objConn = new OleDbConnection(connString);
            objConn.Open();
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (i == 0)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + excelSheets[i] + "]", objConn);
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    oleda.Fill(dss, "TABLE");

                }
                i++;
            }
            grdByExcel.DataSource = dss.Tables[0].DefaultView;
            grdByExcel.DataBind();


        }

        catch (Exception ex)
        {
            ViewState["Fuletypeidlist"] = "0";
            grdByExcel.DataSource = null;
            grdByExcel.DataBind();
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }

    }

Uploading an Excel sheet and importing the data into SQL Server database

9 Answers9

Linked