布景介绍

重视下面大众号”z先生的备忘录”,这儿有关于数据洞察、可视化、大模型等相关技巧、实战教程、干货共享~

!pipinstallpyjanitor==0.26.0
!pipinstallplottable==0.1.5
!pipinstallmpl_font==1.1.0
!pipinstallxlsxwriter==3.2.0
!pipinstallopenpyxl==3.1.2

本文目录

• 布景介绍
• excel相关的布景常识
• 对单个单元格进行操作
• 将2维list数组刺进excel中
• 设置单元格款式
• 刺进图表到excel文件中
• 刺进超链接在单元格中
• 显现迷你图
• 设置单元格维护避免修正
• 显现色阶图
• 显现条件格局图
• 运用calamine读取excel大文件数据功能测验
• 将生成的excel文件发送到’老板’的邮箱
  • 以163为例，翻开邮箱设置对应的smtp服务
  • 编写python代码将excel文件发送到指定邮箱
• 参阅文档

excel相关的布景常识

Excel中常见的文件格局(xls, xlsx, xlsm, xlsb, xla, xlam等等);在操作excel类常见的有三大数据目标，别离如下:

• WorkBook：作业簿目标
• Sheet：表单目标
• Cell：表格目标 本文主要运用XlsxWriter库(github.com/jmcnamara/X…~

对单个单元格进行操作

#导入xlsxwriter库，用于创立Excel文件
importxlsxwriter
#创立一个名为"demo.xlsx"的作业簿（即Excel文件）
workbook=xlsxwriter.Workbook("demo.xlsx")
#在作业簿中增加一个名为'sheet1'的作业表
worksheet=workbook.add_worksheet(name='sheet1')
#在B1单元格写入数值500
worksheet.write("B1",500)
#在第0行第1列等价于B1单元格写入数值500
worksheet.write(0,1,500)
#在B2单元格写入公式，核算B1单元格数值的平方
worksheet.write_formula("B2","{=SUM(B1*B1)}")
#设置A列的宽度为40
worksheet.set_column("A:A",40)
#创立两个格局目标，别离设置缩进级别为1和2
indent1=workbook.add_format({"indent":1})#第1级缩进格局
indent2=workbook.add_format({"indent":2})#第2级缩进格局
#运用不同的缩进格局在A1和A2单元格写入文本，并设置相应的缩进
worksheet.write("A1","Thistextisindented1level",indent1)#A1单元格内容缩进1级
worksheet.write("A2","Thistextisindented2levels",indent2)#A2单元格内容缩进2级
#封闭并保存作业簿
workbook.close()

将2维list数组刺进excel中

#界说表格的表头信息以及相关数据
headings=["Number","Batch1","Batch2"]#表头列表
data=[#数据列表
[2,3,4,5,6,7],#榜首行：编号
[40,40,50,30,25,50],#第二行：批次1的数据
[30,25,30,10,5,10],#第三行：批次2的数据
]
#创立一个加粗格局款式以突出显现表头
bold=workbook.add_format({"bold":1})
#将表头信息写入到榜首行（从A1开端）
worksheet.write_row("A1",headings,bold)
#将数据按列依次写入作业表
worksheet.write_column("A2",data[0])#写入编号数据到A列
worksheet.write_column("B2",data[1])#写入批次1的数据到B列
worksheet.write_column("C2",data[2])#写入批次2的数据到C列
#封闭并保存作业簿
workbook.close()

设置单元格款式

#创立三种不同对角线款式的格局：
format1=workbook.add_format({"diag_type":1})#-format1:对角线类型1
format2=workbook.add_format({"diag_type":2})#-format2:对角线类型2
format3=workbook.add_format({"diag_type":3})#-format3:对角线类型3
#创立一个带有赤色对角线、对角线类型为3且边框线型为7的格局
format4=workbook.add_format({
"diag_type":3,
"diag_border":7,
"diag_color":"red",
})
#运用不同对角线款式写入单元格内容
worksheet.write("B1","Text",format1)#运用format1款式写入
worksheet.write("B2","Text",format2)#运用format2款式写入
worksheet.write("B3","Text",format3)#运用format3款式写入
worksheet.write("B4","Text",format4)#运用format4款式写入
#创立一个居中并对齐、加粗的单元格格局
centered_format=workbook.add_format({"align":'center','valign':'vcenter',"bold":True})
#运用居中对齐格局写入单元格内容
worksheet.write("A2","Text",centered_format)
#最终封闭并保存作业簿
workbook.close()

刺进图表到excel文件中

#在作业表的A1单元格嵌入图片"demo.png"
#注意：此操作默许会依照图片原始尺度刺进
worksheet.insert_image("A1","demo.png")
#注：若要完成图片自适应单元格大小，或许需要在刺进图片时直接指定宽度和高度。
worksheet.insert_image("A1","demo.png",{"x_scale":0.5,"y_scale":0.5})
#最终，封闭并保存作业簿
workbook.close()

刺进超链接在单元格中

#设置榜首列（A列）的宽度为30
worksheet.set_column("A:A",30)
#创立一个样本格局：赤色字体、加粗、下划线、字号为12
red_format=workbook.add_format({
"font_color":"red",#文字色彩设为赤色
"bold":1,#字体加粗
"underline":1,#文字下划线
"font_size":12,#字号为12
})
#写入一些超链接
#A1单元格包括一个隐式格局的超链接至http://www.python.org/
worksheet.write_url("A1","http://www.python.org/")
#A3单元格包括一个显现文本为"PythonHome"的超链接至http://www.python.org/
worksheet.write_url("A3","http://www.python.org/",string="PythonHome")
#A5单元格包括一个提示信息为"Clickhere"的超链接至http://www.python.org/
worksheet.write_url("A5","http://www.python.org/",tip="Clickhere")
#A7单元格包括一个运用了赤色格局的超链接至http://www.python.org/
worksheet.write_url("A7","http://www.python.org/",red_format)
#A9单元格包括一个电子邮件链接至jmcnamara@cpan.org，显现文本为"Mailme"
worksheet.write_url("A9","mailto:jmcnamara@cpan.org",string="Mailme")
#最终，封闭并保存作业簿
workbook.close()

显现迷你图

#示例数据集，每个列表代表一行数据
data=[
[-2,2,3,-1,0],#榜首行数据
[30,20,33,20,15],#第二行数据
[1,-1,-1,1,-1],#第三行数据
]
#将示例数据写入到作业表中
worksheet.write_row("A1",data[0])#写入榜首行数据
worksheet.write_row("A2",data[1])#写入第二行数据
worksheet.write_row("A3",data[2])#写入第三行数据
#在F1单元格增加一条带标记点的默许折线型sparkline图表（根据A1到E1区域的数据）
worksheet.add_sparkline("F1",{"range":"Sheet1!A1:E1","markers":True})
#在F2单元格增加一条柱状sparkline图表，采用非默许风格（根据A2到E2区域的数据）
#同时显现榜首个点和最终一个点
worksheet.add_sparkline("F2",{
"range":"Sheet1!A2:E2",
"type":"column",
"style":12,
"first_point":True,
"last_point":True
})
#在F3单元格增加一条胜败（Win/Loss）类型的sparkline图表（根据A3到E3区域的数据）
#高亮显现负值区域，并显现最高点和最低点
worksheet.add_sparkline("F3",{
"range":"Sheet1!A3:E3",
"type":"win_loss",
"negative_points":True,
"high_point":True,
"low_point":True
})
#最终，封闭并保存作业簿
workbook.close()

设置单元格维护避免修正

#创立两种具有维护特点的单元格格局：
#-unlocked:不确定的格局，能够修改
#-hidden:躲藏格局，公式不可见
unlocked=workbook.add_format({"locked":False})#创立不确定的格局
hidden=workbook.add_format({"hidden":True})#创立躲藏公式的格局
#设置A列的宽度为40，以便更好地展现文本
worksheet.set_column("A:A",40)
#敞开作业表维护模式
worksheet.protect()#默许情况下一切单元格都会被确定
#别离写入确定、解锁及躲藏公式的单元格内容
worksheet.write("A1","CellB1islocked.Itcannotbeedited.")#B1默许确定
worksheet.write("A2","CellB2isunlocked.Itcanbeedited.")
worksheet.write("A3","CellB3ishidden.Theformulaisn'tvisible.")
#写入具有不同维护特点的公式
worksheet.write_formula("B1","=1 2")#默许确定，无法修改公式
worksheet.write_formula("B2","=1 2",unlocked)#公式地点单元格已解锁，能够修改
worksheet.write_formula("B3","=1 2",hidden)#公式地点单元格被躲藏，公式不可见
#封闭并保存作业簿
workbook.close()

显现色阶图

这儿运用openpyxl库来完成色阶图输出到excel文件中。

fromopenpyxlimportWorkbook
fromopenpyxl.formatting.ruleimportColorScaleRule
#创立一个新的作业簿
workbook=Workbook()
#挑选或创立一个作业表
worksheet=workbook.active
worksheet.title='ColorScaleTest'
#假定咱们有以下一列测验数据
data=[30,60,90,120,150,180,210,240,270,300]
#将数据写入单元格
forrow,valueinenumerate(data,1):
ws.cell(row=row,column=1,value=value)
#界说色阶规矩
rule=ColorScaleRule(start_type='min',start_color='FF0000',
mid_type='percentile',mid_value=50,mid_color='FFFF00',
end_type='max',end_color='00FF00')
#运用色阶到数据地点列(这儿是榜首列A)
worksheet.conditional_formatting.add('A1:A{}'.format(len(data)),rule)
#保存Excel文件
wb.save('demo.xlsx')

显现条件格局图

这儿运用openpyxl库来完成条件图输出到excel文件中。

#导入所需库
fromopenpyxlimportWorkbook
fromopenpyxl.formatting.ruleimportColorScaleRule,DataBarRule
fromopenpyxl.styles.colorsimportColor
#1.创立一个新的Excel作业簿
workbook=Workbook()
#2.挑选或获取作业簿的榜首个作业表，并设置其标题
worksheet=workbook.active
worksheet.title='ColorScaleTest'
#3.假定有如下一列测验数据
data=[30,60,90,120,150,180,210,240,270,300]
#4.将测验数据写入Excel作业表的A列，从第二行开端（Excel表格的榜首行默许为表头）
forrow_index,valueinenumerate(data,1):#运用enumerate()函数，索引从1开端
worksheet.cell(row=row_index,column=1,value=value)#设置单元格内容
#5.界说数据地点的区域，即A2到A10这一列（因为enumerate()从1开端，所以此处是A1到A10）
data_range=worksheet['A1:A10']
#6.创立一个DataBarRule实例，用于在指定范围内运用数据条（色彩条）条件格局
rule=DataBarRule(
start_type='min',#数据条的起点根据该列的最小值
end_type='max',#数据条的结尾根据该列的最大值
color=Color(rgb='00FF00'),#数据条的色彩为绿色
showValue=True,#显现数据条旁边的实践数值
minLength=None,#数据条的最小长度将由程序主动核算，坚持默许值
maxLength=None#数据条的最大长度将由程序主动核算，坚持默许值
)
#7.将数据条条件格局规矩运用到指定的单元格区域
worksheet.conditional_formatting.add('A1:A10',rule)#将规矩运用到A1到A10这一列
#8.保存已运用条件格局的作业簿至Excel文件
workbook.save('demo.xlsx')#文件名：demo.xlsx

运用calamine读取excel大文件数据功能测验

#生成一个50000行、5列的随机数二维数组
data=np.random.random(size=(50000,5))
#将生成的随机数组转换为PandasDataFrame，这是一个表格型数据结构，便于进行数据剖析和操作
pf=pd.DataFrame(data)
#运用openpyxl引擎写入excel文件
%timeitpf.to_excel("demo.xlsx",index=False,engine='openpyxl')
#4.33s16.1msperloop(meanstd.dev.of7runs,1loopeach)
#运用calamine引擎读excel文件
%timeitpd.read_excel("demo.xlsx",engine='calamine')
#344ms1.76msperloop(meanstd.dev.of7runs,1loopeach)
#运用openpyxl引擎读excel文件
%timeitpd.read_excel("demo.xlsx",engine='openpyxl')
#3.33s26.7msperloop(meanstd.dev.of7runs,1loopeach)

经过实验看出： 在5万行*5数据中，经过calamine引擎读excel文件比传统openpyxl快9倍多。

将生成的excel文件发送到’老板’的邮箱

以163为例，翻开邮箱设置对应的smtp服务

编写python代码将excel文件发送到指定邮箱

importsmtplib
fromemail.mime.multipartimportMIMEMultipart
fromemail.mime.baseimportMIMEBase
fromemail.mime.textimportMIMEText
fromemail.utilsimportCOMMASPACE
fromemailimportencoders
importos
#装备SMTP服务器和登录信息
smtp_server='smtp.163.com'#运用163邮箱作为发送服务器，也能够运用其他邮箱
smtp_port=25#端口号
username='your_email@163.com'#你的163邮箱，也能够运用其他邮箱
password='xxxxx'#你邮箱对应smtp的授权密码
sender_email='your_email@163.com'
receiver_emails=['recipient1@qq.com',]#收件人列表
#创立MIMEMultipart目标以便包容多部分邮件内容
msg=MIMEMultipart()
msg['From']=sender_email
msg['To']=COMMASPACE.join(receiver_emails)
msg['Subject']='测验文件'
#创立邮件正文
body='这是今日的测验文档.'
msg.attach(MIMEText(body))
#翻开并读取Excel文件
filename='demo.xlsx'#要发送的Excel文件途径
withopen(filename,'rb')asf:
attachment=MIMEBase('application','vnd.openxmlformats-officedocument.spreadsheetml.sheet')
attachment.set_payload(f.read())
encoders.encode_base64(attachment)#对附件内容进行base64编码
attachment.add_header('Content-Disposition',f'attachment;filename="{os.path.basename(filename)}"')
msg.attach(attachment)
#连接SMTP服务器并发送邮件
try:
server=smtplib.SMTP(smtp_server,smtp_port)
server.starttls()#假如运用的对错安全端口，需要启用TLS加密
server.login(username,password)
server.sendmail(sender_email,receiver_emails,msg.as_string())
server.quit()
print("Emailsentsuccessfully.")
exceptExceptionase:
print("Erroroccurredwhilesendingemail:",e)

效果展现

参阅文档

1. github.com/jmcnamara/X…
2. github.com/DeepSpace2/…
3. openpyxl.readthedocs.io/en/stable/t…
4. github.com/tafia/calam…

z先生说

今日给我们共享运用python来完成主动化作业处理excel文件数据，并将结果主动发送老板邮箱，干货满满~
假如本文对你有协助，还请你点赞在看转发。你的支撑就是我创造的最大动力，重视下面大众号不走失~