Słužba SFDocuments.Calc

Zhromadnje wužita biblioteka SFDocuments rjad metodow a kajkosćow skići, kotrež rjadowanje dokumentow LibreOffice a wobchadźenje z nimi wosnadnjeja.

Słužba SFDocuments.Calc je podklasa słužby SFDocuments.Document. Ke wšěm metodam a kajkosćam, kotrež so za słužbu Document definuja, maće z pomocu słužboweje instancy Calc přistup.

Słužba Calc koncentruje so na:

• Wobchadźenje z tabelemi w dokumenće Calc (kopěrować, zasadźić, přesunyć atd.)

• Wuměnjenje datow mjez datowymi strukturami Basic a wobłukami Calc

• Kopěrowanje a importowanje njesměrnych datowych mnóstwow

Tuta strona jenož metody a kajkosće wopisuje, kotrež su jenož na dokumenty Calc nałožujomne.

Słužbowe wuwołanje

Prjedy hač słužbu Calc wužiwaće, dyrbi so biblioteka ScriptForge začitać abo importować:

• Makra Basic se wužaduja, zo so biblioteka ScriptForge z pomocu slědowaceho přikaza začituje:
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")

• Skripty Python sej import z modula scriptforge wužaduja:
from scriptforge import CreateScriptService

Słužba Calc je podobna na słužbu UI biblioteki ScriptForge. Deleka su někotre přikłady, kak móžeće słužbu Calc wuwołać.

Slědowaca kodowa šlipka słužbowu instancu Calc wutworja, kotraž tuchwilu aktiwnemu dokumentej Calc wotpowěduje.

    Set oDoc = CreateScriptService("Calc")
  

Druha móžnosć, zo byšće instancu słužby Calc wutworił, je wužiwanje słužby UI. W slědowacym přikładźe so dokument Calc wutworja a oDoc je instanca słužby Calc:

    Dim ui As Object, oDoc As Object
    Set ui = CreateScriptService("UI")
    Set oDoc = ui.CreateDocument("Calc")
  

Abo wužiwanje metody OpenDocument ze słužby UI:

    Set oDoc = ui.OpenDocument("C:\Documents\MyFile.ods")
  

Je tež móžno, woknowe mjeno za metodu CreateScriptService podać, zo byšće słužbu Calc instancował:

    Dim oDoc As Object
    Set oDoc = CreateScriptService("SFDocuments.Calc", "MyFile.ods")
  

W přikładźe horjeka je "MyFile.ods" mjeno wočinjeneho dokumentoweho wokna. Jeli tutón argument podaty njeje, so aktiwne wokno wobkedźbuje.

Je tež móžno, słužbu Calc z pomocu dokumenta wuwołać, kotryž je přez ThisComponent referencowany. To je předewšěm wužitne, hdyž so makro w IDE Basic wuwjedźe.

    Dim oDoc As Object
    Set oDoc = CreateScriptService("Calc", ThisComponent)
  

Poruča so, resursy po wužiwanju wuswobodźić:

    Set oDoc = oDoc.Dispose()
  

Jeli wšak dokument je so z pomocu metody CloseDocument začinił, trjeba njeje, resursy z pomocu přikaza horjeka wuswobodźić.

    myDoc = CreateScriptService("Calc")
  

    ui = CreateScriptService("UI")
    myDoc = ui.CreateDocument("Calc")
  

    myDoc = ui.OpenDocument(r"C:\Documents\MyFile.ods")
  

    myDoc = CreateScriptService("SFDocuments.Calc", "MyFile.ods")
    myDoc.Dispose()
  

    bas = CreateScriptService("Basic")
    myDoc = CreateScriptService("Calc", bas.ThisComponent)
  

Wužiwanje prefiksa "SFDocuments." při wuwołanju słužby je opcionalne.

Definicije

Wjele metodow sej "Sheet" abo "Range" jako argument wužaduje. Jednotliwe cele maja so jako specialny pad Range.

Wobaj dadźa so wotwisujo wot situacije pak jako znamješkowy rjećazk pak jako referenca (=objekt) zwuraznić:

• W specifiskej instancy Calc so tabele a wobłuki jako znamješkowe rjećazki kaž "Sheet1" a "D2:F6" podawaja.

• Nimo toho kajkosći .Sheet a .Range referencu wróćatej, kotraž da so jako argument metody wužiwać, kotraž so přez druhu instancu słužby Calc wuwołuje.

Slědowacy přikład daty z dokumenta A (jako přećiwo pisanju škitany a schowany) do dokumenta B kopěruje.

    Dim oDocA As Object, oDocB As Object
    Set oDocA = ui.OpenDocument("C:\Documents\FileA.ods", Hidden := True, ReadOnly := True)
    Set oDocB = ui.OpenDocument("C:\Documents\FileB.ods")
    oDocB.CopyToRange(oDocA.Range("SheetX.D4:F8"), "D2:F6") 'CopyToRange(source, target)
  

    docA = ui.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True)
    docB = ui.OpenDocument(r"C:\Documents\FileB.ods")
    docB.CopyToRange(docA.Range("SheetX.D4:F8"), "D2:F6")
  

SheetName

Pak tabelowe mjeno jako znamješkowy rjećazk pak objekt, kotryž so wot kajkosće .Sheet generuje.

Skrótšenka "~" (tilda) aktualnu tabelu reprezentuje.

RangeName

Pak znamješkowy rjećazk, kotryž sadźbu susodnych celow w tabeli aktualneje instancy woznamjenja pak objekt, kotryž so přez kajkosć .Range generuje.

Skrótšenka "~" (tilda) aktualny wuběr reprezentuje abo prěni wubrany wobłuk, jeli wjacore wobłuki su wubrane.

Skrótšenka "*" wšě wužite cele reprezentuje.

Tabelowe mjeno je opcionalne, hdyž so wobłuk definuje. Jeli tabelowe mjeno podate njeje, so aktiwna tabela wužiwa. Wobdawace jednore pazorki a $-znamješka su dowolene, so wšak ignoruja.

Hdyž SheetName jako znamješkowy rjećazk podawaće, dyrbja so jednore pazorki wužiwać, zo bychu tabelowe mjeno wobdawali, jeli mjeno mjezoty " " abo dypki "." wobsahuje.

Slědowace přikłady ilustruja, w kotrych padach wužiwanje jednorych pazorkow je zawjazne:

      ' Wužiwanje jednorych pazorkow je opcionalne
      oDoc.clearAll("SheetA.A1:B10")
      oDoc.clearAll("'SheetA'.A1:B10")
      ' Wužiwanje jednorych pazorkow je trěbne
      oDoc.clearAll("'Sheet.A'.A1:B10")
    

Nimo kajkosće CurrentSelection słužba Calc jenož jednotliwe celowe wobłuki wobkedźbuje.

Kajkosće

Wšě kajkosće, kotrež su powšitkowne za kóždy dokument su implicitnje tež na dokumenty Calc nałožujomne. Za dalše informacije čitajće stronu pomocy dokumentoweje słužby.

Kajkosće, kotrež su specielnje za dokumenty Calc k dispoziciji:

Wopytajće websyło dokumentacije API LibreOffice, zo byšće wjace wo objektach UNO XCellRange, XSheetCellCursor a XSpreadsheet zhonił.

Metody

A1Style

Wróći wobłukowu adresu jako znamješkowy rjećazk na zakładźe tabelowych koordinatow, t. r. linkowych a špaltowych čisłow.

Jeli jenož jedyn por koordinatow je podaty, so jenož adresa jednotliweje cele wróći. Přidatne argumenty móža delnju prawu celu praworóžkateho wobłuka podać.

svc.A1Style(row1: int, column1: int, row2: int = 0; column2: int = 0; sheetname: str = "~"): str

row1, column1: Podajće linkowe a špaltowe čisła cele horjeka nalěwo we wobłuku, kotryž so ma wobkedźbować. Linkowa a špaltowe čisła so pola 1 započinaja.

row2, column2: Podajće linkowe a špaltowe čisła cele deleka naprawo we wobłuku, kotryž so ma wobkedźbować. Jeli tute argumenty podate njejsu, abo jeli hódnoty so mjeńše hač row1 a column1 podawaja, so adresa jednotliweho celoweho wobłuka wróći, kotryž so přez row1 a colum1 reprezentuje.

sheetname: Mjeno tabele, kotraž so ma k wróćenej wobłukowej adresy připowěsnyć. Tabela dyrbi eksistować. Standardna hódnota je "~", kotraž tuchwilu aktiwnej tabeli wotpowěduje.

Slědowace přikłady w Basic a Python maja "Sheet1" za tuchwilu aktiwnu tabelu.

    Set oDoc = CreateScriptService("Calc")
    addr1 = oDoc.A1Style(1, 1) ' '$Sheet1'.$A$1
    addr2 = oDoc.A1Style(2, 2, 3, 6) ' '$Sheet1'.$B$2:$F$3
    addr3 = oDoc.A1Style(2, 2, 0, 6) ' '$Sheet1'.$B$2
    addr4 = oDoc.A1Style(3, 4, 3, 8, "Sheet2") ' '$Sheet2'.$D$3:$H$3
    addr5 = oDoc.A1Style(5, 1, SheetName := "Sheet3") ' '$Sheet3'.$A$5
  

    doc = CreateScriptService("Calc")
    addr1 = doc.A1Style(1, 1) # '$Sheet1'.$A$1
    addr2 = doc.A1Style(2, 2, 3, 6) # '$Sheet1'.$B$2:$F$3
    addr3 = doc.A1Style(2, 2, 0, 6) # '$Sheet1'.$B$2
    addr4 = doc.A1Style(3, 4, 3, 8, "Sheet2") # '$Sheet2'.$D$3:$H$3
    addr5 = doc.A1Style(5, 1, sheetname="Sheet3") # '$Sheet3'.$A$5
  

Metoda A1Style da so z někajkej z wjele kajkosćow a metodow słužby Calc kombinować, kotrež sej wobłuk jako argument wužaduja, na přikład GetValue, GetFormula, ClearAll atd.

Activate

Jeli argument sheetname je podaty, so data tabela aktiwizuje a budźe tuchwilu wubrana tabela. Jeli argument faluje, so dokumentowe wokno aktiwizuje.

svc.Activate(sheetname: str = ""): bool

sheetname: Mjeno tabele, kotraž so ma w dokumenće aktiwizować. Standardna hódnota je prózdny znamješkowy rjećazk, to rěka, zo dokumentowe wokno so aktiwizuje, bjeztoho zo by so aktiwna tabela změniła.

Slědowacy přikład tabelu z mjenom "Sheet4" w tuchwilu aktiwnym dokumenće aktiwizuje.

    Dim ui as Variant, oDoc as Object
    Set ui = CreateScriptService("UI")
    Set oDoc = ui.GetDocument(ui.ActiveWindow)
    oDoc.Activate("Sheet4")
  

    ui = CreateScriptService("UI")
    myDoc = ui.GetDocument(ui.ActiveWindow)
    myDoc.Activate("Sheet4")
  

Aktiwizowanje tabele je jenož zmysłapołne, jeli so w dokumenće Calc přewjedźe. Zo byšće zawěsćił, zo maće dokument Calc k ruce, móžeće kajkosć isCalc dokumentoweho objekta wužiwać, kotraž True wróća, jeli je dokument Calc a hewak False.

Charts

Wróći pak lisćinu z mjenami wšěch diagramowych objektow w datej tabeli abo w jednotliwej słužbowej instancy Chart.

• Jeli jenož sheetname je podate, so matriks znamješkowych rjećazkow na bazy nula wróći, kotraž mjena wšěch diagramow wobsahuje.

• Jeli chartname je podate, so jednotliwy objekt wróći, kotryž požadanemu diagramej wotpowěduje. Podaty diagram dyrbi eksistować.

svc.Charts(sheetname: str, chartname: str = ""): obj

sheetname: Mjeno tabele, z kotrejež so ma lisćina diagramow wuwołać, abo hdźež je podaty diagram.

chartname: Swójske mjeno diagramoweho objekta, kotryž so ma wróćić. Jeli diagram swójske mjeno nima, da so nutřkowne objektowe mjeno wužiwać. Jeli tutón argument faluje, so lisćina diagramowych mjenow w podatej tabeli wróći.

Wužiwajće bóčnicu Nawigator, zo byšće mjena přepruwował, kotrež su diagramam w kategoriji OLE-objekty připokazane.

Slědowacy přikład ličbu diagramowych objektow w "Sheet1" pokazuje.

    Dim arrNames as Object
    arrNames = oDoc.Charts("Sheet1")
    MsgBox "There are " & UBound(arrNames) + 1 & " charts in Sheet1"
  

Slědowacy přikład ma přistup k diagramej z mjenom "MyChart" w "Sheet1" a wudawa jeho typ.

    Dim oChart as Object
    oChart = oDoc.Charts("Sheet1", "MyChart")
    MsgBox oChart.ChartType
  

    bas = CreateScriptService("Basic")
    chart_names = doc.Charts("Sheet1")
    bas.MsgBox(f"There are {len(chart_names)} charts in Sheet1")
  

    chart = doc.Charts("Sheet1", "MyChart")
    bas.MsgBox(chart.ChartType)
  

ClearAll

Zhaša wšón wobsah a wšě formaty dateho wobłuka.

Filtrowa formla da so podać, zo by postajił, kotre cele maja potrjechene być.

svc.ClearAll(range: str, opt filterformula: str, opt filterscope: str)

range: Wobłuk, kotryž so ma zhašeć, jako znamješkowy rjećazk.

filterformula: Formla Calc, kotrež so ma na daty wobłuk nałožić, zo by postajił, kotre cele su potrjechene. Podata formla dyrbi True abo False wróćić. Jeli tutón argument podaty njeje, su wšě cele we wobłuku potrjechene.

filterscope: Postaja, kak so filterformula na daty wobłuk rozšěrja. Tutón argument je zawjazny, jeli filterformula je podaty. Slědowace hódnoty so akceptuja:

• "CELL": Formla, podata w argumenće filterformula, so jónkróć za kóždu celu w rangerozšěrja.

• "ROW": Formla, podata w argumenće filterformula, so jónkróć za kóždu linku w rangerozšěrja.

• "COLUMN": Formla, podata w argumenće filterformula, so jónkróć za kóždu špaltu w rangerozšěrja.

    ' Zhaša wšě cele we wobłuku SheetX.A1:J10
    oDoc.ClearAll("SheetX.A1:J10")
    ' Zhaša wšě cele we wobłuku SheetX.A1:J10, kotrež ma hódnotu, kotraž je wjetša hač 100
    oDoc.ClearAll("SheetX.A1:J10", "=SheetX.A1>100", "CELL")
    ' Zhaša wšě linki we wobłuku SheetX.A1:J10, kotrychž suma je wjetša hač 500
    oDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:J1)>100", "ROW")
    ' Zhaša wšě špalty we wobłuku SheetX.A1:J10, kotrychž suma je wjetša hač 500
    oDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:A10)>100", "COLUMN")
  

    myDoc.ClearAll("SheetX.A1:F10")
    myDoc.ClearAll("SheetX.A1:J10", "=SheetX.A1>100", "CELL")
    myDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:J1)>100", "ROW")
    myDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:A10)>100", "COLUMN")
  

ClearFormats

Zhaša formaty a předłohi w datym wobłuku.

Filtrowa formla da so podać, zo by postajił, kotre cele maja potrjechene być.

svc.ClearFormats(range: str, opt filterformula: str, opt filterscope: str)

range: Wobłuk, kotrehož formaty a předłohi maja so zhašeć, jako znamješkowy rjećazk.

      oDoc.ClearFormats("SheetX.*")
  

    myDoc.ClearFormats("SheetX.*")
  

Čitajće metodowu dokumentaciju ClearAll za přikłady, kak so argumentaj filterformula a filterscope wužiwatej.

ClearValues

Zhaša hódnoty a formle w datym wobłuku.

Filtrowa formla da so podać, zo by postajił, kotre cele maja potrjechene być.

svc.ClearValues(range: str, opt filterformula: str, opt filterscope: str)

range: Wobłuk, kotrehož hódnoty a formle maja so zhašeć, jako znamješkowy rjećazk.

      oDoc.ClearValues("SheetX.A1:F10")
  

    myDoc.ClearValues("SheetX.A1:F10")
  

Čitajće metodowu dokumentaciju ClearAll za přikłady, kak so argumentaj filterformula a filterscope wužiwatej.

CompactLeft

Zhaša špalty podateho wobłuka, kotrež filtrej wotpowěduja, kotryž je jako Calc zwuraznjeny. Filter so na kóždu špaltu nałožuje, zo by rozsudźił, hač so špalta zhaša abo nic.

Zhašana špalta da so na wysokosć podateho wobłuka wobmjezować abo wysokosć cyłeje tabele wopřijeć. Přez to so cyłe špalty zhašeja.

Tuta metoda znamješkowy rjećazk z wobłukowej adresu zhusćeneho wobłuka wróći. Jeli wšě špalty so zhašeja, so prózdny znamješkowy rjećazk wróći.

Jeli wobłuk celow je wubrany, so wuwołanje tuteje metody na wuběr njewuskutkuje.

svc.CompactLeft(range: str, wholecolumn: bool = False, opt filterformula: str): str

range: Wobłuk, z kotrehož so špalty zhašeja, jako znamješkowy rjećazk.

wholecolumn: Jeli tute nastajenje je na True stajene, so cyła špalta z tabele zhaša. Standardna hódnota je False, štož woznamjenja, zo so zhašana špalta na wysokosć podateho wobłuka wobmjezuje.

filterformula: Filter, kotryž so ma na kóždu špaltu nałožić, zo by postajił, hač so zhaša abo nic. Filter so jako formla Calc zwuraznja, kotraž měła so na prěnju špaltu nałožić. Hdyž formla True za špaltu wróća, so ta špalta zhaša. Standardny filter wšě prózdne špalty zhaša.

Připušćmy na přikład, zo so wobłuk A1:J200 wuběra (wysokosć = 200), da standardna formla je =(COUNTBLANK(A1:A200)=200). To rěka, zo, jeli wšě 200 celow su prózdne w prěnjej špalće (špalta A), so špalta zhaša. Dźiwajće na to, zo so formla jenož nastupajo prěnju špaltu zwuraznja. Internje metoda CompactLeft tutu formlu za wšě zbytne špalty generalizuje.

Funkcije Calc, kotrež so w argumenće filterformula wužiwaja, dyrbja so z pomocu swojich jendźelskich mjenow zwuraznić. Wopytajće wikijowu stronu Lisćina funkcijow Calc za dospołnu lisćinu funkcijow Calc jendźelsce.

    ' Wšě prózdne špalty we wobłuku G1:L10 z Sheet1 zhašeć
    newrange = oDoc.CompactLeft("Sheet1.G1:L10")
    ' Slědowacy přikład je podobny, ale cyła špalta so z tabele zhaša
    newrange = oDoc.CompactLeft("Sheet1.G1:L10", WholeColumn := True)
    ' Zhaša wšě špalty, hdźež prěnja linka je z "X" markěrowana
    newrange = oDoc.CompactLeft("Sheet1.G1:L10", FilterFormula := "=(G1=""X"")")
    ' Zhaša wšě špalty, hdźež suma hódnotow w špalće je njeruna
    newrange = oDoc.CompactLeft("Sheet1.G1:L10", FilterFormula := "=(MOD(SUM(G1:G10);2)=1)")
  

    newrange = myDoc.CompactLeft("Sheet1.G1:L10")
    newrange = myDoc.CompactLeft("Sheet1.G1:L10", wholecolumn = True)
    newrange = myDoc.CompactLeft("Sheet1.G1:L10", filterformula = '=(G1="X")')
    newrange = myDoc.CompactLeft("Sheet1.G1:L10", filterformula = '=(MOD(SUM(G1:G10);2)=1)')
  

CompactUp

Zhaša linkow podateho wobłuka, kotrež filtrej wotpowěduja, kotryž je jako formla Calc zwuraznjeny. Filter so na kóždu linku nałožuje, zo by rozsudźił, hač so linka zhaša abo nic.

Zhašane linki dadźa so na šěrokosć podateho wobłuka wobmjezować abo šěrokosć cyłeje tabele wopřijeć. Přez to so cyłe linki zhašeja.

Tuta metoda znamješkowy rjećazk z wobłukowej adresu zhusćeneho wobłuka wróći. Jeli wšě linki so zhašeja, so prózdny znamješkowy rjećazk wróći.

Jeli wobłuk celow je wubrany, so wuwołanje tuteje metody na wuběr njewuskutkuje.

svc.CompactUp(range: str, wholerow: bool = False, opt filterformula: str): str

range: Wobłuk, z kotrehož so linki zhašeja, jako znamješkowy rjećazk.

wholerow: Jeli tute nastajenje je na True stajene, so cyła linka z tabele zhaša. Standardna hódnota je False. štož rěka, zo zhašana linka so na šěrokosć podateho wobłuka wobmjezuje.

filterformula: Filter, kotryž so ma na kóždu linku nałožić, zo by postajił, hač so linka zhaša abo nic. Filter so jako formla Calc zwuraznja, kotraž so měła na prěnju linku nałožić. Hdyž formla True za linku wróći, so ta linka zhaša. Standardny filter wšě prózdne linki zhaša.

Připušćmy na přikład, zo so wobłuk A1:J200 wuběra (šěrokosć = 10), da standardna formla je =(COUNTBLANK(A1:J1)=10). To rěka, zo, jeli wšě 10 celow su prózdne w prěnjej lince (linka 1), so linka zhaša. Dźiwajće na to, zo so formla jenož nastupajo prěnju linku zwuraznja. Internje metoda CompactUp tutu formlu za wšě zbytne linki generalizuje.

Funkcije Calc, kotrež so w argumenće filterformula wužiwaja, dyrbja so z pomocu swojich jendźelskich mjenow zwuraznić. Wopytajće wikijowu stronu Lisćina funkcijow Calc za dospołnu lisćinu funkcijow Calc jendźelsce.

    ' Wšě prózdne linki we wobłuku G1:L10 ze Sheet1 zhašeć
    newrange = oDoc.CompactUp("Sheet1.G1:L10")
    ' Slědowacy přikład je podobny, ale cyła linka so z tabele zhaša
    newrange = oDoc.CompactUp("Sheet1.G1:L10", WholeRow := True)
    ' Zhaša wšě linki, hdźež prěnja špalta je z "X" markěrowana
    newrange = oDoc.CompactUp("Sheet1.G1:L10", FilterFormula := "=(G1=""X"")")
    ' Zhaša wšě linki, hdźež suma hódnotow w lince je njeruna
    newrange = oDoc.CompactUp("Sheet1.G1:L10", FilterFormula := "=(MOD(SUM(G1:L1);2)=1)")
  

    newrange = myDoc.CompactUp("Sheet1.G1:L10")
    newrange = myDoc.CompactUp("Sheet1.G1:L10", wholerow = True)
    newrange = myDoc.CompactUp("Sheet1.G1:L10", filterformula = '=(G1="X")')
    newrange = myDoc.CompactUp("Sheet1.G1:L10", filterformula = '=(MOD(SUM(G1:L1);2)=1)')
  

CopySheet

Kopěruje podatu tabelu před eksistowacu tabelu abo na kónc lisćiny tabelow. Tabela, kotraž so ma kopěrować, móže we wočinjenym dokumenće Calc wobsahowana być. Wróći True, jeli kopěrowanje je wuspěšne.

svc.CopySheet(sheetname: any, newname: str, [beforesheet: any]): bool

sheetname: Mjeno tabele, kotraž so ma jako znamješkowy rjećazk abo jeje referenca jako objekt kopěrować.

newname: Mjeno tabele, kotraž so ma zasadźić. Mjeno njesmě so w dokumenće wužiwać.

beforesheet: Mjeno (znamješkowy rjećazk) abo indeks (numeriski, započinajo z 1) tabele, před kotrejž so ma kopěrowana tabela zasadźić. Tutón argument je opcionalny a standardne zadźerženje je, zo so kopěrowana tabela na poslednjej poziciji přidawa.

Slědowacy přikład kopiju tabele "SheetX" wutworja a pozicioněruje ju jako poslednju tabelu w aktualnym dokumenće. Mjeno kopěrowaneje tabele je "SheetY".

    Dim oDoc as Object
    ' Wotwołuje objekt Document aktiwneho wokna
    Set oDoc = CreateScriptService("Calc")
    oDoc.CopySheet("SheetX", "SheetY")
  

Slědowacy přikład "SheetX" z "FileA.ods" kopěruje a zasadźuje ju na poslednjej poziciji "FileB.ods" z mjenom "SheetY".

      Dim oDocA As Object : Set oDocA = ui.OpenDocument("C:\Documents\FileA.ods", Hidden := True, ReadOnly := True)
      Dim oDocB As Object : Set oDocB = ui.OpenDocument("C:\Documents\FileB.ods")
      oDocB.CopySheet(oDocA.Sheet("SheetX"), "SheetY")
  

    myDoc.CopySheet("SheetX", "SheetY")
  

    docA = ui.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True)
    docB = ui.OpenDocument(r"C:\Documents\FileB.ods")
    docB.CopySheet(docA.Sheet("SheetX"), "SheetY")
  

Zo byšće tabele mjez wočinjenymi dokumentami kopěrował, wužiwajće CopySheet. Zo byšće tabele z dokumentow kopěrował, kotrež su začinjene, wužiwajće CopySheetFromFile.

CopySheetFromFile

Kopěruje podatu tabelu ze začinjeneho dokumenta Calc a zasadźuje ju před eksistowacej tabelu abo na kóncu lisćiny tabelow dataje, na kotruž so přez objekt Document wotkazuje.

Jeli dataja njeeksistuje, zmylk nasta. Jeli dataja płaćiwa dataja Calc njeje, so prózdna tabela zasadźi. Jeli žórłowa tabela w zapodawanskej dataji njeeksistuje, so zmylkowa zdźělenka horjeka w nowo zasadźenej tabeli zasadźi.

svc.CopySheetFromFile(filename: str, sheetname: str, newname: str, [beforesheet: any]): bool

filename: Identifikuje dataju, kotraž so ma wočinić. Dyrbi so po notaciji SF_FileSystem.FileNaming měć. Dataja njesmě z hesłom škitana być.

sheetname: Mjeno tabele, kotraž so ma kopěrować, jako znamješkowy rjećazk.

newname: Mjeno kopěrowaneje tabele, kotraž so ma do dokumenta zasadźić. Mjeno njesmě so w dokumenće wužiwać.

beforesheet: Mjeno (znamješkowy rjećazk) abo indeks (numeriski, započinajo z 1) tabele, před kotrejž so ma kopěrowana tabela zasadźić. Tutón argument je opcionalny a standardne zadźerženje je, zo so kopěrowana tabela na poslednjej poziciji přidawa.

Slědowacy přikła "SheetX" z "myFile.ods" kopěruje a zasadźuje ju do dokumenta, kotryž so přez "oDoc" referencuje, jako "SheetY" na prěnjej poziciji.

    oDoc.CopySheetFromFile("C:\Documents\myFile.ods", "SheetX", "SheetY", 1)
  

    myDoc.CopySheetFromFile(r"C:\Documents\myFile.ods", "SheetX", "SheetY", 1)
  

CopyToCell

Kopěruje podaty žórłowy wobłuk (hódnoty, formle a formaty) do ciloweho wobłuka abo ciloweje cele. Metoda zadźerženje opceracije Kopěrować/Zasadźić z wobłuka do jednotliweje cele reprodukuje.

Wróća znamješkowy rjećazk, kotryž změnjeny celowy wobłuk reprezentuje. Wulkosć změnjeneho wobłuka so dospołnje přez wulkosć žórłoweho wobłuka postaja.

Žórłowy wobłuk móže k druhemu wočinjenemu dokumentej słušeć.

svc.CopyToCell(sourcerange: any, destinationcell: str): str

sourcerange: Žórłowy wobłuk jako znamješkowy rjećazk, hdyž k samsnemu dokumentej słuša, abo jako referenca, hdyž k druhemu wočinjenemu dokumentej Calc słuša.

destinationcell: Cilowa cela, hdźež so kopěrowany wobłuk zasadźi, jako znamješkowy rjećazk. Jeli wobłuk je podaty, so jenož jeho cela horjeka nalěwo wobkedźbuje.

Přichodny přikład je, hdźež so žórło a cil stej w samsnej dataji:

      oDoc.CopyToCell("SheetX.A1:F10", "SheetY.C5")
  

Slědowacy přikład ilustruje, kak móžeće wobłuk z druheho wočinjeneho dokumenta Calc kopěrować:

    Dim ui as Variant : ui = CreateScriptService("UI")
    Dim oDocSource As Object, oDocDestination As Object
    ' Žórłowy dokument w pozadku wočinić (schowany)
    Set oDocSource = ui.OpenDocument("C:\SourceFile.ods", Hidden := True, ReadOnly := True)
    Set oDocDestination = CreateScriptService("Calc")
    oDocDestination.CopyToCell(oDocSource.Range("Sheet1.C2:C4"), "SheetT.A5")
    ' Njezabywajće žórłowy dokument začinić, dokelž je so jako schowany wočinić
    oDocSource.CloseDocument()
  

    docSource = ui.OpenDocument(r"C:\Documents\SourceFile.ods", hidden = True, readonly = True)
    docDestination = CreateScriptService("Calc")
    docDestination.CopyToCell(docSource.Range("Sheet1.C2:C4"), "SheetT.A5")
    docSource.CloseDocument()
  

Zo byšće Kopěrować/Zasadźić z wobłuka do jednotliweje cele simulował, wužiwajće CopyToCell. Zo byšće Kopěrować/Zasadźić z wobłuka do wjetšeho wobłuka simulował (ze samsnymi celemi, kotrež so wjacekróć replikuja), wužiwajće CopyToRange.

CopyToRange

Kopěruje podaty žórłowy wobłuk dele a/abo naprawo (hódnoty, formle a formaty) do ciloweho wobłuka. Metoda zadźerženje operacije Kopěrować/Zasadźić ze žórłoweho wobłuka do wjetšeho ciloweho wobłuka imituje.

• Jeli wysokosć (abo šěrokosć) ciloweho wobłuka je > 1 hač linka (abo špalta), dyrbi wysokosć (abo šěrokosć) žórła <= hač wysokosć (abo šěrokosć) cila być. Hewak so ničo njestanje.

• Jeli wysokosć (abo šěrokosć) cila je = 1, so cil dele (abo doprawa) hač do wysokosće (abo šěrokosće) žórłoweho wobłuka rozšěrjuje.

Metoda znamješkowy rjećazk wróći, kotryž změnjeny celowy wobłuk reprezentuje.

Žórłowy wobłuk móže k druhemu wočinjenemu dokumenta słušeć.

svc.CopyToRange(sourcerange: any, destinationrange: str): str

sourcerange: Žórłowy wobłuk jako znamješkowy rjećazk, hdyž k samsnemu dokumentej słuša, abo jako referenca, hdyž k druhemu wočinjenemu dokumentej Calc słuša.

destinationrange: Cil kopěrowaneho celoweho wobłuka, jako znamješkowy rjećazk.

W samsnym dokumenće kopěrować:

    oDoc.CopyToRange("SheetX.A1:F10", "SheetY.C5:J5")
    ' Wróći wobłukowy znamješkowy rjećazk: "$SheetY.$C$5:$J$14"
  

Z jedneje dataje do druheje kopěrować:

    Dim oDocA As Object : Set oDocA = ui.OpenDocument("C:\Documents\FileA.ods", Hidden := True, ReadOnly := True)
    Dim oDocB As Object : Set oDocB = ui.OpenDocument("C:\Documents\FileB.ods")
    oDocB.CopyToRange(oDocA.Range("SheetX.A1:F10"), "SheetY.C5:J5")
  

    doc.CopyToRange("SheetX.A1:F10", "SheetY.C5:J5")
  

    docA = ui.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True)
    docB = ui.OpenDocument(r"C:\Documents\FileB.ods")
    docB.CopyToRange(docA.Range("SheetX.A1:F10"), "SheetY.C5:J5")
  

CreateChart

Wutworja nowy diagramowy objekt, kotryž daty w podatym wobłuku pokazuje. Wróćeny diagramowy objekt da so z pomocu słužby Chart dale manipulować.

svc.CreateChart(chartname: str, sheetname: str, range: str, columnheader: bool = False, rowheader: bool = False): obj

chartname: Swójske mjeno diagrama, kotryž so ma wutworić. Mjeno dyrbi jónkróćne w samsnej tabeli być.

sheetname: Mjeno tabele, hdźež so diagram placěruje.

range: Wobłuk, kotryž so ma jako datowe žórło za diagram wužiwać. Wobłuk móže so na někajku tabelu dokumenta Calc poćahować.

columnheader: Hdyž True, so najwyša linka wobłuka jako popisy za kategorijowu wósku abo legendu wužiwa (standard = False).

rowheader: Hdyž True, so špalta wobłuka cyle nalěwo jako popisy za kategorijowu wósku abo legendu wužiwa (standard = False).

Slědowace přikłady w Basic a Python diagram z pomocu datow wutworjeja, kotrež su we wobłuku "A:B5" tabele "Sheet1" wobsahowane a placěruja diagram w tabeli "Sheet2".

    Set oChart = oDoc.CreateChart("MyChart", "Sheet2", "Sheet1.A1:B5", RowHeader := True)
    oChart.ChartType = "Donut"
  

    chart = doc.CreateChart("MyChart", "Sheet2", "Sheet1.A1:B5", rowheader=True)
    chart.ChartType = "Donut"
  

Čitajće stronu pomocy wo słužbje Chart ScriptForge, zo byšće wjace wo tym zhonił, kak móžeće diagramowe objekty manipulować. Je móžno, kajkosće jako diagramowy typ, diagramowe a wóskowe titule a diagramowu poziciju změnić.

CreatePivotTable

Wutworja nowu pivotowu tabelu z kajkosćemi, kotrež su so přez argumenty definowali, kotrež su so metodźe přepodali.

Mjeno dyrbi so za pivotowu tabelu podać. Jeli pivotowu tabelu z samsnym mjenom hižo w cilowej tabeli eksistuje, so pivotowa tabela bjez warnowanja wuměni.

Tuta metoda znamješkowy rjećazk wróća, kotryž wobłuk wobsahuje, hdźež so nowa pivotowa tabela placěruje.

svc.CreatePivotTable(pivottablename: str, sourcerange: str, targetcell: str, datafields: str[0..*], rowfields: str[0..*], columnfields: str[0..*], filterbutton: bool = true, rowtotals: bool = true, columntotals: bool = true): str

pivottablename: Swójske mjeno noweje pivotoweje tabele.

sourcerange: Wobłuk, kotrež hrube daty jako znamješkowy rjećazk wobsahuje. Připušća so, zo prěnja linka pólne mjena wobsahuje, kotrež so přez pivotowu tabelu wužiwaja.

targetcell: Cela horjeka nalěwo, hdźež so nowa pivotowa tabela placěruje. Jeli wobłuk je podaty, so jenož cela horjeka nalěwo wobkedźbuje.

datafields: Móže pak jednotliwy znamješkowy rjećazk pak matriks być, kotraž znamješkowe rjećazki wobsahuje, kotrež pólne mjena a funkcije, kotrež so maja nałožić. Hdyž matriks je podata, dyrbi na syntaksu Array("fieldname[;function]", …) dźiwać.

Dowolene funkcije su: Sum, Count, Average, Max, Min, Product, CountNums, StDev, StDevP, Var, VarP a Median. Mjena funkcijow dyrbja so w jendźelšćinje podać. Hdyž wšě hódnoty su numeriske, Sum je standardna funkcija, hewak je Count standardna funkcija.

rowfields: Jednotliwy znamješkowy rjećazk z pólnymi mjenami, kotrež so jako linki pivotoweje tabele wužiwaja.

columnfields: Jednotliwy znamješkowy rjećazk abo matriks z pólnymi mjenami, kotrež so jako špalty pivotoweje tabele wužiwaja.

filterbutton: Postaja, hač filtrowe tłóčatko so nad pivotowej tabelu pokaza (standard = True).

rowtotals: Podawa, hač so separatna špalta za linkowe sumy pivotowej tabeli přidawa (standard = True).

columntotals: Podawa, hač so separatna linka za špaltowe sumy pivotowej tabeli přidawa (standard = True).

    Dim vData As Variant, oDoc As Object, ui As Object, sTable As String, sPivot As String
    Set ui = CreateScriptService("UI")
    Set oDoc = ui.CreateDocument("Calc")
    vData = Array(Array("Item", "State", "Team", "2002", "2003", "2004"), _
        Array("Books", "Michigan", "Jean", 14788, 30222, 23490), _
        Array("Candy", "Michigan", "Jean", 26388, 15641, 32849), _
        Array("Pens", "Michigan", "Jean", 16569, 32675, 25396), _
        Array("Books", "Michigan", "Volker", 21961, 21242, 29009), _
        Array("Candy", "Michigan", "Volker", 26142, 22407, 32841))
    sTable = oDoc.SetArray("A1", vData)
    sPivot = oDoc.CreatePivotTable("PT1", sTable, "H1", _
        Array("2002", "2003;count", "2004;average"), _ ' Tři datowe pola
        "Item", _ ' Jednotliwe linkowe polo
        Array("State", "Team"), False) ' Dwě špaltowej poli
  

    ui = CreateScriptService("UI")
    doc = ui.CreateDocument("Calc")
    vData = [["Item", "State", "Team", "2002", "2003", "2004"],
             ["Books", "Michigan", "Jean", 14788, 30222, 23490],
             ["Candy", "Michigan", "Jean", 26388, 15641, 32849],
             ["Pens", "Michigan", "Jean", 16569, 32675, 25396)],
             ["Books", "Michigan", "Volker", 21961, 21242, 29009],
             ["Candy", "Michigan", "Volker", 26142, 22407, 32841]]
    sTable = doc.SetArray("A1", vData)
    sPivot = doc.CreatePivotTable("PT1", sTable, "H1",
                                  ["2002", "2003;count", "2004;average"],
                                  "Item",
                                  ["State", "Team"], False)
  

Zo byšće wjace wo pivotowych tabelach w LibreOffice Calc zhonił, čitajće stronu pomocy Pivotowa tabela.

DAvg, DCount, DMax, DMin and DSum

Nałožuje funkcije Average, Count, Max, Min a Sum kóždy króć na wšě cele, kotrež numeriske hódnoty w datym wobłuku wobsahuja, nimo hódnoty z filtrowanych a schowanych linkow a schowanych špaltow, runje tak kaž za funkcije statusoweje lajsty.

svc.DAvg(range: str): float

svc.DCount(range: str): float

svc.DMax(range: str): float

svc.DMin(range: str): float

svc.DSum(range: str): float

range: The range to which the function will be applied, as a string.

The example below applies the Sum function to the range "A1:A1000" of the currently selected sheet:

      result = oDoc.DSum("~.A1:A1000")
  

    result = myDoc.DSum("~.A1:A1000")
  

Cells in the given range that contain text will be ignored by all of these functions. For example, the DCount method will not count cells with text, only numerical cells.

ExportRangeToFile

Exports the specified range as an image or PDF file.

This method returns True if the destination file was successfully saved.

Hidden rows or columns in the specified range are not exported to the destination file.

svc.ExportRangeToFile(range: str, filename: str, imagetype: str = "pdf", overwrite: bool = False): bool

range: A sheet name or a cell range to be exported, as a string.

filename: The name of the file to be saved. It must follow the SF_FileSystem.FileNaming notation.

imagetype: Identifies the destination file type. Possible values are "jpeg", "pdf" (default) and "png".

overwrite: When set to True, the destination file may be overwritten (Default = False).

    ' Exports the entire sheet as a PDF file
    oDoc.ExportRangeToFile("SheetX", "C:\Temp\image.pdf")
    ' Exports the range as a PNG file and overwrites the destination file if it exists
    oDoc.ExportRangeToFile("SheetX.A1:D10", "C:\Temp\image.png", "png", Overwrite := True)
  

    doc.ExportRangeToFile("SheetX", r"C:\Temp\image.pdf")
    doc.ExportRangeToFile("SheetX.A1:D10", r"C:\Temp\image.png", "png", overwrite = True)
  

Forms

Depending on the parameters provided this method will return:

• A zero-based Array (or a tuple in Python) with the names of all the forms contained in a given sheet (if the form argument is absent)

• A SFDocuments.Form service instance representing the form specified as argument.

svc.Forms(sheetname: str): str[0..*]

svc.Forms(sheetname: str, form: str = ''): svc

svc.Forms(sheetname: str, form: int): svc

sheetname: The name of the sheet, as a string, from which the form will be retrieved.

form: The name or index corresponding to a form stored in the specified sheet. If this argument is absent, the method will return a list with the names of all forms available in the sheet.

In the following examples, the first line gets the names of all forms stored in "Sheet1" and the second line retrieves the Form object of the form named "Form_A" which is stored in "Sheet1".

    Set FormNames = oDoc.Forms("Sheet1")
    Set FormA = oDoc.Forms("Sheet1", "Form_A")
  

    form_names = doc.Forms("Sheet1")
    form_A = doc.Forms("Sheet1", "Form_A")
  

GetColumnName

Converts a column number ranging between 1 and 1024 into its corresponding letter (column 'A', 'B', ..., 'AMJ'). If the given column number is outside the allowed range, a zero-length string is returned.

svc.GetColumnName(columnnumber: int): str

columnnumber: The column number as an integer value in the interval 1 ... 16384.

Displays a message box with the name of the third column, which by default is "C".

    MsgBox oDoc.GetColumnName(3)
  

    bas = CreateScriptService("Basic")
    bas.MsgBox(myDoc.GetColumnName(3))
  

The maximum number of columns allowed on a Calc sheet is 16384.

GetFormula

Get the formula(s) stored in the given range of cells as a single string, a 1D or a 2D array of strings.

The names of Calc functions used in the returned formulas are expressed in English. Visit the Wiki page List of Calc Functions for a complete list of Calc functions in English.

svc.GetFormula(range: str): any

range: The range where to get the formulas from, as a string.

The following example returns a 3 by 2 array with the formulas in the range "A1:B3" (3 rows by 2 columns):

    arrFormula = oDoc.GetFormula("~.A1:B3")
  

    arrFormula = myDoc.GetFormula("~.A1:B3")
  

GetValue

Get the value(s) stored in the given range of cells as a single value, a 1D array or a 2D array. All values are either doubles or strings.

svc.GetValue(range: str): any

range: The range where to get the values from, as a string.

      arrValues = oDoc.GetValue("~.B1:C100")
  

    arrValues = myDoc.GetValue("~.B1:C100")
  

If a cell contains a date, the number corresponding to that date will be returned. To convert numeric values to dates in Basic scripts, use the Basic CDate builtin function. In Python scripts, use the CDate function from the Basic service.

ImportFromCSVFile

Imports the contents of a CSV-formatted text file and places it on a given destination cell.

The destination area is cleared of all contents and formats before inserting the contents of the CSV file. The size of the modified area is fully determined by the contents of the input file.

The method returns a string representing the modified range of cells.

svc.ImportFromCSVFile(filename: str, destinationcell: str, [filteroptions: str]): str

filename: Identifies the file to open. It must follow the SF_FileSystem.FileNaming notation.

destinationcell: The destination cell to insert the imported data, as a string. If instead a range is given, only its top-left cell is considered.

filteroptions: The arguments for the CSV input filter. The default filter makes following assumptions:

• The input file encoding is UTF8.

• The field separator is a comma, a semi-colon or a Tab character.

• The string delimiter is the double quote (").

• All lines are included.

• Quoted strings are formatted as text.

• Special numbers are detected.

• All columns are presumed to be texts, except if recognized as valid numbers.

• The language is English/US, which implies that the decimal separator is "." and the thousands separator is ",".

    oDoc.ImportFromCSVFile("C:\Temp\myCSVFile.csv", "SheetY.C5")
  

    myDoc.ImportFromCSVFile(r"C:\Temp\myCSVFile.csv", "SheetY.C5")
  

To learn more about the CSV Filter Options, refer to the CSV Filter Options help page.

ImportFromDatabase

Imports the contents of a database table, query or resultset, i.e. the result of a SELECT SQL command, inserting it on a destination cell.

The destination area is cleared of all contents and formats before inserting the imported contents. The size of the modified area is fully determined by the contents in the table or query.

The method returns True when the import was successful.

svc.ImportFromDatabase(filename: str = "", registrationname: str = "", destinationcell: str = "", sqlcommand: str = "", directsql: bool): bool

filename: Identifies the file to open. It must follow the SF_FileSystem.FileNaming notation.

registrationname: The name to use to find the database in the databases register. This argument is ignored if a filename is provided.

destinationcell: The destination of the imported data, as a string. If a range is given, only its top-left cell is considered.

sqlcommand: A table or query name (without surrounding quotes or square brackets) or a SELECT SQL statement in which table and field names may be surrounded by square brackets or quotes to improve its readability.

directsql: When True, the SQL command is sent to the database engine without pre-analysis. Default is False. The argument is ignored for tables. For queries, the applied option is the one set when the query was defined.

    oDoc.ImportFromDatabase("C:\Temp\myDbFile.odb", , "SheetY.C5", "SELECT * FROM [Employees] ORDER BY [LastName]")
  

    myDoc.ImportFromDatabase(r"C:\Temp\myDbFile.odb", , "SheetY.C5", "SELECT * FROM [Employees] ORDER BY [LastName]")
  

    oDoc.ImportStylesFromFile("C:\User\Documents\myFile.ods", "ParagraphStyles", True)
  

    doc.ImportStylesFromFile('C:\User\Documents\myFile.ods', ("ParagraphStyles",), False)
  

InsertSheet

Inserts a new empty sheet before an existing sheet or at the end of the list of sheets.

svc.InsertSheet(sheetname: str, [beforesheet: any]): bool

sheetname: The name of the new sheet.

beforesheet: The name (string) or index (numeric, starting from 1) of the sheet before which to insert the new sheet. This argument is optional and the default behavior is to insert the sheet at the last position.

The following example inserts a new empty sheet named "SheetX" and places it before "SheetY":

    oDoc.InsertSheet("SheetX", "SheetY")
  

    myDoc.InsertSheet("SheetX", "SheetY")
  

MoveRange

Moves a specified source range to a destination range of cells. The method returns a string representing the modified range of cells. The dimension of the modified area is fully determined by the size of the source area.

svc.MoveRange(source: str, destination: str): str

source: The source range of cells, as a string.

destination: The destination cell, as a string. If a range is given, its top-left cell is considered as the destination.

    oDoc.MoveRange("SheetX.A1:F10", "SheetY.C5")
  

    myDoc.MoveRange("SheetX.A1:F10", "SheetY.C5")
  

MoveSheet

Moves an existing sheet and places it before a specified sheet or at the end of the list of sheets.

svc.MoveSheet(sheetname: str, [beforesheet: any]): bool

sheetname: The name of the sheet to move. The sheet must exist or an exception is raised.

beforesheet: The name (string) or index (numeric, starting from 1) of the sheet before which the original sheet will be placed. This argument is optional and the default behavior is to move the sheet to the last position.

The example below moves the existing sheet "SheetX" and places it before "SheetY":

    oDoc.MoveSheet("SheetX", "SheetY")
  

    myDoc.MoveSheet("SheetX", "SheetY")
  

Offset

Returns a new range (as a string) offset by a certain number of rows and columns from a given range.

This method has the same behavior as the homonymous Calc's Offset function.

svc.Offset(reference: str, rows: int = 0, columns: int = 0, [height: int], [width: int]): str

reference: The range, as a string, that the method will use as reference to perform the offset operation.

rows: The number of rows by which the initial range is offset upwards (negative value) or downwards (positive value). Use 0 (default) to stay in the same row.

columns: The number of columns by which the initial range is offset to the left (negative value) or to the right (positive value). Use 0 (default) to stay in the same column.

height: The vertical height for an area that starts at the new range position. Omit this argument when no vertical resizing is needed.

width: The horizontal width for an area that starts at the new range position. Omit this argument when no horizontal resizing is needed.

Arguments rows and columns must not lead to zero or negative start row or column.

Arguments height and width must not lead to zero or negative count of rows or columns.

    oDoc.Offset("A1", 2, 2)
    'SheetX.$C$3 (A1 moved by two rows and two columns down)
    oDoc.Offset("A1", 2, 2, 5, 6)
    'SheetX.$C$3:$H$7 (A1 offset by two rows and columns with width of 5 rows and 6 columns)
  

    myDoc.Offset("A1", 2, 2)
    myDoc.Offset("A1", 2, 2, 5, 6)
  

OpenRangeSelector

Opens a non-modal dialog that can be used to select a range in the document and returns a string containing the selected range.

This method opens the same dialog that is used by LibreOffice when the Shrink button is pressed. For example, the Tools - Goal Seek dialog has a Shrink button to the right of the Formula cell field.

This method does not change the current selection.

svc.OpenRangeSelector(opt title: str, opt selection: str, singlecell: bool = False, closeafterselect: bool = True): str

title: The title of the dialog, as a string.

selection: An optional range that is initially selected when the dialog is displayed.

singlecell: When True (default) only single-cell selection is allowed. When False range selection is allowed.

closeafterselect: When True (default) the dialog is closed immediately after the selection is made. When False the user can change the selection as many times as needed and then manually close the dialog.

    Dim sRange as String
    sRange = oDoc.OpenRangeSelector(Title := "Select a range")
  

    sRange = myDoc.OpenRangeSelector(title = "Select a range")
  

Printf

Returns the input string after substituting its token characters by their values in a given range.

This method does not change the current selection.

This method can be used to quickly extract specific parts of a range name, such as the sheet name or first cell column and row, and use them to compose a new range address.

svc.Printf(inputstr: str, range: str, tokencharacter: str = "%"): str

inputstr: The string containing the tokens that will be replaced by the corresponding values in range.

range: A RangeName from which values will be extracted. If it contains a sheet name, the sheet must exist.

tokencharacter: Character used to identify tokens. By default "%" is the token character. The following tokens are accepted:

• %S - The sheet name containing the range, including single quotes when necessary.

• %R1 - The row number of the top left cell of the range.

• %C1 - The column letter of the top left cell of the range.

• %R2 - The row number of the bottom right cell of the range.

• %C2 - The column letter of the bottom right cell of the range.

The example below extracts each element of the RangeName defined in sRange and uses them to compose a message.

    Dim sRange as String, sInputStr as String
    sRange = "Sheet1.A1:E10"
    sInputStr = "Sheet name: %S" & Chr(10) & _
                "First row: %R1" & Chr(10) & _
                "First column %C1" & Chr(10) & _
                "Last row %R2" & Chr(10) & _
                "Last column %C2"
    MsgBox oDoc.Printf(sInputStr, sRange)
  

The Printf method can be combined with SetFormula to create formulas over multiple cells. For instance, consider a table with numeric values in the range "A1:E10" from which formulas are to be created to sum the values in each row and place the results in the range "F1:F10":

    Dim sFormula as String, sRange as String
    sRange = "A1:E10"
    ' Note the use of the "$" character
    sFormula = "=SUM($%C1%R1:$%C2%R1)"
    oDoc.SetFormula("F1:F10", oDoc.Printf(sFormula, sRange))
  

    sRange = "Sheet1.A1:E10"
    sInputStr = "Sheet name: %S\n" \
                "First row: %R1\n" \
                "First column %C1\n" \
                "Last row %R2\n" \
                "Last column %C2"
    bas = CreateScriptService("Basic")
    bas.MsgBox(myDoc.Printf(sInputStr, sRange))
  

    sRange = "A1:E10
    sFormula = "=SUM($%C1%R1:$%C2%R1)"
    myDoc.SetFormula("F1:F10", myDoc.Printf(sFormula, sRange))
  

PrintOut

This method sends the contents of the given sheet to the default printer or to the printer defined by the SetPrinter method of the Document service.

Returns True if the sheet was successfully printed.

svc.PrintOut(opt sheetname: str, pages: str = "", copies: num = 1): bool

sheetname: The sheet to print, default is the active sheet.

pages: The pages to print as a string, like in the user interface. Example: "1-4;10;15-18". Default is all pages.

copies: The number of copies. Default is 1.

    If oDoc.PrintOut("SheetX", "1-4;10;15-18", Copies := 2) Then
        ' ...
    End If
  

    if doc.PrintOut('SheetX', copies=3, pages='45-88'):
        # ...
  

RemoveDuplicates

Removes duplicate rows from a specified range. The comparison to determine if a given row is a duplicate is done based on a subset of columns in the range.

This method returns a string containing the resulting range.

The removal of duplicate rows is done starting at the first row in the range moving downwards, meaning that if two or more rows are duplicates then only the first one is kept.

svc.RemoveDuplicates(range: str, opt columns: int[0..*], header: bool = False, casesensitive: bool = False, mode: str = "COMPACT"): str

range: The range from which duplicates will be removed, as a string.

columns: An array containing column numbers indicating which columns will be considered to determine if a row is a duplicate or not. If this argument is left blank, then only the first column is used. Items in this array must be in the interval between 1 and the range width.

header: Specifies whether the first row is a header row (Default = False).

casesensitive: Specifies whether string comparisons are case-sensitive (Default = False).

mode: Specifies what to do with duplicate rows. If mode = "CLEAR" then duplicates are simply removed from the sheet leaving the cells blank. If mode = "COMPACT" then duplicates are removed and empty rows are compacted up (Default = "COMPACT").

    ' Removes duplicate rows where values in column A are duplicate
    ' Note that all optional arguments use their default value
    oDoc.RemoveDuplicates("A1:B10")
    ' Removes duplicate rows considering that the first row contains headers
    ' Columns A and B are used to determine if a row is a duplicate
    ' Cells containing duplicate values are left blank
    oDoc.RemoveDuplicates("A1:D10", columns := Array(1, 2), header := True, mode := "CLEAR")
  

    myDoc.RemoveDuplicates("A1:B10")
    myDoc.RemoveDuplicates("A1:D10", columns = (1, 2), header = True, mode = "CLEAR")
  

RemoveSheet

Removes an existing sheet from the document.

svc.RemoveSheet(sheetname: str): bool

sheetname: The name of the sheet to remove.

    oDoc.RemoveSheet("SheetY")
  

    myDoc.RemoveSheet("SheetY")
  

RenameSheet

Renames the given sheet and returns True if successful.

svc.RenameSheet(sheetname: str, newname: str): bool

sheetname: The name of the sheet to rename.

newname: the new name of the sheet. It must not exist yet.

This example renames the active sheet to "SheetY":

    oDoc.RenameSheet("~", "SheetY")
  

    mydoc.RenameSheet("~", "SheetY")
  

SetArray

Stores the given value starting from a specified target cell. The updated area expands itself from the target cell or from the top-left corner of the given range to accommodate the size of the input value argument. Vectors are always expanded vertically.

The method returns a string representing the modified area as a range of cells.

svc.SetArray(targetcell: str, value: any): str

targetcell: The cell or a range as a string from where to start to store the given value.

value: A scalar, a vector or an array (in Python, one or two-dimensional lists and tuples) with the new values to be stored from the target cell or from the top-left corner of the range if targetcell is a range. The new values must be strings, numeric values or dates. Other types will cause the corresponding cells to be emptied.

The following example uses the builtin DimArray function to create an array and then store it in cell "A1":

    Dim arrData as Variant
    arrData = DimArray(2, 1)
    arrData(0, 0) = 1 : arrData(1, 0) = 2 : arrData(2, 0) = 3
    arrData(0, 1) = "One" : arrData(1, 1) = "Two" : arrData(2, 1) = "Three"
    oDoc.SetArray("Sheet1.A1", arrData)
  

This example uses the RangeInit method of the ScriptForge Array service to create an array with values that are then stored from cell "A1" and downwards.

    'Fill 1st column with values from 1 to 1000
    oDoc.SetArray("Sheet1.A1", SF_Array.RangeInit(1, 1000))
  

    arrData = ((1, "One"), (2, "Two"), (3, "Three"))
    myDoc.SetArray("Sheet1.A1", arrData)
  

    myDoc.SetArray("Sheet1.A1", tuple(i + 1 for i in range(1000)))
  

To dump the full contents of an array in a sheet, use SetArray. To dump the contents of an array only within the boundaries of the targeted range of cells, use SetValue.

SetCellStyle

Applies the specified cell style to the given target range. The full range is updated and the remainder of the sheet is left untouched. If the cell style does not exist, an error is raised.

The method returns a string representing the modified area as a range of cells.

svc.SetCellStyle(targetrange: str, style: str, opt filterformula: str, opt filterscope: str): str

targetrange: The range to which the style will be applied, as a string.

style: The name of the cell style to apply.

    oDoc.SetCellStyle("A1:J1", "Heading 1")
    oDoc.SetCellStyle("A2:J100", "Neutral")
  

    myDoc.SetCellStyle("A1:J1", "Heading 1")
    myDoc.SetCellStyle("A2:J100", "Neutral")
  

Refer to the ClearAll method documentation for examples on how to use the arguments filterformula and filterscope.

SetFormula

Inserts the given (array of) formula(s) in the specified range. The size of the modified area is equal to the size of the range.

The method returns a string representing the modified area as a range of cells.

svc.SetFormula(targetrange: str, formula: any): str

targetrange: The range to insert the formulas, as a string.

formula: A string, a vector or an array of strings with the new formulas for each cell in the target range.

The full range is updated and the remainder of the sheet is left unchanged.

If the given formula is a string, the unique formula is pasted along the whole range with adjustment of the relative references.

If the size of formula is smaller than the size of targetrange, then the remaining cells are emptied.

If the size of formula is larger than the size of targetrange, then the formulas are only partially copied until it fills the size of targetrange.

Vectors are always expanded vertically, except if targetrange has a height of exactly 1 row.

Calc functions used in the formula argument must be expressed using their English names. Visit the Wiki page List of Calc Functions for a complete list of Calc functions in English.

    oDoc.SetFormula("A1", "=A2")
    'Horizontal vector, partially empty
    oDoc.SetFormula("A1:F1", Array("=A2", "=B2", "=C2+10"))
    'D2 contains the formula "=H2"
    oDoc.SetFormula("A1:D2", "=E1")
  

    myDoc.SetFormula("A1", "=A2")
    myDoc.SetFormula("A1:F1", ("=A2", "=B2", "=C2+10"))
    myDoc.SetFormula("A1:D2", "=E1")
  

SetValue

Stores the given value in the specified range. The size of the modified area is equal to the size of the target range.

The method returns a string representing the modified area as a range of cells.

svc.SetValue(targetrange: str, value: any): str

targetrange: The range where to store the given value, as a string.

value: A scalar, a vector or an array with the new values for each cell of the range. The new values must be strings, numeric values or dates. Other types will cause the corresponding cells to be emptied.

The full range is updated and the remainder of the sheet is left unchanged. If the size of value is smaller than the size of targetrange, then the remaining cells will be emptied.

If the size of value is larger than the size of targetrange, then value is only partially copied until it fills the size of targetrange.

Vectors are expanded vertically, except if targetrange has a height of exactly 1 row.

    oDoc.SetValue("A1", 2)
    'Below the Value array is smaller than the TargetRange (remaining cells are emptied)
    oDoc.SetValue("A1:F1", Array(1, 2, 3))
    'Below the Value and TargetRange have the same size
    oDoc.SetValue("A1:D2", SF_Array.AppendRow(Array(1, 2, 3, 4), Array(5, 6, 7, 8)))
  

If you want to fill a single row with values, you can use the Offset function. In the example below, consider that arrData is a one-dimensional array:

    Dim firstCell As String : firstCell = "A1"
    Dim lenArray As Integer : lenArray = UBound(arrData) - LBound(arrData) + 1
    Dim newRange As String : newRange = oDoc.Offset(firstCell, width = lenArray)
    oDoc.SetValue(newRange, arrData)
  

    myDoc.SetValue("A1", 2)
    myDoc.SetValue("A1:F1", (1, 2, 3))
    myDoc.SetValue("A1:D2", ((1, 2, 3, 4), (5, 6, 7, 8)))
  

    firstCell = "A1"
    newRange = doc.Offset(firstCell, width = len(arrData))
    doc.SetValue(newRange, arrData)
  

ShiftDown

Moves a given range of cells downwards by inserting empty rows. The current selection is not affected.

Depending on the value of the wholerow argument the inserted rows can either span the width of the specified range or span all columns in the row.

This method returns a string representing the new location of the initial range.

If the shifted range exceeds the sheet edges, then nothing happens.

svc.ShiftDown(range: str, wholerow: bool = False, opt rows: int): str

range: The range above which rows will be inserted, as a string.

wholerow: If set to False (default), then the width of the inserted rows will be the same as the width of the specified range. Otherwise, the inserted row will span all columns in the sheet.

rows: The number of rows to be inserted. The default value is the height of the original range. The number of rows must be a positive number.

    ' Moves the range "A3:D3" down by one row; affects only columns A to D
    oDoc.ShiftDown("A3:D3")
    ' The inserted row spans all columns in the sheet
    oDoc.ShiftDown("A3:D3", WholeRow := True)
    ' Moves the range "A3:D3" down by five rows
    oDoc.ShiftDown("A3:D3", Rows := 5)
    ' Moves the range "A3:D10" down by two rows and shows the new location of the original range
    Dim sNewRange as String
    sNewRange = oDoc.ShiftDown("A3:D10", Rows := 2)
    MsgBox sNewRange   ' $Sheet1.$A$5:$D$12
  

    myDoc.ShiftDown("A3:D3")
    myDoc.ShiftDown("A3:D3", wholerow = True)
    myDoc.ShiftDown("A3:D3", rows = 5)
    sNewRange = myDoc.ShiftDown("A3:D10", rows = 2)
    bas = CreateScriptService("Basic")
    bas.MsgBox(sNewRange)
  

ShiftLeft

Deletes the leftmost columns of a given range and moves to the left all cells to the right of the affected range. The current selection is not affected.

Depending on the value of the wholecolumn argument the deleted columns can either span the height of the specified range or span all rows in the column.

This method returns a string representing the location of the remaining portion of the initial range. If all cells in the original range have been deleted, then an empty string is returned.

svc.ShiftLeft(range: str, wholecolumn: bool = False, opt columns: int): str

range: The range from which cells will be deleted, as a string.

wholecolumn: If set to False (default), then the height of the deleted columns will be the same as the height of the specified range. Otherwise, the deleted columns will span all rows in the sheet.

columns: The number of columns to be deleted from the specified range. The default value is the width of the original range, which is also the maximum value of this argument.

    ' Deletes the range "B3:B6"; moves left all cells to the right
    oDoc.ShiftLeft("B3:B6")
    ' Deletes the first column in the range "A3:D6"
    oDoc.ShiftLeft("A3:D6", Columns := 1)
    ' The deleted columns (A to D) spans all rows in the sheet
    oDoc.ShiftLeft("A3:D6", WholeColumn := True)
  

    myDoc.ShiftLeft("B3:B6")
    myDoc.ShiftLeft("A3:D6", Columns = 1)
    myDoc.ShiftLeft("A3:D6", WholeColumn = True)
  

ShiftUp

Deletes the topmost rows of a given range and moves upwards all cells below the affected range. The current selection is not affected.

Depending on the value of the wholerow argument the deleted rows can either span the width of the specified range or span all columns in the row.

This method returns a string representing the location of the remaining portion of the initial range. If all cells in the original range have been deleted, then an empty string is returned.

svc.ShiftUp(range: str, wholerow: bool = False, opt rows: int): str

range: The range from which cells will be deleted, as a string.

wholerow: If set to False (default), then the width of the deleted rows will be the same as the width of the specified range. Otherwise, the deleted row will span all columns in the sheet.

rows: The number of rows to be deleted from the specified range. The default value is the height of the original range, which is also the maximum value of this argument.

    ' Deletes the range "A3:D3"; moves all cells below it by one row up
    oDoc.ShiftUp("A3:D3")
    ' Deletes the first row in the range "A3:D6"
    oDoc.ShiftUp("A3:D6", Rows := 1)
    ' The deleted rows spans all columns in the sheet
    oDoc.ShiftUp("A3:D6", WholeRow := True)
  

    myDoc.ShiftUp("A3:D3")
    myDoc.ShiftUp("A3:D6", rows = 1)
    myDoc.ShiftUp("A3:D6", wholerow = True)
  

ShiftRight

Moves a given range of cells to the right by inserting empty columns. The current selection is not affected.

Depending on the value of the wholecolumn argument the inserted columns can either span the height of the specified range or span all rows in the column.

This method returns a string representing the new location of the initial range.

If the shifted range exceeds the sheet edges, then nothing happens.

svc.ShiftRight(range: str, wholecolumn: bool = False, opt columns: int): str

range: The range which will have empty columns inserted to its left, as a string.

wholecolumn: If set to False (default), then the height of the inserted columns will be the same as the height of the specified range. Otherwise, the inserted columns will span all rows in the sheet.

columns: The number of columns to be inserted. The default value is the width of the original range.

    ' Moves the range "A3:A6" right by one column; affects only rows 3 to 6
    oDoc.ShiftRight("A3:A6")
    ' Moves the range "A3:A6" right by five columns
    oDoc.ShiftRight("A3:A6", Columns := 5)
    ' The inserted column spans all rows in the sheet
    oDoc.ShiftRight("A3:A6", WholeColumn := True)
  

    myDoc.ShiftRight("A3:A6")
    myDoc.ShiftRight("A3:A6", columns = 5)
    myDoc.ShiftRight("A3:A6", wholecolumn = True)
  

SortRange

Sort the given range on any number of columns/rows. The sorting order may vary by column/row. If the number of sort keys is > 3 then the range is sorted several times, by groups of 3 keys, starting from the last key. It returns a string representing the modified range of cells. The size of the modified area is fully determined by the size of the source area.

svc.SortRange(range: str, sortkeys: any, sortorder: any = "ASC", destinationcell: str = "", containsheader: bool = False, casesensitive: bool = False, sortcolumns: bool = False): str

range: The range to be sorted, as a string.

sortkeys: A scalar (if 1 column/row) or an array of column/row numbers starting from 1.

sortorder: A scalar or an array of strings containing the values "ASC" (ascending), "DESC" (descending). Each item is paired with the corresponding item in sortkeys. If the sortorder array is shorter than sortkeys, the remaining keys are sorted in ascending order.

destinationcell: The destination cell of the sorted range of cells, as a string. If a range is given, only its top-left cell is considered. By default the source Range is overwritten.

containsheader: When True, the first row/column is not sorted.

casesensitive: Only for string comparisons. Default = False

sortcolumns: When True, the columns are sorted from left to right. Default = False : rows are sorted from top to bottom.

    'Sort range based on columns A (ascending) and C (descending)
    oDoc.SortRange("A2:J200", Array(1, 3), Array("ASC", "DESC"), CaseSensitive := True)
  

    myDoc.SortRange("A2:J200", (1, 3), ("ASC", "DESC"), casesensitive = True)
  

Wšě rutiny Basic ScriptForge abo identifikatory, kotrež so z podsmužku „_“ započinaja, su za interne wužiwanje wuměnjene. Njejsu za to myslene, so w makrach Basic abo skriptach Python wužiwać.