Click here to Skip to main content
15,120,268 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
My code is:-

OleDbConnection xlconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+ strFileName + "';;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
OleDbDataAdapter xlda = new OleDbDataAdapter("select * from [sheet1$]", xlconn);
DataTable xldt = new DataTable();
xlda.Fill(xldt);


Its working fine but my problem is this code work only for sheet1
and my requirement is multiple sheet and name should be dynamic.

Awaiting for reply.....
Posted
Updated 8-Aug-10 23:04pm
v2

Try a different approach using Openrowset here

http://support.microsoft.com/kb/321686[^]
   
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.. ;)
   
Comments
kapil0411 9-Aug-10 6:36am
   
Reason for my vote of 3
good answer
Dalek Dave 30-Nov-10 5:59am
   
Great answer.
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
   
v2
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;
        }
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900