Use xlwings to split excel and call outlook to send mail

Posted by lyealain on Sun, 02 Feb 2020 17:03:26 +0100

The reason for writing this code is that the company is going to issue a sales daily report, but since the sales area managers of different departments can only see the data of their own area, each workbook has more than 10 worksheets. If the workload is huge by hand, there is an urgent need to do some automatic operations on the report and send emails automatically. On the basis of the above, we should also consider the beauty.

import xlwings as xw
import win32com.client as win32

 //When the program is running, it will not be displayed in the front end or create a new Excel window
app = xw.App(visible=False,add_book=False)

ywb = ["Zhejiang Department","Henan Department","KA Ministry","Heilongjiang Department","Guangxi Department","Jiangxi Department","Hainan Department","Guangdong Department"]
for i in range(0,len(ywb)):  # This is the beginning of the cycle. Read the number of business units. Each business unit acts as a maximum cycle
    # print(i)
    iw ='E:\Documnet\work\07_Data analysis\Temporary file\copy.xlsm')
    sheet_count = iw.sheets.count # Number of sheet s to read the entire Workbook

   # Because there are many hidden worksheets in the source file that can't be distributed to all regions, and all file formats are#It's the same, so here we define a function sheet [u names(),
    //It is used to read the position of the first "total item sales" in the worksheet, and as the first table of the distribution area

    def first_sheet():
        for ii in range(0, sheet_count):
            if iw.sheets[ii].name == "Sales volume of all products":
                return ii
    # break # It is used for testing when writing code, and it interrupts the program periodically
    # range is left closed and right open, so it needs to be different from python's habit of counting sequence from 0. The row and column count of xlwings starts from 1, so the total number of pages + 1 is needed
    for sht in range(first_sheet(),sheet_count+1): # Start looping through each page
        oldsht = iw.sheets(sht)
        # print(
        rownumber = oldsht.range('A10000').end('up').row
        # Another way to write it is rownumber = oldsht.used'range.last'cell.row
        # print(rownumber)
        # Because it's impossible to copy and paste at present, I use the method of changing directly in the source file and saving as
        # The following cycle starts from the last row up until the corresponding cell is found. rownumber and 1 represent the range, and - 1 represents the step of each cycle
        for r1 in range(rownumber,1,-1):
            # print(oldsht.cells(r,2).value)
            # print(oldsht.cells(r,2).value)
            # print(ywb[i])
            if oldsht.cells(r1,2).value == ywb[i]:
                # You can use range to directly represent rows or columns, but both rows and columns should be in the form of text. Letters refer to columns, and numbers refer to rows
                oldsht.range(str(r1+1) + ':' + str(500)).delete()
        # As there may be other business departments above the target business department, it needs to be deleted again from the top down
        print('Stage 1 complete')
        while i != 0:
            for r2 in range(5,rownumber):
                if oldsht.cells(r2,2).value == ywb[i-1]:
        print('Phase II completion')
        #----The above is to delete all unnecessary lines
        #-----The following is the call macro to cancel the original combination and the hidden line. The macro code is as follows:
        Sub ungroup()
        Rows("3:150").EntireRow.Hidden = False
        End Sub
        ungroup = iw.macro('ungroup')
        #-----Then the following cycle is to delete the redundant sheets. Delete as many sheets as you want
    for iii in range(0,first_sheet()):
    # -----Delete redundant sheet completed
    # Finally, make the first sheet active

    # Because I tried many methods, there was no easy way to copy and paste the sheet with format, so I had to go the opposite way, directly in the source file
    # Make changes in, then save the changed source file as and close'E:\Documnet\work\07_Data analysis\Temporary file\test' + '\\' + ywb[i] + '.xlsm')
    # The transformation is basically completed here, but the problem is that because there are macros in the source workbook, it must be saved as xlsm format. xlwings has not found a way to directly save as xlsx for now
    # Therefore, we can only use win32com module to call excel's own saveas interface to save as operation, and we also need win32com to send email
    # --------------Here is save as xlsx
    xlApp = win32.DispatchEx("Excel.Application")
    xlApp.Visible = True
    xlApp.DisplayAlerts = 0
    xlBook = xlApp.Workbooks.Open(r'E:\Documnet\work\07_Data analysis\Temporary file\test' + '\\' + ywb[i] + '.xlsm', False)
    xlBook.SaveAs(r'E:\Documnet\work\07_Data analysis\Temporary file\test' + '\\' + ywb[i] + '.xlsx', FileFormat=51)
    #The above FileFormat calls Excel's own save as interface, and 51 is the code of xlsx
    # -------------Save as end
    #---------The above is to obtain and operate the workbook. The following is to operate the mail. The modules used are win32----------#
    outlook = win32.Dispatch('Outlook.Application')
    Mail_Item = outlook.CreateItem(0)
    # You should first create a mail group in the email address book, and directly enter the name of the mail group to send
    Mail_Item.Subject = ''
    Mail_Item.BodyFormat = 2
    Mail_Item.HtmlBody = '''
                         Hello Guys,<br/>
                         &nbsp;&nbsp;&nbsp;&nbsp;This a Mail send by Jerry'''
    Mail_Item.Attachments.Add(r'E:\Documnet\work\07_Data analysis\Temporary file\test' + '\\' + ywb[i] + '.xlsx')
    #------------Mail sending completed----------------------------------------------------------#
Published 9 original articles, won praise 3, visited 5791
Private letter follow

Topics: Excel Python