1#************************************************************** 2# 3# Licensed to the Apache Software Foundation (ASF) under one 4# or more contributor license agreements. See the NOTICE file 5# distributed with this work for additional information 6# regarding copyright ownership. The ASF licenses this file 7# to you under the Apache License, Version 2.0 (the 8# "License"); you may not use this file except in compliance 9# with the License. You may obtain a copy of the License at 10# 11# http://www.apache.org/licenses/LICENSE-2.0 12# 13# Unless required by applicable law or agreed to in writing, 14# software distributed under the License is distributed on an 15# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16# KIND, either express or implied. See the License for the 17# specific language governing permissions and limitations 18# under the License. 19# 20# usage: runpy.sh fixtures/calc/make.py <port> <outdir> 21# Calc documents for the Task 12d gap list: validations of every type and operator, and the other table: constructs. 22# Expectations for fixtures/calc/verify.py go to <outdir>/../work/ (the checkers treat every file of <outdir> as a package). 23import sys, os, json, uno 24from unohelp import connect, desktop, pv 25 26port, out = int(sys.argv[1]), os.path.abspath(sys.argv[2]) 27os.makedirs(out, exist_ok=True) 28work = os.path.join(os.path.dirname(out), "work") 29os.makedirs(work, exist_ok=True) 30CTX = connect(port) 31SM = CTX.ServiceManager 32desk = desktop(CTX) 33failed = [] 34expect = {} 35 36 37def E(t, n): 38 return uno.Enum("com.sun.star.sheet." + t, n) 39 40 41def new(): 42 return desk.loadComponentFromURL("private:factory/scalc", "_blank", 0, (pv("Hidden", True),)) 43 44 45def save(doc, name, filt="calc8"): 46 try: 47 doc.storeToURL(uno.systemPathToFileUrl(os.path.join(out, name)), (pv("FilterName", filt),)) 48 print("saved", name, filt) 49 return True 50 except Exception as e: 51 failed.append(name) 52 print("FAIL", name, filt, repr(e)[:200]) 53 return False 54 55 56def step(label, fn, *a, **kw): 57 if os.environ.get("CALC_TRACE"): 58 print("step", label, flush=True) 59 try: 60 return fn(*a, **kw) 61 except Exception as e: 62 failed.append(label) 63 print("FAIL", label, type(e).__name__, str(e)[:300]) 64 65 66def cellrange(sh, addr): 67 return sh.getCellRangeByName(addr) 68 69 70NUMTYPES = ("WHOLE", "DECIMAL", "DATE", "TIME", "TEXT_LEN") 71OPS = ("NONE", "EQUAL", "NOT_EQUAL", "GREATER", "GREATER_EQUAL", "LESS", "LESS_EQUAL", "BETWEEN", "NOT_BETWEEN", "FORMULA") 72SAMPLE = {"WHOLE": ("5", "10"), "DECIMAL": ("1.5", "9.25"), "DATE": ("DATE(2020;1;31)", "43831"), 73 "TIME": ("TIME(8;30;0)", "0.75"), "TEXT_LEN": ("3", "12"), "LIST": ('"a";"b";"c"', ""), "CUSTOM": ("$Sheet1.$B$1>0", "")} 74 75 76def setval(cell, typ, op, f1, f2, alert="STOP", show_list=1, ignore=True, inp=("", ""), err=("", ""), show_inp=False, show_err=True): 77 v = cell.Validation 78 v.Type = E("ValidationType", typ) 79 v.setOperator(E("ConditionOperator", op)) 80 v.setFormula1(f1) 81 v.setFormula2(f2) 82 v.IgnoreBlankCells = ignore 83 v.ShowList = show_list 84 v.ErrorAlertStyle = E("ValidationAlertStyle", alert) 85 v.InputTitle, v.InputMessage = inp 86 v.ErrorTitle, v.ErrorMessage = err 87 v.ShowInputMessage = show_inp 88 v.ShowErrorMessage = show_err 89 cell.Validation = v 90 r = cell.Validation 91 return {"type": r.Type.value, "op": r.getOperator().value, "f1": r.getFormula1(), "f2": r.getFormula2(), 92 "alert": r.ErrorAlertStyle.value, "list": r.ShowList, "ignore": r.IgnoreBlankCells, 93 "inp": [r.InputTitle, r.InputMessage], "err": [r.ErrorTitle, r.ErrorMessage], 94 "showinp": r.ShowInputMessage, "showerr": r.ShowErrorMessage} 95 96 97def validation_doc(name, cases): 98 doc = new() 99 sh = doc.Sheets.getByIndex(0) 100 for i in range(1, 6): 101 sh.getCellByPosition(1, i - 1).Value = i 102 rows = [] 103 for i, (label, args, kw) in enumerate(cases): 104 c = sh.getCellByPosition(0, i) 105 try: 106 exp = setval(c, *args, **kw) 107 except Exception as e: 108 failed.append(name + ":" + label) 109 print("FAIL", name, label, repr(e)[:200]) 110 continue 111 c.Value = 1 112 rows.append({"cell": "A%d" % (i + 1), "label": label, "set": [a for a in args], "kw": {k: (list(v) if isinstance(v, tuple) else v) for k, v in kw.items()}, "readback": exp}) 113 if save(doc, name): 114 expect[name] = rows 115 doc.close(True) 116 117 118def validations(): 119 cases = [] 120 for t in NUMTYPES: 121 for op in OPS: 122 f1, f2 = SAMPLE[t] 123 cases.append(("%s/%s" % (t, op), (t, op, f1, f2 if op in ("BETWEEN", "NOT_BETWEEN") else ""), {})) 124 validation_doc("validation-types.ods", cases) 125 cases = [] 126 for op in OPS: 127 cases.append(("CUSTOM/%s" % op, ("CUSTOM", op, "$Sheet1.$B$1>0", ""), {})) 128 cases.append(("LIST/%s" % op, ("LIST", op, '"a";"b";"c"', ""), {})) 129 cases.append(("ANY/%s" % op, ("ANY", op, "", ""), {"err": ("t", "m")})) 130 for t, f1, f2 in (("LIST", "$Sheet1.$B$1:$B$5", ""), ("LIST", '"one";"two"', ""), ("CUSTOM", "ISNUMBER(A1)", ""), 131 ("CUSTOM", "AND(A1>0;A1<10)", ""), ("CUSTOM", 'LEN(A1)=3', ""), ("CUSTOM", '"abc"', "")): 132 cases.append(("%s/f1=%s" % (t, f1), (t, "FORMULA" if t == "CUSTOM" else "EQUAL", f1, f2), {})) 133 validation_doc("validation-custom-list.ods", cases) 134 cases = [] 135 for alert in ("STOP", "WARNING", "INFO", "MACRO"): 136 for show_err in (True, False): 137 for ign in (True, False): 138 cases.append(("WHOLE/%s/err=%s/ign=%s" % (alert, show_err, ign), 139 ("WHOLE", "BETWEEN", "1", "10"), 140 {"alert": alert, "show_err": show_err, "ignore": ign, 141 "err": ("Macro.Lib.Mod.Fn" if alert == "MACRO" else "Err title", "Err line 1\nErr line 2"), 142 "inp": ("In title", "In text"), "show_inp": ign})) 143 for sl in (0, 1, 2): 144 for ign in (True, False): 145 cases.append(("LIST/showlist=%d/ign=%s" % (sl, ign), ("LIST", "EQUAL", '"x";"y"', ""), {"show_list": sl, "ignore": ign})) 146 for sl in (0, 1, 2): 147 cases.append(("LIST/distinct/showlist=%d" % sl, ("LIST", "EQUAL", '"x";"y"' + ';"z"' * sl, ""), {"show_list": sl})) 148 cases.append(("DATE/msg-only-input", ("DATE", "GREATER", "1", ""), {"inp": ("", "only text"), "show_inp": True, "show_err": False})) 149 cases.append(("ANY/input-only", ("ANY", "NONE", "", ""), {"inp": ("Hint", "Type a number"), "show_inp": True, "show_err": False})) 150 cases.append(("ANY/error-only", ("ANY", "NONE", "", ""), {"err": ("Bad", "No"), "show_inp": False, "show_err": True})) 151 cases.append(("ANY/error-title-only", ("ANY", "NONE", "", ""), {"err": ("Only a title", ""), "alert": "WARNING"})) 152 cases.append(("TEXT_LEN/utf8", ("TEXT_LEN", "LESS", "5", ""), {"err": ("Fehler äöü", "Zu lang – <&> \"q\""), "alert": "INFO"})) 153 validation_doc("validation-alerts.ods", cases) 154 155 156def validation_edges(): 157 cases = [] 158 for t in ("WHOLE", "DECIMAL", "TEXT_LEN", "LIST", "CUSTOM", "DATE", "TIME"): 159 for op in ("EQUAL", "BETWEEN", "NOT_BETWEEN"): 160 cases.append(("%s/%s/f1-empty" % (t, op), (t, op, "", "7"), {})) 161 cases.append(("%s/%s/f2-empty" % (t, op), (t, op, "3", ""), {})) 162 cases.append(("%s/%s/both-empty" % (t, op), (t, op, "", ""), {})) 163 validation_doc("validation-edges.ods", cases) 164 165 166def struct(name, *a): 167 return uno.createUnoStruct(name, *a) 168 169 170def rng_addr(sheet, c1, r1, c2, r2): 171 return struct("com.sun.star.table.CellRangeAddress", sheet, c1, r1, c2, r2) 172 173 174def cell_addr(sheet, c, r): 175 return struct("com.sun.star.table.CellAddress", sheet, c, r) 176 177 178def fill_table(sh, rows=12): 179 heads = ("Region", "Product", "Qty", "Price", "Day") 180 for c, h in enumerate(heads): 181 sh.getCellByPosition(c, 0).String = h 182 regions = ("North", "South", "East", "West") 183 prods = ("Apple", "Pear", "Plum") 184 for r in range(1, rows): 185 sh.getCellByPosition(0, r).String = regions[r % 4] 186 sh.getCellByPosition(1, r).String = prods[r % 3] 187 sh.getCellByPosition(2, r).Value = r * 3 % 17 188 sh.getCellByPosition(3, r).Value = 1.25 * r 189 sh.getCellByPosition(4, r).Value = 43831 + r 190 return sh 191 192 193def pvs(**kw): 194 return tuple(pv(k, v) for k, v in kw.items()) 195 196 197def sheets_doc(n): 198 doc = new() 199 for i in range(1, n): 200 doc.Sheets.insertNewByName("Sh%d" % (i + 1), i) 201 return doc 202 203 204def filter_field(field, op, value, numeric, conn="AND"): 205 f = struct("com.sun.star.sheet.TableFilterField2") 206 f.Field = field 207 f.IsNumeric = numeric 208 f.NumericValue = value if numeric else 0.0 209 f.StringValue = "" if numeric else value 210 f.Operator = uno.getConstantByName("com.sun.star.sheet.FilterOperator2." + op) 211 f.Connection = E("FilterConnection", conn) 212 return f 213 214 215def add_db(doc, i, name, rows=12): 216 fill_table(doc.Sheets.getByIndex(i), rows) 217 doc.DatabaseRanges.addNewByName(name, rng_addr(i, 0, 0, 4, rows - 1)) 218 return doc.DatabaseRanges.getByName(name) 219 220 221def dbranges(): 222 ops = ["EQUAL", "NOT_EQUAL", "GREATER", "GREATER_EQUAL", "LESS", "LESS_EQUAL", "BOTTOM_PERCENT", "BOTTOM_VALUES", "EMPTY", 223 "NOT_EMPTY", "TOP_PERCENT", "TOP_VALUES", "CONTAINS", "DOES_NOT_CONTAIN", "BEGINS_WITH", "DOES_NOT_BEGIN_WITH", 224 "ENDS_WITH", "DOES_NOT_END_WITH"] 225 patterns = {"single": [None], "and2": [None, "AND"], "or2": [None, "OR"], "and3": [None, "AND", "AND"], 226 "or3": [None, "OR", "OR"], "and-or": [None, "AND", "OR"], "or-and": [None, "OR", "AND"], 227 "and-or-and": [None, "AND", "OR", "AND"], "or-and-and": [None, "OR", "AND", "AND"], 228 "and-and-or": [None, "AND", "AND", "OR"], "or-or-and": [None, "OR", "OR", "AND"]} 229 doc = sheets_doc(len(patterns)) 230 231 def one(i, name, conns): 232 db = add_db(doc, i, name) 233 fd = db.getFilterDescriptor() 234 fd.setFilterFields2(tuple(filter_field(k % 5, "EQUAL", 5.5 + k if k % 5 in (2, 3) else "North", k % 5 in (2, 3), cn or "AND") 235 for k, cn in enumerate(conns))) 236 fd.IsCaseSensitive = i % 2 == 0 237 fd.SkipDuplicates = i % 3 == 0 238 fd.UseRegularExpressions = i % 4 == 0 239 if i % 5 == 0: 240 fd.CopyOutputData = True 241 fd.OutputPosition = cell_addr(i, 7, 14) 242 243 for i, (name, conns) in enumerate(patterns.items()): 244 step("filter-" + name, one, i, "f_" + name, conns) 245 save(doc, "filter-connections.ods") 246 doc.close(True) 247 248 doc = sheets_doc(len(ops) * 2) 249 for oi, o in enumerate(ops): 250 for rx in (False, True): 251 def mk(o=o, rx=rx, i=oi * 2 + rx): 252 db = add_db(doc, i, "f_%s%s" % (o.lower(), "_rx" if rx else "")) 253 num = o in ("BOTTOM_PERCENT", "BOTTOM_VALUES", "TOP_PERCENT", "TOP_VALUES", "GREATER", "GREATER_EQUAL", "LESS", "LESS_EQUAL") 254 fd = db.getFilterDescriptor() 255 fd.setFilterFields2((filter_field(2 if num else 0, o, 3.0 if num else "o", num),)) 256 fd.UseRegularExpressions = rx 257 step("op-" + o, mk) 258 save(doc, "filter-operators.ods") 259 doc.close(True) 260 261 doc = sheets_doc(8) 262 sort_fields = lambda *fs: uno.Any("[]com.sun.star.table.TableSortField", tuple( 263 struct("com.sun.star.table.TableSortField", f, asc, cs, uno.Enum("com.sun.star.table.TableSortFieldType", ft), struct("com.sun.star.lang.Locale", "en", "US", ""), "") 264 for f, asc, cs, ft in fs)) 265 266 def sorts(): 267 for i, (cs, bind, copy, uls) in enumerate(((False, True, False, False), (True, False, True, False), (False, True, False, True), (True, True, True, False))): 268 db = add_db(doc, i, "sort%d" % i) 269 sd = db.getSortDescriptor() 270 sd2 = [] 271 for p in sd: 272 v = p.Value 273 if p.Name == "SortFields": 274 v = sort_fields((0, True, cs, "ALPHANUMERIC"), (2, False, cs, "NUMERIC"), (3, True, cs, "AUTOMATIC")) 275 elif p.Name == "BindFormatsToContent": 276 v = bind 277 elif p.Name == "CopyOutputData": 278 v = copy 279 elif p.Name == "OutputPosition": 280 v = cell_addr(i, 7, 14) 281 elif p.Name == "IsUserListEnabled": 282 v = uls 283 elif p.Name == "UserListIndex": 284 v = 1 285 elif p.Name == "ContainsHeader": 286 v = i != 1 287 sd2.append(pv(p.Name, v)) 288 doc.Sheets.getByIndex(i).getCellRangeByName("A1:E12").sort(tuple(sd2)) 289 step("sorts", sorts) 290 291 def subtotals(): 292 for i in (4, 5): 293 db = add_db(doc, i, "sub%d" % i) 294 st = db.getSubTotalDescriptor() 295 st.InsertPageBreaks = i == 5 296 st.IsCaseSensitive = i == 5 297 st.BindFormatsToContent = i == 4 298 st.EnableSort = True 299 st.SortAscending = i == 4 300 if i == 5: 301 st.EnableUserSortList = True 302 st.UserSortListIndex = 1 303 funcs = ["SUM", "COUNT", "AVERAGE", "MAX", "MIN", "PRODUCT", "COUNTNUMS", "STDEV", "STDEVP", "VAR", "VARP"] 304 f0 = st.addNew((), 0) 305 cols = tuple(struct("com.sun.star.sheet.SubTotalColumn", 2 + (k % 2), uno.Enum("com.sun.star.sheet.GeneralFunction", fn)) for k, fn in enumerate(funcs)) 306 sf = st.getByIndex(0) 307 sf.setSubTotalColumns(cols) 308 st.addNew((struct("com.sun.star.sheet.SubTotalColumn", 3, uno.Enum("com.sun.star.sheet.GeneralFunction", "SUM")),), 1) 309 step("subtotals", subtotals) 310 311 def attrs(): 312 for i, (keepf, mov, strip, auto, refresh) in enumerate(((True, True, True, True, 60), (False, False, False, False, 0))): 313 db = add_db(doc, 6 + i, "attr%d" % i) 314 db.KeepFormats, db.MoveCells, db.StripData, db.AutoFilter, db.RefreshPeriod = keepf, mov, strip, auto, refresh 315 fd = db.getFilterDescriptor() 316 fd.ContainsHeader = i == 1 317 sd = tuple(pv(p.Name, (i == 0) if p.Name == "IsSortColumns" else p.Value) for p in db.getSortDescriptor()) 318 doc.Sheets.getByIndex(6 + i).getCellRangeByName("A1:E12").sort(sd) 319 step("dbattrs", attrs) 320 save(doc, "dbranges-sort-subtotal.ods") 321 doc.close(True) 322 323 324step("dbranges", dbranges) 325 326 327def pivot_source(doc, sh, rows=40): 328 heads = ("Region", "Product", "Qty", "Price", "Day", "Hour") 329 for c, h in enumerate(heads): 330 sh.getCellByPosition(c, 0).String = h 331 regions = ("North", "South", "East", "West") 332 prods = ("Apple", "Pear", "Plum") 333 nf = doc.NumberFormats 334 loc = struct("com.sun.star.lang.Locale", "en", "US", "") 335 dfmt = nf.queryKey("YYYY-MM-DD", loc, False) 336 if dfmt == -1: 337 dfmt = nf.addNew("YYYY-MM-DD", loc) 338 tfmt = nf.queryKey("HH:MM:SS", loc, False) 339 if tfmt == -1: 340 tfmt = nf.addNew("HH:MM:SS", loc) 341 for r in range(1, rows): 342 sh.getCellByPosition(0, r).String = regions[r % 4] 343 sh.getCellByPosition(1, r).String = prods[r % 3] 344 sh.getCellByPosition(2, r).Value = (r * 7) % 23 345 sh.getCellByPosition(3, r).Value = 1.25 * r 346 c = sh.getCellByPosition(4, r) 347 c.Value = 43831 + r * 37 348 c.NumberFormat = dfmt 349 c = sh.getCellByPosition(5, r) 350 c.Value = (r * 3671 % 86400) / 86400.0 351 c.NumberFormat = tfmt 352 return rng_addr(sh.RangeAddress.Sheet, 0, 0, 5, rows - 1) 353 354 355def new_pivot(doc, sheetname, name, src, at=(8, 2), fields=None, **props): 356 sh = doc.Sheets.getByName(sheetname) 357 dps = sh.DataPilotTables 358 desc = dps.createDataPilotDescriptor() 359 desc.setSourceRange(src) 360 fl = desc.getDataPilotFields() 361 for fname, fp in (fields or {}).items(): 362 f = fl.getByName(fname) if hasattr(fl, "getByName") else None 363 for k, v in fp.items(): 364 setattr(f, k, v) if not isinstance(v, uno.Enum) else f.setPropertyValue(k, v) 365 for k, v in props.items(): 366 desc.setPropertyValue(k, v) 367 dps.insertNewByName(name, cell_addr(sh.RangeAddress.Sheet, at[0], at[1]), desc) 368 return dps.getByName(name) 369 370 371def orient(n): 372 return uno.Enum("com.sun.star.sheet.DataPilotFieldOrientation", n) 373 374 375def gf(n): 376 return uno.Enum("com.sun.star.sheet.GeneralFunction", n) 377 378 379def pivots(): 380 doc = new() 381 sheets = doc.Sheets 382 names = ["Src", "Funcs", "Refs", "Sorts", "AutoShow", "Layout", "Groups", "Totals", "Filtered", "Pages"] 383 for i, n in enumerate(names[1:], 1): 384 sheets.insertNewByName(n, i) 385 sheets.getByIndex(0).Name = "Src" 386 src = pivot_source(doc, sheets.getByName("Src")) 387 funcs = ["SUM", "COUNT", "AVERAGE", "MAX", "MIN", "PRODUCT", "COUNTNUMS", "STDEV", "STDEVP", "VAR", "VARP"] 388 FN = "Funcs" 389 390 def func_pivot(k, fn): 391 t = new_pivot(doc, FN, "pf_" + fn.lower(), src, at=(k * 4, 1 + 0), fields={ 392 "Region": {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA"), "Function": gf(fn)}}) 393 for k, fn in enumerate(funcs): 394 step("func-" + fn, func_pivot, k, fn) 395 396 RT = "com.sun.star.sheet.DataPilotFieldReferenceType." 397 IT = "com.sun.star.sheet.DataPilotFieldReferenceItemType." 398 399 def ref_pivot(k, rtype, itype): 400 t = new_pivot(doc, "Refs", "pr_%s_%s" % (rtype.lower(), itype.lower()), src, at=(k * 4, 1), fields={ 401 "Region": {"Orientation": orient("ROW")}, "Product": {"Orientation": orient("COLUMN")}, 402 "Qty": {"Orientation": orient("DATA")}}) 403 f = t.getDataPilotFields().getByName("Qty") 404 r = struct("com.sun.star.sheet.DataPilotFieldReference") 405 r.ReferenceType = uno.getConstantByName(RT + rtype) 406 r.ReferenceField = "Region" 407 r.ReferenceItemType = uno.getConstantByName(IT + itype) 408 r.ReferenceItemName = "North" if itype == "NAMED" else "" 409 f.setPropertyValue("Reference", r) 410 k = 0 411 for rtype in ("NONE", "ITEM_DIFFERENCE", "ITEM_PERCENTAGE", "ITEM_PERCENTAGE_DIFFERENCE", "RUNNING_TOTAL", "ROW_PERCENTAGE", 412 "COLUMN_PERCENTAGE", "TOTAL_PERCENTAGE", "INDEX"): 413 for itype in (("NAMED", "PREVIOUS", "NEXT") if rtype.startswith("ITEM") else ("NAMED",)): 414 step("ref-%s-%s" % (rtype, itype), ref_pivot, k, rtype, itype) 415 k += 1 416 417 SM = "com.sun.star.sheet.DataPilotFieldSortMode." 418 419 def sort_pivot(k, mode, asc): 420 t = new_pivot(doc, "Sorts", "ps_%d" % k, src, at=(k * 4, 1), fields={ 421 "Region": {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA")}}) 422 f = t.getDataPilotFields().getByName("Region") 423 si = struct("com.sun.star.sheet.DataPilotFieldSortInfo") 424 si.Field = "Qty" if mode == "DATA" else "" 425 si.IsAscending = asc 426 si.Mode = uno.getConstantByName(SM + mode) 427 f.setPropertyValue("SortInfo", si) 428 k = 0 429 for mode in ("NONE", "MANUAL", "NAME", "DATA"): 430 for asc in (True, False): 431 step("sort-%s-%s" % (mode, asc), sort_pivot, k, mode, asc) 432 k += 1 433 434 def auto_pivot(k, mode, enabled): 435 t = new_pivot(doc, "AutoShow", "pa_%d" % k, src, at=(k * 4, 1), fields={ 436 "Region": {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA")}}) 437 f = t.getDataPilotFields().getByName("Region") 438 ai = struct("com.sun.star.sheet.DataPilotFieldAutoShowInfo") 439 ai.IsEnabled = enabled 440 ai.ShowItemsMode = uno.getConstantByName("com.sun.star.sheet.DataPilotFieldShowItemsMode." + mode) 441 ai.ItemCount = 2 442 ai.DataField = "Qty" 443 f.setPropertyValue("AutoShowInfo", ai) 444 k = 0 445 for mode in ("FROM_TOP", "FROM_BOTTOM"): 446 for en in (True, False): 447 step("autoshow-%s-%s" % (mode, en), auto_pivot, k, mode, en) 448 k += 1 449 450 def layout_pivot(k, mode, empty): 451 t = new_pivot(doc, "Layout", "pl_%d" % k, src, at=(k * 5, 1), fields={ 452 "Region": {"Orientation": orient("ROW")}, "Product": {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA")}}) 453 f = t.getDataPilotFields().getByName("Region") 454 li = struct("com.sun.star.sheet.DataPilotFieldLayoutInfo") 455 li.AddEmptyLines = empty 456 li.LayoutMode = uno.getConstantByName("com.sun.star.sheet.DataPilotFieldLayoutMode." + mode) 457 f.setPropertyValue("LayoutInfo", li) 458 k = 0 459 for mode in ("TABULAR_LAYOUT", "OUTLINE_SUBTOTALS_TOP", "OUTLINE_SUBTOTALS_BOTTOM"): 460 for empty in (True, False): 461 step("layout-%s-%s" % (mode, empty), layout_pivot, k, mode, empty) 462 k += 1 463 464 def totals_pivot(k, row, col, **kw): 465 new_pivot(doc, "Totals", "pt_%d" % k, src, at=(k * 4, 1), fields={ 466 "Region": {"Orientation": orient("ROW")}, "Product": {"Orientation": orient("COLUMN")}, 467 "Qty": {"Orientation": orient("DATA")}}, RowGrand=row, ColumnGrand=col, **kw) 468 for k, (row, col, kw) in enumerate(((True, True, {}), (True, False, {}), (False, True, {}), (False, False, {}), 469 (True, True, {"IgnoreEmptyRows": True}), (True, True, {"RepeatIfEmpty": True}), 470 (True, True, {"ShowFilterButton": False}), (True, True, {"DrillDownOnDoubleClick": False}))): 471 step("totals-%d" % k, totals_pivot, k, row, col, **kw) 472 473 def sub_pivot(): 474 t = new_pivot(doc, "Totals", "pt_sub", src, at=(0, 12), fields={ 475 "Region": {"Orientation": orient("ROW")}, "Product": {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA")}}) 476 f = t.getDataPilotFields().getByName("Region") 477 uno.invoke(f, "setPropertyValue", ("Subtotals", uno.Any("[]com.sun.star.sheet.GeneralFunction", tuple(gf(n) for n in ("SUM", "COUNT", "AVERAGE", "MAX"))))) 478 f.setPropertyValue("ShowEmpty", True) 479 f2 = t.getDataPilotFields().getByName("Product") 480 uno.invoke(f2, "setPropertyValue", ("Subtotals", uno.Any("[]com.sun.star.sheet.GeneralFunction", (gf("AUTO"),)))) 481 items = f.getItems() 482 for n in items.ElementNames[:2]: 483 it = items.getByName(n) 484 it.IsHidden = n == items.ElementNames[0] 485 it.ShowDetail = False 486 step("subtotals", sub_pivot) 487 488 def page_pivot(): 489 t = new_pivot(doc, "Pages", "pg_1", src, at=(0, 3), fields={ 490 "Region": {"Orientation": orient("PAGE")}, "Product": {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA")}}) 491 f = t.getDataPilotFields().getByName("Region") 492 f.setPropertyValue("CurrentPage", "South") 493 step("page", page_pivot) 494 495 def hidden_pivot(): 496 t = new_pivot(doc, "Pages", "pg_2", src, at=(6, 3), fields={ 497 "Region": {"Orientation": orient("HIDDEN")}, "Product": {"Orientation": orient("COLUMN")}, 498 "Qty": {"Orientation": orient("DATA")}, "Price": {"Orientation": orient("DATA")}}) 499 step("two-data-fields", hidden_pivot) 500 501 def filtered_pivot(): 502 sh = doc.Sheets.getByName("Filtered") 503 dps = sh.DataPilotTables 504 desc = dps.createDataPilotDescriptor() 505 desc.setSourceRange(src) 506 fl = desc.getDataPilotFields() 507 fl.getByName("Region").Orientation = orient("ROW") 508 fl.getByName("Qty").Orientation = orient("DATA") 509 fd = desc.getFilterDescriptor() 510 fd.setFilterFields2((filter_field(1, "EQUAL", "Apple", False, "AND"), filter_field(2, "GREATER", 2.0, True, "AND"), 511 filter_field(0, "NOT_EQUAL", "East", False, "OR"))) 512 fd.IsCaseSensitive = True 513 fd.SkipDuplicates = True 514 fd.UseRegularExpressions = False 515 dps.insertNewByName("pfil_1", cell_addr(doc.Sheets.getByName("Filtered").RangeAddress.Sheet, 0, 0), desc) 516 desc2 = dps.createDataPilotDescriptor() 517 desc2.setSourceRange(src) 518 desc2.getDataPilotFields().getByName("Product").Orientation = orient("ROW") 519 desc2.getDataPilotFields().getByName("Price").Orientation = orient("DATA") 520 fd = desc2.getFilterDescriptor() 521 fd.setFilterFields2((filter_field(0, "EQUAL", "N.*", False, "OR"), filter_field(1, "CONTAINS", "ea", False, "OR"))) 522 fd.UseRegularExpressions = True 523 dps.insertNewByName("pfil_2", cell_addr(doc.Sheets.getByName("Filtered").RangeAddress.Sheet, 6, 0), desc2) 524 step("filtered", filtered_pivot) 525 526 GB = "com.sun.star.sheet.DataPilotFieldGroupBy." 527 528 def date_group(k, part, auto, fname="Day"): 529 t = new_pivot(doc, "Groups", "pd_%d" % k, src, at=(k * 4, 1), fields={ 530 fname: {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA")}}) 531 f = t.getDataPilotFields().getByName(fname) 532 gi = struct("com.sun.star.sheet.DataPilotFieldGroupInfo") 533 gi.HasAutoStart = auto 534 gi.HasAutoEnd = auto 535 gi.HasDateValues = True 536 gi.Start = 43831.0 if fname == "Day" else 0.0 537 gi.End = 50000.0 if fname == "Day" else 0.99 538 gi.Step = 1.0 if part == "DAYS" else 0.0 539 gi.GroupBy = uno.getConstantByName(GB + part) 540 f.createDateGroup(gi) 541 k = 0 542 for part, fname in (("YEARS", "Day"), ("QUARTERS", "Day"), ("MONTHS", "Day"), ("DAYS", "Day"), ("HOURS", "Hour"), 543 ("MINUTES", "Hour"), ("SECONDS", "Hour")): 544 for auto in (True, False): 545 step("dategroup-%s-%s" % (part, auto), date_group, k, part, auto, fname) 546 k += 1 547 548 def num_group(k, auto): 549 t = new_pivot(doc, "Groups", "pn_%d" % k, src, at=(60 + k * 4, 1), fields={ 550 "Day": {"Orientation": orient("ROW")}, "Price": {"Orientation": orient("DATA")}}) 551 f = t.getDataPilotFields().getByName("Day") 552 gi = struct("com.sun.star.sheet.DataPilotFieldGroupInfo") 553 gi.HasAutoStart = auto 554 gi.HasAutoEnd = auto 555 gi.HasDateValues = True 556 gi.Start = 43831.0 557 gi.End = 45000.0 558 gi.Step = 7.0 559 gi.GroupBy = uno.getConstantByName(GB + "DAYS") 560 f.createDateGroup(gi) 561 for k, auto in enumerate((True, False)): 562 step("dayrange-%s" % auto, num_group, k, auto) 563 564 def name_group(): 565 t = new_pivot(doc, "Groups", "pg_names", src, at=(80, 1), fields={ 566 "Region": {"Orientation": orient("ROW")}, "Qty": {"Orientation": orient("DATA")}}) 567 f = t.getDataPilotFields().getByName("Region") 568 f.createNameGroup(("North", "South")) 569 f.createNameGroup(("East",)) 570 step("namegroup", name_group) 571 save(doc, "pivot-tables.ods") 572 doc.close(True) 573 574 575step("pivots", pivots) 576 577 578def tracked_doc(name, ops): 579 doc = new() 580 sh = doc.Sheets.getByIndex(0) 581 doc.Sheets.insertNewByName("Other", 1) 582 for r in range(8): 583 for c in range(4): 584 sh.getCellByPosition(c, r).Value = r * 10 + c 585 sh.getCellByPosition(4, r).Formula = "=SUM(A%d:D%d)" % (r + 1, r + 1) 586 fr = doc.CurrentController.Frame 587 disp = SM.createInstanceWithContext("com.sun.star.frame.DispatchHelper", CTX) 588 disp.executeDispatch(fr, ".uno:TraceChangeMode", "", 0, (pv("TraceChangeMode", True),)) 589 for label, fn in ops: 590 step(name + ":" + label, fn, doc, sh) 591 save(doc, name) 592 doc.close(True) 593 594 595def tracked_changes(): 596 def a(doc, sh): 597 sh.getCellByPosition(0, 0).Value = 111 598 sh.getCellByPosition(0, 0).Value = 112 599 sh.getCellByPosition(1, 0).String = "text" 600 sh.getCellByPosition(5, 5).Formula = "=A1+1" 601 sh.getCellByPosition(5, 5).Value = 3 602 sh.getCellRangeByName("A2:B3").clearContents(1 | 2 | 4 | 16) 603 def b(doc, sh): 604 sh.Rows.insertByIndex(2, 2) 605 sh.Rows.insertByIndex(3, 1) 606 sh.Rows.removeByIndex(2, 3) 607 sh.Columns.insertByIndex(1, 2) 608 sh.Columns.removeByIndex(2, 1) 609 sh.getCellByPosition(0, 0).Value = 7 610 def c(doc, sh): 611 sh.Rows.insertByIndex(4, 3) 612 sh.getCellByPosition(0, 5).Value = 1 613 sh.Rows.removeByIndex(5, 2) 614 sh.Rows.removeByIndex(3, 1) 615 def d(doc, sh): 616 sh.moveRange(sh.getCellByPosition(7, 0).CellAddress, sh.getCellRangeByName("A1:B2").RangeAddress) 617 sh.getCellByPosition(7, 0).Value = 5 618 sh.moveRange(sh.getCellByPosition(9, 3).CellAddress, sh.getCellRangeByName("H1:I2").RangeAddress) 619 sh.copyRange(sh.getCellByPosition(0, 12).CellAddress, sh.getCellRangeByName("C1:D4").RangeAddress) 620 sh.Rows.removeByIndex(0, 1) 621 def e(doc, sh): 622 o = doc.Sheets.getByName("Other") 623 o.getCellByPosition(0, 0).Value = 4 624 o.getCellByPosition(0, 1).Formula = "=$Sheet1.A1+A1" 625 sh.getCellByPosition(0, 0).Value = 8 626 sh.Columns.removeByIndex(0, 1) 627 doc.Sheets.removeByName("Other") 628 def f(doc, sh): 629 sh.getCellByPosition(0, 0).String = "multi\nline" 630 sh.getCellByPosition(1, 0).Value = 1.5 631 sh.getCellByPosition(2, 0).Formula = "=1+1" 632 sh.getCellByPosition(3, 0).Formula = '="s"&"t"' 633 sh.getCellByPosition(0, 0).Value = 3 634 sh.Rows.removeByIndex(0, 1) 635 sh.Rows.removeByIndex(0, 4) 636 tracked_doc("tracked-content.ods", [("a", a)]) 637 tracked_doc("tracked-insdel.ods", [("b", b)]) 638 tracked_doc("tracked-cutoffs.ods", [("c", c)]) 639 tracked_doc("tracked-move.ods", [("d", d)]) 640 tracked_doc("tracked-sheets.ods", [("e", e)]) 641 tracked_doc("tracked-deleted-content.ods", [("f", f)]) 642 tracked_doc("tracked-all.ods", [("a", a), ("b", b), ("c", c), ("d", d), ("f", f)]) 643 644 645step("tracked", tracked_changes) 646 647 648def link_source(): 649 path = os.path.join(work, "link-source.ods") 650 doc = new() 651 sh = doc.Sheets.getByIndex(0) 652 sh.Name = "SrcSheet" 653 for r in range(5): 654 for c in range(4): 655 sh.getCellByPosition(c, r).Value = r * 4 + c 656 doc.storeToURL(uno.systemPathToFileUrl(path), (pv("FilterName", "calc8"),)) 657 doc.close(True) 658 return uno.systemPathToFileUrl(path) 659 660 661def links(): 662 url = link_source() 663 doc = sheets_doc(4) 664 665 def arealinks(): 666 sh = doc.Sheets.getByIndex(0) 667 doc.AreaLinks.insertAtPosition(cell_addr(0, 0, 0), url, "SrcSheet.A1:D5", "calc8", "") 668 doc.AreaLinks.insertAtPosition(cell_addr(0, 6, 0), url, "SrcSheet.B2:C4", "calc8", "") 669 al = doc.AreaLinks.getByIndex(1) 670 al.RefreshDelay = 90 671 step("arealinks", arealinks) 672 673 def sheetlinks(): 674 for i, mode in ((1, "NORMAL"), (2, "VALUE")): 675 sh = doc.Sheets.getByIndex(i) 676 sh.link(url, "SrcSheet", "calc8", "", E("SheetLinkMode", mode)) 677 sl = doc.SheetLinks 678 for n in sl.ElementNames: 679 l = sl.getByName(n) 680 l.RefreshDelay = 120 681 break 682 step("sheetlinks", sheetlinks) 683 save(doc, "links.ods") 684 doc.close(True) 685 686 687def scenarios(): 688 doc = sheets_doc(1) 689 sh = doc.Sheets.getByIndex(0) 690 for r in range(6): 691 for c in range(4): 692 sh.getCellByPosition(c, r).Value = r * 4 + c 693 694 def mk(name, ranges, comment): 695 doc.Sheets.getByIndex(0).Scenarios.addNewByName(name, ranges, comment) 696 scn = doc.Sheets.getByName(name) 697 return scn 698 699 def sc1(): 700 scn = mk("scn_one", (rng_addr(0, 0, 0, 1, 1),), "first scenario") 701 scn.getCellByPosition(0, 0).Value = 100 702 scn.ShowBorder = True 703 scn.PrintBorder = True 704 scn.CopyBack = True 705 scn.CopyStyles = True 706 scn.CopyFormulas = True 707 scn.BorderColor = 0x00FF8000 708 scn.IsActive = True 709 step("scenario-1", sc1) 710 711 def sc2(): 712 scn = mk("scn_two", (rng_addr(0, 2, 2, 3, 3), rng_addr(0, 0, 5, 0, 5)), "") 713 scn.ShowBorder = False 714 scn.PrintBorder = False 715 scn.CopyBack = False 716 scn.CopyStyles = False 717 scn.CopyFormulas = False 718 scn.Protected = True 719 scn.IsActive = False 720 step("scenario-2", sc2) 721 722 def sc3(): 723 scn = mk("scn_three", (rng_addr(0, 1, 1, 2, 2),), "third \u00e4 & <b>") 724 scn.CopyFormulas = True 725 step("scenario-3", sc3) 726 save(doc, "scenarios.ods") 727 doc.close(True) 728 729 730def consolidation(): 731 doc = sheets_doc(3) 732 for i in range(2): 733 sh = doc.Sheets.getByIndex(i) 734 for r in range(4): 735 sh.getCellByPosition(0, r).String = "k%d" % r 736 for c in range(1, 3): 737 sh.getCellByPosition(c, r).Value = r + c + i 738 sh.getCellByPosition(1, 0).String = "h1" 739 sh.getCellByPosition(2, 0).String = "h2" 740 fns = ["SUM", "COUNT", "AVERAGE", "MAX", "MIN", "PRODUCT", "COUNTNUMS", "STDEV", "STDEVP", "VAR", "VARP"] 741 for k, fn in enumerate(fns[:6]): 742 def one(k=k, fn=fn): 743 cd = doc.createConsolidationDescriptor(True) 744 cd.Function = gf(fn) 745 cd.UseColumnHeaders = k % 2 == 0 746 cd.UseRowHeaders = k % 3 != 0 747 cd.Sources = (rng_addr(0, 0, 0, 2, 3), rng_addr(1, 0, 0, 2, 3)) 748 cd.StartOutputPosition = cell_addr(2, 0, k * 6) 749 cd.InsertLinks = k % 2 == 1 750 doc.consolidate(cd) 751 step("consolidate-" + fn, one) 752 if k == 5: 753 break 754 save(doc, "consolidation.ods") 755 doc.close(True) 756 757 for fn, rows, cols, links in (("SUM", False, False, False), ("MAX", True, True, True), ("MIN", False, True, False)): 758 doc = sheets_doc(3) 759 for i in range(2): 760 sh = doc.Sheets.getByIndex(i) 761 for r in range(4): 762 sh.getCellByPosition(0, r).String = "k%d" % r 763 for c in range(1, 3): 764 sh.getCellByPosition(c, r).Value = r + c + i 765 766 def one(): 767 cd = doc.createConsolidationDescriptor(True) 768 cd.Function = gf(fn) 769 cd.UseColumnHeaders = cols 770 cd.UseRowHeaders = rows 771 cd.Sources = (rng_addr(0, 0, 0, 2, 3), rng_addr(1, 0, 0, 2, 3)) 772 cd.StartOutputPosition = cell_addr(2, 0, 0) 773 cd.InsertLinks = links 774 doc.consolidate(cd) 775 step("consolidate-%s-%s%s" % (fn, rows, cols), one) 776 save(doc, "consolidation-%s.ods" % fn.lower()) 777 doc.close(True) 778 779 780def detective(): 781 doc = new() 782 sh = doc.Sheets.getByIndex(0) 783 for r in range(4): 784 sh.getCellByPosition(0, r).Value = r + 1 785 sh.getCellByPosition(1, 0).Formula = "=SUM(A1:A4)" 786 sh.getCellByPosition(1, 1).Formula = "=B1*2" 787 sh.getCellByPosition(1, 2).Formula = "=1/0" 788 sh.getCellByPosition(1, 3).Formula = "=B3+1" 789 v = sh.getCellByPosition(2, 0).Validation 790 v.Type = E("ValidationType", "WHOLE") 791 v.setOperator(E("ConditionOperator", "BETWEEN")) 792 v.setFormula1("1") 793 v.setFormula2("10") 794 sh.getCellByPosition(2, 0).Validation = v 795 sh.getCellByPosition(2, 0).Value = 99 796 ctrl = doc.CurrentController 797 disp = SM.createInstanceWithContext("com.sun.star.frame.DispatchHelper", CTX) 798 799 def do(cmd, addr): 800 ctrl.select(sh.getCellRangeByName(addr)) 801 disp.executeDispatch(ctrl.Frame, ".uno:" + cmd, "", 0, ()) 802 for cmd, addr in (("ShowPrecedents", "B1"), ("ShowDependents", "A2"), ("ShowDependents", "B2"), ("ShowPrecedents", "B2"), ("ShowErrors", "B4"), 803 ("MarkErrors", "B3"), ("ShowInvalid", "C1"), ("AddPrecedents", "B1"), ("AddDependents", "A1"), ("ShowDependents", "A4"), 804 ("ShowPrecedents", "B4")): 805 step("detective-" + cmd, do, cmd, addr) 806 save(doc, "detective.ods") 807 doc.close(True) 808 809 810def shapes_doc(): 811 doc = sheets_doc(2) 812 sh = doc.Sheets.getByIndex(0) 813 for r in range(5): 814 sh.getCellByPosition(0, r).Value = r 815 dp = sh.DrawPage 816 817 def add(kind, x, y, w, h, anchor_cell=None, text=""): 818 shp = doc.createInstance("com.sun.star.drawing." + kind) 819 dp.add(shp) 820 shp.Position = struct("com.sun.star.awt.Point", x, y) 821 shp.Size = struct("com.sun.star.awt.Size", w, h) 822 if text: 823 shp.String = text 824 shp.Anchor = sh.getCellRangeByName(anchor_cell) if anchor_cell is not None else sh 825 return shp 826 step("shape-page-rect", add, "RectangleShape", 3000, 1000, 3000, 2000, None, "page anchored") 827 step("shape-page-ellipse", add, "EllipseShape", 7000, 1000, 2000, 2000) 828 step("shape-cell-rect", add, "RectangleShape", 3000, 4000, 3000, 2000, "B3", "cell anchored") 829 step("shape-cell-line", add, "LineShape", 3000, 7000, 3000, 1000, "C5") 830 step("shape-cell-text", add, "TextShape", 8000, 5000, 3000, 1000, "D2", "text") 831 step("shape-conn", add, "ConnectorShape", 1000, 9000, 3000, 1000) 832 sh2 = doc.Sheets.getByIndex(1) 833 shp = doc.createInstance("com.sun.star.drawing.RectangleShape") 834 sh2.DrawPage.add(shp) 835 shp.Size = struct("com.sun.star.awt.Size", 2000, 1000) 836 shp.Position = struct("com.sun.star.awt.Point", 500, 500) 837 save(doc, "shapes.ods") 838 doc.close(True) 839 840 841def calc_settings(): 842 docs = {"calc-settings-a.ods": dict(CalcAsShown=True, IgnoreCase=True, LookUpLabels=False, MatchWholeCell=False, RegularExpressions=False, 843 IsIterationEnabled=True, IterationCount=250, IterationEpsilon=0.0005, NullDate=struct("com.sun.star.util.Date", 1, 1, 1904)), 844 "calc-settings-b.ods": dict(IterationCount=50, NullDate=struct("com.sun.star.util.Date", 31, 12, 1899)), 845 "calc-settings-c.ods": dict(IsIterationEnabled=True, IterationEpsilon=0.01), 846 "calc-settings-d.ods": dict(CalcAsShown=True), 847 "calc-settings-e.ods": dict(MatchWholeCell=False), 848 "calc-settings-f.ods": dict(LookUpLabels=False), 849 "calc-settings-g.ods": dict(RegularExpressions=False), 850 "calc-settings-h.ods": dict(IgnoreCase=True)} 851 for name, props in docs.items(): 852 doc = new() 853 for k, v in props.items(): 854 step(name + ":" + k, doc.setPropertyValue, k, v) 855 doc.Sheets.getByIndex(0).getCellByPosition(0, 0).Value = 1.234 856 save(doc, name) 857 doc.close(True) 858 859 860def null_year(): 861 cp = SM.createInstanceWithContext("com.sun.star.configuration.ConfigurationProvider", CTX) 862 ca = cp.createInstanceWithArguments("com.sun.star.configuration.ConfigurationUpdateAccess", (pv("nodepath", "/org.openoffice.Office.Common/DateFormat"),)) 863 old = ca.getByName("TwoDigitYear") 864 try: 865 ca.setPropertyValue("TwoDigitYear", 1950) 866 ca.commitChanges() 867 doc = new() 868 doc.Sheets.getByIndex(0).getCellByPosition(0, 0).Value = 1.5 869 save(doc, "calc-settings-null-year.ods") 870 doc.close(True) 871 finally: 872 ca2 = cp.createInstanceWithArguments("com.sun.star.configuration.ConfigurationUpdateAccess", (pv("nodepath", "/org.openoffice.Office.Common/DateFormat"),)) 873 ca2.setPropertyValue("TwoDigitYear", old) 874 ca2.commitChanges() 875 876 877def db_imports(): 878 dbc = SM.createInstance("com.sun.star.sdb.DatabaseContext") 879 if "Bibliography" not in dbc.ElementNames: 880 print("FAIL db_imports: no Bibliography data source") 881 return 882 ds = dbc.getByName("Bibliography") 883 qd = ds.QueryDefinitions 884 if not qd.hasByName("t12q"): 885 q = qd.createInstance() 886 q.Command = "SELECT * FROM biblio" 887 qd.insertByName("t12q", q) 888 doc = sheets_doc(6) 889 DIM = "com.sun.star.sheet.DataImportMode" 890 cases = [("TABLE", "biblio", False, {}), ("SQL", "SELECT * FROM biblio", False, {}), ("SQL", "SELECT * FROM biblio", True, {}), 891 ("QUERY", "t12q", False, {}), ("TABLE", "biblio", False, {"StripData": True}), ("TABLE", "biblio", False, {"FromSelection": True})] 892 for i, (t, obj, native, extra) in enumerate(cases): 893 def one(i=i, t=t, obj=obj, native=native, extra=extra): 894 doc.DatabaseRanges.addNewByName("imp%d" % i, rng_addr(i, 0, 0, 2, 3)) 895 doc.Sheets.getByIndex(i).getCellRangeByPosition(0, 0, 2, 3).doImport( 896 (pv("DatabaseName", "Bibliography"), pv("SourceType", uno.Enum(DIM, t)), pv("SourceObject", obj), pv("IsNative", native))) 897 db = doc.DatabaseRanges.getByName("imp%d" % i) 898 for k, v in extra.items(): 899 db.setPropertyValue(k, v) 900 step("import-%s-%d" % (t, i), one) 901 save(doc, "dbranges-import.ods") 902 doc.close(True) 903 904 905step("null_year", null_year) 906step("db_imports", db_imports) 907step("links", links) 908step("scenarios", scenarios) 909step("consolidation", consolidation) 910step("detective", detective) 911step("shapes", shapes_doc) 912step("calc_settings", calc_settings) 913 914 915CATEGORIES = {1: "database", 2: "datetime", 3: "financial", 4: "information", 5: "logical", 6: "math", 7: "matrix", 8: "statistical", 916 9: "spreadsheet", 10: "text", 11: "addin"} 917 918 919def functions(): 920 fdesc = SM.createInstance("com.sun.star.sheet.FunctionDescriptions") 921 by_cat = {} 922 for i in range(fdesc.getCount()): 923 d = {p.Name: p.Value for p in fdesc.getByIndex(i)} 924 by_cat.setdefault(d["Category"], []).append(d) 925 print("function descriptions:", {CATEGORIES.get(k, k): len(v) for k, v in sorted(by_cat.items())}) 926 for cat, items in sorted(by_cat.items()): 927 doc = new() 928 sh = doc.Sheets.getByIndex(0) 929 for r in range(1, 8): 930 for c in range(4): 931 sh.getCellByPosition(c, r + 20).Value = r + c 932 failed_fns = [] 933 for k, d in enumerate(items): 934 args = d.get("Arguments") or () 935 n = sum(1 for a in args if not a.IsOptional) 936 if n == 0 and args: 937 n = 1 938 if cat == 10: 939 vals = ['"abc"', "2", "1", '"b"'] 940 elif cat == 1: 941 vals = ["$A$21:$D$27", "2", "$A$21:$A$22"] 942 elif cat in (7, 9, 8): 943 vals = ["$A$21:$B$24", "$C$21:$D$24", "2", "1"] 944 else: 945 vals = ["2", "1", "3", "4", "5", "6", "7"] 946 text = "=%s(%s)" % (d["Name"], ";".join((vals + ["1"] * 8)[:n])) if n else "=%s()" % d["Name"] 947 cell = sh.getCellByPosition((k // 100) * 3, k % 100) 948 try: 949 cell.Formula = text 950 except Exception as e: 951 failed_fns.append(d["Name"]) 952 print("functions", CATEGORIES.get(cat, cat), len(items), "unparsed:", failed_fns[:10]) 953 save(doc, "formulas-%02d-%s.ods" % (cat, CATEGORIES.get(cat, cat))) 954 doc.close(True) 955 956 957def formula_forms(): 958 doc = sheets_doc(3) 959 sh = doc.Sheets.getByIndex(0) 960 sh.Name = "Data sheet" 961 doc.Sheets.getByIndex(1).Name = "it's" 962 doc.Sheets.getByIndex(2).Name = "Sheet 3" 963 for r in range(10): 964 sh.getCellByPosition(0, r).Value = r 965 sh.getCellByPosition(1, r).String = "t%d" % r 966 forms = ["=1+2*3-4/5^2", "=-A1", "=A1%", "=+A2", "=A1&B1&\"x\"", "=A1=A2", "=A1<>A2", "=A1<=A2", "=A1>=A2", "=A1<A2", "=A1>A2", 967 "=SUM(A1:A5 A3:A7)", "=SUM(A1:A3~A5:A7)", "=SUM($A$1:$A$3)", "=SUM(A:A)", "=SUM(1:3)", "=SUM($'Data sheet'.A1:A3)", 968 "=SUM($'it''s'.A1:$'Sheet 3'.A3)", "=$'it''s'.A1", "=SUM(A1:A3,B1)" , "={1;2;3}", "={1;2|3;4}", "={\"a\";\"b\"}", "=SUM({1;2|3;4})", 969 "=#N/A", "=ERROR.TYPE(#DIV/0!)", "=ISERROR(#VALUE!)", "=IF(A1;\"y\";\"n\")", "=TRUE()", "=FALSE()", "=TRUE", "=PI()", "=1E3+1.5E-3", 970 "=0.5+.5", "=\"he said \"\"hi\"\"\"", "=(1+2)*(3+4)", "=SUM((A1:A3;A5:A7))", "=A1:A3*2", "=ROW(A1:A3)", "=SUM(IF(A1:A5>2;1;0))", 971 "=INDIRECT(\"A1\")", "=OFFSET(A1;1;1)", "=HYPERLINK(\"http://example.org\";\"x\")", "=VLOOKUP(2;A1:B5;2;0)", "=MULTIPLE.OPERATIONS(A1;A2;A3)", 972 "=TEXT(A1;\"0.00\")", "=DDE(\"soffice\";\"x\";\"y\")", "=A1+$Data sheet.A2" ] 973 for i, f in enumerate(forms): 974 def put(i=i, f=f): 975 sh.getCellByPosition(3, i).Formula = f 976 step("form-%d" % i, put) 977 step("array-1", sh.getCellRangeByName("F1:G2").setArrayFormula, "=A1:B2*2") 978 step("array-2", sh.getCellRangeByName("H1:H3").setArrayFormula, "=A1:A3+1") 979 step("array-3", sh.getCellRangeByName("J1:K1").setArrayFormula, "=TRANSPOSE(A1:A2)") 980 sh.getCellByPosition(14, 0).Formula = "=A5*2+A6" 981 # setTableOperation on the Spreadsheet object itself fills the whole sheet and keeps the office busy for minutes 982 step("tableop-col", sh.getCellRangeByName("O2:O5").setTableOperation, sh.getCellRangeByName("N1:O1").RangeAddress, E("TableOperationMode", "COLUMN"), cell_addr(0, 0, 4), cell_addr(0, 0, 5)) 983 step("tableop-row", sh.getCellRangeByName("Q2:R2").setTableOperation, sh.getCellRangeByName("P1:P2").RangeAddress, E("TableOperationMode", "ROW"), cell_addr(0, 0, 4), cell_addr(0, 0, 5)) 984 step("tableop-both", sh.getCellRangeByName("U2:W4").setTableOperation, sh.getCellRangeByName("T1:T1").RangeAddress, E("TableOperationMode", "BOTH"), cell_addr(0, 0, 4), cell_addr(0, 0, 5)) 985 save(doc, "formulas-forms.ods") 986 doc.close(True) 987 988 989def names_and_ranges(): 990 doc = sheets_doc(2) 991 sh = doc.Sheets.getByIndex(0) 992 fill_table(sh) 993 nr = doc.NamedRanges 994 NF = "com.sun.star.sheet.NamedRangeFlag." 995 spec = [("rng_abs", "$Sheet1.$A$1:$C$5", 0), ("rng_rel", "A1:B2", 0), ("expr_const", "0.19", 0), ("expr_formula", "SUM($Sheet1.$C$2:$C$9)*2", 0), 996 ("expr_rel", "$Sheet1.A2*2", 0), ("expr_text", "\"abc\"", 0), ("expr_func", "TODAY()+1", 0), 997 ("print_ar", "$Sheet1.$A$1:$E$12", 2), ("crit", "$Sheet1.$A$1:$B$2", 1), ("colhdr", "$Sheet1.$A$1:$E$1", 4), ("rowhdr", "$Sheet1.$A$1:$A$12", 8), 998 ("all_flags", "$Sheet1.$A$1:$E$12", 1 | 2 | 4 | 8), ("na\u00efve", "$Sheet1.$B$2", 0), ("nested", "rng_abs", 0), ("sheet2ref", "$Sheet2.$A$1", 0), 999 ("array_c", "{1;2|3;4}", 0), ("err_ref", "#REF!", 0)] 1000 for n, c, fl in spec: 1001 step("name-" + n, nr.addNewByName, n, c, cell_addr(0, 0, 0), fl) 1002 for i, rng in enumerate(("A2:A12", "B2:B12")): 1003 step("collabel-%d" % i, doc.ColumnLabelRanges.addNew, rng_addr(0, i, 0, i, 0), rng_addr(0, i, 1, i, 11)) 1004 step("rowlabel-1", doc.RowLabelRanges.addNew, rng_addr(0, 0, 1, 0, 11), rng_addr(0, 1, 1, 4, 11)) 1005 step("rowlabel-2", doc.RowLabelRanges.addNew, rng_addr(1, 0, 0, 0, 3), rng_addr(1, 1, 0, 3, 3)) 1006 step("print-areas", sh.setPrintAreas, (rng_addr(0, 0, 0, 4, 5), rng_addr(0, 0, 8, 4, 11))) 1007 step("title-rows", sh.setPrintTitleRows, True) 1008 step("title-rows-r", sh.setTitleRows, rng_addr(0, 0, 0, 0, 0)) 1009 step("title-cols", sh.setPrintTitleColumns, True) 1010 step("title-cols-r", sh.setTitleColumns, rng_addr(0, 0, 0, 0, 0)) 1011 save(doc, "named-ranges.ods") 1012 doc.close(True) 1013 1014 1015def conditional_formats(): 1016 doc = sheets_doc(1) 1017 sh = doc.Sheets.getByIndex(0) 1018 fill_table(sh) 1019 styles = doc.StyleFamilies.getByName("CellStyles") 1020 for n in ("cf_a", "cf_b", "cf_c"): 1021 st = doc.createInstance("com.sun.star.style.CellStyle") 1022 styles.insertByName(n, st) 1023 st.CellBackColor = {"cf_a": 0xFF0000, "cf_b": 0x00FF00, "cf_c": 0x0000FF}[n] 1024 ops = ["EQUAL", "NOT_EQUAL", "GREATER", "GREATER_EQUAL", "LESS", "LESS_EQUAL", "BETWEEN", "NOT_BETWEEN", "FORMULA"] 1025 for i, op in enumerate(ops): 1026 def one(i=i, op=op): 1027 rg = sh.getCellRangeByName("%s2:%s12" % ("ABCDEFGHI"[i], "ABCDEFGHI"[i])) 1028 cf = rg.ConditionalFormat 1029 f1, f2 = ("5", "9") if op in ("BETWEEN", "NOT_BETWEEN") else (("$A$2>0", "") if op == "FORMULA" else ("5", "")) 1030 cf.addNew((pv("Operator", E("ConditionOperator", op)), pv("Formula1", f1), pv("Formula2", f2), 1031 pv("SourcePosition", cell_addr(0, i, 1)), pv("StyleName", "cf_a"))) 1032 cf.addNew((pv("Operator", E("ConditionOperator", "GREATER")), pv("Formula1", '"text"'), pv("Formula2", ""), 1033 pv("SourcePosition", cell_addr(0, i, 1)), pv("StyleName", "cf_b"))) 1034 cf.addNew((pv("Operator", E("ConditionOperator", "FORMULA")), pv("Formula1", "ISEVEN(A2)"), pv("Formula2", ""), 1035 pv("SourcePosition", cell_addr(0, i, 1)), pv("StyleName", "cf_c"))) 1036 rg.ConditionalFormat = cf 1037 step("cf-" + op, one) 1038 save(doc, "conditional-formats.ods") 1039 doc.close(True) 1040 1041 1042def table_attrs(): 1043 doc = sheets_doc(4) 1044 sh = doc.Sheets.getByIndex(0) 1045 fill_table(sh) 1046 for r in range(8): 1047 sh.getCellByPosition(8, r).Value = r / 24.0 + 0.1 1048 nf = doc.NumberFormats 1049 loc = struct("com.sun.star.lang.Locale", "en", "US", "") 1050 tk = nf.queryKey("HH:MM:SS", loc, False) 1051 if tk == -1: 1052 tk = nf.addNew("HH:MM:SS", loc) 1053 for r in range(8): 1054 sh.getCellByPosition(8, r).NumberFormat = tk 1055 dk = nf.queryKey("[HH]:MM:SS", loc, False) 1056 if dk == -1: 1057 dk = nf.addNew("[HH]:MM:SS", loc) 1058 sh.getCellByPosition(9, 0).Value = 2.5 1059 sh.getCellByPosition(9, 0).NumberFormat = dk 1060 sh.getCellByPosition(9, 1).Formula = "=TIME(1;2;3)" 1061 sh.getCellByPosition(9, 1).NumberFormat = tk 1062 sh.getCellByPosition(9, 2).Formula = '=TIME(1;2;3)+DATE(2020;1;1)' 1063 ck = nf.queryKey('#,##0.00 [$EUR]', loc, False) 1064 if ck == -1: 1065 ck = nf.addNew('#,##0.00 [$EUR]', loc) 1066 sh.getCellByPosition(9, 3).Value = 12.5 1067 sh.getCellByPosition(9, 3).NumberFormat = ck 1068 sh.getCellByPosition(9, 5).Formula = "=TRUE()" 1069 sh.getCellByPosition(9, 6).Formula = '="s"' 1070 sh.getCellByPosition(9, 7).Formula = "=1/0" 1071 sh.getCellByPosition(9, 8).Formula = "=NA()" 1072 sh.getCellByPosition(9, 9).String = "multi\nline two spaces\ttab" 1073 sh.Columns.getByIndex(5).IsVisible = False 1074 sh.Columns.getByIndex(7).IsVisible = False 1075 sh.Rows.getByIndex(3).IsVisible = False 1076 sh.Rows.getByIndex(4).IsVisible = False 1077 sh.Rows.getByIndex(6).Height = 1500 1078 sh.Columns.getByIndex(2).Width = 4000 1079 sh.Columns.getByIndex(3).OptimalWidth = True 1080 sh.Rows.getByIndex(7).OptimalHeight = True 1081 sh.Columns.getByIndex(4).IsStartOfNewPage = True 1082 sh.Rows.getByIndex(9).IsStartOfNewPage = True 1083 step("group-rows", sh.group, rng_addr(0, 0, 14, 0, 18), uno.Enum("com.sun.star.table.TableOrientation", "ROWS")) 1084 step("group-rows2", sh.group, rng_addr(0, 0, 15, 0, 16), uno.Enum("com.sun.star.table.TableOrientation", "ROWS")) 1085 step("group-cols", sh.group, rng_addr(0, 11, 0, 13, 0), uno.Enum("com.sun.star.table.TableOrientation", "COLUMNS")) 1086 step("group-cols2", sh.group, rng_addr(0, 20, 0, 22, 0), uno.Enum("com.sun.star.table.TableOrientation", "COLUMNS")) 1087 step("hide-grp-rows", sh.hideDetail, rng_addr(0, 0, 15, 0, 16)) 1088 step("hide-grp-cols", sh.hideDetail, rng_addr(0, 20, 0, 22, 0)) 1089 m = sh.getCellRangeByName("L5:N7") 1090 for c in range(11, 14): 1091 for r in range(4, 7): 1092 cell = sh.getCellByPosition(c, r) 1093 if (c, r) == (11, 4): 1094 cell.String = "top left" 1095 elif (c, r) == (12, 4): 1096 cell.Value = 7 1097 elif (c, r) == (13, 4): 1098 cell.Formula = "=1+1" 1099 elif (c, r) == (11, 5): 1100 cell.String = "covered text" 1101 cell.NumberFormat = nf.getStandardFormat(1, loc) 1102 elif (c, r) == (12, 5): 1103 cell.Value = 43831 1104 cell.NumberFormat = nf.getStandardFormat(2, loc) 1105 elif (c, r) == (13, 5): 1106 cell.Value = 0.25 1107 cell.NumberFormat = tk 1108 elif (c, r) == (11, 6): 1109 cell.Formula = "=TRUE()" 1110 elif (c, r) == (12, 6): 1111 cell.Value = 5 1112 cell.NumberFormat = ck 1113 else: 1114 cell.Formula = '="f"&"x"' 1115 v = sh.getCellByPosition(12, 4).Validation 1116 v.Type = E("ValidationType", "WHOLE") 1117 v.setOperator(E("ConditionOperator", "GREATER")) 1118 v.setFormula1("1") 1119 sh.getCellByPosition(12, 4).Validation = v 1120 step("merge", m.merge, True) 1121 sh.getCellRangeByName("P1:Q1").merge(True) 1122 sh.getCellRangeByName("P3:P5").merge(True) 1123 sh.getCellRangeByName("S1:T2").setArrayFormula("=A2:B3+1") 1124 sh.getCellRangeByName("V1:V3").setArrayFormula("=A2:A4*2") 1125 sh.getCellRangeByName("A1:B2").CellProtection = struct("com.sun.star.util.CellProtection", True, True, True, True) 1126 sh.getCellRangeByName("C1:C2").CellProtection = struct("com.sun.star.util.CellProtection", False, False, False, False) 1127 doc.Sheets.getByIndex(1).protect("pw") 1128 doc.Sheets.getByIndex(2).protect("") 1129 doc.Sheets.getByIndex(3).setPrintAreas(()) 1130 sh2 = doc.Sheets.getByIndex(1) 1131 sh2.getCellByPosition(0, 0).Value = 1 1132 sh2.setPrintAreas((rng_addr(1, 0, 0, 2, 2),)) 1133 doc.Sheets.getByIndex(3).getCellByPosition(0, 0).Value = 1 1134 doc.Sheets.getByIndex(3).IsVisible = False 1135 sh.TabColor = 0x00FF00 1136 doc.Sheets.getByIndex(1).TabColor = 0xFF0000 1137 save(doc, "table-attrs.ods") 1138 doc.close(True) 1139 1140 1141def filtered_rows(): 1142 doc = sheets_doc(1) 1143 sh = doc.Sheets.getByIndex(0) 1144 fill_table(sh, 20) 1145 rg = sh.getCellRangeByName("A1:E20") 1146 doc.DatabaseRanges.addNewByName("flt", rng_addr(0, 0, 0, 4, 19)) 1147 fd = rg.createFilterDescriptor(True) 1148 fd.setFilterFields2((filter_field(0, "EQUAL", "North", False, "AND"),)) 1149 rg.filter(fd) 1150 sh.Rows.getByIndex(18).IsVisible = False 1151 save(doc, "filtered-rows.ods") 1152 doc.close(True) 1153 1154 1155step("functions", functions) 1156step("formula_forms", formula_forms) 1157step("names", names_and_ranges) 1158step("condfmt", conditional_formats) 1159step("table_attrs", table_attrs) 1160step("filtered", filtered_rows) 1161 1162 1163step("validations", validations) 1164step("validation_edges", validation_edges) 1165 1166json.dump(expect, open(os.path.join(work, "expect.json"), "w"), indent=1) 1167sys.exit(1 if failed else 0) 1168