Openpyxl Help

[SunWers]

Hello Everyone,

I've been working on getting some scripts together to help me automate some of the reports that I do daily and I think I'm about there but I have a few questions. I enjoy working with coding so this is kind of a hobby for me as well. I have around five scripts created and the process is probably the long way around, but I'm thinking once I get these to do what I need them to, I will then try to work on combining them all together if possible. I have a report that I copied a worksheet from that has two columns of data that isn't keeping the original formatting.

The steps I took to get me here...

First, I created a script that added a sheet (Import-A) to a marker report (Week 01.xlsx) where I could copy cut data to this report.

from openpyxl import load_workbook

dest_wb = load_workbook('H:/cutter-reports/01-January/output/Week 01.xlsx')

dest_sheet = dest_wb.create_sheet("Import-A",0)

dest_wb.save('H:/cutter-reports/01-January/output/Week 01.xlsx') 

Second, I copied the import data from our cutter to the Marker report.

from openpyxl import load_workbook

src_wb = load_workbook('H:/cutter-reports/01-January/raw-data/Cutter A - Importv1.xlsx')
dest_wb = load_workbook('H:/cutter-reports/01-January/output/Week 01.xlsx')
src_sheet = src_wb['import']
dest_sheet = dest_wb['Import-A']

for i in range(1, src_sheet.max_row+1):
    for j in range(1, src_sheet.max_column+1):
        dest_sheet.cell(row=i, column=j).value = src_sheet.cell(row=i, column=j).value

dest_wb.save('H:/cutter-reports/01-January/output/Week 01.xlsx')

When I checked the Marker Report (Week 01.xlxs) I noticed the formatting for the date and time didn't tranfer over correctly. The date I wanted in format mm-dd-yyyy and the time I wanted hh:mm:ss AM/PM.

So I wrote another script to format the column I need corrected. I'm working on the Date format but I haven't got to the time format yet. But my date still isn't exactly right, I tried changing the number_format to 'MM-DD-YYYY' but it give me an error. How could I get the format I need for this column?

from openpyxl.styles import NamedStyle

# Workbook/Worksheet to copy
wb=load_workbook('H:/cutter-reports/01-January/output/Week 01.xlsx')
ws=wb['Import-A']

# create date style:
date_style = NamedStyle(name='date_style', number_format='DD.MM.YYYY HH:MM:MM')

# apply the style to the column B of the default sheet:
ws = wb.active
for row in ws[2:ws.max_row]:  # skip the header
    cell = row[1]             # column B
    cell.style = date_style

Thanks,
Veronica

[Larz60+]

can you supply a sample of each input file format.
You can alter or make up the data if it is sensitive.
Need something to run code against.

I'd also suggest that you look into python Pandas.

[SunWers]

can you supply a sample of each input file format.
You can alter or make up the data if it is sensitive.
Need something to run code against.

I'd also suggest that you look into python Pandas.

Hi,

Attached is a link to the files with all my info. Also below is a little explanation of what each one is for....

Cutter File Info

Cutter-A Importv1 Imported cut file from the cutters

Week 17 is a completed Cutter Report from last year. This gives an idea of what it should look like. Column's B,C,D,and E are the column's I'm trying to change to the correct format.

Step 01 adds the dates to the cutter reports for both Cutter A and Cutter B

Step 02 adds the shifts for each cutter

Step 03 adds a worksheet to the cutter report for importing the cut data in Step 04

Step 04 Imports the cut data to the cutter report.

If you need anything else just let me know.

Thanks,
Veronica