Макрос для выгрузки данных из excel
В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений Python и MS Excel. Данные вопрос может возникнуть, например, при создании какой-либо системы онлайн отчетности, которая должна выгружать результаты в общепринятый формат ну или какие-либо другие задачи. Также в статье я покажу и обратную интеграцию, т.е. как использовать функцию написанную на python в Excel, что также может быть полезно для автоматизации отчетов.
Работаем с файлами MS Excel на Python
Для работы с Excel файлами из Python мне известны 2 варианта:
Использование библиотек
Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы c xlrd, xlwt, xlutils. Поэтому в данном материале я приведу небольшой кусок кода с их использованием.
Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
нужный лист с данными:
Теперь давайте посмотрим, как считать значения из нужных ячеек:
Как видно чтение данных не составляет труда. Теперь запишем их в другой файл. Для этого создам новый excel файл с новой рабочей книгой:
Запишем в новый файл полученные ранее данные и сохраним изменения:
Из примера выше видно, что библиотека xlrd отвечает за чтение данных, а xlwt — за запись, поэтому нет возможности внести изменения в уже созданную книгу без ее копирования в новую. Кроме этого указанные библиотеки работают только с файлами формата xls (Excel 2003) и у них нет поддержки нового формата xlsx (Excel 2007 и выше).
Чтобы успешно работать с форматом xlsx, понадобится библиотека openpyxl. Для демонстрации ее работы проделаем действия, которые были показаны для предыдущих библиотек.
Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:
Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:
Отличие от прошлых библиотек в том, что openpyxl дает возможность отображаться к ячейкам и последовательностям через их имена, что довольно удобно и понятно при чтении программы.
Теперь посмотрим как нам произвести запись и сохранить данные:
Из примера видно, что запись, тоже производится довольно легко. Кроме того, в коде выше, можно заметить, что openpyxl кроме имен ячеек может работать и с их индексами.
К недостаткам данной библиотеки можно отнести, то что, как и в предыдущем примере, нет возможности сохранить изменения без создания новой книги.
Как было показано выше, для более менее полноценной работы с excel файлами, в данном случае, нужно 4 библиотеки, и это не всегда удобно. Кроме этого, возможно нужен будет доступ к VBA (допустим для какой-либо последующей обработки) и с помощью этих библиотек его не получить.
Однако, работа с этими библиотеками достаточно проста и удобна для быстрого создания Excel файлов их форматирования, но если Вам надо больше возможностей, то следующий подпункт для Вас.
Работа с com-объектом
В своих отчетах я предпочитаю использовать второй способ, а именно использование файла Excel через com-объект с использованием библиотеки win32com. Его преимуществом, является то, что вы можете выполнять с файлом все операции, которые позволяет делать обычный Excel с использованием VBA.
Проиллюстрируем это на той же задаче, что и предыдущие примеры.
Для начала загрузим нужную библиотеку и создадим COM объект.
Теперь мы можем работать с помощью объекта Excel мы можем получить доступ ко всем возможностям VBA. Давайте, для начала, откроем любую книгу и выберем активный лист. Это можно сделать так:
Давайте получим значение первой ячейки и последовательности:
Как можно заметить, мы оперируем здесь функциями чистого VBA. Это очень удобно если у вас есть написанные макросы и вы хотите использовать их при работе с Python при минимальных затратах на переделку кода.
Посмотрим, как можно произвести запись полученных значений:
Из примера видно, что данные операции тоже довольно просто реализовываются. Кроме этого, можно заметить, что изменения мы сохранили в той же книге, которую открыли для чтения, что достаточно удобно.
Однако, внимательный читатель, обратит внимание на переменную i, которая инициализируется не 0, как принято python, а 1. Это связано с тем, что мы работаем с индексами ячеек как из VBA, а там нумерация начинается не с 0, а с 1.
На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.
Вызываем функции Python из MS Excel
Может возникнуть такая ситуация, что у вас уже есть какой-либо функция, которая обрабатывает данные на python, и нужно перенести ее функциональность в Excel. Конечно же можно переписать ее на VBA, но зачем?
Для использования функций python в Excel есть прекрасная надстройка ExcelPython. С ее помощью вы сможете вызывать функции написанные на python прямо из Excel, правда придется еще написать небольшую обертку на VBA, и все это будет показано ниже.
Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:
На вход ей подается список, состоящий из списков, это одно из условий, которое должно выполняться для работы данной функции в Excel.
Сохраним функцию в файле plugin.py и положим его в ту же директорию, где будет лежать наш excel файл, с которым мы будем работать.
Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.
Когда все приготовления выполнены, открываем тестовый файл excel и вызовем редактор VBA (Alt+F11). Для работы с вышеуказанной надстройкой необходимо ее подключить, через Tools->References, как показано на рисунке:
Ну что же, теперь можно приступить к написанию функции-обертки для нашего Python-модуля plugin.py. Выглядеть она будет следующим образом:
Итак, что же происходит в данной функции?
Для начала, с помощью PyModule , мы подключаем нужный модуль. Для этого в качестве параметров ей передается имя модуля без расширения, и путь до папки в которой он находится. На выходе работы PyModule мы получаем объект для работы с модулем.
Затем, с помощью PyCall , вызываем нужную нам функцию из указанного модуля. В качестве параметров PyCall получает следующее:
- Объект модуля, полученный на предыдущем шаге
- Имя вызываемой функции
- Параметры, передаваемые функции (передаются в виде списка)
Теперь, чтобы убедиться в работоспособности нашей связки, вызовем нашу свежеиспеченую функцию на листе в Excel:
Как видно из рисунка все отработало правильно.
Надо отметить, что в данном материале используется старая версия ExcelPython, и на GitHub'e автора доступна новая версия.
Заключение
В качестве заключения, надо отметить, примеры в данной статье самые простые и для более глубоко изучения данных методов, я рекомендую обратиться к
документации по нужным пакетам.
Также хочу заметить, что указанные пакеты не являются единственными и в статье опущено рассмотрение, таких пакетов как xlsxwriter для генерации excel файлов или xlwings, который может работать с Excel файлами «на лету», а также же PyXLL, который выполняет аналогичные функции ExcelPython.
Кроме этого в статье я попытался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на форумах и думаю некоторым будет полезно иметь, такую «шпаргалку» под рукой.
Добрый вечер форумчане!
Нужна Ваша помощь: имеются 2 файла - основной и файл-выгрузка из системы. В основном файле есть лист, в который из файла-выгрузки нужно копировать определенные столбцы. Суть вопроса следующая - файл-выгрузка содержит достаточно много столбцов из которых для обновления данных в основном файле нужно всего лишь 4 столбца. И если, например эту выгрузку сделать сегодня, порядок столбцов может быть один, а если сделать, например ее завтра - порядок столбцов может быть совершенно другим. При этом названия остаются без изменений, меняется только их (столбцов) порядок. Можно для избавления от рутины как-то оптимизировать этот процесс через макрос, т.е. копировать данные из второго файла опираясь только на названия столбцов, а не на их порядок в книге? И желательно, чтобы при копировании из 2го файла, этот файл не открывался?
Сейчас копирование информации из файла в файл через vba работает, но работает при условии, что порядок столбцов всегда одинаковый и 2й файл при копировании открывается.
Заранее спасибо
Добрый вечер форумчане!
Нужна Ваша помощь: имеются 2 файла - основной и файл-выгрузка из системы. В основном файле есть лист, в который из файла-выгрузки нужно копировать определенные столбцы. Суть вопроса следующая - файл-выгрузка содержит достаточно много столбцов из которых для обновления данных в основном файле нужно всего лишь 4 столбца. И если, например эту выгрузку сделать сегодня, порядок столбцов может быть один, а если сделать, например ее завтра - порядок столбцов может быть совершенно другим. При этом названия остаются без изменений, меняется только их (столбцов) порядок. Можно для избавления от рутины как-то оптимизировать этот процесс через макрос, т.е. копировать данные из второго файла опираясь только на названия столбцов, а не на их порядок в книге? И желательно, чтобы при копировании из 2го файла, этот файл не открывался?
Сейчас копирование информации из файла в файл через vba работает, но работает при условии, что порядок столбцов всегда одинаковый и 2й файл при копировании открывается.
Заранее спасибо Maxxxis
Добрый день!
У меня следующая проблема, которую ни как не могу решить: имеется некий долгосрочный план работ нескольких сотрудников на нескольких листах. Необходимо проводить выгрузку информации из всех листов (их 5-10) на новый лист с фильтрацией по фамилии сотрудника и промежутку работ (в течении месяца). Предполагаю, что это можно сделать через макросы, но я в этом не разбираюсь совсем.
Спасибо
Добрый день!
У меня следующая проблема, которую ни как не могу решить: имеется некий долгосрочный план работ нескольких сотрудников на нескольких листах. Необходимо проводить выгрузку информации из всех листов (их 5-10) на новый лист с фильтрацией по фамилии сотрудника и промежутку работ (в течении месяца). Предполагаю, что это можно сделать через макросы, но я в этом не разбираюсь совсем.
Спасибо lyksor25
Может кто-то предложит решить проблему без макросов, если нет - пойду в раздел VBA.
[moder]Не нужно никуда ходить. Решайте вопрос здесь
Может кто-то предложит решить проблему без макросов, если нет - пойду в раздел VBA.
[moder]Не нужно никуда ходить. Решайте вопрос здесь lyksor25
[offtop]Я, конечно, дико извиняюсь, а можно вопрос: многолистовая структура книги (да еще и с переменным количеством листов, как я понял) - это "священная корова"? Просто так достали на работе эти 100-листовые книги, доставшиеся в "наследство", что как красная тряпка просто .
Плоская структура, "умные" и сводные таблицы - рулят .
А так, макросами решается на 100%, формулами - мэйби, но не уверен
Сорри за оффтоп
[offtop]Я, конечно, дико извиняюсь, а можно вопрос: многолистовая структура книги (да еще и с переменным количеством листов, как я понял) - это "священная корова"? Просто так достали на работе эти 100-листовые книги, доставшиеся в "наследство", что как красная тряпка просто .
Плоская структура, "умные" и сводные таблицы - рулят .
А так, макросами решается на 100%, формулами - мэйби, но не уверен
Сорри за оффтоп abtextime
Плоская структура, "умные" и сводные таблицы - рулят .
А так, макросами решается на 100%, формулами - мэйби, но не уверен
Сорри за оффтоп Автор - abtextime
Дата добавления - 12.01.2016 в 12:44
lyksor25, добрый день,вот макрос с выгрузкой данных на лист Лист1(его надо добавить в книгу)
,-в файл примере,кнопка yyy на листе 1(надо сжать, загружу чуть позже),вызов макроса с Вашего листа 1.
Sub yyy()
Dim z(), i&, i1&, i2&, j&, m&
i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row
z = Sheets("1").Range("A1:G" & i1).Value
m = 3
For i = 2 To UBound(z)
If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then
m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next
End If
Next
Sheets("Лист1").Range("A1").Resize(m, UBound(z, 2)).Value = z
Sheets("Лист1").Columns("A:G").AutoFit
Sheets("Лист1").Range("A3:G3").Merge
i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row
z = Sheets("2").Range("A3:G" & i1).Value
m = 1
For i = 2 To UBound(z)
If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then
m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next
End If
Next
i2 = Sheets("Лист1").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row - 1
Sheets("Лист1").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z
Sheets("Лист1").Range("A6:G6").Merge
End Sub
lyksor25, добрый день,вот макрос с выгрузкой данных на лист Лист1(его надо добавить в книгу)
,-в файл примере,кнопка yyy на листе 1(надо сжать, загружу чуть позже),вызов макроса с Вашего листа 1.
Sub yyy()
Dim z(), i&, i1&, i2&, j&, m&
i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row
z = Sheets("1").Range("A1:G" & i1).Value
m = 3
For i = 2 To UBound(z)
If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then
m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next
End If
Next
Sheets("Лист1").Range("A1").Resize(m, UBound(z, 2)).Value = z
Sheets("Лист1").Columns("A:G").AutoFit
Sheets("Лист1").Range("A3:G3").Merge
i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row
z = Sheets("2").Range("A3:G" & i1).Value
m = 1
For i = 2 To UBound(z)
If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then
m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next
End If
Next
i2 = Sheets("Лист1").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row - 1
Sheets("Лист1").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z
Sheets("Лист1").Range("A6:G6").Merge
End Sub
Sub yyy()
Dim z(), i&, i1&, i2&, j&, m&
i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row
z = Sheets("1").Range("A1:G" & i1).Value
m = 3
For i = 2 To UBound(z)
If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then
m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next
End If
Next
Sheets("Лист1").Range("A1").Resize(m, UBound(z, 2)).Value = z
Sheets("Лист1").Columns("A:G").AutoFit
Sheets("Лист1").Range("A3:G3").Merge
i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row
z = Sheets("2").Range("A3:G" & i1).Value
m = 1
For i = 2 To UBound(z)
If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then
m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next
End If
Next
i2 = Sheets("Лист1").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row - 1
Sheets("Лист1").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z
Sheets("Лист1").Range("A6:G6").Merge
End Sub
Часто при формировании прайс-листов требуется выгрузить большой объём данных в текстовый файл в формате CSV (разделитель - точка с запятой, или запятая)
И далеко не всегда может помочь сохранение файла в этом формате, поскольку в выгрузку попадают лишние данные (заголовки таблиц, лишние строки и столбцы, и т.д.)
В данном случае поможет экспорт заданного диапазона ячеек в файл CSV, что проще всего сделать макросом с использованием функции Range2CSV:
Вот код самой функции Range2CSV:
Улучшенная версия кода (работает заметно быстрее), и дополнительно заключает текст всех ячеек в кавычки:
Комментарии
Если нужно чтобы выводились данные до первой пустой ячейки первого столбца.:
После строки
If Len(Range2CSV) > 50000 Then buffer$ = buffer$ & Range2CSV: Range2CSV = ""
Добавляем строку:
If arr(i + 1, 1) = "" Then i = UBound(arr, 1) 'Если значение первой ячейки следующей строки массива пустое, то завершаем цикл
Автору спасибо! Много полезной инфы на сайте.
Спасибо огромное за помощь!
Доброго времени суток!
А как сделать,чтоб значения в ячейках выгружались в числовом или денежно формате с двумя знаками после запятой?
Вот например есть 4499795,40 выгружается 4499795,4 или 900 000,00 выгружается 900000
Всё хорошо. Только при импорте файла csv в престашоп возникает ошибка . С чем может быть связана?
спасибо! теперь запускается.
подскажите, почему таблица остается таблицей, а не переходит в вид столбца в выгружаемом файле?
при этом, если в строке
CSVtext$ = Range2CSV(ra, ";")
поменять точку с запятой на запятую, то таблица становится столбцом, но запятые в цифрах при этом путаются с запятыми-разделителями. если же поставить точку, то она будет путаться с точками в дате.
не понимаю, как работает эта последняя функция, поэтому не знаю, как это победить
В статье дана ссылка на функцию SaveTXTfile
Надо было код этой функции тоже вставить в ваш файл - и всё сразу заработает.
на SaveTXTfile пишет Sub or Function not defined
Ну так сделайте цикл, в котором будут просматриваться все строки таблицы.
Пример кода написать не могу, не видя ваш файл.
Если сами не справитесь - можете заказать разработку такого макроса (оформите заказ, прикрепив вашу таблицу, и разъяснив, что и куда должно выгружаться)
Подскажите, пожалуйста, как сделать, чтоб выгружать не сплошной диапазон, а только строки, у которых (например) в 11 колонке написано имя формируемого файла?
В идеале будет генерироваться несколько файлов, имена файлов которых прописаны в 11 колонке. В диапазоне возможно наличие строк, у которых значение равно цифре 0
Спасибо, за оперативный ответ.
В новом файле все заработало. Буду искать где были конфликты.
Отдельное спасибо за полезный ресурс.
Не должна эта строка выдавать ошибку, если только мой макрос не конфликтует с существующими в том же файле вашими макросами.
Попробуйте код на новом файле (в котором нет других макросов), и заново скопируйте необходимые макросы с моего сайта.
Если в новом файле проблема исчезнет - ищите проблему несовместимости в своих макросах.
В строке
txt = "": For j = LBound(arr, 2) To UBound(arr, 2): txt = txt & ColumnsSeparator$ & arr(i, j): Next j
Выделяет (txt =) с коментарием: Expected function or variable
Спасибо за оперативный ответ, попробуем
Ну почему же "код должен быть чисто своим"?
Я вот и чужой код часто использую - просто не удивляюсь, если он работает не совсем так, как хочелось бы, а дорабатываю его под свои нужды.
Я использовал функцию только для выгрузки диапазонов, заведомо не содержащих символа разделителя в обрабатываемых ячейках.
Если такие символы присутствуют - надо знать, как их экранировать.
Тут есть много вариантов:
- предварительно заменить разделитель в ячейке на какой-то другой символ
- заменить разделитель на другой символ только в выгрузке CSV (в ячейке оставить как было)
- экранировать сам символ разделителя (например, текст 123;456 заменить на 123";"456 или на 123/;456)
- экранировать всю ячейку в выгрузке (например, текст 123;456 заменить на "123;456")
- и т.д. и т.п. (при нескольких разделителях в ячейке появляются ещё варианты)
Предусматривать все эти варианты в макросе не очень хочется - объём кода заметно увеличится, а желающих внести какие-то дополнительные "навороты" только прибавится.
В вашем случае всё решалось заменой одной строки кода:
Надо вместо arr(i, j) подставить некую функцию, которая произведёт изменения текста в элементе массива при наличии в нём символов разделителей.
к сожалению не совсем корректно работает Function Range2CSV - если внутри ячейки содержится разделитель (у меня это была сложная гиперссылка) -исходнй ЦээСВэ искажается - при копипасте исохранить в формате csv -всё прекрасно
ну даже не знаю говорить ли спасибо? :) полдня промудохался
СПАСИБО - будет Муку наука - код должен быть чисто своим
Есть в IT-отрасли задачи, которые на фоне успехов в big data, machine learning, blockchain и прочих модных течений выглядят совершенно непривлекательно, но на протяжении десятков лет не перестают быть актуальными для целой армии разработчиков. Речь пойдёт о старой как мир задаче формирования и выгрузки Excel-документов, с которой сталкивался каждый, кто когда-либо писал приложения для бизнеса.
Какие возможности построения файлов Excel существуют в принципе?
- VBA-макросы. В наше время по соображениям безопасности идея использовать макросы чаще всего не подходит.
- Автоматизация Excel внешней программой через API. Требует наличия Excel на одной машине с программой, генерирующей Excel-отчёты. Во времена, когда клиенты были толстыми и писались в виде десктопных приложений Windows, такой способ годился (хотя не отличался скоростью и надёжностью), в нынешних реалиях это с трудом достижимый случай.
- Генерация XML-Excel-файла напрямую. Как известно, Excel поддерживает XML-формат сохранения документа, который потенциально можно сгенерировать/модифицировать с помощью любого средства работы с XML. Этот файл можно сохранить с расширением .xls, и хотя он, строго говоря, при этом не является xls-файлом, Excel его хорошо открывает. Такой подход довольно популярен, но к недостаткам следует отнести то, что всякое решение, основанное на прямом редактировании XML-Excel-формата, является одноразовым «хаком», лишенным общности.
- Наконец, возможна генерация Excel-файлов с использованием open source библиотек, из которых особо известна Apache POI. Разработчики Apache POI проделали титанический труд по reverse engineering бинарных форматов документов MS Office, и продолжают на протяжении многих лет поддерживать и развивать эту библиотеку. Результат этого reverse engineering-а, например, используется в Open Office для реализации сохранения документов в форматах, совместимых с MS Office.
Но у прямого использования Apache POI есть и недостатки. Во-первых, это Java-библиотека, и если ваше приложение написано не на одном из JVM-языков, вы ей вряд ли сможете воспользоваться. Во-вторых, это низкоуровневая библиотека, работающая с такими понятиями, как «ячейка», «колонка», «шрифт». Поэтому «в лоб» написанная процедура генерации документа быстро превращается в обильную «лапшу» трудночитаемого кода, где отсутствует разделение на модель данных и представление, трудно вносить изменения и вообще — боль и стыд. И прекрасный повод делегировать задачу самому неопытному программисту – пусть ковыряется.
Но всё может быть совершенно иначе. Проект Xylophone под лицензией LGPL, построенный на базе Apache POI, основан на идее, которая имеет примерно 15-летнюю историю. В проектах, где я участвовал, он использовался в комбинации с самыми разными платформами и языками – а счёт разновидностей форм, сделанных с его помощью в самых разнообразных проектах, идёт, наверное, уже на тысячи. Это Java-проект, который может работать как в качестве утилиты командной строки, так и в качестве библиотеки (если у вас код на JVM-языке — вы можете подключить её как Maven-зависимость).
Xylophone реализует принцип отделения модели данных от их представления. В процедуре выгрузки необходимо сформировать данные в формате XML (не беспокоясь о ячейках, шрифтах и разделительных линиях), а Xylophone, при помощи Excel-шаблона и дескриптора, описывающего порядок обхода вашего XML-файла с данными, сформирует результат, как показано на диаграмме:
Шаблон документа (xls/xlsx template) выглядит примерно следующим образом:
Как правило, заготовку такого шаблона предоставляет сам заказчик. Вовлечённый заказчик с удовольствием принимает участие в создании шаблона: начиная с выбора нужной формы из «Консультанта» или придумывания собственной с нуля, и заканчивая размерами шрифтов и ширинами разделительных линий. Преимущество шаблона в том, что мелкие правки в него легко вносить уже тогда, когда отчёт полностью разработан.
Когда «оформительская» работа выполнена, разработчику остаётся
- Создать процедуру выгрузки необходимых данных в формате XML.
- Создать дескриптор, описывающий порядок обхода элементов XML-файла и копирования фрагментов шаблона в результирующий отчёт
- Обеспечить привязку ячеек шаблона к элементам XML-файла с помощью XPath-выражений.
Если бы в форме, которую мы создаём, не было повторяющихся элементов с разным количеством (таких, как строки накладной, которых разное количество у разных накладных), то дескриптор выглядел бы следующим образом:
Здесь root – название корневого элемента нашего XML-файла с данными, а диапазон A1:Z100 – это прямоугольный диапазон ячеек из шаблона, который будет скопирован в результат. При этом, как можно видеть из предыдущей иллюстрации, подстановочные поля, значения которых заменяются на данные из XML-файла, имеют формат ~ (тильда, фигурная скобка, XPath-выражение относительно текущего элемента XML, закрывающая фигурная скобка).
Что делать, если в отчёте нам нужны повторяющиеся элементы? Естественным образом их можно представить в виде элементов XML-файла с данными, а помочь проитерировать по ним нужным образом помогает дескриптор. Повторение элементов в отчёте может иметь как вертикальное направление (когда мы вставляем строки накладной, например), так и горизонтальное (когда мы вставляем столбцы аналитического отчёта). При этом мы можем пользоваться вложенностью элементов XML, чтобы отразить сколь угодно глубокую вложенность повторяющихся элементов отчёта, как показано на диаграмме:
Красными квадратиками отмечены ячейки, которые будут являться левым верхним углом очередного прямоугольного фрагмента, который пристыковывает генератор отчёта.
Есть и ещё один возможный вариант повторяющихся элементов: листы в книге Excel. Возможность организовать такую итерацию тоже имеется.
Рассмотрим чуть более сложный пример. Допустим, нам надо получить сводный отчёт наподобие следующего:
Пусть диапазон лет для выгрузки выбирает пользователь, поэтому в этом отчёте динамически создаваемыми являются как строки, так и столбцы. XML-представление данных для такого отчёта может выглядеть следующим образом:
Мы вольны выбирать названия тэгов по своему вкусу, структура также может быть произвольной, но с оглядкой на простоту конвертации в отчёт. Например, выводимые на лист значения я обычно записываю в атрибуты, потому что это упрощает XPath-выражения (удобно, когда они имеют вид @имяатрибута ).
Шаблон такого отчёта будет выглядеть так (сравните XPath-выражения с именами атрибутов соответствующих тэгов):
Теперь наступает самая интересная часть: создание дескриптора. Т. к. это практически полностью динамически собираемый отчёт, дескриптор довольно сложен, на практике (когда у нас есть только «шапка» документа, его строки и «подвал») всё обычно гораздо проще. Вот какой в данном случае необходим дескриптор:
Полностью элементы дескриптора описаны в документации. Вкратце, основные элементы дескриптора означают следующее:
- element — переход в режим чтения элемента XML-файла. Может или являться корневым элементом дескриптора, или находиться внутри iteration . С помощью атрибута name могут быть заданы разнообразные фильтры для элементов, например
- name="foo" — элементы с именем тэга foo
- name="*" — все элементы
- name="tagname[@attribute='value']" — элементы с определённым именем и значением атрибута
- name="(before)" , name="(after)" — «виртуальные» элементы, предшествующие итерации и закрывающие итерацию.
- mode="horizontal" — режим вывода по горизонтали (по умолчанию — vertical)
- index=0 — ограничить итерацию только самым первым встреченным элементом
- sourcesheet —лист книги шаблона, с которого берётся диапазон вывода. Если не указывать, то применяется текущий (последний использованный) лист.
- range – диапазон шаблона, копируемый в результирующий документ, например “A1:M10”, или “5:6”, или “C:C”. (Применение диапазонов строк типа “5:6” в режиме вывода horizontal и диапазонов столбцов типа “C:C” в режиме вывода vertical приведёт к ошибке).
- worksheet – если определён, то в файле вывода создаётся новый лист и позиция вывода смещается в ячейку A1 этого листа. Значение этого атрибута, равное константе или XPath-выражению, подставляется в имя нового листа.
Ну что же, настало время скачать Xylophone и запустить формирование отчёта.
Возьмите архив с bintray или Maven Central (NB: на момент прочтения этой статьи возможно наличие более свежих версий). В папке /bin находится shell-скрипт, при запуске которого без параметров вы увидите подсказку о параметрах командной строки. Для получения результата нам надо «скормить» ксилофону все приготовленные ранее ингредиенты:
Открываем файл report.xlsx и убеждаемся, что получилось именно то, что нам нужно:Так как библиотека ru.curs:xylophone доступна на Maven Central под лицензией LGPL, её можно без проблем использовать в программах на любом JVM-языке. Пожалуй, самый компактный полностью рабочий пример получается на языке Groovy, код в комментариях не нуждается:
У класса XML2Spreadsheet есть несколько перегруженных вариантов статического метода process , но все они сводятся к передаче всё тех же «ингредиентов», необходимых для подготовки отчёта.Важная опция, о которой я до сих пор не упомянул — это возможность выбора между DOM и SAX парсерами на этапе разбора файла с XML-данными. Как известно, DOM-парсер загружает весь файл в память целиком, строит его объектное представление и даёт возможность обходить его содержимое произвольным образом (в том числе повторно возвращаясь в один и тот же элемент). SAX-парсер никогда не помещает файл с данными целиком в память, вместо этого обрабатывает его как «поток» элементов, не давая возможности вернуться к элементу повторно.
Использование SAX-режима в Xylophone (через параметр командной строки -sax или установкой в true параметра useSax метода XML2Spreadsheet.process ) бывает критически полезно в случаях, когда необходимо генерировать очень большие файлы. За счёт скорости и экономичности к ресурсам SAX-парсера скорость генерации файлов возрастает многократно. Это даётся ценой некоторых небольших ограничений на дескриптор (описано в документации), но в большинстве случаев отчёты удовлетворяют этим ограничениям, поэтому я бы рекомендовал использование SAX-режима везде, где это возможно.
Надеюсь, что способ выгрузки в Excel через Xylophone вам понравился и сэкономит много времени и нервов — как сэкономил нам.
Читайте также: