Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
OpenPyXl formatting issues
#1
Hi all,
I have written a code that pulls certain columns and rows of values from one sheet and copies it into a template then saves it as another file name. The issue that i am having is that the files i am pulling data from have formatting locked and even after i have done everything i can, the cells in the new file won't change their background colour unless i go in manually and clear all formatting and redo it by hand. Any ideas as to why openpyxl isn't just pulling the values across but the formatting as well?


import openpyxl as xl
from openpyxl.styles import PatternFill
from openpyxl.styles import Protection

#load template
template = xl.load_workbook("C:/WRAC.xlsm",data_only=True)  # Add file name
temp_sheet1 = template["2 WRAC"]  # Add Sheet name
temp_sheet2 = template["1 WRAC Front Page "]  # Add Sheet name

#load sheet to pull data from
wb1 = xl.load_workbook('C:/AirConditioning.xlsx', data_only=True)
ws1 = wb1["1. EQUIPMENT"]
ws2 = wb1['2. PROCESS']
ws3 = wb1['Cover Sheet']


#calculate max row
mr1 = ws1.max_row
mr2 = ws2.max_row
mr3 = ws3.max_row

lastrow = 0

#Transfer Equipment tab
for r in range (10, mr1 + 1):
    for c in range (3, 4):
        #reading values
        value = ws1.cell(row = r, column = c)
        #export to destination
        temp_sheet1.cell(row = r, column = c).value = value.value
    for c in range (4, 5):
        #reading values
        value = ws1.cell(row = r, column = c)
        #export to destination
        temp_sheet1.cell(row = r, column = 6).value = value.value
    for c in range (6, 9):
        #reading values
        value = ws1.cell(row = r, column = c)
        print(value)
        #export to destination
        temp_sheet1.cell(row = r, column = c+4).value = value.value
    for c in range (9, 10):
        #reading values
        value = ws1.cell(row = r, column = c)
        #export to destination
        temp_sheet1.cell(row = r, column = 13).value = value.value
    for c in range (10, 11):
        #reading values
        value = ws1.cell(row = r, column = c)
        #export to destination
        temp_sheet1.cell(row = r, column = 19).value = value.value
    for c in range (1, 2):
        #reading values
        value = ws1.cell(row = r, column = c)
        #export to destination
        temp_sheet1.cell(row = r, column = 1).value = value.value
    lastrow += 1


#transfer equipment tab
for r in range (10, mr2 + 1):
    for c in range (3, 4):
        #reading values
        value = ws2.cell(row = r, column = c)
        #export to destination
        temp_sheet1.cell(row = lastrow + r , column = c).value = value.value
    for c in range(4, 5):
        # reading values
        value = ws2.cell(row=r, column=c)
        # export to destination
        temp_sheet1.cell(row=lastrow + r , column=6).value = value.value
    for c in range(6, 9):
        # reading values
        value = ws2.cell(row=r, column=c)
        print(value)
        # export to destination
        temp_sheet1.cell(row=lastrow + r, column=c + 4).value = value.value
    for c in range(9, 10):
        # reading values
        value = ws2.cell(row=r, column=c)
        # export to destination
        temp_sheet1.cell(row=lastrow + r, column=13).value = value.value

    for c in range(10, 11):
        # reading values
        value = ws2.cell(row=r, column=c)
        # export to destination
        temp_sheet1.cell(row=lastrow + r, column=19).value = value.value

    for c in range(1, 2):
        # reading values
        value = ws2.cell(row=r, column=c)
        # export to destination
        temp_sheet1.cell(row=lastrow + r, column=1).value = value.value


#transfer coversheet tab
value = ws3.cell(row = 11, column = 2)
temp_sheet2.cell(row = 11, column = 1).value = value.value
value = ws3.cell(row = 8, column = 2)
temp_sheet2.cell(row = 8, column = 1).value = value.value


template.save(str("C:///testfile.xlsx"))

#for index in range(1, 11):
    #rest of code
   #wb2.save(f'C:/data{index}.xlsx')
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Formatting issues? Mark17 1 678 Dec-30-2020, 04:17 PM
Last Post: Mark17
  Openpyxl: Excel formula & condition formatting removed JaneTan 0 1,154 Sep-25-2020, 07:02 AM
Last Post: JaneTan
  Formatting Data/Time with Pyodbc and openpyxl bearcats6001 0 993 Aug-17-2020, 03:44 PM
Last Post: bearcats6001
  Python random formatting issues Barnettch3 10 3,482 Jan-28-2018, 11:04 PM
Last Post: Gribouillis
  Openpyxl issues DodoMerlin 1 2,252 Jul-04-2017, 08:16 PM
Last Post: sparkz_alot

Forum Jump:

User Panel Messages

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