Convert EXCEL to DataTable in the asp.net c#

Convert EXCEL to DataTable in the asp.net c#

Step 1:- Get the absolute path for the Excel sheet .I used “Sample.xlsx”

string data = MapPath("sample.xlsx").ToString();

Step2:- Create the connection string for the OLEDB connection

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +

               "Data Source=" + data + ";" +

               "Extended Properties=Excel 12.0 Xml";

Step3:- Get the list of sheets names in the excel sheet in to list

List<string> listSheet = new List<string>();

            using (OleDbConnection conn = new OleDbConnection(strConn.ToString()))

            {

                conn.Open();

                System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

               

                foreach (DataRow drSheet in dtSheet.Rows)

                {

                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))

                    {

                        listSheet.Add(drSheet["TABLE_NAME"].ToString());

                    }

                }

            }

Step4:- By using that sheet name get the table in the sheets and passed to the data table.

OleDbDataAdapter da = new OleDbDataAdapter

                    ("SELECT * FROM [" + listSheet[0]+"]", strConn);

            da.Fill(ds);

The Complete Code is here

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Microsoft.Office.Interop.Excel;

using System.Data;

using System.Data.OleDb;

namespace SampleTestingCodes

{

    public partial class ExcelToDataTable : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

         

            string data = MapPath("sample.xlsx").ToString();

            String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +

               "Data Source=" + data + ";" +

               "Extended Properties=Excel 12.0 Xml";

            DataSet ds = new DataSet();

            List<string> listSheet = new List<string>();

            using (OleDbConnection conn = new OleDbConnection(strConn.ToString()))

            {

                conn.Open();

                System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

               

                foreach (DataRow drSheet in dtSheet.Rows)

                {

                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))

                    {

                        listSheet.Add(drSheet["TABLE_NAME"].ToString());

                    }

                }

            }

            OleDbDataAdapter da = new OleDbDataAdapter

                    ("SELECT * FROM [" + listSheet[0]+"]", strConn);

            da.Fill(ds);

        }

    }

}

Thanks & Regards

LOKESH