把一个表格文件拆分

 1 #coding:utf8
 2 '''
 3 Created on 2018年8月18日
 4 
 5 @author: Administrator
 6 '''
 7 # from openpyxl import load_workbook
 8 # 
 9 # wb=load_workbook("empty_book.xlsx")
10 # 
11 # sheet_ranges=wb['range names']
12 # print(sheet_ranges['D18'].value)
13 
14 
15 # import openpyxl
16 # 
17 # wb=openpyxl.Workbook()
18 # ws=wb.active
19 # ws.column_dimensions.group('A','D',hidden=True)
20 # ws.row_dimensions.group(1,10,hidden=True)
21 # wb.save("group.xlsx")
22 import time,re
23 from openpyxl import load_workbook
24 
25 from openpyxl import Workbook
26 import threading
27 #新建的文件
28 nwb=Workbook(write_only=True)
29 def add_ws(nws_name):#负责增加工作表,设置名字
30     obj=nwb.create_sheet(nws_name)
31     obj.sheet_properties.tabColor="1072BA"
32     return obj
33 
34 #读的数据文件
35 wb = load_workbook(filename='demo_s.xlsx')
36 ws=wb.active
37 
38 f_name="标题"
39 add_ws(f_name)
40 for row in ws.rows: 
41     data_list=[] 
42     for cell in row:
43         a=cell.value
44         data_list.append(a)
45         sheetName=str(a)           
46         if re.search(r'^包d{1,2}$',sheetName):
47             print(cell.value)
48             new_ws=add_ws(sheetName)
49             f_name=sheetName
50     nwb[f_name].append(data_list)
51         
52             
53 nwb.save("new11.xlsx")
 1 '''
 2 Created on 2018年8月18日
 3 
 4 @author: Administrator
 5 '''
 6 import time,re
 7 from openpyxl import load_workbook
 8 from openpyxl.workbook import Workbook
 9 
10 #新建的文件
11 nwb=Workbook(write_only=True)
12 def add_ws(nws_name):#负责增加工作表,设置名字
13     obj=nwb.create_sheet(nws_name)
14     obj.sheet_properties.tabColor="1072BA"
15     return obj
16 def merge_new():
17     obj_ws=nwb["标题"]
18     for j in range(1,14):
19         obj_ws.merge_cells(start_row=2, start_column=j, end_row=4, end_column=j)
20 
21 #读的数据文件
22 wb = load_workbook(filename='demo_s.xlsx')
23 ws=wb.active
24 
25 
26 
27 ares=["标题","曹县","成武县","单县","定陶","东明县","巨野","牡丹区","郓城县","鄄城县"]
28 list_nie=["第一行","第二行","第三行","第四行"]
29 for i in ares:
30     abc=add_ws(i)#已经生成  
31     for i in range(4):
32         abc.append(list_nie)
33     
34 f_name="标题"
35 for row in ws.rows: 
36     data_list=[] 
37     for cell in row:
38         a=cell.value
39         data_list.append(a)
40         sheetName=str(a)        
41         if sheetName in ares:
42             f_name=sheetName 
43             #print(f_name)                     
44 #         if re.search(r'^包d{1,2}$',sheetName):
45 #             print(cell.value)
46 #             new_ws=add_ws(sheetName)
47 #             f_name=sheetName
48     nwb[f_name].append(data_list)
49 # merge_new()       
50 # nwb["标题"].merge_cells(start_row=2, start_column=1, end_row=4, end_column=1)         
51 nwb.save("new14.xlsx")
 1 '''
 2 Created on 2018年8月18日
 3 
 4 @author: Administrator
 5 '''
 6 import time,re
 7 from openpyxl import load_workbook
 8 from openpyxl.workbook import Workbook
 9 
10 #新建的文件write_only=True
11 nwb=Workbook()
12 def add_ws(nws_name):#负责增加工作表,设置名字
13     obj=nwb.create_sheet(nws_name)
14     obj.sheet_properties.tabColor="1072BA"
15     return obj
16 def merge_new():
17     obj_ws=nwb["标题"]
18     for j in range(1,14):
19         obj_ws.merge_cells(start_row=2, start_column=j, end_row=4, end_column=j)
20 
21 #读的数据文件
22 wb = load_workbook(filename='demo_s.xlsx')
23 ws=wb.active
24 
25 
26 
27 ares=["标题","曹县","成武县","单县","定陶","东明县","巨野","牡丹区","郓城县","鄄城县"]
28 list_nie=["第一行","第二行","第三行","第四行"]
29 for i in ares:
30     abc=add_ws(i)#已经生成  
31     for x in range(1,5):
32         for y in range(1,15):
33             abc.cell(row=x,column=y)
34     for j in range(1,14):
35         abc.merge_cells(start_row=2, start_column=j, end_row=4, end_column=j)
36     #for i in range(4):
37         
38         #abc.append(list_nie)
39     
40 f_name="标题"
41 for row in ws.rows: 
42     data_list=[] 
43     for cell in row:
44         a=cell.value
45         data_list.append(a)
46         sheetName=str(a)        
47         if sheetName in ares:
48             f_name=sheetName 
49             #print(f_name)                     
50 #         if re.search(r'^包d{1,2}$',sheetName):
51 #             print(cell.value)
52 #             new_ws=add_ws(sheetName)
53 #             f_name=sheetName
54     nwb[f_name].append(data_list)
55 # merge_new()       
56 # nwb["标题"].merge_cells(start_row=2, start_column=1, end_row=4, end_column=1)         
57 nwb.save("new15.xlsx")
添加合并的一些功能

 增加合并单元格，设置字体格式。颜色填充

  1 '''
  2 Created on 2018年8月18日
  3 
  4 @author: Administrator
  5 '''
  6 import time,re,os
  7 from openpyxl import load_workbook
  8 from openpyxl.workbook import Workbook
  9 from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
 10 from openpyxl.styles.colors import RED
 11 #字体
 12 #font = Font(name='宋体',size=13,bold=True,italic=False,vertAlign=None,underline='none',strike=False,color='FF000000')
 13 #对齐方式
 14 #alignment=Alignment(horizontal='general',vertical='bottom',text_rotation=0,wrap_text=False,shrink_to_fit=False,indent=0)
 15 #填充颜色
 16 #fill = PatternFill(start_color='FFFF33',end_color='FFFF99')
 17 fill = PatternFill(start_color ='FFFF00', end_color = 'FFFF00', fill_type = 'solid') 
 18 path=os.path.dirname(os.path.abspath(__file__))
 19 file_open=os.path.join(path,"demo_s.xlsx")
 20 file_save=os.path.join(path,"new30.xlsx")
 21 #新建的文件write_only=True
 22 nwb=Workbook()
 23 def add_ws(nws_name):#负责增加工作表,设置名字
 24     obj=nwb.create_sheet(nws_name)
 25     obj.sheet_properties.tabColor="1072BA"
 26     return obj
 27 def merge_new(obj):
 28     for j in range(1,14):
 29         obj.merge_cells(start_row=2, start_column=j, end_row=4, end_column=j)
 30     try:
 31         obj.merge_cells('N2:V2')
 32         obj.merge_cells('N3:Q3')
 33         obj.merge_cells('R3:U3')
 34         obj.merge_cells('V3:V4')
 35         
 36         obj.merge_cells('W2:AF2')   
 37         obj.merge_cells('W3:W4')
 38         obj.merge_cells('X3:Z3')
 39         obj.merge_cells('AA3:AD3')
 40         obj.merge_cells('AE3:AE4')
 41         obj.merge_cells('AF3:AF4')
 42         
 43         obj.merge_cells('AG2:AT2')
 44         obj.merge_cells('AG3:AJ3')
 45         obj.merge_cells('AK3:AN3')
 46         obj.merge_cells('AO3:AP3')
 47         obj.merge_cells('AQ3:AR3')
 48         obj.merge_cells('AS3:AS4')
 49         obj.merge_cells('AT3:AT4')
 50         
 51         obj.merge_cells('AU2:AZ2')
 52         obj.merge_cells('AU3:AV3')
 53         obj.merge_cells('AW3:AX3')
 54         obj.merge_cells('AY3:AY4')
 55         obj.merge_cells('AZ3:AZ4')
 56         
 57         obj.merge_cells('BA2:BK2')
 58         obj.merge_cells('BA3:BA4')
 59         obj.merge_cells('BB3:BC3')
 60         obj.merge_cells('BD3:BD4')
 61         obj.merge_cells('BE3:BE4')
 62         obj.merge_cells('BF3:BF4')
 63         obj.merge_cells('BG3:BG4')
 64         obj.merge_cells('BH3:BH4')
 65         obj.merge_cells('BI3:BI4')
 66         obj.merge_cells('BJ3:BJ4')
 67         obj.merge_cells('BK3:BK4')
 68         
 69         obj.merge_cells('BL2:BT2')
 70         obj.merge_cells('BL3:BL4')
 71         obj.merge_cells('BM3:BM4')
 72         obj.merge_cells('BN3:BN4')
 73         obj.merge_cells('BO3:BO4')
 74         obj.merge_cells('BP3:BP4')
 75         obj.merge_cells('BQ3:BQ4')
 76         obj.merge_cells('BR3:BR4')
 77         obj.merge_cells('BS3:BS4')
 78         obj.merge_cells('BT3:BT4')
 79         
 80         obj.merge_cells('BU2:CB2')
 81         obj.merge_cells('BU3:BU4')
 82         obj.merge_cells('BV3:BV4')
 83         obj.merge_cells('BW3:BW4')
 84         obj.merge_cells('BX3:BX4')
 85         obj.merge_cells('BY3:BY4')
 86         obj.merge_cells('BZ3:BZ4')
 87         obj.merge_cells('CA3:CA4')
 88         obj.merge_cells('CB3:CB4')
 89         
 90         obj.merge_cells('CC2:CC4')
 91     except Exception as e:
 92         print(e)
 93 def set_font(obj):
 94     for x in range(1,5):
 95         for y in range(1,82):
 96             a=obj.cell(row=x,column=y)
 97             a.font=Font(name='微软雅黑',size=14,bold=True,italic=True,color=RED)
 98         #obj.row_dimensions[x].font=Font(name='微软雅黑',size=12,bold=True,italic=True)
 99             a.fill=fill
100 # def set_bg(obj):
101 #     
102 #     for fow in obj.rows:
103 #         flag=False
104 #         for cell in row:
105 #          if re.search(r'^包d{1,2}$',str(cell.value)):
106 #             flag=True
107 #             break
108 #         if flag:
109 #             for cell in row:
110 #                 cell.fill=fill
111                     
112 
113 
114 #读的数据文件
115 wb = load_workbook(file_open)
116 ws=wb.active
117 
118 
119 
120 ares=["牡丹区","曹县","成武县","单县","定陶","东明县","巨野","郓城县","鄄城县"]
121 list_nie=["第一行","第二行","第三行","第四行"]
122 for i in ares:
123     abc=add_ws(i)#已经生成    
124 f_name="op"#op 标记的作用。
125 for row in ws.rows: 
126     data_list=[] 
127     for cell in row:
128         a=cell.value
129         data_list.append(a)
130         sheetName=str(a)
131 #         if re.search(r'^包d{1,2}$',sheetName):
132 #             
133 #             pass        
134         if sheetName in ares:
135             f_name=sheetName 
136     if f_name=="op":
137         for ss in ares:    
138             nwb[ss].append(data_list)#.font=font
139             
140     else:
141         nwb[f_name].append(data_list)
142 try:
143     for yy in ares:
144         set_bg(nwb[yy])
145         set_font(nwb[yy])
146         merge_new(nwb[yy])
147 except:
148     pass
149 finally:
150     nwb.save(file_save)
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Mengchangxin/p/9497702.html