GS-Calc 24 has been released

In GS-Calc 24 Python scripting replaces the previously used JScript/VBscript script engines and adds many new functions available in scripts. Using Python and its available modules should be a huge improvement in how you can process your data using GS-Calc.

Examples:

Merging rows from multiple text files from a given folder to the current worksheet

import sys
from ctypes import *
CDLL("gsc2python39.dll")   # e.g. Python 3.9.13
import gscalc

wbook = gscalc.ActiveWorkbook();

merge = gscalc.CreateMergeParams();
merge.setPath("e:\\sample??.txt")
# or e.g. merge.setPath("e:\\my_folder\\some_text_file*.txt")
# or e.g. merge.setPath("e:\\my_folder\\some_text_file_01.txt")
# or e.g. merge.setPath("e:\\my_folder\\*.csv")
# or e.g. merge.setPath("e:\\my_folder\\")
# or e.g. merge.setPath(GSCalc.GetFolder("Find folder", "e:\\"))
# or e.g. merge.setPath(GSCalc.GetFilePath(false, "Merge from text file", "e:\\", "d:\\my_folder", ".csv"))
merge.setTable("")
merge.setFieldNames(False);

text = gscalc.CreateTextParams();
text.setSeparator(",")
text.setEncoding("utf8");

wbook.MergeRowsFromTextFile(merge, text)

Merging rows from multiple Excel XLSX files from a given folder to the current worksheet

import sys
from ctypes import *
CDLL("gsc2python39.dll")   # e.g. Python 3.9.13
import gscalc

wbook = gscalc.ActiveWorkbook();

var merge = GSCalc.CreateMergeParams();
merge.setPath("e:\\my_folder\\some_text_file??.xlsx")

# no table name - merge rows from the default worksheet in each XLSX file
merge.setTable("")
merge.setFieldNames(False)

wbook.MergeRowsFromExcelFile(merge);
# or e.g. wbook.MergeRowsFromMySQLFile(merge);
# or e.g. wbook.MergeRowsFromFile(merge);

Importing tables from multiple text files from a given folder to the current workbook

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

text = gscalc.CreateTextParams();
text.setSeparator("|")
text.setEncoding("utf8");

wbook.MergeTableFromTextFile("e:\\sample??.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\some_text_file*.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\some_text_file_01.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\*.csv", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\some_text_file??.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile(GSCalc.GetFolder("Find folder", "e:\\"), "", text);
# or e.g. wbook.MergeTableFromTextFile(GSCalc.GetFilePath(false, "Merge from text file", "e:\\", "d:\\my_folder", ".csv"), "", text);

Importing tables from multiple Excel XLSX files from a given folder to the current workbook

wbook = gscalc.ActiveWorkbook();

wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file??.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file*.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file_01.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\*.csv", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file??.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile(GSCalc.GetFolder("Find folder", "e:\\"), "");
# or e.g. wbook.MergeTableFromExcelFile(GSCalc.GetFilePath(false, "Merge from Excel XLSX file", "e:\\", "d:\\my_folder", ".xlsx"), "");

Releasing open workbook (gsc/xlsx/txt/csv…) files to allow other programs to update them at the same time

wbook = gscalc.ActiveWorkbook()
wsheet = wbook.ActiveWorksheet()

filePath = wbook.ReleaseFile();
gscalc.MessageBox("Closed file: " + filePath, "ok", 1, "information");

# You can keep on editing the workbook as it's already loaded. You have to use the Attach() method to open/connect it again
# if you want to save it or otherwise the "Save File As" message box will be displayed.

Attaching a released workbook file (gsc/xlsx/txt/csv…) to check for changes and enable saving that file

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

if wbook.AttachFile("") == 1 :
   wbook.Reload()
   # An empty parameter - file path indicates that we're attaching a file from the recent ReleaseFile().
   # AttachFile() returns 1 if the file was modified after you used ReleaseFile().
   # In that case you might want to reload it to see the changes

# After checking and reloading you can release it again
wbook.ReleaseFile();

Activating the n-th worksheet in in the current workbook (ignoring subfolders)

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

// get the first "child" element in the specified folder - in this case: the main folder 
// if path.length = 0 then there is no more worksheets or subfolders
path = wbook.GetFirstTreeItem("");

counter = 0;

// iterate through direct "child" elements of the specified folder (ignoring nested subfolders)
// to find the 3rd worksheet

while path != "" :
	if (wbook.IsFolder(path)) :
		gscalc.MessageBox("folder - " + path, "ok", 1, "information");
	else : counter = counter + 1
	if counter == 3:
		break;
	path = wbook.GetNextTreeItem(path);

if path != "" :
	# activate the 3rd worksheet
	wbook.SetActiveWorksheet(path);

Iterating through the entire tree of subfolders of a given the current workbook.

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

folders = list()

treeItem = wbook.GetFirstTreeItem("");

while True :
	if wbook.IsFolder(treeItem) :
		gscalc.MessageBox("Folder: " + treeItem, "ok", 1, "information");
		if not wbook.IsFolderEmpty(treeItem) :
			folders.append(treeItem)
			treeItem = wbook.GetFirstTreeItem(treeItem)
			continue
	elif wbook.IsWorksheet(treeItem) :
		gscalc.MessageBox("Worksheet: " + treeItem, "ok", 1, "information");

	treeItem = wbook.GetNextTreeItem(treeItem);
	if treeItem == "" and len(folders) > 0 :
		treeItem = wbook.GetNextTreeItem(folders.pop());
	if treeItem == "":
		break;

Editing worksheets

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();


wbook.SelectTreeItem("formulas");

# display amount in "b9" as thousands of dollars

formatParams = wsheet.CreateFormattingParams();
formatParams.SetCurrencyFormat("0", "$ 1.1", "$", False, False, 1);

wsheet.setSelectedRange("b9")
wsheet.SetCellFormat(formatParams);

# if the window is to be updated before the script terminates, UpdateWindow() must be called

wsheet.UpdateWindow(); 

# insert a new formula in b10

wsheet.InsertFormula("b10", "=fv(0.65%, 36, -500, -5500, 0)");
wbook.WaitForUpdate();

# repeat the formatting

wsheet.setSelectedRange("b10")
wsheet.SetCellFormat(formatParams);

# save all as a new encrypted file, specifying no path to display the "Save" dialog box

wbook.SetFilePassword(True, "blowfish", "", "dk3zPi");
# passing an empty path causes the Save File As dialog box to be displayed
wbook.SaveAs("");

Adding worksheets and folders

wbook = gscalc.OpenWorkbook("e:\\sample3.gsc", "dk3zPi");
wbook.SelectTreeItem("2d charts\\sample4");
wbook.DeleteTreeItem();

# insert a new "general" folder as the last folder of the tree

wbook.SelectTreeItem("");
wbook.InsertFolder("general");

# move the folder to the beginning of the tree

wbook.MoveTreeItem("general", wbook.GetFirstTreeItem(""));

# move the "formulas" and "data types" worksheets to "general"

wbook.MoveTreeItem("data types", "general");
wbook.MoveTreeItem("formulas", "general");

# insert a new worksheet as the last one in "general"

wbook.SelectTreeItem("general");
wbook.InsertWorksheet("more formulas");

wbook.Save();
wbook.Close();

*Opening a text file and saving it to new .gsc file

textParams = gscalc.CreateTextParams();
textParams.setSeparator("|")
textParams.setQuotingSymbol("\"")
textParams.setEncoding("utf8");
wbook = gscalc.OpenTextFile("e:\\sample1.txt", False, textParams);
wbook.SaveAs("d:\\some_test_file.gsc");

Reading / inserting a sparse matrix

from ctypes import *
CDLL("gsc2python39.dll")
import gscalc

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet()

m1, m2, m3 = wsheet.GetSparseMatrix("c4:g1000", 0)

wsheet.InsertSparseMatrix("k4", m1, m2, m3)