封面图：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 中的单元格使用的是类似于 A1、B2 这样的坐标，字母为列，数字为行，但是使用 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 国际许可协议进行许可。不允许内容农场类网站、CSDN 用户和微信公众号转载。
本文作者：✨小透明・宸✨
本文链接：https://akarin.dev/2019/10/06/powershell-automated-excel/