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