import argparse
import datetime as _dt
import os
import re
from typing import Any, Iterable, Optional
from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell
from openpyxl.utils import get_column_letter
pause = 0
def terminate():
if pause:
input("\nPress ENTER to terminate\n")
exit()
def initialize():
parser = argparse.ArgumentParser(
description="Excelファイルから値、数式、コメント、図・グラフ情報などを抽出し、Markdownに出力します。"
)
parser.add_argument("-i", "--input", required=True, help="入力するExcelファイル名 (.xlsx/.xlsm)")
parser.add_argument("-o", "--output", required=True, help="出力するMarkdownファイル名")
parser.add_argument("--imagedir", default="images", help="画像ディレクトリ")
parser.add_argument("--max-rows", type=int, default=200, help="値表として出力する最大行数（--all指定時は無視）")
parser.add_argument("--max-cols", type=int, default=60, help="値表として出力する最大列数（--all指定時は無視）")
parser.add_argument("--max-formulas", type=int, default=2000, help="数式表として出力する最大数式数（--all指定時は無視）")
parser.add_argument("--all", action="store_true", help="行数・列数・数式数を制限せず、全範囲を出力します。")
parser.add_argument("--no-values", action="store_true", help="値のMarkdown表を出力しません。")
parser.add_argument("--no-formulas", action="store_true", help="数式のMarkdown表を出力しません。")
parser.add_argument("--no-metadata", action="store_true", help="結合セル、名前付き範囲、テーブル等のメタ情報を出力しません。")
parser.add_argument("--no-comments", action="store_true", help="セルコメントを出力しません。")
parser.add_argument("--no-charts", action="store_true", help="グラフ情報を出力しません。")
parser.add_argument("--no-images", action="store_true", help="埋め込み画像を抽出しません。")
parser.add_argument("--date-format", default="%Y-%m-%d %H:%M:%S", help="日時セルの出力形式")
parser.add_argument("--encoding", default="utf-8", help="Markdown出力の文字コード")
parser.add_argument("--pause", type=int, default=0, help="終了時待機")
args = parser.parse_args()
return args
# -----------------------------
# Markdown / text helpers
# -----------------------------
def md_escape(value: Any) -> str:
"""Markdown table cell用に最低限エスケープする。"""
if value is None:
return ""
s = str(value)
s = s.replace("\r\n", "
").replace("\n", "
").replace("\r", "
")
s = s.replace("|", "\\|")
return s
def code_escape(value: Any) -> str:
"""Markdown inline code用。数式のバッククォート崩れを防ぐ。"""
if value is None:
return ""
s = str(value).replace("`", "\\`")
return f"`{s}`"
def format_value(value: Any, date_format: str) -> str:
"""Excelセル値を生成AIに読みやすいテキストへ変換する。"""
if value is None:
return ""
if isinstance(value, _dt.datetime):
return value.strftime(date_format)
if isinstance(value, _dt.date):
return value.isoformat()
if isinstance(value, _dt.time):
return value.isoformat()
if isinstance(value, float):
# 生成AI向けには、過剰な丸めよりも再現性を優先する。
return format(value, ".15g")
return str(value)
def markdown_table(headers: Iterable[str], rows: Iterable[Iterable[Any]]) -> str:
headers = list(headers)
out = []
out.append("| " + " | ".join(md_escape(h) for h in headers) + " |")
out.append("|" + "|".join(["---"] * len(headers)) + "|")
for row in rows:
row = list(row)
if len(row) < len(headers):
row += [""] * (len(headers) - len(row))
out.append("| " + " | ".join(md_escape(v) for v in row[: len(headers)]) + " |")
return "\n".join(out) + "\n\n"
def safe_filename(name: str) -> str:
name = re.sub(r"[\\/:*?\"<>|\s]+", "_", name.strip())
name = re.sub(r"_+", "_", name).strip("_")
return name or "sheet"
# -----------------------------
# Workbook structure helpers
# -----------------------------
def cell_has_content(cell) -> bool:
if cell is None or isinstance(cell, MergedCell):
return False
return (
cell.value is not None
or cell.comment is not None
or cell.hyperlink is not None
)
def find_used_bounds(ws_formula, ws_values):
"""値・数式・コメント・ハイパーリンクから実質的なUsedRangeを推定する。"""
keys = set(getattr(ws_formula, "_cells", {}).keys()) | set(getattr(ws_values, "_cells", {}).keys())
min_row = min_col = None
max_row = max_col = None
for row, col in keys:
cf = ws_formula.cell(row=row, column=col)
cv = ws_values.cell(row=row, column=col)
if cell_has_content(cf) or cell_has_content(cv):
min_row = row if min_row is None else min(min_row, row)
max_row = row if max_row is None else max(max_row, row)
min_col = col if min_col is None else min(min_col, col)
max_col = col if max_col is None else max(max_col, col)
for merged in ws_formula.merged_cells.ranges:
min_row = merged.min_row if min_row is None else min(min_row, merged.min_row)
max_row = merged.max_row if max_row is None else max(max_row, merged.max_row)
min_col = merged.min_col if min_col is None else min(min_col, merged.min_col)
max_col = merged.max_col if max_col is None else max(max_col, merged.max_col)
if min_row is None:
return 1, 1, 1, 1, False
return min_row, min_col, max_row, max_col, True
def limited_indices(start: int, end: int, max_count: int, output_all: bool):
values = list(range(start, end + 1))
if output_all or max_count <= 0 or len(values) <= max_count:
return values, False
return values[:max_count], True
def get_cell_pair(ws_formula, ws_values, row: int, col: int):
return ws_formula.cell(row=row, column=col), ws_values.cell(row=row, column=col)
def get_cached_value(ws_values, row: int, col: int, date_format: str) -> str:
return format_value(ws_values.cell(row=row, column=col).value, date_format)
# -----------------------------
# Extraction: values / formulas
# -----------------------------
def values_table_to_markdown(ws_formula, ws_values, bounds, args) -> str:
min_row, min_col, max_row, max_col, has_content = bounds
if not has_content:
return "（値なし）\n\n"
rows, row_truncated = limited_indices(min_row, max_row, args.max_rows, args.all)
cols, col_truncated = limited_indices(min_col, max_col, args.max_cols, args.all)
headers = ["row"] + [get_column_letter(c) for c in cols]
table_rows = []
for r in rows:
one = [r]
for c in cols:
one.append(get_cached_value(ws_values, r, c, args.date_format))
table_rows.append(one)
msg = ""
total_rows = max_row - min_row + 1
total_cols = max_col - min_col + 1
if row_truncated or col_truncated:
msg += (
f"> 出力を省略しています。Used range は "
f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row} "
f"（{total_rows} rows × {total_cols} cols）です。"
)
if row_truncated:
msg += f" 行は先頭 {len(rows)} 行のみ出力。"
if col_truncated:
msg += f" 列は先頭 {len(cols)} 列のみ出力。"
msg += " 全出力するには `--all` を指定してください。\n\n"
return msg + markdown_table(headers, table_rows)
def iter_formula_cells(ws_formula, ws_values, bounds):
min_row, min_col, max_row, max_col, has_content = bounds
if not has_content:
return
for row in range(min_row, max_row + 1):
for col in range(min_col, max_col + 1):
cf, cv = get_cell_pair(ws_formula, ws_values, row, col)
value = cf.value
if isinstance(value, str) and value.startswith("="):
yield cf, cv
def formulas_to_markdown(ws_formula, ws_values, bounds, args) -> str:
formulas = []
truncated = False
for idx, (cf, cv) in enumerate(iter_formula_cells(ws_formula, ws_values, bounds), start=1):
if (not args.all) and idx > args.max_formulas:
truncated = True
break
formulas.append(
[
cf.coordinate,
code_escape(cf.value),
format_value(cv.value, args.date_format),
cf.number_format,
]
)
if not formulas:
return "（数式なし）\n\n"
msg = ""
if truncated:
msg += f"> 数式数が多いため、先頭 {len(formulas)} 件のみ出力しています。全出力するには `--all` を指定してください。\n\n"
return msg + markdown_table(["cell", "formula", "cached value", "number format"], formulas)
# -----------------------------
# Extraction: metadata
# -----------------------------
def comments_to_markdown(ws_formula, bounds) -> str:
min_row, min_col, max_row, max_col, has_content = bounds
if not has_content:
return ""
rows = []
for row in range(min_row, max_row + 1):
for col in range(min_col, max_col + 1):
cell = ws_formula.cell(row=row, column=col)
if cell.comment is not None:
rows.append([cell.coordinate, cell.comment.author or "", cell.comment.text or ""])
if not rows:
return ""
return "## Comments\n\n" + markdown_table(["cell", "author", "comment"], rows)
def hyperlinks_to_markdown(ws_formula, bounds) -> str:
min_row, min_col, max_row, max_col, has_content = bounds
if not has_content:
return ""
rows = []
for row in range(min_row, max_row + 1):
for col in range(min_col, max_col + 1):
cell = ws_formula.cell(row=row, column=col)
if cell.hyperlink is not None:
h = cell.hyperlink
target = h.target or h.location or ""
rows.append([cell.coordinate, format_value(cell.value, "%Y-%m-%d %H:%M:%S"), target, h.tooltip or ""])
if not rows:
return ""
return "## Hyperlinks\n\n" + markdown_table(["cell", "text", "target", "tooltip"], rows)
def merged_cells_to_markdown(ws_formula) -> str:
rows = [[str(rng)] for rng in ws_formula.merged_cells.ranges]
if not rows:
return ""
return "## Merged cells\n\n" + markdown_table(["range"], rows)
def tables_to_markdown(ws_formula) -> str:
rows = []
try:
items = ws_formula.tables.items()
except Exception:
items = []
for name, table in items:
# openpyxl 3.x では items() が (name, ref) になることがあるため両対応。
ref = getattr(table, "ref", None) or str(table)
display_name = getattr(table, "displayName", None) or name
rows.append([display_name, ref])
if not rows:
return ""
return "## Excel tables\n\n" + markdown_table(["name", "range"], rows)
def hidden_to_markdown(ws_formula) -> str:
hidden_rows = [str(idx) for idx, dim in ws_formula.row_dimensions.items() if getattr(dim, "hidden", False)]
hidden_cols = [str(idx) for idx, dim in ws_formula.column_dimensions.items() if getattr(dim, "hidden", False)]
if not hidden_rows and not hidden_cols:
return ""
rows = []
if hidden_rows:
rows.append(["hidden rows", ", ".join(hidden_rows[:200]) + (" ..." if len(hidden_rows) > 200 else "")])
if hidden_cols:
rows.append(["hidden columns", ", ".join(hidden_cols[:200]) + (" ..." if len(hidden_cols) > 200 else "")])
return "## Hidden rows / columns\n\n" + markdown_table(["type", "items"], rows)
def data_validations_to_markdown(ws_formula) -> str:
dvs = getattr(ws_formula, "data_validations", None)
if dvs is None:
return ""
rows = []
for dv in getattr(dvs, "dataValidation", []):
rows.append([
str(dv.sqref),
dv.type or "",
dv.operator or "",
dv.formula1 or "",
dv.formula2 or "",
dv.allow_blank,
])
if not rows:
return ""
return "## Data validation\n\n" + markdown_table(
["range", "type", "operator", "formula1", "formula2", "allow blank"], rows
)
def conditional_formatting_to_markdown(ws_formula) -> str:
cf = getattr(ws_formula, "conditional_formatting", None)
if cf is None:
return ""
rows = []
try:
for cf_range in cf:
rules = cf[cf_range]
for rule in rules:
formula = ", ".join(rule.formula or []) if getattr(rule, "formula", None) else ""
rows.append([str(cf_range), rule.type or "", getattr(rule, "operator", "") or "", formula])
except Exception:
return ""
if not rows:
return ""
return "## Conditional formatting\n\n" + markdown_table(["range", "type", "operator", "formula"], rows)
def sheet_overview_to_markdown(ws_formula, bounds) -> str:
min_row, min_col, max_row, max_col, has_content = bounds
used_range = f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}" if has_content else "(empty)"
rows = [
["used range", used_range],
["sheet state", ws_formula.sheet_state],
["freeze panes", ws_formula.freeze_panes or ""],
["auto filter", getattr(ws_formula.auto_filter, "ref", None) or ""],
]
return "## Sheet summary\n\n" + markdown_table(["item", "value"], rows)
def defined_names_to_markdown(wb_formula) -> str:
rows = []
dns = getattr(wb_formula, "defined_names", None)
if dns is None:
return ""
# openpyxl 3.1: wb.defined_names.values(); older: wb.defined_names.definedName
try:
iterator = dns.values()
except Exception:
iterator = getattr(dns, "definedName", [])
for dn in iterator:
name = getattr(dn, "name", "")
scope = getattr(dn, "localSheetId", None)
text = getattr(dn, "attr_text", "") or ""
if name:
rows.append([name, "workbook" if scope is None else f"sheetId={scope}", text])
if not rows:
return ""
return "# Workbook defined names\n\n" + markdown_table(["name", "scope", "reference"], rows)
# -----------------------------
# Extraction: charts / images
# -----------------------------
def object_text(obj: Any) -> str:
"""openpyxlのTitle/RichText風オブジェクトから読めるテキストを可能な範囲で取り出す。"""
if obj is None:
return ""
if isinstance(obj, str):
return obj
# よくある title.tx.rich.p[0].r[0].t 形式を優先する。
try:
paragraphs = obj.tx.rich.p
texts = []
for p in paragraphs:
for r in getattr(p, "r", []) or []:
t = getattr(r, "t", "")
if t:
texts.append(t)
if getattr(p, "endParaRPr", None) is not None and not texts:
pass
if texts:
return "".join(texts)
except Exception:
pass
for attr in ("text", "v"):
try:
val = getattr(obj, attr)
if isinstance(val, str) and val:
return val
except Exception:
pass
return ""
def get_nested(obj: Any, names: Iterable[str]) -> Optional[Any]:
current = obj
for name in names:
if current is None:
return None
current = getattr(current, name, None)
return current
def get_ref(obj: Any, chains: Iterable[Iterable[str]]) -> str:
for chain in chains:
val = get_nested(obj, chain)
if val:
return str(val)
return ""
def chart_anchor(chart) -> str:
try:
marker = chart.anchor._from
return f"{get_column_letter(marker.col + 1)}{marker.row + 1}"
except Exception:
return ""
def chart_type_name(chart) -> str:
name = chart.__class__.__name__
typ = getattr(chart, "type", None)
return f"{name} ({typ})" if typ else name
def charts_to_markdown(ws_formula) -> str:
charts = getattr(ws_formula, "_charts", []) or []
if not charts:
return ""
out = "## Charts\n\n"
for i, chart in enumerate(charts, start=1):
out += f"### Chart {i}\n\n"
rows = [
["type", chart_type_name(chart)],
["anchor", chart_anchor(chart)],
["title", object_text(getattr(chart, "title", None))],
["x axis title", object_text(getattr(getattr(chart, "x_axis", None), "title", None))],
["y axis title", object_text(getattr(getattr(chart, "y_axis", None), "title", None))],
]
out += markdown_table(["item", "value"], rows)
series_rows = []
for j, ser in enumerate(getattr(chart, "series", []) or [], start=1):
title = get_ref(ser, [["tx", "strRef", "f"], ["tx", "v"]])
cat_ref = get_ref(ser, [["cat", "numRef", "f"], ["cat", "strRef", "f"]])
val_ref = get_ref(ser, [["val", "numRef", "f"]])
x_ref = get_ref(ser, [["xVal", "numRef", "f"], ["xVal", "strRef", "f"]])
y_ref = get_ref(ser, [["yVal", "numRef", "f"]])
series_rows.append([j, title, cat_ref, val_ref, x_ref, y_ref])
if series_rows:
out += markdown_table(["series", "title/ref", "category ref", "value ref", "x ref", "y ref"], series_rows)
out += "\n"
return out
def image_anchor(img) -> str:
try:
marker = img.anchor._from
return f"{get_column_letter(marker.col + 1)}{marker.row + 1}"
except Exception:
return ""
def image_extension(img) -> str:
path = getattr(img, "path", "") or ""
ext = os.path.splitext(path)[1].lower().lstrip(".")
return ext if ext else "png"
def images_to_markdown(ws_formula, image_dir: str, sheet_name: str) -> str:
images = getattr(ws_formula, "_images", []) or []
if not images:
return ""
os.makedirs(image_dir, exist_ok=True)
out = "## Images\n\n"
rows = []
sheet_safe = safe_filename(sheet_name)
for i, img in enumerate(images, start=1):
ext = image_extension(img)
filename = f"{sheet_safe}_image{i}.{ext}"
path = os.path.join(image_dir, filename)
try:
data = img._data()
with open(path, "wb") as f:
f.write(data)
rows.append([i, image_anchor(img), f""])
except Exception as e:
rows.append([i, image_anchor(img), f"画像抽出に失敗: {e}"])
return out + markdown_table(["image", "anchor", "file"], rows)
# -----------------------------
# Main extraction
# -----------------------------
def extract_content_to_markdown(input_xlsx: str, output_md: str, image_dir: str, args) -> bool:
try:
wb_formula = load_workbook(input_xlsx, data_only=False, keep_vba=True)
wb_values = load_workbook(input_xlsx, data_only=True, keep_vba=True)
except Exception as e:
print(f"エラー: {e}")
return False
out = []
out.append(f"# Excel workbook: {os.path.basename(input_xlsx)}\n\n")
out.append(
"> 注意: 数式セルの値は、Excelファイル内に保存されているキャッシュ値です。"
"openpyxlはExcel数式を再計算しません。必要ならExcelで再計算・保存してから実行してください。\n\n"
)
if not args.no_metadata:
dn_md = defined_names_to_markdown(wb_formula)
if dn_md:
out.append(dn_md)
for ws_formula in wb_formula.worksheets:
ws_values = wb_values[ws_formula.title]
bounds = find_used_bounds(ws_formula, ws_values)
out.append(f"# Sheet: {ws_formula.title}\n\n")
out.append(sheet_overview_to_markdown(ws_formula, bounds))
if not args.no_values:
out.append("## Values\n\n")
out.append(values_table_to_markdown(ws_formula, ws_values, bounds, args))
if not args.no_formulas:
out.append("## Formulas\n\n")
out.append(formulas_to_markdown(ws_formula, ws_values, bounds, args))
if not args.no_comments:
out.append(comments_to_markdown(ws_formula, bounds))
if not args.no_metadata:
out.append(hyperlinks_to_markdown(ws_formula, bounds))
out.append(merged_cells_to_markdown(ws_formula))
out.append(tables_to_markdown(ws_formula))
out.append(hidden_to_markdown(ws_formula))
out.append(data_validations_to_markdown(ws_formula))
out.append(conditional_formatting_to_markdown(ws_formula))
if not args.no_charts:
out.append(charts_to_markdown(ws_formula))
if not args.no_images:
out.append(images_to_markdown(ws_formula, image_dir, ws_formula.title))
out.append("---\n\n")
with open(output_md, "w", encoding=args.encoding, newline="\n") as f:
f.write("".join(out))
print(f"変換完了: {output_md}")
return True
def main():
args = initialize()
global pause
pause = args.pause
if not os.path.exists(args.input):
print("入力ファイルが見つかりません")
return
extract_content_to_markdown(args.input, args.output, args.imagedir, args)
if __name__ == "__main__":
main()
terminate()