Openpyxl

Хотя в среднем для каждодневных задач автоматизация не требуется, бывают случаи, когда она может быть необходима.Создание множества диаграмм, рисунков, таблиц и отчётов может утомить, если вы работаете вручную. Так быть не должно.Можно построить конвейер на Python, с помощью которого Excel и Word легко интегрировать: нужно создать таблицы в Excel, а затем перенести результаты в Word, чтобы практически мгновенно получить отчёт.

Openpyxl

Встречайте Openpyxl возможно, одну из самых универсальных связок [биндингов] с Python, которая сделает взаимодействие с Excel очень простым. Вооружившись этой библиотекой, вы сможете читать и записывать все нынешние и устаревшие форматы Excel, то есть xlsx и xls.

Openpyxl позволяет заполнять строки и столбцы, выполнять формулы, создавать 2D и 3D диаграммы, маркировать оси и заголовки, а также предоставляет множество других возможностей, которые могут пригодиться.

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

Python-docx

Затем идёт Python-docx, этот пакет для Word то же самое, что Openpyxl для Excel. Если вы ещё не изучили его документацию, вам, вероятно, стоит взглянуть на неё. Python-docx без преувеличения один из самых простых и понятных мне наборов инструментов, с которыми я работал с тех пор, как начал работать с самим Python.

Python-docx позволяет автоматизировать создание документов путём автоматической вставки текста, заполнения таблиц и рендеринга изображений в отчёт без каких-либо накладных расходов. Без лишних слов давайте создадим наш собственный автоматизированный конвейер. Запустите Anaconda (или любую другую IDE по вашему выбору) и установите эти пакеты:

pip install openpyxlpip install python-docx

Автоматизация Microsoft Excel

Сначала загрузим уже созданный лист Excel, вот так:

workbook = xl.load_workbook('Book1.xlsx')sheet_1 = workbook['Sheet1']

Теперь переберём все строки в нашей таблице, чтобы вычислить и вставить значения мощности, умножив ток на напряжение:

for row in range(2, sheet_1.max_row + 1):    current = sheet_1.cell(row, 2)    voltage = sheet_1.cell(row, 3)    power = float(current.value) * float(voltage.value)    power_cell = sheet_1.cell(row, 1)    power_cell.value = power

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

values = Reference(sheet_1, min_row = 2, max_row = sheet_1.max_row, min_col = 1, max_col = 1)chart = LineChart()chart.y_axis.title = 'Power'chart.x_axis.title = 'Index'chart.add_data(values)sheet_1.add_chart(chart, 'e2') workbook.save('Book1.xlsx')

Извлечение диаграммы

Теперь, когда мы сгенерировали нашу диаграмму, нам нужно извлечь её как изображение, чтобы мы могли использовать её в нашем отчёте Word. Сначала укажем точное местоположение файла Excel, а также место, где должно быть сохранено изображение диаграммы:

input_file = "C:/Users/.../Book1.xlsx"output_image = "C:/Users/.../chart.png"

Затем откройте электронную таблицу, используя следующий метод:

operation = win32com.client.Dispatch("Excel.Application")operation.Visible = 0operation.DisplayAlerts = 0workbook_2 = operation.Workbooks.Open(input_file)sheet_2 = operation.Sheets(1)

Позднее вы сможете перебирать все объекты диаграммы в электронной таблице (если их несколько) и сохранять их в указанном месте:

for x, chart in enumerate(sheet_2.Shapes):    chart.Copy()    image = ImageGrab.grabclipboard()    image.save(output_image, 'png')    passworkbook_2.Close(True)operation.Quit()

Автоматизация Microsoft Word

Теперь, когда у нас есть сгенерированное изображение диаграммы, мы должны создать шаблон документа, который в принципе является обычным документом Microsoft Word (.docx), сформированным именно так, как мы хотим: отчёт содержит шрифты, размеры шрифтов, структуру и форматирование страниц.

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

Любой сгенерированный контент, включая текст и изображения, может быть объявлен в двойных фигурных скобках {{ variable_name }}. В случае таблиц вам нужно создать таблицу со строкой шаблона со всеми включёнными столбцами, затем нужно добавить одну строку вверху и одну строку ниже со следующей нотацией:

Первая строка:

{%tr for item in variable_name %}

Последняя строка:

{%tr for item in variable_name %}

На рисунке выше имена переменных:

• table_contents для словаря Python, в котором будут храниться наши табличные данные;

• Index для ключей словаря (первый столбец);

• Power, Current и Voltage для значений словаря (второй, третий и четвёртый столбцы).

Затем импортируем наш шаблонный документ в Python и создаём словарь, в котором будут храниться значения нашей таблицы:

template = DocxTemplate('template.docx')table_contents = []for i in range(2, sheet_1.max_row + 1):    table_contents.append({        'Index': i-1,        'Power': sheet_1.cell(i, 1).value,        'Current': sheet_1.cell(i, 2).value,        'Voltage': sheet_1.cell(i, 3).value        })

Далее импортируем ранее созданное в Excel изображение диаграммы и создадим другой словарь для создания экземпляров всех объявленных в документе шаблона переменных-заполнителей:

image = InlineImage(template,'chart.png',Cm(10))context = {    'title': 'Automated Report',    'day': datetime.datetime.now().strftime('%d'),    'month': datetime.datetime.now().strftime('%b'),    'year': datetime.datetime.now().strftime('%Y'),    'table_contents': table_contents,    'image': image    }

И, наконец, визуализируем отчёт с нашей таблицей значений и изображением диаграммы:

template.render(context)template.save('Automated_report.docx')

Результаты

И вот автоматически сгенерированный отчёт Microsoft Word с числами и созданной в Microsoft Excel диаграммой. Мы получили полностью автоматизированный конвейер, его можно использовать, чтобы создать столько таблиц, диаграмм и документов, сколько вам потребуется.

Исходный код

import openpyxl as xlfrom openpyxl.chart import LineChart, Referenceimport win32com.clientimport PILfrom PIL import ImageGrab, Imageimport osimport sysfrom docx.shared import Cmfrom docxtpl import DocxTemplate, InlineImagefrom docx.shared import Cm, Inches, Mm, Emuimport randomimport datetimeimport matplotlib.pyplot as plt######## Generate automated excel workbook ########workbook = xl.load_workbook('Book1.xlsx')sheet_1 = workbook['Sheet1']  for row in range(2, sheet_1.max_row + 1):    current = sheet_1.cell(row, 2)    voltage = sheet_1.cell(row, 3)    power = float(current.value) * float(voltage.value)    power_cell = sheet_1.cell(row, 1)    power_cell.value = power  values = Reference(sheet_1, min_row = 2, max_row = sheet_1.max_row, min_col = 1, max_col = 1)chart = LineChart()chart.y_axis.title = 'Power'chart.x_axis.title = 'Index'chart.add_data(values)sheet_1.add_chart(chart, 'e2')  workbook.save('Book1.xlsx')######## Extract chart image from Excel workbook ########input_file = "C:/Users/.../Book1.xlsx"output_image = "C:/Users/.../chart.png"operation = win32com.client.Dispatch("Excel.Application")operation.Visible = 0operation.DisplayAlerts = 0    workbook_2 = operation.Workbooks.Open(input_file)sheet_2 = operation.Sheets(1)    for x, chart in enumerate(sheet_2.Shapes):    chart.Copy()    image = ImageGrab.grabclipboard()    image.save(output_image, 'png')    passworkbook_2.Close(True)operation.Quit()######## Generating automated word document ########template = DocxTemplate('template.docx')#Generate list of random valuestable_contents = []for i in range(2, sheet_1.max_row + 1):        table_contents.append({        'Index': i-1,        'Power': sheet_1.cell(i, 1).value,        'Current': sheet_1.cell(i, 2).value,        'Voltage': sheet_1.cell(i, 3).value        })#Import saved figureimage = InlineImage(template,'chart.png',Cm(10))#Declare template variablescontext = {    'title': 'Automated Report',    'day': datetime.datetime.now().strftime('%d'),    'month': datetime.datetime.now().strftime('%b'),    'year': datetime.datetime.now().strftime('%Y'),    'table_contents': table_contents,    'image': image    }#Render automated reporttemplate.render(context)template.save('Automated_report.docx')

Вот мой репозиторий на GitHub с шаблоном документа и исходным кодом для этого туториала. А вот ссылка на курс Fullstack-разработчик на Python, который сделает из вас настоящего универсального солдата от кодинга.

Узнайте, как прокачаться и в других специальностях или освоить их с нуля:

• Профессия Data Scientist

• Профессия Data Analyst

• Курс по Data Engineering

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

Так как большинство выписок предоставляются банками в формате старых добрых excel-таблиц, возникло желание автоматизировать работу по поиску в них необходимой информации. Требовалось разработать такой инструмент, который позволяет:

1. Открыть нужную excel-таблицу и провести построковый и полистовой поиск значения по 1-3 ключевым словам, не заморачиваясь с инструментами самого MO Excel по сортировке и фильтру. Построковый поиск предпочтительный, чтобы позволить выводить всю интересующую транзакцию и проводить поиск по всем колонкам.

2. Обнаружив строки с ключевыми словами - перенести их значения в новую таблицу вместе с номером соответствующей строки.

В качестве инструмента для таких операций был выбран пайтоновский модуль openpyxl.

import openpyxl from openpyxl import Workbookbankstatetment = input('Введите название файла для анализа ') #просим указать имя файла в рабочей директории#просим ввести ключевые для поиска словаobj1= input('Введите ключевое слово ') obj2= input('Введите ключевое слово ')obj3= input('Введите ключевое слово ')wb = openpyxl.load_workbook(bankstatetment) # загружаем выбранную выписку/файлresults_string_list = list() #создаем список, куда будет помещаться значение строк с ключевыми словамиresults_stringrow_list = list() #создаем список, куда будет помещаться номер строки с ключевыми словами

Как я понял, посредством openpyxl нельзя перебирать листы таблицы - в каждом случае приходится прописывать, какой лист таблицы должен стать активным и точно указать его наименование. Конечный пользователь, в результате, будет вынужден удостовериться, что в проверяемом файле все листы названы как в программе "Лист 1", "Лист 2", "Лист 3".

Поиск ключевых слов в строках реализован через перебор значений ячеек в строках с помещением обнаруженных значений в два списка. В итоге у нас получаются два списка: results_string_list для значений строк и results_stringrow_list для номеров соответствующих строк. Так как добавление содержания и номера строки идет одновременно, их индексы в каждом списке будут совпадать, т.е. не нужно будет создавать словарь.

sheet = wb['Лист1'] #делаем активным первый лист таблицы.for row in sheet: #перебор строк в листе    string = ''    for cell in row:        string = string + str(cell.value) + ' ' #определить значение в ячейках строки        string_row = str(cell.row)+ ' '#определить номер строки    if obj1 in string:         results_string_list.append (string) #добавляем значение строки в отдельный список        results_stringrow_list.append (string_row) #добавляем номер строки в отдельный список    if obj2 in string:        results_string_list.append (string)        results_stringrow_list.append (string_row)    if obj3 in string:        results_string_list.append (string)        results_stringrow_list.append (string_row)

Аналогичный код идет после установки в качестве активного второго листа страницы и далее. После приступаем к передаче найденных данных в новую таблицу:

wb = Workbook() #создаем новую таблицуws = wb.active #делаем новую таблицу активнойa1 = ws['A1']a1.value = 'Содержание транзакции' #задаем значение подзаголовка колонки "А"b1 = ws['B1']b1.value = 'Номер строки с транзакцией' #задаем значение подзаголовка колонки "B"

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

a2 = ws['A2']a3 = ws['A3']a4 = ws['A4']a5 = ws['A5']...b2 = ws['B2']b3 = ws['B3']b4 = ws['B4']b5 = ws['B5']

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

for i in results_string_list[0:1]:    a2.value = ifor i in results_string_list[1:2]:    a3.value = ifor i in results_string_list[2:3]:    a4.value = ifor i in results_string_list[3:4]:    a5.value = i...for i in results_stringrow_list[0:1]:    b2.value = ifor i in results_stringrow_list[1:2]:    b3.value = ifor i in results_stringrow_list[2:3]:    b4.value = ifor i in results_stringrow_list[3:4]:    b5.value = i...wb.save('результаты анализа.xlsx') 

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

P.S. При применении openpyxl поиск ключевых слов не был чувствителен к регистру, так что эту проблему решать не пришлось.