Load data from excel file

I have an excel file with the structure as the below. I want to read data from this file. There are 2 ways to do that: - Using OLEDB to load to DB. But in this case, it seemly can not do that.

       String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFileName + ";" + "Extended Properties=Excel 8.0;";

        OleDbConnection objConn 开发者_高级运维= new OleDbConnection(sConnectionString);
        objConn.Open();
        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        objAdapter1.SelectCommand = objCmdSelect;

        DataSet objDataset1 = new DataSet();
        objAdapter1.Fill(objDataset1);

• Using Excel object. In this case, how can i know what last cell which has data?

alt text http://img31.imageshack.us/img31/6736/unstructureexcel.png

Thanks

---

The difficulty with oledb is the sheet has three header rows. Therefore you'll want to specify the range to exclude headers.

    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$A4:E65535]", objConn);

Even though you specified the max row value for the range, it will only read available data.

If this works for you, it'll be much faster and easier than Excel automation.

---

It looks like you're only using this to load the data into the database (I'm assuming SQL Server?) You should look into using SSIS (SQL Server Integration Services) to load the excel file.

here is a link to help you further