Python Forum
Help with Creating a Script for Automating Reports
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Help with Creating a Script for Automating Reports
#1
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)

[Image: As0uelj.jpg]

Is there a different way I should be doing this?

Thank,
Veronica
Reply
#2
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.
SunWers likes this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python best library for Excel reports & review of existing code MasterOfDestr 4 498 Feb-14-2024, 03:39 PM
Last Post: MasterOfDestr
Question Is there a python program for automating this invoicing workflow? PG_Archipelago 3 1,029 Feb-02-2023, 11:01 PM
Last Post: Larz60+
  Automating to run python script 100 times by changing parameters pmt 1 2,563 Dec-29-2020, 10:31 AM
Last Post: andydoc
  Need help creating a simple script Nonameface 12 4,429 Jul-14-2020, 02:10 PM
Last Post: BitPythoner
  Creating an executable from a script wolf8963 6 5,645 May-11-2020, 05:23 PM
Last Post: wolf8963
  Parsing Date/Time from Metar Reports with 6 hourly weather information Lawrence 0 2,290 May-03-2020, 08:15 PM
Last Post: Lawrence
  Need help creating complex loop around existing script CephloRhod 5 2,706 Apr-16-2020, 01:23 PM
Last Post: deanhystad
  Automating Windows GUI applications metro17 4 12,176 Feb-10-2020, 09:46 AM
Last Post: metro17
  How do I generate reports in pdf format? okbeat9 2 3,220 Jan-13-2020, 04:38 AM
Last Post: okbeat9
  Automating Excel sheets with Python wendysling 1 2,535 Mar-15-2019, 01:39 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020