SakuraMacaron2.Excel

Download ver.1.0.3.1 Read this page in Japanese

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

Reading Tables

Creating, Updating, and Replacing Tables

Excel Control

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:

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.

Using UpdateInsertTable for Updating and Inserting Rows

If you want to update existing rows and also insert new rows for IDs that don’t exist in the table, use the UpdateInsertTable method instead of UpdateTable.

The following example demonstrates how UpdateInsertTable works. When you provide data with both existing IDs (which will be updated) and new IDs (which will be inserted as new rows):

// Example 2b: Using UpdateInsertTable to update existing rows and insert new rows (C#)

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

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

private void button2b_Click(object sender, RibbonControlEventArgs e)
{
    // Create data for updating and inserting into the table
    // Existing IDs (1, 2) will be updated, new IDs (3, 4) will be inserted
    var data = new List<Example2b>() {
        new Example2b { Id = 1, Title = "Updated Title 1", Value = "Updated Value 10" },
        new Example2b { Id = 2, Title = "Updated Title 2", Value = "Updated Value 20" },
        new Example2b { Id = 3, Title = "New Title 3", Value = "New Value 30" },
        new Example2b { Id = 4, Title = "New Title 4", Value = "New Value 40" }
    };

    // Update the active Excel table with the specified data
    // Existing rows will be updated, new rows will be inserted
    ExcelMacaron.ActiveTable.UpdateInsertTable(data);
}

The key difference between UpdateTable and UpdateInsertTable:

Example 3: Creating a New Excel Table with InsertTable

When you need to create a completely new Excel table (when no table exists yet), use the InsertTable method. This method creates a new table at the specified location and populates it with your data.

The data preparation and class definition are similar to the previous examples, but InsertTable is used when you want to create a fresh table rather than update an existing one.

// Example 3: Creating a new Excel table with initial data (C#)

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

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

private void button3_Click(object sender, RibbonControlEventArgs e)
{
    // Create initial data for the new table
    var data = new List<Example3>() {
        new Example3 { Id = 1, Title = "First Item", Value = "Value 1" },
        new Example3 { Id = 2, Title = "Second Item", Value = "Value 2" },
        new Example3 { Id = 3, Title = "Third Item", Value = "Value 3" }
    };

    // Insert a new Excel table at the active cell with the specified data
    ListObject newTable = ExcelMacaron.ActiveSheet.InsertTable(data);
    
    // The method returns the newly created table, which you can use for further operations
    Debug.WriteLine($"Created new table with {newTable.ListRows.Count} rows");
}

You can also specify where to insert the table at a specific cell:

// Insert table at a specific cell
ListObject table2 = ExcelMacaron.ActiveSheet.GetCell("C5").InsertTable(data);

When to use each method:

Example 4: Using ReadOnlyCell Attribute to Create Read-Only Columns

The ReadOnlyCell attribute allows you to make specific columns read-only in your Excel tables. When you apply this attribute to a property, the corresponding Excel column will be protected using data validation rules, preventing users from easily modifying the values.

Important Note: Read-only protection is implemented using Excel’s data validation feature, not password-based worksheet protection. While this prevents typical user input, it’s not absolutely unbreakable – users can still modify values through Excel’s advanced features. However, it effectively prevents accidental changes during normal Excel usage.

Creating a Table with Read-Only Columns

First, define a class with the ReadOnlyCell attribute applied to properties you want to make read-only:

// Example 4: Creating a table with read-only Title column (C#)

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

    [ReadOnlyCell]
    public string Title { get; set; }    
    
    public string Value { get; set; }
}

private void button4_Click(object sender, RibbonControlEventArgs e)
{
    // Create initial data for the new table
    var data = new List<Example4>() {
        new Example4 { Id = 1, Title = "Read-Only Title 1", Value = "Editable Value 1" },
        new Example4 { Id = 2, Title = "Read-Only Title 2", Value = "Editable Value 2" },
        new Example4 { Id = 3, Title = "Read-Only Title 3", Value = "Editable Value 3" }
    };

    // Insert a new Excel table - the Title column will be read-only
    ListObject newTable = ExcelMacaron.ActiveCell.InsertTable(data);
    
    Debug.WriteLine($"Created table with read-only Title column: {newTable.ListRows.Count} rows");
}

Behavior with UpdateTable and UpdateInsertTable

When you use UpdateTable or UpdateInsertTable on tables with ReadOnlyCell properties, those columns will not be updated, even if you provide new values in your data:

// Example 4b: Demonstrating read-only behavior with updates (C#)

private void button4b_Click(object sender, RibbonControlEventArgs e)
{
    // Attempt to update the table, including read-only Title values
    var data = new List<Example4>() {
        new Example4 { Id = 1, Title = "This will NOT change (read-only)", Value = "Updated Value 1" },
        new Example4 { Id = 2, Title = "This will NOT change (read-only)", Value = "Updated Value 2" },
        new Example4 { Id = 4, Title = "New Read-Only Title 4", Value = "New Value 4" }  // New row
    };

    // UpdateInsertTable will:
    // - Update the Value column for existing rows (Id 1, 2)
    // - NOT update the Title column for existing rows (read-only protection)
    // - Insert new row (Id 4) with all values including Title (allowed during insertion)
    ExcelMacaron.ActiveTable.UpdateInsertTable(data);
    
    Debug.WriteLine("Update completed - existing Title values remain unchanged");
}

Key Points about ReadOnlyCell behavior:

Example 5: Using ReplaceTable Method for Conditional Data Replacement

The ReplaceTable method is a powerful feature for replacing data in Excel tables based on conditions. Unlike UpdateTable or UpdateInsertTable, ReplaceTable executes the replacement lambda expression (second argument) only for rows where the condition lambda expression (first argument) returns true.

Implementing Conditional Data Replacement

// Example 5: Using ReplaceTable for conditional data replacement (C#)

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

    [ReadOnlyCell]
    public string Title { get; set; }    
    
    public int Value { get; set; }  // Using int type for Value
}

private void button5_Click(object sender, RibbonControlEventArgs e)
{
    // Use ReplaceTable to conditionally replace data
    // First argument: Condition lambda (only rows where Value < 0)
    // Second argument: Replacement lambda (change Value to 0)
    ExcelMacaron.ActiveTable.ReplaceTable<Example5>(
        (data) => data.Value < 0,  // Condition: only when Value is negative
        (data) =>
        {
            // ReadOnlyCell attribute means data.Title won't change the table's Title even if modified
            data.Value = 0;  // Replace negative values with 0
        });
        
    Debug.WriteLine("ReplaceTable completed - only negative values were replaced with 0");
}

Understanding ReplaceTable Behavior

Key differences between ReplaceTable and UpdateTable:

ReplaceTable execution flow:

  1. Reads each row of the Excel table and creates data objects
  2. Evaluates the first lambda expression (where condition)
  3. Only when the condition is true, executes the second lambda expression (replace logic)
  4. Writes the modified data back to the Excel table

Combination with ReadOnlyCell:

In this example, only rows where Value is negative will be targeted, and their Value will be replaced with 0. Rows that don’t match the condition (where Value is 0 or positive) will remain completely unchanged.

Example 6: Using SuspendScreenUpdating to Improve Performance

The SuspendScreenUpdating method allows you to disable Excel’s screen updating during time-consuming operations, significantly improving performance. The method returns a ScreenUpdateSuspender object that must be used within a using block to ensure screen updating is properly resumed, even if an error occurs.

This approach is much safer and more convenient than manually managing Excel’s ScreenUpdating property with try-finally blocks.

// Example 6: Using SuspendScreenUpdating to improve performance during table creation (C#)

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

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

private void button6_Click(object sender, RibbonControlEventArgs e)
{
    // Create a large dataset to demonstrate performance improvement
    var data = new List<Example6>();
    for (int i = 1; i <= 1000; i++)
    {
        data.Add(new Example6 { Id = i, Title = $"Title {i}", Value = $"Value {i}" });
    }

    // Use SuspendScreenUpdating to improve performance
    using (ExcelMacaron.SuspendScreenUpdating())
    {
        // Screen updating is disabled during this block
        // Insert the table - this will be much faster without screen updates
        ListObject newTable = ExcelMacaron.ActiveSheet.InsertTable(data);
        
        Debug.WriteLine($"Created table with {newTable.ListRows.Count} rows with improved performance");
    }
    // Screen updating is automatically resumed here, even if an error occurred
    
    Debug.WriteLine("SuspendScreenUpdating completed - screen updating has been restored");
}

Benefits of SuspendScreenUpdating:

When to use SuspendScreenUpdating:

Category：Visual Studio Extensions and NuGet Packages | Date : 2025/5/19 09:33