» » » Препарирование файлов .XLSX: редактирование файла средствами PL/SQL

 

Препарирование файлов .XLSX: редактирование файла средствами PL/SQL

Автор: admin от 21-02-2018, 16:25, посмотрело: 210

Часть 1. Введение, стили

Часть 2. Строки, разметка

Часть 3. Редактирование через PL/SQL



Доброго дня. Третья часть разговора про формат XLSX подоспела. Я не случайно начал со внутреннего устройства файла. Не понимая где что находится и как выглядит, сложно понять, для чего я сделал то-то и то-то. К тому же, теперь я могу сделать несколько замечаний:



Первое. Если я не упомянул какой-то элемент, который нужен именно вам, — создайте пустой файл XLSX, сделайте нужный элемент и сохраните. Теперь вы знаете, где искать код, определяющий этот элемент.



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

Alexandria PL/SQL. Сама библиотека огромна, и если кроме как для целей, описанных ниже, она вам не нужна, то лучше имплантировать ее выборочно.



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




  • Трансформируем файл-заготовку в BLOB;

  • Заменяем условные метки в XML-файлах внутри него на наши данные;

  • Сохраняем измененный BLOB как новый файл;

  • Возвращаем измененный файл пользователю.



  • Пройдемся по этим пунктам подробнее. Чтобы различать библиотечные средства и самописные, представим, что библиотечный код я разместил в умозрительном пакете lib_utils.



    Трансформация файла-заготовки в BLOB



    Если у нас есть в наличии некий бланк, к примеру, квитанция, в которую надо вставить ФИО абонента и сумму к оплате, то все проще некуда: берем готовую квитанцию и меняем содержимое переменных полей на специальные метки-маячки. По поводу текста этих маячков есть два основных правила — они не должны имитировать теги и вероятность совпадения в исходном тексте документа или в заменяющем тексте должна быть исчезающе малой. В остальном все зависит от вашей фантазии или привычек. Я использую что-то типа %name%. Объясню почему. Знак "%" не имитирует разметку, и вероятность того, что где-то будет слово, с двух сторон обособленное этим знаком, — мизерна.



    А вот в случае, когда мы заранее не знаем, что может быть в файле на выходе, работы будет больше.



    Перво-наперво рекомендую поэксплуатировать Excel и обозначить все стили ячеек, которые нам могут понадобиться (если не понадобятся — ничего страшного, это лучше, чем если чего-то не хватит). После этого лезем блокнотом в стили и записываем индексы конкретных стилей. Так, я сделал себе отдельный стиль для заголовка (серая заливка, полужирное написание и тонкие границы) и отдельный стиль для рядовой строки (без заливки, обычное написание и тонкие границы).



    Далее работать придется не через Excel, а руками.



    Файл sharedStrings.xml должен выглядеть примерно так:



    <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    	%strings%
    </sst>
    


    Файл sheet1.xml (предполагая, что основным листом у нас будет первый) должен содержать следующее:



    <sheetViews>
    	<sheetView tabSelected="1" workbookViewId="0">
    		%attach%
    	</sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    %colsize%
    <sheetData>
    	%data%
    </sheetData>
    %filter%
    


    Метка %attach% расположена там, где должен находиться тег закрепления области. Метка %colsize% — там, где находится тег, указывающий ширину занятых столбцов. Это сделано для того, чтобы, допустим, в столбце ФИО ширина была соответствующей. Метка %data% будет заменена сгенерированной разметкой ячеек. Метка %filter% — на случай, если понадобится встраивать автофильтр.



    Сохраняем, закрываем — болванка готова. Далее нам надо трансформировать ее в BLOB. Для этого нам понадобится библиотечная функция lib_utils.get_blob_from_file (на всякий случай напомню, что lib_utils — это функции из библиотеки по ссылке в начале поста). Функция принимает два параметра: директорию и имя файла. Поскольку это слегка неочевидно, поясню, что под директорией подразумевается оракловый объект DIRECTORY. В нашем примере назовем директорию FILE_DIR. То есть вызов будет выглядеть примерно так:



    -- Заранее извиняюсь, если мой стиль написания кода и именования объектов кого-то покоробит
    v_blobsrc := lib_utils.get_blob_from_file('FILE_DIR', 'src_blank.xlsx');
    


    Замена меток на кастомные данные



    В более простом случае с бланком квитанции (или аналогичном случае), просто используем функцию lib_utils.multi_replace. Библиотека все сделает за вас.



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



    /* Тип: ячейка */
    type tp_cell is record(address varchar2(15),
    			style number,
    			val varchar2(4000),
    			lines number default 1);
    
    /* Тип: строка */
    type tp_row is table of tp_cell index by binary_integer;
    
    /* Тип: таблица (лист) */
    type tp_table is table of tp_row index by binary_integer;
    
    /* Тип: массивы строковых и числовых значений */
    type tp_string is table of varchar2(4000) index by binary_integer;
    type tp_number is table of number index by binary_integer;
    


    Последний тип напрямую не задействован в построении tp_table, но все равно далее будет нужен. Поясню элементы типа tp_cell.




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

    • style — ссылка на индекс стиля описываемой ячейки. Я для себя решил, что все описанные мной стили будут храниться в виде глобальных констант пакета custom_utils.

    • val — содержимое ячейки.

    • lines — флажок многострочности. Установка его значения на 2 или выше будет означать, что в содержимом ячейки подразумевается перенос, а как мы помним, для его отображения следует увеличивать высоту ячейки.



    Итоговая сигнатура моей процедуры компоновки файла выглядит так:



    file_build(i_content tp_table,
    	i_filename varchar2,
    	i_filter number default 0,
    	i_attach number default 0);
    


    Процедура принимает следующие параметры:




    • i_content — содержимое будущего файла.

    • i_filename — имя будущего файла.

    • i_filter — флажок необходимости автофильтра.

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



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





    Сразу хочу кое-что пояснить. Поскольку данных в файле может быть много, работать с varchar-ообразными типами нельзя, их просто не хватит. Поэтому приходится затачивать свое решение под CLOB. В целом же, пока что ничего сверхъестественного. Но — к делу.





    Собственно, всё. Ну и да, хочу сделать оговорку: эта процедура периодически дополняется в соответствии с текущими потребностями.

    Источник: Хабрахабр

    Категория: Операционные системы » Android

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

    Добавление комментария

    Имя:*
    E-Mail:
    Комментарий:
    Полужирный Наклонный текст Подчеркнутый текст Зачеркнутый текст | Выравнивание по левому краю По центру Выравнивание по правому краю | Вставка смайликов Выбор цвета | Скрытый текст Вставка цитаты Преобразовать выбранный текст из транслитерации в кириллицу Вставка спойлера
    Введите два слова, показанных на изображении: *