Python uses OPENPYXL to write data to an Excel table

It's really handy to write execl with OPENPYXL. Let me first introduce the relevant modules and functions used

Workbook: Workbook module, creating a workbook in memory.

Excelwriter: Use it to write data to the Exel.

Get_column_letter: Get a column name for a number, such as A,b,c

Writing data to an Excel table

#!/usr/bin/env python# _*_ coding:utf-8 _*_from openpyxl.workbook import workbookfrom openpyxl.writer.excel Import Excelwriterfrom Openpyxl.cell Import get_column_letterdata = [' ordinal ', ' group number ', ' Declaration unit ', ' name ', ' quasi-test ', ' Test score (scaled minutes ') '],[' 1 ', ' 1 ', ' Xinmin oil production plant ', ' Liu Tie ', ' 2012051224 ', ' 67.834 '],[' 2 ', ' 1 ', ' Jilin Oilfield General Hospital ', ' LV book ', ' 2012120214 ', ' 66.776 '],[' 3 ', ' 1 ', ' Jilin Oilfield General Hospital ', ' Wang Yansu ', ' 2012120718 ', ' 66.683 '],[' 4 ', ' 1 ', ' Agricultural development Company ', ' Liu Hua ', ' 2012060120 ', ' 66.664 '],[' 5 ', ' 1 ', ' petroliferous Education Office ', ' Sharisan ', ' 2012020817 ', ' 66.657 '],[' 6 ', ' 1 ', ' petroliferous Education Office ', ' Wang Zhihai ', ' 2012020914 ', ' 66.545 '],[' 7 ', ' 1 ', ' Fuyu oil production ', ' Jiang Bo ', ' 2012041210 ', ' 66.355 '],[' 8 ', ' 2 ', ' Honggang oil production plant ', ' Wang Xin ', ' 2012021226 ', ' 66.3 '],[' 9 ', ' 2 ', ' Communications company ', ' Penny ', ' 2012121014 ', ' 66.17 '],[' 10 ', ' 2 ', ' Qianguo mining ', ' Miyunrong ', ' 2012070121 ', ' 65.313 '],[' 11 ', ' 2 ', ' petroliferous Education Office ', ' Qin Qin ', ' 2012022620 ', ' 65.061 '],[' 12 ', ' 2 ', ' Storage and Sales company ', ' Xu Hua ', ' 2012130406 ', ' 64.95 '],[' 13 ', ' 2 ', ' Gangnam Property ', ' Lan Qingwei ', ' 2012030620 ', ' 64.869 '],[' 14 ', ' 2 ', ' Riverside Property ', ' Tong Winter Bud ', ' 2012110417 ', ' 64.652 '],[' 15 ', ' 2 ', ' Agricultural development Company ', ' Cao Quan ', ' 2012061025 ', ' 64.564 '],[' 16 ', ' 2 ', ' Agricultural development Company ', ' Sun Chengwei ', ' 2012060318 ', ' 64.462 '],[' 17 ', ' 3 ', ' Honggang oil plant ', ' Zhang Dayong ', ' 2012020112 ', ' 64.384 '], [' 18 ', ' 3 ', ' Storage and Sales company ', ' Liu Xianyu ', ' 2012130721 ', ' 64.378 '],[' 19 ', ' 3 ', ' Construction company ', ' Li Yue ', ' 2012010316 ', ' 64.029 '],[' 20 ', ' 3 ', ' Passenger company ' , ' Yau Jiahuan ', ' 2012130613 ', ' 63.914 '],[' 21 ', ' 3 ', ' supplies department ', ' Zhang Li ', ' 2012110925 ', ' 63.883 '],[' 22 ', ' 3 ', ' Jiangbei property ', ' Yi Changhong ', ' 2012100222 ', ' 63.723 '],[' 23 ', ' 3 ', ' Construction company ', ' Zhang Yanbing ', ' 2012013327 ', ' 63.630 '],[' 24 ', ' 3 ', ' Utility management company ', ' Wang Han ', ' 2012022214 ', ' 63.522 ']]# Create a workbook in memory OBJWB = Workbook () # writes Workbook obj to an excel file EW = Excelwriter (WORKBOOK=WB) # Sets the first sheet page, as if the first sheet only set ws =  Wb.worksheets[0]ws.title=u ' Recruit Personnel list ' # write the data to the first sheet page i = 1for line in Data:for col in range (1,len (line) +1): Colnum = Get_column_letter (col) Ws.cell ('%s%s '% (colnum,i)). Value = Line[col-1] i + = # # workbook saved to disk Wb.save (' test.xlsx ')

：

Data The second column is the group number, the number of the same group number in a cell, how to do?

#!/usr/bin/env python# _*_ coding:utf-8 _*_from openpyxl.workbook import workbookfrom openpyxl.writer.excel Import Excelwriterfrom Openpyxl.cell Import get_column_letterdata = [' ordinal ', ' group number ', ' Declaration unit ', ' name ', ' quasi-test ', ' Test score (scaled minutes ') '],[' 1 ', ' 1 ', ' Xinmin oil production plant ', ' Liu Tie ', ' 2012051224 ', ' 67.834 '],[' 2 ', ' 1 ', ' Jilin Oilfield General Hospital ', ' LV book ', ' 2012120214 ', ' 66.776 '],[' 3 ', ' 1 ', ' Jilin Oilfield General Hospital ', ' Wang Yansu ', ' 2012120718 ', ' 66.683 '],[' 4 ', ' 1 ', ' Agricultural development Company ', ' Liu Hua ', ' 2012060120 ', ' 66.664 '],[' 5 ', ' 1 ', ' petroliferous Education Office ', ' Sharisan ', ' 2012020817 ', ' 66.657 '],[' 6 ', ' 1 ', ' petroliferous Education Office ', ' Wang Zhihai ', ' 2012020914 ', ' 66.545 '],[' 7 ', ' 1 ', ' Fuyu oil production ', ' Jiang Bo ', ' 2012041210 ', ' 66.355 '],[' 8 ', ' 2 ', ' Honggang oil production plant ', ' Wang Xin ', ' 2012021226 ', ' 66.3 '],[' 9 ', ' 2 ', ' Communications company ', ' Penny ', ' 2012121014 ', ' 66.17 '],[' 10 ', ' 2 ', ' Qianguo mining ', ' Miyunrong ', ' 2012070121 ', ' 65.313 '],[' 11 ', ' 2 ', ' petroliferous Education Office ', ' Qin Qin ', ' 2012022620 ', ' 65.061 '],[' 12 ', ' 2 ', ' Storage and Sales company ', ' Xu Hua ', ' 2012130406 ', ' 64.95 '],[' 13 ', ' 2 ', ' Gangnam Property ', ' Lan Qingwei ', ' 2012030620 ', ' 64.869 '],[' 14 ', ' 2 ', ' Riverside Property ', ' Tong Winter Bud ', ' 2012110417 ', ' 64.652 '],[' 15 ', ' 2 ', ' Agricultural development Company ', ' Cao Quan ', ' 2012061025 ', ' 64.564 '],[' 16 ', ' 2 ', ' Agricultural development Company ', ' Sun Chengwei ', ' 2012060318 ', ' 64.462 '],[' 17 ', ' 3 ', ' Honggang oil plant ', ' Zhang Dayong ', ' 2012020112 ', ' 64.384 '], [' 18 ', ' 3 ', ' Storage and Sales company ', ' Liu Xianyu ', ' 2012130721 ', ' 64.378 '],[' 19 ', ' 3 ', ' Construction company ', ' Li Yue ', ' 2012010316 ', ' 64.029 '],[' 20 ', ' 3 ', ' Passenger company ' , ' Yau Jiahuan ', ' 2012130613 ', ' 63.914 '],[' 21 ', ' 3 ', ' supplies department ', ' Zhang Li ', ' 2012110925 ', ' 63.883 '],[' 22 ', ' 3 ', ' Jiangbei property ', ' Yi Changhong ', ' 2012100222 ', ' 63.723 '],[' 23 ', ' 3 ', ' Construction company ', ' Zhang Yanbing ', ' 2012013327 ', ' 63.630 '],[' 24 ', ' 3 ', ' Utility management company ', ' Wang Han ', ' 2012022214 ', ' 63.522 ']]# Create a workbook in memory OBJWB = Workbook () # writes Workbook obj to an excel file EW = Excelwriter (WORKBOOK=WB) # Sets the first sheet page, as if the first sheet only set ws =  Wb.worksheets[0]ws.title=u ' Recruit Personnel list ' # write the data to the first sheet page i = 1for line in Data:for col in range (1,len (line) +1): Colnum = Get_column_letter (col) Ws.cell ('%s%s '% (colnum,i)). Value = Line[col-1] i + = # ========= Merge cell Area ===========li =    []TMP1 = []TMP2 = []tmp3 =[]for line in Data: # Enumeration method, a total of three sets of data, to find out the number of each group of data V = line[1] if v = = ' 1 ': Tmp1.append (v) If v = = ' 2 ': Tmp2.append (v) If v = = ' 3 ': Tmp3.append (v) li.append (TMP1) li.append (TMP2) Li.append (Tmp3) # Define the starting position of a merged cell Start_row = 2end_row = 2start_column = 2end_column = 2fLi:end_row = Start_row + len (each)-1 ws.merge_cells (Start_row=start_row,end_row=end_row,start_column=start_ Column,end_column=end_column) Start_row = end_row + # ========= Merge cell area ===========# workbook saved to disk Wb.save (' test.xlsx ')

：

I want to create a sheet page again

# Create another sheet page ws = Wb.create_sheet (title=u ' second sheet page ')

　　

Python uses OPENPYXL to write data to an Excel table