handle_excel.py
1.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time : 2022/12/8 21:58
# @Author : Shitou
# @FileName: hand_excel.py
# @Software: PyCharm
"""
封装读写excel表格
"""
import openpyxl
class Excel(object):
def __init__(self, file_name, file_sheet):
"""
:param file_name: 文件路径
:param file_sheet: Excel表格sheet页
"""
self.file_name = file_name
self.file_sheet = file_sheet
def open_excel(self):
"""打开工作簿"""
self.wb = openpyxl.load_workbook(self.file_name)
self.sh = self.wb[self.file_sheet]
def read_excel(self):
"""读取excel表格数据"""
# 打开工作簿
self.open_excel()
# 读取表头
res = list(self.sh.rows)
title = []
for i in res[0]:
title.append(i.value)
test_case = []
# 读取非表头
for item in res[1:]:
data = []
for j in item:
data.append(j.value)
case = dict(zip(title, data))
test_case.append(case)
return test_case
def read_excel_location(self, row_column):
"""读写指定的单元格"""
self.open_excel()
# dy = self.sh["A2"].value
dy = self.sh[row_column].value
return dy
def write_excel(self, row, column, value):
"""Excel表格写入数据"""
self.open_excel()
# 获取单元格定位并写入
self.sh.cell(row=row, column=column, value=value)
# 保存
self.wb.save(self.file_name)
if __name__ == '__main__':
a = Excel(r"/demo_08_workai_1/data/system/test_02_department.xlsx",
'adddepartment')
a.read_excel_location("I2")