在工作過程中，常遇到不同的sheet存儲著一樣格式的數(shù)據(jù)，比如每個月份都有1個Sheet，當(dāng)對整年進(jìn)行數(shù)據(jù)分析時，需要對同一個表格中的所有sheet數(shù)據(jù)進(jìn)行合并，再進(jìn)行分析。為提高我們的工作效率，可以使用python 的xlwings庫文件對Excel進(jìn)行操作，瞬間完成同一個表格所有sheet的合并，具體實(shí)現(xiàn)的代碼如下：

# -*- coding: utf-8 -*-
"""
Created on Sun Jan 31 15:12:24 2021

@author: lam
"""


def mgSheet(inputfilename,outputfilename):
    import xlwings as xw      
    app = xw.App(visible=False)    
    wb = xw.Book(inputfilename)    
    sths = wb.sheets  
    data = []   
    for sth in sths:
        if sth.range('A1').value:
            print(sth)
            values = sth.range('A2').expand().value
            if isinstance(values[0],str):
                data += [values]
            else:
                data += values
                
    wb_new = xw.Book()
    sth_new = wb_new.sheets[0]
    last_column = sths[0].used_range.last_cell.column    
    sth_new.range('A1').value = sths[0].range('1:'+str(last_column)).value    
    sth_new.range('A2').value = data    
    wb_new.save(outputfilename)
    wb.close()
    wb_new.close()
    import os   
    path = os.path.join(os.path.dirname(__file__) , outputfilename)
   app.quit()
    print(path,':end!')

if __name__=='__main__':
    print('start.....')
    inputfilename =r'.\filename\測試表.xlsx'
    outputfilename =r'合并.xlsx'
    mgSheet(inputfilename,outputfilename)