Excel处理模块
打开文件:
-
创建:
1 2 3 4 5 6 7 8
| from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
print(sheet.title) sheet.title = "Salary luffy"
|
-
打开已有文件:
1 2
| from openpyxl import load_workbook wb2 = load_workbook("文件名.xlsx")
|
写数据
1 2 3 4 5 6 7 8 9
| sheet["C5"] = "hello" sheet["C7"] = "hi"
sheet.append([1,2,3])
sheet["A3"] = datetime.datetime.now().strfrime("%Y-%m-%d")
|
选择表
1 2 3 4 5 6 7 8
| ws3 = wb["New Title"] ws4 = wb.get_sheet_by_name("New Title")
print(wb.get_sheet_name())
sheet = wb.worksheets[0]
|
保存表
遍历表
按行遍历
1 2 3 4
| for row in sheet: for cell in row: print(cell.value, end=",") print()
|
按列遍历
1 2 3 4
| for column in sheet.columns: for cell in column: print(cell.value, end=",") print()
|
遍历指定行和列
1 2 3 4 5
| for row in sheet.iter_rows(min_row = 2,max_row = 5, max_col = 5): for cell in row: print(cell.value,end=",") print()
|
遍历指定几列的数据
1 2 3 4
| for col in sheet.iter_cols(min_col = 2, max_col = 5): for i in col: print(i.value,end = "") print()
|
删除工作表
1 2
| we.remove(sheet) def wb[sheet]
|
给指定cell设置值
1
| sheet1.cell(row=i, column=m, value=dataRow[i-1][m-1])
|
设置表的格式
需要导入的类:
1
| from openpyxl.styles import Font,colors,Alignment
|
字体
下面的代码指定了等线24号,加粗斜体,字体颜色红色。直接使用cell的font属性,将Font对象赋值给它。
1 2
| bol_itatic_24_font = Font(name='等线',size=24,italic=True,color=colors.RED,bold=True) sheet [ 'A1 ' ].font = bold_itatic_24_font
|
对齐方式
也是直接使用cell的属性aligment,这里指定垂直居中和水平居中。除了center,还可以使用
right、left等等参数。
1 2
| sheet[ 'B1 ' ].alignment = Alignment (horizontal='center ', vertical='center')
|
设置行高&列宽
1 2 3
| sheet.row_dimensions[ 2 ].height = 40 sheet.column_dimensions[ 'C' ].width = 30
|
使用python发动邮件
邮件发送smtplib(邮件授权码:uxqgzxwrzjimebge)
SMTP(Simple Mail Transfer Protocol )简单邮件传输协议,它是一组用于由源地址到目的地址发送邮件的规则,由他控制信件的中转方式。
想实现发送邮件需要经过以下几步
- 登录邮件服务器
- 构造符合邮件协议规则要求的邮件内容
- 发送
Python对SMTP支持有smtplib和email两个模块,email负责构造邮件, smtplib负责发送邮件,他对tmep协议进行了简单的封装
- 发送纯文本文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| import smtplib from email.mime.text import MIMEText from email.header import Header
stmp_obj = smtplib.SMTP_SSL("smtp.qq.com", 465)
stmp_obj.login("2152889763@qq.com", "uxqgzxwrzjimebge")
msg = MIMEText("Hello, I'm from England. What's your name,Can I be your pen pal", "plain", "utf-8")
msg["From"] = Header("Dave", "utf-8")
msg["To"] = Header("Mr.zhang", "utf-8")
msg["Subject"] = Header("Dave", "utf-8")
Receiver = ["2622599434@qq.com", "2174489554@qq.com"]
stmp_obj.sendmail("2152889763@qq.com", Receiver, msg=msg.as_string()) print("OVER!")
|
-
发送html文件:
1 2 3 4 5 6 7 8
| mail_body = ''' <h5>你好啊!</h5> <p> <a href="网址">这是我新创建的网址,要来看看吗</a> </P> '''
msg = MIMEText(mail_body, "html", "utf-8")
|
-
添加图片:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| import smtplib from email .mime . image import MIMEImage from email .mime.multipart import MIMEMultipart from email.mime.text import MIMEText from email.header import Header
smtp_obj = smtplib.SMTP_SSL ("smtp.exmail.qq.com", 465)
smtp_obj.login ("nami@luffycity.com","333dsfsf#$#")
smtp_obj.set_debuglevel(1)
mail_body =''' <h5>hello</h5> <p> 你好 <p><img src="cid :image1"> </p> '''
msg_root = MIMEMultipart ('related') msg_root [ "From" ] = Header("来自娜美的问候", "utf-8") msg_root [ "To" ] = Header ( "有缘人","utf-8") msg_root [ "subject" ] = Header ("娜美的信", "utf-8" )
msgAlternative = MIMEMultipart ( 'alternative ' ) msgAlternative.attach (MIMEText ( mail_body,'html ', 'utf-8 ' ) ) msg_root.attach (msgAlternative)
fp = open("image.jpg", 'rb') msgImage = MIMEImage(fp.read()) fp.close()
msgImage.add_header("Content-ID", "<image>") msg_root.attach(msgImage)
smtp_obj.sendmail("发件人邮箱", "收件人邮箱", msg=msg_root.as_string())
|