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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
| #coding=utf-8
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import math
import xlrd
import json
import datetime
import time
def handleInt(rawValue):
if rawValue == '':
return 0
return int(rawValue)
def handleFloat(rawValue):
if rawValue == '':
return 0
return float(rawValue)
def handleBool(rawValue):
try:
if int(rawValue) == 1:
return True
except Exception as e:
pass
return False
def handleStr(rawValue):
try:
intvalue = int(rawValue)
floatvalue = float(rawValue)
if intvalue != 0 and intvalue == floatvalue:
return str(intvalue)
except Exception as e:
pass
return str(rawValue)
def handleBigNum(rawValue):
i = int(rawValue)
s = str(i)
l = len(s)
if l == 1:
return {"a": i, "b": 0}
b = math.pow(10, l - 1)
a = float(i / b)
return {"a": a, "b": l - 1}
def handleListStr(rawValue):
arr = []
if rawValue == '':
return arr
for a in rawValue.split(','):
arr.append(str(a))
return arr
def handleListInt(rawValue):
arr = []
rawValue = str(rawValue)
if rawValue == '':
return arr
for a in rawValue.split(','):
try:
v = float(a)
v = int(v)
except Exception as e:
v = int(a)
arr.append(v)
return arr
def handleTime(rawValue):
tuple = xlrd.xldate_as_tuple(rawValue, 0)
excel_datetime = datetime.datetime(*tuple)
timestamp = time.mktime(excel_datetime.timetuple())
return int(timestamp)
def getDataHandleFunc(typeName):
dic = {
'int' : handleInt,
'str' : handleStr,
'string' : handleStr,
'float' : handleFloat,
'bool' : handleBool,
'bignum' : handleBigNum,
'list|str' : handleListStr,
'list|int' : handleListInt,
'time' : handleTime,
}
if typeName not in dic:
raise Exception("type err: %s" % typeName)
return dic[typeName]
def convert(srcFileName, sheetName, desFileName, kvMode):
print 'Start %s : %s ---> %s' % (srcFileName, sheetName, desFileName)
try:
wb = xlrd.open_workbook(filename=srcFileName)
except Exception as e:
print srcFileName, 'can not open'
return
try:
sheet = wb.sheet_by_name(sheetName)
except Exception as e:
print 'file:', srcFileName, ' sheet:', sheetName, ' not exist'
return
data = []
if kvMode:
data = {}
varNames = sheet.row_values(1) # ['id', ' type', 'name' ...]
typeNames = sheet.row_values(2) # ['int', 'int', 'str' ...]
key = "id"
for row in xrange(3, sheet.nrows):
if sheet.cell_value(row, 0) == "":
break
rowData = {}
for idx, col in enumerate(xrange(sheet.ncols)):
cellValue = sheet.cell_value(row, col)
if idx >= len(typeNames) or idx >= len(varNames):
continue
if typeNames[idx] == "" or varNames[idx] == "":
break
if typeNames[idx].startswith('list.'):
typeArr = typeNames[idx].split('.')
func = getDataHandleFunc(typeArr[1])
else:
func = getDataHandleFunc(typeNames[idx])
varName = varNames[idx]
if typeNames[idx].startswith('list.'):
rowData.setdefault(varName, [])
if cellValue == 'null':
if typeNames[idx].startswith('list.'):
rowData[varName].append('')
else:
rowData[varName] = ''
continue
try:
realVal = func(cellValue)
if typeNames[idx].startswith('list.'):
rowData[varName].append(realVal)
else:
rowData[varName] = realVal
if idx == 0 and kvMode:
key = str(rowData[varName])
except Exception as e:
raise Exception("func: %s, err: %s, row: %d, col:%d, rawData:%s" % (typeNames[idx], e, row, col, cellValue))
if kvMode:
data[key] = rowData
else:
data.append(rowData)
with open(desFileName, 'w') as f:
f.write(json.dumps(data, indent=4, sort_keys=True, ensure_ascii=False, encoding='utf-8'))
print 'Done %s : %s ---> %s' % (srcFileName, sheetName, desFileName)
return data
if __name__ == '__main__':
data = [
["测试表.xlsx", "测试KV", "TestKV.json", 1],
["测试表.xlsx", "测试列表", "TestList.json", 0],
]
for line in data:
try:
srcFileName = line[0]
sheetName = line[1]
desFileName = line[2]
kvMode = line[3]
except Exception as e:
raise Exception('convertList file format invalid')
if kvMode == 0:
kvMode = False
elif kvMode == 1:
kvMode = True
convert(srcFileName, sheetName, desFileName, kvMode)
|
