# -*- coding:utf-8 -*-
from openpyxl import Workbook
import matplotlib.pyplot as plt
from matplotlib.pyplot import MultipleLocator
import numpy as np
import xlrd
import xlwt
from xlutils.copy import copy
import sys
reload(sys)
sys.setdefaultencoding('utf8')
# 初始化字典
dic = {'00.00_00.00':1}
# 判断key 是否在键值对中存在
def write_dic(lng,lat):
keyss = lng + '_' + lat
# 判断key 是否存在 ,存在返回value 不存在返回 默认值
val = dic.get(keyss,'None')
if val == 'None':
dic[keyss] = 1
else:
dic[keyss] = val + 1
# 读取 excel ,xlsx和xls都行
def read_excel():
workbook = xlrd.open_workbook(u'C:/pythonProject/pachongdemo/最终.xlsx') # 打开工作簿
sheets = workbook.sheet_names() # 获取工作簿中的所有表格
worksheet = workbook.sheet_by_name(sheets[0]) # 获取第一个Sheet1页
# 按索引循环excel内容
for i in range(1,994986):
print '进度================:' ,i
# 获取哪一行 哪一列的数据
a = worksheet.cell_value(i, 0)
b = worksheet.cell_value(i, 1)
index = str(a).index('.')
# print 'index : ',index
x1 = str(a)[:index + 3]
print 'x1 : ', x1
index1 = str(b).index('.')
# print 'index : ',index
y1 = str(b)[:index1 + 3]
print 'y1 : ', y1
write_dic(x1, y1)
# 循环把字典中的数据存入xlsx ,xlsx只能用openpyxl模块来写入,xls则可以用xlwt模块来写入 (xls最大行数是 65536行,xlsx最大行数是1048576行 )
def write():
outwb = Workbook()
outws = outwb.create_sheet(index=0)
index = 0
# 循环字典
for key, value in dic.items():
index +=1
list = key.split('_')
outws.cell(index, 2).value = list[0]
outws.cell(index, 3).value = list[1]
outws.cell(index, 4).value = value
# 保存
outwb.save('demo.xlsx')
print 'ok'
if __name__ == "__main__":
read_excel()
# 打印最终字典内容
print 'res : ', dic
write()
# for i in range(len(dic)):
python 使用openpyxl模块写入excel (大数据量的时候,先把数据存入列表或者字典,统一写入excel)
最新推荐文章于 2024-05-25 17:03:05 发布