python中excel封装

 1 from openpyxl import load_workbook
 2 
 3 
 4 class D0Excel(object):
 5     """
 6     封装excel类
 7     """
 8     def __init__(self, file_name, sheet_name):
 9         """
10         :param file_name: 文件名
11         :param sheet_name: sheet页名称
12         """
13         self.file_name = file_name
14         self.sheet_name = sheet_name
15 
16     def get_excel(self):
17         """
18         获取excel内容
19         :return:
20         """
21         wd = load_workbook(self.file_name)  # 定义一个workbook的对象
22         ws = wd[self.sheet_name]            # 获取sheet页对象
23         # 获取表头信息
24         watch_head = tuple(ws.iter_rows(max_row=1, values_only=True))[0]
25         # 使用表头信息与表数据信息构成一个嵌套字典的列表
26         one_list = []
27         for one_tuple in tuple(ws.iter_rows(min_row=2,min_col=1, values_only=True)):
28             one_list.append(dict(zip(watch_head, one_tuple)))
29         return one_list
30 
31     def write_excel(self, row, actual, result):
32         """
33         写入excel表格内容
34         :param row: 行号
35         :param actual: 实际结果
36         :param result: 通过与否
37         :return:
38         """
39         wb = load_workbook(self.file_name)
40         if self.sheet_name is None:
41             ws = wb.active
42         else:
43             ws = wb[self.sheet_name]
44 
45         if isinstance(row, int) and (2 <= row <= ws.max_row):
46             ws.cell(row, column=6, value=actual)
47             ws.cell(row, column=7, value=result)
48             wb.save(self.file_name)
49             wb.close()
50         else:
51             print("输入的行数超出范围")

 

转载于:https://www.cnblogs.com/xingyunqiu/p/11226814.html