import json import os from pathlib import Path import subprocess import sys import unittest from lxml import etree as ET sys.path.insert(0, str(Path(__file__).resolve().parents[1])) import test_expense_template as fixtures from expense_template import Template, encoded, export_custom_expense, inspect_template, set_cell, tag from expense_preview import prepare_preview from spreadsheet_layout import formatted, read_layout class SpreadsheetLayoutTests(unittest.TestCase): def setUp(self): self.fixture = fixtures.ExpenseTemplateTests() self.fixture.setUp() self.addCleanup(self.fixture.tearDown) def test_wps_signature_and_accounting_regressions(self): source = Path(os.environ.get('RECEIPT_PREVIEW_SOURCE', self.fixture.template)) original = source.read_bytes() mapping = inspect_template(source)['sheets'][0]['mapping'] output = self.fixture.root / 'preview.xlsx' export_custom_expense(dict(matches=[self.fixture.match('changed', '交通', '447')]), output, source, mapping, {}, ['部门长', '剧组出纳', '制片主任', '剧组会计', '执行制片人'], dict(recipient='测试收款人', bankName='测试银行', accountNumber='001234567890'), {}) result = prepare_preview(output) before = output.read_bytes() layout = read_layout(output) self.assertEqual(output.read_bytes(), before) self.assertEqual(source.read_bytes(), original) self.assertEqual(layout, result['previewLayout']) self.assertNotIn('templatePreviewLayout', result) cells = {c['reference']: c for c in layout['sheets'][0]['cells']} for ref, label in [('A30', '部门长'), ('D30', '剧组出纳'), ('A31', '制片主任'), ('D31', '剧组会计')]: self.assertEqual(cells[ref]['text'], label + '：') self.assertGreater(cells[ref]['overflowRight'], cells[ref]['x'] + cells[ref]['width']) self.assertEqual(cells['A30']['overflowRight'], cells['D30']['x']) self.assertEqual(cells['H30']['style']['rotation'], 255) self.assertEqual(cells['H30']['style']['vertical'], 'bottom') self.assertEqual(cells['H30']['style']['borders']['right']['style'], 'medium') self.assertEqual(cells['H30']['style']['borders']['bottom']['style'], 'medium') for ref in ['F27', 'H29']: self.assertTrue(cells[ref]['style']['accounting']) self.assertEqual(cells[ref]['text'].strip(), '447.00') self.assertTrue(cells[ref]['style']['bold']) self.assertNotIn('left', cells['D31']['style']['borders']) self.assertEqual(cells['C25']['text'], '001234567890') if os.environ.get('RECEIPT_PREVIEW_FIXTURE_DIR'): folder = Path(os.environ['RECEIPT_PREVIEW_FIXTURE_DIR']) folder.mkdir(parents=True, exist_ok=True) (folder / 'layout.json').write_text(json.dumps(layout, ensure_ascii=False), encoding='utf-8') (folder / 'preview.xlsx').write_bytes(before) def test_overflow_stops_at_text_and_merged_cells(self): output, _ = self.fixture.export() template = Template(output) _, path, document = template.sheet('个人报销单') set_cell(document, 'B30', '阻止覆盖') template.parts[path] = encoded(document) self.fixture.rewrite(output, template.parts) cells = {c['reference']: c for c in read_layout(output)['sheets'][0]['cells']} self.assertEqual(cells['A30']['overflowRight'], cells['A30']['x'] + cells['A30']['width']) self.assertEqual(cells['D30']['overflowRight'], cells['H30']['x']) self.assertEqual(cells['A32']['overflowRight'], cells['A32']['x'] + cells['A32']['width']) def test_visible_sheets_and_pagination(self): source, mapping = self.fixture.alternate() state = dict(matches=self.fixture.state['matches'] + [self.fixture.match('extra', '住宿', '50')]) output, _ = self.fixture.export(mapping=mapping, template=source, state=state) layout = prepare_preview(output)['previewLayout'] self.assertEqual([s['name'] for s in layout['sheets']], ['组 B 报销', '保留说明', '组 B 报销-续2']) def test_unrelated_template_style_and_geometry(self): from openpyxl import Workbook from openpyxl.styles import Alignment, Border, Font, PatternFill, Side workbook = Workbook() sheet = workbook.active sheet.title = '横向费用单' sheet.column_dimensions['A'].width = 4 sheet.column_dimensions['B'].width = 24 sheet.column_dimensions['C'].width = 12 sheet.column_dimensions['D'].width = 20 sheet.column_dimensions['E'].width = 16 sheet.column_dimensions['F'].width = 10 sheet.row_dimensions[2].height = 42 sheet.row_dimensions[6].height = 34 sheet.merge_cells('B2:F2') sheet['B2'] = '另一种费用模板' sheet['B2'].font = Font(name='Arial', size=18, bold=True, color='145A32') sheet['B2'].fill = PatternFill('solid', fgColor='D5F5E3') sheet['B2'].alignment = Alignment(horizontal='center', vertical='center') sheet['B2'].border = Border(bottom=Side(style='double', color='145A32')) for ref, text in [('B5', '说明'), ('D5', '金额'), ('E5', '比例'), ('F5', '编号')]: sheet[ref] = text sheet.merge_cells('B6:C6') sheet['B6'] = '这里是长一些的费用说明，需要按模板换行' sheet['B6'].alignment = Alignment(wrap_text=True, vertical='top') sheet['D6'] = 1234.5 sheet['D6'].number_format = '#,##0.00' sheet['D6'].alignment = Alignment(horizontal='right') sheet['E6'] = .125 sheet['E6'].number_format = '0.0%' sheet['F6'] = '000012' sheet.print_area = 'A1:F8' workbook.create_sheet('说明页')['A1'] = '附加工作表' output = self.fixture.root / 'different-style.xlsx' workbook.save(output) before = output.read_bytes() layout = read_layout(output) self.assertEqual(output.read_bytes(), before) self.assertEqual([s['name'] for s in layout['sheets']], ['横向费用单', '说明页']) cells = {c['reference']: c for c in layout['sheets'][0]['cells']} self.assertNotIn('C2', cells) self.assertNotIn('C6', cells) self.assertEqual(cells['B2']['height'], 56) self.assertEqual(cells['B2']['style']['font'], 'Arial') self.assertEqual(cells['B2']['style']['fill'], 'D5F5E3') self.assertEqual(cells['B2']['style']['borders']['bottom']['style'], 'double') self.assertTrue(cells['B6']['style']['wrap']) self.assertEqual(cells['D6']['text'], '1,234.50') self.assertEqual(cells['D6']['style']['horizontal'], 'right') self.assertEqual(cells['E6']['text'], '12.5%') self.assertEqual(cells['F6']['text'], '000012') if os.environ.get('RECEIPT_PREVIEW_FIXTURE_DIR'): folder = Path(os.environ['RECEIPT_PREVIEW_FIXTURE_DIR']) folder.mkdir(parents=True, exist_ok=True) (folder / 'alternate-layout.json').write_text(json.dumps(layout, ensure_ascii=False), encoding='utf-8') def test_hidden_dimensions_and_outside_print_area(self): output, _ = self.fixture.export() template = Template(output) _, path, document = template.sheet('个人报销单') document.find(tag('cols'))[0].set('hidden', '1') document.find(tag('sheetData'))[0].set('hidden', '1') set_cell(document, 'J36', '打印范围外') template.parts[path] = encoded(document) self.fixture.rewrite(output, template.parts) cells = {c['reference']: c for c in read_layout(output)['sheets'][0]['cells']} self.assertNotIn('A9', cells) self.assertEqual(cells['B9']['x'], 0) self.assertIn('J36', cells) def test_large_range_rejected(self): output, _ = self.fixture.export() template = Template(output) _, path, document = template.sheet('个人报销单') set_cell(document, 'IV2000', 'range') template.parts[path] = encoded(document) self.fixture.rewrite(output, template.parts) with self.assertRaisesRegex(ValueError, '范围过大'): read_layout(output) def test_advanced_features_warn(self): output, _ = self.fixture.export() template = Template(output) _, path, document = template.sheet('个人报销单') ET.SubElement(document, tag('conditionalFormatting'), sqref='H9') template.parts[path] = encoded(document) self.fixture.rewrite(output, template.parts) self.assertTrue(read_layout(output)['warnings']) def test_numeric_and_date_formatting(self): warnings = set() for value, code, expected in [('447', '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)', '447.00'), ('2122.6', '#,##0.00', '2,122.60'), ('-25.3', '0.00;(0.00)', '(25.30)'), ('12.50', '0.##', '12.5'), ('12', '0.0#', '12.0'), ('8', '0000', '0008'), ('0.125', '0.0%', '12.5%')]: self.assertEqual(formatted(value, 'n', code, False, warnings).strip(), expected) self.assertEqual(formatted('1', 'n', 'yyyy/m/d', True, warnings), '1904/1/2') self.assertFalse(warnings) def test_sandbox_import(self): script = """ import sys def audit(event, args): if event == 'open' and str(args[0]).endswith('mime.types'): raise PermissionError('System MIME database is unavailable') sys.addaudithook(audit) import spreadsheet_layout """ result = subprocess.run([sys.executable, '-c', script], cwd=Path(__file__).resolve().parents[1], capture_output=True, text=True) self.assertEqual(result.returncode, 0, result.stderr) if __name__ == '__main__': unittest.main()