133 lines
4.4 KiB
Python
133 lines
4.4 KiB
Python
|
||
# -*- coding: utf-8 -*-
|
||
import json
|
||
import re
|
||
from openpyxl import load_workbook
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
PAT_H = re.compile(r'^\{\{([A-Za-z0-9_\.]+)\[\]\}\}$') # {{key[]}}
|
||
PAT_V = re.compile(r'^\{\{([A-Za-z0-9_\.]+)\[\]\[\]\}\}$') # {{key[][]}}
|
||
PAT_S = re.compile(r'^\{\{([A-Za-z0-9_\.]+)\}\}$') # {{key}}
|
||
|
||
def excel_col_width_to_px(width_char):
|
||
if width_char is None:
|
||
width_char = 8.43
|
||
return int(round(width_char * 7))
|
||
|
||
def excel_row_height_to_px(height_pt):
|
||
if height_pt is None:
|
||
height_pt = 15.0
|
||
return int(round(height_pt * 96 / 72))
|
||
|
||
def export_schema_from_excel(xlsx_path: str, sheet_name: str | None, out_json: str = "schema.json"):
|
||
wb = load_workbook(xlsx_path, data_only=True)
|
||
ws = wb[sheet_name] if sheet_name else wb.active
|
||
|
||
# 使用範囲
|
||
dim = ws.calculate_dimension()
|
||
from openpyxl.worksheet.dimensions import SheetDimension
|
||
dims = SheetDimension(dim)
|
||
min_col, min_row, max_col, max_row = dims.min_col, dims.min_row, dims.max_col, dims.max_row
|
||
|
||
cols_px = []
|
||
for c in range(min_col, max_col+1):
|
||
width_char = ws.column_dimensions[get_column_letter(c)].width
|
||
cols_px.append(excel_col_width_to_px(width_char))
|
||
|
||
rows_px = []
|
||
for r in range(min_row, max_row+1):
|
||
height_pt = ws.row_dimensions[r].height
|
||
rows_px.append(excel_row_height_to_px(height_pt))
|
||
|
||
# 結合セル
|
||
covered = set()
|
||
merged_top = {}
|
||
for mr in ws.merged_cells.ranges:
|
||
r0, r1 = mr.min_row, mr.max_row
|
||
c0, c1 = mr.min_col, mr.max_col
|
||
merged_top[(r0, c0)] = {"rowspan": r1 - r0 + 1, "colspan": c1 - c0 + 1}
|
||
for rr in range(r0, r1+1):
|
||
for cc in range(c0, c1+1):
|
||
if not (rr == r0 and cc == c0):
|
||
covered.add((rr, cc))
|
||
|
||
cells = []
|
||
for r in range(min_row, max_row+1):
|
||
for c in range(min_col, max_col+1):
|
||
if (r, c) in covered:
|
||
continue
|
||
cell = ws.cell(row=r, column=c)
|
||
raw = cell.value
|
||
s = str(raw) if raw is not None else ""
|
||
|
||
entry = {
|
||
"r": r - min_row + 1,
|
||
"c": c - min_col + 1,
|
||
"rowspan": 1,
|
||
"colspan": 1,
|
||
"style": {},
|
||
"type": "text", # 既定はラベル/テキスト
|
||
}
|
||
if (r, c) in merged_top:
|
||
entry["rowspan"] = merged_top[(r, c)]["rowspan"]
|
||
entry["colspan"] = merged_top[(r, c)]["colspan"]
|
||
|
||
# スタイル（簡易）
|
||
if cell.font and cell.font.bold:
|
||
entry["style"]["bold"] = True
|
||
hal = cell.alignment.horizontal
|
||
if hal:
|
||
entry["style"]["align_h"] = hal
|
||
val = cell.alignment.vertical
|
||
if val:
|
||
entry["style"]["align_v"] = val
|
||
if cell.alignment.wrap_text:
|
||
entry["style"]["wrap"] = True
|
||
|
||
# マーカー判定
|
||
m = PAT_H.match(s)
|
||
if m:
|
||
entry["type"] = "repeat_h"
|
||
entry["repeat"] = {"key": m.group(1)}
|
||
cells.append(entry)
|
||
continue
|
||
|
||
m = PAT_V.match(s)
|
||
if m:
|
||
entry["type"] = "repeat_v"
|
||
entry["repeat"] = {"key": m.group(1)}
|
||
cells.append(entry)
|
||
continue
|
||
|
||
m = PAT_S.match(s)
|
||
if m:
|
||
entry["type"] = "value"
|
||
entry["key"] = m.group(1)
|
||
entry["label"] = None
|
||
else:
|
||
entry["label"] = s # ラベル扱い
|
||
|
||
cells.append(entry)
|
||
|
||
schema = {
|
||
"page": {"size": "A4", "margin_mm": 12},
|
||
"cols": cols_px,
|
||
"rows": rows_px,
|
||
"cells": cells,
|
||
# リピータの既定動作（必要に応じて上書き可能）
|
||
"repeat_defaults": {
|
||
"gap_px": 0, # 項目間の隙間（横/縦）
|
||
"border": None, # 項目間の区切り線
|
||
"item_wrap": True, # 項目内の折り返し
|
||
"max_cols": None # 横方向の最大列数（Noneなら均等に1行で詰める）
|
||
}
|
||
}
|
||
|
||
with open(out_json, "w", encoding="utf-8") as f:
|
||
json.dump(schema, f, ensure_ascii=False, indent=2)
|
||
|
||
print(f"書き出し: {out_json}")
|
||
|
||
if __name__ == "__main__":
|
||
export_schema_from_excel("form.xlsx", sheet_name="A4_帳票", out_json="schema.json")
|