python版本:3.3
xlrd版本:0.9.2
编辑工具:Notepad++
环境:win7
xlrd安装:命令行下进入xlrd所在目录,输入
python setup.py install
excel格式
id | name | age |
1 | zzz | 12 |
2 | 呵呵 | 50 |
Json输出格式为对象结构,文件编码UTF-8
{
"1": {
"name": "zzz",
"age": "12"
},
"2": {
"name": "呵呵",
"age": "50"
}
}
python代码
# coding=utf-8
# 将excel解析成json文件
# last date: 2013-10-28 version 1.0
import os
import sys
import codecs
import xlrd #http://pypi.python.org/pypi/xlrd
global PRINT_LEVEL# 0:no log; 1: warning; 2: prompt; 3: all
PRINT_LEVEL = 1
def getStrFromObj(obj):
if type(obj) == float:
return str(int(obj))
else:
return obj
def getEcelColStr(col):
if col <= 26:
return chr(ord('A') + col - 1)
elif col <= 52:
return 'A'+chr(ord('A') + col - 26 - 1)
elif col <= 78:
return 'A'+chr(ord('A') + col - 26 - 1)
else:
return str(col)
def praseRowData(outputFile, fieldName, rowData, row):
ncols = len(rowData)
cellValue = getStrFromObj(rowData[0])
outputFile.write('\t\"' + cellValue + '\": {\n')
if PRINT_LEVEL >= 3:
print ('id: ' + cellValue)
for col in range(ncols-1):
if col != 0:
outputFile.write(',\n')
if PRINT_LEVEL >= 3:
print ('col: ' + str(col))
cellValue = getStrFromObj(rowData[col+1])
if PRINT_LEVEL >= 1:
if len(cellValue) == 0:
colStr = getEcelColStr(col+2)
print ('Warning: the value in row %d col %s is null!' % (row+2, colStr))
field = fieldName[col+1]
lineStr = '\"' + field + '\": \"' + cellValue + '\"'
if PRINT_LEVEL >= 3:
print (lineStr)
outputFile.write('\t\t' + lineStr)
outputFile.write('\n\t}')
def main():
if len(sys.argv) != 2:
print ('argv count != 2, program exit')
print ('Usage: a.py excelFileName')
exit(0)
if PRINT_LEVEL >= 2:
print ('****************')
print ('excel to json')
print ('****************')
# prase file name
excelFileName = sys.argv[1]
if PRINT_LEVEL >= 2:
print ('Excel File Name: ' + excelFileName)
tmpStr = excelFileName.split('.')[0]
jsonFileName = 'Static' + tmpStr[0:1].upper() + tmpStr[1:] + '.json'
if PRINT_LEVEL >= 2:
print ('Json File Name: ' + jsonFileName)
print ('****************')
if os.path.exists(excelFileName) == False:
if PRINT_LEVEL >= 1:
print ('Warning: the excel file %s dose not exsit!' % (excelFileName))
exit(0)
# open file
workbook = xlrd.open_workbook(excelFileName)
outputFile = codecs.open(jsonFileName, 'w', 'utf-8')
# prase
if PRINT_LEVEL >= 2:
print ('Prase ...')
firstLine = True
for sheet in workbook.sheets():
if sheet.nrows != 0:
# prase table data begin
if firstLine:
firstLine = False
else:
outputFile.write(u'\n')
outputFile.write(u'{\n')
# get field
fieldName = sheet.row_values(0)
for row in range(sheet.nrows - 1):
if row != 0:
outputFile.write(',\n')
praseRowData(outputFile, fieldName, sheet.row_values(row + 1), row)
# prase table data end
outputFile.write(u'\n}')
# close file
outputFile.close()
if PRINT_LEVEL >= 2:
print ('****************')
print ('Excel to Json Finished!')
print ('****************')
main()
遇到的问题:
1. print :3.3版本和以前不太一样貌似,print ("xxx"),一开始出现 错误 SyntaxError: invalid syntax,是因为没有添加 ()
2. 报错:IndentationError: unindent does not match any outer indentation level,是因为代码的缩进问题,对于python格式很重要。(参考)
3. python类型转换:str(x) chr(x) ord(x) (参考)(参考2)
4. 大小写转换:s.upper(),s.lower()(参考)
5. 判断文件是否存在os.path.exists(path) (参考)
6. 全局变量:(参考)
global aaa
aaa = 1
7. if语句:注意elif和后面的冒号(参考)
if ...:
...
elif ...:
...
else:
...
8. 字符截取:str.split('xxx') str[1:4] (参考)
9. python文件编码格式:utf-8(参考)
在文件起始位置添加:# coding=utf-8