From office-docs
Use when reading, writing, editing, or generating Microsoft Excel workbooks (.xlsx/.xls/.csv) on Windows - drives Excel.Application via PowerShell COM to read/write cells and ranges, set formulas, manage multiple sheets, recalculate, or export to PDF
How this skill is triggered — by the user, by Claude, or both
Slash command
/office-docs:excel-com-powershellThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Drive the installed Microsoft Excel application through COM
Drive the installed Microsoft Excel application through COM
(New-Object -ComObject Excel.Application) to read and write .xlsx/.xls
workbooks with real formula recalculation and exact formatting.
Core principle: Bulk reads/writes go through Range.Value2 as a 2D array —
touching cells one at a time over COM is extremely slow. And, like all Office
COM, you MUST quit and release every reference in finally or you leak
EXCEL.EXE processes.
REQUIRED BACKGROUND: Read office-docs:office-com-cleanup for the release
pattern. Read office-docs:office-docs-overview for when to prefer a library
(Excel COM is especially unreliable under service accounts / Session 0).
WPS note: On a machine with WPS Office (and no Microsoft Office),
New-Object -ComObject Excel.Applicationmay launch WPS Spreadsheets instead — WPS registers under the Microsoft ProgIDs. Check$excel.Pathto confirm. WPS covers common automation but implements only a subset of the API, and its hidden process iset.exe, notEXCEL.EXE. Seeoffice-docs:office-docs-overview.
.xlsx, .xls, .csvFor headless/CI or pure tabular data, prefer openpyxl/ClosedXML — see
office-docs:office-docs-overview.
| Name | Value | Meaning |
|---|---|---|
xlOpenXMLWorkbook | 51 | Save as .xlsx |
xlOpenXMLWorkbookMacroEnabled | 52 | .xlsm |
xlExcel8 | 56 | Legacy .xls |
xlCSV | 6 | .csv |
xlTypePDF | 0 | PDF export type for ExportAsFixedFormat |
xlCalculationManual | -4135 | Manual calc mode |
xlCalculationAutomatic | -4105 | Automatic calc mode |
xlUp | -4162 | Direction for .End() (find last row) |
xlToLeft | -4159 | Direction for .End() |
$xlOpenXMLWorkbook = 51
$xlCSV = 6
$ErrorActionPreference = 'Stop'
$excel = $null
$wb = $null
try {
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$excel.ScreenUpdating = $false # big speedup for writes
$path = [System.IO.Path]::GetFullPath('C:\data\book.xlsx') # ABSOLUTE
$wb = $excel.Workbooks.Open($path)
$ws = $wb.Worksheets.Item(1)
# ... work ...
$wb.Save()
}
finally {
if ($wb) { $wb.Close($false) }
if ($excel) { $excel.Quit() }
foreach ($o in @($ws, $wb, $excel)) {
if ($o) { [void][Runtime.InteropServices.Marshal]::ReleaseComObject($o) }
}
[GC]::Collect(); [GC]::WaitForPendingFinalizers()
}
| Goal | Code |
|---|---|
| New workbook | $wb = $excel.Workbooks.Add() |
| Open existing | $wb = $excel.Workbooks.Open($absPath) |
| Sheet by index (1-based) | $ws = $wb.Worksheets.Item(1) |
| Sheet by name | $ws = $wb.Worksheets.Item("Data") |
| Add sheet | $ws = $wb.Worksheets.Add() |
| Read one cell | $ws.Cells.Item($r,$c).Value2 |
| Write one cell | $ws.Cells.Item($r,$c).Value2 = 42 |
| Read whole used range | $ws.UsedRange.Value2 (2D array) |
| Last used row | $ws.Cells.Item($ws.Rows.Count,1).End(-4162).Row |
| Set a formula | $ws.Cells.Item($r,$c).Formula = "=SUM(A1:A10)" |
| Recalculate | $excel.Calculate() |
| Autofit columns | $ws.UsedRange.EntireColumn.AutoFit() |
| Save as xlsx | $wb.SaveAs($path, 51) |
| Save as csv | $wb.SaveAs($path, 6) |
| Close (no save) | $wb.Close($false) |
Value2is the fast, locale-independent accessor (no currency/date COM wrapping). Prefer it over.Valueand.Text.
$data = $ws.UsedRange.Value2 # [object[,]] 1-based on BOTH dimensions
$rows = $data.GetLength(0)
$cols = $data.GetLength(1)
for ($r = 1; $r -le $rows; $r++) {
$line = for ($c = 1; $c -le $cols; $c++) { $data[$r, $c] }
Write-Output ($line -join "`t")
}
When
UsedRangeis a single cell,Value2is a scalar, not an array. Guard withif ($data -is [array]).
$lastRow = $ws.Cells.Item($ws.Rows.Count, 1).End(-4162).Row # -4162 = xlUp
$data = @(
@("Name","Score"),
@("Ada", 99),
@("Alan", 98)
)
$rows = $data.Count
$cols = $data[0].Count
# Build a [,] array Excel can consume in a single assignment
$arr = New-Object 'object[,]' $rows, $cols
for ($r = 0; $r -lt $rows; $r++) {
for ($c = 0; $c -lt $cols; $c++) { $arr[$r, $c] = $data[$r][$c] }
}
$start = $ws.Cells.Item(1, 1)
$end = $ws.Cells.Item($rows, $cols)
$range = $ws.Range($start, $end)
$range.Value2 = $arr # single COM round-trip — fast
Writing cell-by-cell in a loop is 100x+ slower. Always batch via a range.
CRITICAL — PowerShell
[,]index parsing pitfall: In$arr[$r + 1, 0], the,is the array-construction operator, so PowerShell parses this as$r + (1, 0)— array concatenation — and throws[System.Object[]] 不包含名为 "op_Addition" 的方法. Always compute the row index into a separate variable first:$ri = $r + 1 # compute once $arr[$ri, 0] = $ri # then index with a plain integerThis applies to ALL
object[,]indexing in PowerShell, not just Excel scripts.
CRITICAL — Mixed-type PS arrays cause
InvalidCastException: When writing cell-by-cell from a PowerShellobject[]that contains mixed types (e.g.@(1, "600519", "贵州茅台", 1682.00, ...)), COM interop may fail to coerceInt32orDoubleelements to the internal Variant type, throwingInvalidCastException. Fix: use an all-string array, or cast explicitly at the write site:$ws.Cells.Item($r, $c).Value2 = [string]$row[$c - 1] # always safeThis avoids the interop cast failure while still letting Excel re-parse numeric strings into numbers for formatting/calculation.
CRITICAL — Excel auto-interprets string values: When writing strings via
Range.Value2, Excel automatically converts values that look like numbers, dates, or percentages."002594"becomes2594(drops leading zeros);"+0.93%"becomes0.0093(parsed as percentage). To preserve exact text:# 1. Prefix with apostrophe when building the array (forces text storage) $arr[$r, $c] = "'" + $code # "'002594" stored as text, leading zeros kept # 2. Also set NumberFormat to '@' (Text) on the target column range $ws.Range($ws.Cells(2, 2), $ws.Cells($n, 2)).NumberFormat = '@'Both steps are recommended: the apostrophe ensures the raw value is stored as text at write time, and the
NumberFormat = '@'prevents Excel from re-interpreting the value if a user double-clicks the cell later. This pattern is essential for stock codes, phone numbers, ID numbers, and any numeric-looking string that must not lose leading zeros or formatting.
$ws.Cells.Item(4, 2).Formula = "=SUM(B2:B3)"
$excel.Calculate() # cached value now correct
$result = $ws.Cells.Item(4, 2).Value2
$wb.Save() # in place
$wb.SaveAs("C:\out\book.xlsx", 51) # 51 = xlOpenXMLWorkbook
$wb.SaveAs("C:\out\data.csv", 6) # 6 = xlCSV (active sheet only)
$ws.ExportAsFixedFormat(0, "C:\out\sheet.pdf") # 0 = xlTypePDF
.ps1 file encoding — UTF-8 BOM requiredPowerShell requires UTF-8 with BOM for .ps1 files containing non-ASCII
characters (Chinese paths, headers with multi-byte chars, etc.). Without BOM,
multi-byte characters are garbled at parse time — the file still runs but path
strings become corrupted.
# WRONG: Set-Content writes UTF-8 without BOM by default
$script | Set-Content -Path $path -Encoding UTF8
# RIGHT: Explicit BOM
$utf8Bom = New-Object System.Text.UTF8Encoding $true
[System.IO.File]::WriteAllText($scriptPath, $content, $utf8Bom)
Or from Python: open(path, 'w', encoding='utf-8-sig').
Symptom: 无法找到 "C:\...\纭欢鏃ュ織\..." — garbled Chinese in file paths.
-f format operator with cell valuesCell values may contain { or } characters (e.g. [Yes/No] parsed as format
placeholder). PowerShell's -f operator will throw FormatError.
# BROKEN: cell value "{some}" breaks -f
"Col{0}: {1}" -f $c, $cellValue
# FIX: use string concatenation
"Col" + $c + ": " + $cellValue
PowerShell interprets $var:text as a variable with drive/provider scope.
Use $() subexpression to disambiguate:
# BROKEN: "$hh:$mm:$ss" → InvalidVariableReferenceWithDrive
# FIX:
$ts = "$($hh):$($mm):$($ss)"
If a previous run leaked EXCEL.EXE or et.exe (WPS), the output file stays
locked. Solutions:
# Option 1: Kill known COM orphans before starting (last-resort recovery)
Get-Process et, EXCEL -ErrorAction SilentlyContinue | Stop-Process -Force
# Option 2: Use a unique output filename each run
$csv = Join-Path $PSScriptRoot "output_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
See office-docs:office-com-cleanup for the proper release pattern that
prevents this.
VS Code terminal may "simplify" long one-liners internally, which can corrupt
array indexing ($data[$r, 2] may return wrong values like the "Time" header
string instead of row data). Prefer .ps1 script files over inline commands
longer than ~500 characters.
$PSScriptRoot for portable pathsAlways resolve paths relative to the script location rather than hardcoding:
$xlsx = Join-Path $PSScriptRoot 'data.xlsx'
Or use [Environment]::GetFolderPath('Desktop') / Resolve-Path for
user-profile-relative paths.
scripts/Get-ExcelData.ps1 — read a sheet (or all sheets) to objects/CSV.scripts/Set-ExcelData.ps1 — write a 2D array / CSV into a sheet in one call.scripts/Convert-ExcelToPdf.ps1 — export a workbook/sheet to PDF.All scripts implement the full open/try/finally/release lifecycle.
Visible = $false, ScreenUpdating = $false, DisplayAlerts = $false.UsedRange.Value2; write with a single Range.Value2 = [,].Calculation = -4135 (manual), write, then
$excel.Calculate() and restore -4105.| Mistake | Symptom | Fix |
|---|---|---|
| Cell-by-cell loop | Painfully slow | Batch via Range.Value2 = [,] |
Value2 array assumed 0-based | Off-by-one / null | UsedRange.Value2 is 1-based both dims |
Single-cell UsedRange | Indexing a scalar fails | Guard if ($data -is [array]) |
| Relative path | File saved in wrong place | GetFullPath() first |
| Not releasing COM | EXCEL.EXE leaks, file locked | finally + ReleaseComObject + GC |
| Running in CI/service | Flaky COM errors | Use openpyxl/ClosedXML instead |
| Reading formula expecting value | Got =SUM(...) string | Use .Value2 after $excel.Calculate() |
$arr[$r+1,0] with computed index | op_Addition / array-concat error | $ri=$r+1; $arr[$ri,0] — compute index into a variable first |
Writing strings that look like numbers via Value2 | Leading zeros dropped, "+0.93%" → 0.0093 | Prefix with ' and set NumberFormat = '@' on the column |
Non-ASCII .ps1 saved UTF-8 without BOM | Parser error on lines with Chinese/CJK text under PS 5.1 | Save .ps1 as UTF-8 with BOM — see office-docs:word-com-powershell → Script File Encoding |
npx claudepluginhub g-yong/office-docs --plugin office-docsGuides creation, editing, and verification of skills for AI coding agents using test-driven development with subagent scenarios. Use when authoring or debugging skills.