Help with Creating a Script for Automating Reports

[SunWers]

Hi,

I'm new to Phython and I'm working on trying to automate some reports that I've done manually for years. I work for a manufacturing facility that makes garments. Some detail on the reports that I'm trying to automate is a cutter report. I create this report weekly with information on the cutting processes. Such as how long it takes to cut a particular spread, interruption times, idle times, etc. I have eight worksheets to one workbook. Four sheets are for Cutter A and the other four sheets are for Cutter B. Each cutter has one worksheet for cut data that is imported in from the cutters, second worksheet shows a daily percentage between all operations, third sheet shows a weekly percentage between all operations, and the last sheet is the maintenance log for that week. When I'm finished creating the workbook to add this imported data in, I have 52 workbooks for the year, one for each week. I will get to importing the data into the spreadsheets through Python later, once I figure out how to automate getting the workbooks setup to add the imported data to. I have four workbooks that I use as a base to create these weekly reports from. I have the first three workbooks created through a Spyder script with the script loading from a base workbook. I'm thinking that I'm going the long way around to do this... For example, for adding the date to each workbook here is the script I'm using:

# Week 01
# Cutter A - Marker Report 
# Cutter B - Marker Report

# Import 'load_workbok' module from 'openpyxl'
from openpyxl import load_workbook

# Workbook/Worksheet to copy dates from
wb1=load_workbook('Weekly Data.xlsx')
ws1=wb1['DateData']

# Workbook/Worksheet to paste dates to
wb=load_workbook('Weekly-temp.xlsx')
ws=wb['Cutter A - Marker Report']
ws2=wb['Cutter B - Marker Report']

# Week 01 Date - Worksheet: Cutter A - Marker Report
ws.cell(row=4,column=3).value=ws1.cell(row=4,column=6).value # Monday
ws.cell(row=62,column=3).value=ws1.cell(row=4,column=8).value # Tuesday
ws.cell(row=120,column=3).value=ws1.cell(row=4,column=10).value # Wednesday
ws.cell(row=178,column=3).value=ws1.cell(row=4,column=12).value # Thursday
ws.cell(row=236,column=3).value=ws1.cell(row=4,column=14).value # Friday
ws.cell(row=294,column=3).value=ws1.cell(row=4,column=16).value # Saturday

# Week 01 Date - Worksheet: Cutter B - Marker Report
ws2.cell(row=4,column=3).value=ws1.cell(row=4,column=6).value # Monday
ws2.cell(row=62,column=3).value=ws1.cell(row=4,column=8).value # Tuesday
ws2.cell(row=120,column=3).value=ws1.cell(row=4,column=10).value # Wednesday
ws2.cell(row=178,column=3).value=ws1.cell(row=4,column=12).value # Thursday
ws2.cell(row=236,column=3).value=ws1.cell(row=4,column=14).value # Friday
ws2.cell(row=294,column=3).value=ws1.cell(row=4,column=16).value # Saturday

# Saving the excel file using "wb.save" method
wb.save("H:/Gerber Cutter Reports/2021/01 - January/Week 01.xlsx")

When moving to the next week only change to this script would be changing the row # and adding the save file for the next workbook (Week 01.xlsx then Week 02.xlsx)

# Week 02 Date - Worksheet: Cutter A - Marker Report
ws.cell(row=4,column=3).value=ws1.cell(row=5,column=6).value # Monday
ws.cell(row=62,column=3).value=ws1.cell(row=5,column=8).value # Tuesday
ws.cell(row=120,column=3).value=ws1.cell(row=5,column=10).value # Wednesday
ws.cell(row=178,column=3).value=ws1.cell(row=5,column=12).value # Thursday
ws.cell(row=236,column=3).value=ws1.cell(row=5,column=14).value # Friday
ws.cell(row=294,column=3).value=ws1.cell(row=5,column=16).value # Saturday

# Week 02 Date - Worksheet: Cutter B - Marker Report
ws2.cell(row=4,column=3).value=ws1.cell(row=5,column=6).value # Monday
ws2.cell(row=62,column=3).value=ws1.cell(row=5,column=8).value # Tuesday
ws2.cell(row=120,column=3).value=ws1.cell(row=5,column=10).value # Wednesday
ws2.cell(row=178,column=3).value=ws1.cell(row=5,column=12).value # Thursday
ws2.cell(row=236,column=3).value=ws1.cell(row=5,column=14).value # Friday
ws2.cell(row=294,column=3).value=ws1.cell(row=5,column=16).value # Saturday

# Saving the excel file using "wb.save" method
wb.save("H:/Gerber Cutter Reports/2021/01 - January/Week 02.xlsx")

Also, I'm confused about how to move to the monthend spreadsheet. I'm thinking I need to add a loop or something. How the monthend works is, according to the year, some months have 4 weeks and some months have 5 weeks. So the last workbook of the month will have an extra worksheet with the monthend data. This is when I use the Weekly Data worksheet that I link to, to pull the dates for the workbooks. It tells me how many weeks I need for each month and when I need a monthend workbook.

Base Workbooks
Weekly-temp.xlsx (normal weekly sheet)
Monthend 4 Week-temp.xlsx (when monthend has 4 weeks)
MOnthend 5 Week-temp.xlsx (when montend has 5 weeks)
Weekly Data.xlsx (Data for each month that I pull dates for each week from)



Is there a different way I should be doing this?

Thank,
Veronica

[jjc385]

For the monthly report, it sounds like you mostly need to find all the weeks in a month (be it four or five). You could do this in a couple of ways -- I would probably use column B of the spreadsheet in the screenshot. Given a row of that sheet, you can check whether the corresponding week is the last of the month (that is, the following week belongs to a different month, but be careful you don't run out of rows). You can also find all previous weeks which belong to the same month, by looping backward until you find a row/week corresponding to a different month (or you run out of rows).

Good luck with this project! I think there are a lot of improvements that can make this script easier to use, but for now I think you're right to focus on getting it working.