COM

GemBox.Spreadsheet can be used in other programming languages through COM. Due to the COM limitations, GemBox.Spreadsheet exposes a ComHelper class that provides overrides for some API members not supported through COM.

The example demonstrates how to create a new ExcelFile and populate it with data and formulas. It also shows how to style and format cells. At the end, document is saved in XLSX format.

Notes

  • GemBox.Spreadsheet assembly needs to be registered in order for this example to work. You can find more information about the COM registration here.
  • Python code requires the pywin32 extension to be installed.

Wrapper assembly

If you need to use many GemBox.Spreadsheet classes and methods via COM Interop, consider creating a .NET wrapper assembly that does all the work within and exposes a minimal set of classes and methods to the unmanaged code. This will enable you to avoid various COM limitations and take the advantage of GemBox.Spreadsheet's full capabilites.

Screenshot

COM Example Screenshot

See the full code below.

' Create ComHelper object and set license. 
' NOTE: If you're using a Professional version you'll need to put your serial key below.
Set comHelper = CreateObject("GemBox.Spreadsheet.ComHelper")
comHelper.ComSetLicense("FREE-LIMITED-KEY")

' Create new ExcelFile object and add new worksheet.
Set workbook = CreateObject("GemBox.Spreadsheet.ExcelFile")
Set worksheet = workbook.Worksheets.Add("Sheet1")

' Format first row columns.
Set header = worksheet.Cells.GetSubrange("A1", "B1")
header.Merged = true
header.Value = "GemBox.Spreadsheet COM Example"
header.Style.HorizontalAlignment = 2
header.Style.Font.Weight = 700

' Set column A width and values.
Set column = comHelper.GetColumn(worksheet, 0)
column.Width = 20 * 256
column.Cells.Item(1).Value = "1 + 1 ="
column.Cells.Item(2).Value = "B2 * 2 ="
column.Cells.Item(3).Value = "B3 * 120% ="
column.Cells.Item(4).Value = "SUM(B2:B4) ="

' Set column B width and formulas.
Set column = comHelper.GetColumn(worksheet, 1)
column.Width = 20 * 256
column.Cells.Item(1).Formula = "=1 + 1"
column.Cells.Item(2).Formula = "=B2 * 2"
column.Cells.Item(3).Formula = "=B3 * 120%"
column.Cells.Item(4).Formula = "=SUM(B2:B4)"

' Calculate all worksheet formulas.
worksheet.Calculate()

' Output formula results.
Response.Write("Cell calculation results:")
For i = 1 to 4
    Response.Write(" B" & i & " = " & column.Cells.Item(i).Value)
Next

' Get output path and save workbook as XLSX file.
Dim path
path = Server.MapPath(".") & "\ComExample.xlsx"

workbook.Save(path)
Response.Write("Workbook saved as '" & path & "'")
// Create ComHelper object and set license.
// NOTE: If you're using a Professional version you'll need to put your serial key below.
$comHelper = new Com("GemBox.Spreadsheet.ComHelper"); 
$comHelper->ComSetLicense("FREE-LIMITED-KEY");

// Create new ExcelFile object and add new worksheet.
$workbook = new Com("GemBox.Spreadsheet.ExcelFile");
$worksheet = $workbook->Worksheets->Add("Sheet1");

// Format first row columns.
$header = $worksheet->Cells->GetSubrange("A1", "B1");
$header->Merged = true;
$header->Value = "GemBox.Spreadsheet COM Example";
$header->Style->HorizontalAlignment = 2;
$header->Style->Font->Weight = 700;

// Set column A width and values.
$column = $comHelper->GetColumn($worksheet, 0);
$column->Width = 20 * 256;
$column->Cells->Item(1)->Value = "1 + 1 =";
$column->Cells->Item(2)->Value = "B2 * 2 =";
$column->Cells->Item(3)->Value = "B3 * 120% =";
$column->Cells->Item(4)->Value = "SUM(B2:B4) =";

// Set column B width and formulas.
$column = $comHelper->GetColumn($worksheet, 1);
$column->Width = 20 * 256;
$column->Cells->Item(1)->Formula = "=1 + 1";
$column->Cells->Item(2)->Formula = "=B2 * 2";
$column->Cells->Item(3)->Formula = "=B3 * 120%";
$column->Cells->Item(4)->Formula = "=SUM(B2:B4)";

// Calculate all worksheet formulas.
$worksheet->Calculate();

// Output formula results.
echo "Cell calculation results:";
for ($i = 1; $i < 5; $i++)
{
    echo " B" . $i . " = " . $column->Cells->Item($i)->Value;
}

// Get output path and save workbook as XLSX file.
$path = getcwd() . "\\ComExample.xlsx";

$workbook->Save($path);
echo "Workbook saved as '" . $path . "'";
# Create ComHelper object and set license. 
# NOTE: If you're using a Professional version you'll need to put your serial key below.
import win32com.client as COM
comHelper = COM.Dispatch("GemBox.Spreadsheet.ComHelper")
comHelper.ComSetLicense("FREE-LIMITED-KEY")

# Create new ExcelFile object and add new worksheet.
workbook = COM.Dispatch("GemBox.Spreadsheet.ExcelFile")
worksheet = workbook.Worksheets.Add("Sheet1")

# Format first row columns.
header = worksheet.Cells.GetSubrange("A1", "B1")
header.Merged = True
header.Value = "GemBox.Spreadsheet COM Example"
header.Style.HorizontalAlignment = 2
header.Style.Font.Weight = 700

# Set column A width and values.
column = comHelper.GetColumn(worksheet, 0)
column.Width = 20 * 256
column.Cells.Item(1).Value = "1 + 1 ="
column.Cells.Item(2).Value = "B2 * 2 ="
column.Cells.Item(3).Value = "B3 * 120% ="
column.Cells.Item(4).Value = "SUM(B2:B4) ="

# Set column B width and formulas.
column = comHelper.GetColumn(worksheet, 1)
column.Width = 20 * 256
column.Cells.Item(1).Formula = "=1 + 1"
column.Cells.Item(2).Formula = "=B2 * 2"
column.Cells.Item(3).Formula = "=B3 * 120%"
column.Cells.Item(4).Formula = "=SUM(B2:B4)"

# Calculate all worksheet formulas.
worksheet.Calculate

# Output formula results.
print("Cell calculation results:")
for i in range(1, 4):
    print(" B" + str(i) + " = " + str(column.Cells.Item(i).Value))

# Get output path and save workbook as XLSX file.
import os
path = os.getcwd() + "\\ComExample.xlsx"

workbook.Save(path)
print("Workbook saved as '" + path + "'")

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.