Как записать данные в excel python openpyxl

Электронные таблицы Excel — это интуитивно понятный и удобный способ манипулирования большими наборами данных без какой-либо предварительной технической подготовки. По этому, это один из форматов, с которым, в какой-то момент времени, вам придется иметь дело. Часто будут стоять задачи по извлечению каких-то данных из базы данных или файла логов в электронную таблицу Excel, или наоборот, преобразовывать электронную таблицу Excel в какую-либо более удобную программную форму, примеров этому масса.

Модуль openpyxl — это библиотека Python для чтения/записи форматов Office Open XML (файлов Excel 2010) с расширениями xlsx/xlsm/xltx/xltm.

Установка модуля openpyxl в виртуальное окружение.

Модуль openpyxl размещен на PyPI, поэтому установка относительно проста.

# создаем виртуальное окружение, если нет
$ python3 -m venv .venv --prompt VirtualEnv
# активируем виртуальное окружение 
$ source .venv/bin/activate
# ставим модуль openpyxl
(VirtualEnv):~$ python3 -m pip install -U openpyxl

Основы работы с файлами Microsoft Excel на Python.

• Создание книги Excel.
  • Новый рабочий лист книги Excel.
  • Копирование рабочего листа книги Excel.
  • Удаление рабочего листа книги Excel.
• Доступ к ячейке электронной таблицы и ее значению.
• Доступ к диапазону ячеек листа электронной таблицы.
• Получение только значений ячеек листа.
• Добавление данных в ячейки списком.
• Сохранение созданной книги в файл Excel.
  • Сохранение данных книги в виде потока.
• Загрузка документа XLSX из файла.

Создание книги Excel.

Чтобы начать работу с модулем openpyxl, нет необходимости создавать файл электронной таблицы в файловой системе. Нужно просто импортировать класс Workbook и создать его экземпляр. Рабочая книга всегда создается как минимум с одним рабочим листом, его можно получить, используя свойство Workbook.active:

>>> from openpyxl import Workbook
# создаем книгу 
>>> wb = Workbook()
# делаем единственный лист активным 
>>> ws = wb.active

Новый рабочий лист книги Excel.

Новые рабочие листы можно создавать, используя метод Workbook.create_sheet():

# вставить рабочий лист в конец (по умолчанию)
>>> ws1 = wb.create_sheet("Mysheet")
# вставить рабочий лист в первую позицию
>>> ws2 = wb.create_sheet("Mysheet", 0)
# вставить рабочий лист в предпоследнюю позицию
>>> ws3 = wb.create_sheet("Mysheet", -1)

Листам автоматически присваивается имя при создании. Они нумеруются последовательно (Sheet, Sheet1, Sheet2, …). Эти имена можно изменить в любое время с помощью свойства Worksheet.title:

Цвет фона вкладки с этим заголовком по умолчанию белый. Можно изменить этот цвет, указав цветовой код RRGGBB для атрибута листа Worksheet.sheet_properties.tabColor:

>>> ws.sheet_properties.tabColor = "1072BA"

Рабочий лист можно получить, используя его имя в качестве ключа экземпляра созданной книги Excel:

Что бы просмотреть имена всех рабочих листов книги, необходимо использовать атрибут Workbook.sheetname. Также можно итерироваться по рабочим листам книги Excel.

>>> wb.sheetnames
# ['Mysheet1', 'NewPage', 'Mysheet2', 'Mysheet']

>>> for sheet in wb:
...     print(sheet.title)
# Mysheet1
# NewPage
# Mysheet2
# Mysheet

Копирование рабочего листа книги Excel.

Для создания копии рабочих листов в одной книге, необходимо воспользоваться методом Workbook.copy_worksheet():

>>> source_page = wb.active
>>> target_page = wb.copy_worksheet(source_page)

Примечание. Копируются только ячейки (значения, стили, гиперссылки и комментарии) и определенные атрибуты рабочего листа (размеры, формат и свойства). Все остальные атрибуты книги/листа не копируются, например, изображения или диаграммы.

Поддерживается возможность копирования рабочих листов между книгами. Нельзя скопировать рабочий лист, если рабочая книга открыта в режиме только для чтения или только для записи.

Удаление рабочего листа книги Excel.

Очевидно, что встает необходимость удалить лист электронной таблицы, который уже существует. Модуль openpyxl дает возможность удалить лист по его имени. Следовательно, сначала необходимо выяснить, какие листы присутствуют в книге, а потом удалить ненужный. За удаление листов книги отвечает метод Workbook.remove().

Смотрим пример:

# выясним, названия листов присутствуют в книге
>>> name_list = wb.sheetnames
>>> name_list
# ['Mysheet1', 'NewPage', 'Mysheet2', 'Mysheet', 'Mysheet1 Copy']

# допустим, что нам не нужны первый и последний
# удаляем первый лист по его имени с проверкой 
# существования такого имени в книге
>>> if 'Mysheet1' in wb.sheetnames:
        # Если лист с именем `Mysheet1` присутствует
        # в списке листов экземпляра книги, то удаляем
...     wb.remove(wb['Mysheet1'])
...
>>> wb.sheetnames
# ['NewPage', 'Mysheet2', 'Mysheet', 'Mysheet1 Copy']

# удаляем последний лист через оператор
#  `del`, имя листа извлечем по индексу 
# полученного списка `name_list`
>>> del wb[name_list[-1]]
>>> wb.sheetnames
# ['NewPage', 'Mysheet2', 'Mysheet']

Доступ к ячейке и ее значению.

После того как выбран рабочий лист, можно начинать изменять содержимое ячеек. К ячейкам можно обращаться непосредственно как к ключам рабочего листа, например ws['A4']. Это вернет ячейку на A4 или создаст ее, если она еще не существует. Значения могут быть присвоены напрямую:

>>> ws['A4'] = 5
>>> ws['A4']
# <Cell 'NewPage'.A4>
>>> ws['A4'].value
# 5
>>> ws['A4'].column
# 1
>>> ws['A4'].row
# 4

Если объект ячейки присвоить переменной, то этой переменной, также можно присваивать значение:

>>> c = ws['A4']
>>> c.value = c.value * 2
>>> c.value
# 10

Существует также метод Worksheet.cell(). Он обеспечивает доступ к ячейкам с непосредственным указанием значений строк и столбцов:

>>> d = ws.cell(row=4, column=2, value=10)
>>> d
# <Cell 'NewPage'.B4>
>>> d.value = 3.14
>>> print(d.value)
# 3.14

Примечание. При создании рабочего листа в памяти, он не содержит ячеек. Ячейки создаются при первом доступе к ним.

Важно! Из-за такого поведения, простой перебор ячеек в цикле, создаст объекты этих ячеек в памяти, даже если не присваивать им значения.

Не запускайте этот пример, поверьте на слово:

# создаст в памяти 100x100=10000 пустых объектов  
# ячеек, просто так израсходовав оперативную память.
>>> for x in range(1,101):
...        for y in range(1,101):
...            ws.cell(row=x, column=y)

Доступ к диапазону ячеек листа электронной таблицы.

Диапазон с ячейками активного листа электронной таблицы можно получить с помощью простых срезов. Эти срезы будут возвращать итераторы объектов ячеек.

>>> cell_range = ws['A1':'C2']
>>> cell_range
# ((<Cell 'NewPage'.A1>, <Cell 'NewPage'.B1>, <Cell 'NewPage'.C1>), 
# (<Cell 'NewPage'.A2>, <Cell 'NewPage'.B2>, <Cell 'NewPage'.C2>))

Аналогично можно получить диапазоны имеющихся строк или столбцов на листе:

# Все доступные ячейки в колонке `C`
>>> colC = ws['C']
# Все доступные ячейки в диапазоне колонок `C:D`
>>> col_range = ws['C:D']
# Все доступные ячейки в строке 10
>>> row10 = ws[10]
# Все доступные ячейки в диапазоне строк `5:10`
>>> row_range = ws[5:10]

Можно также использовать метод Worksheet.iter_rows():

>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
...    for cell in row:
...        print(cell)
# <Cell Sheet1.A1>
# <Cell Sheet1.B1>
# <Cell Sheet1.C1>
# <Cell Sheet1.A2>
# <Cell Sheet1.B2>
# <Cell Sheet1.C2>

Точно так же метод Worksheet.iter_cols() будет возвращать столбцы:

>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
...     for cell in col:
...         print(cell)
# <Cell Sheet1.A1>
# <Cell Sheet1.A2>
# <Cell Sheet1.B1>
# <Cell Sheet1.B2>
# <Cell Sheet1.C1>
# <Cell Sheet1.C2>

Примечание. Из соображений производительности метод Worksheet.iter_cols() недоступен в режиме только для чтения.

Если необходимо перебрать все строки или столбцы файла, то можно использовать свойство Worksheet.rows:

>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
# ((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
# (<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
# (<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
# ...
# (<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
# (<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
# (<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))

или свойство Worksheet.columns:

>>> tuple(ws.columns)
# ((<Cell Sheet.A1>,
# <Cell Sheet.A2>,
# ...
# <Cell Sheet.B8>,
# <Cell Sheet.B9>),
# (<Cell Sheet.C1>,
# <Cell Sheet.C2>,
# ...
# <Cell Sheet.C8>,
# <Cell Sheet.C9>))

Примечание. Из соображений производительности свойство Worksheet.columns недоступно в режиме только для чтения.

Получение только значений ячеек активного листа.

Если просто нужны значения из рабочего листа, то можно использовать свойство активного листа Worksheet.values. Это свойство перебирает все строки на листе, но возвращает только значения ячеек:

for row in ws.values:
   for value in row:
     print(value)

Для возврата только значения ячейки, методы Worksheet.iter_rows() и Worksheet.iter_cols(), представленные выше, могут принимать аргумент values_only:

>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
...   print(row)
# (None, None, None)
# (None, None, None)

Добавление данных в ячейки листа списком.

Модуль openpyxl дает возможность супер просто и удобно добавлять данные в конец листа электронной таблицы. Такое удобство обеспечивается методом объекта листа Worksheet.append(iterable), где аргумент iterable — это любой итерируемый объект (список, кортеж и т.д.). Такое поведение позволяет, без костылей, переносить в электронную таблицу данные из других источников, например CSV файлы, таблицы баз данных, дата-фреймы из Pandas и т.д.

Метод Worksheet.append() добавляет группу значений в последнюю строку, которая не содержит данных.

• Если это список: все значения добавляются по порядку, начиная с первого столбца.
• Если это словарь: значения присваиваются столбцам, обозначенным ключами (цифрами или буквами).

Варианты использования:

• добавление списка: .append([‘ячейка A1’, ‘ячейка B1’, ‘ячейка C1’])
• добавление словаря:
  • вариант 1: .append({‘A’ : ‘ячейка A1’, ‘C’ : ‘ячейка C1’}), в качестве ключей используются буквы столбцов.
  • вариант 2: .append({1 : ‘ячейка A1’, 3 : ‘ячейка C1’}), в качестве ключей используются цифры столбцов.

Пример добавление данных из списка:

# существующие листы рабочей книги
>>> wb.sheetnames
# ['NewPage', 'Mysheet2', 'Mysheet']

# добавим данные в лист с именем `Mysheet2`
>>> ws = wb["Mysheet2"]
# создадим произвольные данные, используя
# вложенный генератор списков
>>> data = [[row*col for col in range(1, 10)] for row in range(1, 31)]
>>> data
# [
#     [1, 2, 3, 4, 5, 6, 7, 8, 9], 
#     [2, 4, 6, 8, 10, 12, 14, 16, 18], 
# ...
# ...
#     [30, 60, 90, 120, 150, 180, 210, 240, 270]
# ]

# добавляем данные в выбранный лист
>>> for row in data:
...     ws.append(row)
...

Вот и все, данные добавлены… Просто? Не просто, а супер просто!

Сохранение созданной книги в файл Excel.

Самый простой и безопасный способ сохранить книгу, это использовать метод Workbook.save() объекта Workbook:

>>> wb = Workbook()
>>> wb.save('test.xlsx')

Внимание. Эта операция перезапишет существующий файл без предупреждения!!!

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

Примечание. Расширение имени файла не обязательно должно быть xlsx или xlsm, хотя могут возникнуть проблемы с его открытием непосредственно в другом приложении. Поскольку файлы OOXML в основном представляют собой ZIP-файлы, их также можете открыть с помощью своего любимого менеджера ZIP-архивов.

Сохранение данных книги в виде потока.

Если необходимо сохранить файл в поток, например, при использовании веб-приложения, такого как Flask или Django, то можно просто предоставить tempfile.NamedTemporaryFile():

from tempfile import NamedTemporaryFile
from openpyxl import Workbook

wb = Workbook()

with NamedTemporaryFile() as tmp:
    wb.save(tmp.name)
    tmp.seek(0)
    stream = tmp.read()

Можно указать атрибут template=True, чтобы сохранить книгу как шаблон:

>>> from openpyxl import load_workbook
>>> wb = load_workbook('test.xlsx')
>>> wb.template = True
>>> wb.save('test_template.xltx')

Примечание. Атрибут wb.template по умолчанию имеет значение False, это означает — сохранить как документ.

Внимание. Следующее не удастся:

>>> from openpyxl import load_workbook
>>> wb = load_workbook('test.xlsx')
# Необходимо сохранить с расширением *.xlsx
>>> wb.save('new_test.xlsm') # MS Excel не может открыть документ

# Нужно указать атрибут `keep_vba=True`
>>> wb = load_workbook('test.xlsm')
>>> wb.save('new_test.xlsm')

>>> wb = load_workbook('test.xltm', keep_vba=True)
# Если нужен шаблон документа, то необходимо указать расширение *.xltm.
>>> wb.save('new_test.xlsm') # MS Excel не может открыть документ

Загрузка документа XLSX из файла.

Чтобы открыть существующую книгу Excel необходимо использовать функцию openpyxl.load_workbook():

>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print(wb2.sheetnames)
# ['Mysheet1', 'NewPage', 'Mysheet2', 'Mysheet']

Есть несколько флагов, которые можно использовать в функции openpyxl.load_workbook().

• data_only: определяет, будут ли содержать ячейки с формулами — формулу (по умолчанию) или только значение, сохраненное/посчитанное при последнем чтении листа Excel.
• keep_vba определяет, сохраняются ли какие-либо элементы Visual Basic (по умолчанию). Если они сохранены, то они не могут изменяться/редактироваться.

Больше …

Время на прочтение
10 мин

Количество просмотров 290K

Первая часть статьи была опубликована тут.

Как читать и редактировать Excel файлы при помощи openpyxl

ПЕРЕВОД
Оригинал статьи — www.datacamp.com/community/tutorials/python-excel-tutorial
Автор — Karlijn Willems

Эта библиотека пригодится, если вы хотите читать и редактировать файлы .xlsx, xlsm, xltx и xltm.

Установите openpyxl using pip. Общие рекомендации по установке этой библиотеки — сделать это в виртуальной среде Python без системных библиотек. Вы можете использовать виртуальную среду для создания изолированных сред Python: она создает папку, содержащую все необходимые файлы, для использования библиотек, которые потребуются для Python.

Перейдите в директорию, в которой находится ваш проект, и повторно активируйте виртуальную среду venv. Затем перейдите к установке openpyxl с помощью pip, чтобы убедиться, что вы можете читать и записывать с ним файлы:

# Activate virtualenv
$ source activate venv

# Install `openpyxl` in `venv`
$ pip install openpyxl

Теперь, когда вы установили openpyxl, вы можете начать загрузку данных. Но что именно это за данные? Например, в книге с данными, которые вы пытаетесь получить на Python, есть следующие листы:

Функция load_workbook () принимает имя файла в качестве аргумента и возвращает объект рабочей книги, который представляет файл. Это можно проверить запуском type (wb). Не забудьте убедиться, что вы находитесь в правильной директории, где расположена электронная таблица. В противном случае вы получите сообщение об ошибке при импорте.

# Import `load_workbook` module from `openpyxl`
from openpyxl import load_workbook

# Load in the workbook
wb = load_workbook('./test.xlsx')

# Get sheet names
print(wb.get_sheet_names())

Помните, вы можете изменить рабочий каталог с помощью os.chdir (). Фрагмент кода выше возвращает имена листов книги, загруженной в Python. Вы можете использовать эту информацию для получения отдельных листов книги. Также вы можете проверить, какой лист активен в настоящий момент с помощью wb.active. В приведенном ниже коде, вы также можете использовать его для загрузки данных на другом листе книги:

# Get a sheet by name 
sheet = wb.get_sheet_by_name('Sheet3')

# Print the sheet title 
sheet.title

# Get currently active sheet
anotherSheet = wb.active

# Check `anotherSheet` 
anotherSheet

На первый взгляд, с этими объектами Worksheet мало что можно сделать. Однако, можно извлекать значения из определенных ячеек на листе книги, используя квадратные скобки [], к которым нужно передавать точную ячейку, из которой вы хотите получить значение.

Обратите внимание, это похоже на выбор, получение и индексирование массивов NumPy и Pandas DataFrames, но это еще не все, что нужно сделать, чтобы получить значение. Нужно еще добавить значение атрибута:

# Retrieve the value of a certain cell
sheet['A1'].value

# Select element 'B2' of your sheet 
c = sheet['B2']

# Retrieve the row number of your element
c.row

# Retrieve the column letter of your element
c.column

# Retrieve the coordinates of the cell 
c.coordinate

Помимо value, есть и другие атрибуты, которые можно использовать для проверки ячейки, а именно row, column и coordinate:

Атрибут row вернет 2;
Добавление атрибута column к “С” даст вам «B»;
coordinate вернет «B2».

Вы также можете получить значения ячеек с помощью функции cell (). Передайте аргументы row и column, добавьте значения к этим аргументам, которые соответствуют значениям ячейки, которые вы хотите получить, и, конечно же, не забудьте добавить атрибут value:

# Retrieve cell value 
sheet.cell(row=1, column=2).value

# Print out values in column 2 
for i in range(1, 4):
     print(i, sheet.cell(row=i, column=2).value)

Обратите внимание: если вы не укажете значение атрибута value, вы получите <Cell Sheet3.B1>, который ничего не говорит о значении, которое содержится в этой конкретной ячейке.

Вы используете цикл с помощью функции range (), чтобы помочь вам вывести значения строк, которые имеют значения в столбце 2. Если эти конкретные ячейки пусты, вы получите None.
Более того, существуют специальные функции, которые вы можете вызвать, чтобы получить другие значения, например get_column_letter () и column_index_from_string.

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

# Import relevant modules from `openpyxl.utils`
from openpyxl.utils import get_column_letter, column_index_from_string

# Return 'A'
get_column_letter(1)

# Return '1'
column_index_from_string('A')

Вы уже получили значения для строк, которые имеют значения в определенном столбце, но что нужно сделать, если нужно вывести строки файла, не сосредотачиваясь только на одном столбце?

Конечно, использовать другой цикл.

Например, вы хотите сосредоточиться на области, находящейся между «A1» и «C3», где первый указывает левый верхний угол, а второй — правый нижний угол области, на которой вы хотите сфокусироваться. Эта область будет так называемой cellObj, которую вы видите в первой строке кода ниже. Затем вы указываете, что для каждой ячейки, которая находится в этой области, вы хотите вывести координату и значение, которое содержится в этой ячейке. После окончания каждой строки вы хотите выводить сообщение-сигнал о том, что строка этой области cellObj была выведена.

# Print row per row
for cellObj in sheet['A1':'C3']:
      for cell in cellObj:
              print(cells.coordinate, cells.value)
      print('--- END ---')

Обратите внимание, что выбор области очень похож на выбор, получение и индексирование списка и элементы NumPy, где вы также используете квадратные скобки и двоеточие чтобы указать область, из которой вы хотите получить значения. Кроме того, вышеприведенный цикл также хорошо использует атрибуты ячейки!

Чтобы визуализировать описанное выше, возможно, вы захотите проверить результат, который вернет вам завершенный цикл:

('A1', u'M')
('B1', u'N')
('C1', u'O')
--- END ---
('A2', 10L)
('B2', 11L)
('C2', 12L)
--- END ---
('A3', 14L)
('B3', 15L)
('C3', 16L)
--- END ---

Наконец, есть некоторые атрибуты, которые вы можете использовать для проверки результата импорта, а именно max_row и max_column. Эти атрибуты, конечно, являются общими способами обеспечения правильной загрузки данных, но тем не менее в данном случае они могут и будут полезны.

# Retrieve the maximum amount of rows 
sheet.max_row

# Retrieve the maximum amount of columns
sheet.max_column

Это все очень классно, но мы почти слышим, что вы сейчас думаете, что это ужасно трудный способ работать с файлами, особенно если нужно еще и управлять данными.
Должно быть что-то проще, не так ли? Всё так!

Openpyxl имеет поддержку Pandas DataFrames. И можно использовать функцию DataFrame () из пакета Pandas, чтобы поместить значения листа в DataFrame:

# Import `pandas` 
import pandas as pd

# Convert Sheet to DataFrame
df = pd.DataFrame(sheet.values)
Если вы хотите указать заголовки и индексы, вам нужно добавить немного больше кода:
# Put the sheet values in `data`
data = sheet.values

# Indicate the columns in the sheet values
cols = next(data)[1:]

# Convert your data to a list
data = list(data)

# Read in the data at index 0 for the indices
idx = [r[0] for r in data]

# Slice the data at index 1 
data = (islice(r, 1, None) for r in data)

# Make your DataFrame
df = pd.DataFrame(data, index=idx, columns=cols)

Затем вы можете начать управлять данными при помощи всех функций, которые есть в Pandas. Но помните, что вы находитесь в виртуальной среде, поэтому, если библиотека еще не подключена, вам нужно будет установить ее снова через pip.

Чтобы записать Pandas DataFrames обратно в файл Excel, можно использовать функцию dataframe_to_rows () из модуля utils:

# Import `dataframe_to_rows`
from openpyxl.utils.dataframe import dataframe_to_rows

# Initialize a workbook 
wb = Workbook()

# Get the worksheet in the active workbook
ws = wb.active

# Append the rows of the DataFrame to your worksheet
for r in dataframe_to_rows(df, index=True, header=True):
    ws.append(r)

Но это определенно не все! Библиотека openpyxl предлагает вам высокую гибкость в отношении того, как вы записываете свои данные в файлы Excel, изменяете стили ячеек или используете режим только для записи. Это делает ее одной из тех библиотек, которую вам точно необходимо знать, если вы часто работаете с электронными таблицами.

И не забудьте деактивировать виртуальную среду, когда закончите работу с данными!

Теперь давайте рассмотрим некоторые другие библиотеки, которые вы можете использовать для получения данных в электронной таблице на Python.

Готовы узнать больше?

Чтение и форматирование Excel файлов xlrd
Эта библиотека идеальна, если вы хотите читать данные и форматировать данные в файлах с расширением .xls или .xlsx.

# Import `xlrd`
import xlrd

# Open a workbook 
workbook = xlrd.open_workbook('example.xls')

# Loads only current sheets to memory
workbook = xlrd.open_workbook('example.xls', on_demand = True)

Если вы не хотите рассматривать всю книгу, можно использовать такие функции, как sheet_by_name () или sheet_by_index (), чтобы извлекать листы, которые необходимо использовать в анализе.

# Load a specific sheet by name
worksheet = workbook.sheet_by_name('Sheet1')

# Load a specific sheet by index 
worksheet = workbook.sheet_by_index(0)

# Retrieve the value from cell at indices (0,0) 
sheet.cell(0, 0).value

Наконец, можно получить значения по определенным координатам, обозначенным индексами.
О том, как xlwt и xlutils, соотносятся с xlrd расскажем дальше.

Запись данных в Excel файл при помощи xlrd

Если нужно создать электронные таблицы, в которых есть данные, кроме библиотеки XlsxWriter можно использовать библиотеки xlwt. Xlwt идеально подходит для записи и форматирования данных в файлы с расширением .xls.

Когда вы вручную хотите записать в файл, это будет выглядеть так:

# Import `xlwt` 
import xlwt

# Initialize a workbook 
book = xlwt.Workbook(encoding="utf-8")

# Add a sheet to the workbook 
sheet1 = book.add_sheet("Python Sheet 1") 

# Write to the sheet of the workbook 
sheet1.write(0, 0, "This is the First Cell of the First Sheet") 

# Save the workbook 
book.save("spreadsheet.xls")

Если нужно записать данные в файл, то для минимизации ручного труда можно прибегнуть к циклу for. Это позволит немного автоматизировать процесс. Делаем скрипт, в котором создается книга, в которую добавляется лист. Далее указываем список со столбцами и со значениями, которые будут перенесены на рабочий лист.

Цикл for будет следить за тем, чтобы все значения попадали в файл: задаем, что с каждым элементом в диапазоне от 0 до 4 (5 не включено) мы собираемся производить действия. Будем заполнять значения строка за строкой. Для этого указываем row элемент, который будет “прыгать” в каждом цикле. А далее у нас следующий for цикл, который пройдется по столбцам листа. Задаем условие, что для каждой строки на листе смотрим на столбец и заполняем значение для каждого столбца в строке. Когда заполнили все столбцы строки значениями, переходим к следующей строке, пока не заполним все имеющиеся строки.

# Initialize a workbook
book = xlwt.Workbook()

# Add a sheet to the workbook
sheet1 = book.add_sheet("Sheet1")

# The data
cols = ["A", "B", "C", "D", "E"]
txt = [0,1,2,3,4]

# Loop over the rows and columns and fill in the values
for num in range(5):
      row = sheet1.row(num)
      for index, col in enumerate(cols):
          value = txt[index] + num
          row.write(index, value)

# Save the result
book.save("test.xls")

В качестве примера скриншот результирующего файла:

Теперь, когда вы видели, как xlrd и xlwt взаимодействуют вместе, пришло время посмотреть на библиотеку, которая тесно связана с этими двумя: xlutils.

Коллекция утилит xlutils

Эта библиотека в основном представляет собой набор утилит, для которых требуются как xlrd, так и xlwt. Включает в себя возможность копировать и изменять/фильтровать существующие файлы. Вообще говоря, оба этих случая подпадают теперь под openpyxl.

Использование pyexcel для чтения файлов .xls или .xlsx

Еще одна библиотека, которую можно использовать для чтения данных таблиц в Python — pyexcel. Это Python Wrapper, который предоставляет один API для чтения, обработки и записи данных в файлах .csv, .ods, .xls, .xlsx и .xlsm.

Чтобы получить данные в массиве, можно использовать функцию get_array (), которая содержится в пакете pyexcel:

# Import `pyexcel`
import pyexcel

# Get an array from the data
my_array = pyexcel.get_array(file_name="test.xls")
 
Также можно получить данные в упорядоченном словаре списков, используя функцию get_dict ():
# Import `OrderedDict` module 
from pyexcel._compact import OrderedDict

# Get your data in an ordered dictionary of lists
my_dict = pyexcel.get_dict(file_name="test.xls", name_columns_by_row=0)

# Get your data in a dictionary of 2D arrays
book_dict = pyexcel.get_book_dict(file_name="test.xls")

Однако, если вы хотите вернуть в словарь двумерные массивы или, иными словами, получить все листы книги в одном словаре, стоит использовать функцию get_book_dict ().

Имейте в виду, что обе упомянутые структуры данных, массивы и словари вашей электронной таблицы, позволяют создавать DataFrames ваших данных с помощью pd.DataFrame (). Это упростит обработку ваших данных!

Наконец, вы можете просто получить записи с pyexcel благодаря функции get_records (). Просто передайте аргумент file_name функции и обратно получите список словарей:

# Retrieve the records of the file
records = pyexcel.get_records(file_name="test.xls")

Записи файлов при помощи pyexcel

Так же, как загрузить данные в массивы с помощью этого пакета, можно также легко экспортировать массивы обратно в электронную таблицу. Для этого используется функция save_as () с передачей массива и имени целевого файла в аргумент dest_file_name:

# Get the data
data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]

# Save the array to a file
pyexcel.save_as(array=data, dest_file_name="array_data.xls")

Обратите внимание: если указать разделитель, то можно добавить аргумент dest_delimiter и передать символ, который хотите использовать, в качестве разделителя между “”.

Однако, если у вас есть словарь, нужно будет использовать функцию save_book_as (). Передайте двумерный словарь в bookdict и укажите имя файла, и все ОК:

# The data
2d_array_dictionary = {'Sheet 1': [
                                   ['ID', 'AGE', 'SCORE']
                                   [1, 22, 5],
                                   [2, 15, 6],
                                   [3, 28, 9]
                                  ],
                       'Sheet 2': [
                                    ['X', 'Y', 'Z'],
                                    [1, 2, 3],
                                    [4, 5, 6]
                                    [7, 8, 9]
                                  ],
                       'Sheet 3': [
                                    ['M', 'N', 'O', 'P'],
                                    [10, 11, 12, 13],
                                    [14, 15, 16, 17]
                                    [18, 19, 20, 21]
                                   ]}

# Save the data to a file                        
pyexcel.save_book_as(bookdict=2d_array_dictionary, dest_file_name="2d_array_data.xls")

Помните, что когда используете код, который напечатан в фрагменте кода выше, порядок данных в словаре не будет сохранен!

Чтение и запись .csv файлов

Если вы все еще ищете библиотеки, которые позволяют загружать и записывать данные в CSV-файлы, кроме Pandas, рекомендуем библиотеку csv:

# import `csv`
import csv

# Read in csv file 
for row in csv.reader(open('data.csv'), delimiter=','):
      print(row)
      
# Write csv file
data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
outfile = open('data.csv', 'w')
writer = csv.writer(outfile, delimiter=';', quotechar='"')
writer.writerows(data)
outfile.close()

Обратите внимание, что NumPy имеет функцию genfromtxt (), которая позволяет загружать данные, содержащиеся в CSV-файлах в массивах, которые затем можно помещать в DataFrames.

Финальная проверка данных

Когда данные подготовлены, не забудьте последний шаг: проверьте правильность загрузки данных. Если вы поместили свои данные в DataFrame, вы можете легко и быстро проверить, был ли импорт успешным, выполнив следующие команды:

# Check the first entries of the DataFrame
df1.head()

# Check the last entries of the DataFrame
df1.tail()

Note: Используйте DataCamp Pandas Cheat Sheet, когда вы планируете загружать файлы в виде Pandas DataFrames.

Если данные в массиве, вы можете проверить его, используя следующие атрибуты массива: shape, ndim, dtype и т.д.:

# Inspect the shape 
data.shape

# Inspect the number of dimensions
data.ndim

# Inspect the data type
data.dtype

Что дальше?

Поздравляем, теперь вы знаете, как читать файлы Excel в Python Но импорт данных — это только начало рабочего процесса в области данных. Когда у вас есть данные из электронных таблиц в вашей среде, вы можете сосредоточиться на том, что действительно важно: на анализе данных.

Если вы хотите глубже погрузиться в тему — знакомьтесь с PyXll, которая позволяет записывать функции в Python и вызывать их в Excel.