利用pythonscrip解析excel文件中的数据并发送日历邀请

2024-06-26 10:20:09 发布

您现在位置：Python中文网/ 问答频道 /正文
4115 3

我有一个输入excel文件，其中包含以下数据：

Server Name Event   Deploy Dist Type    Engineer    CR Number   Env Deployment Status   Date (IT)   Start (IT)  End (IT)    Primary Application
A   X   X   X   X   X   X   1/11/2019   8:30    12:30   X
B   X   X   X   X   X   X   1/11/2019   8:30    12:30   X
C   X   X   X   X   X   X   1/13/2019   8:30    12:30   X
D   X   X   X   X   X   X   1/13/2019   8:30    15:30   X

我需要按以下方式发送日历邀请（带日期的Groupby）

1.日期、开始时间（列[start{IT]]中的较小值）、结束时间（列（End（IT））中的值较大）

以下应打印在日历邀请正文中

^{pr2}$

2.与1相同

Server Name Event   Deploy Dist Type    Engineer    CR Number   Env Deployment Status   Date (IT)   Start (IT)  End (IT)    Primary Application
C   X   X   X   X   X   X   1/13/2019   8:30    12:30   X
D   X   X   X   X   X   X   1/13/2019   8:30    15:30   X

我得到了发送日历邀请的代码，但不确定如何解析上述输入，并根据输入文件中的日期发送多个邀请

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.utils import COMMASPACE, formatdate
from email import encoders
from tabulate import tabulate
import os, datetime
import csv
import pandas

#path_to_file = "C:\Users\kj\Desktop\Jan\sample.csv"
path_to_file = "C:/Users/kj/Desktop/Jan/sample_upd.csv"
output_to_file= "C:/Users/kj/Desktop/Jan/output.csv"
df = pandas.read_csv(path_to_file)
grouped = df.groupby('Date (IT)')
text = """
"""

html = """
<html><body><p></p>
<p></p>
{table}
<p></p>
<p></p>
</body></html>
"""
for name, group in grouped:
    dates_grp = name
    group.to_csv(output_to_file)

    CRLF = "\r\n"
    attendees = ["test@test.com"]
    organizer = "ORGANIZER;CN=organiser:mailto:test@test.com"
    fro = "test@test.com <test@test.com>"

    ddtstart = datetime.datetime.now()
    dtoff = datetime.timedelta(days=1)
    dur = datetime.timedelta(hours=1)
    ddtstart = ddtstart + dtoff
    dtend = ddtstart + dur
    dtstamp = datetime.datetime.now().strftime("%Y%m%dT%H%M%SZ")
    dtstart = ddtstart.strftime("%Y%m%dT%H%M%SZ")
    dtend = dtend.strftime("%Y%m%dT%H%M%SZ")

    description = "DESCRIPTION: OS PATCHING" + CRLF
    attendee = ""
    for att in attendees:
        attendee += "ATTENDEE;CUTYPE=INDIVIDUAL;ROLE=REQ-    PARTICIPANT;PARTSTAT=ACCEPTED;RSVP=TRUE" + CRLF + " ;CN=" + att + ";X-NUM-GUESTS=0:" + CRLF + " mailto:" + att + CRLF
    ical = "BEGIN:VCALENDAR" + CRLF + "PRODID:pyICSParser" + CRLF + "VERSION:2.0" + CRLF + "CALSCALE:GREGORIAN" + CRLF
    ical += "METHOD:REQUEST" + CRLF + "BEGIN:VEVENT" + CRLF + "DTSTART:" + dtstart + CRLF + "DTEND:" + dtend + CRLF + "DTSTAMP:" + dtstamp + CRLF + organizer + CRLF
    ical += "UID:FIXMEUID" + dtstamp + CRLF
    ical += attendee + "CREATED:" + dtstamp + CRLF + description + "LAST-MODIFIED:" + dtstamp + CRLF + "LOCATION:" + CRLF + "SEQUENCE:0" + CRLF + "STATUS:CONFIRMED" + CRLF
    ical += "SUMMARY:test " + ddtstart.strftime(
        "%Y%m%d @ %H:%M") + CRLF + "TRANSP:OPAQUE" + CRLF + "END:VEVENT" + CRLF + "END:VCALENDAR" + CRLF

    with open(output_to_file) as csvfile:
        reader = csv.reader(csvfile)
        eml_body = list(reader)
    text = text.format(table=tabulate(eml_body, headers="firstrow", tablefmt="grid"))
    html = html.format(table=tabulate(eml_body, headers="firstrow", tablefmt="html"))
    eml_body_bin = "This is the email body in binary - two steps"
    msg = MIMEMultipart('mixed')
    msg['Reply-To'] = fro
    msg['Date'] = formatdate(localtime=True)
    msg['Subject'] = "pyICSParser invite" + dtstart
    msg['From'] = fro
    msg['To'] = ",".join(attendees)

    #part_email = MIMEText(eml_body, "html")
    part_cal = MIMEText(ical, 'calendar;method=REQUEST')

    #msgAlternative = MIMEMultipart('alternative')
    msgAlternative = MIMEMultipart("alternative", None, [MIMEText(text), MIMEText(html, 'html')])
    msg.attach(msgAlternative)

    ical_atch = MIMEBase('application/ics', ' ;name="%s"' % ("invite.ics"))
    ical_atch.set_payload(ical)
    encoders.encode_base64(ical_atch)
    ical_atch.add_header('Content-Disposition', 'attachment; filename="%s"' % ("invite.ics"))

    eml_atch = MIMEBase('text/plain', ' ')
    # encoders.encode_base64(eml_atch)
    encoders.encode_7or8bit(eml_atch)
    eml_atch.add_header('Content-Transfer-Encoding', "")

    #msgAlternative.attach(part_email)
    msgAlternative.attach(part_cal)

    mailServer = smtplib.SMTP('test.test.com')
    # mailServer = smtplib.SMTP('MSGEXSV2D3906',25)
    # mailServer = smtplib.SMTP(s)
    mailServer.ehlo()
    # mailServer.starttls()
    mailServer.ehlo()
    # mailServer.login(login, password)
    mailServer.sendmail(fro, attendees, msg.as_string())
    mailServer.close()
    break

Tags： csvtotestimportdatetimeemailhtmlit
1条回答
网友
1楼 · 发布于 2024-06-26 10:20:09

Question: Parsing the data from excel file and send calendar invite

您必须使用function或class将模块化为以下内容：

  1. 模块数据

    • 将Excel数据分组
    • 逐行分析数据

  2. 模块ICAL

    • 从Excel行数据创建ICAL记录

  3. 模块邮件

    • 使用ICAL记录准备邮件
    • 发送邮件

Implementation - 1. Module Data

  1. Reading an Excel file using pandas

    import pandas
df = pandas.read_excel(open('your_xls_xlsx_filename','rb'), sheetname='Sheet 1')

  2. Loop over grouped Pandas dataframe?

    grouped = df.groupby('Date (IT)')

for name, group in grouped:
    ...

相关问题 更多 >

    编程相关推荐