python 解析excel json数据 存入excel （先存入内存，最后统一写入excel）

# -*- coding:utf-8 -*-
import xlrd # 读excel
import xlwt # 写excel
from xlutils.copy import copy
import datetime
# import sys
# reload(sys)
# sys.setdefaultencoding('utf8')
import json

latList = []
lngList = []
shicahngList = []
startTimeList = []
endTimeList = []
dunweiList = []
carIdList = []
# 解析json
def read_json():
    workbook = xlrd.open_workbook(u'C:/last_data1_30000.xlsx')  # 打开工作簿
    sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格

     #循环excel
    for p in range(1,30000):
        print 'num : ',p

        # print '车辆id:', carId
        # 获取哪一行 哪一列的数据
        # print (worksheet.cell_value(p, 8))
        # json 数组
        data = json.loads(worksheet.cell_value(p, 8))
        # 循环json中的数据
        for i in data:
            # 车辆id
            carId = worksheet.cell_value(p, 0)
            carIdList.append(carId)
            # 吨位
            dunwei = worksheet.cell_value(p, 4)
            dunweiList.append(dunwei)
            latList.append(i['lat'])
            lngList.append(i['lng'])
            basic_endtime = i['stop_end_time'][11:]
            if basic_endtime == '00:00:00':
                basic_endtime = '23:59:59'
            # print 'end:',basic_endtime

            basic_startime = i['stop_start_time'][11:]
            if basic_startime == '00:00:00':
                basic_startime = '23:59:59'
            # print 'start:',basic_startime

            # 算时长
            end = datetime.datetime.strptime(basic_endtime, '%H:%M:%S')
            start = datetime.datetime.strptime(basic_startime, '%H:%M:%S')
            shicahng = end - start
            shicahngList.append(str(shicahng))
            endTimeList.append(i['stop_end_time'])
            startTimeList.append(i['stop_start_time'])


# 把存入list 中的数据 写入excel 这里是存的xls ,因为xls有行数限制 由于数据量比较大，所以分了很少Sheet页，也可以用openpyxl模块来写入，行数很大
def write_json():
    # print latList
    # print lngList
    # print shicahngList
    # print dunweiList
    # print startTimeList
    # print endTimeList
    # print len(latList)
    # print len(lngList)
    workbook1 = xlwt.Workbook()  # 新建一个工作簿
      # 在工作簿中新建一个表格
    index = 0
    sheet1 = workbook1.add_sheet(u''+str(index))
    for i in range(len(latList)):
        if(i%60000 == 0):
            index += 1
            sheet1 = workbook1.add_sheet(u'' + str(index))
        print 'i:',i
        # 写入坐标
        row = (i - (index-1) * 60000) + 1
        sheet1.write(row, 0, latList[i])
        sheet1.write(row, 1, lngList[i])
        sheet1.write(row, 2, carIdList[i])
        sheet1.write(row, 3, startTimeList[i])
        sheet1.write(row, 4, endTimeList[i])
        sheet1.write(row, 5, shicahngList[i])
        sheet1.write(row, 6, dunweiList[i])
    workbook1.save(u'最终结果1_30000.xls')  # 保存工作簿
    print 'ok'







if __name__ == "__main__":
    read_json()
    write_json()