Using the new Enhanced Shapes Engine in Spread.NET 13, you can easily port your Microsoft Excel® Macro-enabled Workbooks (*.XLSM files) with custom shapes and VBA macro code to a .NET WinForms application executable.

In this example, I have created a template worksheet using Microsoft Excel® which defines custom shapes in VBA macro code, based on the code for this Car Insurance Claim sample for SpreadJS 12, the GrapeCity Spread JavaScript version (a live sample is here):

Building an Insurance App using the WinForms Enhanced Shapes Engine Figure 1: Car Insurance Claim in Microsoft Excel®

To create this workbook, I ported the JavaScript code in the SpreadJS demo sample to VBA macro code in a new Module added in the workbook. Then I ported the JavaScript code for the shape click event handler to VBA macro code for the car part shapes and confirmed that the template workbook works in Microsoft Excel® just like the SpreadJS demo sample.

The final ported workbook is here:

car_repair_excel.xlsm

Document Caching to Preserve VBA Macros

Spread.NET 13 can import your Microsoft Excel® Macro-enabled Workbooks (*.XLSM files) and keep the embedded VBA macros (and any other unsupported content, such as pivot tables, form controls, etc.) in a memory stream for later exporting. You can make changes to the workbook, worksheets, tables, charts, etc. and then export your changes merged with the saved memory stream in the resulting XLSM, and your VBA macro code should continue to work in Microsoft Excel®.

To load the XLSM and keep the VBA macros and other unsupported content, you need only use the ExcelOpenFlag.DocumentCaching flag. If you are using the Spread Designer tool, the File - Open dialog includes a drop-down at the bottom for selecting which ExcelOpenFlag enumeration(s) you want to enable before importing the file:

Building an Insurance App using the WinForms Enhanced Shapes Engine Figure 2: Checking ExcelOpenFlags.DocumentCaching flag before importing Macro-enabled Excel Workbook (XLSM) in Spread Designer

For this sample, the Spread Designer tool was not used; instead the template car_repair_excel.XLSM from above is added in the WinForms application as a new Resource named ExcelTemplate:

Building an Insurance App using the WinForms Enhanced Shapes Engine Figure 3: ExcelTemplate resource named "car_repair_excel" containing XLSM

Initializing the Form

The form constructor handles importing the XLSM from the application resources and initializing the spreadsheet control:

[C#]

Form constructor

public Form1()
{
    InitializeComponent();
    fpSpread1.Features.EnhancedShapeEngine = true;
    fpSpread1.Features.RichClipboard = true;
    using (MemoryStream s = new MemoryStream(ExcelTemplate.car_repair_excel))
    {
        fpSpread1.OpenExcel(s, FarPoint.Excel.ExcelOpenFlags.DocumentCaching);
    }
    fpSpread1.ActiveSheet.RowCount = 43;
    IWorkbook workbook = fpSpread1.AsWorkbook();
    for (int n = 0; n < workbook.ActiveSheet.Shapes.Count; n++)
    {
        IShape shape = workbook.ActiveSheet.Shapes[n];
        shape.CanMove = Moving.None;
        shape.CanRotate = false;
        shape.CanSize = Sizing.None;
        if (shape.Name == "Picture 2")
            continue;
        shape.Action += Shape_Action;
     }
     BindProperties();
}
[VB]

Form constructor

Public Sub New()
    ' This call is required by the designer.
    InitializeComponent()
    ' Add any initialization after the InitializeComponent() call.
    FpSpread1.Features.EnhancedShapeEngine = True
    FpSpread1.Features.RichClipboard = True
    Using s As MemoryStream = New MemoryStream(My.Resources.ExcelTemplate.car_repair_excel)
        FpSpread1.OpenExcel(s, FarPoint.Excel.ExcelOpenFlags.DocumentCaching)
    End Using
    FpSpread1.ActiveSheet.RowCount = 43
    Dim workbook As IWorkbook = FpSpread1.AsWorkbook()
    For n As Integer = 0 To workbook.ActiveSheet.Shapes.Count - 1
        Dim shape As IShape = workbook.ActiveSheet.Shapes(n)
        shape.CanMove = FarPoint.Win.Spread.DrawingSpace.Moving.None
        shape.CanRotate = False
        shape.CanSize = FarPoint.Win.Spread.DrawingSpace.Sizing.None
        If shape.Name = "Picture 2" Then
            Continue For
        End If
        AddHandler shape.Action, AddressOf Shape_Action
    Next n
    BindProperties()
End Sub

First, the code enables the Enhanced Shape Engine and Rich Clipboard features, then loads the template XLSM from the application resources using the ExcelOpenFlags.DocumentCaching to preserve the embedded VBA in an internal memory stream. Then the RowCount is set to 43, and each shape in the worksheet is initialized to prevent the user from moving, rotating, and sizing the shape using the CanMove, CanRotate, and CanSize properties.

The shape named "Picture 2" contains the background image of the exploded car diagram over which the shapes are layered, so that shape is skipped for the code that attaches the event handler for the Shape.Action event and the BindProperties code, which handles binding the shape properties to cell values.

Binding Shape Properties to Cell Values

The code in BindProperties handles binding the shape properties to cell values in Sheet2:

[C#]

BindProperties

private void BindProperties()
{
    IWorkbook workbook = fpSpread1.AsWorkbook();
    IShapes shapes = workbook.Worksheets[0].Shapes;
    // bind shape properties to cells
    shapes["Front"].Bindings.Add("Left", "Sheet2!B2");
    shapes["Front"].Bindings.Add("Top", "Sheet2!C2");
    shapes["Front"].Bindings.Add("Width", "Sheet2!D2");
    shapes["Front"].Bindings.Add("Height", "Sheet2!E2");
    shapes["Front"].Bindings.Add("Line", "Sheet2!F2");
    shapes["Hood"].Bindings.Add("Left", "Sheet2!B3");
    shapes["Hood"].Bindings.Add("Top", "Sheet2!C3");
    shapes["Hood"].Bindings.Add("Width", "Sheet2!D3");
    shapes["Hood"].Bindings.Add("Height", "Sheet2!E3");
    shapes["Hood"].Bindings.Add("Line", "Sheet2!F3");
    shapes["FrontWindshield"].Bindings.Add("Left", "Sheet2!B4");
    shapes["FrontWindshield"].Bindings.Add("Top", "Sheet2!C4");
    shapes["FrontWindshield"].Bindings.Add("Width", "Sheet2!D4");
    shapes["FrontWindshield"].Bindings.Add("Height", "Sheet2!E4");
    shapes["FrontWindshield"].Bindings.Add("Line", "Sheet2!F4");
    shapes["Roof"].Bindings.Add("Left", "Sheet2!B5");
    shapes["Roof"].Bindings.Add("Top", "Sheet2!C5");
    shapes["Roof"].Bindings.Add("Width", "Sheet2!D5");
    shapes["Roof"].Bindings.Add("Height", "Sheet2!E5");
    shapes["Roof"].Bindings.Add("Line", "Sheet2!F5");
    shapes["RearTop"].Bindings.Add("Left", "Sheet2!B6");
    shapes["RearTop"].Bindings.Add("Top", "Sheet2!C6");
    shapes["RearTop"].Bindings.Add("Width", "Sheet2!D6");
    shapes["RearTop"].Bindings.Add("Height", "Sheet2!E6");
    shapes["RearTop"].Bindings.Add("Line", "Sheet2!F6");
    shapes["Rear"].Bindings.Add("Left", "Sheet2!B7");
    shapes["Rear"].Bindings.Add("Top", "Sheet2!C7");
    shapes["Rear"].Bindings.Add("Width", "Sheet2!D7");
    shapes["Rear"].Bindings.Add("Height", "Sheet2!E7");
    shapes["Rear"].Bindings.Add("Line", "Sheet2!F7");
    shapes["LeftFront"].Bindings.Add("Left", "Sheet2!B8");
    shapes["LeftFront"].Bindings.Add("Top", "Sheet2!C8");
    shapes["LeftFront"].Bindings.Add("Width", "Sheet2!D8");
    shapes["LeftFront"].Bindings.Add("Height", "Sheet2!E8");
    shapes["LeftFront"].Bindings.Add("Line", "Sheet2!F8");
    shapes["LeftFrontDoor"].Bindings.Add("Left", "Sheet2!B9");
    shapes["LeftFrontDoor"].Bindings.Add("Top", "Sheet2!C9");
    shapes["LeftFrontDoor"].Bindings.Add("Width", "Sheet2!D9");
    shapes["LeftFrontDoor"].Bindings.Add("Height", "Sheet2!E9");
    shapes["LeftFrontDoor"].Bindings.Add("Line", "Sheet2!F9");
    shapes["LeftBackDoor"].Bindings.Add("Left", "Sheet2!B10");
    shapes["LeftBackDoor"].Bindings.Add("Top", "Sheet2!C10");
    shapes["LeftBackDoor"].Bindings.Add("Width", "Sheet2!D10");
    shapes["LeftBackDoor"].Bindings.Add("Height", "Sheet2!E10");
    shapes["LeftBackDoor"].Bindings.Add("Line", "Sheet2!F10");
    shapes["LeftBack"].Bindings.Add("Left", "Sheet2!B11");
    shapes["LeftBack"].Bindings.Add("Top", "Sheet2!C11");
    shapes["LeftBack"].Bindings.Add("Width", "Sheet2!D11");
    shapes["LeftBack"].Bindings.Add("Height", "Sheet2!E11");
    shapes["LeftBack"].Bindings.Add("Line", "Sheet2!F11");
    shapes["RightFront"].Bindings.Add("Left", "Sheet2!B12");
    shapes["RightFront"].Bindings.Add("Top", "Sheet2!C12");
    shapes["RightFront"].Bindings.Add("Width", "Sheet2!D12");
    shapes["RightFront"].Bindings.Add("Height", "Sheet2!E12");
    shapes["RightFront"].Bindings.Add("Line", "Sheet2!F12");
    shapes["RightFrontDoor"].Bindings.Add("Left", "Sheet2!B13");
    shapes["RightFrontDoor"].Bindings.Add("Top", "Sheet2!C13");
    shapes["RightFrontDoor"].Bindings.Add("Width", "Sheet2!D13");
    shapes["RightFrontDoor"].Bindings.Add("Height", "Sheet2!E13");
    shapes["RightFrontDoor"].Bindings.Add("Line", "Sheet2!F13");
    shapes["RightBackDoor"].Bindings.Add("Left", "Sheet2!B14");
    shapes["RightBackDoor"].Bindings.Add("Top", "Sheet2!C14");
    shapes["RightBackDoor"].Bindings.Add("Width", "Sheet2!D14");
    shapes["RightBackDoor"].Bindings.Add("Height", "Sheet2!E14");
    shapes["RightBackDoor"].Bindings.Add("Line", "Sheet2!F14");
    shapes["RightBack"].Bindings.Add("Left", "Sheet2!B15");
    shapes["RightBack"].Bindings.Add("Top", "Sheet2!C15");
    shapes["RightBack"].Bindings.Add("Width", "Sheet2!D15");
    shapes["RightBack"].Bindings.Add("Height", "Sheet2!E15");
    shapes["RightBack"].Bindings.Add("Line", "Sheet2!F15");
}
[VB]

BindProperties

Private Sub BindProperties()
    Dim workbook As IWorkbook = FpSpread1.AsWorkbook()
    Dim shapes As IShapes = workbook.Worksheets(0).Shapes
    ' bind shape properties to cells
    shapes("Front").Bindings.Add("Left", "Sheet2!B2")
    shapes("Front").Bindings.Add("Top", "Sheet2!C2")
    shapes("Front").Bindings.Add("Width", "Sheet2!D2")
    shapes("Front").Bindings.Add("Height", "Sheet2!E2")
    shapes("Front").Bindings.Add("Line", "Sheet2!F2")
    shapes("Hood").Bindings.Add("Left", "Sheet2!B3")
    shapes("Hood").Bindings.Add("Top", "Sheet2!C3")
    shapes("Hood").Bindings.Add("Width", "Sheet2!D3")
    shapes("Hood").Bindings.Add("Height", "Sheet2!E3")
    shapes("Hood").Bindings.Add("Line", "Sheet2!F3")
    shapes("FrontWindshield").Bindings.Add("Left", "Sheet2!B4")
    shapes("FrontWindshield").Bindings.Add("Top", "Sheet2!C4")
    shapes("FrontWindshield").Bindings.Add("Width", "Sheet2!D4")
    shapes("FrontWindshield").Bindings.Add("Height", "Sheet2!E4")
    shapes("FrontWindshield").Bindings.Add("Line", "Sheet2!F4")
    shapes("Roof").Bindings.Add("Left", "Sheet2!B5")
    shapes("Roof").Bindings.Add("Top", "Sheet2!C5")
    shapes("Roof").Bindings.Add("Width", "Sheet2!D5")
    shapes("Roof").Bindings.Add("Height", "Sheet2!E5")
    shapes("Roof").Bindings.Add("Line", "Sheet2!F5")
    shapes("RearTop").Bindings.Add("Left", "Sheet2!B6")
    shapes("RearTop").Bindings.Add("Top", "Sheet2!C6")
    shapes("RearTop").Bindings.Add("Width", "Sheet2!D6")
    shapes("RearTop").Bindings.Add("Height", "Sheet2!E6")
    shapes("RearTop").Bindings.Add("Line", "Sheet2!F6")
    shapes("Rear").Bindings.Add("Left", "Sheet2!B7")
    shapes("Rear").Bindings.Add("Top", "Sheet2!C7")
    shapes("Rear").Bindings.Add("Width", "Sheet2!D7")
    shapes("Rear").Bindings.Add("Height", "Sheet2!E7")
    shapes("Rear").Bindings.Add("Line", "Sheet2!F7")
    shapes("LeftFront").Bindings.Add("Left", "Sheet2!B8")
    shapes("LeftFront").Bindings.Add("Top", "Sheet2!C8")
    shapes("LeftFront").Bindings.Add("Width", "Sheet2!D8")
    shapes("LeftFront").Bindings.Add("Height", "Sheet2!E8")
    shapes("LeftFront").Bindings.Add("Line", "Sheet2!F8")
    shapes("LeftFrontDoor").Bindings.Add("Left", "Sheet2!B9")
    shapes("LeftFrontDoor").Bindings.Add("Top", "Sheet2!C9")
    shapes("LeftFrontDoor").Bindings.Add("Width", "Sheet2!D9")
    shapes("LeftFrontDoor").Bindings.Add("Height", "Sheet2!E9")
    shapes("LeftFrontDoor").Bindings.Add("Line", "Sheet2!F9")
    shapes("LeftBackDoor").Bindings.Add("Left", "Sheet2!B10")
    shapes("LeftBackDoor").Bindings.Add("Top", "Sheet2!C10")
    shapes("LeftBackDoor").Bindings.Add("Width", "Sheet2!D10")
    shapes("LeftBackDoor").Bindings.Add("Height", "Sheet2!E10")
    shapes("LeftBackDoor").Bindings.Add("Line", "Sheet2!F10")
    shapes("LeftBack").Bindings.Add("Left", "Sheet2!B11")
    shapes("LeftBack").Bindings.Add("Top", "Sheet2!C11")
    shapes("LeftBack").Bindings.Add("Width", "Sheet2!D11")
    shapes("LeftBack").Bindings.Add("Height", "Sheet2!E11")
    shapes("LeftBack").Bindings.Add("Line", "Sheet2!F11")
    shapes("RightFront").Bindings.Add("Left", "Sheet2!B12")
    shapes("RightFront").Bindings.Add("Top", "Sheet2!C12")
    shapes("RightFront").Bindings.Add("Width", "Sheet2!D12")
    shapes("RightFront").Bindings.Add("Height", "Sheet2!E12")
    shapes("RightFront").Bindings.Add("Line", "Sheet2!F12")
    shapes("RightFrontDoor").Bindings.Add("Left", "Sheet2!B13")
    shapes("RightFrontDoor").Bindings.Add("Top", "Sheet2!C13")
    shapes("RightFrontDoor").Bindings.Add("Width", "Sheet2!D13")
    shapes("RightFrontDoor").Bindings.Add("Height", "Sheet2!E13")
    shapes("RightFrontDoor").Bindings.Add("Line", "Sheet2!F13")
    shapes("RightBackDoor").Bindings.Add("Left", "Sheet2!B14")
    shapes("RightBackDoor").Bindings.Add("Top", "Sheet2!C14")
    shapes("RightBackDoor").Bindings.Add("Width", "Sheet2!D14")
    shapes("RightBackDoor").Bindings.Add("Height", "Sheet2!E14")
    shapes("RightBackDoor").Bindings.Add("Line", "Sheet2!F14")
    shapes("RightBack").Bindings.Add("Left", "Sheet2!B15")
    shapes("RightBack").Bindings.Add("Top", "Sheet2!C15")
    shapes("RightBack").Bindings.Add("Width", "Sheet2!D15")
    shapes("RightBack").Bindings.Add("Height", "Sheet2!E15")
    shapes("RightBack").Bindings.Add("Line", "Sheet2!F15")
End Sub

This code uses the IShape.Bindings.Add method to add the bindings for the shape's Left, Top, Width, Height, and Line properties to cells in Sheet2. The shape properties available for binding also include AutoShapeType, which can specify the name of the shape (e.g. "Cloud" or "Diamond"), TextEffect, which can specify _font-size bold italic brush(color) font-name _(e.g. "8.5 true false red MS Comic Sans_")_, and Fill, which can specify theme-color[SchemeThemeColors] known-color[KnownColor] image-url ARGB/HTML-code-color pattern (if pattern is used, you can define 2 colors). The Line property binding syntax is size style[LineDashStyle] brush(color) (e.g. "1 solid blue").

Shape Action

The code handling the Shape_Action event is identical to the VBA code in the car_repair_excel.XLSM template workbook:

[C#]

Shape_Action

private void Shape_Action(object sender, ShapeEventArgs e)
{
    IWorkbook workbook = fpSpread1.AsWorkbook();
    IWorksheet worksheet = workbook.ActiveSheet;
    IShapes shapes = worksheet.Shapes;
    if (e.Shape != null)
    {
        string name = e.Shape.Name;
        if (e.Shape.Fill.Transparency == 0.99)
        {
            e.Shape.Fill.Transparency = 0.5;
            if (name == "Front" || name == "Rear")
                shapes["BumperDamage"].Fill.Transparency = 0.5;
            if (name == "Roof")
                shapes["RoofDamage"].Fill.Transparency = 0.5;
            if (name == "RoofDamage")
                shapes["Roof"].Fill.Transparency = 0.5;
            if (name == "HighSeverity" || name == "MediumSeverity" || name == "LowSeverity")
            {
                shapes["HighSeverity"].Fill.Transparency = 0.99;
                shapes["MediumSeverity"].Fill.Transparency = 0.99;
                shapes["LowSeverity"].Fill.Transparency = 0.99;
                shapes[name].Fill.Transparency = 0.5;
            }
        }
        else
        {
            e.Shape.Fill.Transparency = 0.99;
            if (name == "Front" && shapes["Rear"].Fill.Transparency == 0.99)
                shapes["BumperDamage"].Fill.Transparency = 0.99;
            if (name == "Rear" && shapes["Front"].Fill.Transparency == 0.99)
                shapes["BumperDamage"].Fill.Transparency = 0.99;
            if (name == "Roof")
                shapes["RoofDamage"].Fill.Transparency = 0.99;
            if( name == "RoofDamage")
                shapes["Roof"].Fill.Transparency = 0.99;
        }
    }
}
[VB]

Shape_Action

Private Sub Shape_Action(ByVal sender As Object, ByVal e As Drawing.ShapeEventArgs)
    Dim workbook As IWorkbook = FpSpread1.AsWorkbook()
    Dim worksheet As IWorksheet = workbook.ActiveSheet
    Dim shapes As IShapes = worksheet.Shapes
    If Not IsNothing(e.Shape) Then
        Dim name As String = e.Shape.Name
        If e.Shape.Fill.Transparency = 0.99 Then
            e.Shape.Fill.Transparency = 0.5
            If name = "Front" Or name = "Rear" Then shapes("BumperDamage").Fill.Transparency = 0.5
            If name = "Roof" Then shapes("RoofDamage").Fill.Transparency = 0.5
            If name = "RoofDamage" Then shapes("Roof").Fill.Transparency = 0.5
            If name = "HighSeverity" Or name = "MediumSeverity" Or name = "LowSeverity" Then
                shapes("HighSeverity").Fill.Transparency = 0.99
                shapes("MediumSeverity").Fill.Transparency = 0.99
                shapes("LowSeverity").Fill.Transparency = 0.99
                shapes(name).Fill.Transparency = 0.5
            End If
        Else
            e.Shape.Fill.Transparency = 0.99
            If name = "Front" And shapes("Rear").Fill.Transparency = 0.99 Then shapes("BumperDamage").Fill.Transparency = 0.99
            If name = "Rear" And shapes("Front").Fill.Transparency = 0.99 Then shapes("BumperDamage").Fill.Transparency = 0.99
            If name = "Roof" Then shapes("RoofDamage").Fill.Transparency = 0.99
            If name = "RoofDamage" Then shapes("Roof").Fill.Transparency = 0.99
        End If
    End If
End Sub

This code handles toggling the Fill.Transparency between 0.99 (almost completely transparent) and 0.5 (half-transparent) to implement the selection behavior for the car part shapes. Some shapes are linked together so that selecting one also selects another, and deselecting certain shapes also deselects other related shapes.

Note that when you handle the Action event for the shape, then left-click on the shape will invoke the Action event handler and skip the regular processing of the left-click that would normally select the shape in the worksheet. When the Action event is mapped for a shape, then you must use a right-click to select the shape.

Saving the File with DocumentCaching

The code in the File - Save menu handles saving the workbook with the ExcelSaveFlags.DocumentCaching:

[C#]

SaveToolStripMenuItem_Click

private void SaveToolStripMenuItem_Click(object sender, EventArgs e)
{
    SaveFileDialog d = new SaveFileDialog();
    d.Filter = "Excel Macro-enabled Workbook (*.XLSM)|*.XLSM";
    d.FilterIndex = 0;
    d.Title = "Save File";
    if (d.ShowDialog() == DialogResult.OK)
    {
        fpSpread1.SaveExcel(d.FileName, FarPoint.Excel.ExcelSaveFlags.DocumentCaching | FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);
    }
}
[VB]

SaveToolStripMenuItem_Click

Private Sub SaveToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveToolStripMenuItem.Click
    Dim d As New SaveFileDialog
    d.Filter = "Excel Macro-enabled Workbook (*.XLSM)|*.XLSM"
    d.FilterIndex = 0
    d.Title = "Save File"
    If d.ShowDialog() = DialogResult.OK Then
        FpSpread1.SaveExcel(d.FileName, FarPoint.Excel.ExcelSaveFlags.DocumentCaching Or FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat)
    End If
End Sub

The code in SaveToolStripMenuItem_Click handles saving the file using the ExcelSageFlags.DocumentCaching flag, which will keep the VBA macro code intact inside the exported XLSM.

The Sample:

This sample is available in C# and VB:

CarInsuranceClaimCS.zip | CarInsuranceClaimVB.zip

Try Spread.NET Free for 30 Days

Download the latest version of Spread.NET

Download Now!