excel_to_dict.py
1.48 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
import xlrd,xlwt,json,os
excel_file_name = r'illegal_code_file.xls'
#excel_file_name = r'event_code.xls'
#workbook = xlrd.open_workbook(excel_file_name,encoding_override='utf-8')
def excel_to_list(filename, sheetnum=0):
print('in excel_to_list .....transform.....filename:%s' %filename)
if os.path.exists(os.path.splitext(filename)[0] + ".xls"):
data = xlrd.open_workbook(os.path.splitext(filename)[0] + ".xls")
elif os.path.exists(os.path.splitext(filename)[0] + ".xlsx"):
data = xlrd.open_workbook(os.path.splitext(filename)[0] + ".xlsx")
else:
print('illegal_code_file.xls not exists')
#data = xlrd.open_workbook(filename)
table = data.sheets()[sheetnum]
row_nums = table.nrows
code_list = []
for row in range(1,row_nums):
title = table.row_values(row)[0]
value = table.row_values(row)[1]
new_value = table.row_values(row)[2]
if new_value != "":
if isinstance(new_value, float):
code_list.append([value,str(int(new_value))])
else:
code_list.append([value, new_value])
return code_list
# import pandas as pd
#
# pd_file = pd.read_excel(excel_file_name)
# print(pd_file)
#
# exec_dict = pd_file.to_dict()
# print(exec_dict)
if __name__ == '__main__':
print(excel_to_list(excel_file_name))
# for i in excel_to_dict(excel_file_name):
# print(json.dumps(i,encoding='UTF-8', ensure_ascii=False))