Python и Excel: автоматизация рабочих процессов

Подробные примеры продвинутой работы с Excel

1. Создание гистограммы с помощью openpyxl

Добавим диаграмму к данным, демонстрируя возможности openpyxl по визуализации.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.append(['Категория', 'Продажи'])
ws.append(['A', 120])
ws.append(['B', 200])
ws.append(['C', 150])
ws.append(['D', 80])

chart = BarChart()
chart.type = 'col'
chart.title = 'Продажи по категориям'
chart.y_axis.title = 'Сумма'
chart.x_axis.title = 'Категория'

data = Reference(ws, min_col=2, min_row=1, max_row=5)
cats = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

ws.add_chart(chart, 'E5')
wb.save('chart_example.xlsx')

Файл chart_example.xlsx создан с диаграммой на листе.

2. Сводная таблица с pandas

Используем DataFrame для создания сводной таблицы и вывода в новый файл.

import pandas as pd
df = pd.read_excel('sales_data.xlsx')
pivot = pd.pivot_table(df, values='Сумма', index='Менеджер', columns='Квартал', aggfunc='sum', fill_value=0)
pivot.to_excel('pivot_output.xlsx', sheet_name='Сводка')
print(pivot.head())

                 Q1   Q2   Q3   Q4
Менеджер
Иванов        15000 18000 20000 21000
Петров        12000 13000 11000 14000

3. Чтение формата .xls с xlrd и преобразование в .xlsx

Пример конвертации старого файла.

import xlrd
from openpyxl import Workbook

xls_book = xlrd.open_workbook('old_data.xls')
xls_sheet = xls_book.sheet_by_index(0)

wb = Workbook()
ws = wb.active
for row_idx in range(xls_sheet.nrows):
    row_data = xls_sheet.row_values(row_idx)
    ws.append(row_data)

wb.save('converted.xlsx')

Файл converted.xlsx создан и готов к использованию.

4. Выполнение VBA макроса с помощью xlwings

Предположим, в файле есть макрос, который обновляет сводные данные.

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('report_template.xlsm')
# Вызов макроса
wb.macro('UpdateReport')()
# Сохранение результата
wb.save('report_result.xlsx')
app.quit()
print('Макрос выполнен, результат сохранен.')

Макрос выполнен, результат сохранен.

5. Использование win32com для замены текста во всех листах

Автоматизация поиска и замены по всей книге.

import win32com.client as win32

excel = win32.Dispatch('Excel.Application')
excel.Visible = False
wb = excel.Workbooks.Open('C:\data\report.xlsx')
for sheet in wb.Worksheets:
    try:
        sheet.Cells.Replace('Старый текст', 'Новый текст')
    except:
        pass
wb.Save()
wb.Close()
excel.Quit()
print('Замена выполнена.')

Замена выполнена.

6. Оптимизированная запись больших данных с openpyxl

Используем генератор для экономии памяти.

from openpyxl import Workbook
import random

wb = Workbook()
ws = wb.active
ws.append(['ID', 'Value'])
# Генерация 100 000 строк
for i in range(1, 100001):
    row = [i, random.randint(1, 1000)]
    ws.append(row)
wb.save('large_data.xlsx')
print('Большой файл создан.')

Большой файл создан. В файле 100 001 строка.

7. Использование pandas с несколькими листами и пользовательским форматированием

Запись DataFrame в разные листы с разными стилями (через ExcelWriter).

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

df1 = pd.DataFrame({'A': [1,2], 'B': [3,4]})
df2 = pd.DataFrame({'C': [5,6], 'D': [7,8]})

with pd.ExcelWriter('multi_sheet.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name='Первый')
    df2.to_excel(writer, sheet_name='Второй')
    # Применяем стиль к первому листу
    workbook = writer.book
    worksheet = writer.sheets['Первый']
    for cell in worksheet['A1:B2']:
        for c in cell:
            c.font = Font(bold=True)

print('Многостраничный файл с форматированием создан.')

Многостраничный файл с форматированием создан.

8. Чтение только определенных столбцов из Excel с openpyxl

import openpyxl
wb = openpyxl.load_workbook('data.xlsx', read_only=True)
ws = wb.active
# Извлекаем только столбцы A и C
for row in ws.iter_rows(min_col=1, max_col=3, values_only=True):
    # Индексы 0 и 2
    print(row[0], row[2])

... вывод данных ...