| 1 | #!/usr/bin/env python3 |
| 2 | """ |
| 3 | Excel to Markdown Converter |
| 4 | |
| 5 | Supported formats: |
| 6 | .xlsx Excel workbook |
| 7 | .xlsm Excel macro-enabled workbook |
| 8 | |
| 9 | Unsupported by default: |
| 10 | .xls Legacy binary Excel format; resave as .xlsx first |
| 11 | |
| 12 | All paths produce the same output convention: |
| 13 | <input>.md Markdown file |
| 14 | """ |
| 15 | |
| 16 | import argparse |
| 17 | import re |
| 18 | import sys |
| 19 | from datetime import date, datetime, time |
| 20 | from pathlib import Path |
| 21 | from typing import Any |
| 22 | |
| 23 | _SCRIPTS_DIR = Path(__file__).resolve().parents[1] |
| 24 | if str(_SCRIPTS_DIR) not in sys.path: |
| 25 | sys.path.insert(0, str(_SCRIPTS_DIR)) |
| 26 | |
| 27 | from console_encoding import configure_utf8_stdio # noqa: E402 |
| 28 | from _batch import run_path_batch # noqa: E402 |
| 29 | from _conversion_profile import write_conversion_profile_best_effort # noqa: E402 |
| 30 | |
| 31 | configure_utf8_stdio() |
| 32 | |
| 33 | |
| 34 | # ───────────────────────────────────────────────────────────── |
| 35 | # Format registry |
| 36 | # ───────────────────────────────────────────────────────────── |
| 37 | |
| 38 | EXCEL_FORMATS = {".xlsx", ".xlsm"} |
| 39 | LEGACY_EXCEL_FORMATS = {".xls"} |
| 40 | |
| 41 | |
| 42 | # ───────────────────────────────────────────────────────────── |
| 43 | # Shared helpers |
| 44 | # ───────────────────────────────────────────────────────────── |
| 45 | |
| 46 | def _format_size(size: int) -> str: |
| 47 | for unit in ("B", "KB", "MB"): |
| 48 | if size < 1024: |
| 49 | return f"{size:.0f} {unit}" |
| 50 | size /= 1024 |
| 51 | return f"{size:.1f} GB" |
| 52 | |
| 53 | |
| 54 | def _report_result(out_file: Path) -> None: |
| 55 | size = out_file.stat().st_size |
| 56 | print(f"[OK] Saved Markdown to: {out_file} ({_format_size(size)})") |
| 57 | |
| 58 | |
| 59 | def _is_empty(value: Any) -> bool: |
| 60 | return value is None or (isinstance(value, str) and value.strip() == "") |
| 61 | |
| 62 | |
| 63 | def _markdown_escape(value: str) -> str: |
| 64 | value = value.replace("\\", "\\\\") |
| 65 | value = value.replace("|", "\\|") |
| 66 | value = value.replace("\r\n", "\n").replace("\r", "\n") |
| 67 | return re.sub(r"\s*\n\s*", "<br>", value).strip() |
| 68 | |
| 69 | |
| 70 | def _format_cell_value(value: Any) -> str: |
| 71 | if _is_empty(value): |
| 72 | return "" |
| 73 | if isinstance(value, bool): |
| 74 | return "TRUE" if value else "FALSE" |
| 75 | if isinstance(value, datetime): |
| 76 | return value.isoformat(sep=" ", timespec="seconds") |
| 77 | if isinstance(value, date): |
| 78 | return value.isoformat() |
| 79 | if isinstance(value, time): |
| 80 | return value.isoformat(timespec="seconds") |
| 81 | if isinstance(value, float): |
| 82 | return _markdown_escape(f"{value:g}") |
| 83 | return _markdown_escape(str(value)) |
| 84 | |
| 85 | |
| 86 | def _is_numeric_value(value: Any) -> bool: |
| 87 | return isinstance(value, (int, float)) and not isinstance(value, bool) |
| 88 | |
| 89 | |
| 90 | def _sheet_state_label(sheet_state: str) -> str: |
| 91 | if sheet_state == "visible": |
| 92 | return "visible" |
| 93 | return sheet_state or "unknown" |
| 94 | |
| 95 | |
| 96 | # ───────────────────────────────────────────────────────────── |
| 97 | # Worksheet extraction |
| 98 | # ───────────────────────────────────────────────────────────── |
| 99 | |
| 100 | def _merged_value_map(worksheet) -> dict[tuple[int, int], Any]: |
| 101 | """Return propagated values for merged cells, keyed by (row, column). |
| 102 | |
| 103 | Merged regions whose top-left cell is empty are intentionally skipped. |
| 104 | These are typically formatting-only ranges that carry no textual content. |
| 105 | """ |
| 106 | merged_values: dict[tuple[int, int], Any] = {} |
| 107 | for merged_range in worksheet.merged_cells.ranges: |
| 108 | value = worksheet.cell(merged_range.min_row, merged_range.min_col).value |
| 109 | if _is_empty(value): |
| 110 | continue |
| 111 | for row in range(merged_range.min_row, merged_range.max_row + 1): |
| 112 | for col in range(merged_range.min_col, merged_range.max_col + 1): |
| 113 | merged_values[(row, col)] = value |
| 114 | return merged_values |
| 115 | |
| 116 | |
| 117 | def _cell_value(worksheet, row: int, col: int, merged_values: dict[tuple[int, int], Any]) -> Any: |
| 118 | value = worksheet.cell(row, col).value |
| 119 | if _is_empty(value): |
| 120 | return merged_values.get((row, col), value) |
| 121 | return value |
| 122 | |
| 123 | |
| 124 | def _content_bounds(worksheet, merged_values: dict[tuple[int, int], Any]) -> tuple[int, int, int, int] | None: |
| 125 | min_row = min_col = None |
| 126 | max_row = max_col = None |
| 127 | |
| 128 | for row in worksheet.iter_rows(): |
| 129 | for cell in row: |
| 130 | if _is_empty(cell.value): |
| 131 | continue |
| 132 | min_row = cell.row if min_row is None else min(min_row, cell.row) |
| 133 | max_row = cell.row if max_row is None else max(max_row, cell.row) |
| 134 | min_col = cell.column if min_col is None else min(min_col, cell.column) |
| 135 | max_col = cell.column if max_col is None else max(max_col, cell.column) |
| 136 | |
| 137 | for row, col in merged_values: |
| 138 | min_row = row if min_row is None else min(min_row, row) |
| 139 | max_row = row if max_row is None else max(max_row, row) |
| 140 | min_col = col if min_col is None else min(min_col, col) |
| 141 | max_col = col if max_col is None else max(max_col, col) |
| 142 | |
| 143 | if min_row is None or min_col is None or max_row is None or max_col is None: |
| 144 | return None |
| 145 | return min_row, min_col, max_row, max_col |
| 146 | |
| 147 | |
| 148 | def _trim_trailing_empty_cells(row: list[Any]) -> list[Any]: |
| 149 | trimmed = list(row) |
| 150 | while trimmed and _is_empty(trimmed[-1]): |
| 151 | trimmed.pop() |
| 152 | return trimmed |
| 153 | |
| 154 | |
| 155 | def _extract_rows( |
| 156 | worksheet, |
| 157 | bounds: tuple[int, int, int, int], |
| 158 | merged_values: dict[tuple[int, int], Any], |
| 159 | max_rows: int, |
| 160 | max_cols: int, |
| 161 | ) -> tuple[list[list[Any]], bool, bool]: |
| 162 | min_row, min_col, max_row, max_col = bounds |
| 163 | |
| 164 | row_limit = max_row |
| 165 | col_limit = max_col |
| 166 | rows_truncated = False |
| 167 | cols_truncated = False |
| 168 | |
| 169 | if max_rows > 0 and (max_row - min_row + 1) > max_rows: |
| 170 | row_limit = min_row + max_rows - 1 |
| 171 | rows_truncated = True |
| 172 | if max_cols > 0 and (max_col - min_col + 1) > max_cols: |
| 173 | col_limit = min_col + max_cols - 1 |
| 174 | cols_truncated = True |
| 175 | |
| 176 | rows: list[list[Any]] = [] |
| 177 | width = 0 |
| 178 | for row_index in range(min_row, row_limit + 1): |
| 179 | row = [ |
| 180 | _cell_value(worksheet, row_index, col_index, merged_values) |
| 181 | for col_index in range(min_col, col_limit + 1) |
| 182 | ] |
| 183 | row = _trim_trailing_empty_cells(row) |
| 184 | width = max(width, len(row)) |
| 185 | rows.append(row) |
| 186 | |
| 187 | if width == 0: |
| 188 | return [], rows_truncated, cols_truncated |
| 189 | |
| 190 | normalized_rows = [row + [""] * (width - len(row)) for row in rows] |
| 191 | return normalized_rows, rows_truncated, cols_truncated |
| 192 | |
| 193 | |
| 194 | def _column_alignments(rows: list[list[Any]]) -> list[str]: |
| 195 | if not rows: |
| 196 | return [] |
| 197 | |
| 198 | width = len(rows[0]) |
| 199 | alignments: list[str] = [] |
| 200 | data_rows = rows[1:] if len(rows) > 1 else rows |
| 201 | for col_index in range(width): |
| 202 | values = [row[col_index] for row in data_rows if not _is_empty(row[col_index])] |
| 203 | if values and all(_is_numeric_value(value) for value in values): |
| 204 | alignments.append("---:") |
| 205 | else: |
| 206 | alignments.append("---") |
| 207 | return alignments |
| 208 | |
| 209 | |
| 210 | def _rows_to_markdown_table(rows: list[list[Any]]) -> str: |
| 211 | if not rows: |
| 212 | return "_No tabular content found._" |
| 213 | |
| 214 | formatted_rows = [[_format_cell_value(value) for value in row] for row in rows] |
| 215 | width = len(formatted_rows[0]) |
| 216 | separator = _column_alignments(rows) |
| 217 | lines = [ |
| 218 | "| " + " | ".join(formatted_rows[0]) + " |", |
| 219 | "| " + " | ".join(separator or ["---"] * width) + " |", |
| 220 | ] |
| 221 | |
| 222 | for row in formatted_rows[1:]: |
| 223 | lines.append("| " + " | ".join(row) + " |") |
| 224 | |
| 225 | return "\n".join(lines) |
| 226 | |
| 227 | |
| 228 | # ───────────────────────────────────────────────────────────── |
| 229 | # Excel → Markdown |
| 230 | # ───────────────────────────────────────────────────────────── |
| 231 | |
| 232 | def _convert_excel(input_file: Path, out_file: Path, max_rows: int, max_cols: int) -> str: |
| 233 | try: |
| 234 | from openpyxl import load_workbook |
| 235 | from openpyxl.utils import get_column_letter |
| 236 | except ImportError: |
| 237 | print("[ERROR] openpyxl not installed. Run: pip install openpyxl") |
| 238 | return "" |
| 239 | |
| 240 | workbook = load_workbook(input_file, data_only=True, read_only=False) |
| 241 | visible_sheets = [sheet for sheet in workbook.worksheets if sheet.sheet_state == "visible"] |
| 242 | |
| 243 | lines: list[str] = [ |
| 244 | f"# Spreadsheet Source: {input_file.name}", |
| 245 | "", |
| 246 | "## Workbook Summary", |
| 247 | "", |
| 248 | f"- Sheets: {len(workbook.worksheets)}", |
| 249 | f"- Visible sheets: {', '.join(sheet.title for sheet in visible_sheets) or 'None'}", |
| 250 | "", |
| 251 | "> Note: Formula cells are exported as cached values. This converter does not recalculate formulas.", |
| 252 | "", |
| 253 | ] |
| 254 | |
| 255 | if not visible_sheets: |
| 256 | lines.extend(["_No visible sheets found._", ""]) |
| 257 | |
| 258 | for worksheet in visible_sheets: |
| 259 | merged_values = _merged_value_map(worksheet) |
| 260 | bounds = _content_bounds(worksheet, merged_values) |
| 261 | |
| 262 | lines.extend([ |
| 263 | f"## Sheet: {worksheet.title}", |
| 264 | "", |
| 265 | f"- State: {_sheet_state_label(worksheet.sheet_state)}", |
| 266 | ]) |
| 267 | |
| 268 | if bounds is None: |
| 269 | lines.extend(["", "_No content found._", ""]) |
| 270 | continue |
| 271 | |
| 272 | min_row, min_col, max_row, max_col = bounds |
| 273 | used_range = ( |
| 274 | f"{get_column_letter(min_col)}{min_row}:" |
| 275 | f"{get_column_letter(max_col)}{max_row}" |
| 276 | ) |
| 277 | rows, rows_truncated, cols_truncated = _extract_rows( |
| 278 | worksheet, |
| 279 | bounds, |
| 280 | merged_values, |
| 281 | max_rows=max_rows, |
| 282 | max_cols=max_cols, |
| 283 | ) |
| 284 | |
| 285 | lines.extend([ |
| 286 | f"- Used range: {used_range}", |
| 287 | f"- Rows: {max_row - min_row + 1}", |
| 288 | f"- Columns: {max_col - min_col + 1}", |
| 289 | "", |
| 290 | ]) |
| 291 | |
| 292 | if rows_truncated or cols_truncated: |
| 293 | limit_notes = [] |
| 294 | if rows_truncated: |
| 295 | limit_notes.append(f"rows limited to {max_rows}") |
| 296 | if cols_truncated: |
| 297 | limit_notes.append(f"columns limited to {max_cols}") |
| 298 | lines.extend([f"> Truncated: {', '.join(limit_notes)}.", ""]) |
| 299 | |
| 300 | lines.extend([_rows_to_markdown_table(rows), ""]) |
| 301 | |
| 302 | markdown = "\n".join(lines).rstrip() + "\n" |
| 303 | out_file.write_text(markdown, encoding="utf-8") |
| 304 | _report_result(out_file) |
| 305 | return markdown |
| 306 | |
| 307 | |
| 308 | # ───────────────────────────────────────────────────────────── |
| 309 | # Dispatcher |
| 310 | # ───────────────────────────────────────────────────────────── |
| 311 | |
| 312 | def convert_to_markdown( |
| 313 | input_path: str, |
| 314 | output_path: str | None = None, |
| 315 | max_rows: int = 0, |
| 316 | max_cols: int = 0, |
| 317 | ) -> str: |
| 318 | input_file = Path(input_path) |
| 319 | if not input_file.exists(): |
| 320 | print(f"[ERROR] File not found: {input_path}") |
| 321 | return "" |
| 322 | |
| 323 | suffix = input_file.suffix.lower() |
| 324 | if suffix in LEGACY_EXCEL_FORMATS: |
| 325 | print("[ERROR] Unsupported legacy Excel format: .xls") |
| 326 | print(" Please resave the workbook as .xlsx and run this converter again.") |
| 327 | return "" |
| 328 | if suffix not in EXCEL_FORMATS: |
| 329 | supported = ", ".join(sorted(EXCEL_FORMATS)) |
| 330 | print(f"[ERROR] Unsupported format: {suffix}") |
| 331 | print(f" Supported: {supported}") |
| 332 | return "" |
| 333 | |
| 334 | if max_rows < 0 or max_cols < 0: |
| 335 | print("[ERROR] --max-rows and --max-cols must be zero or positive integers") |
| 336 | return "" |
| 337 | |
| 338 | out_file = Path(output_path) if output_path else input_file.with_suffix(".md") |
| 339 | out_file.parent.mkdir(parents=True, exist_ok=True) |
| 340 | |
| 341 | print(f"[INFO] Converting Excel workbook: {input_file.name}") |
| 342 | markdown = _convert_excel(input_file, out_file, max_rows=max_rows, max_cols=max_cols) |
| 343 | if markdown: |
| 344 | profile_path = write_conversion_profile_best_effort( |
| 345 | input_path=str(input_file), |
| 346 | markdown_path=out_file, |
| 347 | converter="excel_to_md.py", |
| 348 | conversion_type=suffix.lstrip("."), |
| 349 | ) |
| 350 | if profile_path: |
| 351 | print(f" Wrote conversion profile -> {profile_path}") |
| 352 | return markdown |
| 353 | |
| 354 | |
| 355 | def main() -> int: |
| 356 | parser = argparse.ArgumentParser( |
| 357 | description="Convert Excel workbooks to Markdown", |
| 358 | formatter_class=argparse.RawDescriptionHelpFormatter, |
| 359 | epilog=""" |
| 360 | Examples: |
| 361 | python excel_to_md.py report.xlsx |
| 362 | python excel_to_md.py report.xlsx budget.xlsm |
| 363 | python excel_to_md.py ./workbooks -o ./markdown |
| 364 | python excel_to_md.py report.xlsx -o output.md |
| 365 | python excel_to_md.py report.xlsm --max-rows 200 --max-cols 40 |
| 366 | |
| 367 | Supported formats: |
| 368 | .xlsx .xlsm |
| 369 | |
| 370 | Unsupported by default: |
| 371 | .xls Resave as .xlsx first |
| 372 | """, |
| 373 | ) |
| 374 | parser.add_argument("inputs", nargs="+", help="Input Excel workbook(s) or directories") |
| 375 | parser.add_argument( |
| 376 | "-o", |
| 377 | "--output", |
| 378 | help="Output Markdown file for one input, or output directory for multiple inputs/directories", |
| 379 | ) |
| 380 | parser.add_argument( |
| 381 | "--max-rows", |
| 382 | type=int, |
| 383 | default=0, |
| 384 | help="Maximum rows per sheet to export (0 = no limit)", |
| 385 | ) |
| 386 | parser.add_argument( |
| 387 | "--max-cols", |
| 388 | type=int, |
| 389 | default=0, |
| 390 | help="Maximum columns per sheet to export (0 = no limit)", |
| 391 | ) |
| 392 | args = parser.parse_args() |
| 393 | |
| 394 | return run_path_batch( |
| 395 | args.inputs, |
| 396 | EXCEL_FORMATS | LEGACY_EXCEL_FORMATS, |
| 397 | args.output, |
| 398 | lambda source, output: bool( |
| 399 | convert_to_markdown( |
| 400 | str(source), |
| 401 | str(output), |
| 402 | max_rows=args.max_rows, |
| 403 | max_cols=args.max_cols, |
| 404 | ) |
| 405 | ), |
| 406 | ) |
| 407 | |
| 408 | |
| 409 | if __name__ == "__main__": |
| 410 | raise SystemExit(main()) |
| 411 |