Help with Creating a Script for Automating Reports - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Help with Creating a Script for Automating Reports (/thread-31719.html) |
Help with Creating a Script for Automating Reports - SunWers - Dec-29-2020 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 RE: Help with Creating a Script for Automating Reports - jjc385 - Dec-29-2020 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. |