Python и Excel: автоматизация обработки данных
Работа с таблицами Excel в Python позволяет автоматизировать обработку данных, упрощая задачи чтения, записи и трансформации. Существует несколько библиотек, каждая из которых подходит для определённых сценариев. В этой статье рассматриваются наиболее распространённые подходы и приводятся примеры их реализации.
Основное решение - библиотека pandas
Как прочитать данные из Excel файла с помощью pandas?
Функция read_excel() загружает содержимое листа (или нескольких листов) в объект DataFrame. Это удобно для последующего анализа и очистки.
import pandas as pd
df = pd.read_excel('data.xlsx', sheet_name='Лист1')
print(df.head())код python в excel (код python в excel)
Пояснение: первый аргумент - путь к файлу, параметр sheet_name указывает лист. По умолчанию читается первый лист. Функция автоматически определяет типы столбцов.
Типичная проблема: отсутствие библиотеки openpyxl или xlrd. Решение - установить их через pip install openpyxl (для .xlsx) или pip install xlrd (для .xls).
Как записать DataFrame обратно в Excel?
Метод to_excel() сохраняет данные в новый файл. Параметр index=False отключает запись индекса.
df.to_excel('output.xlsx', index=False)Python excel openpyxl (работа с excel в python через openpyxl)
Пояснение: можно указать имя листа через sheet_name.
Проблема: потеря форматирования исходного файла. Pandas не сохраняет стили и формулы. Для их сохранения требуется комбинация с openpyxl или xlsxwriter.
Как обработать несколько листов одновременно?
Параметр sheet_name=None возвращает словарь DataFrames, где ключи - имена листов.
dfs = pd.read_excel('multi_sheet.xlsx', sheet_name=None)
for name, df in dfs.items():
print(f'Лист {name}: {df.shape}')Python работа с таблицами excel (работа с таблицами excel в python)
Пояснение: позволяет выполнить итерацию по всем листам.
Как получить доступ к конкретной ячейке и изменить её (openpyxl)?
Библиотека openpyxl предоставляет низкоуровневый доступ к ячейкам и позволяет сохранять форматирование.
from openpyxl import load_workbook
wb = load_workbook('template.xlsx')
ws = wb.active
ws['A1'] = 'Новое значение'
wb.save('template_modified.xlsx')Excel скрипты python (скрипты python для excel)
Пояснение: load_workbook загружает существующий файл, после изменений файл сохраняется. Можно работать с ячейками, стилями, объединёнными ячейками.
Ошибка: при сохранении может быть потеряно форматирование, если используются сторонние надстройки. Решение - использовать keep_vba=False (по умолчанию) и не затрагивать макросы.
Как работать со старыми файлами .xls (xlrd/xlwt)?
Для формата Excel 97-2003 (.xls) применяется библиотека xlrd (чтение) и xlwt (запись).
import xlrd
workbook = xlrd.open_workbook('old_file.xls')
sheet = workbook.sheet_by_index(0)
print(sheet.cell_value(0, 0))Python сохранить в excel (сохранение данных в excel с помощью python)
Пояснение: xlrd поддерживает только .xls, не .xlsx. Для записи используется xlwt с ограниченными возможностями форматирования.
Проблема: xlrd версии 2.0 и выше не читает .xlsx, только .xls. Устанавливайте xlrd == 1.2.0 для поддержки .xlsx, если это необходимо.
Как создать Excel файл с форматированием и диаграммами (xlsxwriter)?
Xlsxwriter позволяет создавать файлы .xlsx с богатым форматированием, условным оформлением и диаграммами, но не читает существующие файлы.
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_example.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
worksheet.write('A1', 'Значение', bold)
workbook.close()Пояснение: все настройки задаются через объекты формата. После завершения файл закрывается методом close().
Ошибка: если файл уже открыт, xlsxwriter не сможет его перезаписать. Нужно закрыть файл в Excel.
Дополнительные примеры и продвинутые приёмы
Ниже приведены расширенные сценарии использования библиотек для работы с Excel в Python.
Чтение нескольких листов с разными параметрами
Иногда листы имеют разные заголовки. Можно задать словарь с именами листов и номерами строк заголовков.
import pandas as pd
sheets = {'Продажи': 0, 'Закупки': 2} # заголовок на строке 0 и 2 соответственно
dfs = {name: pd.read_excel('data.xlsx', sheet_name=name, header=row)
for name, row in sheets.items()}
print(dfs['Продажи'].head())Дата Товар Сумма 0 01.01.2024 A 100 1 02.01.2024 B 200
Пояснение: параметр header указывает индекс строки, содержащей названия столбцов.
Запись DataFrame с пользовательскими стилями (openpyxl)
Можно добавлять цвета, границы и шрифты после записи с помощью pandas, используя движок openpyxl.
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
df = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
with pd.ExcelWriter('styled.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Data', index=False)
workbook = writer.book
worksheet = writer.sheets['Data']
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
for cell in worksheet[1]:
cell.fill = fill
Пояснение: после записи через ExcelWriter доступны объекты openpyxl для изменения стилей.
Создание файла с формулами (xlsxwriter)
Xlsxwriter поддерживает вставку формул Excel, которые будут вычислены при открытии файла.
import xlsxwriter
workbook = xlsxwriter.Workbook('formulas.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, 100)
worksheet.write(0, 1, 200)
worksheet.write_formula(0, 2, '=A1+B1')
workbook.close()После открытия в Excel в ячейке C1 отобразится результат = 300.
Пояснение: формула записывается как строка. Xlsxwriter не вычисляет результат, только сохраняет формулу.
Обработка больших файлов с чанкингом (pandas)
Для файлов, которые не помещаются в оперативную память, можно читать данные порциями.
import pandas as pd
chunk_size = 5000
for chunk in pd.read_excel('large_file.xlsx', chunksize=chunk_size):
# обрабатываем часть данных
print(chunk.shape)
break # для примера только первый чанк(5000, 15)
Пояснение: chunksize возвращает итератор, который загружает только указанное количество строк за раз.
Работа с датами и форматирование (openpyxl)
openpyxl позволяет задавать формат даты для ячеек.
from openpyxl import Workbook
from openpyxl.styles import numbers
from datetime import datetime
wb = Workbook()
ws = wb.active
ws['A1'] = datetime.now()
ws['A1'].number_format = numbers.FORMAT_DATE_DDMMYYYY
wb.save('dates.xlsx')Пояснение: формат задаётся через строку (например 'DD.MM.YYYY') или константу из модуля numbers.