Python 发送邮件 and 编辑Excel

记录一下Python 发送邮件的代码，这是半年前写的，不知道现在有什么类库的改动。

 

类库

import smtplib

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

from email.header import Header

import datetime

 

def SendEmail(self,SendEmailExcelUrl,ProjectName):
       
        sender ='发送人邮箱'
        senderPwd =os.environ.get('EmailPassword') #邮箱密码我这里是放在环境变量中了（Win）
        receivers ='123@123.com,123@123.com' #接收人邮箱
        #create a Instance
        message = MIMEMultipart()
        message['From'] = Header("发送人", 'utf-8')
        message['To'] =  Header("邮件内容标题", 'utf-8')
        subject = '邮件标题’
        message['Subject'] = Header(subject, 'utf-8')
        #Message body content
        message.attach(MIMEText(' Dear All, \n\n ××× \n\n Regards, \n ××××××× ', 'plain', 'utf-8'))
        #Send xlsx file 
        att = MIMEText(open(SendEmailExcelUrl, 'rb').read(), 'base64', 'utf-8')
        att["Content-Type"] = 'application/octet-stream'
        #Here you can rename the attachments in the message.
        att["Content-Disposition"] = 'attachment; filename="{}.xlsx"'.format(ProjectName)
        message.attach(att)
        try:
            smtpObj = smtplib.SMTP('代理服务器地址','代理服务器端口')
            smtpObj.starttls()
            smtpObj.login(sender, senderPwd)#代理服务器帐号密码验证
            smtpObj.sendmail(sender, receivers, message.as_string())
            #terminating the session
            smtpObj.quit()
            print("Send email successful")
        except  smtplib.SMTPException as e:
            print(e.__doc__,datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            print(e.__context__,datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))

 

下面是编辑excel 仅仅用于记录

　　python编辑excel 还是比较坑的，个人观点。但是我是调用一个接口，该接口返回excel,该excel的样式什么的都有，如果我直接进行保存这样没问题，但是我需要对其加一列然后在保存excel就被破坏了，后来了解到目前该类库对样式的编辑支持的还不是很好

 

import xlrd

import xlwt

from openpyxl import Workbook ,load_workbook

 

    def CreateCoreExcel(self,SendEmailExcelUrl,ApiSaveExcelSaveUrl):
        projectConfigJson=self.getProductfigJson()['Core']
        Group= projectConfigJson['Group']
        wb = load_workbook(ApiSaveExcelSaveUrl)
        ws = wb['Vulnerabilities']
        ws.insert_cols(2)
        ws.insert_cols(3)
        for index, row in enumerate(ws.rows):
            if index == 0:
                row[1].value='Group'
                row[2].value='ServiceName'
            else:
                values= row[22].value
                validationValues=True
                try:
                    values.split('\\')
                except Exception as e:
                    print(e.__doc__,datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                    print(e.__context__,datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
                    validationValues=False
                    print("values not is path values:{}, datetime:{} ".format(values,datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
                if validationValues == True:
                    strlist = values.split('\\')
                    serviceName=strlist[3]
                    groupName=""
                    if serviceName in Group['1']:
                        groupName="1"
                    elif serviceName in Group['2']:
                        groupName="2"
                    elif serviceName in Group['3']:
                        groupName="3"
                    else:
                        groupName="OtherGroup"
                    row[1].value=groupName
                    row[2].value=serviceName
                else :
                     row[1].value="N/A"
                     row[2].value="N/A"
        content = []
        index = 1
        for i in range(2,ws.max_row+2): 
            contentJson ={}
            for j in range(1,ws.max_column+1):  
                contentJson[ws.cell(index,j).value]=ws.cell(i,j).value
            content.append(contentJson)
        jsonstr=json.dumps(content)

        wbnew = Workbook() 
        sheet = wbnew.active
        listHead=[]
        data= json.loads(jsonstr)
        for c,i in enumerate(data[0].keys()):
            sheet.cell(row=1,column=c+1,value=i)
            listHead.append(i)
        for r,i in enumerate(data):
            row=r+2
            for c,d in enumerate(listHead):
                sheet.cell(row=row,column=c+1,value=i.get(d,""))
        returnValue=os.path.exists(SendEmailExcelUrl)
        if returnValue==True:
            os.remove(SendEmailExcelUrl)
        wbnew.save(SendEmailExcelUrl)

　　该内容只是个人记录。