How to access Excel data in C#

Question

I am trying to access data from Excel in C#. Ideally I want to put the data into a list or a series collection. I was using this tutorial - http://www.aspsnippets.com/Articles/Read-Excel-file-using-OLEDB-Data-Provider-in-C-Net.aspx.

It was very helpful but I think he missed out the data adapter part. Here is the code I got following his example.

        string connectionString = null;
        connectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = P:\\Visual Studio 2012\\Projects\\SmartSheetAPI\\SmartSheetAPI\\bin\\Debug\\OUTPUT.xls; Extended Properties = 'excel 12.0 Xml; HDR=YES; IMEX=1;';";            

        //Establish Connection
        string dataSource = "P:\\Visual Studio 2012\\Projects\\SmartSheetAPI\\SmartSheetAPI\\bin\\Debug\\OUTPUT.xls;";
        string excelConnection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + dataSource + " Extended Properties='Excel 8.0; HDR=Yes'";
        OleDbConnection connExcel = new OleDbConnection(connectionString);
        OleDbCommand cmdExcel = new OleDbCommand();
        cmdExcel.Connection = connExcel;

        //Accessing Sheets
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        connExcel.Close();

        //access excel Sheets (tables in database)
        DataSet dataset = new DataSet();
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        da.SelectCommand = cmdExcel;
        da.Fill(dataset);
        connExcel.Close();

If you look at the bottom three lines you will notice he uses da.SelectCommand and da.Fill to fill the dataset. But I think this requires a dataadapter and he doesn't have that in his example. I have tried creating a dataadapter as below:

SqlDataAdapter dataadapter = new SqlDataAdapter();

But I get an error stating: cannot implicitly convert type 'System.Data.OleDb.OleDbCommand' to System.Data.SqlClient.SqlCommand'.

I know it is working right up to the select statement. Can someone help me I basically just want to be able to access the information I am getting in the select statement.

VIVEK P S · Accepted Answer · 2014-12-08 04:54:55Z

Accessing excel data using Oledb connection is always a headache.You can try third party controls instead, like Aspose.Usage is very simple .You can try the following code after adding the control's reference to your project.

//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream("C:\\book1.xls", FileMode.Open);

//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

//Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 7, 2, true);

//Binding the DataTable with DataGrid
dataGrid1.DataSource = dataTable;

//Closing the file stream to free all resources
fstream.Close();

shree.pat18 · Accepted Answer · 2014-12-08 04:39:21Z

1

You need an OleDBDataAdapter, not SqlDataAdapter. So, do this:

OleDBDataAdapter da = new OleDBDataAdapter(cmdExcel);
da.Fill(dataset);

Excel is an OLEDB data source, and so the classes you should be using will be prefixed with OleDb in general, just like the ones for database connectivity and manipulation are prefixed with Sql.

Documentation

answered Dec 8, 2014 at 4:39

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to access Excel data in C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related