Repository Public
1"""Data sheets shared by the LibreOffice fixtures (sheet "D" and the hidden-row sheet "H")."""234def col(letter, values, start=1):5 return {f"{letter}{start + i}": v for i, v in enumerate(values)}678X = [3, 7.5, -2, 10, 4.25, 8, 15, 1, 6, 12.5, 9, 4]9Y = [2.1, 5.9, -1.5, 8.2, 3.3, 7.7, 12.9, 0.4, 5.1, 10.8, 7.6, 3.9]10# no booleans: LibreOffice has no logical cell type (TRUE is the number 1 there), so it cannot serve as a11# reference for logical values inside ranges; those are covered by hand-written tests.12MIXED = [5, "text", 2.5, None, 12, "7", 0, 3.5, None, -4, "abc", 5]13FRUIT = ["apple", "banana", "apple", "cherry", "Banana", "apple pie", "cherry", "APPLE", "date", "banana", "apple", "fig"]14DUPS = [1, 2, 2, 3, 3, 3, 4, 5, 5, 7, 9, 9]15POS = [0.5, 1.2, 2.8, 3.1, 4.7, 5.0, 6.3, 7.9, 8.4, 9.6, 10.2, 11.5]16X2 = [1, 4, 2, 8, 5, 7, 1, 9, 3, 6, 2, 8]17GROW = [1.2, 1.9, 3.1, 4.8, 8.1, 12.5, 20.3, 33.0, 54.1, 90.2, 148.0, 245.1]18PROB = [0.1, 0.2, 0.3, 0.25, 0.15]19PX = [1, 2, 3, 4, 5]20DATES = [45292, 45323, 45352, 45383, 45413, 45444] # first of month, Jan–Jun 202421WILD = ["a*b", "a?b", "axb", "a~b", "AXXB", "ab", None, "5", 5, "x*", "50%", "10"]2223DB = [24 ["Name", "Dept", "Age", "Salary", "Bonus"],25 ["Alice", "Sales", 34, 52000, 1500],26 ["Bob", "Engineering", 45, 87000, None],27 ["Carla", "Sales", 29, 48000, 900],28 ["David", "Engineering", 38, 91000, 4000],29 ["Dave", "Marketing", 51, 64000, 1200],30 ["Eve", "Sales", 41, 58500, "n/a"],31 ["Frank", "Marketing", 26, 39000, 300],32 ["Grace", "Engineering", 33, 78000, 2500],33]343536def data_sheet():37 cells = {}38 cells.update(col("A", X))39 cells.update(col("B", Y))40 cells.update(col("C", MIXED))41 cells.update(col("D", FRUIT))42 cells.update(col("E", DUPS))43 cells.update(col("F", POS))44 for r, row in enumerate([[2, 1, 0], [1, 3, 1], [0, 1, 4]]):45 for c, v in enumerate(row):46 cells[f"{'GHI'[c]}{r + 1}"] = v47 for r, row in enumerate([[1, 2, 3], [4, 5, 6], [7, 8, 10]]):48 for c, v in enumerate(row):49 cells[f"{'GHI'[c]}{r + 5}"] = v50 for r, row in enumerate([[1, 2], [3, 4], [5, 6]]):51 for c, v in enumerate(row):52 cells[f"{'GH'[c]}{r + 9}"] = v53 cells.update(col("K", X2))54 cells.update(col("L", GROW))55 cells.update(col("M", PROB))56 cells.update(col("N", PX))57 cells.update(col("O", WILD))58 for r, row in enumerate(DB):59 for c, v in enumerate(row):60 cells[f"{'PQRST'[c]}{r + 1}"] = v61 # criteria ranges62 crit = {63 "V1": "Dept", "V2": "Sales",64 "W1": "Dept", "X1": "Age", "W2": "Engineering", "X2": ">35",65 "Y1": "Dept", "Z1": "Salary", "Y2": "Sales", "Z2": ">50000", "Y3": "Marketing", "Z3": None,66 "AA1": "Age", "AB1": "Age", "AA2": ">=30", "AB2": "<45",67 "AC1": "Name", "AC2": "Grace",68 "AD1": "Salary", "AD2": 91000,69 "AE1": "Dept", "AE2": "<>Sales",70 "AF1": "Name", "AF2": "Zed",71 }72 cells.update(crit)73 cells.update(col("AH", DATES))74 cells.update(col("AI", [100, 200, 300, 400, 500, 600]))75 # contingency tables for CHISQ.TEST76 for r, row in enumerate([[58, 35], [11, 25], [10, 23]]):77 for c, v in enumerate(row):78 cells[f"{['AK', 'AL'][c]}{r + 1}"] = v79 for r, row in enumerate([[45.35, 47.65], [17.56, 18.44], [16.09, 16.91]]):80 for c, v in enumerate(row):81 cells[f"{['AM', 'AN'][c]}{r + 1}"] = v82 return {"name": "D", "cells": cells}838485def hidden_sheet():86 cells = col("A", [10, 20, 30, 40, 50, 60, 70, 80, 90, 100])87 cells.update(col("B", [1, 2, "x", 4, 5, 6, 7, 8, 9, 10]))88 return {"name": "H", "cells": cells, "formulas": {"A11": "=SUBTOTAL(9,A1:A10)", "C1": "=1/0", "C2": "=5", "C3": "=7"},89 "hiddenRows": [3, 4, 7]}90