# -*- 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()
python 解析excel json数据 存入excel (先存入内存,最后统一写入excel)
最新推荐文章于 2024-07-23 15:48:30 发布