xlsx-to-markdown

name	xlsx-to-markdown
compatibility	Extraction is .NET-native PowerShell. Creating or editing workbooks requires Python with openpyxl and pandas; the recalculation and formula-error scan requires LibreOffice (soffice).
description	Read, create, and edit XLSX (Excel) files. Recipe 1: convert XLSX to Markdown tables via .NET-native ZIP/XML parsing in PowerShell — no Excel COM, ImportExcel, or Python required. Beyond Extraction: create new workbooks and edit existing ones with openpyxl + pandas (no Excel install), the cardinal rule "write Excel formulas, never hardcoded computed values", header formatting / freeze panes / number formats, and a recalc + error-scan pass via LibreOffice headless that catches every #REF! / #DIV/0! / #VALUE! / #N/A / #NAME?. USE FOR: convert xlsx to markdown, Excel to markdown, xlsx to md, parse Excel in PowerShell, read xlsx without Excel, Excel attachment, create xlsx, write xlsx with formulas, openpyxl, pandas to_excel, edit xlsx, recalc xlsx, scan xlsx for formula errors, #REF! in xlsx, financial model in Excel, freeze header row. DO NOT USE FOR: Excel charts and pivot tables, Excel COM automation, files requiring style preservation that openpyxl drops.

XLSX to Markdown Conversion

When to Use

• Converting Excel attachments to text for downstream analysis
• Bulk conversion of many XLSX files (e.g., email attachment processing)
• No Excel installation, ImportExcel module, or Python available
• XLSX files contain tabular data (not charts/images)

How XLSX Files Work

XLSX is a ZIP archive containing XML files:

• xl/sharedStrings.xml — shared string table (most cell text is stored here)
• xl/worksheets/sheet1.xml, sheet2.xml, etc. — worksheet data
• xl/workbook.xml — workbook metadata (sheet names)
• Cell references use Excel notation: A1, B2, AA100 (column letters + row number)
• Cell types: s = shared string index, n or no type = number, inline string = <is><t>...</t></is>

Recipe: .NET Native XLSX Parsing

Complete Function (PS 5.1 Compatible)

Add-Type -AssemblyName System.IO.Compression.FileSystem

function Convert-XlsxToMarkdown {
    param([string]$XlsxPath)
    $mdPath = $XlsxPath -replace '\.xlsx$', '.md'
    if (Test-Path $mdPath) { return 'SKIP' }
    try {
        $zip = [System.IO.Compression.ZipFile]::OpenRead($XlsxPath)
        
        # Step 1: Read shared strings
        $sharedStrings = @()
        $ssEntry = $zip.Entries | Where-Object { $_.FullName -eq 'xl/sharedStrings.xml' }
        if ($ssEntry) {
            $stream = $ssEntry.Open()
            $reader = New-Object System.IO.StreamReader($stream)
            $ssXml = [xml]$reader.ReadToEnd()
            $reader.Close(); $stream.Close()
            $ns = New-Object System.Xml.XmlNamespaceManager($ssXml.NameTable)
            $ns.AddNamespace('s', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main')
            $siNodes = $ssXml.SelectNodes('//s:si', $ns)
            foreach ($si in $siNodes) {
                $text = ''
                $tNodes = $si.SelectNodes('.//s:t', $ns)
                foreach ($t in $tNodes) { $text += $t.InnerText }
                $sharedStrings += $text
            }
        }
        
        # Step 2: Process each worksheet
        $sheetEntries = $zip.Entries | Where-Object {
            $_.FullName -match '^xl/worksheets/sheet\d+\.xml$'
        } | Sort-Object Name
        if ($sheetEntries.Count -eq 0) { $zip.Dispose(); return 'NO_SHEETS' }
        
        $sb = New-Object System.Text.StringBuilder
        $sheetNum = 0
        
        foreach ($sheetEntry in $sheetEntries) {
            $sheetNum++
            $stream = $sheetEntry.Open()
            $reader = New-Object System.IO.StreamReader($stream)
            $sheetXml = [xml]$reader.ReadToEnd()
            $reader.Close(); $stream.Close()
            
            $ns2 = New-Object System.Xml.XmlNamespaceManager($sheetXml.NameTable)
            $ns2.AddNamespace('s', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main')
            
            $rows = $sheetXml.SelectNodes('//s:sheetData/s:row', $ns2)
            if ($rows.Count -eq 0) { continue }
            
            if ($sheetNum -gt 1) { [void]$sb.AppendLine() }
            [void]$sb.AppendLine("## Sheet $sheetNum")
            [void]$sb.AppendLine()
            
            # Step 3: Parse cell references and values
            $allRows = New-Object System.Collections.Generic.List[string[]]
            $maxCols = 0
            
            foreach ($row in $rows) {
                $cells = $row.SelectNodes('s:c', $ns2)
                $rowData = @{}
                $maxColIdx = 0
                
                foreach ($cell in $cells) {
                    $ref = $cell.GetAttribute('r')
                    # Convert column letters to index (A=0, B=1, ..., AA=26)
                    $colLetters = ($ref -replace '\d+', '')
                    $colIdx = 0
                    for ($ci = 0; $ci -lt $colLetters.Length; $ci++) {
                        $colIdx = $colIdx * 26 + ([int][char]$colLetters[$ci] - 64)
                    }
                    $colIdx-- # zero-based
                    if ($colIdx -gt $maxColIdx) { $maxColIdx = $colIdx }
                    
                    # Resolve cell value
                    $cellType = $cell.GetAttribute('t')
                    $vNode = $cell.SelectSingleNode('s:v', $ns2)
                    $val = ''
                    if ($vNode) {
                        if ($cellType -eq 's' -and $sharedStrings.Count -gt 0) {
                            $ssIdx = [int]$vNode.InnerText
                            if ($ssIdx -lt $sharedStrings.Count) {
                                $val = $sharedStrings[$ssIdx]
                            }
                        } else {
                            $val = $vNode.InnerText
                        }
                    }
                    # Check for inline string
                    $isNode = $cell.SelectSingleNode('s:is/s:t', $ns2)
                    if ($isNode) { $val = $isNode.InnerText }
                    
                    $rowData[$colIdx] = $val
                }
                
                if ($maxColIdx + 1 -gt $maxCols) { $maxCols = $maxColIdx + 1 }
                $rowArray = New-Object string[] ($maxColIdx + 1)
                foreach ($key in $rowData.Keys) { $rowArray[$key] = $rowData[$key] }
                $allRows.Add($rowArray)
            }
            
            if ($allRows.Count -eq 0) { continue }
            
            # Step 4: Build Markdown table
            $isFirst = $true
            foreach ($r in $allRows) {
                # Pad to max columns
                if ($r.Length -lt $maxCols) {
                    $padded = New-Object string[] $maxCols
                    for ($pi = 0; $pi -lt $r.Length; $pi++) { $padded[$pi] = $r[$pi] }
                    $r = $padded
                }
                $cleaned = $r | ForEach-Object {
                    if ($_) { $_ -replace '\|', '\|' } else { '' }
                }
                [void]$sb.AppendLine('| ' + ($cleaned -join ' | ') + ' |')
                if ($isFirst) {
                    $sep = $r | ForEach-Object { '---' }
                    [void]$sb.AppendLine('| ' + ($sep -join ' | ') + ' |')
                    $isFirst = $false
                }
            }
        }
        
        $zip.Dispose()
        $result = $sb.ToString().Trim()
        if ($result.Length -gt 0) {
            [System.IO.File]::WriteAllText($mdPath, $result, [System.Text.Encoding]::UTF8)
            return 'OK'
        }
        return 'EMPTY'
    } catch {
        return "ERR: $_"
    }
}

Batch Conversion

$xlsxFiles = Get-ChildItem $BasePath -Recurse -Filter '*.xlsx' -EA 0
$ok = 0; $fail = 0; $skip = 0; $count = 0
foreach ($f in $xlsxFiles) {
    $count++
    $fi = New-Object System.IO.FileInfo($f.FullName)
    if ($fi.Length -gt 5MB) { $skip++; continue } # Size cap
    $r = Convert-XlsxToMarkdown -XlsxPath $f.FullName
    switch ($r) {
        'OK' { $ok++ }
        'SKIP' { $skip++ }
        default { $fail++ }
    }
    if ($count % 50 -eq 0) {
        Write-Output "Progress: $count / $($xlsxFiles.Count) | OK: $ok"
    }
}

Pitfalls and Lessons Learned

File Size Cap (5MB)

Large XLSX files (5MB+) with many shared strings can cause the XML parser to consume >1.5GB RAM and take minutes to process. Add a size check:

$fi = New-Object System.IO.FileInfo($XlsxPath)
if ($fi.Length -gt 5MB) { return 'TOO_LARGE' }

Sort files smallest-first to process the majority quickly.

Shared Strings Are Required

Most XLSX generators store cell text in xl/sharedStrings.xml and reference it by index. If you skip shared strings, most cells appear empty or show numeric indices instead of text.

Column Letter Conversion

Excel uses letters for columns: A-Z (1-26), then AA-AZ (27-52), BA-BZ, etc. The conversion formula:

$colIdx = 0
for ($ci = 0; $ci -lt $colLetters.Length; $ci++) {
    $colIdx = $colIdx * 26 + ([int][char]$colLetters[$ci] - 64)
}
$colIdx-- # zero-based

Sparse Rows

XLSX only stores non-empty cells. A row with data in columns A and E will only have 2 <c> elements, not 5. You must pad the array to maxCols to align the Markdown table.

Pipe Characters in Cell Content

Cell values containing | will break Markdown table formatting. Escape them:

$val = $val -replace '\|', '\|'

Date and Number Formatting

XLSX stores dates as serial numbers (e.g., 44927 = 2023-01-01) and formatted numbers as raw values. This basic parser does not apply number formats — dates appear as integers. For date conversion, check the cell's style reference against xl/styles.xml format codes.

PS 5.1 Compatibility

All code uses New-Object instead of ::new() and avoids for-in-expression syntax to ensure Windows PowerShell 5.1 compatibility (required when combining with Outlook COM scripts).

File Lock Errors

When multiple conversion processes run simultaneously, WriteAllText may fail with "file in use" errors if two processes try to write the same .md file. This is harmless — the file was already written by the other process. Log and continue.

Beyond Extraction: Create and Edit XLSX with Formulas

The ZIP/XML reader above is one-way. When the task is to produce or modify an XLSX (add a sheet, write formulas, fix a value, add formatting), use openpyxl for cell-level work and pandas for bulk data. Both install via pip; no Excel installation required.

uv pip install openpyxl pandas

The cardinal rule: write formulas, not computed values

A spreadsheet's whole point is recalculation. The most common failure mode when an assistant generates a sheet is computing totals / averages / growth rates in Python and writing the result as a hardcoded number. When the user changes an input, the totals don't update. Always write the formula and let Excel recalculate.

import openpyxl
wb = openpyxl.Workbook(); ws = wb.active
ws.append(["Item", "Qty", "Price", "Line total"])
ws.append(["Pen", 3, 2.50, "=B2*C2"])      # ✅ formula — reacts to changes
ws.append(["Pad", 5, 4.00, "=B3*C3"])
ws["D4"] = "=SUM(D2:D3)"                       # ✅ not Python's sum()
# ws["D4"] = 22.5                              # ❌ hardcoded total; breaks the moment a price changes
wb.save("order.xlsx")

Edit an existing workbook in place

from openpyxl import load_workbook
wb = load_workbook("existing.xlsx")          # preserves formulas, formatting, charts
ws = wb["Sheet1"]
ws["B5"] = 42                                # change a value
ws.insert_rows(2); ws.delete_cols(7)         # structural edits
new = wb.create_sheet("Notes")
new["A1"] = "Generated 2026-05-19"
wb.save("existing.xlsx")

Trap: load_workbook(..., data_only=True) reads the last cached calculated values and strips formulas on save. Use it only for reading; never for round-trip edits.

Recalculate formulas (openpyxl does not compute)

openpyxl writes formula strings but never evaluates them. The saved file's cached values stay stale until something opens it. To force recalculation in CI / a script:

# Requires LibreOffice (winget install TheDocumentFoundation.LibreOffice)
soffice --headless --calc --convert-to xlsx --outdir recalc\ in.xlsx

LibreOffice opens the file, recomputes every formula, and writes the result. Then scan for errors:

from openpyxl import load_workbook
wb = load_workbook("recalc/in.xlsx", data_only=True)
ERRORS = {"#REF!", "#DIV/0!", "#VALUE!", "#N/A", "#NAME?", "#NULL!", "#NUM!"}
found = []
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for c in row:
            if isinstance(c.value, str) and c.value in ERRORS:
                found.append((ws.title, c.coordinate, c.value))
if found:
    raise SystemExit(f"Formula errors: {found}")

Fix every #REF! / #DIV/0! before declaring the workbook ready. A #REF! is a deleted-row/column footprint; #DIV/0! means a denominator hits zero and needs an IFERROR wrapper.

Common formatting (when the user asks for it)

from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
ws["A1"].font = Font(bold=True, color="FFFFFF")
ws["A1"].fill = PatternFill("solid", fgColor="1F4E79")
ws["A1"].alignment = Alignment(horizontal="center")
ws.column_dimensions["A"].width = 22
ws.freeze_panes = "A2"                       # freeze header row

For data analysts: use pandas (df.to_excel("out.xlsx", index=False)) when the deliverable is just data; switch to openpyxl when formulas, formatting, or multiple sheets matter.

Verification checklist before handing off a workbook

• Every calculation is a formula, not a hardcoded value.
• LibreOffice recalc pass ran; no #REF! / #DIV/0! / #VALUE! remain.
• Currency / percentage / date columns have explicit number formats (ws.cell(...).number_format = "#,##0.00").
• Header row is frozen and bold (matches the convention of pre-existing templates when editing one).
• Test by changing an input cell in Excel/LibreOffice and confirming dependent cells update.