0

I have an xls file i would like to read using c# and populate the information in a data table. The code I am using is : 

public static DataTable GetExcelData(string excelFilePath)
{
    OleDbConnection objConn = null;
    string oledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 10.0;";
    objConn = new OleDbConnection(oledbConnectionString);

    if (objConn.State == ConnectionState.Closed)
    {
        objConn.Open();
    }
    var objCmdSelect = new OleDbCommand("Select * from [Sheet1$]", objConn);
    var objAdapter = new OleDbDataAdapter();
    objAdapter.SelectCommand = objCmdSelect;
    var objDataset = new DataSet();
    objAdapter.Fill(objDataset, "ExcelDataTable");
    objConn.Close();
    return objDataset.Tables[0];
}

Once this data table is populated, I need to remove the first 5 or so rows which contain header information, and loop through the data table populating an access database table. I have had no luck with this or any of the other 10,000 ways suggested. Does anyone have any information that can help me. I am running VS2010 .net 4.0 framework. Any and all help would be super appreciated.

Thanks, John

SSISPissesMeOff
  • 412
  • 1
  • 5
  • 15
  • You can do this the hard way or the easy way. The hard way is certainly assuming that a spreadsheet resembles a database. Or you can just use the classes in the Microsoft.Office.Interop.Excel namespace. – Hans Passant Jun 14 '11 at 23:25
  • the spreadsheet will always be in the same format. The process im looking for is: 1. read the spreadsheet into a temp holding table of some sort, then put that data into an access db. The field mapping is straight forward, all im looking to figure out is how to read the spread sheet into a data table, cut off the rows from the data table with the information i do not need then write the data from the data table into an access table with fields of the same name. I am thinking it cannot be that hard, yet i have had no luck at all with any method mentioned. – SSISPissesMeOff Jun 15 '11 at 00:41

1 Answers1

1

I've had a great deal of trouble trying to get Excel data into a DataTable using OLEDB. I finally solved the issue by switching to a solution that uses Excel Interop. Please see this answer for further explanation and sample code:

Importing from Excel: some cells become null

Community
  • 1
  • 1
devuxer
  • 41,681
  • 47
  • 180
  • 292