运维工具 – 异常数据分析实践，将多个表格生成一个表格或将一个表格生成多个工作簿

把多个工作表合并为一个工作表是办公中相当常见的工作任务。如果只是合并两三个工作表，可以手动完成，但如果要合并几十个甚至更多的工作表，手动操作的效率就太低了。本节将利用Python将多个工作簿中的同名工作表快速合并为一个工作表，并保存为一个新的工作簿。

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
from pathlib import Path

import xlwings as xw

src_folder = Path('D:\\月销售统计\')

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

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

for i in file_list:

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

        continue

    workbook = app.books.open(i)

    for j in workbook.sheets:

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

        for index, val in enumerate(data):

            if val[2] == '背包':

                val[2] = '双肩包'

                data[index] = val

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

    workbook.save()

    workbook.close()

app.quit()





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()

将一个工作表拆分为多个工作簿

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
rom pathlib import Path

import xlwings as xw

src_file = Path('\\统计表.xlsx')

des_folder = Path('\\拆分后的产品统计表\')

if not des_folder.exists():

    des_folder.mkdir(parents=True)

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

workbook = app.books.open(src_file)

worksheet = workbook.sheets['统计表']

header = worksheet['A1:H1'].value

data1 = worksheet.range('A2').expand('table').value

data2 = dict()

for i in range(len(data1)):

    product_name = data1[i][1]

    if product_name not in data2:

        data2[product_name] = []

    data2[product_name].append(data1[i])

for k, v in data2.items():

    new_workbook = xw.books.add()

    new_worksheet = new_workbook.sheets.add(k)

    new_worksheet['A1'].value = header

    new_worksheet['A2'].value = v

    new_worksheet.autofit()

    new_workbook.save(des_folder / f'{k}.xlsx')

    new_workbook.close()

app.quit()