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 MS 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. These function wrappers use exceptions as a method of handling errors. If you don't want to use exceptions, please see those headers files for how to use the 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 the other available objects and their interfaces remain in general the same except that you can access object "properties" directly, without the "get_..." and "put_..." functions. In both cases the methods/functions are the same and they use the same parameters described in see the Methods, properties and sample usage scripting help topic.

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

Creating and saving a new workbook

#include <stdio.h>
#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, i);
	}

	//alternatively, the InsertData() function can be used, however
	//it performs additional checkings, 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:c1000)"));

	//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 till 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"));
}
catch(_com_error error)
{
	// ...
}

Editing an existing workbook

#include <stdio.h>
#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 C4
	wsheet->InsertFormulaRC(5, 3, _bstr_t("=sum(c1:c4)"));

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

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

	//from the B6 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 B1 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 excplicitely;
	//wbook->UpdateActiveWorksheet();

	//if updating is performed in the background, wait till 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 B1
	wsheet->InsertData(wsheet->Address(3, 1), _bstr_t("#N/A!"), true);

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

	//if updating is performed in the background, wait till 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();

}
catch(_com_error error)
{
	// ...
}

Copying and pasting cell ranges

#include <stdio.h>
#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 till it's complete
	wbook->WaitForUpdate();

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

Formatting

#include <stdio.h>
#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;
	//boder 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"));
	//boder 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 <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);

	// 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->(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 <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()/InsertWorskeet() functions require names without paths as parameters
	//and folders/sheets are inserted in the active folder
	wbook->SetActiveFolder((BSTR)NULL);
	wbook->(_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()/InsertWorskeet() 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() functions concerns 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"));

	//some time to check the results
	::Sleep(10000);

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

Password protection and encryption

#include <stdio.h>
#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 <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 <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 <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