Python operation Excel merge cells

Every programming language will encounter operation Excel! This article mainly talks about how to merge cells when Python operates on Excel.

table of Contents

1. Effect picture

2. Method used

3. Complete code

1

Effect picture

2

Method used

There are many libraries for operating Excel in Python. This time we use xlsxwriter to write Excel. Python version 3.6.

xlsxwriter can be installed directly with pip. Create a new Excel, which can contain multiple sheets. In the program, you need to operate on specific work sheets, which is like operating Excel under windows.

add_worksheet add a sheet
add_format add a style, such as centered, left, etc.
write write a row of data
merge_range('A1:A7')Merge Cells,A2-A7
set_column set the width of the column, etc.

3

Complete code

import os
import xlsxwriter

def get_history():

 # write excel，construct dict
 filename ='333.xlsx'
 # Delete result file
 if(os.path.isfile(filename)):
  os.remove(filename)

 list ={}

 # Note that the dictionary can no longer be embedded in the python dictionary!
 list['name1']=[]
 list['name1'].append({'title':'title-1-1','comments':[{'content':'comments-name-1-1','created_at':'2020-04-13 12:17:10'},{'content':'comments-name-1-2','created_at':'2020-04-13 12:17:10'}]})

 list['name1'].append({'title':'title-1-2','comments':[{'content':'comments-name-1-2','created_at':'2020-04-13 12:17:10'},{'content':'comments-name-1-3','created_at':'2020-04-13 12:17:10'}]})

 list['name2']=[]
 list['name2'].append({'title':'title-2-1','comments':[{'content':'comments-name-2-1','created_at':'2020-04-13 12:17:10'},{'content':'comments-name-2-2','created_at':'2020-04-13 12:17:10'},{'content':'comments-name-2-3','created_at':'2020-04-13 12:17:10'}]})

 list['name3']=[]
 list['name3'].append({'title':'title-3-1','comments':[{'content':'comments-name-3-1','created_at':'2020-04-13 12:17:10'}]})write_to_excel(filename,list)

def write_to_excel(output,list):

 workbook = xlsxwriter.Workbook(output)

 # define sheet name
 sheetName ='History and Comment'
 worksheet_s = workbook.add_worksheet(sheetName)

 # Style the cell
 header = workbook.add_format({'bg_color':'#F7F7F7','color':'black','align':'center','valign':'top','border':1})

 merge_format = workbook.add_format({'bold': True,'border':6,'align':'center',  #Center horizontally
  ' valign':'vcenter',  #Vertically centered
  ' fg_color':'#D7E4BC',  #Color fill
    })

 bold_cell = workbook.add_format({'bold': True,'align':'left','valign':'top','text_wrap': True,'border':1})

 cell = workbook.add_format({'align':'left','valign':'top','text_wrap': True,'border':1})

 # The following are other styles
 bold_header = workbook.add_format({'bold': True,'bg_color':'#F7F7F7','color':'black','align':'center','valign':'top','border':1})

 cell_center = workbook.add_format({'align':'center','valign':'top','border':1})

 title = workbook.add_format({'bold': True,'font_size':14,'align':'center','valign':'vcenter'})

 # title
 header_define =['Name','Titile','Commit Date','Comments']for i inrange(0,len(header_define)):
  worksheet_s.write(0, i, header_define[i], header)

 a_col_width =50
 b_col_width =50
 c_col_width =50
 d_col_width =50
 # add data into the table
 data_row =1
 data_row_2 =1
 data_row_1 =1for one_name in list.keys():

  # Write the third and fourth columns first
  for one_history in list[one_name]:print(one_history)for one_comment in one_history['comments']:
    worksheet_s.write_string(data_row,2, one_comment['created_at'], bold_cell)
    worksheet_s.write_string(data_row,3, one_comment['content'], cell)
    data_row = data_row+1
   # Write second column
   historys_len =len(one_history['comments'])if historys_len ==1:
    worksheet_s.write_string(data_row_2,1, one_history['title'], bold_cell)
    data_row_2 = data_row_2 +1else:print('B'+str(data_row_2)+':B'+str(data_row_2+historys_len))
    worksheet_s.merge_range('B'+str(data_row_2+1)+':B'+str(data_row_2+historys_len), one_history['title'], merge_format)
    data_row_2 = data_row_2+historys_len

  # Write the first column
  if data_row_2 - data_row_1 ==1:
   worksheet_s.write_string(data_row_1,0, one_name, bold_cell)else:
   worksheet_s.merge_range('A'+str(data_row_1 +1)+':A'+str(data_row_2),one_name, merge_format)
  data_row_1 = data_row_2

 worksheet_s.set_column('A:A', a_col_width)
 worksheet_s.set_column('B:B', b_col_width)
 worksheet_s.set_column('C:C', c_col_width)
 worksheet_s.set_column('D:D', d_col_width)
 workbook.close()
 # xlsx_data = output.getvalue()
 # return xlsx_data

def test():
 excle1 = xlsxwriter.Workbook("test.xlsx")
 worksheet = excle1.add_worksheet()

 worksheet.write("A1","hello world")
 excle1.close()if __name__ =='__main__':get_history()

END