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 capabilities.

COM Example Screenshot
# 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 + "'")
// 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", null, CP_UTF8);
$comHelper->ComSetLicense("FREE-LIMITED-KEY");

// Create new ExcelFile object and add new worksheet.
$workbook = new Com("GemBox.Spreadsheet.ExcelFile", null, CP_UTF8);
$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.
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 & "'")

Check next example or download examples from GitHub.