【Python】openpyxl、spire库使用教程
·
目录
14、删除使用spire库生成的表"Evaluation Warning"
本文介绍Python处理Excel常用库（openpyxl、spire）的基础用法，包含安装、读取与修改等基本操作。
使用python脚本读取工作簿之后，要记得释放资源， 如果不释放资源，文件会被持续占用导致其他程序无法操作，内存也会只增不减让程序越来越卡，此外还可能出现数据未能及时保存到磁盘的情况。
| .xls | .xlsx | |
|---|---|---|
| openpyxl | × | √ |
| spire | √ | √ |
一、openpyxl
1、安装openpyxl
pip install openpyxl
2、调用
import openpyxl
3、创建工作簿
# 创建新的工作簿
wb = openpyxl.Workbook()
# wb.active 获取当前工作簿中活跃的（也就是当前正在操作的、排在前面的）工作表
# 刚用 Workbook() 创建的新空表，openpyxl 默认会自动生成一个名为 “Sheet” 的工作表
sheet = wb.active
# 保存工作簿
wb.save('test.xlsx')
wb.close()
4、添加工作表
# 添加新工作表，并命名为newSheet
sheet2 = wb.create_sheet(title="newSheet")
5、删除工作表
# 删除名为 "Sheet1" 的工作表
# 方法一：
del wb["Sheet1"]
# 方法二：
wb.remove(wb["Sheet1"])
6、更改工作表的名字
# 重命名工作表
sheet.title = "testsheet"
7、读取表格数据
# 打开工作簿
wb = openpyxl.load_workbook("test.xlsx")
# 获取工作簿的工作表
# 方法一：通过索引获取工作表
sheet = wb.worksheets[0]
# 方法二：通过表名获取工作表
sheet = wb["Sheet1"]
# 方法三：获取当前活跃的表（默认第一个表）
sheet = wb.active
# 获取表中指定位置的值
# 方法一：
value1 = sheet['B2'].value
# 方法二：
value2 = sheet.cell(row=2, column=2).value
8、获取表格总行/列数
# 获取表格的总行数
rownum = sheet.max_row
# 获取表格的总列数
colnum = sheet.max_column
9、修改表格数据
修改表格数据，直接让该位置的值等于修改好的数据：
# 方法一：
sheet['B2'].value = 111
# 方法二：按行列号修改
ws.cell(row=2, column=2, value='111')
10、复制粘贴表格内容
from copy import copy
from openpyxl.utils import range_boundaries
# 复制表格区域
def copy_range(source_ws, target_ws, source_range, target_range):
# range_boundaries：把 Excel 的表格范围（字符串）翻译成 Python 能理解的坐标（数字）。
min_col, min_row, max_col, max_row = range_boundaries(source_range) # 返回：一个包含 4 个整数的元组 (最小列, 最小行, 最大列, 最大行)
target_min_col, target_min_row, target_max_col, target_max_row = range_boundaries(target_range)
# 【关键修改】计算行和列的偏移量
row_offset = target_min_row - min_row
col_offset = target_min_col - min_col
# .iter_rows(...)：这是工作表对象的一个方法，意思是“按行迭代”
for row in source_ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
for cell in row:
# 目标位置 = 源位置 + 偏移量
target_row = cell.row + row_offset
target_col = cell.column + col_offset
# target_cell存放目标单元格，.cell(...)：这是工作表对象的一个方法，专门用来获取单元格
target_cell = target_ws.cell(row=target_row, column=target_col)
# 1. 复制值，如果只想复制格式不想复制值，可以把这行注释掉
target_cell.value = cell.value
# 2. 复制样式
if cell.has_style:
target_cell.font = copy(cell.font)
target_cell.border = copy(cell.border)
target_cell.fill = copy(cell.fill)
target_cell.number_format = cell.number_format
target_cell.protection = copy(cell.protection)
target_cell.alignment = copy(cell.alignment)
函数可直接粘贴使用，会将表格的格式和字体样式等都一起复制过去。
source_ws为源工作表，target_ws为目标工作表。
source_range为源工作表的复制源，要写起始位置和结束位置
target_range为要复制到目标工作表的具体位置，可以只写起始位置
如果两个传的是同一个表，则表示在同一个表格内粘贴复制内容，如果是不同表，则表示在不同表格之间粘贴复制内容，可以是不同工作簿的表。
调用示例：
# 打开工作簿
wb = openpyxl.load_workbook("test.xlsx")
# 获取工作簿第一张工作表
sheet = wb.worksheets[0]
rangeSrc = 'A2:C3'
rangeTgt = 'A9'
copy_range(sheet, sheet, rangeSrc, rangeTgt)
wb.save("test.xlsx")
wb.close()
复制前
复制后
11、插入行/列
# 在第2行的位置插入一行
sheet.insert_rows(2)
# 从第2行开始插入3行
sheet.insert_rows(2, 3)
# 在第3列的位置插入一列
sheet.insert_cols(3)
# 从第3列开始插入2列
sheet.insert_cols(3, 2)
insert_rows()括号里是要加位置，int类型，索引从1开始。
增加的新行是没有任何格式的，可以结合上面的复制函数，复制上一行的格式。
示例：m为增加的那一行的位置
sheet.insert_rows(m)
# 增加的空行没有格式，需要复制上一行的格式
rangeSrc = 'A' + str(m - 1) + ':F' + str(m - 1)
rangeTgt = 'A' + str(m) + ':F' + str(m)
copy_range(sheet, sheet, rangeSrc, rangeTgt)
12、删除行/列
# 在第2行的位置删除一行
sheet.delete_rows(2)
# 删除第2行开始的3行
sheet.delete_rows(2, 3)
# 在第3列的位置删除一列
sheet.delete_cols(3)
# 删除从第3列开始的2列
sheet.delete_cols(3, 2)
刚刚新增的第2行被删除了。
13、释放资源
wb = openpyxl.load_workbook("test.xlsx")
# 有做增删改操作时，使用save()保存表格数据
wb.save("test.xlsx")
wb.close()
二、spire
1、安装spire
pip install spire.xls
2、调用
from spire.xls import *
from spire.xls.common import *
3、创建工作簿
from spire.xls import *
from spire.xls.common import *
# 创建一个空白的工作簿对象
wb = Workbook()
# 保存工作簿
wb.SaveToFile("我的新表格.xlsx", ExcelVersion.Version2016)
wb.Dispose()
新建工作簿时，Spire 默认会自动创建 3 个空白工作表(Sheet1, Sheet2, Sheet3)。
使用spire会在工作簿最后生成一个工作表”Evaluation Warning“（可以使用别的库删除，具体看后面）。
这个表是软件的“防盗版水印”，强制插入以提示购买商业授权。
原因：使用了未授权的免费版/试用版，或超出了免费限制（如Excel超过150行或5个工作表）。
4、添加工作表
# 添加新工作表，并命名为newSheet
sheet = wb.Worksheets.Add("newSheet")
5、删除工作表
# 方法一：删除名为 "Sheet1" 的工作表
wb.Worksheets.Remove(wb.Worksheets["Sheet1"])
# 方法二：通过索引删除 (删除第1个工作表)
wb.Worksheets.Remove(wb.Worksheets[0])
6、更改工作表的名字
sheet.Name = "testsheet"
7、读取表格数据
# 创建一个空白的工作簿对象
wb = Workbook()
# 打开指定的 Excel 文件
wb.LoadFromFile("test.xlsx")
# 获取工作簿的工作表
# 方法一：通过索引获取工作表
sheet = wb.Worksheets[0]
# 方法二：通过表名获取工作表
sheet = wb.Worksheets["Sheet1"]
# 获取表中指定位置的值
# 方法一：
value = sheet.Range["A1"].Value
# 方法二：
value = sheet.Range[1, 1].Value
8、获取表格总行/列数
# 获取表格的总行数
rownum = sheet.LastRow
# 获取表格的总列数
colnum = sheet.LastColumn
9、修改表格数据
# 方法一：
sheet.Range["A1"].Value = "111"
# 方法二：按行列号修改
sheet.Range[2, 3].Value = "111"
10、复制粘贴表格内容
# 复制内容，并复制行宽、列宽
def copyCommon(sheetSrc, sheetTgt, rangeSrc, rangeTgt):
# 关键步骤1：复制内容+格式
rangeSrc.Copy(rangeTgt, CopyRangeOptions.All)
# 关键步骤2：复制行高
for rowIndex in range(rangeSrc.Row, rangeSrc.Row + rangeSrc.RowCount):
srcHeight = sheetSrc.GetRowHeight(rowIndex)
sheetTgt.SetRowHeight(rowIndex - rangeSrc.Row + rangeTgt.Row, srcHeight)
# 关键步骤3：复制列宽
for colIndex in range(rangeSrc.Column, rangeSrc.Column + rangeSrc.ColumnCount):
srcWidth = sheetSrc.GetColumnWidth(colIndex)
sheetTgt.SetColumnWidth(colIndex - rangeSrc.Column + rangeTgt.Column, srcWidth)
函数可直接粘贴使用，会将表格的格式和字体样式等都一起复制过去。
sheetSrc为源工作表，sheetTgt为目标工作表。
rangeSrc为源工作表的复制源，要写起始位置和结束位置。
rangeTgt为要复制到目标工作表的具体位置，可以只写起始位置。
如果两个传的是同一个表，则表示在同一个表格内粘贴复制内容，如果是不同表，则表示在不同表格之间粘贴复制内容，可以是不同工作簿的表。
调用示例：
# 创建一个空白的工作簿对象
wb = Workbook()
# 打开指定的 Excel 文件
wb.LoadFromFile("test.xlsx")
# 获取工作簿的工作表
sheet = wb.Worksheets[0]
rangeSrc = sheet.Range["A2:C3"]
rangeTgt = sheet.Range["A9"]
copyCommon(sheet,sheet,rangeSrc, rangeTgt)
wb.SaveToFile("test.xlsx", ExcelVersion.Version2016)
wb.Dispose()
11、插入行/列
# 在第2行的位置插入一行
sheet.InsertRow(2)
# 从第2行开始插入3行
sheet.InsertRow(2, 3)
# 在第3列的位置插入一列
sheet.InsertColumn(3)
# 从第3列开始插入2列
sheet.InsertColumn(3, 2)
12、删除行/列
# 在第2行的位置删除一行
sheet.DeleteRow(2)
# 删除第2行开始的3行
sheet.DeleteRow(2, 3)
# 在第3列的位置删除一列
sheet.DeleteColumn(3)
# 删除从第3列开始的2列
sheet.DeleteColumn(3, 2)
13、释放资源
# 保存文件到当前脚本目录 (指定格式为 xlsx)
# 建议指定第二个参数（如 `ExcelVersion.Version2016`），明确告诉它你要保存为现代的 `.xlsx` 格式，否则它可能会按老版本的 `.xls` 格式保存，导致后续用其他库读取时出问题
wb.SaveToFile("test.xlsx", ExcelVersion.Version2016)
# 释放资源 (Spire 很吃内存，用完必须 Dispose)
wb.Dispose()
14、删除使用spire库生成的表"Evaluation Warning"
使用spire.xls会在表末自动生成表“Evaluation Warning”。
生成这个表之后，打开工作簿会默认打开这个工作表，如果想要一打开工作簿就默认打开第一个工作簿，可以使用别的开源库删除掉这个库，这样打开工作簿会默认打开第一个工作表。
# 方法一：使用openpyxl库删除工作表
wb = load_workbook("test.xlsx")
if "Evaluation Warning" in wb.sheetnames:
del wb["Evaluation Warning"]
wb.save("test.xlsx")
else:
print("Evaluation Warning不存在")
# 方法二：使用xlwings删除工作表
app = xw.App(visible=False) # 后台运行
try:
wb = app.books.open(excelName)
# 查找并删除工作表
sheet_names = [sheet.name for sheet in wb.sheets]
if "Evaluation Warning" in sheet_names:
wb.sheets["Evaluation Warning"].delete()
wb.save()
else:
print("⚠️ 'Evaluation Warning' 不存在")
wb.close()
finally:
app.quit()
更多推荐
9
0- 0
已为社区贡献1条内容
所有评论(0)