python之excel加工处理小案例一则

 一、工具用途

工作中，需要对各类excel进行加工处理，当表和字段比较多时，关联条件又有多个，每次通过execl的vlookup之类的关联公式手工可以解决工作需求，但一般耗时较长，且人工统计匹配也存在出错的情况。

如果是常态会的excel加工场景工作，那么python工具值得你去研究开发一下，是时候解放双手了。

二、场景说明

假设某教育集团有三类表

A表

B表

C表

最终要根据以上三个表进行关联处理，输出目标表格如下

假设这个教育集团有10几个分校，每个分校都有这三种格式相同的表，每张表中都有若干不等的数据。现需要进行所有分校表的汇总。

这里需要解决的问题有：

1. 所有表之间关联需要有个关键词，例如这里的ID号

2. 汇总表里需要根据所属分校新增字段，例如学校名称

3. 所有表汇总的数据要有合并和去重的功能

4. 汇总表里需要将原本一些列的数据转为行数据，如各科的成绩。

三、python相关知识点

1、需要导入的模块

import pandas as pd

import os

import time

from openpyxl import load_workbook

from openpyxl.utils import get_column_letter

from openpyxl.styles import Alignment

2.模块的使用

读取execl内容
aa=pd.read_excel(r'aa.xlsx')
过滤excel需要的字段
 aa0=aa[['ID','姓名']]
复制excel表格内容
aa0=aa0.copy()
替换excel中列名字段
 aa0.rename(columns={'姓名': '个人姓名', inplace=True)
帅选excel中数据
aal = aa0.loc[(aa0['性别'] == '男')]
添加excel中的列
 col_name=df.columns.tolist()
 col_name.insert(2, '课程')，并往列中添加数据
 df=df.reindex(columns=col_name)
 df['课程'] = "数学"
输出内容到新表格
 df.to_excel('result1.xlsx',index=None)
 #匹配两张表的数据
 df1 = pd.read_excel('result1.xlsx')
 df2 = pd.read_excel('result2.xlsx')
 file1=pd.merge(df1,df2,on=['姓名','ID'],how='outer')
 file1.to_excel('result3.xlsx',index=None)
#合并两张表数据
 result1=pd.concat([tmp,ac2],axis=0)
 result1.drop_duplicates(inplace=True)#数据做去重
 result1.to_excel('resultfinal.xlsx',index=None)

3.彩蛋打印心形

    myData = "love"
    for char in myData.split():
        allChar = []
        for y in range(12, -12, -1):
            lst = []
            lst_con = ''
            for x in range(-30, 30):
                formula = ((x * 0.05) ** 2 + (y * 0.1) ** 2 - 1) ** 3 - (x * 0.05) ** 2 * (y * 0.1) ** 3
                if formula <= 0:
                    lst_con += char[(x) % len(char)]
                else:
                    lst_con += ' '
            lst.append(lst_con)
            allChar += lst
        print('\n'.join(allChar))
        time.sleep(5)

4.最终输出excel格式的处理

def reset_col(filename):
    wb=load_workbook(filename)
    for sheet in wb.sheetnames:
        ws=wb[sheet]
        df=pd.read_excel(filename,sheet).fillna('-')
        df.loc[len(df)]=list(df.columns)                        
        for col in df.columns:              
            index=list(df.columns).index(col)                   
            letter=get_column_letter(index+1)                   
            ws.column_dimensions[letter].width=9.98 
        ws['A1'].alignment=Alignment(wrap_text=True)
        ws['E1'].alignment=Alignment(wrap_text=True)
        ws['I1'].alignment=Alignment(wrap_text=True)
        ws['G1'].alignment=Alignment(wrap_text=True)
        ws['H1'].alignment=Alignment(wrap_text=True)
        ws['J1'].alignment=Alignment(wrap_text=True)
        ws['K1'].alignment=Alignment(wrap_text=True)        
        ws['N1'].alignment=Alignment(wrap_text=True) 

    wb.save(filename)

四、效果展示

写好的小工具加上彩蛋，制作成exe小程序给小伙伴用，小伙伴表示这下每月需要一天的活，1分钟就能搞定咯。