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
| # encoding: utf-8
"""
--------------------------------------
@describe Excel python 读取
@version: 1.0
@project: test
@file: excel.py
@author: yuanlang
@time: 2019-03-12 14:53
---------------------------------------
"""
from openpyxl.reader.excel import load_workbook
import os
class ExcelReader(object):
def reader_all_file(self,root_path):
path = os.listdir(root_path)
for file in path:
self.reader_file(root_path+"/"+file)
def reader_file(self,path):
# 读取excel2007文件
wb = load_workbook(filename=r'{0}'.format(path))
# 显示有多少张表
# print ("Worksheet range(s):", wb.get_named_ranges())
# print ("Worksheet name(s):", wb.get_sheet_names())
# 取第一张表
sheetnames = wb.sheetnames
ws = wb[sheetnames[0]]
# 显示表名，表行数，表列数
# print ("Work Sheet Titile:", ws.title)
# print ("Work Sheet Rows:", ws.max_row)
# print ("Work Sheet Cols:", ws.max_column)
# 建立存储数据的字典
data_dic = {}
data_list = []
# 把数据存到字典中
for rx in range(1, ws.max_row + 1):
temp_list = []
pid = rx
w1 = ws.cell(row=rx, column=1).value
w2 = ws.cell(row=rx, column=2).value
w3 = ws.cell(row=rx, column=3).value
w4 = ws.cell(row=rx, column=4).value
temp_list = [w1, w2, w3, w4]
data_dic[pid] = temp_list
if w2 is not None or w3 is not None or w4 is not None:
if w4 != '市辖区' and w1 != '代码':
data_list.append(temp_list)
# 打印字典数据个数
# print ('Total:%d' % len(data_dic))
#print(data_list)
for _list in data_list:
if _list[3] is not None:
print(str(_list[0])[:6]+","+_list[3]+","+str(_list[0])[:3]+"000")
# print (json.dumps(data_dic, encoding="UTF-8", ensure_ascii=False))
reader=ExcelReader()
reader.reader_all_file(os.path.dirname(__file__)+"/5w放款名单-20190312.xlsx")
|