COM Programming: Samples

Sample Index

Creating and saving a new workbook Editing an existing workbook Copying and pasting cell ranges Formatting Browsing the worksheet tree Inserting, copying, and removing worksheets and folders Password protection and encryption Saving and editing text files Printing Saving ranges as PNG images

The following examples were created in MS Visual Studio C++. (The "Community" version can be downloaded from the Microsoft website at no cost.) The examples use "smart pointers" and function wrappers with declarations and definitions automatically generated as header and source files by MS VS C++ when you use the #import directive.

The main() definitions are skipped for brevity. These function wrappers use exceptions as a method of handling errors. If you do not want to use exceptions, please refer to those header files for instructions on using generic COM system function calls instead. For the complete list of interface definitions, please see Interfaces, methods and properties.

These interfaces are also used in GS-Calc to support scripting. In scripting languages like JScript and VBScript, the rules for creating and using the "GS-Calc.Application" object and other available objects/interfaces remain generally the same, except that you can access object properties directly without the get_... and put_... functions. In both cases, the methods and functions are identical and use the same parameters described in the Methods, properties and sample usage scripting help topic.

Before these interfaces are accessible to other programs in the Windows system, they must be registered by GS-Calc using the Register GS-Calc COM Interfaces command (found in the GS-Calc "Settings" menu).

Creating and saving a new workbook

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;

try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //create a new workbook with the default "sheet1" worksheet
    IWorkbookPtr wbook = app->NewWorkbook();

    //alternatively, add another worksheet
    //wbook->InsertWorksheet(_bstr_t("sheet2"));

    IWorksheetPtr wsheet = wbook->GetActiveWorksheet();

    //turn off automatic updating of formulas for best performance
    //
    //"default" - as defined in the global application settings
    //"automatic" - updating after each cell data change
    //"manual" - updating only with the "Update All/Worksheet" commands
    wbook->put_updateMode(_bstr_t("manual"));

    //insert a series of numbers 1 to 10000 in c1:c10000
    for (int i = 1; i <= 10000; ++i)
    {
        wchar_t buffer[20] = { 0 };
        wsheet->InsertNumberRC(i, 3, (double)i);
    }

    //alternatively, the InsertData() function can be used, however
    //it performs additional checks, conversions, autoformatting and activating "undo"
    //which results in a much slower (up to several hundreds of times) execution (see the corresponding help topic for details)
    /*
    for (int i = 1; i <= 10000; ++i)
    {
        char buffer[20] = { 0 };
        ::_itoa(i, buffer, 10);
        wsheet->InsertData(wsheet->Address(3, i), _bstr_t(buffer), true);
    }
    */

    //insert a label and a formula to sum the above numbers
    wsheet->InsertTextRC(10001, 1, _bstr_t("sample sum"));
    wsheet->InsertFormulaRC(10001, 3, _bstr_t("=sum(c1:c10000)"));

    //add a comment
    wsheet->InsertCommentsRC(10001, 3, _bstr_t("sum of numbers from c1 to c10000"));

    //if it's necessary recalculate all worksheets and update the views;
    wbook->UpdateAllWorksheets();

    //if updating is performed in the background, wait until it's complete
    wbook->WaitForUpdate();

    //or simply update the screen (as the "Insert[...]RC" functions don't refresh the screen)
    //wsheet->UpdateWindow();

    //save a workbook using the native *.gsc format;
    //specifying the *.ods extension causes saving it in the ODF format
    wbook->SaveAs(_bstr_t("e:\\sample111.gsc"));
    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Editing an existing workbook

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;

try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    //set the default active worksheet;
    //once it's set and the file is saved it'll remain the active one after re-opening;

    if (::_wcsicmp(wbook->GetActiveWorksheetPath(), _bstr_t(L"data types")))
        wbook->SetActiveWorksheet(_bstr_t(L"data types"));

    IWorksheetPtr wsheet = wbook->GetActiveWorksheet();

    //add 1.0 to the cell C1
    double v1 = wsheet->GetNumberRC(1, 3);
    v1 += 1.0;
    wsheet->InsertNumberRC(1, 3, v1);

    //not using the WaitForUpdate() function will cause terminating the current updating process
    //and restarting it after another cell change below
    //wbook->WaitForUpdate();

    //insert formula in C5 (Row 5, Col 3)
    wsheet->InsertFormulaRC(5, 3, _bstr_t("=sum(c1:c4)"));

    //if the update mode it set to "manual", update formulas explicitly;
    //wbook->UpdateActiveWorksheet();

    //if updating is performed in the background, wait until it's complete
    wbook->WaitForUpdate();

    //from the C5 cell get the formula, its value and optionally an error code;
    //note: if a formula returns a text string, the IsFormulaTextualRC() functions returns TRUE
    //and the GetTextRC() function must be used to obtain its current value
    _bstr_t formula = wsheet->GetFormulaRC(5, 3);
    double v2 = wsheet->GetNumberRC(5, 3);
    BYTE errorCode = wsheet->IsErrorRC(5, 3);

    //assuming it's a console app, print the data
    if (!errorCode)
        ::wprintf(L"%s  %s -> %g", static_cast<wchar_t*>(wsheet->AddressRC(5, 3)), static_cast<wchar_t*>(formula), v2);
    else
        ::wprintf(L"%s  %s -> error (%u)", static_cast<wchar_t*>(wsheet->AddressRC(5, 3)), static_cast<wchar_t*>(formula), errorCode);

    //clear the C1 cell;
    //deleting and formatting cells/ranges requires selecting the respective range;
    //the 2nd parameter in DeleteCells can be a string with the following substrings:
    //data numbers labels formulas formatting lists comments;
    //data = numbers labels formulas
    wsheet->put_selectedRange(wsheet->AddressRC(1, 3));
    wsheet->DeleteCells(false, _bstr_t("data"));

    //if the update mode it set to "manual", update formulas explicitly;
    //wbook->UpdateActiveWorksheet();

    //if updating is performed in the background, wait until it's complete
    wbook->WaitForUpdate();

    errorCode = wsheet->IsErrorRC(5, 3);

    //print the data again
    if (!errorCode)
        ::wprintf(L"\n%s  %s -> %g", static_cast<wchar_t*>(wsheet->AddressRC(5, 3)), static_cast<wchar_t*>(formula), wsheet->GetNumberRC(5, 3));
    else
        ::wprintf(L"\n%s  %s -> error (%u)", static_cast<wchar_t*>(wsheet->AddressRC(5, 3)), static_cast<wchar_t*>(formula), errorCode);

    //insert the n/a! error code in C1
    wsheet->InsertData(wsheet->AddressRC(1, 3), _bstr_t("#N/A!"), true);

    //if the update mode it set to "manual", update formulas explicitly;
    //wbook->UpdateActiveWorksheet();

    //if updating is performed in the background, wait until it's complete
    wbook->WaitForUpdate();

    errorCode = wsheet->IsErrorRC(5, 3);

    //print the data again
    if (!errorCode)
        ::wprintf(L"\n%s  %s -> %g", static_cast<wchar_t*>(wsheet->AddressRC(5, 3)), static_cast<wchar_t*>(formula), wsheet->GetNumberRC(5, 3));
    else
        ::wprintf(L"\n%s  %s -> error (%u)", static_cast<wchar_t*>(wsheet->AddressRC(5, 3)), static_cast<wchar_t*>(formula), errorCode);

    //save changes
    wbook->Save();
    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Copying and pasting cell ranges

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    //set the default active worksheet;
    //once it's set and the file is saved it'll remain the active one after re-opening;

    if (::_wcsicmp(wbook->GetActiveWorksheetPath(), _bstr_t(L"data types")))
        wbook->SetActiveWorksheet(_bstr_t(L"data types"));

    IWorksheetPtr wsheet = wbook->GetActiveWorksheet();

    //select the range to copy
    wsheet->put_selectedRange(wsheet->AddressRC(1, 3) + _bstr_t(L":") + wsheet->AddressRC(5, 3));

    //copy
    //1 - copy all data including formulas
    //5 - copy all data with formula values instead of formulas
    wsheet->CopyCells(1);

    //select the top-left cell for the inserted data
    wsheet->put_selectedRange(wsheet->AddressRC(1, 4));

    //paste the copied cells
    //true - paste data and formatting
    //false - paste only data, preserving the existing formatting
    wsheet->PasteCells(true);

    //select the range to paste
    wsheet->put_selectedRange(wsheet->AddressRC(1, 5) + _bstr_t(L":") + wsheet->AddressRC(15, 6));

    //paste the copied cells;
    //the data will be duplicated within that range
    wsheet->PasteCells(true);

    //if updating is performed in the background, wait until it's complete
    wbook->WaitForUpdate();

    //save changes
    wbook->Save();
    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Formatting

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    //set the default active worksheet;
    //once it's set and the file is saved it'll remain the active one after re-opening;
    if (::_wcsicmp(wbook->GetActiveWorksheetPath(), _bstr_t(L"data types")))
        wbook->SetActiveWorksheet(_bstr_t(L"data types"));

    IWorksheetPtr wsheet = wbook->GetActiveWorksheet();

    //select the range C1:C5
    wsheet->put_selectedRange(wsheet->AddressRC(1, 3) + _bstr_t(L":") + wsheet->AddressRC(5, 3));

    IFormatParamsPtr format = wsheet->CreateFormatParams();

    //set the scientific format leaving the default options
    //parameters:
    //1. a number of decimals: auto|0...14
    //2. an exponent: auto|-99...99
    format->SetScientificFormat(_bstr_t("auto"), _bstr_t("auto"));

    //format C1:C5
    wsheet->SetCellFormat(format);

    //clear previously used formatting attributes
    format->Reset();

    // set the italic attribute
    format->put_italicFont(true);

    wsheet->SetCellFormat(format);

    format->Reset();

    // set a new font and all its attributes
    format->put_italicFont(TRUE);
    format->put_underlineFont(TRUE);
    format->put_strikeoutFont(TRUE);
    format->put_boldFont(TRUE);
    format->put_fontColor(_bstr_t("white")); //or format->put_fontColor(_bstr_t("#FFFFFF"));
    format->put_fontSize(15);
    format->put_fontName(_bstr_t("Courier"));

    wsheet->SetCellFormat(format);

    format->Reset();

    //set new cell filling
    format->put_bkgColor(_bstr_t("green")); //or format->put_fontColor(_bstr_t("#00FF00"));

    //set new cell border attributes;
    //border type parameter:
    //0 - remove borders
    //1 - top
    //2 - bottom
    //4 - left
    //8 - right
    //16 - diagonal left
    //32 - diagonal right
    //64 - auto
    format->put_borderPosition(1 | 2 | 4 | 8);
    format->put_borderColor(_bstr_t("blue"));
    //border style parameter:
    //none | solid | dot | dash | dash-dot | dash-dot-dot
    format->put_borderStyle(_bstr_t("solid"));
    format->put_borderWidth(2);

    wsheet->SetCellFormat(format);

    //save changes
    wbook->Save();
    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Browsing the worksheet tree

#include <stdio.h>
#include <comdef.h>
#include <assert.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    // 1. browse the folder containing the active worksheet; don't list nested folders

    _bstr_t folder = wbook->GetParentFolder(wbook->GetActiveWorksheetPath());

    _bstr_t itemPath = wbook->GetFirstTreeItem(folder);

    size_t counter = 0;

    while (itemPath.length())
    {
        if (wbook->IsFolder(itemPath))
        {
            ::wprintf(L"\n%zu.  Folder: %s", ++counter, static_cast<wchar_t*>(itemPath));
        }
        else
        {
            ::wprintf(L"\n%zu. Worksheet: %s", ++counter, static_cast<wchar_t*>(itemPath));
            //
            //... e.g. wbook->SetActiveWorksheet(itemPath)...
            //
        }
        itemPath = wbook->GetNextTreeItem(itemPath);
    }

    ::wprintf(L"\n\n");

    // 2. browse the entire worksheet tree; list nested folders

    //an arbitrary chosen max. nesting level for the sample.gsc folders
    _bstr_t stack[5];
    size_t level = 0;

    itemPath = wbook->GetFirstTreeItem((BSTR)NULL);

    counter = 0;

    while (itemPath.length())
    {
        if (wbook->IsFolder(itemPath))
        {
            ::wprintf(L"\n%zu. Folder: %s", ++counter, static_cast<wchar_t*>(itemPath));

            _bstr_t itemPath2 = wbook->GetFirstTreeItem(itemPath);
            if (itemPath2.length() && level < 5)
            {
                stack[level++] = itemPath;
                itemPath = itemPath2;
                continue;
            }
        }
        else
        {
            ::wprintf(L"\n%zu. Worksheet: %s", ++counter, static_cast<wchar_t*>(itemPath));
            //
            //... e.g. wbook->SetActiveWorksheet(itemPath)...
            //
        }
        itemPath = wbook->GetNextTreeItem(itemPath);
        if (!itemPath.length() && level)
            itemPath = wbook->GetNextTreeItem(stack[--level]);
    }

    assert(counter == static_cast<size_t>(wbook->GetWorksheetCount()) + wbook->GetFolderCount());

    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Inserting, copying and removing worksheets and folders

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    //copy/duplicate the "data types" worksheet within the main folder;
    //the worksheet will be inserted as "data types(1)"
    wbook->CopyTreeItem(_bstr_t(L"data types"), (BSTR)NULL);

    //create a new folder in the main/root folder;
    //the InsertFolder()/InsertWorksheet() functions require names without paths as parameters
    //and folders/sheets are inserted in the active folder
    wbook->SetActiveFolder((BSTR)NULL);
    wbook->InsertFolder(_bstr_t(L"new-folder"));

    //copy the worksheet "data types" from the main folder to the folder "new-folder";
    wbook->CopyTreeItem(_bstr_t(L"data types"), _bstr_t(L"new-folder"));

    //copy it again - the worksheet name will be "data types(1)"
    wbook->CopyTreeItem(_bstr_t(L"data types"), _bstr_t(L"new-folder"));

    //create a new nested folder in the folder "new-folder";
    //the InsertFolder()/InsertWorksheet() functions require names without paths as parameters
    //and folders/sheets are inserted in the active folder
    wbook->SetActiveFolder(_bstr_t(L"new-folder"));
    wbook->InsertFolder(_bstr_t(L"new-nested-folder"));

    //move "data types(1)" to "new-nested-folder"
    wbook->MoveTreeItem(_bstr_t(L"new-folder\\data types(1)"), _bstr_t(L"new-folder\\new-nested-folder"));


    //create a new folder in the main/root folder
    wbook->SetActiveFolder((BSTR)NULL);
    wbook->InsertFolder(_bstr_t(L"new-folder2"));

    //copy the folder "2d charts" from the main folder to "new-folder2"
    wbook->CopyTreeItem(_bstr_t(L"2D charts"), _bstr_t(L"new-folder2"));

    //delete the active folder "2d charts" along with its worksheets;
    //the DeleteTreeItem() function applies to the currently selected worksheet or folder
    wbook->SetActiveFolder(_bstr_t(L"2d charts"));
    wbook->DeleteTreeItem();

    //move the "3d charts" folder to "new-folder2";
    wbook->MoveTreeItem(_bstr_t(L"3d charts"), _bstr_t(L"new-folder2"));

    //pause execution to allow for manual result verification
    ::Sleep(10000);

    //close without saving
    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Password protection and encryption

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    wbook->SetFilePassword(true, _bstr_t(L"blowfish"), _bstr_t(""), _bstr_t("Rk4@m7"));

    wbook->SaveAs(_bstr_t(L"e:\\sample_p.gsc"));
    wbook->Close();

    //open the protected sample.gsc workbook
    wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample_p.gsc"), _bstr_t("Rk4@m7"));

    //
    //...
    //

    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Saving and editing text files

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    ITextParamsPtr txt = app->CreateTextParams();
    txt->put_separator(_bstr_t("\t"));

    wbook->SetActiveWorksheet(_bstr_t(L"Pivot tables\\customers"));

    //save the "customers" worksheet as a text file;
    //don't display dialog boxes with options
    wbook->SaveAsTextFile(_bstr_t("e:\\sample_123.txt"), false, txt);

    //close the text file
    wbook->Close();


    //open the "e:\\sample_123.txt" text file;
    //show the dialog box with text options
    wbook = app->OpenTextFile(_bstr_t("e:\\sample_123.txt"), true, txt);

    //
    // ... edit the file
    //

    //to save the file with modified parameters, the SaveAsTextFile() function must be used
    txt->put_separator(_bstr_t(","));
    //set the utf16 encoding (the default one was "utf8")
    txt->put_encoding(_bstr_t("utf16"));
    wbook->SaveAsTextFile(_bstr_t("e:\\sample_123.txt"), false, txt);

    //to save it using the originally used "txt" parameters, the Save() function can be used
    //wbook->Save();

    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Printing

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    //set the default active worksheet;
    //once it's set and the file is saved it'll remain the active one after re-opening;
    if (::_wcsicmp(wbook->GetActiveWorksheetPath(), _bstr_t(L"data types")))
        wbook->SetActiveWorksheet(_bstr_t(L"data types"));

    IWorksheetPtr wsheet = wbook->GetActiveWorksheet();

    IPrintSettingsPtr prn = wsheet->CreatePrintSettings();

    //get the default print settings for this worksheet
    wsheet->GetPrintSettings(prn);

    //modify some options
    prn->put_autoFit(true);
    prn->put_marginTop(15 /*mm*/);

    //save back the modified settings
    wsheet->SetPrintSettings(prn);

    //print the worksheet; don't display the "Print" dialog box
    wsheet->Print(false);

    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Saving ranges as *.png images

#include <stdio.h>
#include <comdef.h>

#import "E:\gscalc\gscalc.exe"

using namespace GSCALCLib;

struct StartOle
{
    StartOle() { ::CoInitialize(NULL); }
    ~StartOle() { ::CoUninitialize(); }
} _startOle;


try
{
    IApplicationPtr app;

    app.CreateInstance(L"GSCalc.Application");

    //open the sample.gsc workbook
    IWorkbookPtr wbook = app->OpenWorkbook(_bstr_t(L"e:\\sample.gsc"), (BSTR)NULL);

    //set the default active worksheet;
    //once it's set and the file is saved it'll remain the active one after re-opening;
    if (::_wcsicmp(wbook->GetActiveWorksheetPath(), _bstr_t(L"data types")))
        wbook->SetActiveWorksheet(_bstr_t(L"data types"));

    IWorksheetPtr wsheet = wbook->GetActiveWorksheet();

    wsheet->put_selectedRange(_bstr_t(L"c1:c5"));

    wsheet->SaveSelectionAsImage(_bstr_t(L"e:\\image_c1c5.png"));

    wbook->Close();
}
catch(_com_error error)
{
    // ...
}

Related Topics

Interfaces