openpyxl 和 xlrd&xlwt 都能对excel进行读写，但是它们读写的格式不同，openpyxl 只能读写 xlsx格式的excel，xlrd&xlwt 只能读写 xls格式的excel。
openpyxl
创建excel文件
import openpyxl
# ====== 创建格式为xlsx的excel文件 ======
# 创建一个工作簿,若表格已存在，则覆盖
wb = openpyxl.Workbook()# 创建一个名为test的sheet
wb.create_sheet('test')# 保存文件。注：创建表格会有两个sheet,按顺序名称分别为Sheet和test
wb.save('test.xlsx')# 关闭工作薄
wb.close()
读写已有excel文件
import openpyxl
# ====== 打开已有的excel表格 ======
# 打开一个工作簿
wb = openpyxl.load_workbook('test.xlsx')# 选择一个sheet
# sheet = wb["Sheet1"] # 通过表名选择
sheet = wb.worksheets[0] # 通过索引选择# 关闭工作薄
wb.close()
对sheet进行读写
# coding:utf-8
import openpyxl# ====== 向sheet写入数据 ======
# 打开一个工作簿
wb = openpyxl.load_workbook('test.xlsx')
# 选择一个sheet
# sheet = wb["Sheet1"] # 通过表名选择
sheet = wb.worksheets[0] # 通过索引选择# 获取行数
row = sheet.max_row
# 获取列数
column = sheet.max_column
print(row, column)# 写入数据
sheet.append(["aaa", "bbb", "ccc"]) # 在最后一行写入一行数据，列表中每一个数据表示每列写入的数据
sheet.append([1, 2, 3, 4])# 读取数据
ce = sheet.cell(row=1, column=1) # 读取第1行，第1列的数据
print(ce.value)# 更新数据
ce.value = "ddd" # 更新第1行，第1列的数据为 ddd
sheet.cell(3, 1, '') # 更新第3行第1列的数据为 空串# 删除数据
# 从第2行开始删除，删除1行
sheet.delete_rows(2, amount=1)
# 删除第3列
sheet.delete_cols(3)# 保存文件
wb.save('test.xlsx')
# 关闭工作薄
wb.close()
简单封装
# coding:utf-8
import openpyxl
import osclass Workbook:
workbook = None
filename = None @classmethod
def get_workbook(cls, filename, is_create=False):
""" 获取工作簿，若文件存在则打开，若文件不存在则新建 """
cls.filename = filename
is_file = os.path.exists(filename)
if is_create:
is_file = False
if is_file:
cls.workbook = openpyxl.load_workbook(filename)
else:
cls.workbook = openpyxl.Workbook()
return cls() def sheet_by_name(self, sheet_name):
""" 通过表的名称获取sheet对象 """
ws = WorkSheet(self.workbook)
return ws.sheet_by_name(sheet_name) def sheet_by_index(self, index):
""" 通过表的索引获取sheet对象 """
ws = WorkSheet(self.workbook)
return ws.sheet_by_index(index) def save(self, filename=None):
if filename is None:
filename = self.filename
self.workbook.save(filename) def close(self):
self.workbook.close()class WorkSheet: def __init__(self, workbook):
self.workbook = workbook
self.sheet = None def sheet_by_name(self, name):
""" 通过表的名称获取sheet对象 """
self.sheet = self.workbook[name]
return self def sheet_by_index(self, index):
""" 通过表的索引获取sheet对象 """
self.sheet = self.workbook.worksheets[index]
return self def get_cell_value(self, row, column):
""" 获取单元格的值 """
return self.sheet.cell(row, column).value def get_col_values(self, column):
""" 获取指定列的所有值 """
col_values = []
rows = self.rows
for row in range(rows):
row += 1
v = self.get_cell_value(row, column)
col_values.append(v)
return col_values def get_row_values(self, row):
""" 获取指定行的所有值 """
row_values = []
columns = self.columns
for column in range(columns):
column += 1
v = self.get_cell_value(row, column)
row_values.append(v)
return row_values def write_cell_value(self, row, column, value):
""" 写入或更新单元格的值 """
return self.sheet.cell(row, column, value) def insert_row_values(self, values, row=None):
"""
插入一行数据，默认插入最后一行
:param values: 插入的数据
:type values: list
:param row: 行号
:type row: int
"""
if row is None:
self.sheet.append(values)
else:
self.sheet.insert_rows(row)
col = 1
for v in values:
self.write_cell_value(row, col, v)
col += 1 def insert_column_values(self, values, column=None):
""" 插入一列数据，默认插入最后一列 """
if column is None:
col = self.columns
row = self.rows
v = self.get_cell_value(1, 1)
if col == 1 and row == 1 and v is None:
column = 1
else:
column = self.columns + 1
else:
self.sheet.insert_cols(column)
row = 1
for v in values:
self.write_cell_value(row, column, v)
row += 1 def delete_rows(self, row, amount=1):
""" 删除行数, row表示删除起始行，amount表示删除的行数 """
self.sheet.delete_rows(row, amount) @property
def rows(self):
""" 表格的行数 """
return self.sheet.max_row @property
def columns(self):
""" 表格的列数 """
return self.sheet.max_columndef del_wrap(values):
""" 删除换行符 """
if isinstance(values, str):
values = [values]
new_values = []
for value in values:
new_values.append(value.replace("\n", ""))
return new_valuesif __name__ == '__main__':
# 打开一个工作簿
filename = r'C:\Users\41850\Desktop\test.xlsx'
wb = Workbook.get_workbook(filename) # 选择一个sheet
sheet = wb.sheet_by_index(0) # 通过索引选择 RequireNum = [u"需求编号"]
RequireNum_col_values = None TestModel = [u"测试项"]
TestModel_col_values = None CaseName = [u"用例名称", u"TestSummary"]
CaseName_col_values = None Step = [u"操作步骤", u"Action"]
Step_col_values = None ExpectResult = [u"预期结果", u"Result"]
ExpectResult_col_values = None # 获取各字段的值
fields = sheet.get_row_values(1)
for field in fields:
if field in RequireNum:
RequireNum_col_num = fields.index(field) + 1 # 需求编号列号
RequireNum_col_values = sheet.get_col_values(RequireNum_col_num)[1:]
RequireNum_col_values_new = []
t_value = None
for value in RequireNum_col_values:
if value is not None:
RequireNum_col_values_new.append(value)
t_value = value
else:
RequireNum_col_values_new.append(t_value)
RequireNum_col_values = RequireNum_col_values_new
elif field in TestModel:
TestModel_col_num = fields.index(field) + 1 # 测试项列号
TestModel_col_values = sheet.get_col_values(TestModel_col_num)[1:]
TestModel_col_values_new = []
t_value = None
for value in TestModel_col_values:
if value is not None:
TestModel_col_values_new.append(value)
t_value = value
else:
TestModel_col_values_new.append(t_value)
TestModel_col_values = TestModel_col_values_new
elif field in CaseName:
CaseName_col_num = fields.index(field) + 1 # 用例名称列号
CaseName_col_values = sheet.get_col_values(CaseName_col_num)[1:]
CaseName_col_values = del_wrap(CaseName_col_values)
elif field in Step:
Step_col_num = fields.index(field) + 1 # 操作步骤列号
Step_col_values = sheet.get_col_values(Step_col_num)[1:]
Step_col_values = del_wrap(Step_col_values)
elif field in ExpectResult:
ExpectResult_col_num = fields.index(field) + 1 # 操作步骤列号
ExpectResult_col_values = sheet.get_col_values(ExpectResult_col_num)[1:]
ExpectResult_col_values = del_wrap(ExpectResult_col_values) # 检查必要字段
if not all([CaseName_col_values, Step_col_values, ExpectResult_col_values]):
raise ValueError("缺少必要字段名称，必要字段名称为：用例名称、操作步骤、预期结果") # 拼接用例编号和测试项
if RequireNum_col_values:
TestModel_col_values_new = []
for RequireNum_col_value, TestModel_col_value in zip(RequireNum_col_values, TestModel_col_values):
TestModel_col_values_new.append(u"【%s-%s】" % (RequireNum_col_value, TestModel_col_value))
TestModel_col_values = TestModel_col_values_new
CaseName_col_values_new = [] # 拼接测试项和用例名称
for TestModel_col_value, CaseName_col_value in zip(TestModel_col_values, CaseName_col_values):
CaseName_col_values_new.append(TestModel_col_value + CaseName_col_value)
CaseName_col_values = CaseName_col_values_new # 拼接用例名称、操作步骤、预期结果
insert_values = []
insert_values.append("TCID;TestSummary;Action;Result") tcid = 1
for case_name, step, expect_result in zip(CaseName_col_values, Step_col_values, ExpectResult_col_values):
v = "%s;%s;%s;%s" % (tcid, case_name, step, expect_result)
insert_values.append(v)
tcid += 1 # 将数据另存到新的文件
filename_path = filename.split('\\')
new_filename = "new_" + filename_path[-1]
filename_path.pop()
filename_path.append(new_filename)
new_filename_path = "\\".join(filename_path)
new_wb = Workbook.get_workbook(new_filename_path, is_create=True) # 选择一个sheet
sheet_new = wb.sheet_by_index(0) # 通过索引选择 # 写入新的数据
sheet_new.insert_column_values(insert_values) # 保存文件
new_wb.save(new_filename_path) # 关闭工作薄
wb.close()
new_wb.close()
xlrd&xlwt
xlrd
xlrd只能对excel文件进行读
# coding:utf-8
import xlrd# 使用xlrd创建一个工作薄对象
workbook = xlrd.open_workbook('C:/Users/41850/Desktop/test.xls')# 根据工作表的名称创建表格对象
# sheet = workbook.sheet_by_name('Sheet1')
# 根据工作表的索引创建表格对象，索引从0开始
sheet = workbook.sheet_by_index(0)# 获取sheet名称
sheet_name = sheet.name
print(u"表格名称: %s" % sheet_name)# 获取工作表的行数
row_count = sheet.nrows
# 获取工作表的列数
col_count = sheet.ncols
print("行数: %s 列数: %s" % (row_count, col_count))# 获取数据
row_value = sheet.row_values(0) # 获取第1行数据
col_value = sheet.col_values(0) # 获取第1列数据
cell_value = sheet.cell_value(0, 1) # 获取第1行第2列数据
print("获取的数据值为：%s, %s, %s" %(row_value, col_value, cell_value))
xlwt
xlwt只能对xls文件进行写
# coding:utf-8
import xlwt# 打开一个工作薄
filename = 'C:/Users/41850/Desktop/test1.xls'
write_book = xlwt.Workbook(encoding="utf-8")# 新增个表格，若文件已存在，则覆盖
sheet = write_book.add_sheet('test')# 写入数据(行号， 列号， 写入值)
sheet.write(0, 0, 123.456)
sheet.write(1, 0, 789)
sheet.write(2, 0, 'hello')# 保存
write_book.save(filename)
注意，xlwt没有直接修改已有 xls 文件的方法。通常的做法是，读取出文件，复制一份数据，对其进行修改，再保存。
# coding:utf-8
import xlrd
from xlutils.copy import copy# 打开文件
filename = 'C:/Users/41850/Desktop/test1.xls'
rb = xlrd.open_workbook(filename)# 复制
wb = copy(rb)
# 选取表单
s = wb.get_sheet(0)
# 写入数据
s.write(0, 1, 'new data')
# 保存
wb.save(filename)
