+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: OpenPyXl formatting issues (/thread-35645.html)
OpenPyXl formatting issues - kpayney1 - Nov-26-2021
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')