将excel中所有的sheet转变为对应的json

#-*- encoding:utf-8 -*-
import sys
import locale
import json
import codecs
import xlrd
# 确定运行环境的encoding
__g_codeset = sys.getdefaultencoding()
if "ascii"==__g_codeset:
  __g_codeset = locale.getdefaultlocale()[1]
#
def object2double(obj):
  if(obj==None or obj==""):
    return 0
  else:
    return float(obj)
  #end if
#
def utf8_to_mbs(s):
  return s.decode("utf-8").encode(__g_codeset)
#
def mbs_to_utf8(s):
  return s.decode(__g_codeset).encode("utf-8")
#
def _tongjiFirstRow():
  xlrd.Book.encoding = "gbk"
  data = xlrd.open_workbook("C:\Users\sunsh\Desktop\erp_data.xls",formatting_info=True)
  data_dict= {}
  sheet_list= data.sheet_names()
  for j in range(len(sheet_list)):
    tblTDLYMJANQSXZB = data.sheet_by_name(sheet_list[j])
    # 找到有几列几列
    nrows = tblTDLYMJANQSXZB.nrows  # 行数
    ncols = tblTDLYMJANQSXZB.ncols  # 列数
    totalArray = []
    arr = []
    for i in range(0, ncols):
      arr.append(tblTDLYMJANQSXZB.cell(0, i).value)
    # end for
    for rowindex in range(1, nrows):
      dic = {}
      for colindex in range(0, ncols):
        s = tblTDLYMJANQSXZB.cell(rowindex, colindex).value
        dic[arr[colindex]] = s
      # end for
      totalArray.append(dic)
    # end for
    data_dict[sheet_list[j]]=totalArray
  file=codecs.open("xy.txt","w",'utf-8')
  print(json.dumps(data_dict,ensure_ascii=False)) # ensure_ascii=False  解决乱码
  file.write(json.dumps(data_dict))
  file.close()
#end

if __name__ == "__main__":
  _tongjiFirstRow()
  print("export OK")