统计excel所有子表的总行数

  1. cd D:\fuzz\xl\worksheets
  2. $file_list = Get-ChildItem *.xml*
  4. $summ = 0
  5. foreach ($i in $file_list) {
  6.     $xmldata = [xml](Get-Content $i.name)
  7.     $xmldata.worksheet.dimension.ref -match '(?<=(:\S))\d*'
  8.     $summ += $Matches.0
  9. }
  10. echo $summ
  • 首先将excel文件的后缀xlsx改成zip，然后解压，解压后可以看到有下面的结构，其实xlsx格式的文件就是一系列xml文件的集合体
  • image.png我们需要的表格的内容在xl文件夹中image.png
  • 在powershell中解析xml文本，get-content获取xml的内容，文如其名xml的内容类似与网页的html是一个个节点构成的，有子节点，叶子节点，
  • image.png
  • [xml]将读取出来的内容序列化，这样就可以通过.的方式访问节点。
  • 记有表格行数的内容在worksheet.dimension.ref中，返回的是单元格的文本如A2:F20，通过正则表达式将最后两位数匹配出来

完整的代码如下

  1. function RowCount ($Path) {
  2.     Copy-Item -Path $Path -Destination D:\test_field
  3.     $Path -match '[^\\]+[A-Z]$'
  4.   $name = $Matches.0
  5.     Rename-Item -Path "D:\test_field\$name" -NewName test.zip
  6.     New-Item D:\test_field\testing -Type Directory
  7.     Expand-Archive -Path D:\test_field\test.zip -DestinationPath D:\test_field\testing\
  9.   cd D:\test_field\testing\xl\worksheets
  11.     $file_list = Get-ChildItem  *.xml*
  13.     $summ = 0
  14.     foreach ($i in $file_list) {
  15.         $xmldata = [xml](Get-Content $i.name)
  16.         $xmldata.worksheet.dimension.ref -match '(?<=(:\S))\d*'
  17.         $summ += $Matches.0 - 2
  18.         echo $i.name, ($Matches.0 - 2)
  19.     }
  20.     echo $summ
  23.     Get-ChildItem D:\test_field\ | Remove-Item -Recurse
  24. }
  • 这里-2是因为有表头和表尾，不在内容行的统计范围内
  • [^\\]+[A-Z]$正则表达式用于匹配文件名和格式
  • 流程是先将文件复制到一个临时的空文件夹中，然后将文件更名为压缩包，解压，来到文件夹中存放worksheet的文件夹中，将所有xml文件读取，并输出每个子表的行数和总计，最后将临时文件夹中的所有文件删除