Автоматизация, Программирование

Так ли вы «любите» отчеты, как «любим» их мы? Создаем отчет с помощью ExcelWriter

Время прочтения: 2 мин.

Наверняка, многих из нас не радует день сдачи отчетности… Собрать данные из разных АС, объединить их так, чтобы результат был понятен не только тебе, но и соответствовал привычному формату, с которым работают ваши коллеги.

Давайте разберемся как же получить не «сырую» выгрузку, а готовый, отформатированный отчет, привычный конечному пользователю. Нам в помощь приходят Python и SQL!

Циклами определяем параметры для подключения к разным БД, а также индивидуальные параметры для каждого запроса.

for user, password, schema in BD:
conn = cx_Oracle.connect(user, password, schema)

parametrs = [parametr_1, ..., parametr_n]
for acc in parametrs:

Сформировав запросы general_query (соответствующие вашим задачам) с указанными параметрами, отправляем их в БД.

cur = conn.cursor()
cur.execute(general_query)
if(result_temp is None):
result_temp = pd.DataFrame(cur.fetchall())
else:
result_temp = result_temp.append(pd.DataFrame(cur.fetchall()))
cur.close()

Полученные результаты запросов сохраняем во временные файлы.

result_temp.to_excel('{}/{}.xlsx'.format(login, acc[:5]), index=None)

И, приводим «сырые» данные к «человеческому» виду путём отсеивания лишней информации, фильтрации и сортировки (например, группируем данные, оставляем только суммы от 1 млн. и сортируем по убыванию каждый блок в таблице).

for col in column:
dfcol = pd.DataFrame()
dfsum = pd.DataFrame()
dfcol['name'] = result_temp[13]
dfcol['sum'] = result_temp[col]
dfsum = dfcol.groupby('name', as_index=False).sum()
dfsum = dfsum[dfsum['sum'] >= 1000000]
dfsum = dfsum.sort_values('sum', ascending=False)

Создаём конечный файл Excel при помощи ExcelWriter.

writer = pd.ExcelWriter('files/' + file_name, engine='xlsxwriter')
workbook = writer.book
worksheet = writer.sheets['result']

Итак, содержимое отчета готово. Далее задаём индивидуальные форматы для разных типов данных и формируем красивую «шапку» методами merge_range и add_format.

worksheet.merge_range(0, sBegin, 0, sEnd, sTitle, merge_format)
workbook.add_format({'bold': 1,
                     'border': 1,
                     'align': 'center',
                     'valign': 'vcenter',
                     'fg_color': cellColor})
fGeneral = workbook.add_format({'num_format': 'General'})
fFin = workbook.add_format({'num_format': '#,##0.00 _?'})
fPerc = workbook.add_format({'num_format': '0%'})
fDate = workbook.add_format({'num_format': 'm/d/yyyy'})

Как вы видите, синтаксис форматов для add_format похож на аналогичный синтаксис из VBA.

Осталось применить созданные форматы к нужным ячейкам и сохранить файл.

worksheet.write(1, num, title, fCellTitle)
worksheet.set_row(row, column, fGeneral)
writer.save()

Готовый отчёт не отличим от созданного руками человека! Таким образом, мы автоматизируем повторяющиеся действия, дополнительно исключая ошибки, вызванные человеческим фактором, и освобождая время для чашечки кофе😊.

Советуем почитать