copy format also while copying the row

[Prady A]

Hi All,

In my below program I am trying to update an existing excel template. But when I copy the existing row to a new row(Line 27) it copies the font style and color but wont copy the cell format.

Please advice to copy entire thing from the template. Thank you in advance.

Regards

1. import sys
2. import openpyxl
3.  
4. def del_row(sheet, row_index):
5.   sheet.delete_rows(row_index)
6.  
7. def copy_source(sheet, i, row_to_copy):
8.    
9.     #Get the values from the row to copy
10.     values_to_copy = []
11.     for cell in sheet[row_to_copy]:
12.          values_to_copy.append(cell.value)
13.    
14.     # Inset a new row at the next row
15.     sheet.insert_rows(row_to_copy+i)
16.    
17.     # Paste the values into the new row
18.     for column, value in enumerate(values_to_copy, start=1):
19.          sheet.cell(row=row_to_copy + i, column=column, value=value)
20.          final_row = row_to_copy + i
21.     return final_row
22.    
23. def write_to_excel(sheet, host_list):
24.     # This for loop is to copy the source row as per the number of hosts
25.     for row_index, host in enumerate(host_list, start= 1):
26.       #copy the source row
27.       next_source_row = copy_source(sheet, row_index, 17)
28.    
29.       #Update the copied row with the host
30.       sheet.cell(row=next_source_row, column=2).value = host
31.       sheet.cell(row=next_source_row, column=3).value = "UHST_"+ host.upper()
32.       sheet.cell(row=next_source_row, column=12).value = "UHST_"+ host.upper()
33.       sheet.cell(row=next_source_row, column=19).value = "UHST_"+ host.upper()
34.      
35.       #Copy the next source row
36.       next_source_row_2 = copy_source(sheet, row_index, next_source_row + 1)
37.    
38.       #Update the copied row with the host
39.       sheet.cell(row=next_source_row_2 , column=2).value = host
40.     return (next_source_row_2 - row_index - 1)
41.  
42. #split the hostnames to individual hostname
43. def split_hostnames(hostnames):
44.     host_list = hostnames[0].split()
45.     #print(host_list)
46.     return host_list
47.  
48. if __name__ == "__main__":
49. hostnames = sys.argv[1:]
50. # Split the hostnames list to individual host
51. host_list = split_hostnames(hostnames)
52. # print(len(host_list))
53. # print(host_list)
54.    
55. # Load the Excel file
56. file_name= "Template.xltm"
57. workbook = openpyxl.load_workbook(file_name, keep_vba= True)
58.  
59. # Select the Host sheet
60. sheet_name="Host"
61. sheet = workbook[sheet_name]
62.  
63. # Write to excel
64. result = write_to_excel(sheet, host_list)
65.  
66. del_row(sheet, 17)
67. del_row(sheet, result)
68.  
69. # Save the modified workbook
70. workbook.save(file_name)
71. print(" File has been updated ")