Samples

To be able to use Python scripting, you need the Python package. Enabling Python scripting requires the same steps as using the Python UDF functions/formulas. Please see Installing Python and integrating it with GS-Calc.

You can create your scripts either as global scripts saved in the program settings and available to all databases or you can create scripts stored in a given GS-Calc workbook and available only after you open that workbook.
To create these scripts use the "File > Application Scripts" and "File > Database Scripts" commands.

The "(...) Scripts" dialog box enables you to organize your scripts in subfolders, copy/import/export them etc.

Sample "Scripts" screen.

Notes:

If a global script is executed when there is no open workbook, it should start with one of the application Open(...)/New(..) functions
If a workbook is already loaded, an executed script, either global or local, automatically refers to that workbook and using the Open(...) functions with the same workbook path has no effect.
The backslash occurring in the script text mostly in file path parameters must always be doubled for example:
f:\\my_folder\\file01.gsc

import sys from ctypes import * CDLL("gsc2python39.dll") # 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);

import sys from ctypes import * CDLL("gsc2python39.dll") # 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);

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);

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"), "");

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.

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();

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);

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;

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"); wbook.SaveAs("");

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();

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");

from ctypes import * CDLL("gsc2python39.dll") import gscalc2 wbook = gscalc2.ActiveWorkbook(); wsheet = wbook.ActiveWorksheet() m1, m2, m3 = wsheet.GetSparseMatrix("c4:g1000", 0) wsheet.InsertSparseMatrix("k4", m1, m2, m3)