Excel Connection String for ADO.NET

You will first need a connection string to connect to the Excel Workbook, which would be the following:

[pre 0]

This says the spreadsheet is located in the current directory and called Book1.xls, and the first row is a header row containing the names of the columns.

Read Excel Spreadsheet using ADO.NET and DbDataReader

Once you have the connection string all normal ADO.NET coding applies. Here is some sample code that reads each row of the excel worksheet using DbDataReader. You don’t have to use the DbProviderFactory Classes. I thought I would show it just for kicks.

[pre 1][pre 2][pre 3][pre 4][pre 5][pre 6][pre 7][pre 8][pre 9][pre 10][pre 11][pre 12][pre 13][pre 14][pre 15][pre 16][pre 17][pre 18][pre 19][pre 20][pre 21][pre 22][pre 23][pre 24]

Read Excel Spreadsheet using ADO.NET and DataSet

Here is another example of reading an Excel spreadsheet using ADO.NET and a DataSet.

[pre 25][pre 26][pre 27][pre 28][pre 29][pre 30][pre 31][pre 32][pre 33][pre 34][pre 35][pre 36][pre 37][pre 38][pre 39][pre 40][pre 41][pre 42][pre 43][pre 44][pre 45][pre 46]

I was binding to the Janus GridEx Control, which is why you see gridEX1 above. You could easily replace those 2 lines with

[pre 47]

Inserting a Row into Excel Using ADO.NET

Here I will add a 4th city, Tampa, to the list of cities in Florida. This inserts it right into the Excel Worksheet as you would expect.

[pre 48][pre 49][pre 50][pre 51][pre 52][pre 53][pre 54][pre 55][pre 56][pre 57][pre 58][pre 59][pre 60][pre 61][pre 62][pre 63][pre 64]

Updating Excel Using ADO.NET

Let’s modify the name of the first city from Bradenton to Venice in the Excel Spreadsheet using ADO.NET:

[pre 65][pre 66][pre 67][pre 68][pre 69][pre 70][pre 71][pre 72][pre 73][pre 74][pre 75][pre 76][pre 77][pre 78][pre 79][pre 80][pre 81]

Conclusion

It is just too cool that we can use ADO.NET and the OleDb Managed Data Provider in the .NET Framework to insert, update, and delete information in an Excel Spreadsheet like it was a database.

Advertisements