Python—实现从一个Excel筛选数据生成另一个Excel(保留所有单元格式)

使用Python的pandas模块，处理Excel的筛选问题是非常方便的，
但是最大的问题就是，DataFrame导出的Excel是不具有原表格的格式的，
在网上找了很久都一无所获，相信此路不通。

能处理单元格格式的是openpyxl模块，不过要命的是，openpyxl读取的单元格的value，如果单元格是经过公式加工的，则读取的就是公式本身，而不是加工后的数据。所以以筛选数据而论，openpyxl模块远远不如pandas模块。

不过不考虑公式的问题，openpyxl实现筛选也是可行的，那就是逐行判断，不符合条件的就直接删除。不过要注意，删除了行，剩下的行的行号都要减1。这个方法的优点就是完美保留了原有Excel的所有内部格式。代码如下

#从国家列表中，筛选地区为亚洲的行
from openpyxl import load_workbook
wb=load_workbook('world.xlsx')
for sheet in wb.sheetnames:				#遍历每一个sheet
  ws=wb[sheet]
  seg='地区'                            #筛选地区为亚洲国家
  cols=next(ws.values)                  #标题行
  index=cols.index(seg)                 #地区字段的序列号
  offset=0                              #每次行被删，行号就会变化，这里是偏差
  dellist=[]                            #存储要删的行的行号列表
  for i in range(2,ws.max_row+1):
    region=ws.cell(row=i,column=index+1).value
    if(region!='亚洲'):
      dellist.append(i)
  for i in dellist:
    ws.delete_rows(i-offset)			#注意openpyxl版本必须在2.5以上否则不能
    offset+=1
wb.save('asia.xlsx')

这么搞看似完美，却出现了一个很致命的问题，执行效率很差！如果Excel文件不到100K还好，否则执行会非常缓慢。

考虑到公式的问题怎么办呢，我的思路就是二者的结合，先使用Pandas模块，把数据筛选导出成Excel。然后使用openpyxl模块，把原表的单元格式复制到新表，这里面一个难点就是必须搞清楚原表和新表的单元格的映射。比如原表的A1肯定对应新表的A1,都是标题行嘛。不过下面的呢，如果要筛选亚洲，则原表的A8对应的就是新表的A3。所以这种对应，列字母是一样的，A对应A,B对应B，但是行号就不一一定一样。而且要注意，DataFrame的第一行行号是0，但是对应Excel表格的行号就是2，错了一个2。还要注意，A是第一列，B是第二列，比如A2,就是df.iloc[0,0]

我们从原表筛选数据，然后一行行写入到新表，也就是从A1第一行，A2,第二行这么写，其中A1是标题，A2才是内容的第一行。

代码如下：

#第一步 利用Dataframe筛选生成Excel 设定原表为oldfile 新表为newfile
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter   #列编号对应的字符
from copy import copy
import pandas as pd
cellmap={}
writer=pd.ExcelWriter(newfile)			
alldata=pd.read_excel(oldfile,None)
for sheet in alldata.keys():				#遍历每一个sheet
	celllmap[sheet]={}
	data=alldata[sheet].fillna('-')
	data=data.loc[data.要筛选的字段=='某数值']
	data.to_excel(writer,sheet,index=False)
	#确认新表和原表的单元格对应关系
	k=1										#新表的单元格行号，从1开始递增		
	rowlist=[1]+list(data.index+2)			#原表的df对应的单元格行号列表
	for rowid in rowlist:
		for col in range(len(data.columns)): #col为每列的列编号	
			letter=get_column_letter(col+1)	 #col对应的列字母
			code2=letter+str(k)
			code1=letter+str(rowid)
			cellmap[sheet][code2]=code1
		k=k+1		
writer.save()
#第二步 复制原表的单元格格式到新表
wb1=load_workbook(oldfile)
wb2=load_workbook(newfiie)
for sheet in wb2.sheetnames:
  ws1=wb1[sheet]
  ws2=wb2[sheet]
  ws2.data_validations=copy(ws1.data_validations)   #复制序列等效性
  for cell2 in cellmap[sheet]:
    cell1=cellmap[sheet][cell2]
    ws2[cell2].fill=copy(ws1[cell1].fill)
    ws2[cell2].font=copy(ws1[cell1].font)
    ws2[cell2].number_format=copy(ws1[cell1].number_format)

wb2.save(newfile)