Shulou(Shulou.com)06/01 Report--

This article is about how Python generates excel fields with adaptive width and content length. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Xlwt tool is used to generate excel field width with adaptive content length import xlwtresult = [['name', 'gender', 'age'], ['Zhang San 1111111111111111111,' male', 186], ['Li Si', 'male', 18], ['floret', 'female', 16], ['Meimei', 'female', 14] ] total = [['male (human)', 'female (human)', 'total (human)'], [2,2,4],] # get the string length The length of a Chinese text is 2def len_byte (value): length = len (value) utf8_length = len (value.encode ('utf-8')) length = (utf8_length-length) / 2 + length return int (length) # set font font = xlwt.Font () font.bold = True # set border borders = xlwt.Borders () borders.left = xlwt.Borders.THINborders.right = xlwt.Borders.THINborders.top = xlwt.Borders.THINborders .bottom = xlwt.Borders.THIN # set center alignment = xlwt.Alignment () alignment.horz = xlwt.Alignment.HORZ_CENTER # horizontal alignment.vert = xlwt.Alignment.VERT_TOP # Vertical # set background color pattern = xlwt.Pattern () pattern.pattern = xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour = 3 # background color # define different excel stylestyle1 = xlwt.XFStyle () style1.font = fontstyle1.borders = bordersstyle1.alignment = alignmentstyle2 = xlwt.XFStyle () style2.borders = bordersstyle2.alignment = alignmentstyle3 = xlwt.XFStyle () style3.borders = bordersstyle3.alignment = alignmentstyle4 = xlwt.XFStyle () style4.borders = bordersstyle4.font = fontstyle4.pattern = patternstyle4.alignment = alignmentworkbook = xlwt.Workbook (encoding='utf-8') worksheet = workbook.add_sheet ('My Worksheet') # determine the field width col_width = [] for i in range (len (result): for j in range (len (result [I]) : if I = = 0: col_width.append (len_byte (result [I] [j]) else: if col_ width [j]

10: worksheet.col (I) .width = 256* (col_ width [I] + 1) # set field height # tall_style = xlwt.easyxf ('font:height 720 ) # set font height # row0 = worksheet.row (0) # row0.set_style (tall_style) # excel content write for i in range (len (result)): for j in range (len (result [I])): if I = = 0: worksheet.write (I, j, label=result [I] [j], style=style1) else: worksheet.write (I, j, label=result [I] [j]) Style=style2) # excel Statistical results are written into for m in range (len (total)): for n in range (len (total [m])): if m = = 0: worksheet.write (m + I + 3, n + 2, label=total [m] [n], style=style4) else: worksheet.write (m + I + 3, n + 2, label=total [m] [n], style=style2) # merge cell worksheet.write _ merge (m + I + 4) M + I + 6,2,4, label=' Statistics: signature:', style=style3) workbook.save ('Excel_Workbook.xls')

Xlwt module adaptive column width write excel

Recently, I need to write a gadget to write data to excel with python. I have encountered the problem of adaptive column width writing to excel, so I would like to record the solution and process.

Operating environment: Windows10, PyCharm, Python3.6, xlwt1.3.0

Write a small dome as follows: import xlwt''' uses python's xlwt module adaptive column width to write excel'''row_num = 0 # record the number of rows written # personal information: name, sex, age, mobile phone number, landline Mailbox line_name = ['name', 'gender', 'age', 'cell phone number', 'landline', 'mailbox'] data = [['clarify', 'female','31'] data = ['clarify', 'female','31', '18745214693', '010-4784125'], ['Zhang San', 'male','26', '18245554693', '010-4784125'], ['Wang Wu', 'male'] ], ['Xiong Da', 'male', '16Zhi Zuo', '010-4784125' Zhenzheng1154125qq.com'], ['Xiong er', 'male', '22Zhong' 18745214693 'Zuozheng010-47841255q.com'] # create a Workbook object book = xlwt.Workbook (encoding= "utf-8") Style_compression=0) # create a sheet object sheet = book.add_sheet ('person_msg', cell_overwrite_ok=True) # write column name for name in range (0, len (line_name)): sheet.write (row_num, name, line_ name [name]) row_num + = write data for i in range (0, len (data)): for j in range (0, len (data [I])): sheet.write (row_num, j Data [I] [j]) row_num + = save the excel file book.save ('person_msg.xls') the generated excel file does not have any typesetting disorder

Solution idea

Gets the write character length of each column, compares the widest length of each column, and finally sets the appropriate length.

When calculating the length of a string, it should be noted that a Chinese character occupies two bytes. It is necessary to specify the character set of the string in order to obtain the correct number of characters.

> str = 'liming Li Ming' > len (str) 8 > len (str.encode ('gb18030')) 10 the final code is as follows: import xlwtimport copy''' uses the adaptive column width of python's xlwt module to write excel'''# to obtain the maximum column width def get_max_col (max_list) occupied by each column: line_list = [] # I represents the row J represents column for j in range (len (max_list [0])): line_num = [] for i in range (len (max_list)): line_num.append (max_ list [I] [j]) # stores the width of each column in line_num line_list.append (max (line_num)) # stores the maximum width of each column in line_list return line_ Listdef write_excel (): row_num = 0 # number of rows written to the record col_list = [] # record width per line # personal information: name Gender, age, cell phone number, landline Mailbox data = ['name', 'gender', 'age', 'cell phone number', 'landline', 'mailbox'], ['clarify', 'female','31', '0745214693', '0104784125', 5412546qq.com'], [' Zhang San', 'male', '26Li', '18245554693',' no'] ['Wangwu', 'male', '1919' Xiong er, 'male', '18745214693', '785992546qq.com'], [' Xiong Da', 'male', '16Fei', '010-4784125', '010-4784125qq.com'], [' Xiong er', 'male', '22Zhi' 18745214693'], ['Xiong Da', 'male', '18745214693'', '010-4784125' '3654126qq.com'] # create a Workbook object book = xlwt.Workbook (encoding= "utf-8", style_compression=0) # create a sheet object sheet = book.add_sheet ('person_msg', cell_overwrite_ok=True) col_num = [0 for x in range (0, len (data))] # write data for i in range (0, len (data): for j in range (0) Len (data [I]): sheet.write (row_num, j Data [I] [j]) col_ Num [j] = len (data [I] [j] .encode ('gb18030')) # calculate the size of each column value col_list.append (copy.copy (col_num)) # record the length of each column written row_num + = 1 # get the maximum width of each column col_max_num = get_max_col (col_list ) # set adaptive column width for i in range (0 Len (col_max_num): # 256 * number of characters to get excel column width, add two character widths sheet.col (I). Width = 256 * (col_max_ Num [I] + 2) # Save the excel file book.save ('person_msg.xls') write_excel ()

Generate excel file person_msg.xls

Thank you for reading! This is the end of the article on "how Python generates excel field width and adaptive content length". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.