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)