Kirish I. Umumiy qism

Python funktsiyasi yordamida hisobotni avtomatlashtirish
Endi hisobot tayyor bo'lgach, biz barcha kodlarimizni hisobot yaratishni avtomatlashtiradigan funktsiyaga qo'yishimiz mumkin. Va keyingi safar biz bunday hisobotni yaratmoqchi bo'lganimizda, biz faqat fayl nomini kiritishimiz va kodni ishga tushirishimiz kerak bo'ladi.
Eslatma. Ushbu funktsiya ishlashi uchun fayl nomi "sales_month.xlsx». Bundan tashqari, biz savdo faylining oy/yilini o'zgaruvchi sifatida ishlatadigan bir nechta kod satrlarini qo'shdik, shunda biz uni yakuniy fayl va hisobot sarlavhasida qayta ishlatishimiz mumkin.
Quyidagi kod qo'rqinchli tuyulishi mumkin, ammo bu biz yuqorida yozgan barcha narsalarni birlashtirishdir. Bundan tashqari, yangi o'zgaruvchilar file_namemonth_nameva month_and_extension.

1. 
   import pandas as pd
   
2. 
   import openpyxl
   
3. 
   from openpyxl import load_workbook
   
4. 
   from openpyxl.styles import Font
   
5. 
   from openpyxl.chart import BarChart, Reference
   
6. 
   import string
   
7. 
   
8. 
   def automate_excel(file_name):
   
9. 
   """The file name should have the following structure: sales_month.xlsx"""
   
10. 
    # read excel file
    
11. 
    excel_file = pd.read_excel(file_name)
    
12. 
    # make pivot table
    
13. 
    report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
    
14. 
    # splitting the month and extension from the file name
    
15. 
    month_and_extension = file_name.split('_')[1]
    
16. 
    # send the report table to excel file
    
17. 
    report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
    
18. 
    # loading workbook and selecting sheet
    
19. 
    wb = load_workbook(f'report_{month_and_extension}')
    
20. 
    sheet = wb['Report']
    
21. 
    # cell references (original spreadsheet)
    
22. 
    min_column = wb.active.min_column
    
23. 
    max_column = wb.active.max_column
    
24. 
    min_row = wb.active.min_row
    
25. 
    max_row = wb.active.max_row
    
26. 
    # adding a chart
    
27. 
    archart = BarChart()
    
28. 
    data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
    
29. 
    categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
    
30. 
    barchart.add_data(data, titles_from_data=True)
    
31. 
    barchart.set_categories(categories)
    
32. 
    sheet.add_chart(barchart, "B12") #location chart
    
33. 
    barchart.title = 'Sales by Product line'
    
34. 
    barchart.style = 2 #choose the chart style
    
35. 
    # applying formulas
    
36. 
    # first create alphabet list as references for cells
    
37. 
    alphabet = list(string.ascii_uppercase)
    
38. 
    excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
    
39. 
    # sum in columns B-G
    
40.