运维工具 – 异常数据分析实践，批量拆分列数据或批量汇总数据

Excel的分列功能可以将一列数据按照指定的分隔符拆分为多列，本节则要利用Python编程实现类似的功能，对多个工作簿中的列进行拆分。

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

import xlwings as xw

import pandas as pd

src_folder = Path('\\每月统计表\')

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)

    worksheet = workbook.sheets['Sheet1']

    data = worksheet.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value

    new_data = data['产品尺寸（mm）'].str.split('*', expand=True)

    new_data.columns = ['长（mm）', '宽（mm）', '高（mm）']

    for j in range(new_data.shape[1] - 1):

        worksheet['F:F'].insert(shift='right', copy_origin='format_from_left_or_above')

    worksheet['F1'].options(index=False).value = new_data

    worksheet.autofit()

    workbook.save()

    workbook.close()

app.quit()

Excel的分列功能可以将一列数据按照指定的分隔符拆分为多列，本节则要利用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
import xlwings as xw

import pandas as pd

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

workbook = app.books.open('\\产品销售统计表.xlsx')

worksheets = workbook.sheets

table = pd.DataFrame()

for i, j in enumerate(worksheets):

    data = j.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value

    data = data.reindex(columns=['单号', '销售日期', '产品名称', '成本价（元/个）', '销售价（元/个）', '销售数量（个）', '产品成本（元）', '销售收入（元）', '销售利润（元）'])

    table = table.append(data, ignore_index=True)

table = table.groupby('产品名称')

new_workbook = xw.books.add()

for idx, group in table:

    new_worksheet = new_workbook.sheets.add(idx)

    new_worksheet['A1'].options(index=False).value = group

    last_cell = new_worksheet['A1'].expand('table').last_cell

    last_row = last_cell.row

    last_column = last_cell.column

    last_column_letter = chr(64 + last_column)

    sum_cell_name = f'{last_column_letter}{last_row + 1}'

    sum_last_row_name = f'{last_column_letter}{last_row}'

    formula = f'=SUM({last_column_letter}2:{sum_last_row_name})'

    new_worksheet[sum_cell_name].formula = formula

    new_worksheet.autofit()

new_workbook.save('\\产品销售统计表（已汇总）.xlsx')

app.quit()