RU
EN
RU
EN
Работа с файлами Excel в Python: полное руководство
Работа с файлами Excel в Python осуществляется с помощью специализированных библиотек, которые обеспечивают чтение, запись и обработку данных в форматах .xlsx и .xls. Рассмотрим четыре основные библиотеки и их практическое применение.
Основные библиотеки для работы с Excel
openpyxl - универсальная библиотека для современного формата Excel (.xlsx). Поддерживает чтение, запись и редактирование файлов, включая форматирование ячеек, создание диаграмм и работу с формулами.
xlrd - специализированная библиотека для чтения старых файлов Excel (.xls). Оптимальна для работы с файлами Excel 2003 и более ранних версий.
pandas - мощная библиотека для анализа данных, которая упрощает работу с Excel-файлами через структуру данных DataFrame. Поддерживает оба формата и предоставляет богатый функционал для обработки данных.
xlsxwriter - библиотека, ориентированная на создание новых файлов Excel с расширенными возможностями форматирования и визуализации данных.
Установка библиотек
pip install openpyxl xlrd pandas xlsxwriter
Практические примеры работы
Чтение данных с помощью openpyxl
from openpyxl import load_workbook
# Загрузка файла Excel
wb = load_workbook('example.xlsx')
sheet = wb.active
# Итерация по всем строкам
for row in sheet.iter_rows(values_only=True):
print(row)
# Чтение конкретной ячейки
cell_value = sheet['A1'].value
print(f"Значение ячейки A1: {cell_value}")
Чтение данных с помощью xlrd
import xlrd
# Открытие файла Excel
wb = xlrd.open_workbook('example.xls')
sheet = wb.sheet_by_index(0)
# Чтение всех строк
for row in range(sheet.nrows):
print(sheet.row_values(row))
# Чтение конкретной ячейки
cell_value = sheet.cell_value(0, 0)
print(f"Значение ячейки A1: {cell_value}")
Работа с данными через pandas
import pandas as pd
# Загрузка данных из Excel в DataFrame
df = pd.read_excel('example.xlsx')
print(df.head())
# Обработка данных
df_filtered = df[df['column_name'] > 100]
# Сохранение обработанных данных
df_filtered.to_excel('filtered_output.xlsx', index=False)
# Работа с несколькими листами
df_dict = pd.read_excel('example.xlsx', sheet_name=None)
Создание файлов с xlsxwriter
import xlsxwriter
# Создание нового файла Excel
workbook = xlsxwriter.Workbook('formatted_output.xlsx')
worksheet = workbook.add_worksheet()
# Добавление данных с форматированием
bold = workbook.add_format({'bold': True})
worksheet.write('A1', 'Заголовок', bold)
worksheet.write('A2', 'Данные')
# Создание диаграммы
chart = workbook.add_chart({'type': 'line'})
chart.add_series({'values': '=Sheet1!$A$2:$A$10'})
worksheet.insert_chart('C1', chart)
workbook.close()
Расширенные возможности работы с Excel
Создание и редактирование файлов с openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
# Создание нового файла
wb = Workbook()
ws = wb.active
# Добавление данных
ws['A1'] = 'Название'
ws['B1'] = 'Значение'
ws['A2'] = 'Продукт 1'
ws['B2'] = 100
# Форматирование ячеек
ws['A1'].font = Font(bold=True)
ws['A1'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# Сохранение файла
wb.save('formatted_example.xlsx')
Обработка больших файлов с pandas
import pandas as pd
# Чтение файла по частям для больших данных
chunk_size = 1000
chunks = []
for chunk in pd.read_excel('large_file.xlsx', chunksize=chunk_size):
processed_chunk = chunk.groupby('category').sum()
chunks.append(processed_chunk)
# Объединение обработанных частей
result = pd.concat(chunks, ignore_index=True)
result.to_excel('processed_large_file.xlsx', index=False)
Выбор подходящей библиотеки
Используйте openpyxl, если вам нужно:
- Работать с современными файлами Excel (.xlsx)
- Редактировать существующие файлы
- Применять форматирование и стили
- Работать с формулами
Выберите xlrd для:
- Чтения старых файлов Excel (.xls)
- Простого извлечения данных
- Работы с унаследованными системами
Применяйте pandas при необходимости:
- Анализа и обработки данных
- Работы с большими объемами информации
- Интеграции с другими источниками данных
- Статистической обработки
Используйте xlsxwriter для:
- Создания новых файлов с нуля
- Генерации отчетов с диаграммами
- Создания файлов с расширенным форматированием
- Автоматизации создания документов
Обработка ошибок и оптимизация
import pandas as pd
from openpyxl import load_workbook
try:
# Безопасное чтение файла
df = pd.read_excel('data.xlsx')
# Проверка наличия данных
if df.empty:
print("Файл пуст")
else:
print(f"Загружено {len(df)} строк")
except FileNotFoundError:
print("Файл не найден")
except Exception as e:
print(f"Ошибка при чтении файла: {e}")
Вот подробная таблица методов основных библиотек для работы с Excel в Python:
pandas (для работы с Excel)
| Метод | Описание | Пример использования |
|---|---|---|
pd.read_excel() |
Чтение Excel файла | df = pd.read_excel('file.xlsx') |
df.to_excel() |
Запись в Excel файл | df.to_excel('output.xlsx') |
pd.ExcelFile() |
Создание объекта Excel файла | xls = pd.ExcelFile('file.xlsx') |
xls.sheet_names |
Получение списка листов | sheets = xls.sheet_names |
xls.parse() |
Чтение конкретного листа | df = xls.parse('Sheet1') |
openpyxl (основные классы и методы)
Workbook (Рабочая книга)
| Метод | Описание | Пример использования |
|---|---|---|
Workbook() |
Создание новой книги | wb = Workbook() |
load_workbook() |
Загрузка существующей книги | wb = load_workbook('file.xlsx') |
wb.save() |
Сохранение книги | wb.save('file.xlsx') |
wb.create_sheet() |
Создание нового листа | ws = wb.create_sheet('NewSheet') |
wb.remove() |
Удаление листа | wb.remove(wb['Sheet1']) |
wb.sheetnames |
Список имен листов | names = wb.sheetnames |
wb.active |
Активный лист | ws = wb.active |
wb.copy_worksheet() |
Копирование листа | wb.copy_worksheet(ws) |
Worksheet (Рабочий лист)
| Метод | Описание | Пример использования |
|---|---|---|
ws.cell() |
Получение/установка значения ячейки | ws.cell(row=1, column=1, value='Hello') |
ws.append() |
Добавление строки | ws.append(['A', 'B', 'C']) |
ws.insert_rows() |
Вставка строк | ws.insert_rows(1, 3) |
ws.insert_cols() |
Вставка столбцов | ws.insert_cols(1, 2) |
ws.delete_rows() |
Удаление строк | ws.delete_rows(1, 2) |
ws.delete_cols() |
Удаление столбцов | ws.delete_cols(1, 2) |
ws.max_row |
Максимальная строка с данными | max_r = ws.max_row |
ws.max_column |
Максимальный столбец с данными | max_c = ws.max_column |
ws.iter_rows() |
Итерация по строкам | for row in ws.iter_rows(): |
ws.iter_cols() |
Итерация по столбцам | for col in ws.iter_cols(): |
ws.merge_cells() |
Объединение ячеек | ws.merge_cells('A1:B2') |
ws.unmerge_cells() |
Разъединение ячеек | ws.unmerge_cells('A1:B2') |
Cell (Ячейка)
| Свойство/Метод | Описание | Пример использования |
|---|---|---|
cell.value |
Значение ячейки | val = cell.value |
cell.coordinate |
Координаты ячейки | coord = cell.coordinate |
cell.row |
Номер строки | row = cell.row |
cell.column |
Номер столбца | col = cell.column |
cell.column_letter |
Буква столбца | letter = cell.column_letter |
cell.font |
Шрифт ячейки | cell.font = Font(bold=True) |
cell.fill |
Заливка ячейки | cell.fill = PatternFill(fill_type='solid') |
cell.border |
Границы ячейки | cell.border = Border(left=Side()) |
cell.alignment |
Выравнивание | cell.alignment = Alignment(horizontal='center') |
cell.number_format |
Формат числа | cell.number_format = '0.00' |
xlsxwriter (основные методы)
Workbook
| Метод | Описание | Пример использования |
|---|---|---|
xlsxwriter.Workbook() |
Создание новой книги | wb = xlsxwriter.Workbook('file.xlsx') |
wb.add_worksheet() |
Добавление листа | ws = wb.add_worksheet() |
wb.add_format() |
Создание формата | fmt = wb.add_format({'bold': True}) |
wb.close() |
Закрытие и сохранение | wb.close() |
Worksheet
| Метод | Описание | Пример использования |
|---|---|---|
ws.write() |
Запись данных | ws.write(0, 0, 'Hello') |
ws.write_row() |
Запись строки | ws.write_row(0, 0, ['A', 'B', 'C']) |
ws.write_column() |
Запись столбца | ws.write_column(0, 0, [1, 2, 3]) |
ws.write_formula() |
Запись формулы | ws.write_formula(0, 0, '=A1+B1') |
ws.insert_image() |
Вставка изображения | ws.insert_image('A1', 'image.png') |
ws.add_chart() |
Добавление графика | chart = wb.add_chart({'type': 'column'}) |
ws.set_column() |
Настройка столбца | ws.set_column('A:A', 20) |
ws.set_row() |
Настройка строки | ws.set_row(0, 30) |
ws.merge_range() |
Объединение ячеек | ws.merge_range('A1:B2', 'Text') |
ws.freeze_panes() |
Закрепление панелей | ws.freeze_panes(1, 0) |
ws.autofilter() |
Автофильтр | ws.autofilter('A1:D10') |
xlrd (для чтения старых файлов .xls)
Workbook
| Метод | Описание | Пример использования |
|---|---|---|
xlrd.open_workbook() |
Открытие файла | wb = xlrd.open_workbook('file.xls') |
wb.sheet_names() |
Имена листов | names = wb.sheet_names() |
wb.sheet_by_index() |
Лист по индексу | ws = wb.sheet_by_index(0) |
wb.sheet_by_name() |
Лист по имени | ws = wb.sheet_by_name('Sheet1') |
wb.nsheets |
Количество листов | count = wb.nsheets |
Worksheet
| Метод | Описание | Пример использования |
|---|---|---|
ws.cell_value() |
Значение ячейки | val = ws.cell_value(0, 0) |
ws.cell_type() |
Тип ячейки | type = ws.cell_type(0, 0) |
ws.nrows |
Количество строк | rows = ws.nrows |
ws.ncols |
Количество столбцов | cols = ws.ncols |
ws.row_values() |
Значения строки | row = ws.row_values(0) |
ws.col_values() |
Значения столбца | col = ws.col_values(0) |
xlwt (для записи в .xls)
Workbook
| Метод | Описание | Пример использования |
|---|---|---|
xlwt.Workbook() |
Создание книги | wb = xlwt.Workbook() |
wb.add_sheet() |
Добавление листа | ws = wb.add_sheet('Sheet1') |
wb.save() |
Сохранение | wb.save('file.xls') |
Worksheet
| Метод | Описание | Пример использования |
|---|---|---|
ws.write() |
Запись данных | ws.write(0, 0, 'Hello') |
ws.write_merge() |
Запись с объединением | ws.write_merge(0, 1, 0, 1, 'Text') |