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):
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:
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:
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:
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
Sean Lawyer