使用 PowerShell 自动读写 Excel 表格
✨小透明・宸✨
2019-10-06 20:07:21

封面图:Pixiv ID: 23245036 「綾あか」 by 雄塩
写完这篇去看了一下,《摇曳百合》的十周年纪念 OVA 有资源啦!

一个前提:只有在电脑上安装了微软的 Office 软件才能使用 PowerShell 进行自动操作,对于 WPS Office 是无效的。

使用 PowerShell 对 Excel 表格进行操作,实际上并不是让 PowerShell 解析 *.xlsx 文件然后进行修改,而是在调用 Excel 的软件对文件进行操作——简单来说,就是用脚本模拟了在 Excel 里面的各种点点点。

打开和保存工作表

如图所示,打开一个 Excel 表格的时候,实际上是依次打开了以下三个东西:

  1. Excel 软件本体的一个实例
  2. 一个名为“工作簿 1”的工作簿(文件)
  3. 工作簿名为“Sheet1”的工作表

在 PowerShell 中对应的操作是:

# Excel软件本体
$Excel = New-Object -ComObject Excel.Application

# 新建或打开工作簿(文件)
$Workbook = $Excel.Workbooks.Add()
$Workbook = $Excel.Workbooks.Open($Path)

# 打开工作表,可以使用从1开始的序号或名称
$Sheet = $Workbook.Worksheets.Item(1)
$Sheet = $Workbook.Worksheets.Item('Sheet1')

# 新建工作簿时会默认添加一个名为Sheet1的工作表
# 也可以自己添加、重命名、删除工作表
$Sheet = $Workbook.Worksheets.Add() # 添加,新工作表的序号为1,其它工作表的序号向后顺延
$Sheet.Name = '新的工作表' # 重命名
$Sheet.Delete() # 删除,工作簿中至少要有一个工作表

# 保存工作簿
$Workbook.Save()

# 工作簿另存为
# 使用相对路径的话,根目录为“文档”文件夹
# 可以用常数指定类型,各个类型对应的常数可以参见:
# https://docs.microsoft.com/zh-CN/office/vba/api/excel.xlfileformat
$Workbook.SaveAs($Path) # 默认用xlsx格式保存
$Workbook.SaveAs($Path, 56) # 旧版Excel的xls格式
$Workbook.SaveAs($Path, 62) # 使用UTF-8编码的csv格式

# 退出Excel软件
$Excel.Quit()

为了便于自动化操作,可以在脚本中设定 Excel 软件的实例是否需要显示窗口(默认不显示)和警告提示框(默认显示)。“警告提示框”的典型例子是退出 Excel 时显示的“是否保存对○○的更改?”或者保存时的“是否覆盖○○?”,如果设定为不显示的话,就会默认选取“不保存”然后直接退出。

$Excel.Visible = $false
$Excel.DisplayAlerts = $true

修改单元格

工作表中的每个单元格也是一个对象,可以直接进行赋值,也可以通过它的属性获取单元格的值。

# 修改位于第1行第2列的单元格B1的内容
$Sheet.Cells.Item(1, 2) = 'B1单元格'

# 可以直接使用 PowerShell 中基本类型的变量
# 使用循环,将A1到A50的单元格用0-255的随机数填充
for ($i = 1; $i -le 50; $i++) {
    $Sheet.Cells.Item($i, 1) = (Get-Random -Minimum 0 -Maximum 256)
}

# 使用公式和函数
$Sheet.Cells.Item(1, 2) = '=A1+A2'
$Sheet.Cells.Item(2, 2) = '=AVERAGE(A1:A50)'

# 获取单元格内的值
$Sheet.Cells.Item(2, 2).Text

# 获取单元格内的原始公式或函数
$Sheet.Cells.Item(2, 2).Formula

Excel 中的单元格使用的是类似于 A1B2 这样的坐标,字母为列,数字为行,但是使用 PowerShell 脚本进行操作时,定位单元格时就是直接用数字指定行和列了。公式和函数中使用的还是单元格坐标而不是行和列,可以使用下面的函数将行和列转换为单元格坐标:

function Get-ExcelCellIndex([Int32]$Row, [Int32]$Column) {
    $ColumnIndex = ''
    do {
        $Column--
        $Remainder = $Column % 26
        $ColumnIndex += [Char](0x41 + $Remainder)
        $Column = ($Column - $Remainder) / 26
    } while ($Column -gt 0)
    return $ColumnIndex + $Row
}

Get-ExcelCellIndex 1 1 # 输出A1
Get-ExcelCellIndex 1 2 # 输出B1
Get-ExcelCellIndex 4 26 # 输出Z4
Get-ExcelCellIndex 5 27 # 输出AA5

如果需要遍历某一整行/列单元格的话,可以通过工作表的 UsedRange 来获取工作表的行/列数:

# 获取行数
$Sheet.UsedRange.Rows.Count
# 获取列数
$Sheet.UsedRange.Columns.Count

下面的录屏是使用 PowerShell 脚本打开 Excel、在工作表中编辑数据和使用函数、最后退出的全过程。这里不隐藏 Excel 软件界面只是为了演示,全程并没有在 Excel 软件中直接进行操作(注意鼠标的位置)。

实例:古诗词打印稿

这个例子是瞎扯的,主要是为了展示使用 PowerShell 操作整个微软 Office 全家桶的便利性_(:зゝ∠)_

某校正在开展传统文化宣传活动,内容之一是将用 A4 纸打印的古诗词名句张贴在校园各处。为此需要收集一批古诗词名句,以及将收集到的每条名句用 Word 制作成打印稿。

为了简化例子,这里就只考虑古诗(不考虑词)的打印稿样式,如图所示:

获取古诗词

古诗词的收集并不需要去从图书馆里翻出《唐诗三百首》之类的书籍然后一个一个字地录入,在网上有现成的接口“古诗词・一言 API”,访问 https://api.gushi.ci/all 就可以随机得到一条古诗词名句。这个接口使用 JSON 格式返回数据,示例:

{
    "content" : "飞来山上千寻塔,闻说鸡鸣见日升。",
    "origin" : "登飞来峰",
    "author" : "王安石",
    "category" : "古诗文-天气-太阳"
}

PowerShell 可以解析 JSON 数据,于是可以使用下面的代码获取一批(这里设为十条)古诗词,保存在数组 $PoemList 中:

$PoemList = @()
for ($i = 0; $i -lt 10; $i++) {
    # 使用Invoke-RestMethod直接得到解析后的JSON数据
    $Poem = Invoke-RestMethod 'https://api.gushi.ci/all'
    $PoemList += $Poem
}

整理古诗词,保存到 Excel 表格中

使用一个循环就可以将数组 $PoemList 保存古诗词保存到表格中。

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Add()
$Sheet = $Workbook.Worksheets.Item(1)
$Sheet.Name = '古诗词'

# 第一行用作表头
$Sheet.Cells.Item(1, 1) = '出处'
$Sheet.Cells.Item(1, 2) = '作者'
$Sheet.Cells.Item(1, 3) = '正文'

# 设定列宽
$Sheet.Columns.Item(1).ColumnWidth = 18
$Sheet.Columns.Item(2).ColumnWidth = 10
$Sheet.Columns.Item(3).ColumnWidth = 40

# 从第二行开始保存古诗词数据
$Line = 2
foreach ($Poem in $PoemList) {
    $Sheet.Cells.Item($Line, 1) = $Poem.origin
    $Sheet.Cells.Item($Line, 2) = $Poem.author
    $Sheet.Cells.Item($Line, 3) = $Poem.content
    $Line++
}

$Workbook.SaveAs('古诗词.xlsx')
$Excel.Quit()

用 Word 生成打印稿

虽然这里涉及到的是用 PowerShell 操作 Word,但是和操作 Excel 是差不多的:

# Word软件本体
$Word = New-Object -ComObject Word.Application

# 打开文档
$Document = $Word.Documents.Open($Path)

# 另存为文档
$Document.SaveAs($Path)

# 关闭文档,但不退出Word
$Document.Close()

# 退出Word软件
$Word.Quit()

由于 Word 文档不像 Excel 表格可以使用单元格直接定位修改位置,所以这里使用一个模板来生成打印稿:模板上留下四个% 表示的占位符(也可以使用其它符号),分别对应古诗词正文的前半句、后半句、作者、出处。在脚本运行时将占位符替换为对应的值,然后以古诗词的出处作为文件名,将文档另存为。

# 用于在Word文档中执行一次替换的函数
function Replace-WordDoc($Document, $FindText, $ReplaceText) {
    $Range = $Document.Content
    [void]$Range.Find.Execute($FindText)
    $Range.Text = $ReplaceText
}

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open('C:\Users\Admin\Documents\古诗词.xlsx')
$Sheet = $Workbook.Worksheets.Item('古诗词')

$Word = New-Object -ComObject Word.Application

# 读取表格中的每一行数据(表头除外),在文档中进行替换,然后另存为
for ($i = 2; $i -le $Sheet.UsedRange.Rows.Count; $i++) {
    $Origin = $Sheet.Cells.Item($i, 1).Text
    $Author = $Sheet.Cells.Item($i, 2).Text
    $Content = $Sheet.Cells.Item($i, 3).Text.Split(',。')
    $Document = $Word.Documents.Open('C:\Users\Admin\Documents\古诗词打印稿模板.docx')

    Replace-WordDoc $Document '%0%' $Content[0]
    Replace-WordDoc $Document '%1%' $Content[1]
    Replace-WordDoc $Document '%2%' $Author
    Replace-WordDoc $Document '%3%' $Origin

    $Document.SaveAs('C:\Users\Admin\Documents\古诗词打印稿\' + $Origin + '.docx')
    $Document.Close()
}

$Excel.Quit()
$Word.Quit()

脚本执行完成后,就可以得到批量生成的打印稿了。

如果把 Excel 表格中的内容以 CSV 格式保存的话,就可以使用 PowerShell 的 Import-Csv 命令直接读取数据,不需要在脚本中频繁调用 Excel,提高运行速度。

本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。
本文作者:✨小透明・宸✨
本文链接:https://akarin.dev/2019/10/06/powershell-automated-excel/
chevron_left 上一篇 下一篇 chevron_right