Python 日常数据分析 – EXCEL实战- 多个工作表合并为一个

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
from pathlib import Path

import xlwings as xw

src_folder = Path('文件夹')

file_list = list(src_folder.glob('*.xlsx'))

app = xw.App(visible=False, add_book=False)

sheet_name = '产品销售统计'

header = None

all_data = []

for i in file_list:

    if i.name.startswith('~$'):

        continue

    workbook = app.books.open(i)

    for j in workbook.sheets:

        if j.name == sheet_name:

            if header is None:

                header = j['A1:I1'].value

            data = j['A2'].expand('table').value

            all_data = all_data + data

    workbook.close()

new_workbook = xw.Book()

new_worksheet = new_workbook.sheets.add(sheet_name)

new_worksheet['A1'].value = header

new_worksheet['A2'].value = all_data

new_worksheet.autofit()

new_workbook.save(src_folder / '.xlsx')

new_workbook.close()

app.quit()