How to insert data from Excel sheet (Multiple sheet) into database with dynamic name

Hi,

Namespace --> Using System.Data.OleDb;

// For Connection
string sConnection="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+Your File Name+";Extended Properties=Excel 8.0;";
OleDbConnection dbCon=new OleDbConnection(sConnection);

//Connection Open
dbCon.Open();

// Get All Sheets Name
DataTable dtSheetName=dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);

// Retrive the Data by Sheetwise
Dataset dsOutput=new DataSet();
for(int nCount=0;nCount<dtsheetname.rows.count;ncount++)>
{
string sSheetName=dtSheetName.Rows[nCount]["TABLE_NAME"].ToString();
string sQuery="Select * From ["+sSheetName+"];
OleDbCommand dbCmd=new OleDbCommand(sQuery,dbCon);
OleDbDataAdapter dbDa=new OleDbDataAdapter(dbCmd);
DataTable dtData=new DataTable();
dbDa.Fill(dtData);
dsOutput.Tables.Add(dtData);
}

//Connection Close
dbCon.Close();

return dsOutput;

I think its Very Useful to You.
Cheers.. ;)

You can dynamically get the list of sheets in the excel file. You can get the articles for this on CodeProject itself. here is the link for one of such article. C# - Retrieve Excel Workbook Sheet Names.[^]

Use This Link
http://www.dotnetspider.com/resources/19121-Reading-Excel-Sheet-Names-using-C.aspx

public DataTable GetExcelData(string _sfile)
        {
            EventLog.WriteEntry("GETEXCEL DATA", "GET EXCELDATE STARTED");
            //string strConn;
            //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            //"Data Source= " + _sfile + ";" +
            //"Extended Properties=Excel 8.0;";
            //OleDbConnection objConn = new OleDbConnection(strConn);
            //objConn.Open();
            //OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [sheet1$]", objConn);
            //OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            OleDbConnection xlconn = new OleDbConnection();
            xlconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + _sfile + "';Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
            xlconn.Open();
            DataTable dtExcel = new DataTable();
            dtExcel = xlconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            OleDbDataAdapter xlda = new OleDbDataAdapter();
            DataTable xldt = new DataTable();

            if (dtExcel != null)
            {

                String[] excelSheetNames = new String[dtExcel.Rows.Count];
                int i = 0;
                EventLog.WriteEntry("Loop", (dtExcel.Rows.Count).ToString());
                foreach (DataRow row in dtExcel.Rows)
                {
                    EventLog.WriteEntry("Excel FOR Loop", dtExcel.Rows.Count.ToString());
                    excelSheetNames[i] = row["TABLE_NAME"].ToString();
                    EventLog.WriteEntry("ExcelSheet Name", excelSheetNames[i].ToString());
                    string lastChars = excelSheetNames[i].Substring(excelSheetNames[i].Length - 1);
                    EventLog.WriteEntry(" lastChar", lastChars);
                    if (lastChars == "_")
                    {
                        excelSheetNames[i] = excelSheetNames[i].Remove(excelSheetNames[i].Length - 1);
                       EventLog.WriteEntry("After Remove" ,excelSheetNames[i]);
                    }

                    string strQ = "select * from [" + excelSheetNames[i] + "]";
                    xlda = new OleDbDataAdapter(strQ, xlconn);
                    EventLog.WriteEntry("ExcelSheet Name After", excelSheetNames[i].ToString());
                    //    DataTable xldt = new DataTable();
                    xlda.Fill(xldt);

                    // xldt.AcceptChanges();
                    i++;
                }
            }


            //objAdapter1.SelectCommand = objCmdSelect;
            //DataTable objDt1 = new DataTable();
            //objAdapter1.Fill(objDt1);
            xlconn.Close();

            return xldt;
        }