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/t14/error_cells.py <port> <outdir> 23# Builds an ODS with office:value-type="error" cells (with and without office:string-value, error code only in the text:p, a 24# repeated cell, a string cell as control), loads it, compares each cell's error code with the expected one, saves it again and 25# compares the reloaded file. Exit status 1 when a row fails. 26import os, sys, zipfile 27import uno 28from unohelp import FILTERS, connect, desktop, pv 29 30port, out = int(sys.argv[1]), os.path.abspath(sys.argv[2]) 31os.makedirs(out, exist_ok=True) 32 33# text, expected error code (519 = #VALUE!, the fallback) 34CASES = [("#NULL!", 521), ("#DIV/0!", 532), ("#VALUE!", 519), ("#REF!", 524), ("#NAME?", 525), ("#NUM!", 503), 35 ("#N/A", 32767), ("Err:504", 504), ("Err:99999", 519), ("Err:12x", 519), ("#BOGUS", 519), ("", 519)] 36 37NS = ('xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" ' 38 'xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"') 39rows = [] 40for t, _ in CASES: 41 p = "<text:p>%s</text:p>" % t if t else "" 42 rows.append('<table:table-row><table:table-cell office:value-type="error" office:string-value="%s">%s</table:table-cell>' 43 '<table:table-cell office:value-type="error">%s</table:table-cell></table:table-row>' % (t, p, p)) 44rows.append('<table:table-row><table:table-cell office:value-type="error" office:string-value="#DIV/0!" table:number-columns-repeated="3"/></table:table-row>') 45rows.append('<table:table-row><table:table-cell office:value-type="string" office:string-value="#DIV/0!"><text:p>#DIV/0!</text:p></table:table-cell></table:table-row>') 46rows.append('<table:table-row><table:table-cell office:value-type="error"><text:p><text:span>#DIV/</text:span><text:span>0!</text:span></text:p></table:table-cell></table:table-row>') 47rows.append('<table:table-row><table:table-cell office:value-type="error"><text:p> #DIV/0!\n </text:p></table:table-cell></table:table-row>') 48sort_rows = ('<table:table-row><table:table-cell office:value-type="float" office:value="2"><text:p>2</text:p></table:table-cell>' 49 '<table:table-cell office:value-type="error" office:string-value="#DIV/0!"><text:p>#DIV/0!</text:p></table:table-cell></table:table-row>' 50 '<table:table-row><table:table-cell office:value-type="float" office:value="1"><text:p>1</text:p></table:table-cell>' 51 '<table:table-cell office:value-type="error" office:string-value="#N/A"><text:p>#N/A</text:p></table:table-cell></table:table-row>') 52content = ('<?xml version="1.0" encoding="UTF-8"?><office:document-content %s office:version="1.4"><office:body><office:spreadsheet>' 53 '<table:table table:name="Sheet1"><table:table-column table:number-columns-repeated="3"/>%s</table:table>' 54 '<table:table table:name="Sort"><table:table-column table:number-columns-repeated="2"/>%s</table:table></office:spreadsheet>' 55 '</office:body></office:document-content>') % (NS, "".join(rows), sort_rows) 56manifest = ('<?xml version="1.0" encoding="UTF-8"?><manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0" ' 57 'manifest:version="1.4"><manifest:file-entry manifest:full-path="/" manifest:version="1.4" ' 58 'manifest:media-type="application/vnd.oasis.opendocument.spreadsheet"/>' 59 '<manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/></manifest:manifest>') 60src = os.path.join(out, "error-cells.ods") 61with zipfile.ZipFile(src, "w") as z: 62 z.writestr(zipfile.ZipInfo("mimetype"), "application/vnd.oasis.opendocument.spreadsheet") 63 z.writestr("META-INF/manifest.xml", manifest, zipfile.ZIP_DEFLATED) 64 z.writestr("content.xml", content, zipfile.ZIP_DEFLATED) 65 66fails = 0 67 68 69TEXT = {521: "#NULL!", 532: "#DIV/0!", 519: "#VALUE!", 524: "#REF!", 525: "#NAME?", 503: "#NUM!", 32767: "#N/A", 504: "Err:504"} 70 71 72def check(label, doc, asstring): 73 # asstring: the saved file holds error cells as value-type="string" (no formula), so the reload is a text cell 74 global fails 75 sh = doc.Sheets.getByIndex(0) 76 77 def row(what, c, code): 78 global fails 79 if asstring: 80 ok = c.getError() == 0 and c.getString() == TEXT[code] and c.getFormula() == TEXT[code] 81 got = "error %d string %r formula %r" % (c.getError(), c.getString(), c.getFormula()) 82 else: 83 ok = c.getError() == code 84 got = "error %d" % c.getError() 85 fails += not ok 86 print("%-7s %-26s expect %-7s got %s %s" % (label, what, TEXT[code] if asstring else code, got, "ok" if ok else "FAIL")) 87 88 for i, (t, code) in enumerate(CASES): 89 for col, kind in ((0, "string-value"), (1, "text only")): 90 row("%r %s" % (t, kind), sh.getCellByPosition(col, i), code) 91 for col in range(3): 92 row("repeated col %d" % col, sh.getCellByPosition(col, len(CASES)), 532) 93 c = sh.getCellByPosition(0, len(CASES) + 1) 94 ok = c.getError() == 0 and c.getString() == "#DIV/0!" 95 fails += not ok 96 print("%-7s string control: error %d string %r %s" % (label, c.getError(), c.getString(), "ok" if ok else "FAIL")) 97 row("text in text:span", sh.getCellByPosition(0, len(CASES) + 2), 532) 98 row("text with whitespace", sh.getCellByPosition(0, len(CASES) + 3), 532) 99 100 101def check_sort(doc): 102 """Sorting by column A must take the error cells of column B along (rows 1/2: 2,#DIV/0! and 1,#N/A).""" 103 global fails 104 sh = doc.Sheets.getByName("Sort") 105 rng = sh.getCellRangeByName("A1:B2") 106 fld = uno.createUnoStruct("com.sun.star.table.TableSortField") 107 fld.Field, fld.IsAscending = 0, True 108 rng.sort((pv("SortFields", uno.Any("[]com.sun.star.table.TableSortField", (fld,))), pv("ContainsHeader", False))) 109 got = [sh.getCellByPosition(0, 0).getValue(), sh.getCellByPosition(1, 0).getError(), sh.getCellByPosition(0, 1).getValue(), sh.getCellByPosition(1, 1).getError()] 110 ok = got == [1.0, 32767, 2.0, 532] 111 fails += not ok 112 print("sort expect [1.0, 32767, 2.0, 532] got %s %s" % (got, "ok" if ok else "FAIL")) 113 114 115ctx = connect(port) 116desk = desktop(ctx) 117load = lambda p: desk.loadComponentFromURL(uno.systemPathToFileUrl(p), "_blank", 0, (pv("Hidden", True),)) 118doc = load(src) 119if doc is None: 120 sys.exit("could not load " + src) 121check("load", doc, False) 122check_sort(doc) 123# an unchanged sheet is copied verbatim in some modes, so touch it to force the real export 124doc.Sheets.getByIndex(0).getCellByPosition(5, 0).setValue(1) 125dst = os.path.join(out, "error-cells-resaved.ods") 126if os.path.exists(dst): 127 os.remove(dst) 128doc.storeToURL(uno.systemPathToFileUrl(dst), (pv("FilterName", FILTERS["ods"]), pv("Overwrite", True))) 129doc.close(True) 130doc = load(dst) 131check("reload", doc, True) 132doc.close(True) 133print("FAILS", fails) 134sys.exit(1 if fails else 0) 135