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?
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')