Автоматизация Microsoft Excel и Word с помощью Python

Microsoft Excel и Word, без сомнения, являются двумя наиболее широко используемыми программами в корпоративном и некорпоративном мире. Они практически являются синонимами самому термину «работа». Часто не проходит и недели, чтобы мы не использовали комбинацию этих двух программ. Хотя для усредненных задач автоматизация не требуется, бывают случаи, когда автоматизация может быть необходимостью. А именно, когда у вас есть множество диаграмм, рисунков, таблиц для создания отчетов, это может стать чрезвычайно утомительным занятием, если вы выберете ручную работу. На самом деле существует способ создать конвейер в Python, в котором вы можете легко интегрировать их для создания электронных таблиц в Excel, а затем передать результаты в Word для практически мгновенного создания отчета.

Openpyxl

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

Python-docx

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

Без лишних слов давайте создадим наш собственный автоматизированный конвейер. Запустите 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 = 0
operation.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()

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

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

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

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

{%tr for item in variable_name %}

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

{%tr endfor %}

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

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. И с этим у вас есть полностью автоматизированный конвейер, который можно использовать для создания любого количества таблиц, диаграмм и документов, которое вам может когда-либо понадобиться.

Исходный код

Исходный код и шаблон для этого руководства можно найти в следующем репозитории GitHub.

mkhorasani / excel_word_automation

Интегрируйте Microsoft Excel и Word для беспрепятственного создания автоматических отчетов.

Оригинал: Automate Microsoft Excel and Word Using Python