$src = $args[0]
|
if (-not $src) { throw 'missing xlsx path' }
|
$sheetFile = 'sheet1.xml'
|
$tmp = Join-Path $env:TEMP ('crm_xlsx_' + [guid]::NewGuid())
|
New-Item -ItemType Directory -Path $tmp | Out-Null
|
$zip = Join-Path $tmp 'book.zip'
|
$dir = Join-Path $tmp 'unzipped'
|
Copy-Item -LiteralPath $src -Destination $zip
|
Expand-Archive -LiteralPath $zip -DestinationPath $dir -Force
|
|
[xml]$sstXml = Get-Content (Join-Path $dir 'xl\sharedStrings.xml') -Encoding UTF8
|
$shared = @()
|
foreach ($si in $sstXml.sst.si) {
|
$nodes = $si.SelectNodes('.//*[local-name()="t"]')
|
$texts = @()
|
foreach ($n in $nodes) { $texts += $n.InnerText }
|
$shared += ($texts -join '')
|
}
|
|
function Get-ColIndex($ref) {
|
$letters = ($ref -replace '[0-9]', '')
|
$n = 0
|
foreach ($ch in $letters.ToCharArray()) {
|
$n = $n * 26 + ([int][char]$ch - [int][char]'A' + 1)
|
}
|
return $n
|
}
|
|
function Get-CellText($cell) {
|
if ($cell.t -eq 's') {
|
$idx = [int]$cell.v
|
if ($idx -ge 0 -and $idx -lt $shared.Count) { return $shared[$idx] }
|
return ''
|
}
|
if ($cell.t -eq 'inlineStr') { return [string]$cell.is.t }
|
return [string]$cell.v
|
}
|
|
[xml]$sx = Get-Content (Join-Path $dir "xl\worksheets\$sheetFile") -Encoding UTF8
|
foreach ($row in @($sx.worksheet.sheetData.row | Select-Object -First 12)) {
|
$vals = New-Object string[] 29
|
foreach ($cell in $row.c) {
|
$i = (Get-ColIndex $cell.r) - 1
|
if ($i -ge 0 -and $i -lt 29) { $vals[$i] = (Get-CellText $cell) }
|
}
|
Write-Output (($vals | ForEach-Object { if ($_) { $_ } else { '' } }) -join "`t")
|
}
|
|
Remove-Item -LiteralPath $tmp -Recurse -Force
|
