Pedro Tavares

Обнаруженное недавно семейство вредоносного ПО под названием Epic Manchego использует хитрый трюк для создания вредоносных файлов MS Excel с минимальной степенью обнаружения и повышенной вероятностью обхода систем безопасности. Изучая способы обхода систем безопасности, используемые злоумышленниками, можно понять, какие первоочередные меры следует предпринять для защиты систем от атак подобного рода.

Описание угрозы

Семейство вредоносного ПО "работает" с июня 2020 года и атакует организации из разных стран с применением фишинговых сообщений электронной почты, содержащих изменённый файл Excel. Чтобы фишинговые сообщения не попадали в папки со спамом и против них не срабатывали механизмы отсечения нежелательной почты, злоумышленники отправляют свои письма с официальных учётных записей организаций. Учётные данные таких организаций, как правило, попадают в руки злоумышленников в результате взлома. Злоумышленники с помощью сервиса проверки аккаунтов на утечки "Have I Been Pwned?" проверяют, были ли скомпрометированы учётные записи электронной почты, или просто взламывают такие записи до того, как приступить к вредоносной рассылке.

Согласно данным NVISO, "через VirusTotal было пропущено около 200 вредоносных документов, и был составлен список из 27 стран, ранжированных по количеству отправленных документов. В списке не делалось различие, каким способом загружались такие файлы (возможно, через VPN)".

В ходе исследования выяснилось, что наибольшему риску рассылки вредоносных файлов подвергаются такие страны, как Соединённые Штаты Америки, Чешская Республика, Франция, Германия и Китай.

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

Как работает Epic Manchego

В некоторых рассылаемых документах Office содержатся нарисованные фигуры, например прямоугольники, как это показано на рисунке 4.

Вредоносные документы Microsoft Office создаются не через Microsoft Office Excel, а с использованием .NET библиотеки EPPlus. Поскольку такие документы не являются стандартными документами Excel, они могут маскироваться и обходить защитные механизмы.

Документ на рисунке 4 содержит объект drawing1.xml (скруглённый прямоугольник) с именем name="VBASampleRect и создан с использованием исходного кода EPPLUS Wiki (справа), как это показано ниже.

Если открыть окно макросов документа, в нём не будет ни одного макроса.

Тем не менее вредоносный код существует и к тому же защищён паролем. Интересно отметить, что этот код VBA вообще не зашифрован, а представлен открытым текстом.

При открытии документа с VBA-проектом, защищённым паролем, макросы VBA будут запускаться без пароля. Пароль необходим только для просмотра проекта VBA внутри интегрированной среды разработки (IDE) VBA.

Если внести изменения в строку DPB или дешифровать пароль, можно увидеть, что при запуске вредоносного файла Office на компьютере жертвы запускается и выполняется полезная нагрузка PowerShell.

На приведённом ниже скриншоте продемонстрирован запуск полезной нагрузки PowerShell во время заражения.

Согласно результатам исследования NVISO Labs, чтобы загрузить полезную нагрузку в коде VBA, используются либо объекты PowerShell, либо объекты ActiveX, в зависимости от разновидности исходного вредоносного ПО.

Анализ завершающего этапа работы вредоносного ПО

Через вредоносный код VBA на втором этапе с различных интернет-сайтов загружается полезная нагрузка. Каждый исполняемый файл, создаваемый соответствующим вредоносным документом и запускаемый на втором этапе, выступает для конечной полезной нагрузки как носитель вируса (дроппер). После этого на втором этапе также загружается вредоносный файл DLL. Этот компонент DLL формирует дополнительные параметры и полезную нагрузку для третьего этапа, после чего запускает на выполнение конечную полезную нагрузку, которая, как правило, крадёт информацию.

Как отмечают исследователи из NVISO Labs, "несмотря на то что описанная выше схема обфускации данных применяется многими вредоносными программами, мы видим, как она усложняется, поэтому существует вероятность применения более изощрённых методик".

Кроме того, "общим фактором второго этапа заражения является использование методов стеганографии (то есть тайной передачи информации путём сокрытия самого факта передачи) с целью маскирования злонамеренного умысла".

После этого выполняется последний шаг запускаются несколько классических троянских программ, и устройства жертвы полностью компрометируются.

Чаще всего (более чем в 50% случаев) на компьютер жертвы устанавливается вредоносная программа AZORult, похищающая личные данные пользователей, программы для кражи информации называются инфостилерами. В качестве других полезных нагрузок могут использоваться трояны AgentTesla, Formbook, Matiex и njRat, причем Azorult и njRAT имеют высокий уровень повторного использования.

Обнаружение и действия

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

Обнаружение и блокирование новых способов заражения посредством создания вредоносных документов (maldoc), один из которых описывается в настоящей статье, позволит организациям оперативно реагировать на инциденты. Для предотвращения атак подобного рода рекомендуется принимать следующие меры:

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

• Регулярно обновлять программное обеспечение, приложения и системы до последних версий.

• Использовать решения защиты конечных точек (Endpoint Protection) и обновлённое антивирусное ПО для предотвращения заражения вредоносными программами.

• Использовать системы управления уязвимостями и мониторинга для выявления потенциальных неустранённых уязвимостей и инцидентов в режиме реального времени.

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

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

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

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

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

• Курс по Data Engineering

Источники:

• Epic Manchego, NVISO Labs.

• Использование библиотеки .NET для обхода антивируса, ZDNet.

Всем привет! Идея для этой статьи пришла еще месяц назад, но в силу занятости на работе времени катастрофически не хватало. Однажды вечером в YouTube я наткнулся на ролик о создании игры-платформера в стиле пиксельной графики. И тут мне вспомнились мои первые уроки информатики в школе, где мы "рисовали на Бейсике" и играли в "ворона ест буквы".

Предисловие

На дворе стоял 2000-й год. Кризис 98 года остался позади. Я учился в 8 классе местной школы, в небольшом городке. С началом учебного года всех ждало небольшое событие - ввели урок информатики. Многие отнеслись к этому, как к еще одному предмету который надо учить, но были и те, у кого загорелись глаза. В числе последних оказался и я.

Надо отметить, что информатику хоть и ввели, но "ввести новые компьютеры" забыли, потому что денег на эти цели не было. На службе у нашей школы тогда стояли машины made in USSR - "Электроника МС 0511" и несколько их чуть более современных аналогов. Работали они только по им самим ведомым законам, или после прихода некоего "Николая Владимировича" - местного мастера.

Вести предмет как водится поставили молодого и "горячего" преподавателя - девушку 26 лет, которая кстати очень старалась. Мы учили системы счисления и переводили письменно числа из одной в другую. Читали про общее устройство ПК и конечно был Бейсик. У каждого тетрадка была в прочной прозрачной обложке, сзади которой была нарисована система координат. Это был своего рода холст для эскизов фигур, которые мы потом старательно переносили в код.

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

Рисуем первое изображение

Для своих целей я взял BufferedImage. Начал с простой функции, которая рисует пиксель в заданных координатах и с определенным цветом.

fun drawPixel(    x:Int, y:Int, red:Int, green:Int, blue: Int,    image: BufferedImage) {    image.setRGB(x, y, Color(red,green,blue).rgb)}

Чтобы проверить работу набросал метод, который выводит картинку с пикселями рандомного цвета. В функции можно понизить значение каждого из каналов цвета, задав диапазон - красного redRng, зеленого greenRng и синего blueRng цвета.

fun drawRandImage(     image: BufferedImage, stepSize: Int = 1,      redRng: Int = 255, greenRng: Int = 255, blueRng: Int = 255) {     for(posX in 0 until image.width step stepSize){         for (posY in 0 until image.height step stepSize) {            val r = if (redRng <= 0) 0 else Random.nextInt(0, redRng)             val g = if (greenRng <= 0) 0 else Random.nextInt(0, greenRng)            val b = if (blueRng <= 0) 0 else Random.nextInt(0, blueRng)             drawPixel(posX, posY, r, g, b, image)         }      }}

Если поставить в цикле шаг stepSize отличный от единицы и занизить один из каналов, то можно получить интересный эффект.

Вроде что-то вырисовывается. Теперь надо сохранить результат. Роль по записи изображения была героически возложена на ImageIO. Насколько я знаю - он блокирующий, поэтому я его от греха подальше обернул в Thread.

fun writeImage(img: BufferedImage, file: String) {    val imgthread = Thread(Runnable {        ImageIO.write(img, File(file).extension, File(file))    })    try {        imgthread.start()    } catch (ex: Exception) {        ex.printStackTrace()        imgthread.interrupt()    }}

Останавливаться на этом было глупо, поэтому следующим шагом решил сделать "рисовалку" на базе двумерного списка.

Пиксельное сердце

Координаты для отрисовки решил сделать в виде двумерного списка ArrayList<List<Int>>. Получить "пиксельный" эффект мне помогла функция drawTitle, которая "дергает" в цикле drawPixel, рисуя "big pixel" в виде плитки.

fun drawTile(    startX: Int, startY: Int, size: Int,     red: Int, green: Int, blue: Int, image: BufferedImage) {    for (posX in startX until startX+size) {        for (posY in startY until startY+size) {            drawPixel(posX,posY,red,green,blue,image)        }    }}

Настала очередь обработать массив с числами. Сказано-сделано. Добавив с помощью оператора when обработку 4 цветов

fun drawImage(pixels: ArrayList<List<Int>>, image: BufferedImage) {    pixels.forEachIndexed { posY, row ->        row.forEachIndexed { posX, col ->            when(col) {                1 -> drawTile(posX*10,posY*10,10,255,2,0,image)                2 -> drawTile(posX*10,posY*10,10,156,25,31,image)                3 -> drawTile(posX*10,posY*10,10,255,255,255,image)                else -> drawTile(posX*10,posY*10,10,23,0,44,image)            }        }    }}

и создав список в виде двумерного массива, где каждая цифра соответствует своему цвету (1 = красный, 2 = темно-красный, 3 = белый, 4 = фиолетовый)

val map = arrayListOf(    listOf(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),    listOf(0,0,0,1,1,1,0,0,0,1,2,2,0,0,0),    listOf(0,0,1,3,3,1,1,0,1,1,1,2,2,0,0),    listOf(0,1,3,3,1,1,1,1,1,1,1,1,2,2,0),    listOf(0,1,3,1,1,1,1,1,1,1,1,1,2,2,0),    listOf(0,1,1,1,1,1,1,1,1,1,1,1,2,2,0),    listOf(0,1,1,1,1,1,1,1,1,1,1,1,2,2,0),    listOf(0,0,1,1,1,1,1,1,1,1,1,2,2,0,0),    listOf(0,0,0,1,1,1,1,1,1,1,2,2,0,0,0),    listOf(0,0,0,0,1,1,1,1,1,2,2,0,0,0,0),    listOf(0,0,0,0,0,1,1,1,2,2,0,0,0,0,0),    listOf(0,0,0,0,0,0,1,2,2,0,0,0,0,0,0),    listOf(0,0,0,0,0,0,0,2,0,0,0,0,0,0,0),    listOf(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),)

...на выходе получил такую красоту. Мой внутренний "школьник" был очень доволен.

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

Excel как холст

Следующим вечером я продолжил. Сперва подумал о JS (Resct JS), но тут нужно было переписывать все полностью на нем, да и JavaScript я пробовал слишком давно. Хотелось взять что-то простое

По работе часто приходится работать с таблицами, поэтому само собой выбор остановился на Excel. Привел строки столбцы к виду квадратной сетки и вуаля - наш холст готов к работе с цифровыми красками. Осталось лишь только получить данные из ячеек. "Цифровая бумага все стерпит" - подумал я, и взял Apache POI - библиотеку для работы файлами word, excel, pdf. Документация у нее написана хорошо, но некоторые примеры кода там явно требуют корректировки.

Для начала набросал простую лямбду для преобразования hex в rgba, которая отдает стандартный джавовский класс Color.

val toRGBA = { hex: String ->    val red = hex.toLong(16) and 0xff0000 shr 16    val green = hex.toLong(16) and 0xff00 shr 8    val blue = hex.toLong(16) and 0xff    val alpha = hex.toLong(16) and 0xff000000 shr 24    Color(red.toInt(),green.toInt(),blue.toInt(),alpha.toInt())}

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

fun getPixelColors(file: String, listName: String): ArrayList<List<String>> {    val table = FileInputStream(file)    val sheet = WorkbookFactory.create(table).getSheet(listName)    val rowIterator: Iterator<Row> = sheet.iterator()    val rowArray: ArrayList<Int> = ArrayList()    val cellArray: ArrayList<Int> = ArrayList()    while (rowIterator.hasNext()) {        val row: Row = rowIterator.next()        rowArray.add(row.rowNum)        val cellIterator = row.cellIterator()        while (cellIterator.hasNext()) {            val cell = cellIterator.next()            cellArray.add(cell.address.column)        }    }    val rowSize = rowArray.maxOf { el->el }    //...проходим по листу     //...и формируем массив    return pixelMatrix}

Функция немаленькая и всю ее приводить я не буду (ссылка на код в конце статьи). Конечно, ее можно сократить, но ради читаемости я оставил все как есть. И тут хотелось бы остановиться на одном моменте.

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

val rows = sheet.lastRowNumval cells = sheet.getRow(rows).lastCellNum // + rowsval pixArray = Array(rows+1) {Array(ccc+1) {""} }

...то Вы получите ошибку OutOfBounds. Количество строк (row) получается всегда правильным, но количество ячеек порой то меньше, то больше чем нужно. Я так и не понял, почему результат "скачет", причем проявляется это рандомно. Исправить это можно при помощи iterator.hasNext(), который реально возвращает последнюю ячейку.

Дело сталось за малым - преобразовать нашу "пиксельную матрицу" в картинку и вернуть в качестве результата BufferedImage. В отличии от начала статьи, тип картинки у нас изменился на - TYPE_INT_ARGB, чтобы не закрашенные ячейки таковыми и оставались.

fun renderImage(pixels: ArrayList<List<String>>): BufferedImage {    val resultImage = BufferedImage(        pixels[0].size*10,        pixels.size*10,        BufferedImage.TYPE_INT_ARGB    )    pixels.forEachIndexed { posY, row ->        row.forEachIndexed { posX, col ->            drawTile(                (posX)*10,(posY)*10, 10,                toRGBA(col).red, toRGBA(col).green,toRGBA(col).blue,                toRGBA(col).alpha, resultImage            )        }    }    return resultImage}

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

Выводы

Весь код доступен по ссылке на github. Что дальше? В планах добавить поддержку svg, может добавить несколько фильтров (blur, glitch, glow, etc..), переписать все с индусского кода на человеческий, добавить поддержку xls (HSSF Color) и возможно набросать пару тестов. Чего-то больше добавлять не имеет смысла, так как это скорее интересная задача с легким налетом ностальгии, чем какой-то проект.

Послесловие

Конечно, можно было ограничиться лишь "Фотошопом и Экселем" (ctrl+c, ctrl+v), но цель была не просто получить пиксельный "шедевр" в пару кликов. Хотелось вспомнить школьные уроки информатики, ту теплую атмосферу: Бейсик, старые компьютеры, пиксельные рисунки на экране черно-белого монитора "Электроника МС". Да черт побери, в конечном счете это хоть и простая, но интересная задача, потратить на которую пару вечеров просто приятно.

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

Пусть через пару лет "Электронику МС" сменили современные аналоги на базе Pentium, те первые занятия на старых компьютерах навсегда останутся со мной, ведь именно они вложили в меня любовь к компьютерам и всему что с ними связано...

А с чего начиналась информатика у Вас в школе?

Всем спасибо! Всем пока!

В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, Excel де-факто является стандартом. Относительно дружелюбный интерфейс, прозрачная структура, в купе с его распространенностью... трудно навскидку назвать решение лучше.

Однако, у многих Excel до сих пор ассоциируется с чем-то тяжелым, неповоротливым и сложным. Давайте посмотрим, как мы - обычные C# разработчики, можем легко сформировать простой Excel документ, на примере табличного отчета.

Историческая справка

Времена, когда доминировал проприетарный формат .xls(Excel Binary File Format) давно прошли и сейчас мы имеем только .xlsx(Excel Workbook), в рамках Office Open XML. Последний представляет собой обычный .zip архив с XML файлами. Не будем углубляться в его структуру, я искренне надеюсь что вам это никогда не понадобится:)

На github, и не только, можно найти ряд библиотек, бесплатных и не только. Пожалуй самой популярной является EPPlus. До определенной степени, она довольно хорошо отражает концепцию Excel, именно по этому я всегда использую EPPlus. Версия 4 полностью бесплатна, начиная с 5й версии вам потребуется приобрести лицензию для коммерческого использования.

Задача

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

Сам по себе, отчет содержит краткое описание компании и историю изменения некоторых экономических показателей. Для простоты все свойства компании строки. Экономические показатели большие целые числа и числа с плавающей точкой, а также даты. Предположим, что где-то в недрах микросервисного backend-да есть сервис-генератор подобных отчетов, например по id компании. Однако, поскольку id нет смысла выводить пользователю, идентификатор отсутствует в самой модели отчета.

Аналитик, в свою очередь, выдает задачу с феноменально точнымописанием - "Сгенерироватьexcel отчет на базе данных MarketReport". Что ж, для нашего примера, создадим заглушку генератор фейковых данных:

Первый запуск

Подключим EPPlus версии 4.5.3.3 и создадим базовую обвязку для будущего генератора.

Сердцем генератора будет метод Generate. ExcelPackage это модель документа, через которую мы и будем осуществлять все взаимодействияс ним. Также имеется конструктор для передачи пути к файлу или потока.

В методе main создается генератор отчетов, а также генератор Excel файлов. Далее полученный файл просто записывается на диск.

При попытке запустить приложение, получаем exception:InvalidOperationException: The workbook must contain at least one worksheet

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

var sheet = package.Workbook.Worksheets    .Add("Market Report");

Запускаем снова и... вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB - значит мы работаем с Excel правильно и все идет как надо.

Вывод данных

Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:

sheet.Cells["B2"].Value = "Company:";sheet.Cells[2, 3].Value = report.Company.Name;

sheet.Cells["B2"].Value = "Company:";sheet.Cells[2, 3].Value = report.Company.Name;sheet.Cells["B3"].Value = "Location:";sheet.Cells["C3"].Value = $"{report.Company.Address}, " +  $"{report.Company.City}, " +                            $"{report.Company.Country}";sheet.Cells["B4"].Value = "Sector:";sheet.Cells["C4"].Value = report.Company.Sector;sheet.Cells["B5"].Value = report.Company.Description;

Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:

sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][]{ new []{"Capitalization", "SharePrice", "Date"} });var row = 9;var column = 2;foreach (var item in report.History){  sheet.Cells[row, column].Value = item.Capitalization;  sheet.Cells[row, column + 1].Value = item.SharePrice;  sheet.Cells[row, column + 2].Value = item.Date;      row++;}

Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.

Стилизация

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

Во-первых, шапка никак не выделяется, во-вторых таблица не имеет границ... выравнивание пляшет, даты отображаются магическимичислами, а капитализация "уходит в какую-то математику" - как это прокомментировал аналитик.

Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности... но, мы же backend разработчики, так давайте сверстаем Excel Sheet!

Размер ячеек

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

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

sheet.Cells[1, 1, row, column + 2].AutoFitColumns();sheet.Column(2).Width = 14;sheet.Column(3).Width = 12;

Формат данных

Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже...

sheet.Cells[9, 4, 9 + report.History.Length, 4].Style.Numberformat.Format = "yyyy";sheet.Cells[9, 2, 9 + report.History.Length, 2].Style.Numberformat.Format =  "### ### ### ##0";

Выравнивание

Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.

sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

Стиль текста

Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:

sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true;sheet.Cells["B2:C4"].Style.Font.Bold = true;

Границы

Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой... не так ли?

sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double);sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

График

"Ну что за отчет без графиков, верно, Карл?" - ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го...

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

var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line);capitalizationChart.Title.Text = "Capitalization";capitalizationChart.SetPosition(7, 0, 5, 0);capitalizationChart.SetSize(800, 400);var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"]));capitalizationData.Header = report.Company.Currency;

Еще, может понадобиться защитить страницу от редактирования:

sheet.Protection.IsProtected = true;

Заключение

public byte[] Generate(MarketReport report){      var package = new ExcelPackage();        var sheet = package.Workbook.Worksheets            .Add("Market Report");          sheet.Cells["B2"].Value = "Company:";      sheet.Cells[2, 3].Value = report.Company.Name;      sheet.Cells["B3"].Value = "Location:";      sheet.Cells["C3"].Value = $"{report.Company.Address}, " +    $"{report.Company.City}, " +                                 $"{report.Company.Country}";      sheet.Cells["B4"].Value = "Sector:";      sheet.Cells["C4"].Value = report.Company.Sector;      sheet.Cells["B5"].Value = report.Company.Description;        sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][]{ new []{"Capitalization", "SharePrice", "Date"} });      var row = 9;      var column = 2;      foreach (var item in report.History)      {            sheet.Cells[row, column].Value = item.Capitalization;           sheet.Cells[row, column + 1].Value = item.SharePrice;           sheet.Cells[row, column + 2].Value = item.Date;            row++;      }        sheet.Cells[1, 1, row, column + 2].AutoFitColumns();      sheet.Column(2).Width = 14;      sheet.Column(3).Width = 12;            sheet.Cells[9, 4, 9+ report.History.Length, 4].Style.Numberformat.Format = "yyyy";      sheet.Cells[9, 2, 9+ report.History.Length, 2].Style.Numberformat.Format =  "### ### ### ##0";        sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;      sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;      sheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;        sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true;      sheet.Cells["B2:C4"].Style.Font.Bold = true;    sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double);      sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;         var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line);      capitalizationChart.Title.Text = "Capitalization";      capitalizationChart.SetPosition(7, 0, 5, 0);      capitalizationChart.SetSize(800, 400);      var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"]));      capitalizationData.Header = report.Company.Currency;           sheet.Protection.IsProtected = true;        return package.GetAsByteArray();}

Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.

Во-вторых, возможно имеет смысл искать новоюработу, но, забегая вперед, я бы с этим не спешил... Если данная публикация наберет 1+ просмотров, то во второй части мы поговорим о том, как можно отделить стилизациюот логики заполнения данными, упростить манипуляции над ячейками и в целом сделаем код боле поддерживаемым.

Предыстория

Как известно Лень двигатель прогресса. В своей работе однажды я столкнулся задачей, когда нужно было составить таблицу расчёта процентов по договору займа, где за базу должно было быть фактическое количество дней в году. Неудобство составляло то, что нужно было не забыть про високосные года и разделять дни, которые относятся к високосному году и дни не високосных лет. Была написана простенькая формула, но позже я выяснил, что расчётом високосных лет не всё так просто.

Описание проблемы

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

Причина 1

Большинство стран живут по Григорианскому календарю, правила високосных лет для которого были определены ещё 1582 году римским папой Григорием XIII:

1. Год, номер которого кратен 400, - високосный;

2. Остальные годы, номер которых кратен 100, - невисокосные (например, годы 1700, 1800, 1900, 2100, 2200,2300)

3. Остальные годы, номер которых кратен 4, - високосные.

Также существует неоднозначность в мнениях определения високосных лет таких как 2900, 3200, 4000, поэтому я решил ограничить функцию максимальной датой 01.01.2900.

Причина 2

Пользовательская функция в Excel создаётся на языке VBA (Visual Basic for Applications). Несмотря на то, что интерпретатор данного языка встроен в MS Office, я обнаружил некоторые отличия в работе с датами.

Excel поддерживает две системы дат, так называемые системы 1900 и 1904. По умолчанию используется система 1900. Это означает, что число 1 введённое в ячейку соответствует 01 января 1900 года, 2 2 января и так далее.

В VBA есть функция CDate(expression), которая приводит к типу Date введённое значение. И если этой функции передать число 1, то она вернёт переменную типа Date с датой 31 декабря 1899 года. А вот для числа 60 функция CDate вернёт 28.02.1900, а то же значение введённое в ячейку будет отображать 29.02.1900 (хотя, конечно, 1900 год високосным не является). Далее, начиная с 01 марта 1900 года значения дат выравниваются.

Такое поведение Excel, давно известно компании Microsoft и было принято решение оставить как есть, нужно просто учитывать его. Поэтому и появилось второе ограничение минимальной даты периода 01 марта 1900 года.

Алгоритм решения

Для того, чтобы скорость вычисления количества (не)високосных не зависела от количества дней между датами мне нужно было разработать некую формулу или алгоритм, где не было бы никаких циклов.

Так как все делители, с помощью которых мы можем определить високосность года кратны 4, то я решил разбить все годы на блоки по 4 (квартеты) начиная с 1 года. То есть 1-й блок начинается с 1 года и заканчивается 4, 2-й блок с 5 по 8 и так далее.

В каждом блоке год будет иметь свой индекс от 1 до 4 (например, 2021 год это 506-й блок, индекс в блоке 1)

Теперь мы можем разделить вычисление на 3 блока:

В зависимости от года параметров и индексов квартетов формула расчёта количества дней високосного года будет следующей:

Если год начальной и конечной даты равны и год високосный:

Если год начальной даты невисокосный, а конечной високосный и индексы квартета начальной даты и конечной равны, то:

Если год начальной даты високосный, а конечной нет, то:

Ну и наконец, если год начальной даты невисокосный и конечной тоже, индексы квартетов разные и в текущем индексе квартета есть високосный год, то в 1-м квартете количество дней високосного года лежащего внутри периода будет 366 (так как невисокосные года квартета с 1 по 3, а вторая дата лежит в одном из следующих квартетов).

Выше указанная логика расчёта количества дней високосных лет для первого квартета реализована следующей функцией на VBA:

Private Function first_quartet_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long    Dim result As Long    result = 0        Dim year_diff As Long    Dim quartet_index_diff As Long        year_diff = year(d_end) - year(d_begin)    quartet_index_diff = quartet_index(year(d_end)) - quartet_index(year(d_begin))        If year_diff = 0 And is_year_leap(d_begin) Then        result = DateDiff("d", d_begin, d_end)        first_quartet_leap_year_days = result        Exit Function    End If        If quartet_index_diff = 0 Then            If is_year_leap(d_begin) Then            result = DateDiff("d", d_begin, CDate(DateSerial(year(d_begin), 12, 31)))            first_quartet_leap_year_days = result            Exit Function                End If                If is_year_leap(d_end) Then            result = DateDiff("d", CDate(DateSerial(year(d_end) - 1, 12, 31)), d_end)            first_quartet_leap_year_days = result            Exit Function        End If            Else            If is_year_leap(d_begin) Then            result = DateDiff("d", d_begin, CDate(DateSerial(year(d_begin), 12, 31)))            first_quartet_leap_year_days = result            Exit Function        Else                    If Not is_quartet_noleap(quartet_index(year(d_begin))) Then                result = 366                first_quartet_leap_year_days = result                Exit Function            End If                    End If            End If    first_quartet_leap_year_days = result    End Function

Если разница индексов квартетов начальной и конечной даты >0, то рассчитывается 3-й блок формулы "Количество високосных дней в последнем квартете".

Здесь формула только одна, где при условии, что год конечной даты високосный:

Private Function last_quartet_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long        Dim result As Long    result = 0         Dim quartet_index_diff As Long           quartet_index_diff = quartet_index(year(d_end)) - quartet_index(year(d_begin))        If quartet_index_diff > 0 Then            If is_year_leap(d_end) Then            result = DateDiff("d", CDate(DateSerial(year(d_end) - 1, 12, 31)), d_end)        End If            End If             last_quartet_leap_year_days = result    End Function

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

При этом К полных столетий означает разность индексов столетий между датами. Например 1999 индекс столетия 19, а 2001 20, таким образом разность столетий 1.

Аналогично и 400-летий.

Private Function middle_quartets_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long        Dim quartet_count As Long        quartet_count = middle_quartets_count(d_begin, d_end)        If quartet_count = 0 Then            middle_quartets_leap_year_days = 0        Exit Function            End If        Dim q_begin, q_end As Long        q_begin = quartet_index(year(d_begin))    q_end = quartet_index(year(d_end)) - 1        Dim quot_25, quot_100 As Integer        quot_25 = WorksheetFunction.Quotient(q_end, 25) - WorksheetFunction.Quotient(q_begin, 25)    quot_100 = WorksheetFunction.Quotient(q_end, 100) - WorksheetFunction.Quotient(q_begin, 100)        Dim result As Long        result = (quartet_count - quot_25 + quot_100) * 366        middle_quartets_leap_year_days = result        End Function

Реализация функций

Функция вычисления високосных дней для периода:

Public Function LEAP_DAYS(ByVal val_begin As Long, ByVal val_end As Long, Optional count_first_day = 0, Optional count_last_day = 1) As Long        Dim d_begin, d_end As Date        count_first_day = IIf(count_first_day <> 0, 1, 0)    count_last_day = IIf(count_last_day <> 0, 1, 0)            d_begin = CDate(val_begin)    d_end = CDate(val_end)        Dim check_error As Variant    check_error = check_constrains(d_begin, d_end)        If IsError(check_error) Then        LEAP_DAYS = check_error        Exit Function    End If        Dim result As Long    result = 0        If is_year_leap(d_begin) And count_first_day = 1 Then result = result + 1    If is_year_leap(d_end) And count_last_day = 0 Then result = result - 1        result = result + first_quartet_leap_year_days(d_begin, d_end) _            + middle_quartets_leap_year_days(d_begin, d_end) _            + last_quartet_leap_year_days(d_begin, d_end)        LEAP_DAYS = result    End Function

В приведённом выше коде мы сначала приводим значения параметров count_first_day и count_last_day к значению 1 или 0. Затем мы объявляем переменные типа Date для даты начала и окончания периода и задаём значения. Далее следует проверка параметров на ограничения.

По умолчанию функция не учитывает первый день периода, но учитывает последний день периода, но с помощью необязательных параметров указанных абзацем выше можно изменить это поведение. В строках 23-24 мы корректируем результат функции в зависимости от параметров.

Далее идёт сложение результатов промежуточных функций, которые были выше описаны.

Заключение

Таким образом мы получили формулы расчёта количества дней високосных и невисокосных лет в заданном периоде, скорость которой не зависит от количества дней в периоде. Единственный минус это то, что формула способна корректно работать только в рамках одной тысячи лет (2900 - 1900). Думаю, что до 2900 года у нас есть ещё время усовершенствовать такую функцию.

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

Гитхаб

Источники и дополнительные ссылки

1. Статья из журнала "Главная книга" "Считаем проценты по займу: день первый, день последний"

2. Excel неправильно предполагает, что 1900 год является високосным годом.

3. Статья из Википедии "Григорианский календарь"

Voximplant, как и любая облачная платформа, предоставляет API, с помощью которого можно управлять пользователями, запускать звонки, просматривать их историю и логи и делать немало других полезных вещей. В принципе, практически всё, что можно настроить в панели управления, можно сделать и через API.

Но в современном мире иметь API недостаточно мало кто хочет формировать HTTP-запросы, передавать параметры, думать про правильную авторизацию. Поэтому мы предлагаем SDK для разных языков программирования: Python, PHP, C# и многих других. И кажется, что этого достаточно, чтобы сделать нашу платформу лёгкой в использовании для очень большой аудитории. Или всё-таки недостаточно?

Обратимся к статистике. По разным данным сейчас в мире насчитывается где-то 15-30 миллионов разработчиков цифра несомненно впечатляющая. Но, например, пользователей MS Excel в мире не менее 100 миллионов. Почему же они должны страдать? Ведь, будем честны, почти каждый из тех, кто хоть раз открывал Excel, явно ощущал недостаток возможностей по управлению коммуникационными платформами в этом без сомнения очень гибком программном продукте. Практически каждый день мы получаем на наш email сотни запросов, которые сводятся к очень простой просьбе: Я хочу звонить из Excel!. Однажды у окон нашего офиса даже выстроились люди с такими требованиями (видели фото выше?) Мы просто не могли оставаться в стороне.

Однако звонки это всё-таки слишком революционно, а главное, потребует установки дополнительных ActiveX-компонентов, что, безусловно, противоречит всем существующим и несуществующим политикам информационной безопасности, поэтому давайте начнём с более простой вещи SDK для работы с нашим API. Из средств разработки в Экселе доступен VBA, для него мы и создадим SDK.

Для того, чтобы выполнить API-запрос, необходимо:

1. Сформировать URL и тело POST-запроса.

2. Добавить аутентификационные параметры.

3. Непосредственно выполнить запрос.

4. Распарсить результат (в нашем случае это JSON).

Формируем URL и тело POST-запроса

Первая часть, казалось бы, самая простая: нужно просто закодировать параметры в URL-кодировку и склеить их. Но в стандартом VBA не предусмотрена URL-кодировка (позже мы поймём, почему). Ничего страшного, на просторах Интернета есть множество разных решений, выберем одно из них.

Public Function URL_Encode(ByRef txt As String) As String    Dim buffer As String, i As Long, c As Long, n As Long    buffer = String$(Len(txt) * 12, "%")     For i = 1 To Len(txt)        c = AscW(Mid$(txt, i, 1)) And 65535         Select Case c            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95  ' Unescaped 0-9A-Za-z-._ '                n = n + 1                Mid$(buffer, n) = ChrW(c)            Case Is <= 127            ' Escaped UTF-8 1 bytes U+0000 to U+007F '                n = n + 3                Mid$(buffer, n - 1) = Right$(Hex$(256 + c), 2)            Case Is <= 2047           ' Escaped UTF-8 2 bytes U+0080 to U+07FF '                n = n + 6                Mid$(buffer, n - 4) = Hex$(192 + (c \ 64))                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))            Case 55296 To 57343       ' Escaped UTF-8 4 bytes U+010000 to U+10FFFF '                i = i + 1                c = 65536 + (c Mod 1024) * 1024 + (AscW(Mid$(txt, i, 1)) And 1023)                n = n + 12                Mid$(buffer, n - 10) = Hex$(240 + (c \ 262144))                Mid$(buffer, n - 7) = Hex$(128 + ((c \ 4096) Mod 64))                Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))            Case Else                 ' Escaped UTF-8 3 bytes U+0800 to U+FFFF '                n = n + 9                Mid$(buffer, n - 7) = Hex$(224 + (c \ 4096))                Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))        End Select    Next    URL_Encode = Left$(buffer, n)End Function

Следующий нюанс передача даты и времени. В API Voximplant временные метки принимаются в UTC в формате YYYY-MM-DD hh:mm:ss. В Excel же дата и время хранятся без учёта часового пояса (на самом деле, в самой таблице они вообще хранятся как число с плавающей точкой). Поэтому нам придётся принимать дату/время из таблицы тоже UTC. Мы думаем, что все 100+ миллионов пользователей Excel знают, что такое UTC, и это не вызовет у них никаких вопросов.

Кстати, в VBA есть функция форматирования даты, и она даже работает, но весьма необычным образом. Интересующий нас формат даты описывается так: yyyy-mm-dd hh:mm:ss. То есть mm это либо месяц, либо минуты в зависимости от того, за чем оно следует: за hhили за yyyy (это не шутка, это даже в MSDN описано). В общем, если кто-то захочет вывести время без часов, придётся импровизировать.

Переходим к аутентификации

Здесь нас ожидает самое большое разочарование. Мы в Voximplant предлагаем нашим клиентам использовать JWT, что, конечно, весьма мудрёно, если выполнять запросы из консоли или браузера, но при использовании наших SDK это совершенно никак не усложняет жизнь разработчику. В то же время JWT обеспечивает крайне высокий уровень безопасности.

А что же VBA? К сожалению, разумно простого способа сформировать JWT-подпись просто не существует. Причина в том, что в VBA доступен фреймворк .NET версии 4.x, а функция RSA.ImportPkcs8PrivateKey, необходимая для загрузки приватного ключа из PKCS8, появилась только в .NET 5. Да и вообще, все .NET-разработчики используют для таких задач сторонние библиотеки.

Поэтому нам придётся ограничиться авторизацией с помощью статического API-ключа. Ведь, как известно, один из способов обработки риска информационной безопасности - принятие этого риска. Так и поступим. Ведь нам ну очень-очень нужен этот SDK.

Выполняем запрос

Переходим к третьей части к выполнению самого запроса. Встроенных средств работы с HTTP в VBA нет (теперь понятно, почему нет и функции URL-кодирования, а зачем?).

Но, тем не менее, это достаточно тривиальная манипуляция подключаем необходимый фреймворк MSXML 6.0 и Microsoft Scripting Runtime и выполняем запрос, подключая через COM сам MSXML. Просто!

Function makeRequest(name As String, params As Dictionary, accountId As Integer, apiKey As String) As Object    Dim objHTTP As New MSXML2.XMLHTTP60    Dim jsonData As String    Dim parsedJson As Object    Dim postString As String    postString = ""        Dim iterKey As Variant        For Each iterKey In params.Keys        postString = postString & "&" & iterKey & "=" & URL_Encode(params(iterKey))    Next    Url = "https://api.voximplant.com/platform_api/" + name    objHTTP.Open "POST", Url, False    objHTTP.send "account_id=" & accountId & "&api_key=" & apiKey & postString    jsonData = objHTTP.responseText    Set parsedJson = JsonConverter.ParseJson(jsonData)    Set makeRequest = parsedJsonEnd Function

Парсим JSON

Ну и, наконец, JSON. Как и всё остальное, парсер JSON надо искать где-то вовне экосистемы VBA. К счастью, на дворе 2021 год, есть GitHub, и кто-то уже озадачился созданием JSON-парсера для VBA. Мы взяли вот такой.

Он подключается как отдельный модуль и превращает JSON-строку в Dictionary. То, что нужно!

Дальше берём генератор одного из наших SDK (мы взяли питоновский), заменяем шаблоны и заставляем его генерировать код на VBA. В итоге получаем готовый SDK, который можно скачать на нашем GitHub.

SDK представляет собой Class Module, который можно подключить к вашей любимой книге в Excel и делать с его помощью разные странные вещи. В принципе, можно даже звонки запускать, SDK поддерживает все необходимые для этого функции API в). Но это будет не лучшей идеей, учитывая, что не до конца известно, когда Excel решит пересчитать все формулы (именно в этот момент произойдёт вызов функции API).

А вот для каких-то статистических задач SDK, несмотря на всю абсурдность происходящего, может подойти. Например, можно посчитать, какой из пользователей потратил на звонки больше всех денег за какой-то период.

Для этого пишем вот такую функцию:

Function getTotalCallCost(FromDate, ToDate, Username) As Double    Dim totalCost As Double    Dim lastCount As Integer    Dim offset As Integer    Dim res As Dictionary    Dim RecordsPerRequest As Integer    Dim api As New VoximplantAPI    totalCost = 0    lastCount = 1    offset = 0    RecordsPerRequest = 100        'Pass Voximplant account id and API key    api.SetCredentials 100, "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"        Do While lastCount > 0        Set res = api.GetCallHistory(FromDate, ToDate, remote_number:=Username, with_calls:=True, with_records:=True, with_other_resources:=True, offset:=offset, count:=RecordsPerRequest)                Dim session As Variant        Dim item As Variant                For Each session In res("result")            For Each item In session("calls")                totalCost = totalCost + item("cost")            Next            For Each item In session("records")                totalCost = totalCost + item("cost")            Next            For Each item In session("other_resource_usage")                totalCost = totalCost + item("cost")            Next        Next                lastCount = res("count")        offset = offset + RecordsPerRequest    Loop        getTotalCallCost = totalCostEnd Function

И вызываем её следующим образом:

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

Резюме:

При желании можно и для VBA сделать какое-то подобие SDK. При его создании не пострадал ни один разработчик. Ах да, с 1 апреля! :D

Хотя в среднем для каждодневных задач автоматизация не требуется, бывают случаи, когда она может быть необходима.Создание множества диаграмм, рисунков, таблиц и отчётов может утомить, если вы работаете вручную. Так быть не должно.Можно построить конвейер на 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, SAP и ещё парочки источников, но так как я очень ленив, стал искать способ как это дело можно автоматизировать и вот уже два года, я с некой периодичностью создаю новые или редактирую старые программы действий для компьютера (скрипты или сценарии), позволяя ему работать вместо меня, а себе гонять чай в рабочее время посматривая на экран. Я не стал супер экспертом по автоматизированию (RPA разработка если по научному), но работа стала приносить намного меньше стресса.

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

Почему она?
- Она бесплатная. Совсем. Никаких пробных версий, никаких реклам в самой программе, создана на чистом энтузиазме и добровольных пожертвованиях автору.
- Интерфейс максимально прост, как и язык написания сценариев.
- Есть возможность записи действий, можно полностью обойтись без редактирования или создания с нуля своего скрипта.
- Встроенный редактор.
- Не требуются права администратора для установки, скачал - запустил.
- Русскоязычная справка по программе и русскоязычное сообщество.

Что можно с помощью неё делать?
Лично я автоматизировал работу с 1С, SAP, сайтами, тем же Excel, публикации в социальных сетях. Так же можно запрограммировать действия в играх, реакцию на происходящее на экране, набор текста, буквально что угодно.

познакомимся с интерфейсом программы:

Он минималистичен и тем хорош. Создадим свой первый скрипт и попробуем написать какой-нибудь простой скрипт, своего рода Hello world от мира автокликинга.

Нажимаем на кнопку создания скрипта (1) и кнопку редактора (3).

Интерфейс редактора

Нажимаем на сохранить как (4) выбираем место, где он будет храниться (желательно оставить его в папке с программой) и имя сценария.

Например, создадим скрипт который будет брать адрес страницы из таблицы Excel, открывать его в браузере и так несколько раз. Вот что у нас получилось:

https://cs14.pikabu.ru/video/2021/04/11/1618089701287548721_1920x1080.webm

Мы используем несколько команд:

WAIT- небольшая задержка между выполнениями действий, нужна для того, чтобы компьютер успевал открывать страницы, переключать между окнами и так далее, нужна почти между всеми действиями.

LCLICK- Кликает левой кнопкой мыши по указанным в скобках координатам, необходимости прописывать их в ручную нет, просто наводите мышь туда, куда хотите кликнуть и нажимаете комбинациюALT + Q. Команда с координатами вставляется в скрипт автоматически. Либо если хотите вручную их поправить, опять же наводим мышь на желаемое расположение и смотрим сюда:

RCLICK- Соответственно всё тоже самое, только правой кнопкой мыши, вызывается тем же набором клавиш, просто нужно заменить в скрипте L на R.

Шаблоныкопирования и вставки (CTRL + C, CTRL +V) они уже есть в разделе шаблоны, нет необходимости их прописывать самому, просто выбираем подходящий.

KEYPRESS- В том случае, если нам нужно нажать какую либо кнопку, выбираем эту команду, кстати чтобы не прописывать в ручную, эту команду тоже можно выбрать из списка команд:

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

Написать эту статейку меня побудила замечательная серия многоуважаемого @abak "Теория инвестиций для начинающих". В качестве конфетки внизу даю ссылку на табличку, получающую текущие цены на европейские фонды, их прибыльность и другие параметры из интернета. Кроме того мне хотелось бы немного расширить изначальную статью с точки зрения начинающего инвестора, инвестирующего из дома и лишенного вкусностей внутренней кухни Дойче банка.

Предостережение: Автор ни разу не спец в скрэпинге интернет-сайтов и инвестициях. Всё что здесь описывается делалось для души и на коленке. Автор никому ничего не обещает, а лишь делится собственным опытом и мыслями по поводу инвестиций, в частности в отраслевые фонды. Ещё одна цель дать пищу для размышлений, а не предоставить рецепт от всех болезней. Всё, что здесь описано, добывалось автором путём проб и ошибок, а, как известно: Срубленные головы стремительно умнеют. Потому, главный посыл статейки: Не бояться экспериментировать.

Почему фонды

Хотя вся необходимая информация и доступна и бесплатна, докопаться до реально работающих схем, приносящих доход, каждому приходится самому. Та пена, которую все ежедневно видят на баннерах типа: Покупай акции такие-то и продавай такие-то не имеют ничего общего с реальной жизнью. В частности, реклама фондов, рассылаемая банками (Вложись в супер-ETF и обрети целых 4.5% годовых!!!), оказала на автора противоположное действие и отложило инвестиции в фонды на три года. Прежде чем перейти к фондам я остановлюсь на минусах акций и облигаций.

Акции:

• Трудно установить точку входа и выхода из позиции. Все индикаторы вроде для того и существуют, но на практике не работают.

• Анализ всяких технических вкусностей вроде ROE (см. finance.yahoo.com, раздел Statistics и Financials), а также прибылей за определённые промежутки времени, по аналогии с выложенной таблицей для оценки фондов, даёт прибыльность меньшую, нежели фонды.

• Высокая волатильность.

• Абсолютно непонятна логика/психология рынка: Почему если в новостях говорят о том, что у фирмы был хороший или плохой год, курс может как упасть, так и подняться. Где логика?

• С акциями такое чувство, будто все фирмы мухлюют с отчётностью (привет, Wirecard!)

Облигации:

• Низкая доходность

• Можно по недосмотру нарваться на что-то экзотическое, например на облигацию, которая погашается по мере приближения к моменту погашения, зато

Куча плюсов облигаций:

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

• Можно играть на курсах, зная, что по истечению времени действия облигации будет выплачена полная её стоимость (а может и не выплачена, если по облигации случился дефолт).

• В случае дефолта, владельцы облигаций обладают преимуществом при получении имущества обанкротившейся фирмы (проданного конечно, никто не пришлёт Вам по почте старый стул). Правда выплат по дефолтным облигациям можно ждать лет десять: года три назад я с удивлением увидел в поиске облигаций Lehman Brothers.

Поэтому я выбрал

Фонды

Точнее отраслевые фонды. В отличие от индексных фондов, в которые входят взвешенные акции определённого индекса, в отраслевых фондах намешана любая солянка. Главное, чтобы вкладчики были довольны.

Чтобы облегчить себе, а теперь уже и вам, жизнь, я составил табличку на старом добром Экселе, которая грабит данные с сайта ariva.de. Так как странички иногда меняют формат и наполнение, то табличка требует постоянного ухода, что я постараюсь и делать через GitHub. В общем, снимаю шляпу перед теми, кто занимается скрэпингом профессионально: это редкостное болото, вытянуть из которого что-то дельное очень не просто.

Таблица

Для тех, кто никогда не работал с Selenium в Экселе, существует библиотека SeleniumBasic. Коротенькое описание установки для работы с ней приведено здесь. По нажатии на Update, таблица загружает данные о прибыли ценной бумаги (ЦБ) с сайта ariva.de.

Внутренности таблицы:

Таблица настроена на работу с запятой в качестве разделителя между целой и дробной частями числа. Американский формат записи, через точку, не тестировался.

Цель таблицы: Обновить данные и предоставить мне и вам удобные сортировки по разным критериям:

• 3m/6m/1yr/3yrs/5yrs: Доходность ЦБ за данный период в процентах. В принципе меня обычно интересует только доходность за последние 3 месяца.

• Положение (место) в каждой категории (3m/6m) вычисляется в колонках AB:AI.

• Ну и в колонке AJ находится сумма всех категорий. Теоретически, чем меньше число, тем больший доход принесёт фонд. Стоит также отменить, что в сумму входят так же значения альфа и бета и отношение Шарпа, хотя они не являются линейнозависимыми. В общем можно и альфа и бета выкинуть.

В качестве простеньких бонусов:

Часто задают вопрос: А откуда следует, что если фонд давал прибыль в прошлом, то он будет давать прибыль и в будущем? Для ответа нарисуем график прибыли между 6 месяцами и 3 месяцами (условное будущее) и между годом и 6 месяцами (прошлое). Как видно из графика, чем больше прибыль в прошлом, тем больше прибыль и в будущем. Если убрать сильно выпадающие точки в верхней части графика, то коэффициент корреляции будет ещё выше: 0.2. Для автора этот результат был откровением, так как автор ожидал более хаотичного распределения точек. И, не забываем, что посредине, между 6 месяцами и годом был кризис в виде ковида. Выпадающие две верхних точки относятся к зелёной энергетике, т. Е. к хайпу в настоящий момент. Конечно, хотелось бы, чтобы наклон был больше, но ведь растёт!

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

Как работает табличка

Сперва несколько простых напоминаний, которые все знают, но без которых никуда:

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

• Ждать кризиса, чтобы вложиться на низких ценах, или бояться кризиса, это примерно как откладывать по тем же причинам рождение детей. Кризис будет, и будет он завтра, а сегодня необходимо инвестировать (ну и параллельно делать детей).

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

Что я стараюсь делать:

• Продаю ЦБ только не ранее 3 месяцев после покупки. Даю каждой ЦБ шанс показать на что она способна. А могу и продать и раньше хозяин счёта всё-таки я, а не табличка.

• Если ЦБ лежит на счету уже более 3 месяцев и выбывает из первой десятки, то могу её продать и купить что-то из первой пятёрки.

• Когда наступает время очередного зуда и хочется продать или купить или и то и другое, я залезаю на finanzen.net/ariva.de в раздел Fonds/ETFs в разделы Fonds-Suche и ETF-Suche (поиск по фондам и ETF), ищу фонды с максимальной прибыльностью за год и вношу их в табличку. Таким образом проблема попасть на фонд вроде индексного фонда японского рынка и зависнуть на десятилетия в минусах практически невозможна.

Что я стараюсь не делать:

• Вкладываться в иные валюты, нежели евро и доллар. Если посмотреть в долгосрочной перспективе, все региональные валюты (рубль, турецкая лира, ранд, бразильский реал) постоянно снижаются. Единственное наверное исключение это юань, но в него вложиться надо сильно постараться китайские рынки так и остаются за китайской же стеной. Видимо по традиции боятся варваров с севера, и правильно делают.

• Вкладываться в хайп: Руки, конечно, чешутся, но Тесла/Биткоин не для меня. Если хотите, чтобы Тесла/Биткоин обрушилась, напишите мне и я их куплю. Убытки поделим пополам :-)

• Меня смущают фонды, не торгующиеся на бирже (пример: green benefit Global Impact Fund). Да, он есть в моей копилке, но его покупка растянулась на три дня, так что быстро избавиться от него в течение 10 минут и по нужной цене скорее всего не удастся.

• Не связываюсь с акциями и облигациями европейских фирм: К ним у меня никакого доверия после банкротства AirBerlin и Sympatex, чьи дефолтные облигации до сих пор висят мёртвым грузом на моём счету. Только моя медлительность спасла меня от покупки Wirecard прямо перед их дефолтом. Да и Баффета недавно немцы обидели.

• Я не вкладываю в ЦБ дополнительных денег и вот почему (это лишь моё видение, оно не критично, у каждого свой стиль инвестирования):

• Фонд должен приносить деньги мне, а не я ему. Поэтому 10% прибыли я вывожу и вкладываю в покупку золота, наличной валюты (можно и в тушенку/патроны, зависит от фантазии), в НЗ, на случай если всё обрушится.

• Не получается откладывать 10% с зарплаты получается наверное намазываем на хлеб слишком много масла и моемся горячей водой.

А это ни тепло, ни холодно:

• Комиссия инвестиционной компании (ИК): Не особо обращаю внимания на комиссию. В некоторых случаях она достигает 5%, но и отдача фонда в таком случае может быть неплохой.

• StopLoss-ы: Иногда применяю, но чаще нет. Их иногда внезапно срывает, после чего позиция откатывается на старые значения. Но уже без меня.

• Продавать всё во время кризиса. Да, в этот раз (март 2020) стоп-лоссы сработали как часы и я всё быстро распродал, в основном с прибылью. Но потом рынки достаточно быстро восстановились и я еле успел вложиться. Так что вышло то на то.

• Можно застраховать фонды через Put-опционы. Тут у меня двойственная позиция:

• Покупать: Автор предпочитает спать спокойно и видеть, что счёт в ИК не зависит от падения рынка.

• Не покупать: Автор предпочитает спать спокойно, а не подгадывать момент, когда рынок упал с целью продать опцион.

• В общем, и так и эдак плохо. Реально, последние две мои покупки опционов закончились потерей денег, ну а в сумме: плюс/минус ноль.

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

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

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

Риски

• Доллар обрушится, обрушится: Честно говоря, не верю по одной причине:

• Штаты собирают налоги со всего мира: Вот с какого бодуна мне приходится регулярно заполнять форму W-8BEN и посылать её в ИК, хотя я ни разу не гражданин США, а лишь купил их акции или АДР (американские депозитарные расписки)?

• А вдруг будет новый кризис?

• Кризис будет, но, экономика сейчас достаточно устойчива. Проверено корона-вирусом.

• А вдруг завтра война?

• Очень возможно, учитывая события 2014 года. В этом случае все накопления превратятся в ничто и именно на этот случай 10% прибыли и откладываются в НЗ (золото/тушенка/патроны). Автор до сих пор с содроганием вспоминает прочитанные в школе рассказы очевидцев о блокадном Ленинграде, когда за золото выменивали килограмм требухи типа лёгких.

• А как на счёт искусственного раскачивания лодки, вроде того что сделали с GameStop?

• Вот это действительно рискованно. Надеюсь у них хватит такта остановиться. А вообще, вся ситуация с GameStop сильно напоминает Россию в февральскую революцию, когда кучке большевиков удалось расшатать за пару месяцев огромную империю. Есть в Reddite что-то анархо-большевицкое.

Заключение

Вся информация, имеющаяся в статье, получена из открытых источников и ни разу не секретная. Кроме того, табличка скорее корова Шрёдингера неизвестно, жива она, или уже мертва, узнать это можно только опытным путём, начав инвестировать.

Ссылка на табличку

Будьте здоровы!

Живите богато!

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

Так как большинство выписок предоставляются банками в формате старых добрых 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 поиск ключевых слов не был чувствителен к регистру, так что эту проблему решать не пришлось.

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

1. Постановка задачи

В связи с тем, что мне нельзя разглашать подробности ТЗ, сущности, алгоритмы сбора данных и т. д. Пришлось придумать что-то аналогичное:

Итак представим, что у нас есть онлайн чат с высокой активностью, и заказчик хочет выгружать все сообщения, обогащенные данными о пользователе, за определенную дату в Excel. В день может копиться более 1 миллиона сообщений.

У нас есть 3 таблицы:

1. User. Хранит имя пользователя и его некий рейтинг (не важно откуда он берется и как считается)

2. Message. Хранит данные о сообщении Имя пользователя, ДатуВремя, Текст сообщения.

3. Task. Задача на формирование отчета, которую создает заказчик. Хранит ID, Статус задачи (выполнено или нет), и два параметра: Дату сообщения начало, Дату сообщения конец.

Состав колонок будет следующим:

В Excel Заказчик хочет видеть 4 колонки 1) message_date. 2) name. 3) rating. 4) text. Ограничение по количеству строк 1 млн. Надо заполнить этими данными excel, а дальше заказчик уже будет работать с этими данными в екселе самостоятельно.

2. Задача понятна, начнем поиск решения

Так как в компании все стараются придерживаться единого стиля в разработке приложений, то и мне пришлось начать с самого обычного подхода, который используется во всех остальных микросервисах это Spring + Hibernate для запуска приложения и работы с БД. В качестве БД используется Oracle, хотя использование любой другой СУБД будет плюс минус похожим.

Для старта приложения нам понадобится зависимость spring-boot-starter-data-jpa, которая объединяет в себе сразу Spring Data, Hibernate и JPA, все это нам понадобится для удобства работы с БД и нашими сущностями.

        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-data-jpa</artifactId>            <version>2.4.5</version>        </dependency>

Для тестирования добавим spring-boot-starter-test

        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <scope>test</scope>        </dependency>

И еще нам нужен сам драйвер для подключения к БД

        <dependency>            <groupId>com.oracle.database.jdbc</groupId>            <artifactId>ojdbc10</artifactId>            <version>19.10.0.0</version>        </dependency>

Далее нам нужно добавить некоторые настройки конфигурации. У нас будет один метод, который будет ходить в таблицу TASK, искать задачу в статусе CREATED и, если такая задача существует, то запускать генерацию отчета с параметрами. Предполагается, что генерация отчета может быть долгой, поэтому наш метод будет запускаться по расписанию в два потока асинхронными процессами. Так же для Spring Data укажем наш репозиторий для поиска соответствующих сущностей. Класс конфигурации будет выглядеть следующим образом:

package com.report.generator.demo.config;import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.PropertySource;import org.springframework.data.jpa.repository.config.EnableJpaRepositories;import org.springframework.scheduling.TaskScheduler;import org.springframework.scheduling.annotation.EnableAsync;import org.springframework.scheduling.annotation.EnableScheduling;import org.springframework.scheduling.concurrent.ThreadPoolTaskScheduler;@Configuration@EnableScheduling@EnableAsync@EnableJpaRepositories(basePackages = "com.report.generator.demo.repository")@PropertySource({"classpath:application.properties"})@ConditionalOnProperty(    value = "app.scheduling.enable", havingValue = "true", matchIfMissing = true)public class DemoConfig {    private static final int CORE_POOL_SIZE = 2;    @Bean(name = "taskScheduler")    public TaskScheduler getTaskScheduler() {        ThreadPoolTaskScheduler scheduler = new ThreadPoolTaskScheduler();        scheduler.setPoolSize(CORE_POOL_SIZE);        scheduler.initialize();        return scheduler;    }}

Класс генерации отчетов содержит в себе @Scheduled метод, который раз в минуту ищет Task и, если находит, то запускает генерацию отчета с параметрами из этой таски.

    @Async("taskScheduler")    @Scheduled(fixedDelay = 60000)    public void scheduledTask() {        log.info("scheduledTask is started");        Task task = getTask();        if (Objects.isNull(task)) {            log.info("task not found");            return;        }        log.info("task found");        generate(task);    }

Класс стартер приложения не имеет ничего примечательного, весь код можно посмотреть на GitHub.

3. Выборка данных из БД

Т.к. в компании повсеместно используется Hibernate было решено использовать его. Добавлено entity MessageData с необходимым набором полей (id, name, rating, messageDate, test). Первой попыткой выбрать необходимые данные была попытка в лоб выгрузить все в List<Message> с помощью простого метода:

List<Message> findAllByMessageDateBetween(Instant dateFrom, Instant dateTo);

А дальше уже в цикле создавать объекты MessageData и обогащать их недостающими данными. Было очевидно, что данных подход в корне не верный и выгружать сразу миллион записей в List как минимум медленно. Но для эксперимента и замера скорости работы проверить хотелось, чтобы потом сравнить с другими вариантами. Но в результате данный набор записей выгружался около 30 минут после чего было получено OutOfMemoryError и на этом эксперимент завершился.

Даже если бы пользователь задал узкие рамки в параметрах и нам бы удалось выбрать все в один List, то дальше мы бы столкнулись со следующей проблемой для заполнения всех необходимых колонок нужно было бы собирать id пользователей, идти снова в базу, получать их имена и рейтинги, и заполнить уже с полными данными. Сложность такого алгоритма вырастала в разы. Было понятно, что выборку надо производить по частям и переложить все возможные действия с данными на сторону бд. Чтобы не выбирать все разом и, чтобы не городить велосипедов, было решено использовать ScrollableResults. Это позволяет нам получить ссылку на курсор и итерироваться по результатам с определенным шагом. Далее пришлось переписать запрос так, чтобы он возвращал сразу все необходимые данные уже после всех джойнов, объединений, группировок и т. д.

Следующий вопрос где хранить сам текст запроса. Это был не простая ситуация т.к. в действительности количество таблиц, которые участвовали в запросе было около десяти, количество джойнов и всяческих группировок было огромным, в результате чего текст запроса вышел на 200+ строк после ревью всевозможных коллег и утверждении самим тех лидом. Хранить такой запрос в java коде не хотелось, плюс в нем были захардкожены некоторые константы в условиях и светить ими в общем репозитории было бы неправильно. Для решения всех этих вопросов мне на помощь пришла идея использовать view. Весь текст запроса прекрасно туда вписывался, плюс на выходе мы получаем готовую сущность, с которой может работать hibernate как с обычной entity.

По началу все выглядело нормально, запрос на выборку 1 млн таких строк выполнялся за разумные 10 мин. или около того. Немного больше, чем хотелось бы, но заказчика это устраивало. Однако в процессе тестирования обнаружился серьезный минус такого подхода когда мы выбираем 1 млн записей, запрос выполняется 10 минут, но когда мы хотим отчет по короче и указываем в параметрах границы даты поуже у нас запрос так же выполняется 10 минут, но в результате мы можем получить хоть 1 запись, хоть миллион. Суть в том, что внутрь запроса view нельзя передавать параметры, мы можем только выполнить статический запрос и уже на результат наложить параметры. Поэтому не важно сколько будет в результате строк, в первую очередь будет выбрано все, что найдется в бд, а только потом будет применены параметры. Заказчику было все равно, его устраивало и то, что отчет с одной строкой будет формироваться практически за такое же время, что и отчет с 1 млн строк. Однако это излишне нагружало бд и было решено отказаться от этого варианта.

Оставался всего один вариант, который нам подходил это хранимая в бд функция. В нее можно передавать параметры, она может вернуть ссылку на курсор и ее результат можно удобно маппить на нашу entity. Таким образом была описана функция, которая принимала на вход несколько параметров, и возвращала sys_refcursor, весь скрипт занял около 300 строк в реальности, а в упрощенном варианте здесь она выглядит так:

create function message_ref(    date_from timestamp,    date_to timestamp) return sys_refcursor as    ret_cursor sys_refcursor;begin    open ret_cursor for        select m.id,               u.name,               u.rating,               m.message_date,               m.text        from message m                 left join users u on m.user_id = u.id        where m.message_date between date_from and date_to;    return ret_cursor;end message_ref;

Теперь как ее использовать? Для этого отлично подходит @NamedNativeQuery. Запрос для вызова функции выглядит следующим образом: "{ ? = call message_ref(?, ?) }", callable = true дает понять, что запрос представляет собой вызов функции, cacheMode = CacheModeType.IGNORE для указания не использовать кэш, т. к. скорость работы нам не так критична, как затрачиваемая память, ну и в конце resultClass = MessageData.class для маппинга результата на нашу entity. Класс MessageData выглядит следующим образом:

package com.report.generator.demo.repository.entity;import lombok.Data;import org.hibernate.annotations.CacheModeType;import org.hibernate.annotations.NamedNativeQuery;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.Id;import java.io.Serializable;import java.time.Instant;import static com.report.generator.demo.repository.entity.MessageData.MESSAGE_REF_QUERY_NAME;@Data@Entity@NamedNativeQuery(    name = MESSAGE_REF_QUERY_NAME,    query = "{ ? = call message_ref(?, ?) }",    callable = true,    cacheMode = CacheModeType.IGNORE,    resultClass = MessageData.class)public class MessageData implements Serializable {    public static final String MESSAGE_REF_QUERY_NAME = "MessageData.callMessageRef";    private static final long serialVersionUID = -6780765638993961105L;    @Id    private long id;    @Column    private String name;    @Column    private int rating;    @Column(name = "MESSAGE_DATE")    private Instant messageDate;    @Column    private String text;}

Для того чтобы не использовать кэш было решено выполнять запрос в StatelessSession. Однако есть важная особенность: если попытаться вызвать namedQuery то hibernate при попытке установить CacheMode выдаст UnsupportedOperationException. Чтобы этого избежать необходимо установить два хинта:

            query.setHint(JPA_SHARED_CACHE_STORE_MODE, null);            query.setHint(JPA_SHARED_CACHE_RETRIEVE_MODE, null);

В итоге наш метод генерации имеет следующий вид:

 @Transactional    void generate(Task task) {        log.info("generating report is started");        try (            StatelessSession statelessSession = sessionFactory.openStatelessSession()        ) {            ReportExcelStreamWriter writer = new ReportExcelStreamWriter();            Query<MessageData> query = statelessSession.createNamedQuery(MESSAGE_REF_QUERY_NAME, MessageData.class);            query.setParameter(1, task.getDateFrom());            query.setParameter(2, task.getDateTo());            query.setHint(JPA_SHARED_CACHE_STORE_MODE, null);            query.setHint(JPA_SHARED_CACHE_RETRIEVE_MODE, null);            ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);            int index = 0;            while (results.next()) {                index++;                writer.createRow(index, (MessageData) results.get(0));                if (index % 100000 == 0) {                    log.info("progress {} rows", index);                }            }            writer.writeWorkbook();            task.setStatus(DONE.toString());            log.info("task {} complete", task);        } catch (Exception e) {            task.setStatus(FAIL.toString());            e.printStackTrace();            log.error("an error occurred with message {}. While executing the task {}", e.getMessage(), task);        } finally {            taskRepository.save(task);        }    }

4. Запись данных в Excel

На данном этапе вопрос с выборкой данных из БД был решен и возник следующий вопрос как теперь все это писать в excel так, чтобы это было быстро и не затратно по памяти. Первая попытка была самой очевидной это использование библиотеки org.apache.poi. Тут все просто: подключаем зависимость

        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>5.0.0</version>        </dependency>

Создаем XSSFWorkbook далее XSSFSheet, из него уже row и так далее. Ничего примечательного, примерный код ниже:

package com.report.generator.demo.service;import com.report.generator.demo.repository.entity.MessageData;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;import java.time.Instant;public class ReportExcelWriter {    private final XSSFWorkbook wb;    private final XSSFSheet sheet;    public ReportExcelWriter() {        this.wb = new XSSFWorkbook();        this.sheet = wb.createSheet();        createTitle();    }    public void createRow(int index, MessageData data) {        XSSFRow row = sheet.createRow(index);        setCellValue(row.createCell(0), data.getMessageDate());        setCellValue(row.createCell(1), data.getName());        setCellValue(row.createCell(2), data.getRating());        setCellValue(row.createCell(3), data.getText());    }    public void writeWorkbook() throws IOException {        FileOutputStream fileOut = new FileOutputStream(Instant.now().getEpochSecond() + ".xlsx");        wb.write(fileOut);        fileOut.close();    }    private void createTitle() {        XSSFRow rowTitle = sheet.createRow(0);        setCellValue(rowTitle.createCell(0), "Date");        setCellValue(rowTitle.createCell(1), "Name");        setCellValue(rowTitle.createCell(2), "Rating");        setCellValue(rowTitle.createCell(3), "Text");    }    private void setCellValue(XSSFCell cell, String value) {        cell.setCellValue(value);    }    private void setCellValue(XSSFCell cell, long value) {        cell.setCellValue(value);    }    private void setCellValue(XSSFCell cell, Instant value) {        cell.setCellValue(value.toString());    }}

Но такой подход оказался не очень оптимальным. Примерно 3 минуты потребовалось на выборку 1 млн строк из бд и запись их в excel. И в итоге приводил к OutOfMemoryError. Вот пример:

А когда я выполнял его на терминалке с выделенной оперативной памятью в 2Gb, то падал он с OutOfMemoryError примерно на 30% прогресса.

Грузить весь миллион строк в память в excel было так же плохой идеей, как и выгружать весь запрос в List, очевидно, здесь надо было использовать некий stream, но хоть какой-то годный пример google тогда мне не дал. Была попытка написать свое подобие I/O Stream для работы с excel, но мысль о том, что я пишу велосипед не давала мне покоя. В результате я стал изучать библиотеку org.apache.poi пристальней и оказалось, что там уже есть пакет streaming. В этом пакете уже есть весь необходимый набор классов для работы с большим объемом данных в excel. Оставалось только заменить все ключевые классы на аналогичные из пакета streaming и все:

package com.report.generator.demo.service;import com.report.generator.demo.repository.entity.MessageData;import org.apache.poi.xssf.streaming.SXSSFCell;import org.apache.poi.xssf.streaming.SXSSFRow;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;import java.time.Instant;public class ReportExcelStreamWriter {    private final SXSSFWorkbook wb;    private final SXSSFSheet sheet;    public ReportExcelStreamWriter() {        this.wb = new SXSSFWorkbook();        this.sheet = wb.createSheet();        createTitle();    }    public void createRow(int index, MessageData data) {        SXSSFRow row = sheet.createRow(index);        setCellValue(row.createCell(0), data.getMessageDate());        setCellValue(row.createCell(1), data.getName());        setCellValue(row.createCell(2), data.getRating());        setCellValue(row.createCell(3), data.getText());    }    public void writeWorkbook() throws IOException {        FileOutputStream fileOut = new FileOutputStream(Instant.now().getEpochSecond() + ".xlsx");        wb.write(fileOut);        fileOut.close();    }    private void createTitle() {        SXSSFRow rowTitle = sheet.createRow(0);        setCellValue(rowTitle.createCell(0), "Date");        setCellValue(rowTitle.createCell(1), "Name");        setCellValue(rowTitle.createCell(2), "Rating");        setCellValue(rowTitle.createCell(3), "Text");    }    private void setCellValue(SXSSFCell cell, String value) {        cell.setCellValue(value);    }    private void setCellValue(SXSSFCell cell, long value) {        cell.setCellValue(value);    }    private void setCellValue(SXSSFCell cell, Instant value) {        cell.setCellValue(value.toString());    }}

Теперь сравним скорость обработки данных с этой библиотекой:

Вся обработка заняла пол минуты и, самое главное, никаких OutOfMemoryError.

5. Итог

В результате удалось добиться максимальной производительности за счет использования хранимой функции, StatelessSession, ScrollableResults и использования библиотеки org.apache.poi из пакета streaming. При большом желании можно улучшить производительность еще, если написать все на чистом jdbc, может быть есть еще варианты, как, что и где можно улучшить. Буду рад услышать комментарии от более опытных в этом экспертов. В данном примере не учтено ограничение на 1 млн. строк, т. к. это простая формальность и для примера не очень важна. Для наполнения БД тестовыми данными был добавлен тестовый класс DemoApplicationTests. Весь код можно посмотреть в репозитории на GitHub.

Правила. Мы знаем это слово с самого детства. Сначала родители учат нас, как нужно правильно поступать, потом мы приходим в школу, где учителя диктуют свои порядки. В университете мы вновь сталкиваемся с правилами, которые для нас устанавливают преподаватели и деканат. По мере взросления мы начинаем следовать законам, которые разработаны государством. Что общего у всех этих правил? То, что они выверены годами, десятилетиями, а некоторые даже и поколениями. Представьте, какой была бы наша жизнь, если бы правила менялись часто: каждую неделю или каждый день!

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

Что это вообще такое?

Довольно часто в процессе разработки коммерческого софта мы сталкиваемся с проблемой, когда некоторую логику расчета необходимо передать на сторону заказчика. Зачастую представитель этого заказчика не хочет (или не может) использовать какой-либо язык программирования для описания необходимой логики работы приложения. В таких случаях на помощь приходят BRMS или Business Rule Management System. Это информационная система для создания, управления и исполнения той самой
бизнес-логики приложения, её также называют бизнес-правилами. Обычно такие системы состоят из сервера, на котором происходит выполнение правил - это юрисдикция программистов, и средств ведения самих правил - это уже зона ответственности бизнеса.

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

BRMS фреймворков на рынке достаточно много. Свои решения предлагают многие крупные компании: IBM, Red Hat, Agiloft, SAS и даже Bosch. Все они либо платные, либо не подходили нам по разным критериям. Мы решили начать с уже зарекомендовавшей себя системы JBoss Drools. Она достаточно надежная, проверенная временем, используется в банковских решениях, ритейле и телекоме, а также предоставляет возможность ведения бизнес-правил как на специальном языке DRL, так и с помощью Excel-таблиц. Существует также и несколько UI-решений для ведения правил. Так как наши аналитики используют единую систему ведения справочников и нам удобнее хранить правила там, от использования UI мы отказались в пользу понятных бизнесу Excel-таблиц.

Что же такое бизнес-правило и как оно выглядит?

Обычно под бизнес-правилом понимается набор инструкций или ограничений, которые позволяют принимать решение о выполнении того или иного действия. Другими словами, это некий набор логики, который позволяет в зависимости от вариации входных значений выполнять определенные действия, соответствующие входным параметрам. Давайте рассмотрим простой пример.

У нас есть респондент, который обладает характеристикойГендерная идентичность (gender). В зависимости от значения этой характеристики респонденту выставляетсяПол (SEX).То есть, еслиgender = male, то значению свойства Пол нужно будет поставить1. В противном случае это будет2. На языке DRL это правило будет выглядеть следующим образом:

rule "Rule 1 Example 1"      when         $s: Respondent($s.gender == "male")      then         $s.addResult("SEX", "1");end rule "Rule 2 Example 2"      when         $s: Respondent($s.gender == "female")      then         $s.addResult("SEX", "2"); end

Таким образом, мы имеем некий синтаксис, очень похожий на языкGherkin. В нашем случае у нас есть два правила, которые состоят из условия (указанного после ключевого словаwhen) и результата, которой будет получен в ходе выполнения этого условия (идет после словаthen).Условия в терминологииDroolsпринято называтьLeftHandSide(илиLHS), а действия -RightHandSide(илиRHS). Стоит упомянуть еще об одном объекте:Respondent.Этоfact,то есть объект в текущей памятиDrools, над которым будут производиться те или иные преобразования. В нашем случае у этого объекта присутствуют свойстваgenderиresult. Для того, чтобы было удобнее работать с объектами,frameworkпредусматривает возможность введения переменных. Обычно переменные начинаются с символа$.

Вот так это правило будет выглядеть на языкеExcel-таблиц:

Как создать таблицу правил?

Прежде, чем мы перейдем к описанию основных конструкций, используемых в таблицах, необходимо разобраться, как правильно формировать эту таблицу. Стоит отметить, чтоDroolsумеет работать как с таблицами, созданными в табличных редакторахMiscrosoftExcelилиOpenOffice, так и с форматомCSV. Перед применением правил они будут сконвертированы из табличной формы вDRL-формат.

В любой таблице правил можно выделить две области: область настройки правил и область описания правил. Первую можно узнать по зарезервированному словуRuleSet, тогда как вторая этоRuleTable. Обратите внимание, что все зарезервированные чувствительны к регистру.

В области настройки правил указываются основные конструкцииDRL-формата и атрибуты самих правил. Обычно это пара - зарезервированное слово в левой ячейке и значение в правой. Полный список зарезервированных слов можно найти в документации. Вот часть из них:

• RuleSet здесь указывается имя пакета для сгенерированного файлаDRL. Этот параметр обязательно должен стоять первым

• Import через запятую указываются факты с указанием пакета, а также такиеJava-классы, которые можно использовать в расчетах, например,java.lang.Math

• Functions здесь можно описывать функции, которые будут работать в рамках данногоRuleSetа. Функции должны соответствоватьDRL-синтаксису.

В области описания правил тоже есть свои зарезервированные слова, при этом главным словом являетсяRuleTable, которое указывает на то, что таблица ниже соответствует таблице правил, которую движокDroolsдолжен преобразовать вDRL-синтаксис. Опционально можно указать название для таблицы правил. В нашем случае этоnameforRuleTable.

Начиная со следующей строки идут колонки:

• NAME имя правила. Его можно не указывать.

• DESCRIPTION расшифровка для правила. Его тоже можно не указывать. Эти два параметра нужны для того, чтобы не потеряться в большом количестве правил.

• CONDITION это тотLeftHandSideили указание условия, на основании которого будет выполненACTION. Этот параметр обязателен.

• ACTION действие, которое необходимо применить к факту. В нашем случае это методaddResult, который добавляет вMapзначения результирующих переменных. Этот параметр обязателен. В блокеACTIONвыполняетсяJava-код, поэтому наличие точки с запятой здесь также обязательно. Также через точку с запятой можно указать сколько угодно методов.

  Следует отметить, что количествоCONDITIONиACTIONможет быть больше одного.

Как отмечалось ранее, строкой ниже идет присваивание переменной$sфактаRespondent. Отмечу, что его полное имя с названием пакета, в котором он находится, нужно обязательно указать в параметреImport. Если у нас несколькоCONDITIONотносятся к одному факту, тогда ячейки с фактом необходимо объединить в одну. Также мы можем работать с разными фактами в разныхCONDITION-колонках: для этого мы просто указываем новый факт и новую переменную, не забыв добавить его вImport.

Далее, на следующей строке, у нас идет описание самих правил в колонкеCONDITIONи действий в колонкеACTION, которые необходимо выполнить. Какие могут быть условия и действия к выполнению, я описал ниже. А пока перейдем к следующей строке. Это строка заголовков полей (Text-Parameter-Resultна картинке). Ее указывать обязательно. В противном случае те переменные/условия, которые будут указаны вместо этой строки,Droolsпроигнорирует. Далее у нас идет указание самих условий.

Какие могут быть условия?

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

Для начала стоит упомянуть еще раз, что все манипуляции мы будем производить над объектомRespondent. В нашей терминологии респондент это лицо, которое принимает участие в исследовании. У каждого респондента есть свой набор свойств (например, гендерная принадлежность, рассмотренная ранее). Для того, чтобы показать все многообразие условий, с которыми работаетDrools, я вынес каждое свойство в отдельное поле классаRespondent. Для простоты понимания, в качестве еще одного поля класса добавляемMap<String,String>result, в котором будем собирать все результирующие переменные. Таким образом, классRespondentбудет выглядеть так:

public class Respondent {    public int id;    public String gender;    public Boolean isActive;    public Integer age;    public List<String> pets;    public String city;    public List<String> devices;    public Map<String, String> properties;    public Car car;    public MobileBrand mobileBrand;    public Household household;    public Map<String, String> result = new HashMap<>();    public void addResult(String key, String value) {        result.put(key, value);    }//  getters, setters, constructor}

Самое простое правило, по определению гендерной принадлежности, мы уже рассмотрели. А что, если нам нужно определить половой признак только у тех респондентов, которые участвуют в исследовании? Для фильтрации таких респондентов мы будет использовать булеву переменнуюisActive.Значениеtrue респондент участвует в исследовании,falseнет.

На рисунке выше показано, как мы объединили два условия: активность респондента и его половой признак. Не трудно догадаться, что в таком случае происходит объединение правил, находящихся в одной строке, по логическомуИ. Два условия объединены одним фактомRespondent. Вот так выглядит описание правила на языкеDRL:

rule "name_for_RuleTable_20"when$s: Respondent(isActive == true, gender == "male")then$s.addResult("SEX", "M");endrule "name_for_RuleTable_21"when$s: Respondent(isActive == true, gender == "female")then$s.addResult("SEX", "F");end

Стоит отметить использование параметра$param-этот параметр работает в рамках одного столбца и во время компиляции правил он будет заменен на конкретное значение из ячейки. То есть условиеisActive== $paramбудет преобразовано вisActive==true. В случае работы с булевыми или целочисленными переменными экранировать его не надо. ДвижокDroolsпонимает, что это не строка. В случае работы со строками экранирование обязательно, как показано в примере со свойствомgender.

В социологических исследованиях часто разбивают респондентов на половозрастные группы. Следующий пример как раз демонстрирует такое разбиение:

Данное правило производит разбивку активных респондентов по половому признаку, а также на две возрастные категории - до 17 лет включительно и после 18 лет. Повторюсь, в случае работы с числами экранирующие кавычки не требуются.

Еще один пример, где кавычки не требуются это перечисления. Допустим, каждый из респондентов обладает мобильным телефоном. В зависимости от того, какого бренда телефон, мы запишем в результирующую переменнуюPHONE_SALES_PERзначение процента продаж на мировом рынке мобильных устройств за 2019 год. Так как результирующий словарь в качестве значения принимает строку, то$paramнеобходимо экранировать. Также стоит отметить, чтоMobileBrandнеобходимо добавить в настройкуImportв области описания правил.

Иногда встречаются такие условия, когда одинаковое действие нужно применить к разным условиям. Такого поведения можно добиться, если в колонкеACTIONдублировать значения$param, то есть создавать отдельные правила с разными условиями и одинаковыми результатами. Но это будет загромождать нашу таблицу. Для таких случаев предусмотрена специальная конструкцияin:

В данном примере у нас будет два правила: если бренд мобильного телефона нашего респондента маркиSAMSUNGилиAPPLE, тогда в результирующую переменную мы запишем значениеPREMIUM. В случае, когда бренд телефонаHUAWEIилиXIAOMI, значение будетNOT_PREMIUM. (Прошу владельцев данных смартфонов меня извинить за то, что отнес ихкнепривилегированным :) ) Другими словами, мы выбираем значение из массива значений.

А если у нас обратная задача, когда мы имеем массив значений и при вхождении конкретного значения выполняется действие? В подобном случае мы используем конструкциюcontains. Эта конструкция работает со всеми объектами интерфейсаjava.util.Collection. В нашем примере у объектаRespondentесть коллекция домашних животныхpets.Мы сделали выборку тех респондентов, которые не достигли совершеннолетия и указали в своих ответах в качестве домашнего животного кошку или собаку. Третье правило сработает в том случае, если в семье есть и собака, и кошка. Так как домашнее животное это строковая переменная, то переменную$param мы заключаем в кавычки.

В следующих двух примерах мы рассмотрим еще один оператор работы с коллекциями:forall(<оператор>){<условие>}.В первом примере правило сработает в случае выполнения условия в фигурных скобках и хотя бы одного из перечисленных через запятую параметров в ячейке. Во втором примере должно выполниться условие в фигурных скобках со всеми параметрами, перечисленными через запятую в соответствующей ячейке. Другими словами, в первом примере объединение условий происходит через логическоеИЛИ, тогда как во втором черезИ. Это условие и указывается в фигурных скобках. Важно отметить, что переменная, которая будет обращаться к значению ячейки, в данном случае указывается без словаparam. Еще один интересный момент, который я хотел бы отметить в примерах ниже это отсутствие ссылки на факт ($s). Дело в том, что если мы работаем с одним фактом, тоDroolsпонимает, что мы обращаемся полям класса-факта (cityиdevices), поэтому указание переменной в описанииCONDITIONможно опустить.

Последнее, что хотелось бы рассмотреть при работе с коллекциями это количество значений. Обращение к количеству элементов происходит через функциюsize:

Когда у объекта-факта много вложенных свойств, это немного затрудняет понимание объекта, а также работу с ним. Поэтому мы стараемся все свойства объекта привести к словарю с парой ключ-значение.Droolsимеет удобный механизм обращения к значениям словаря по ключу:

В нашей практике мы часто получаем значение с древовидными структурами, когда объект имеет вложенные классы со своими наборами свойств. Предлагаю разобрать несколько таких примеров. Допустим, у каждого респондента есть вложенный объектCarcнабором свойств: порядковый номер, марка автомобиля, модель и год выпуска. И, к примеру, нам необходимо категоризировать автомобиль по году выпуска. Для того, чтобы обратиться к объекту в родительском объекте-факте, необходимо явно указать новую переменную и вложенный объект. В ячейке описания условия нужно сослаться на этот дочерний объект с помощью ключевого словаfrom:

Достаточно удобно объединять вложенные объекты и их наборы свойств, которые представлены в виде словаря. В примере, описанном ниже, у объектаCar,который является частью фактаRespondent,естьMap<String,String>properties с набором свойств. В данном наборе правил мы обращаемся к свойствуPOWER. Также в описании условий правил присутствует функцияDroolsgetValue. Ее реализацию необходимо вынести в блокFunctions в области настройки правил:

И само правило:

В заключение описания возможных реализаций условий хотелось бы показать комплексный пример использования словаря во вложенном объекте, где в качестве значения по ключу используется массив данных. Условие задачи звучит следующим образом: необходимо отобрать тех респондентов, у которых в домохозяйстве есть приставкаPlayStation. Другими словами, у дочернего объектаHouseholdфактаRespondentнеобходимо найти свойствоTVDevicesи проверить наличие приставкиPlayStation. Правило будет выглядеть следующим образом:

Как все это применять?

Все описанные выше условия мы можем комбинировать различным образом между собой или создавать длинные цепочки правил через логическое объединение. Но как мы можем эффективно использовать блокACTION? Как я уже отмечал, в этом блоке мы можем воспользоваться всем арсеналом возможностейJava. В описанных примерах я использовал достаточно тривиальный метод добавления значения в словарь. Очень часто, особенно для отладки правил, удобно использовать вACTIONлогирование или вывод на консоль.

Но есть и ряд зарезервированных выраженийDrools, которые позволяют сделать расчет правил еще гибче:

• setустанавливает значение свойства факта, но не уведомляет движокDroolsоб этом;

• updateуведомляет движок о том, что факт был изменен посредством одного или несколькихset;

• modifyэто некая сумма двух предыдущих действий, здесь происходит установка одного или нескольких свойств факта, а также уведомляется движок об изменении факта;

• insertдобавление нового факта.

Приведу пример наиболее часто используемого выраженияmodify.

В первомRuleTableмы производим отбор тех активных респондентов, у которых по каким-то причинам возраст больше 99 лет и меньше 0. Если такие респонденты найдены, то выражение изменит факт$r, то естьRespondentaи установит ему значениеfalse.В следующемRuleTableу нас будет уже измененное свойствоisActiveи неактивным респондентам в свойствеREJECTEDзапишется значениеTRUE.

В заключение

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

Информацию по запускуDroolsи работе с ним можно с легкостью найти в интернете или же форкайте мой проект с примерами на гитлаб (https://github.com/sxexesx/drools-decision-table).

Спасибо за внимание! Надеюсь, данная статья окажется полезна тем, кто хочет погрузиться в удивительный мирBRMS!