37 lines
3.2 KiB
PowerShell
37 lines
3.2 KiB
PowerShell
param([Parameter(Mandatory=$true)][string]$Source,[Parameter(Mandatory=$true)][string]$Output)
|
|
$ErrorActionPreference='Stop'
|
|
Add-Type -AssemblyName System.IO.Compression.FileSystem
|
|
$absolute=(Resolve-Path -LiteralPath $Source).Path
|
|
$before=(Get-FileHash -LiteralPath $absolute -Algorithm SHA256).Hash.ToLowerInvariant()
|
|
$zip=[IO.Compression.ZipFile]::OpenRead($absolute)
|
|
function Read-XmlEntry([string]$Name){
|
|
$entry=$zip.GetEntry($Name);if($null -eq $entry){throw "Missing workbook part: $Name"}
|
|
$stream=$entry.Open();$settings=[Xml.XmlReaderSettings]::new();$settings.DtdProcessing=[Xml.DtdProcessing]::Prohibit;$settings.XmlResolver=$null
|
|
$reader=[Xml.XmlReader]::Create($stream,$settings)
|
|
try{$doc=[Xml.XmlDocument]::new();$doc.XmlResolver=$null;$doc.Load($reader);return ,$doc}finally{$reader.Dispose();$stream.Dispose()}
|
|
}
|
|
try{
|
|
$book=Read-XmlEntry 'xl/workbook.xml';$shared=Read-XmlEntry 'xl/sharedStrings.xml'
|
|
$ns=[Xml.XmlNamespaceManager]::new($shared.NameTable);$ns.AddNamespace('m','http://schemas.openxmlformats.org/spreadsheetml/2006/main')
|
|
$strings=@($shared.SelectNodes('/m:sst/m:si',$ns)|ForEach-Object {($_.SelectNodes('.//m:t',$ns)|ForEach-Object {$_.InnerText}) -join ''})
|
|
$sheet=Read-XmlEntry 'xl/worksheets/sheet1.xml';$sn=[Xml.XmlNamespaceManager]::new($sheet.NameTable);$sn.AddNamespace('m','http://schemas.openxmlformats.org/spreadsheetml/2006/main')
|
|
$rows=[Collections.Generic.List[object]]::new();$formulas=0;$hidden=0
|
|
foreach($row in $sheet.SelectNodes('/m:worksheet/m:sheetData/m:row',$sn)){
|
|
if($row.GetAttribute('hidden') -eq '1'){$hidden++}
|
|
$cells=[ordered]@{}
|
|
foreach($cell in $row.SelectNodes('m:c',$sn)){
|
|
$key=$cell.GetAttribute('r') -replace '[0-9]','';$valueNode=$cell.SelectSingleNode('m:v',$sn);$value=if($valueNode){$valueNode.InnerText}else{''};$type=$cell.GetAttribute('t')
|
|
if($type -eq 's'){$value=$strings[[int]$value]}elseif($type -eq 'inlineStr'){$value=($cell.SelectNodes('.//m:t',$sn)|ForEach-Object {$_.InnerText}) -join ''}
|
|
if($cell.SelectSingleNode('m:f',$sn)){$formulas++}
|
|
if($value -ne ''){$cells[$key]=$value}
|
|
}
|
|
if($cells.Count -gt 0){$rows.Add([ordered]@{row=[int]$row.GetAttribute('r');cells=$cells})}
|
|
}
|
|
$sheets=@($book.SelectNodes('//*[local-name()="sheet"]')|ForEach-Object {$_.GetAttribute('name')})
|
|
$result=[ordered]@{source_name=[IO.Path]::GetFileName($absolute);sha256=$before;sheets=$sheets;dimension=$sheet.SelectSingleNode('/m:worksheet/m:dimension',$sn).GetAttribute('ref');populated_rows=$rows.Count;formula_cells=$formulas;hidden_rows=$hidden;external_links=@($zip.Entries|Where-Object {$_.FullName -like 'xl/externalLinks/*'}).Count;rows=$rows.ToArray()}
|
|
$json=$result|ConvertTo-Json -Depth 12
|
|
[IO.File]::WriteAllText($Output,$json,[Text.UTF8Encoding]::new($false))
|
|
[ordered]@{sha256=$before;sheets=$sheets;dimension=$result.dimension;populated_rows=$rows.Count;formula_cells=$formulas;hidden_rows=$hidden;sample=@($rows|Select-Object -First 9)}|ConvertTo-Json -Depth 8
|
|
}finally{$zip.Dispose()}
|
|
if((Get-FileHash -LiteralPath $absolute -Algorithm SHA256).Hash.ToLowerInvariant() -ne $before){throw 'Source changed during read'}
|