处理excel文件的python库有很多,如xlsxwriter、xlrd、openpyxl等。由于经常用pandas处理数据,而且pandas读写excel更方便,所以主要讲pandas是如何操作excel的。也介绍了如何分别使用用xlsxwriter和openpyxl向excel中插入图片,如何在不覆盖原excel的基础上追加新的内容。
####1 生成excel文件
建立工作簿 => 将数据写入sheet表中 => 保存数据
DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)
'''
该函数主要参数为:excel_writer。
excel_writer:写入的目标excel文件,可以是文件路径、ExcelWriter对象;
sheet_name:被写入的sheet名称,string类型,默认为'sheet1';
engine:也很重要,可以与openpyxl库对接。
na_rep:缺失值表示,string类型;
header:是否写表头信息,布尔或list of string类型,默认为True;
index:是否写行号,布尔类型,默认为True;
encoding:指定写入编码,string类型。
'''
import pandas as pd
writer = pd.ExcelWriter(path) #path为工作簿的路径和名称
data.to_excel(writer, 'sheet1') #sheet1为工作表的名称, data为DataFrame数据
writer.save()#保存数据的步骤不能少
####2 读取excel文件
有两种方式读取excel:
(1)pd.read_excel()
pd.read_excel(io, sheetname=0, header=0, skiprows=None, skip_footer=0, index_col=None, names=None, parse_cols=None, parse_dates=False, date_parser=None, na_values=None, thousands=None, convert_float=True, has_index_names=None, converters=None, dtype=None, true_values=None, false_values=None, engine=None, squeeze=False, **kwds)
'''
该函数主要的参数为io、sheetname、header、names、encoding。
io:excel文件,可以是文件路径、文件网址、file-like对象、xlrd workbook;
sheetname:返回指定的sheet,参数可以是字符串(sheet名)、整型(sheet索引)、list(元素为字符串和整型,返回字典{'key':'sheet'})、none(返回字典,全部sheet);
header:指定数据表的表头,参数可以是int、list of ints,即为索引行数为表头;
names:返回指定name的列,参数为array-like对象。
encoding:关键字参数,指定以何种编码读取。
该函数返回pandas中的DataFrame或dict of DataFrame对象,利用DataFrame的相关操作即可读取相应的数据。
'''
data = pd.read_excel('excel文件的路径和名称')
print(data)
(2)读取工作簿 => 读取sheet表中的数据
import pandas as pd
writer = pd.ExcelFile(path) #path为工作簿的路径和名称.注意是pd.ExcelFile, 不是pd.ExcelWriter
data2 = writer.parse('sheet1') #读取名为sheet1的表格,data2为DataFrame
print(data2)
####3 xlsxwriter处理excel(向excel中插入图片)
内容丰富,包括用excel作图,向excel中插入图片等。
在工作中需要向excel中插入图片,查了一下资料,遇到一个问题。
import xlsxwriter
work_book=xlsxwriter.Workbook(PATH)
sheet_1 = work_book.add_worksheet("sheet1")
sheet_1.insert_image('A1','图片.jpg')#可以用png格式
workbook.close()
上面代码不完整,只是为了说明,如果图片是jpg的格式,在workbook.close()时会出错,改成png就没问题了。
更多内容,可以参考:
Python自动化运维笔记(八):XlsxWriter模块实现Excel操作(下)
Python Excel操作模块XlsxWriter之插入图片worksheet.insert_image()
4 在已有的工作簿中追加新的工作表,但不覆盖以前的内容。
方法一
原工作簿中不能含有图片,否则追加新的sheet后,图片会消失。
import openpyxl
import pandas as pd
from pandas import DataFrame
data = DataFrame([{"a":2, "b":3}, {"a":4, "b":5}])
work_book = openpyxl.load_workbook('D:\\work\\loan_rules\\rules_loan_2018-03-06\\2018-03-06.xlsx') # 这是原有的工作簿
writer = pd.ExcelWriter('D:\\work\\loan_rules\\rules_loan_2018-03-06\\2018-03-06.xlsx', engine='openpyxl') # 新建一个同路径同名的工作簿,关键要指定engine='openpyxl'
writer.book = work_book # 将读取的原工作簿赋值给新的工作簿writer
writer.sheets = dict((ws.title, ws) for ws in work_book.worksheets) # 原工作簿中的所有表格也要赋值过去
#[print(ws.title) for ws in work_book.worksheets]#可以查看一下有哪些表格
data.to_excel(writer, sheet_name='yj_new') # 将data数据保存到新工作簿中的新建的sheet中
writer.save()# 新工作簿writer覆盖原工作簿work_book
#####方法二
如果原工作簿中含有图片,即使追加了新数据, 虽然原有的sheet不会消失,但原sheet中的图片会消失。所以可以先写入数据,再用openpyxl追加图片。
import openpyxl
work_book = openpyxl.load_workbook('D:\\work\\loan_rules\\rules_loan_2018-03-07\\2018-03-07.xlsx') # 这是现有的工作簿,只有sheet1表
for image_name in ["贷前规则随时间变化拆线图", "贷中规则随时间变化拆线图", "贷前规则柱状图", "贷中规则柱状图"]:
img = openpyxl.drawing.image.Image(PATH_2 + '\\' + image_name + '.png')#提取图片,转化后再用add_image添加。
work_book.create_sheet(title=image_name).add_image(img, 'A1')#create_sheet创建新表格
####处理txt文件
data = pd.read_table(path, header=0, encoding='utf-8', sep=分隔符, names=列名)
data = pd.read_table('D:\\work\\15.txt', header=0, encoding='utf-8', sep='\t', names=['借款id','姓名','身份证'])
#路径名和文件名尽量不要用中文名
#如果列名太多了,从txt文件中复制所有列名,放在字条串s中,并用s.split()转化成列表,就不用给列名单独加上引号。
####处理csv文件
如果生成的csv文件无法被excel打开怎么办?
data.to_csv('D:\\work\\15.csv', sep=',', encoding='gbk')
请使用逗号作分隔符,并且使用gbk作为编码方式。
更多内容,请参考:
python用openpyxl操作excel