python处理Excel很方便,可以使用的库页很多,常用的有 xlwt,xlrd,openpyxl,Pandas等。
下面我展示的代码为xlwt和openpyxl的,注意建议使用openpyxl,因为xlwt处理文件的大小有限制。
from openpyxl import load_workbook
wb = load_workbook(data2name)
sheet_names = wb.get_sheet_names()
table2 = wb.get_sheet_by_name(sheet_names[0]) # index为0为第一张表
nrows2 = table2.max_row # 行
ncols2 = table2.max_column # 列
print(data2name, nrows2, ncols2)
# 注意从坐标(1,1)开始的
# print(ws.cell(2, 2).value)
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2019/12/18 19:33
# @Author : ystraw
# @Site :
# @File : main.py
# @Software: PyCharm Community Edition
# @function: 第一步,合成 excel
import xlwt
import xlrd
from openpyxl import Workbook
# 读取文本函数:
def readtext(filename):
with open(filename, 'r', encoding='utf-8-sig') as f:
str = f.read()
return str
# 写入Excel
def write_excel_xls(path, sheet_name, value):
# 获取需要写入数据的行数
index = len(value)
# 新建一个工作簿
workbook = xlwt.Workbook()
# 在工作簿中新建一个表格
sheet = workbook.add_sheet(sheet_name)
# 写入表头
sheet.write(0, 0, '站号')
sheet.write(0, 1, '经度')
sheet.write(0, 2, '纬度')
sheet.write(0, 3, '时间')
sheet.write(0, 4, '温度')
for i in range(0, index):
for j in range(0, len(value[i])):
# 像表格中写入数据(对应的行和列)
sheet.write(i+1, j, value[i][j])
# 保存工作簿
workbook.save(path)
print(path + '表格写入数据成功!')
# 写入Excel
def write_excel_xls2(path, sheet_name, value):
# 获取需要写入数据的行数
index = len(value)
wb = Workbook()
# 激活 worksheet
ws = wb.active
# 第一行输入
ws.append(['站号', '经度', '纬度', '时间', '温度' ])
# .cell(row=x, column=2, value=z.project)
# 注意:这个库的坐标是从(1,1)开始,不是(0,0)开始的
for i in range(2, index+2):
for j in range(1, len(value[i-2]) + 1):
# ws.append(value[i])
ws.cell(row=i, column=j, value=value[i-2][j-1])
# 保存
wb.save(path)
print(path + '表格写入数据成功!')
if __name__ == '__main__':
# 读入站点数据
data= xlrd.open_workbook('./data2/站点信息.xls')
table = data.sheets()[0]
#数一共由多少行,多少列
nrows = table.nrows # 行
ncols = table.ncols # 列
print(nrows, ncols)
# 构建站点字典
dict = {str(int(table.cell(0,0).value)): [table.cell(0,1).value,table.cell(0,2).value]}
for i in range(1,nrows):
zdid = table.cell(i,0).value
try:
zdid = int(zdid)
except:
pass
dict[str(zdid)]=[table.cell(i,1).value,table.cell(i,2).value]
# print('站点字典:', dict)
# 读入txt数据
datastr = readtext('./data2/201604_1.txt')
list1 = datastr.split('\n')
datalist = []
# print(datalist)
for tlist in list1:
if tlist == '' or tlist == None:
continue
# 获取二维列表
datalist.append(tlist.split('\t'))
# print(datalist)
# 存储数据
alldata = []
for tlist in datalist:
zdid = tlist[0]
# print(zdid)
# zw = dict[zdid]
try:
zw = dict[zdid]
# 加入每一行数据
alldata.append([tlist[0], zw[0], zw[1], tlist[1], tlist[2]])
# print(tlist + dict[zdid])
except Exception as exce:
# print(exce)
pass
# break
# print(alldata)
# 写入文件
write_excel_xls2('./data2/result/result.xls', 'data', alldata)