Как получить список листов в книге excel python

xls = pandas.ExcelFile(path)
sheets = xls.sheet_names

xls = xlrd.open_workbook(path)
sheets = xls.sheet_names

3 ответа

Лучший ответ

import xlrd
xls = xlrd.open_workbook(r'<path_to_your_excel_file>', on_demand=True)
print xls.sheet_names() # <- remeber: xlrd sheet_names is a function, not a property

def get_sheet_details(file_path):
    sheets = []
    file_name = os.path.splitext(os.path.split(file_path)[-1])[0]
    # Make a temporary directory with the file name
    directory_to_extract_to = os.path.join(settings.MEDIA_ROOT, file_name)
    os.mkdir(directory_to_extract_to)

    # Extract the xlsx file as it is just a zip file
    zip_ref = zipfile.ZipFile(file_path, 'r')
    zip_ref.extractall(directory_to_extract_to)
    zip_ref.close()

    # Open the workbook.xml which is very light and only has meta data, get sheets from it
    path_to_workbook = os.path.join(directory_to_extract_to, 'xl', 'workbook.xml')
    with open(path_to_workbook, 'r') as f:
        xml = f.read()
        dictionary = xmltodict.parse(xml)
        for sheet in dictionary['workbook']['sheets']['sheet']:
            sheet_details = {
                'id': sheet['sheetId'], # can be @sheetId for some versions
                'name': sheet['name'] # can be @name
            }
            sheets.append(sheet_details)

    # Delete the extracted files directory
    shutil.rmtree(directory_to_extract_to)
    return sheets

data=pd.read_excel('demanddata.xlsx',sheet_name='oil&gas')
print(data)   

Время на прочтение
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.

Для работы с табличными данными часто используют продукт Microsoft Excel. В таблицы Excel помещают как списки покупок, так и отчетности компаний. Благодаря распространенности данного формата разработчики создали инструменты для атоматизации обработки данных.

Pandas является средством работы с табличными данными и умеет работать с файлами формата Excel-таблиц: .xls и .xlsx. И каждый разработчик должен уметь работать с такими форматами наравне с текстовыми файлами и файлами формата json и html.

В этом уроке мы познакомимся с основными методами библиотеки Pandas для работы с табличными данными в формате Microsoft Excel: .xls и .xlsx. Мы научимся их читать и записывать. Также мы разберем работу с файлами, в которых есть несколько листов, а также форматированию данных при записи.

Обработка Excel файлов в Python

Среди форматов файлов Excel наиболее популярными являются:

• .xls — использовался в версиях Microsoft Excel до 2007
• .xlsx — используется во всех версиях после 2007

Для работы с обоими типами в Python есть ряд открытых библиотек:

• xlwt
• openpyxl
• XlsxWriter
• xlrd

В библиотеке Pandas не реализован свой функционал работы с Excel-файлами, но есть единый интерфейс для работы с каждой из указанных выше библиотек.

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

Чтение таблиц из Excel файлов

Чтобы читать файлы в Pandas, используется метод read_excel(). Ему на вход подается путь к читаемому файлу:

import pandas as pd
df_orders = pd.read_excel('data_read/Shop_orders_one_week.xlsx')
print(df_orders.head())
# =>    weekday shop_1  shop_2  shop_3  shop_4
#     0     mon     7       1       7       8
#     1     tue     4       2       4       5
#     2     wed     3       5       2       3
#     3     thu     8       12      8       7
#     4     fri     15      11      13      9
#     5     sat     21      18      17      21
#     6     sun     25      16      25      17

В примере выше прочитан файл продаж четырех магазинов за неделю и размещен в объекте DataFrame. Pandas по умолчанию добавил столбец индексов — последовательность целых чисел от 0 до 6.

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

df_orders = pd.read_excel('data_read/Shop_orders_one_week.xlsx', index_col=0)
print(df_orders.head())
# =>    shop_1  shop_2  shop_3  shop_4
# weekday
# mon       7   1   7   8
# tue       4   2   4   5
# wed       3   5   2   3
# thu       8   12  8   7
# fri       15  11  13  9
# sat       21  18  17  21
# sun       25  16  25  17

Если перед таблицей некоторые строки содержали записи, то попытка прочтения не приведет к ожидаемому результату. Pandas будет стараться положить данные в строках до таблицы в качестве индексов столбцов:

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xlsx')
print(df_orders.head())
# => Orders by shop Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4
# 0            NaN        NaN        NaN        NaN        NaN
# 1        weekday     shop_1     shop_2     shop_3     shop_4
# 2            mon          7          1          7          8
# 3            tue          4          2          4          5
# 4            wed          3          5          2          3

Для корректного прочтения необходимо пропустить некоторое количество строк при прочтении. Для этого нужно использовать параметр skiprows и указать количество пропускаемых строк:

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xlsx', skiprows=2)
print(df_orders.head())
# => weekday  shop_1  shop_2  shop_3  shop_4
#  0     mon       7       1       7       8
#  1     tue       4       2       4       5
#  2     wed       3       5       2       3
#  3     thu       8      12       8       7
#  4     fri      15      11      13       9

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

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xls', skiprows=2, index_col=0)
print(df_orders.head())
# =>       shop_1  shop_2  shop_3  shop_4
# weekday
# mon           7       1       7       8
# tue           4       2       4       5
# wed           3       5       2       3
# thu           8      12       8       7
# fri          15      11      13       9

Запись таблиц в Excel файл

Также в Excel-файл можно записывать результаты работы программы. Эту задачу можно разделить на два типа по сложности используемого синтаксиса:

• Быстрая запись на один лист — записывается одна таблица, которая будет размещена на одном листе файла Excel
• Создание файла с несколькими листами — если результаты работы программы располагаются в нескольких итоговых таблицах, то для формирования единого файла Excel с несколькими листами потребуется применить определенные правила создания

Быстрая запись на один лист

В качестве результатов работы программы используем среднее по магазинам за неделю:

df_orders_mean = pd.DataFrame(df_orders.mean()).T.round(1)
df_orders_mean.index = ['mean']
print(df_orders_mean)
# =>    shop_1  shop_2  shop_3  shop_4
# mean    11.9     9.3    10.9    10.0

Сформируем итоговую таблицу на основе исходной и добавим аналитические результаты:

df_analitic_results = pd.concat([
    df_orders,
    df_orders_mean
])
print(df_analitic_results)
# =>    shop_1  shop_2  shop_3  shop_4
# mon      7.0     1.0     7.0     8.0
# tue      4.0     2.0     4.0     5.0
# wed      3.0     5.0     2.0     3.0
# thu      8.0    12.0     8.0     7.0
# fri     15.0    11.0    13.0     9.0
# sat     21.0    18.0    17.0    21.0
# sun     25.0    16.0    25.0    17.0
# mean    11.9     9.3    10.9    10.0

Чтобы быстро записать данную таблицу, достаточно воспользоваться методом to_excel(). Формат файла .xls или .xlsx необходимо указать в расширении файла. Pandas автоматически определит, какой библиотекой воспользоваться для конкретного формата:

df_analitic_results.to_excel('data_read/Shop_orders_one_week_analitics.xlsx')
df_analitic_results.to_excel('data_read/Shop_orders_one_week_analitics.xls')

Создание файла с несколькими листами

Чтобы задать имя листа, на котором располагается таблица, необходимо указать его в параметре sheet_name. В данном примере получится лист Total:

path_for_analitic_results = 'data_read/Shop_orders_one_week_analitics.xlsx'
df_analitic_results.to_excel(
    path_for_analitic_results,
    sheet_name='Total'
)

Попробуем добавить к сформированному файлу лист итогов только для первого магазина:

df_analitic_results[['shop_1']].to_excel(
    path_for_analitic_results,
    sheet_name='shop_1',
)

Все выполнено без ошибок, но в итоговом файле листа Total нет. Чтобы перезаписать файл и удалить предыдущий, вызовем функцию to_excel().

Для корректной записи или дозаписи нужно использовать следующую конструкцию. В одном файле запишем итоговую таблицу на один лист, а для каждого магазина создадим отдельный лист только с его итогами:

with pd.ExcelWriter(
        path_for_analitic_results,
        engine="xlsxwriter",
        mode='w') as excel_writer:
    # Add total df
    df_analitic_results.to_excel(excel_writer, sheet_name='Total')
    # Add all shop df results
    for shop_name in df_analitic_results.columns.to_list():
        df_analitic_results[[shop_name]].to_excel(excel_writer, sheet_name=shop_name)

В коде выше создается экземпляр класса ExcelWriter на «движке» библиотеки xlsxwriter. Далее мы используем инициализированный экземпляр excel_writer в качестве первого параметра метода to_excel(). Конструкция with...as... позволяет безопасно работать с потоком данных и закрыть файл, даже когда возникают ошибки записи.

Чтение таблиц из Excel файлов с несколькими листами

Чтобы прочитать файл с несколькими листами, не хватит метода read_excel(), поскольку будет прочитан только первый лист из файла:

df_analitic_results_from_file = pd.read_excel(path_for_analitic_results, index_col=0)
print(df_analitic_results_from_file)
# =>    shop_1  shop_2  shop_3  shop_4
# mon      7.0     1.0     7.0       8
# tue      4.0     2.0     4.0       5
# wed      3.0     5.0     2.0       3
# thu      8.0    12.0     8.0       7
# fri     15.0    11.0    13.0       9
# sat     21.0    18.0    17.0      21
# sun     25.0    16.0    25.0      17
# mean    11.9     9.3    10.9      10

При этом можно прочитать конкретный лист, если указать его название в параметре sheet_name:

df_analitic_results_from_file = pd.read_excel(path_for_analitic_results, index_col=0, sheet_name='shop_1')
print(df_analitic_results_from_file)
# =>    shop_1
# mon      7.0
# tue      4.0
# wed      3.0
# thu      8.0
# fri     15.0
# sat     21.0
# sun     25.0
# mean    11.9

Чтобы прочитать несколько листов и не переоткрывать файл, достаточно использовать экземпляр класса ExcelFile и его метод parse(). В последнем указывается имя нужного листа и дополнительные параметры чтения, аналогичные методу read_excel().

excel_reader = pd.ExcelFile(path_for_analitic_results)
df_shop_1 = excel_reader.parse('shop_1', index_col=0)
df_shop_2 = excel_reader.parse('shop_2', index_col=0)
print(df_shop_1)
print(df_shop_2)
# =>    shop_1
# mon      7.0
# tue      4.0
# wed      3.0
# thu      8.0
# fri     15.0
# sat     21.0
# sun     25.0
# mean    11.9
#       shop_2
# mon      1.0
# tue      2.0
# wed      5.0
# thu     12.0
# fri     11.0
# sat     18.0
# sun     16.0
# mean     9.3

Данный подход для чтения файла Excel удобен, чтобы получить список всех листов. Для этого нужно посмотреть на атрибут sheet_names:

print(excel_reader.sheet_names)
# => ['Total', 'shop_1', 'shop_2', 'shop_3', 'shop_4']

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

sheet_to_df_map = {}
for sheet_name in excel_reader.sheet_names:
    sheet_to_df_map[sheet_name] = excel_reader.parse(sheet_name, index_col=0)
print(sheet_to_df_map['shop_1'])
print(sheet_to_df_map['Total'])
# =>    shop_1
# mon      7.0
# tue      4.0
# wed      3.0
# thu      8.0
# fri     15.0
# sat     21.0
# sun     25.0
# mean    11.9
#       shop_1  shop_2  shop_3  shop_4
# mon      7.0     1.0     7.0       8
# tue      4.0     2.0     4.0       5
# wed      3.0     5.0     2.0       3
# thu      8.0    12.0     8.0       7
# fri     15.0    11.0    13.0       9
# sat     21.0    18.0    17.0      21
# sun     25.0    16.0    25.0      17
# mean    11.9     9.3    10.9      10

Форматирование таблиц

За время своего развития Excel накопил довольно мощный функционал, чтобы анализировать и презентовать данные: создание графиков, цветовая подсветка результатов по условию, настройка шрифтов и многое другое.

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

with pd.ExcelWriter(
        path_for_analitic_results,
        engine="xlsxwriter",
        mode='w') as excel_writer:

    # Add total df
    df_analitic_results.to_excel(excel_writer, sheet_name='Total')
    # Formatting total df
    threshold = 11
    workbook = excel_writer.book
    worksheet = excel_writer.sheets['Total']
    format1 = workbook.add_format({'bg_color': '#FFC7CD',
                                'font_color': '#9C0006'})
    format2 = workbook.add_format({'bg_color': '#C6EFCD',
                                'font_color': '#006100'})

    worksheet.conditional_format('B2:E9', {
            'type' : 'cell',
            'criteria' : '>=',
            'value' : threshold,
            'format' : format1}
    )
    worksheet.conditional_format('B2:E9', {
            'type' : 'cell',
            'criteria' : '<',
            'value' : threshold,
            'format' : format2}
    )

В примере выше используются методы движка xlsxwriter. Разбор всех возможностей форматирования данных при записи выходит за рамки данного урока. Можно глубже погрузиться в данную тему через документацию с примерами по следующей ссылке.

Выводы

Основные атрибуты и методы объекта Workbook().

Синтаксис:

from openpyxl import Workbook

wb = Workbook(write_only=False, iso_dates=False)

Параметры:

• write_only=False — режим только для записи,
• iso_dates=False — базовый формат даты.

Возвращаемое значение:

• объект Workbook.

Описание:

Класс Workbook() модуля openpyxl представляет собой контейнер для всех остальных частей XLSX-документа.

Внимание. При создании экземпляра рабочей книги, по умолчанию уже создается один пустой рабочий лист, доступ к которому можно получить при помощи свойства Workbook.active:

>>> from openpyxl import Workbook
# создаем объект книги
>>> wb = Workbook()
# список листов в только что созданной книге
>>> wb.sheetnames
# ['Sheet']

# доступ к созданному по умолчанию листу 
>>> ws = wb.active

Атрибуты и методы объекта Workbook.

• Workbook.active текущий активный лист,
• Workbook.add_named_style() добавляет именованный стиль,
• Workbook.chartsheets список таблиц диаграмм,
• Workbook.close() закрывает файл,
• Workbook.copy_worksheet() копирует существующий лист,
• Workbook.create_chartsheet() создает лист таблицы диаграмм,
• Workbook.create_sheet() создает рабочий лист,
• Workbook.defined_names возвращает список объектов определенных имен,
• Workbook.index() индекс рабочего листа,
• Workbook.mime_type возвращает mime-тип,
• Workbook.move_sheet() перемещает лист,
• Workbook.named_styles список доступных именованных стилей,
• Workbook.remove() удаляет рабочий лист,
• Workbook.save() сохраняет текущую книгу,
• Workbook.sheetnames список имен рабочих,
• Workbook.worksheets список объектов листов Worksheet,

Workbook.active:

Свойство Workbook.active получает текущий активный лист или None.

>>> from openpyxl import Workbook
# создаем объект книги
>>> wb = Workbook()
>>> wb.sheetnames
# ['MySheet']

# доступ к созданному по умолчанию листу 
>>> ws = wb.active
# переименовываем
>>> ws.title = 'MySheet'
# смотрим
>>> wb.sheetnames
# ['MySheet']

Workbook.add_named_style(style):

Метод Workbook.add_named_style() добавляет именованный стиль.

Workbook.chartsheets:

Свойство Workbook.chartsheets возвращает список таблиц диаграмм в этой книге.

Workbook.close():

Метод Workbook.close() закрывает файл рабочей книги, если он открыт. Влияет только на режимы только для чтения и только для записи.

Workbook.copy_worksheet(from_worksheet):

Метод Workbook.copy_worksheet() копирует существующий лист в текущую книгу. Возвращает копию первоначального рабочего листа.

Аргумент from_worksheet — это объект рабочего листа Worksheet, который нужно скопировать.

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

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

Workbook.create_chartsheet(title=None, index=None):

Метод Workbook.create_chartsheet() создает новый лист таблицы диаграмм с именем title и вставляет его в положение положением index. Если index не задан, то вставляет лист последним.

Workbook.create_sheet(title=None, index=None):

Метод Workbook.create_sheet() создает новый рабочий лист с необязательным заголовком листа title и необязательным положением index, в которое будет вставлен лист.

>>> from openpyxl import Workbook
>>> wb = Workbook()
# вставить рабочий лист в конец (по умолчанию)
>>> ws1 = wb.create_sheet("Mysheet")
# вставить рабочий лист в первую позицию
>>> ws2 = wb.create_sheet("Mysheet", 0)
# вставить рабочий лист в предпоследнюю позицию
>>> ws3 = wb.create_sheet("Mysheet", -1)
>>> wb.sheetnames
# ['Mysheet1', 'Sheet', 'Mysheet2', 'Mysheet']

При чтении уже имеющейся электронной таблицы из файла XLSX, доступ к рабочим листам можно получить, обратившись к объекту книги, как к словарю. При этом колючем будет служить имя существующего листа: wb['NewPage']

>>> from openpyxl import load_workbook
# загружаем XLSX-документ
>>> wb = load_workbook('test.xlsx')
# список рабочих листов
>>> name_sheet = wb.sheetnames
>>> name_sheet
# ['Mysheet1', 'Sheet', 'Mysheet2', 'Mysheet']

# получим доступ к рабочему листу 'Mysheet1'
sheet = wb['Mysheet1']
# или получим доступ к листу 'Mysheet2'
# используя его индекс в списке `wb.sheetnames`
sheet1 = wb[name_sheet[2]]

Workbook.defined_names:

Свойство Workbook.defined_names возвращает список объектов определенных имен, доступных в рабочей книге.

>>> from openpyxl import Workbook
>>> from openpyxl.workbook.defined_name import DefinedName
>>> wb = Workbook()
>>> new_range = DefinedName('newrange', attr_text='Sheet!$A$1:$A$5')
>>> wb.defined_names.append(new_range)
>>> wb.defined_names
# <openpyxl.workbook.defined_name.DefinedNameList object>

Workbook.index(worksheet):

Метод Workbook.index() возвращает текущий индекс объекта рабочего листа worksheet.

>>> from openpyxl import Workbook
>>> wb = Workbook()
# вставить рабочий лист в конец (по умолчанию)
>>> ws1 = wb.create_sheet("Mysheet")
# вставить рабочий лист в первую позицию
>>> ws2 = wb.create_sheet("Mysheet", 0)
# вставить рабочий лист в предпоследнюю позицию
>>> ws3 = wb.create_sheet("Mysheet", -1)
>>> wb.index(ws1)
# 3
>>> wb.sheetnames[3]
# 'Mysheet'

Workbook.mime_type:

Свойство Workbook.mime_type возвращает тип mime, который определяется тем, является ли рабочая книга шаблоном или нет, а также содержит ли она макросы.

>>> wb.mime_type
# 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'

Excel требует, чтобы расширение файла совпадало, но модуль openpyxl не обеспечивает этого.

Workbook.move_sheet(sheet, offset=0):

Метод Workbook.move_sheet() перемещает лист с именем sheet на величину offset.

>>> wb.sheetnames
# ['Mysheet', 'Mysheet1', 'Mysheet2']
>>> wb.move_sheet('Mysheet', offset=1)
>>> wb.sheetnames
# ['Mysheet1', 'Mysheet', 'Mysheet2']

Workbook.named_styles
Workbook.style_names:

Свойство Workbook.named_styles возвращает список доступных именованных стилей.

Workbook.remove(worksheet):

Метод Workbook.remove() удаляет объект рабочего листа из этой книги. Аргумент worksheet — объект рабочего листа Worksheet.

# выясним, названия листов присутствуют в книге
>>> 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']

Workbook.save(filename):

Метод Workbook.save() сохраняет текущую книгу под заданным именем файла filename.

Предупреждение. При создании книги с использованием аргумента write_only, установленного в значение True, можно вызвать эту функцию только один раз. Последующие попытки изменить или сохранить файл вызовут исключение openpyxl.shared.exc.WorkbookAlreadySaved.

Workbook.sheetnames:

Свойство Workbook.sheetnames возвращает список имен рабочих листов в этой книге. Имена возвращаются в порядке следования рабочих листов.

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

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

Workbook.worksheets:

Свойство Workbook.worksheets возвращает список объектов листов Worksheet в этой книге.

>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws1 = wb.create_sheet("Mysheet")
>>> wb.worksheets
# [<Worksheet "Sheet">, <Worksheet "Mysheet">]