Read a Excel File with Sheet wise into DataSet using C#

Posted by T.saravanan under C# category on 5/30/2011 | Points: 40 | Views : 41756

Post Code |

Search Codes |

Code Home

Hi All,

Here i am given a Code Snippet for How to read a Excel file data with Sheet wise using C#

Using System.Data.OleDb;  // Namespace

 private DataSet ReadExcelData(string sFilePath)
 {
        string sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + sFilePath + ";Extended Properties=Excel 8.0;";
            OleDbConnection dbCon = new OleDbConnection(sConnection);
            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);
            }
            dbCon.Close();
            return dsOutput;      
 }

I hope its helpful.

Cheers :)

Alert Moderator

Bookmark It

Comments or Responses

Posted by: Raja on: 9/16/2010 Level:Starter | Status: [Member] | Points: 10

Hi T.Saravanan,

Thanks for the code snippet, I tried to copy-paste your code and it doesn't even compile when copy-pasting. So I have modified it a bit and pasting it here.


using System.Data;

using System.Data.OleDb;

private DataSet ReadExcelData(string sFilePath)

    {



        string sConnection="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+sFilePath+";Extended Properties=Excel 8.0;";

        OleDbConnection dbCon=new OleDbConnection(sConnection);

        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);

       }

      dbCon.Close();

     return dsOutput;

    }

Thanks

Latest Code Snipptes