Python_openpyxl、xlrd&xlwt_对excel表格进行读写
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 os class 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_column def handle_values(values): """ 处理表格数据 """ if isinstance(values, str): values = [values] new_values = [] for i, value in enumerate(values): # 替换换行符;将英文分号替换为中文分号; try: # 替换换行符解决jira无法导入问题，替换分号解决jira分割问题，替换逗号解决csv文件分割问题 v = value.replace("\n", "").replace(";", "；").replace(",", "，") new_values.append(v) except Exception as e: raise RuntimeError("第【%s】的数据替换失败，失败原因：%s" % (i + 1, e)) return new_values def save_to_csv(filename, col_values): # 将数据另存到新的文件 filename_path = filename.split('\\') new_filenames = filename_path[-1].split('.') new_filenames.pop() new_filename = "jira_" + ".".join(new_filenames) + ".csv" filename_path.pop() filename_path.append(new_filename) new_filename_path = "\\".join(filename_path) with open(new_filename_path, "w") as f: for col_value in col_values: try: f.write(col_value + "\n") except Exception as e: raise RuntimeError("数据写入文件失败【%s】，报错：%s" % (col_value, e)) print("生成jira导入文件：%s" % new_filename) def export_jira_file(file_name, sheet_index=0, test_summary=None, action=None, result=None): """ file_name：excel的xlsx文件 sheet_index：sheet页索引 test_summary：jira的“test_summary”在excel中对应的名称。如果以列表传入多个excel字段，会拼接成一个字段 action：jira的“action”在excel中对应的名称。如果以列表传入多个excel字段，会拼接成一个字段 result：jira的“result”在excel中对应的名称。如果以列表传入多个excel字段，会拼接成一个字段 """ test_summary = test_summary or ["用例名称"] action = action or "用例步骤" result = result or "预期结果" # 打开一个工作簿 wb = Workbook.get_workbook(file_name) # 选择一个sheet _sheet = wb.sheet_by_index(sheet_index) # 通过索引选择 # 获取表格所有字段 sheet_fields = _sheet.get_row_values(1) field_values = [] for filed_names in [test_summary, action, result]: if isinstance(filed_names, str): filed_names = [filed_names] join_values = [] # 待连接值 for fn in filed_names: if fn not in sheet_fields: raise RuntimeError("表格不存在字段：%s" % fn) # 获取字段列号 col_num = sheet_fields.index(fn) + 1 # 获取字段列值 col_values = _sheet.get_col_values(col_num)[1:] # 处理空白值 col_values_new = [] t_value = None for value in col_values: if value: col_values_new.append(value) t_value = value else: col_values_new.append(t_value) col_values = col_values_new try: col_values = handle_values(col_values) except RuntimeError as e: raise RuntimeError("【%s】%s" % (fn, e)) join_values.append(col_values) if len(join_values) == 1: field_values.append(join_values[0]) else: new_values = [] for i in zip(*join_values): i = "-".join(i) new_values.append(i) field_values.append(new_values) # 拼接用例名称、操作步骤、预期结果 join_insert_values = [] for i in zip(*field_values): v = ";".join(i) join_insert_values.append(v) # 去重（清空使用的行会产生空白行，空白行会产生与上一行一样的数据） d = {} for iv in join_insert_values: d[iv] = 1 join_insert_values = d.keys() # 增加tcid字段 insert_values = [] insert_values.append("TCID;TestSummary;Action;Result") for i, iv in enumerate(join_insert_values): iv = str(i + 1) + ";" + iv insert_values.append(iv) # 保存为csv save_to_csv(file_name, insert_values) # 关闭工作薄 wb.close() if __name__ == '__main__': file_name = "用例.xlsx" sheet_index = 0 # test_summary = ["需求编号", "模块", "用例名称"] test_summary = "用例名称" action = "用例步骤" result = "预期结果" export_jira_file(file_name=file_name, sheet_index=sheet_index, test_summary=test_summary, action=action, result=result)
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)
