ADO.NET 3.5 Cookbook, 2nd Edition

5.5. Modifying Data in a Microsoft Excel Workbook

Problem

You need to modify the contents of a Microsoft Excel workbook.

Solution

Use an OLE DB DataAdapter together with parameterized SQL insert and update statements.

The solution uses the Excel workbook Category.xlsx shown in Figure 5-8.

Figure 5-8. Excel workbook Category.xlsx

The solution creates a DataAdapter and creates parameterized insert and update SQL statements. A DataSet is created and filled from the Excel workbook using the DataAdapter. The initial contents are output to the console. Next, a new row is added and the Update() method of the DataAdapter is called to update the Excel workbook. The DataSet is reloaded and its contents output to the console. Finally, a row is updated and the Update() method of the DataAdapter is called to update the Excel workbook. The DataSet is reloaded and its contents output to the console.

The C# code in Program.cs in the project ModifyExcelData is shown in Example 5-11.

Example 5-11. File:Program.cs for ModifyExcelData solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ModifyExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;" +
                @"Data Source=..\..\..\Category.xlsx;" + "Extended Properties=\"Excel 12.0;HDR=YES\";"; string commandText = "SELECT CategoryID, CategoryName, Description " + "FROM [Sheet1$]"; ...