用 Python 操作 Excel 文档（基础）
<h2>Excel表格的基本结构</h2>
<p>一个Excel表格文件，又叫做一个<strong>工作簿（Workbook）</strong>。</p>
<p>一个工作簿中包含一个或多个<strong>工作表（Worksheet）</strong>。 </p>
<p>在工作薄页面的左下方可以进行工作表的切换和增删。</p>
<p><img src=“https://assets.moshanghua.net/images/2024/06/msh-3297-01.png” alt=“” /></p>
<p>一个工作表由<strong>单元格（Cell）</strong>组成。Excel的数据存储在单元格中。 </p>
<p>我们可以通过<strong>列号（Column）</strong>和<strong>行号（Row）</strong>对单元格进行定位。</p>
<p><img src=“https://assets.moshanghua.net/images/2024/06/msh-3297-02.png” alt=“” /></p>
<p>行号默认从数字1开始，并依次递增。 </p>
<p>列号默认从字母A开始，依次递增。超过字母Z后，以AA，AB的方式继续计数。</p>
<p><img src=“https://assets.moshanghua.net/images/2024/06/msh-3297-03.png” alt=“” /></p>
<p>至此，就是Excel表格的基本结构，总结如图所示。</p>
<p><img src=“https://assets.moshanghua.net/images/2024/06/msh-3297-04.png” alt=“” /></p>
<h2>openpyxl 模块</h2>
<p>要使用Python对Excel表格进行读取，我们需要安装一个用于读取数据的工具 <code>openpyxl</code> 。openpyxl 是一个用于读、写Excel文件的开源模块。</p>
<h2>Excel表格读取</h2>
<h3>读取工作簿</h3>
<p>读取指定路径的工作簿需要使用函数：<code>openpyxl.load_workbook()</code></p>
<p>openpyxl.load_workbook()函数读取成功后，会返回一个工作簿对象，本例中将这个对象赋值给了变量wb。</p>
<pre><code class=“language-Bash”># 导入openpyxl模块
import openpyxl
#读取工作目录里名为"2019年1月销售订单.xlsx"的工作簿并赋值给变量wb
wb = openpyxl.load_workbook("2019年1月销售订单.xlsx")
</code></pre>
<h3>读取指定工作表</h3>
<p>如果事先不知道工作簿内有哪些工作表，可以通过访问工作簿的 <code>.sheetnames</code> 属性来获取一个包含所有工作表名称的列表。 </p>
<p>具体操作为在变量wb之后添加代码 <code>.sheetnames</code> 。</p>
<pre><code class=“language-Bash”>import openpyxl
wb = openpyxl.load_workbook("2019年1月销售订单.xlsx")
使用print输出工作簿中所有的工作表名称
print(wb.sheetnames) # ['销售商品', '销售订单数据']
通过工作簿对象wb获取名为“销售商品”的工作表对象，
orderSheet = wb["销售商品"]
</code></pre>
<h3>读取指定单元格</h3>
<p><img src=“https://assets.moshanghua.net/images/2024/06/msh-3297-05.png” alt=“” /></p>
<p>要获取工作表中指定的单元格对象，我们可以通过在中括号 <code>[ ]</code> 内填入<code>列号和行号</code>的方式去获取。</p>
<p>单元格对象除了包含具体的值，还包含相关的函数和属性。 </p>
<p>要访问单元格里的值，可以在单元格对象后加一个 <code>.value</code></p>
<pre><code class=“language-Bash”>import openpyxl
wb = openpyxl.load_workbook("sample.xlsx")
print(wb.sheetnames) # ['sheet1', 'sheet2']
orderSheet = wb["销售商品"]
使用print输出 orderSheet 的C5单元格对象
print (orderSheet["C5"]) # < Cell '销售商品'.C5 >
使用 orderSheet["C5"].value 输出orderSheet的C5单元格的值
print(orderSheet["C5"].value)
</code></pre>
<p>若单元格中包含<strong>公式</strong>，现有方式读取出的值是公式本身。 </p>
<p>若需要读取公式计算后的值，要在读取工作簿的代码部分，传入一个参数： <code>data_only=True</code> ，便可以得出公式计算后的值了。</p>
<pre><code class=“language-Bash”># 导入openpyxl模块
import openpyxl
原打开方式，直接读取公式本身
wb = openpyxl.load_workbook("2019年1月销售订单.xlsx")
orderSheet = wb["销售订单数据"]
输出公式本身
print(orderSheet["I3"].value)
添加data_only=True打开工作簿，获取公式计算后的值
wb2 = openpyxl.load_workbook("2019年1月销售订单.xlsx", data_only=True)
orderSheet2 = wb2["销售订单数据"]
输出公式计算后的值
print(orderSheet2["I3"].value)</code></pre>
<h3>工作表行数据的遍历</h3>
<p><img src=“https://assets.moshanghua.net/images/2024/06/msh-3297-06.png” alt=“” /></p>
<p>要对整个工作表的每一行数据进行浏览查询，可以使用for循环对工作表对象的行属性（rows）进行遍历。具体代码为 <code>for rowData in orderSheet.rows</code> </p>
<p>这样程序就会以从上到下的顺序，逐个获取到“销售订单数据”工作表内的每一行数据，读取出的每一行数据是由单元格对象组成的元组。</p>
<pre><code class=“language-Bash”>import openpyxl
wb = openpyxl.load_workbook("2019年1月销售订单.xlsx", data_only=True)
orderSheet = wb["销售订单数据"]
遍历工作表的所有行数据
for rowData in orderSheet.rows:
# 输出行数据
print(rowData)
# (<Cell '销售订单数据'.A1>, <Cell '销售订单数据'.B1>, <Cell '销售订单数据'.C1>, <Cell '销售订单数据'.D1>, <Cell '销售订单数据'.E1>, <Cell '销售订单数据'.F1>, <Cell '销售订单数据'.G1>, <Cell '销售订单数据'.H1>, <Cell '销售订单数据'.I1>, <Cell '销售订单数据'.J1>)
# 通过索引2获取第3列数据，也就是商品名
productName = rowData[2].value
# 输出商品名
print(productName)</code></pre>
<h3>列号转数字</h3>
<p>使用函数<code>openpyxl.utils.cell.column_index_from_string()</code>来获取工作表列号对应的数字。</p>
<p>遍历行数据时，如果要定位的列数字比较大，比如订单的总价在第 Z 列，列号太大不太容易数的时候就可以使用函数：<code>openpyxl.utils.cell.column_index_from_string()</code>，来获取列号对应的数字.</p>
<p>比如传入参数“I”就会获取到数字9，表示“I”列是第9列。</p>
<p><strong>这个数字减一即可得到对应的索引。因为索引是从0开始的，所以需要减一</strong></p>
<pre><code class=“language-Bash”>import openpyxl
wb = openpyxl.load_workbook("./doc/2019年1月销售订单.xlsx", data_only=True)
orderSheet = wb["销售订单数据"]
for rowData in orderSheet.rows:
productName = rowData[2].value
# print(productName)
priceIndex = openpyxl.utils.cell.column_index_from_string("I") - 1
# print(priceIndex)
price = rowData[priceIndex].value
print(price)
总价
5
20
40
…</code></pre>
<h2>Excel表格写入</h2>
<h3>创建工作簿</h3>
<p>使用<code>openpyxl.Workbook()</code>函数即可创建一个新工作簿。 </p>
<p>创建成功后，新创建的工作簿对象会被返回。为了方便之后对这个工作簿进行操作，将这个对象赋值给一个变量newWb。</p>
<p>可以访问<code>sheetnames</code>字段来获取工作簿内所有的工作表名称，使用<code>openpyxl.Workbook()</code>创建的工作簿里面，都有一张默认的工作表，名称为Sheet。</p>
<pre><code class=“language-Bash”>import openpyxl
创建一个新工作簿并赋值给变量newWb
newWb = openpyxl.Workbook()
输出新工作簿内所有的工作表名称
print(newWb.sheetnames) # ['Sheet']
</code></pre>
<h3>修改工作表名称</h3>
<p>先通过变量newWb使用<strong>中括号 + 工作表名称</strong>获取这个工作表对象，然后把这个对象赋值给变量<strong>aSheet</strong>。</p>
<p>通过对工作表对象的 <code>.title</code>属性进行赋值，即可修改工作表的名称。</p>
<pre><code class=“language-Bash”>import openpyxl
newWb = openpyxl.Workbook()
将名为Sheet的默认工作表赋值给aSheet变量
aSheet = newWb["Sheet"]
将aSheet工作表名称修改为“A平台”
aSheet.title = "A平台"
</code></pre>
<h3>创建工作表</h3>
<p>通过工作簿对象使用<code>create_sheet()</code>函数可以创建一个名称为Sheet的工作表。 </p>
<p>若名为Sheet工作表已经存在，则会在Sheet后依次添加数字，比如<strong>Sheet1</strong>，<strong>Sheet2</strong>。 </p>
<p>在创建时如需要指定工作表名称，可以将需要指定的工作表名称作为参数传入create_sheet()函数。</p>
<pre><code class=“language-Bash”>import openpyxl
newWb = openpyxl.Workbook()
不指定名称创建工作表
newWb.create_sheet()
指定创建的新工作表名称为"陌上花"
newWb.create_sheet("陌上花")
输出所有的工作表名称以检查是否创建成功
print(newWb.sheetnames) # ['Sheet', 'Sheet1', '陌上花']</code></pre>
<p>新创建的工作表对象会在函数调用后返回，在这里也可以直接分别赋值给变量bSheet和cSheet，方便之后操作使用</p>
<pre><code class=“language-Bash”># 创建 B平台 的工作表并赋值给变量bSheet
bSheet = newWb.create_sheet("B平台")
创建 C平台 的工作表并赋值给变量cSheet
cSheet = newWb.create_sheet("C平台")</code></pre>
<h3>Excel设置单元格的值</h3>
<p>每一个工作表都有一个表头，分别是“<strong>商品名</strong>”、“<strong>月份</strong>”和“<strong>销售额</strong>”。 </p>
<p>本质上，每一个表头也就是一个一个单元格组成的。要修改每个工作表的表头，就需要用到“<strong>设置单元格的值</strong>”这个知识点。</p>
<p><img src=“https://assets.moshanghua.net/images/2024/06/msh-3297-07.png” alt=“” /></p>
<p>可以通过“<code>工作表对象["列号行号"].value</code>”这种方式来获取指定的单元格的值。 </p>
<p>而直接把要设置的值赋值给.value属性，就可以设置或修改这个单元格的值了。</p>
<pre><code class=“language-Python”># 设置aSheet里A1单元格的值为“编号”
aSheet["A1"].value = "编号"
aSheet["B1"].value = "月份"
aSheet["C1"].value = "销售额"
输出A1单元格的值以检查是否设置成功
print(aSheet["A1"].value)
</code></pre>
<p>可以使用for循环对工作簿对象内的<code>worksheets</code>属性进行遍历，以达到逐个访问所有<code>工作表</code>并设置表头的目的</p>
<pre><code class=“language-Python”># 使用for循环遍历工作簿对象的worksheets属性
for sheet in newWb.worksheets:
# 给每一个工作表设置表头
sheet["A1"].value = "商品名"
sheet["B1"].value = "月份"
sheet["C1"].value = "销售额"
</code></pre>
<h3>保存工作簿文件</h3>
<p>通过工作簿对象使用<code>save()</code> 函数，将文件保存路径作为参数，即可将工作簿保存到指定的文件路径。 一般将工作簿存储成后缀名为<code>.xlsx</code>的文件。 </p>
<p>如果指定路径的文件已经存在，使用<code>save()</code>函数会<strong>覆盖原有文件</strong>。</p>
<pre><code class=“language-Python”># 将工作簿保存到指定路径
newWb.save("/Users/chixm/data/汇总.xlsx")</code></pre>
<h3>Excel添加行数据</h3>
<p>想要添加一整行数据可以通过工作表对象使用<code>append()</code>函数。 </p>
<p><code>append()</code>函数会在现有工作表内数据的最后一行之后再添加一行数据。</p>
<p><code>append()</code>函数只有一个参数，该参数是一个列表或者元组。 </p>
<p>使用函数后，列表或元组内的数据会按照顺序逐个添加到目标行中。</p>
<pre><code class=“language-Python”>import openpyxl
读取工作簿和工作表
wb = openpyxl.load_workbook("怪物数值.xlsx")
sheet = wb["东胜神州"]
通过append()函数传入一个元组添加一行数据
sheet.append(("D10002", "白鼠", 600))
保存工作簿到原路径
wb.save("怪物数值.xlsx")</code></pre>