This is a framework for building Excel add-ins. It simplifies working with Excel tables, including reading and updating data.

Features

This framework offers capabilities to identify Excel tables (ListObjects) within workbooks and worksheets, read their data, and update or replace the contents. It also supports inserting new tables into Excel.

// Example 1: Reading values from the ID, Title, and Value columns in an Excel table (C#)

private class Example1
{
    [ExcelColumnName("ID")][Identifier]
    public int Id { get; set; }

    public string Title { get; set; }    
    public string Value { get; set; }
}

private void button1_Click(object sender, RibbonControlEventArgs e)
{
    // Read the active Excel table
    IEnumerable<Example1> rows = ExcelMacaron.ActiveTable.ReadTable<Example1>( );
}

Finding Excel Tables and Cells

• ExcelMacaron.ActiveTable : Returns the currently active Excel table (ListObject). (Other properties such as ExcelMacaron.ActiveCell, ActiveSheet, and ActiveWorkbook are also available, allowing you to work with tables, cells, sheets, and workbooks without needing to switch between ExcelMacaron and the native Application object.)
• FindTable : Locates an Excel table.
• GetCell : Retrieves a cell from a worksheet. You can specify the cell either by numeric row and column indices or by a string like “A1”. (Overloads are available, so you don’t need to distinguish between Cells and Range when accessing cells.)

Reading Tables

• ReadTable : Reads data from an Excel table and returns it as objects of the specified type.

Creating, Updating, and Replacing Tables

• InsertTable : Inserts a new Excel table and populates it with the provided data.
• UpdateTable : Updates an existing Excel table with the specified data. (Does not insert a new table.)
• UpdateInsertTable : Updates an Excel table with the specified data. Rows not present in the table will be newly inserted.
• ReplaceTable : Replaces the data in an Excel table using a lambda expression or anonymous function to define the replacement logic.
• SetReadOnlyUsingDataValidation : Makes cells read-only by applying data validation rules.

Getting Started

Install the SakuraMacaron2.Excel NuGet package into your VSTO Excel Add-in project.

Initialize ExcelMacaron.Application in the Add-in’s Startup method or in the Ribbon’s Load event to start using the features provided by ExcelMacaron.

private void ExampleRibbon_Load(object sender, RibbonUIEventArgs e)
{
    // Initialize ExcelMacaron.Application
    ExcelMacaron.Application = Globals.ThisAddIn.Application;
}

Usage Examples

This section provides example code demonstrating how to use the various features of ExcelMacaron. Each example shows how to use the library’s functionality within the event handler of a ribbon button click.

To try out these examples, follow these steps:

1. In your Excel VSTO Add-in project, add a new item and select Ribbon (Visual Designer).
2. If ExcelMacaron.Application has not been initialized yet, make sure to initialize it before using any features.
3. From the Toolbox, drag and drop a Button from the Office Ribbon Controls onto the ribbon.
4. Implement the provided example code in the event handler for the button click.

If you want to learn how to insert a table in Excel, refer to the official Microsoft documentation:

• Overview of Excel tables – Microsoft Support
• Create and format tables – Microsoft Support

These examples are designed to help you quickly understand and integrate ExcelMacaron’s capabilities into your own Excel VSTO add-in projects.

Example 1: Reading Values from the Active Excel Table

To access the currently active table in the open Excel workbook, use ExcelMacaron.ActiveTable.

To read values from an Excel table, use the ReadTable method.

First, define a class that specifies the columns you want to read and their corresponding data types.
When you pass this class to ReadTable, it will read each row from the Excel table, populate instances of the class with the data, and return the results as an enumerable collection (IEnumerable<T>).

// Example 1: Reading the values of the ID, Title, and Value columns from an Excel table (C#)

private class Example1
{
    [ExcelColumnName("ID")][Identifier]
    public int Id { get; set; }

    public string Title { get; set; }    
    public string Value { get; set; }
}

private void button1_Click(object sender, RibbonControlEventArgs e)
{
    // Reading Data from the Active Excel Table
    IEnumerable<Example1> rows = ExcelMacaron.ActiveTable.ReadTable<Example1>( );

    foreach (var row in rows)
    {
        // Print the contents of each row to the debug output
        Debug.WriteLine($"ID:{row.Id}, Title:{row.Title}, Value:{row.Value}");
    }
}

If you want to use different names for the Excel table columns and the properties in your class, you can use the ExcelColumnName attribute.

For example, if the column name in the Excel table is ID, but you want to name the property in your C# class Id, you can apply the ExcelColumnName attribute with the value “ID”.

This attribute is also useful when the column name in Excel contains spaces, uses non-English characters, or includes names that are not valid as C# property names.

Example 2: Updating an Excel Table

To update values in an Excel table, use the UpdateTable method.

First, define a class that specifies the names of the columns you want to update and the data to be written.

To identify which rows to update, use the Identifier attribute to specify a unique key.
In the following example, the rows where the ID column has the values 1 and 2 will be updated.

Pass an enumerable collection (IEnumerable<T>) containing the updated values to UpdateTable, and the corresponding rows in the Excel table will be updated.
In this example, a List<T> is used.

// Example 2: Updating the Title and Value columns of rows where the ID column matches in an Excel table (C#)

private class Example2
{
    [ExcelColumnName("ID")][Identifier]
    public int Id { get; set; }

    public string Title { get; set; }    
    public string Value { get; set; }
}

private void button2_Click(object sender, RibbonControlEventArgs e)
{

    // Create data for updating the table
    // Find rows by the ID column and update the Title and Value columns
    var data = new List<Example2>() {
        new Example2 { Id = 1, Title = "Updated Title 1", Value = "Updated Value 10" },
        new Example2 { Id = 2, Title = "Updated Title 2", Value = "Updated Value 20" } };

    // Update the active Excel table with the specified data
    ExcelMacaron.ActiveTable.UpdateTable(data);
}

By using the ExcelColumnName attribute, you can assign different names to Excel table columns and class properties.

In this example, the Id property is decorated with both the Identifier and ExcelColumnName attributes.
UpdateTable will look for a column named ID, and update the Title and Value columns in the rows where the value of the ID column matches the value of the Id property.

You can also combine Example 1 and Example 2: retrieve data using ReadTable, modify part of the data, and then update the Excel table using UpdateTable.