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 = app.books.open(r'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(oldsht.name)
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()
break
# 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]:
oldsht.range('5'+':'+str(r2)).delete()
break
break
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").ClearOutline
Rows("3:150").EntireRow.Hidden = False
Range("B5").Select
End Sub
'''
oldsht.activate()
ungroup = iw.macro('ungroup')
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()):
iw.sheets[0].delete()
# -----Delete redundant sheet completed
# Finally, make the first sheet active
iw.sheets[0].activate()
# 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
iw.save(r'E:\Documnet\work\07_Data analysis\Temporary file\test' + '\\' + ywb[i] + '.xlsm')
iw.close()
# 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
xlBook.Close(False)
xlApp.Quit()
# -------------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)
Mail_Item.Recipients.Add('zhuce@cuixiaoyuan.cn')
# 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 = 'zhuce@cuixiaoyuan.cn'
Mail_Item.BodyFormat = 2
Mail_Item.HtmlBody = '''
Hello Guys,<br/>
This a Mail send by Jerry'''
Mail_Item.Attachments.Add(r'E:\Documnet\work\07_Data analysis\Temporary file\test' + '\\' + ywb[i] + '.xlsx')
Mail_Item.Save()
#------------Mail sending completed----------------------------------------------------------#
print(ywb[i]+'complete')
app.quit()
