"""Read-only workbook display model; never modify the exported workbook.""" import math import mimetypes import re from decimal import Decimal, ROUND_HALF_UP # openpyxl otherwise reads system MIME files unavailable in the app sandbox. mimetypes.knownfiles = [] mimetypes.init() from openpyxl.styles.colors import COLOR_INDEX from openpyxl.styles.numbers import BUILTIN_FORMATS, is_date_format from openpyxl.utils.datetime import from_excel, MAC_EPOCH, WINDOWS_EPOCH from expense_template import Template, column_name, coordinate, tag, xml MAX_CELLS = 50000 DRAWING = 'http://schemas.openxmlformats.org/drawingml/2006/main' def flag(node, key, default=False): return node.get(key, '1' if default else '0') in ('1', 'true') def child_value(node, name, default): child = node.find(tag(name)) return child.get('val', default) if child is not None else default def theme_colors(template): defaults = ['FFFFFF', '000000', 'EEECE1', '1F497D', '4F81BD', 'C0504D', '9BBB59', '8064A2', '4BACC6', 'F79646', '0000FF', '800080'] if 'xl/theme/theme1.xml' not in template.parts: return defaults scheme = xml(template.parts['xl/theme/theme1.xml']).find('.//{' + DRAWING + '}clrScheme') if scheme is None: return defaults colors = {entry.tag.split('}')[-1]: entry[0].get('lastClr', entry[0].get('val', '000000')) for entry in scheme if len(entry)} return [colors.get(name, defaults[i]) for i, name in enumerate( ['lt1', 'dk1', 'lt2', 'dk2', 'accent1', 'accent2', 'accent3', 'accent4', 'accent5', 'accent6', 'hlink', 'folHlink'])] def color(node, palette, default='000000'): if node is None: return default value = node.get('rgb') if value is None and 'theme' in node.attrib: value = palette[int(node.get('theme')) % len(palette)] if value is None and 'indexed' in node.attrib: index = int(node.get('indexed')) value = COLOR_INDEX[index] if 0 <= index < len(COLOR_INDEX) else default value = (value or default)[-6:] if not re.fullmatch('[0-9a-fA-F]{6}', value): return default tint = max(-1, min(1, float(node.get('tint', 0)))) return ''.join(f'{round(c * (1 + tint) if tint < 0 else c * (1 - tint) + 255 * tint):02X}' for c in (int(value[i:i + 2], 16) for i in (0, 2, 4))) def literal(value): return re.sub(r'"([^"]*)"|\\(.)|_.|\*.', lambda m: m[1] or m[2] or '', value) def number_section(value, code): sections = re.split(r';(?=(?:[^"]*"[^"]*")*[^"]*$)', code) return (sections[2] if value == 0 and len(sections) > 2 else sections[1] if value < 0 and len(sections) > 1 else sections[0]) def formatted(value, kind, code, date_1904, warnings): if not value or kind in ('s', 'inlineStr', 'str', 'e'): return value if kind == 'b': return 'TRUE' if value == '1' else 'FALSE' number = Decimal(value) if is_date_format(code): date = from_excel(float(number), MAC_EPOCH if date_1904 else WINDOWS_EPOCH) section = literal(re.sub(r'\[[^\]]*\]', '', code.split(';')[0])).lower() if section == 'mm-dd-yy': return date.strftime('%m/%d/%y') if re.fullmatch(r'yyyy([/.-])m{1,2}\1d{1,2}', section): return (str(date.year) + section[4] + (f'{date.month:02d}' if 'mm' in section else str(date.month)) + section[4] + (f'{date.day:02d}' if 'dd' in section else str(date.day))) if section == 'yyyy年m月d日': return f'{date.year}年{date.month}月{date.day}日' if section in ('h:mm', 'hh:mm', 'h:mm:ss', 'hh:mm:ss'): return date.strftime('%H:%M:%S' if 'ss' in section else '%H:%M') warnings.add('特殊日期格式请在办公软件中核对') return date.date().isoformat() if hasattr(date, 'date') else date.isoformat() if code.lower() in ('general', '@'): return format(number, 'f').rstrip('0').rstrip('.') if '.' in str(number) else str(number) section = number_section(number, code) section = re.sub(r'\[\$([^\]-]*)[^\]]*\]', lambda m: m[1], section) if re.search(r'\[(?:[<>=]|\d)', section): warnings.add('条件数字格式请在办公软件中核对') return str(number) section = re.sub(r'\[[^\]]*\]', '', section) masked = re.sub(r'"[^"]*"|\\.|_.|\*.', lambda m: ' ' * len(m[0]), section) match = re.search(r'[#0][#0,]*(?:\.[#0?]+)?%?', masked) if not match: return literal(section).strip().replace('?', '') if re.search(r'[Ee][+-]|[/]', masked) or masked[match.end():].lstrip().startswith(','): warnings.add('特殊数字格式请在办公软件中核对') return str(number) pattern = match[0] fraction = pattern.rstrip('%').split('.')[1] if '.' in pattern else '' number = abs(number) if number < 0 and len(code.split(';')) > 1 else number if '%' in pattern: number *= 100 number = number.quantize(Decimal(1).scaleb(-len(fraction)), rounding=ROUND_HALF_UP) text = format(number, (',' if ',' in pattern else '') + f'.{len(fraction)}f') for _ in range(len(fraction) - len(fraction.rstrip('#?'))): if text.endswith('0'): text = text[:-1] if fraction: text = text.rstrip('.') if ',' not in pattern and '.' not in pattern: text = text.zfill(pattern.count('0')) return literal(section[:match.start()]) + text + ('%' if '%' in pattern else '') + literal(section[match.end():]) def read_styles(template, palette): root = xml(template.parts['xl/styles.xml']) fonts, fills, borders = (root.find(tag(name)) for name in ('fonts', 'fills', 'borders')) formats = dict(BUILTIN_FORMATS) for item in root.findall(tag('numFmts') + '/' + tag('numFmt')): formats[int(item.get('numFmtId'))] = item.get('formatCode') styles = [] for entry in root.find(tag('cellXfs')): font = fonts[int(entry.get('fontId', 0))] fill = fills[int(entry.get('fillId', 0))].find(tag('patternFill')) border = borders[int(entry.get('borderId', 0))] alignment = entry.find(tag('alignment')) if alignment is None: alignment = {} sides = {} for edge in ('left', 'right', 'top', 'bottom'): side = border.find(tag(edge)) if side is not None and side.get('style'): sides[edge] = dict(style=side.get('style'), color=color(side.find(tag('color')), palette)) styles.append(dict( font=child_value(font, 'name', 'Calibri'), fontSize=float(child_value(font, 'sz', '11')), bold=font.find(tag('b')) is not None and flag(font.find(tag('b')), 'val', True), italic=font.find(tag('i')) is not None and flag(font.find(tag('i')), 'val', True), underline=font.find(tag('u')) is not None and child_value(font, 'u', 'single') != 'none', strike=font.find(tag('strike')) is not None and flag(font.find(tag('strike')), 'val', True), color=color(font.find(tag('color')), palette), fill=color(fill.find(tag('fgColor')), palette, 'FFFFFF') if fill is not None and fill.get('patternType') == 'solid' else 'FFFFFF', horizontal=alignment.get('horizontal', 'general'), vertical=alignment.get('vertical', 'bottom'), wrap=alignment.get('wrapText') in ('1', 'true'), shrink=alignment.get('shrinkToFit') in ('1', 'true'), rotation=int(alignment.get('textRotation', 0)), indent=float(alignment.get('indent', 0)), borders=sides, numberFormat=formats.get(int(entry.get('numFmtId', 0)), 'General'), accounting=False)) return styles def read_layout(path): template = Template(path) styles = read_styles(template, theme_colors(template)) properties = template.workbook.find(tag('workbookPr')) date_1904 = properties is not None and flag(properties, 'date1904') sheets, warnings, total_cells = [], set(), 0 for sheet_index, sheet in enumerate(template.sheets): if sheet.get('state', 'visible') != 'visible': continue name = sheet.get('name') _, _, document = template.sheet(name) cells = {c.get('r'): c for c in document.iter(tag('c'))} merges = [m.get('ref') for m in document.iter(tag('mergeCell'))] references = [ref for ref, c in cells.items() if any(c.find(tag(k)) is not None for k in ('v', 'is', 'f'))] for definition in template.workbook.findall(tag('definedNames') + '/' + tag('definedName')): if definition.get('name') == '_xlnm.Print_Area' and definition.get('localSheetId') == str(sheet_index): references += [c + r for _, _, c, r in re.findall( r'\$?([A-Z]+)\$?(\d+):\$?([A-Z]+)\$?(\d+)', definition.text or '')] references += [m.split(':')[-1] for m in merges] positions = [coordinate(ref) for ref in references or list(cells) or ['A1']] columns, rows = max(p[0] for p in positions), max(p[1] for p in positions) total_cells += columns * rows if total_cells > MAX_CELLS: raise ValueError('模板范围过大，无法完整预览；请精简模板后重试') defaults = document.find(tag('sheetFormatPr')) dw = float(defaults.get('defaultColWidth', 8.43)) if defaults is not None else 8.43 dh = float(defaults.get('defaultRowHeight', 15)) if defaults is not None else 15 widths, heights = [dw] * columns, [dh] * rows column_styles, row_styles = {}, {} for column in document.findall(tag('cols') + '/' + tag('col')): for index in range(max(0, int(column.get('min')) - 1), min(columns, int(column.get('max')))): widths[index] = 0 if flag(column, 'hidden') else float(column.get('width', dw)) column_styles[index + 1] = int(column.get('style', 0)) for row in document.findall(tag('sheetData') + '/' + tag('row')): index = int(row.get('r')) - 1 if 0 <= index < rows: heights[index] = 0 if flag(row, 'hidden') else float(row.get('ht', dh)) if flag(row, 'customFormat'): row_styles[index + 1] = int(row.get('s', 0)) xs, ys = [0], [0] for width in widths: xs.append(xs[-1] + math.floor(((256 * width + 18) / 256) * 7)) for height in heights: ys.append(ys[-1] + height * 4 / 3) covered, spans = set(), {} for merged in merges: first, last = merged.split(':') left, top = coordinate(first) right, bottom = coordinate(last) spans[first] = (right, bottom) covered.update((c, r) for c in range(left, right + 1) for r in range(top, bottom + 1) if (c, r) != (left, top)) def style_at(ref): col, row = coordinate(ref) cell = cells.get(ref) return styles[int(cell.get('s', row_styles.get(row, column_styles.get(col, 0)))) if cell is not None else row_styles.get(row, column_styles.get(col, 0))] occupied = {coordinate(ref) for ref, c in cells.items() if any(c.find(tag(k)) is not None for k in ('v', 'is', 'f'))} occupied.update(covered) occupied.update(coordinate(ref) for ref in spans) output = [] for reference, cell in cells.items(): col, row = coordinate(reference) if col > columns or row > rows or (col, row) in covered: continue right, bottom = spans.get(reference, (col, row)) appearance = dict(style_at(reference)) appearance['borders'] = dict(appearance['borders']) if reference in spans: # Use the perimeter, not the anchor's former internal edges. for edge, edge_ref in [('right', column_name(right) + str(row)), ('bottom', column_name(col) + str(bottom)), ('right', column_name(right) + str(bottom)), ('bottom', column_name(right) + str(bottom))]: candidate = style_at(edge_ref)['borders'].get(edge) if candidate: appearance['borders'][edge] = candidate kind = cell.get('t', 'n') value = cell.findtext(tag('v'), '') if kind == 's': value = template.shared[int(value)] if value else '' elif kind == 'inlineStr': value = ''.join(t.text or '' for t in cell.iter(tag('t'))) text = formatted(value, kind, appearance['numberFormat'], date_1904, warnings) if kind == 'n' and value: section = number_section(Decimal(value), appearance['numberFormat']) # Asterisk-space pads the value to the right even when the cell # alignment is centered. Do not discard that layout instruction. masked = re.sub(r'"[^"]*"|\\.', '', section) appearance['accounting'] = '* ' in masked if appearance['horizontal'] == 'general': appearance['horizontal'] = 'right' if kind == 'n' else 'center' if kind == 'b' else 'left' width, height = xs[right] - xs[col - 1], ys[bottom] - ys[row - 1] if width <= 0 or height <= 0: continue left_limit, right_limit = col, right if (text and kind in ('s', 'inlineStr', 'str') and reference not in spans and not appearance['wrap'] and not appearance['shrink'] and appearance['rotation'] == 0): if appearance['horizontal'] in ('left', 'center'): while right_limit < columns and (right_limit + 1, row) not in occupied: right_limit += 1 if appearance['horizontal'] in ('right', 'center'): while left_limit > 1 and (left_limit - 1, row) not in occupied: left_limit -= 1 output.append(dict(reference=reference, x=xs[col - 1], y=ys[row - 1], width=width, height=height, text=text, style=appearance, overflowLeft=xs[left_limit - 1], overflowRight=xs[right_limit])) for feature in ('drawing', 'legacyDrawing', 'conditionalFormatting'): if document.find(tag(feature)) is not None: warnings.add('模板中的图片、图表或条件格式请在 Excel / WPS 中核对') sheets.append(dict(name=name, width=max(1, xs[-1]), height=max(1, ys[-1]), cells=output)) return dict(version=4, sheets=sheets, warnings=sorted(warnings))