Как я наконец синхронизировал свои внешние данные

В настоящее время мы переносим наши отчеты Excel в наше новое решение. Тем временем я хотел сберечь энергию моего аналитика данных, чтобы он мог сосредоточиться на более крупных и качественных вещах. Я буду говорить о моих трех попытках и о том, что в итоге сработало.

Оглавление

  1. Фон
  2. Первая попытка: сценарии Office и Power Automate
  3. Вторая попытка: Power Refresh
  4. Третья и последняя попытка: PyWin32

Фон

У нас есть около 46 отчетов Excel, которые синхронизируются с несколькими подключениями к базе данных SQL Azure и другими файлами.

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

Эти обновления могут занять от нескольких минут до примерно 20 минут.

Их обновление занимает около 1–2 часов в день, поэтому их автоматизация сэкономит нашему аналитику данных массу времени.

Первая попытка: сценарии Office и Power Automate

Я не собираюсь углубляться в это, потому что на самом деле обновление ваших источников данных в Excel не работает.

Вы будете использовать это как сценарий, который вы можете создать и позже получить к нему доступ через приложение Excel (в разделе «Автоматизация» на верхней панели):

function main(workbook: ExcelScript.Workbook) {
// Refresh all data connections     workbook.refreshAllDataConnections() 
}

В Power Automate вы создадите поток, начиная с планировщика или триггера. Я использовал планировщик, так как мне нужно обновлять эти листы Excel в 9 утра каждое утро.

Когда я не мог понять, почему не обновляются данные в отчетах excel, я искал причину. И я узнал, почему. В разделе Устранение неполадок со сценариями Office, работающими в Power Automate,

Сценарии Office не могут обновлять данные при запуске в Power Automate. Такие методы, как PivotTable.refresh, при вызове в потоке ничего не делают. Power Automate не запускает обновление данных для формул, использующих ссылки на книгу.

Интересно, почему это обновление не работает, поскольку это могло быть значительным использованием Power Automate. После обращения к своим связям в LinkedIn я получил еще два подхода:

Вторая попытка: Power Refresh

Power-Refresh — это фреймворк с контроллером отчетов, созданный IvanBond. Я внимательно следовал инструкциям, но не мог заставить это работать. Последнее обновление для этой папки GitHub было в 2018 году; в Microsoft Excel было внесено несколько значительных обновлений, так что это может быть причиной.

Что не работало? Он смог распознать тестовые файлы Excel, но не смог их запустить. Кнопка запуска застряла на «Выполняется».

Затем я перешел к следующему и последнему предложению.

Третья и последняя попытка: Pywin32

Я выполнил последнее предложение по реализации Pywin32, где мне предложили посмотреть этот пост StackOverflow. Я удобно написал первый ответ ниже.

import win32com.client

# Start an instance of Excel
xlapp = win32com.client.DispatchEx("Excel.Application")

# Open the workbook in said instance of Excel
wb = xlapp.workbooks.open(<path_to_excel_workbook>)

# Optional, e.g. if you want to debug
# xlapp.Visible = True

# Refresh all data connections.
wb.RefreshAll()
wb.Save()

# Quit
xlapp.Quit()

Этот ответ, к сожалению, не работает так, как предполагалось, поскольку не рассматриваются два сценария.

  1. Рабочая книга будет сохранена, но не обязательно будет закрыта, поэтому я добавил закрытую книгу в строке 19 после сохранения.
  2. Несмотря на программирование, xlapp все еще остается открытым, поэтому я добавил del xlapp в строку 25.

Спасибо, что прочитали мои знания, и я надеюсь, что Microsoft в конечном итоге реализует обновление данных как часть своей Power Automate.

References 
1. https://docs.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration#example
2. https://stackoverflow.com/questions/40893870/refresh-excel-external-data-with-python

Дополнительные материалы на PlainEnglish.io. Подпишитесь на нашу бесплатную еженедельную рассылку новостей. Подпишитесь на нас в Twitter и LinkedIn. Присоединяйтесь к нашему сообществу Discord.