| 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 dataxl/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"])
ws.append(["Pad", 5, 4.00, "=B3*C3"])
ws["D4"] = "=SUM(D2:D3)"
wb.save("order.xlsx")
Edit an existing workbook in place
from openpyxl import load_workbook
wb = load_workbook("existing.xlsx")
ws = wb["Sheet1"]
ws["B5"] = 42
ws.insert_rows(2); ws.delete_cols(7)
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"
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
