The user wants to build a Python script to convert PDF charts (tables) into Excel. Key requirements: 1. Extract tables from PDF 2. Detect merged cells (this is the main challenge) 3. The PDF was printed from Excel, so it should be well-structured/reconstructable Let me think about the technical approaches: **Libraries available for PDF table extraction:** 1. **pdfplumber** - Great for table extraction, has access to lines, rects, and can detect table structures. It can give you cell bounding boxes which is key for merged cell detection. 2. **camelot** - Good for tables with visible borders (lattice mode) or whitespace-separated (stream mode) 3. **tabula-py** - Java-based, wrapper around tabula 4. **PyMuPDF (fitz)** - Fast, has table detection in newer versions (1.23+) 5. **pdfminer.six** - Lower level **Merged cell detection strategies:** Since the PDF was printed from Excel, there are likely visible gridlines/borders. Merged cells can be detected by: 1. **Grid-based approach**: Detect all horizontal and vertical lines, build a grid of all line positions. Then for each potential cell in the grid, check if borders exist. If a cell spans multiple grid columns/rows without internal dividing lines, it's a merged cell. 2. **Cell bounding box approach**: pdfplumber's `extract_table()` with explicit lines settings returns cells. The `debug_tablefinder()` or using `page.find_tables()` gives you `table.cells` which are bounding boxes. Cells that span multiple grid positions are merged cells. Actually, pdfplumber's `Table` object has a `cells` attribute - list of cell bounding boxes. And it has `rows` where each row has `cells`. When cells are merged, pdfplumber actually handles this: in `table.extract()`, merged cells appear with the value in the top-left and None in other spanned positions... Actually let me recall. pdfplumber's table extraction: `page.extract_table()` returns a list of lists where merged cells... I believe pdfplumber returns the text in the top-left cell and None for the covered cells. Actually, for vertical merges it returns None for cells below, and for horizontal merges it also collapses. The best robust approach: - Use `page.find_tables()` to get Table objects - Access `table.cells` - this is a list of tuples (x0, top, x1, bottom) for each detected cell - The grid positions: unique x-coordinates define column boundaries, unique y-coordinates define row boundaries - A cell whose bbox spans multiple column boundaries or row boundaries is a merged cell - Then reconstruct in openpyxl, using `merge_cells()` for merged regions and placing text in the anchor (top-left) cell Let me think about the implementation: ```python import pdfplumber from openpyxl import Workbook from openpyxl.utils import get_column_letter def pdf_tables_to_excel(pdf_path, excel_path): wb = Workbook() wb.remove(wb.active) with pdfplumber.open(pdf_path) as pdf: for page_num, page in enumerate(pdf.pages, 1): tables = page.find_tables() for table_num, table in enumerate(tables, 1): ws = wb.create_sheet(f"p{page_num}_t{table_num}") write_table(ws, table) wb.save(excel_path) ``` For the merged cell detection, I need to: 1. Get all cell bboxes from `table.cells` 2. Compute unique sorted x edges and y edges across all cells 3. For each cell, map its bbox to grid indices: column span = number of grid columns it covers, row span similarly 4. Extract text per cell using `page.crop(bbox).extract_text()` 5. Write to worksheet at anchor position, and if span > 1x1, call `ws.merge_cells()` Actually, pdfplumber's Table object has `rows` attribute, each row has `cells` list where merged cells show up as None or repeated bboxes. Let me recall the exact structure: - `table.rows` → list of Row objects - `row.cells` → list of bboxes or None. For merged cells, the same bbox appears multiple times? Actually I think for horizontally merged cells, the bbox appears in each covered position... Hmm. From pdfplumber docs: "Each row is a list of cells; each cell is either a bbox tuple or None." Cells that are part of a merged region — I believe pdfplumber repeats the merged cell's bbox across the spanned positions, or returns None. Let me think... Actually in pdfplumber, for a horizontally merged cell, `row.cells` contains the same bbox tuple repeated for each column it spans. For cells covered by a vertical merge from above, the cell is None. Hmm, I'm not 100% sure. Let me think about the actual implementation. In pdfplumber's `Table` class: - `self.cells` = list of all cell bboxes (intersection of lines) - Rows are constructed by grouping cells. Each `Row.cells` corresponds to columns. From the source code of pdfplumber: The Table object builds `rows` where each row has cells. Looking at the source, `_bbox_to_cell` mapping... I recall that in practice, merged cells result in the same bbox appearing in multiple positions of the row's cells list. So you can detect merges by checking if consecutive cells in a row share the same bbox, or if a cell's bbox spans multiple columns. The safest approach that doesn't depend on pdfplumber internals: derive the grid from cell bboxes myself. Algorithm: ```python def get_grid(cells): xs = sorted({c[0] for c in cells} | {c[2] for c in cells}) ys = sorted({c[1] for c in cells} | {c[3] for c in cells}) return xs, ys ``` Wait, but cells from a table with merged cells: `table.cells` contains actual detected cells (the merged ones as single large bboxes). So the x-edges include both the merge boundaries and internal grid lines? No — if a cell is merged across columns 2-3, there is no vertical line between them, so the internal edge won't appear from that cell, but it may appear from other rows' cells. So the union of all edges gives the full grid. Good. Then for each cell bbox (x0, y0, x1, y1): - col_start = xs.index(x0), col_end = xs.index(x1) → colspan = col_end - col_start - row_start = ys.index(y0), row_end = ys.index(y1) → rowspan = row_end - row_start - If colspan > 1 or rowspan > 1 → merged cell Then write: ```python anchor_row = row_start + 1 anchor_col = col_start + 1 ws.cell(row=anchor_row, column=anchor_col, value=text) if colspan > 1 or rowspan > 1: ws.merge_cells(start_row=anchor_row, start_column=anchor_col, end_row=anchor_row + rowspan - 1, end_column=anchor_col + colspan - 1) ``` Text extraction per cell: use `page.crop(bbox).extract_text()`. But there's a subtlety: text near borders may get clipped. pdfplumber's own `extract()` method handles this. Actually, the Table object has an `extract()` method that returns the table as list of lists with merged cells handled (value repeated or None?). Alternative: extract words once with `page.extract_words()` and assign each word to a cell by checking containment. That's more efficient than cropping per cell: ```python words = page.extract_words() def text_in_bbox(words, bbox, tolerance=2): x0, top, x1, bottom = bbox selected = [w for w in words if w['x0'] >= x0 - tol and w['x1'] <= x1 + tol and w['top'] >= top - tol and w['bottom'] <= bottom + tol] # sort by (top, x0) and join ``` Need to join words preserving line breaks: group by 'top' (rounded), then join with spaces/newlines. Also handling settings for `find_tables`: - `vertical_strategy="lines"`, `horizontal_strategy="lines"` (default is lines for both? Default table_settings: vertical_strategy="lines", horizontal_strategy="lines"). Actually defaults are "lines" for both I believe. Yes, default is lines-based. - If the Excel printout has borders, lines strategy works well. - `snap_tolerance`, `join_tolerance` may need tuning (default 3). Edge cases: - Borderless tables: fall back to "text" strategy, but merged cell detection becomes unreliable — mention this. - Multiple tables per page. - Tables spanning pages. - Text strategy doesn't give real cell bboxes for merges in the same way — actually with text strategy, cells are still formed, but merges can't be detected reliably. Also worth mentioning: camelot has `split_text` and can report... not merged cells directly. PyMuPDF's `page.find_tables()` also gives cells with rowspan/colspan? PyMuPDF's Table has `cells` and its `extract()` gives... In PyMuPDF, `tab.cells` are bboxes and `tab.extract()` returns rows where merged cells... PyMuPDF Table object has `.row_count`, `.col_count`, `.cells`, and extract returns None for merged-away cells? I don't remember exactly. pdfplumber approach is solid, I'll go with that. Let me also add formatting carryover: could copy bold/fonts? That's extra. Maybe mention as extension. Also add: preserve numbers as numbers — convert text that looks numeric to float/int so Excel treats them properly. Nice touch. Let me now write a complete, working script. ```python """ pdf_to_excel.py Reconstruct Excel tables (including merged cells) from a PDF that was originally printed/exported from Excel. Requires: pip install pdfplumber openpyxl """ import pdfplumber from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Alignment # ---------- helpers ---------- def cluster(values, tol=2.0): """Cluster nearly-identical coordinates (line detection jitter).""" clusters = [] for v in sorted(values): if clusters and abs(v - clusters[-1][-1]) <= tol: clusters[-1].append(v) else: clusters.append([v]) return [sum(c) / len(c) for c in clusters] def build_grid(cells, tol=2.0): """Return sorted unique column (x) and row (y) boundary lines.""" xs = cluster([c[0] for c in cells] + [c[2] for c in cells], tol) ys = cluster([c[1] for c in cells] + [c[3] for c in cells], tol) return xs, ys def locate(edges, value, tol=2.0): """Index of the grid edge matching a coordinate.""" for i, e in enumerate(edges): if abs(e - value) <= tol: return i raise ValueError(f"edge not found for {value}") def extract_cell_text(page, bbox, x_tol=1.5, y_tol=1.5): """Extract text inside a cell bbox.""" cropped = page.crop(bbox) return (cropped.extract_text() or "").strip() def coerce(value): """Convert numeric-looking strings to numbers.""" if not isinstance(value, str): return value s = value.replace(",", "").strip() try: return int(s) except ValueError: pass try: return float(s) except ValueError: return value ``` Wait — cropping per cell: pdfplumber crop includes objects whose... `page.crop(bbox)` keeps objects fully or partially within? `crop` retains chars whose coordinates fall within bbox? It filters objects within the bbox (chars whose centers? or partial?). pdfplumber's crop: "Retains only objects that fall entirely within the bounding box"? Actually `crop` keeps objects where the object is within bbox — I believe it filters by objects being at least partially inside? Let me recall: `Page.crop(bbox, strict=False)` — with strict=False (default... or strict=True default?). Signature: `crop(self, bbox, strict=False)`. With strict=False, keeps objects partially overlapping? Hmm, actually I think crop keeps objects whose coordinates are within... For chars, it checks if the char's bbox is within... To be safe, I'll extract words once and assign them to cells by containment with tolerance. That avoids double-counting and border chars. But chars on the border lines themselves... Words fully inside the cell bbox (with small tolerance) get assigned. A word near the border belongs to one cell since borders are lines, and words don't usually straddle lines. Plan: ```python words = page.extract_words(x_tolerance=1.5, keep_blank_chars=False) def words_in_cell(words, bbox, tol=2): x0, top, x1, bottom = bbox inside = [w for w in words if w['x0'] >= x0 - tol and w['x1'] <= x1 + tol and w['top'] >= top - tol and w['bottom'] <= bottom + tol] # group into lines by 'top' lines = {} for w in inside: key = round(w['top'], 1) # or cluster lines.setdefault(key, []).append(w) out = [] for key in sorted(lines): line_words = sorted(lines[key], key=lambda w: w['x0']) out.append(" ".join(w['text'] for w in line_words)) return "\n".join(out) ``` Rounding top to 0.1 may split words on the same visual line if their tops differ slightly. Better to cluster tops with tolerance ~3: ```python def group_lines(words, tol=3): words = sorted(words, key=lambda w: (w['top'], w['x0'])) lines = [] for w in words: if lines and abs(w['top'] - lines[-1][0]['top']) <= tol: lines[-1].append(w) else: lines.append([w]) ... ``` OK. Then main conversion: ```python def table_to_sheet(page, table, ws): cells = table.cells xs, ys = build_grid(cells) words = page.extract_words() for (x0, top, x1, bottom) in cells: c0 = locate(xs, x0) c1 = locate(xs, x1) r0 = locate(ys, top) r1 = locate(ys, bottom) colspan = c1 - c0 rowspan = r1 - r0 text = words_in_cell(words, (x0, top, x1, bottom)) value = coerce(text) row, col = r0 + 1, c0 + 1 cell = ws.cell(row=row, column=col, value=value) if colspan > 1 or rowspan > 1: ws.merge_cells(start_row=row, start_column=col, end_row=row + rowspan - 1, end_column=col + colspan - 1) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ``` Hmm, alignment centering for merged cells is a guess; maybe leave default or make optional. I'll set wrap_text maybe. Keep it simple; mention it. Column widths: set based on PDF widths: ```python for i in range(len(xs) - 1): width_chars = max(8, (xs[i+1] - xs[i]) / 5) # rough pt->chars ws.column_dimensions[get_column_letter(i+1)].width = width_chars ``` Dedupe cells: `table.cells` might contain duplicates? It's a set-like structure (list of unique bboxes). I think it's unique. Fine, but converting to set doesn't hurt. Multiple tables and pages: ```python def convert(pdf_path, out_path): wb = Workbook() wb.remove(wb.active) with pdfplumber.open(pdf_path) as pdf: for pno, page in enumerate(pdf.pages, 1): tables = page.find_tables() for tno, tbl in enumerate(tables, 1): name = f"p{pno}" if len(tables) == 1 else f"p{pno}_t{tno}" ws = wb.create_sheet(name[:31]) table_to_sheet(page, tbl, ws) wb.save(out_path) ``` Also a debug/report feature: print detected merged ranges per sheet so the user can verify: ```python if colspan > 1 or rowspan > 1: merges.append((row, col, rowspan, colspan)) ``` Print summary. Also mention alternative quick path: `camelot` and `pdftables`... And note the limitation: if the Excel print didn't include gridlines (borderless), lines strategy fails → use `vertical_strategy="text"` but merged-cell detection is then heuristic (whitespace gaps), much less reliable. Since they said printed from Excel, presumably borders exist. But Excel prints don't include gridlines by default unless enabled! Important caveat: Excel only prints gridlines if "Print gridlines" is enabled; otherwise only explicit borders print. If the chart has explicit borders (typical for formatted reports), fine. Also mention header rows, keep_vba not needed, etc. Let me also think about whether `find_tables` cells include merged regions correctly. pdfplumber's table detection: it finds intersections of lines and builds cells as rectangles bounded by lines. A merged cell region with no internal lines becomes one big cell — exactly what we want. Yes, this works: the "cells" are the maximal rectangles bounded by detected lines. One more subtlety: `find_tables` default settings include `snap_tolerance=3`, `join_tolerance=3`, `intersection_tolerance=3` (newer versions use `intersection_tolerance`... older `intersection_x_tolerance`). Defaults are fine; can pass `table_settings` if needed. Also `min_words_vertical` / `min_words_horizontal` for text strategy only. Let me also handle the case where `find_tables` finds nothing: warn the user. Edge: sheet name duplicates across pages (p1, p2 unique). Fine. Now the text extraction per cell — I want to use extract_words once per page. Multi-line cells joined with "\n" and set wrap_text. Good. Also coerce numbers: careful with things like "1,234" → 1234, "12.5%" → keep string or convert to 0.125 with percent format? Keep simple: strip commas, try int/float. Leave percents as strings, or handle %: ```python if s.endswith('%'): try: return float(s[:-1]) / 100 # and set number_format='0.0%' ``` That requires setting number_format on the cell. Could do it — nice touch. I'll include percent handling with number_format. Dates: leave as strings (date parsing is risky). Mention. Now, about detecting merged cells explanation — the core insight to communicate: **Key insight**: In a bordered table, pdfplumber reconstructs cells as rectangles bounded by ruling lines. A merged cell has no internal ruling lines, so it appears as one large rectangle spanning several grid positions. By building the full grid from the union of all cell edges, we can compute each cell's row/col span and reproduce the merge with openpyxl's `merge_cells`. Also alternative: if lines are missing, fallback strategy `text` + heuristics. Provide `table_settings` parameter. Let me also give a quick sanity-check snippet to visualize detected cells: ```python im = page.to_image() im.debug_tablefinder() im.save("debug.png") ``` This is super useful for tuning. I should also handle cells assignment when a word overlaps multiple cells (e.g., text crossing a border due to tolerance). Minor; skip. Let me write the final script cleanly, with docstrings, and a CLI (`if __name__ == "__main__":` with argparse or sys.argv). Also discuss testing workflow: 1. Run debug_tablefinder to verify cell detection. 2. Convert, open output, compare merges. Also mention: PyMuPDF alternative one-liner-ish? PyMuPDF `find_tables` + `to_excel()`? Actually PyMuPDF's Table has `.to_excel()`? I recall PyMuPDF 1.23 added `page.find_tables()` and the Table has `.extract()`, `.to_markdown()`, `.to_pandas()`. Not sure about to_excel. And its merged-cell handling: extract() returns None in spanned cells? I believe PyMuPDF extract puts the value in the top-left and empty strings elsewhere, without giving merge ranges. So pdfplumber remains the better choice for explicit merge ranges. I'll mention briefly. Format: This is a coding question — provide a full script with explanation. Use headers, code blocks. Length: substantial but focused. The user asked "can we build" — yes, deliver the script plus explanation of the merged-cell detection logic, caveats, and tuning tips. Markdown appropriate. Let me draft the final code carefully, checking pdfplumber API details: - `pdfplumber.open(path)` → PDF with `.pages` - `page.find_tables(table_settings={})` → list of Table - `table.cells` → list of (x0, top, x1, bottom) tuples. Yes: "cells: list of tuples" — bbox coordinates in pdfplumber's coordinate system (top is distance from top of page). - `page.extract_words()` → list of dicts with keys: text, x0, x1, top, bottom, doctop, etc. Coordinate system: pdfplumber uses top-based (top, bottom). Consistent between words and table cells. Good. crop approach alternative — I'll go with word assignment. One concern: `extract_words` merges chars into words; words crossing cell borders (e.g., no borders on one side) could spill. With tolerance, a word exactly at border edge could match two cells. Use containment with tolerance 1 and also require word's center within bbox? A stricter approach: word's center must be inside bbox: ```python cx = (w['x0'] + w['x1']) / 2 cy = (w['top'] + w['bottom']) / 2 if x0 - tol <= cx <= x1 + tol and top - tol <= cy <= bottom + tol ``` Center-based assignment guarantees each word goes to exactly one cell (borders are lines; centers rarely exactly on a line). I'll use center-based with small tolerance. But then multi-line grouping still works. Edge: words belonging to no cell (text outside table) — ignored, fine. Now `build_grid` clustering: edges from different cells should coincide, but floating jitter possible; cluster with tol=2 (or 3). locate uses same tol. Potential issue: `table.cells` — does it include merged cell bboxes only, or also the constituent grid cells? It's the actual rectangles found = merged ones as big rects. Confirmed by design of line-based cell detection. Number coercion function with percent: ```python def coerce(text): s = text.strip() if not s: return None # single-line only for numeric coercion if "\n" in s: return text num = s.replace(",", "") pct = False if num.endswith("%"): pct = True num = num[:-1] try: v = float(num) except ValueError: return text if pct: v /= 100 if v.is_integer() and not pct: v = int(v) return v, "0.0%" if pct else None ``` Hmm, returning tuple complicates. Maybe return (value, number_format). Fine. Parentheses negatives like (1,234)? Skip; mention as extension. Also handle currency symbols: strip "$" "€" "£" prefix: ```python for sym in ("$", "€", "£"): if num.startswith(sym): num = num[1:] currency = sym ``` Maybe too much; keep $ handling? I'll strip leading currency symbols and set format '"$"#,##0.00'? This adds complexity. I'll strip common currency symbols but not set format (keep simple) — actually setting format matters for display. Keep it minimal: handle commas, %, and plain numbers; mention extension points. Actually let me include % since common in charts, and currency with format. Hmm, balance brevity. I'll include % and note extensions. Merged cell alignment: set center alignment + wrap for merged cells only, optional flag. Column widths from PDF geometry: nice touch: ```python def set_column_widths(ws, xs): for i in range(len(xs) - 1): w_pt = xs[i+1] - xs[i] ws.column_dimensions[get_column_letter(i+1)].width = max(6, w_pt / 4.5) ``` Rough: 1 char ≈ 5-7 pt at 11pt Calibri... width unit ≈ width of '0' ≈ 7 px ≈ 5.25 pt. Divide by ~5. Fine, it's approximate; mention. Row heights similar; skip. Debug mode: ```python def debug_page(page, path): im = page.to_image(resolution=120) im.debug_tablefinder() im.save(path) ``` Note: `debug_tablefinder` draws on the ImageAnnotator and returns it; `im.debug_tablefinder()` returns new image? Signature: `im.debug_tablefinder()` returns the annotator with drawings. So: ```python im = page.to_image(resolution=120) im.debug_tablefinder() im.save("debug.png") ``` debug_tablefinder returns self after drawing, so this works. CLI: ```python if __name__ == "__main__": import sys pdf_in = sys.argv[1] xlsx_out = sys.argv[2] if len(sys.argv) > 2 else pdf_in.rsplit(".",1)[0] + ".xlsx" convert(pdf_in, xlsx_out) ``` Add merge report printing. Now let me also double check `locate` for cell edges not in grid due to clustering averaging: since grid edges are cluster means, a cell edge equals one of the clustered values, within tol of mean. tol=2 vs cluster tol=2 — mean deviation ≤ tol. OK. Also: dedupe cells via set, since table.cells should be unique but just in case. Sorting cells for deterministic writing order: not needed since we compute indices. Now potential problem: a table with only horizontal merges but no full vertical lines crossing merged region — the union grid from all cells handles it. What about the outer border? Cells' outer edges define grid extremes. Good. What if find_tables detects each row as separate table (broken lines)? Tuning: `join_tolerance`, `snap_tolerance`. Mention. Also `horizontal_strategy="lines_strict"`? There's "lines", "lines_strict", "text", "explicit". lines_strict only uses actual lines (not rects). Excel borders may be rects (filled) — pdfplumber treats rects as lines by default in "lines" strategy (edges of rects). Fine. Let me write out the whole final script now, cleanly. ```python #!/usr/bin/env python3 """ Reconstruct Excel tables — including merged cells — from a PDF that was originally printed / exported from Excel. Usage: pip install pdfplumber openpyxl python pdf_to_excel.py input.pdf [output.xlsx] """ from collections import defaultdict import pdfplumber from openpyxl import Workbook from openpyxl.styles import Alignment from openpyxl.utils import get_column_letter TOL = 2.0 # coordinate-matching tolerance, in PDF points # ---------------------------------------------------------------- grid utils def cluster(values, tol=TOL): """Merge nearly identical coordinates into their mean.""" groups = [] for v in sorted(values): if groups and v - groups[-1][-1] <= tol: groups[-1].append(v) else: groups.append([v]) return [sum(g) / len(g) for g in groups] def build_grid(cells): """Column/row boundary lines = union of all cell edges.""" xs = cluster([c[0] for c in cells] + [c[2] for c in cells]) ys = cluster([c[1] for c in cells] + [c[3] for c in cells]) return xs, ys def grid_index(edges, value, tol=TOL): for i, e in enumerate(edges): if abs(e - value) <= tol: return i raise ValueError(f"no grid edge near {value}") # ---------------------------------------------------------------- text utils def words_in_cell(words, bbox, tol=1.0): """Words whose center lies inside the cell bbox, joined into lines.""" x0, top, x1, bottom = bbox inside = [ w for w in words if x0 - tol <= (w["x0"] + w["x1"]) / 2 <= x1 + tol and top - tol <= (w["top"] + w["bottom"]) / 2 <= bottom + tol ] inside.sort(key=lambda w: (w["top"], w["x0"])) lines, current, last_top = [], [], None for w in inside: if last_top is None or abs(w["top"] - last_top) <= 3: current.append(w) else: lines.append(current) current = [w] last_top = w["top"] if current: lines.append(current) return "\n".join( " ".join(w["text"] for w in sorted(line, key=lambda w: w["x0"])) for line in lines ).strip() def coerce(text): """Turn numeric-looking strings into numbers; return (value, fmt).""" if not text or "\n" in text: return text or None, None s = text.replace(",", "").strip() pct = s.endswith("%") if pct: s = s[:-1] try: v = float(s) except ValueError: return text, None if pct: return v / 100, "0.0%" return (int(v) if v.is_integer() else v), None # ---------------------------------------------------------------- conversion def table_to_sheet(page, table, ws): cells = sorted(set(table.cells)) xs, ys = build_grid(cells) words = page.extract_words() merges = [] for x0, top, x1, bottom in cells: c0, c1 = grid_index(xs, x0), grid_index(xs, x1) r0, r1 = grid_index(ys, top), grid_index(ys, bottom) rowspan, colspan = r1 - r0, c1 - c0 text = words_in_cell(words, (x0, top, x1, bottom)) value, fmt = coerce(text) row, col = r0 + 1, c0 + 1 cell = ws.cell(row=row, column=col, value=value) if fmt: cell.number_format = fmt if rowspan > 1 or colspan > 1: ws.merge_cells(start_row=row, start_column=col, end_row=row + rowspan - 1, end_column=col + colspan - 1) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) merges.append((row, col, rowspan, colspan)) elif "\n" in (text or ""): cell.alignment = Alignment(wrap_text=True, vertical="top") # approximate column widths from PDF geometry for i in range(len(xs) - 1): ws.column_dimensions[get_column_letter(i + 1)].width = \ max(6, (xs[i + 1] - xs[i]) / 5) return merges def convert(pdf_path, xlsx_path, table_settings=None): wb = Workbook() wb.remove(wb.active) with pdfplumber.open(pdf_path) as pdf: for pno, page in enumerate(pdf.pages, 1): tables = page.find_tables(table_settings=table_settings or {}) if not tables: print(f"page {pno}: no tables found") continue for tno, tbl in enumerate(tables, 1): name = f"p{pno}" if len(tables) == 1 else f"p{pno}_t{tno}" ws = wb.create_sheet(name[:31]) merges = table_to_sheet(page, tbl, ws) print(f"page {pno} table {tno}: " f"{len(tbl.cells)} cells -> sheet '{name}', " f"{len(merges)} merged ranges") wb.save(xlsx_path) print(f"saved {xlsx_path}") if __name__ == "__main__": import sys src = sys.argv[1] dst = sys.argv[2] if len(sys.argv) > 2 else src.rsplit(".", 1)[0] + ".xlsx" convert(src, dst) ``` Wait — there's a subtle issue with merged cell borders in openpyxl: merged cells' borders aren't written; formatting beyond anchor is lost. Not a concern for data fidelity. Another subtlety: when merging, openpyxl requires the spanned cells to be empty — we only write anchors, so fine. Also `words_in_cell` returning "" → coerce returns (None, None) → empty cell. Good. Also note about `extract_words` on the whole page: includes words outside the table; fine. The merge report: could also print ranges like "B2:D2". Let me format merges nicely: ```python f"{get_column_letter(col)}{row}:{get_column_letter(col+colspan-1)}{row+rowspan-1}" ``` I'll include that in the printed summary — helpful for verification, which addresses their main concern. Now the explanation of how merged-cell detection works, and caveats: 1. **How it works**: lines → cells; merged region = one big rect; grid from union of edges; span = merge. 2. **Requirement**: visible ruling lines (Excel "Print gridlines" or actual borders). If not, fallback to text strategy with caveat. 3. **Tuning**: debug_tablefinder image; snap/join tolerances; lines_strict if rects cause phantom lines. 4. **Limitations**: colors/fonts/formulas not recoverable from PDF; dates stay text; multi-page tables need stitching. 5. Alternatives: camelot (lattice), PyMuPDF find_tables — but pdfplumber gives the raw cell geometry needed for merges. Also worth noting: `table.extract()` exists but collapses merges (None in covered cells) — we bypass it to get geometry. Actually note: pdfplumber's extract puts the value in the anchor and None elsewhere, but doesn't tell you the span shape — which is why we go through `.cells`. Good to mention. Also I might add a variant: if the user prefers every table appended into one sheet — keep simple, one sheet per table. Let me also double-check `find_tables(table_settings={})` accepts empty dict — yes. `page.find_tables()` default settings fine. One more check on `table.cells` attribute name: pdfplumber Table has `.cells` (list of bboxes), `.rows`, `.extract()`. Yes. Potential edge: `find_tables` may include nested/duplicate tables (e.g., a table inside a table region) — rare; mention dedup if seen. Also: if Excel prints without borders but with banded fills — fills are rects; "lines" strategy treats rect edges as lines, which can actually help or create phantom cells. `lines_strict` avoids rect edges. Mention in tuning. Formatting of the answer: - Brief intro: yes, doable; key idea for merged cells. - The script (complete). - How merged-cell detection works (short section). - Tuning/caveats section. - Keep prose tight; code does the talking. Length: medium-long; it's a code request, full script justified. Avoid excessive bullet lists; a few are fine. Let me finalize. I'll also add a debug snippet for visualizing detection. Also double-check: `im.debug_tablefinder()` — in pdfplumber, `Page.to_image()` returns ImageAnnotator (actually a `PageImage` subclass). `.debug_tablefinder(settings={})` draws detected tables and returns self. Then `.save(path)`. Good. Percent values: e.g., "12.5%" → 0.125 with format "0.0%" displays 12.5%. Good. Negative numbers in parentheses: mention as extension. Also, merged title rows spanning the whole table are common in these printouts — handled naturally. One more consideration: sheet per page vs per table — printing puts page breaks; a table split across pages becomes two sheets. Mention stitching as manual/extension. I think the answer is ready. Let me write it. Yes, this is very doable — and since the PDF was printed from Excel, the ruling lines (borders) give you everything you need to reconstruct merged cells reliably. ## The key insight for merged cells When a table has visible borders, a merged cell is simply a region with **no internal dividing lines**. So the approach is: 1. Use `pdfplumber` to detect cells as rectangles bounded by ruling lines — a merged cell comes back as *one big rectangle* instead of several small ones. 2. Build the full grid from the **union of all cell edges** (every x and y boundary seen anywhere in the table). 3. For each cell, compute how many grid columns/rows its bounding box spans. Span > 1 → it was merged → reproduce with openpyxl's `merge_cells()`. This is why we bypass `table.extract()` (which collapses merges into `None`s without telling you the span) and work with `table.cells` geometry directly. ## The script ```python #!/usr/bin/env python3 """ Reconstruct Excel tables — including merged cells — from a PDF that was originally printed/exported from Excel. Usage: pip install pdfplumber openpyxl python pdf_to_excel.py input.pdf [output.xlsx] """ import pdfplumber from openpyxl import Workbook from openpyxl.styles import Alignment from openpyxl.utils import get_column_letter TOL = 2.0 # coordinate-matching tolerance, in PDF points # ------------------------------------------------------------- grid helpers def cluster(values, tol=TOL): """Merge nearly identical coordinates into their mean (line jitter).""" groups = [] for v in sorted(values): if groups and v - groups[-1][-1] <= tol: groups[-1].append(v) else: groups.append([v]) return [sum(g) / len(g) for g in groups] def build_grid(cells): """Column/row boundary lines = union of all cell edges.""" xs = cluster([c[0] for c in cells] + [c[2] for c in cells]) ys = cluster([c[1] for c in cells] + [c[3] for c in cells]) return xs, ys def grid_index(edges, value, tol=TOL): for i, e in enumerate(edges): if abs(e - value) <= tol: return i raise ValueError(f"no grid edge near {value}") # ------------------------------------------------------------- text helpers def words_in_cell(words, bbox, tol=1.0): """Words whose center lies inside the cell bbox, joined into lines.""" x0, top, x1, bottom = bbox inside = [ w for w in words if x0 - tol <= (w["x0"] + w["x1"]) / 2 <= x1 + tol and top - tol <= (w["top"] + w["bottom"]) / 2 <= bottom + tol ] inside.sort(key=lambda w: (w["top"], w["x0"])) lines, current, last_top = [], [], None for w in inside: if last_top is None or abs(w["top"] - last_top) <= 3: current.append(w) else: lines.append(current) current = [w] last_top = w["top"] if current: lines.append(current) return "\n".join( " ".join(w["text"] for w in sorted(line, key=lambda w: w["x0"])) for line in lines ).strip() def coerce(text): """Turn numeric-looking strings into numbers; return (value, fmt).""" if not text or "\n" in text: return text or None, None s = text.replace(",", "").strip() pct = s.endswith("%") if pct: s = s[:-1] try: v = float(s) except ValueError: return text, None if pct: return v / 100, "0.0%" return (int(v) if v.is_integer() else v), None # ------------------------------------------------------------- conversion def table_to_sheet(page, table, ws): cells = sorted(set(table.cells)) xs, ys = build_grid(cells) words = page.extract_words() merges = [] for x0, top, x1, bottom in cells: c0, c1 = grid_index(xs, x0), grid_index(xs, x1) r0, r1 = grid_index(ys, top), grid_index(ys, bottom) rowspan, colspan = r1 - r0, c1 - c0 text = words_in_cell(words, (x0, top, x1, bottom)) value, fmt = coerce(text) row, col = r0 + 1, c0 + 1 cell = ws.cell(row=row, column=col, value=value) if fmt: cell.number_format = fmt if rowspan > 1 or colspan > 1: # merged cell! ws.merge_cells(start_row=row, start_column=col, end_row=row + rowspan - 1, end_column=col + colspan - 1) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) merges.append(f"{get_column_letter(col)}{row}:" f"{get_column_letter(col + colspan - 1)}{row + rowspan - 1}") elif "\n" in (text or ""): cell.alignment = Alignment(wrap_text=True, vertical="top") # approximate column widths from PDF geometry for i in range(len(xs) - 1): ws.column_dimensions[get_column_letter(i + 1)].width = \ max(6, (xs[i + 1] - xs[i]) / 5) return merges def convert(pdf_path, xlsx_path, table_settings=None): wb = Workbook() wb.remove(wb.active) with pdfplumber.open(pdf_path) as pdf: for pno, page in enumerate(pdf.pages, 1): tables = page.find_tables(table_settings=table_settings or {}) if not tables: print(f"page {pno}: no tables found") continue for tno, tbl in enumerate(tables, 1): name = f"p{pno}" if len(tables) == 1 else f"p{pno}_t{tno}" ws = wb.create_sheet(name[:31]) merges = table_to_sheet(page, tbl, ws) print(f"page {pno} table {tno}: {len(tbl.cells)} cells -> " f"sheet '{name}', merged ranges: {merges or 'none'}") wb.save(xlsx_path) print(f"saved {xlsx_path}") if __name__ == "__main__": import sys src = sys.argv[1] dst = sys.argv[2] if len(sys.argv) > 2 else src.rsplit(".", 1)[0] + ".xlsx" convert(src, dst) ``` The script prints every detected merged range (e.g. `B2:D2`) so you can verify the merge detection against the original at a glance. ## Debugging the detection If cells/merges look wrong, visualize what pdfplumber sees before converting: ```python with pdfplumber.open("input.pdf") as pdf: im = pdf.pages[0].to_image(resolution=120) im.debug_tablefinder() im.save("debug.png") ``` This draws the detected grid and cells over the page — merged regions should show up as single large rectangles. ## Caveats and tuning - **This requires visible ruling lines.** Excel only prints gridlines if "Print gridlines" was enabled, or if the cells have explicit borders. If your tables are borderless, pass `table_settings={"vertical_strategy": "text", "horizontal_strategy": "text"}` — but then merged-cell detection becomes heuristic and much less reliable. - **Phantom lines from fills/rects:** if banded row shading creates spurious cell splits, try `{"vertical_strategy": "lines_strict", "horizontal_strategy": "lines_strict"}`. - **Broken grids** (one table detected as several): increase `snap_tolerance` / `join_tolerance` in `table_settings`. - **Not recoverable from PDF:** formulas (only computed values exist), cell colors/fonts (extractable with more work via `page.chars`, but usually not worth it), and dates come back as text since date parsing is ambiguous. - **Tables split across pages** become separate sheets; stitching them needs a page-break heuristic if that applies to you. Want me to extend it with anything specific — e.g., carrying over bold/fonts, handling `(1,234)`-style negatives, or stitching multi-page tables into one sheet?

Can we build a python script to convert PDF charts into exce...