xref: /trunk/test/odf-validate/fixtures/p8/formula_export_check.py (revision 0e16a31d78a652c56c5c0fc79c752f48b9048df9) 
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#**************************************************************
21
22# usage: runpy.sh fixtures/p8/formula_export_check.py <port> <outdir> <mode name> <office:version>
23# The office runs with the save mode of <mode name> (DefaultVersion in its profile). Builds a sheet with a named expression
24# that is a list of ranges (as the Excel import creates it) and with formulas whose references were broken by deleting a column
25# (one single reference, one range, one range with a function), saves it, and checks the formulas in content.xml:
26#  - the range list is joined with the union operator `~`, never with `;`;
27#  - broken references are written as [#REF!], never with #REF! inside a reference such as [.#REF!1];
28#  - a function with several parameters keeps `;` between its parameters;
29# then reloads the file: the named expression keeps both ranges and the broken formulas still give #REF! (error 524); a second
30# save of the reloaded file keeps both spellings.
31# Exit code 1 when any check fails.
32import os, re, sys, zipfile
33import uno
34from com.sun.star.table import CellAddress
35from unohelp import connect, desktop, load, pv
36
37port, out, name, want_version = int(sys.argv[1]), os.path.abspath(sys.argv[2]), sys.argv[3], sys.argv[4]
38os.makedirs(out, exist_ok=True)
39desk = desktop(connect(port))
40doc = desk.loadComponentFromURL("private:factory/scalc", "_blank", 0, (pv("Hidden", True),))
41sh = doc.Sheets.getByIndex(0)
42cell = lambda a: sh.getCellRangeByName(a)
43for r in range(1, 4):
44    cell("A%d" % r).Value = r
45    cell("B%d" % r).Value = 10 * r
46cell("E1").setFormula("=A1+1")
47cell("E2").setFormula("=SUM(A1:A3)")
48cell("E3").setFormula("=IF(A1>0;SUM(A1:A3);0)")
49cell("E4").setFormula("=SUM(B1:B3;C1)")
50doc.NamedRanges.addNewByName("rangelist", "$Sheet1.$H$1:$I$2;$Sheet1.$K$1:$L$2", CellAddress(0, 0, 0), 0)
51sh.Columns.removeByIndex(0, 1)          # column A: the references to it are now broken
52
53path = os.path.join(out, "formulas-%s.ods" % name)
54doc.storeToURL(uno.systemPathToFileUrl(path), (pv("FilterName", "calc8"),))
55doc.close(True)
56
57z = zipfile.ZipFile(path)
58content = z.read("content.xml").decode("utf-8")
59version = re.search(r'office:version="([^"]+)"', content).group(1)
60formulas = re.findall(r'table:formula="([^"]*)"', content)
61exprs = re.findall(r'table:expression="([^"]*)"', content)
62print("version", version, "| formulas", formulas, "| expressions", exprs)
63
64checks = [
65    ("office:version of the mode", version == want_version),
66    ("a named expression was written", len(exprs) == 1),
67    ("range list joined with ~", bool(exprs) and "~" in exprs[0] and "];[" not in exprs[0]),
68    ("#REF! only as a whole reference [#REF!]", all(re.sub(r"\[#REF!\]", "", f).find("#REF!") < 0 for f in formulas + exprs)),
69    ("broken references are [#REF!]", sum("[#REF!]" in f for f in formulas) == 3),
70    ("parameters keep ;", any(f.startswith("of:=SUM(") and ";" in f for f in formulas)
71                          or any(";" in f for f in formulas if "SUM" in f)),
72]
73
74ctx_doc = load(desk, path)
75sh2 = ctx_doc.Sheets.getByIndex(0)
76names = ctx_doc.NamedRanges
77checks.append(("the named expression survives a reload", names.hasByName("rangelist")))
78if names.hasByName("rangelist"):
79    content2 = names.getByName("rangelist").Content
80    print("reloaded content:", content2)
81    checks.append(("the reloaded list has both ranges", content2.count("$G$1") == 1 and content2.count("$J$1") == 1))
82errs = [sh2.getCellRangeByName(a).getError() for a in ("D1", "D2", "D3")]
83print("reloaded errors of the broken formulas (D1..D3):", errs)
84checks.append(("broken formulas still give #REF! (524)", errs == [524, 524, 524]))
85path2 = os.path.join(out, "formulas2-%s.ods" % name)
86ctx_doc.storeToURL(uno.systemPathToFileUrl(path2), (pv("FilterName", "calc8"),))
87ctx_doc.close(True)
88content3 = zipfile.ZipFile(path2).read("content.xml").decode("utf-8")
89f3 = re.findall(r'table:formula="([^"]*)"', content3)
90e3 = re.findall(r'table:expression="([^"]*)"', content3)
91print("second save:", f3, e3)
92checks.append(("a second save keeps [#REF!] and ~", sum("[#REF!]" in f for f in f3) == 3 and len(e3) == 1 and "~" in e3[0]
93               and all(re.sub(r"\[#REF!\]", "", f).find("#REF!") < 0 for f in f3 + e3)))
94
95fails = 0
96for label, ok in checks:
97    fails += not ok
98    print("%-4s %s" % ("ok" if ok else "FAIL", label))
99sys.exit(1 if fails else 0)
100