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