办公自动化 Python 小工具，对Excel文件,按某一列拆分

使用pandas、xlwings、os 库文件，对Excel文件,按某一列拆分成多个Excel文件或sheet，提高工作效率。

# -*- coding: utf-8 -*-
"""
Created on Sun Jan 31 09:32:40 2021

@author: lam
"""

class tables:
    def __init__(self,dirname=None,filename=None,column_name=None,save_file_name=None):
        self._dirname = dirname
        self._filename = filename
        self._column_name = column_name
        self._save_file_name = save_file_name
    
    def mk_dir(self,dirname=None):
        import os
        if dirname:
            self._dirname = dirname       
        if os.path.exists(self._dirname):
            print(self._dirname,'已存在！')
        else:
            os.mkdir(self._dirname)
            print('创建文件夹：',self._dirname)
        return self._dirname
    
    def read_data(self,filename=None):
        import pandas as pd
        if filename:
           self._filename = filename
        return pd.read_excel(self._filename)
    
    
    
    
    def save_same(self):
        df = self.read_data(filename=None)
        import xlwings as xw
        app =xw.App(visible=False,add_book=True)
        wb = xw.Book()
        _dirname = self.mk_dir(dirname=None)
        for col in set(df[self._column_name]):
            sth = wb.sheets.add(col)
            sth.range(1,1).value = df[df[self._column_name]==col].set_index(self._column_name)                                  
        import os
        path = os.path.dirname(__file__)             
        excelfile = path+'\\'+_dirname+'\\'+self._save_file_name+'.xlsx'
        wb.save(excelfile)
        print('已保存：%s'%excelfile)
        wb.close()
        return "运行结束！"
    
    def save_diff(self):
        df = self.read_data(filename=None)
        import xlwings as xw
        app =xw.App(visible=False,add_book=True)
        wb = xw.Book()
        _dirname = self.mk_dir(dirname=None)
        for col in set(df[self._column_name]):
            sth = wb.sheets[0]
            sth.range(1,1).value = df[df[self._column_name]==col].set_index(self._column_name)  
            import os
            path = os.path.dirname(__file__)
            excelfile = path+'\\'+_dirname+'\\'+str(col)+'.xlsx'
            wb.save(excelfile)            
            sth.clear()
            print('已保存：%s'%excelfile)
        wb.close()
        return "运行结束！"

    def save_file(self):
        if self._save_file_name :
            self.save_same()       
        else:
            self.save_diff()
            
    
if __name__=='__main__':

    #拆分后要存放的运行程序下的文件夹名称
    dirname = 'filename'
    #要读取的excel文件
    filename = '20181119-26.xlsx'
    #对某一列中的值进行拆分
    column_name = '反馈人员'
    #可选参数保存同到1个文件不同的sheet中，save_file_name=None保存到不同的文件中。
    save_file_name = '测试表'
    table = tables(dirname,filename,column_name,save_file_name)
    table.save_file()