Таблицы Excel в Python: варианты решений и практические советы

Расширенные примеры работы с Excel в Python

Ниже приведены подробные примеры, выходящие за рамки базовых операций: условное форматирование, работа с диаграммами, объединение ячеек, формулы и вставка изображений.

Пример 1: Условное форматирование с openpyxl

Создадим файл с цветовой шкалой для столбца 'Результат'.

from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.formatting.rule import CellIsRule, FormulaRule

wb = Workbook()
ws = wb.active
ws.title = 'Условное форматирование'
ws.append(['Имя', 'Результат'])

# Данные
data = [('Анна', 85), ('Борис', 45), ('Виктор', 92), ('Галина', 38)]
for row in data:
    ws.append(row)

# Определяем заливку для ячеек, где Результат >= 70
highlight = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')
lowlight = PatternFill(start_color='FFC0CB', end_color='FFC0CB', fill_type='solid')

# Применяем условное форматирование ко всем ячейкам с результатом (диапазон B2:B5)
ws.conditional_formatting.add(
    'B2:B5',
    CellIsRule(operator='greaterThanOrEqual', formula=['70'], fill=highlight)
)
ws.conditional_formatting.add(
    'B2:B5',
    CellIsRule(operator='lessThan', formula=['60'], fill=lowlight)
)

wb.save('conditional_formatting.xlsx')

Файл conditional_formatting.xlsx создан. Ячейки со значением >= 70 – зелёные, < 60 – розовые.

Пример 2: Создание диаграммы с xlsxwriter

Построим столбчатую диаграмму прямо в Excel-файле.

import xlsxwriter

workbook = xlsxwriter.Workbook('chart_example.xlsx')
worksheet = workbook.add_worksheet('График')

# Данные
headings = ['Месяц', 'Продажи']
data = [
    ['Янв', 'Фев', 'Мар', 'Апр', 'Май'],
    [120, 150, 80, 200, 170]
]

worksheet.write_row('A1', headings)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])

# Диаграмма
chart = workbook.add_chart({'type': 'column'})
chart.add_series({
    'name': '=График!$B$1',
    'categories': '=График!$A$2:$A$6',
    'values': '=График!$B$2:$B$6',
})
chart.set_title({'name': 'Продажи по месяцам'})
chart.set_x_axis({'name': 'Месяц'})
chart.set_y_axis({'name': 'Продажи (шт)'})

worksheet.insert_chart('D2', chart)

workbook.close()

Создан файл chart_example.xlsx с диаграммой на листе 'График'.

Пример 3: Объединение ячеек и вставка многострочного текста в openpyxl

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

# Объединение ячеек A1:C1
ws.merge_cells('A1:C1')
cell = ws['A1']
cell.value = 'Отчёт о продажах\nза первый квартал'
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

# Заполняем заголовки столбцов
ws['A2'] = 'Товар'
ws['B2'] = 'Количество'
ws['C2'] = 'Сумма'

ws.append(['Столы', 4, 20000])
ws.append(['Стулья', 15, 7500])

# Устанавливаем высоту первой строки
ws.row_dimensions[1].height = 40

wb.save('merged_example.xlsx')

Файл merged_example.xlsx содержит объединённую ячейку с переносом текста.

Пример 4: Работа с формулами и автозаполнением

Добавим колонку с вычислением налога (20%) с помощью формул Excel.

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Формулы'
ws.append(['Товар', 'Цена', 'Налог 20%'])
ws.append(['Ноутбук', 50000])
ws.append(['Мышь', 1200])
ws.append(['Клавиатура', 2500])

# В столбец C вводим формулу =B*0.2
for row in range(2, 5):
    ws.cell(row=row, column=3).value = f'=B{row}*0.2'

# Автосумма итога
ws.cell(row=5, column=2).value = '=SUM(B2:B4)'
ws.cell(row=5, column=3).value = '=SUM(C2:C4)'

wb.save('formulas.xlsx')

При открытии в Excel формулы автоматически вычисляются: налог = 20% от цены, итоги суммируются.

Пример 5: Чтение данных из защищённого паролем Excel-файла

openpyxl не поддерживает защиту паролем. Для открытия защищённых файлов используйте msoffcrypto-tool (для .xls) или VBA (не в Python). Однако можно попробовать снять защиту с помощью external-утилит. Пример с msoffcrypto-tool (только для .xls!):

import msoffcrypto
import io
import openpyxl

# Подготовка временного файла
with open('protected.xls', 'rb') as f:
    encrypted = msoffcrypto.OfficeFile(f)
    encrypted.load_key('password123')
    decrypted = io.BytesIO()
    encrypted.decrypt(decrypted)

# Теперь можно прочитать openpyxl (только если формат .xls? нет, openpyxl не читает .xls)
# Для .xls используйте xlrd
import xlrd

decrypted.seek(0)
workbook = xlrd.open_workbook(file_contents=decrypted.read())
sheet = workbook.sheet_by_index(0)
print(sheet.cell_value(0, 0))

Выведено значение первой ячейки из расшифрованного файла.

Эти примеры демонстрируют широкие возможности Python для автоматизации Excel, от простого переноса данных до сложных отчётов с визуализацией.