copy format also while copying the row

126 views
Skip to first unread message

Prady A

unread,
Jul 25, 2023, 10:16:57 PM7/25/23
to openpyx...@googlegroups.com
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 ")
Reply all
Reply to author
Forward
0 new messages