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
  Automating to run python script 100 times by changing parameters pmt 1 1,010 Dec-29-2020, 10:31 AM
Last Post: andydoc
  Need help creating a simple script Nonameface 12 1,335 Jul-14-2020, 02:10 PM
Last Post: BitPythoner
  Creating an executable from a script wolf8963 6 1,795 May-11-2020, 05:23 PM
Last Post: wolf8963
  Parsing Date/Time from Metar Reports with 6 hourly weather information Lawrence 0 691 May-03-2020, 08:15 PM
Last Post: Lawrence
  Need help creating complex loop around existing script CephloRhod 5 923 Apr-16-2020, 01:23 PM
Last Post: deanhystad
  Automating Windows GUI applications metro17 4 3,813 Feb-10-2020, 09:46 AM
Last Post: metro17
  How do I generate reports in pdf format? okbeat9 2 1,080 Jan-13-2020, 04:38 AM
Last Post: okbeat9
  Automating Excel sheets with Python wendysling 1 1,160 Mar-15-2019, 01:39 PM
Last Post: Larz60+
  Python for Reports in HTML or PDF Format ImPyBoy17 1 1,833 Dec-10-2018, 09:35 PM
Last Post: j.crater
  Automating unittest ichabod801 3 1,334 Oct-28-2018, 09:32 PM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

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