Часть 1. Введение, стили
Часть 2. Строки, разметка
Часть 3. Редактирование через PL/SQL
Доброго дня. Третья часть разговора про формат XLSX подоспела. Я не случайно начал со внутреннего устройства файла. Не понимая где что находится и как выглядит, сложно понять, для чего я сделал то-то и то-то. К тому же, теперь я могу сделать несколько замечаний:
Первое. Если я не упомянул какой-то элемент, который нужен именно вам, — создайте пустой файл XLSX, сделайте нужный элемент и сохраните. Теперь вы знаете, где искать код, определяющий этот элемент.
Второе. OpenXML допускает наличие в разметке произвольного текста, если он не нарушает структуру тегов (этим мы будем очень активно пользоваться). Сейчас проиллюстрирую. Вот так делать можно:
А вот так — нельзя:
Но к делу. Строго говоря, есть два возможных случая. Либо мы имеем некий шаблон, который нам необходимо заполнить данными, либо сами вынуждены составлять файл, что называется, с нуля. Первый случай проще, второй — интереснее. Но при этом оба случая требуют от нас наличия файла-заготовки: поскольку файл .XLSX состоит не только из .XML-файлов, создать его «руками», увы, не выйдет.
Вообще методика, которой я пользуюсь, в значительной степени основывается на библиотеке Alexandria PL/SQL. Сама библиотека огромна, и если кроме как для целей, описанных ниже, она вам не нужна, то лучше имплантировать ее выборочно.
Для простой подстановки в бланк квитанции ФИО абонента средств, представленных в этой библиотеке, должно вполне хватить. Мне же, с учетом моей специфики, пришлось делать над ней надстройку. Поэтому если вы начали читать, рекомендую дочитать до конца: как знать, быть может, мое решение покажется вам более удобным или эффективным. Общий же алгоритм действий таков:
Пройдемся по этим пунктам подробнее. Чтобы различать библиотечные средства и самописные, представим, что библиотечный код я разместил в умозрительном пакете lib_utils.
Если у нас есть в наличии некий бланк, к примеру, квитанция, в которую надо вставить ФИО абонента и сумму к оплате, то все проще некуда: берем готовую квитанцию и меняем содержимое переменных полей на специальные метки-маячки. По поводу текста этих маячков есть два основных правила — они не должны имитировать теги и вероятность совпадения в исходном тексте документа или в заменяющем тексте должна быть исчезающе малой. В остальном все зависит от вашей фантазии или привычек. Я использую что-то типа %name%. Объясню почему. Знак "%" не имитирует разметку, и вероятность того, что где-то будет слово, с двух сторон обособленное этим знаком, — мизерна.
А вот в случае, когда мы заранее не знаем, что может быть в файле на выходе, работы будет больше.
Перво-наперво рекомендую поэксплуатировать Excel и обозначить все стили ячеек, которые нам могут понадобиться (если не понадобятся — ничего страшного, это лучше, чем если чего-то не хватит). После этого лезем блокнотом в стили и записываем индексы конкретных стилей. Так, я сделал себе отдельный стиль для заголовка (серая заливка, полужирное написание и тонкие границы) и отдельный стиль для рядовой строки (без заливки, обычное написание и тонкие границы).
Далее работать придется не через Excel, а руками.
Файл sharedStrings.xml должен выглядеть примерно так:
Файл sheet1.xml (предполагая, что основным листом у нас будет первый) должен содержать следующее:
Метка %attach% расположена там, где должен находиться тег закрепления области. Метка %colsize% — там, где находится тег, указывающий ширину занятых столбцов. Это сделано для того, чтобы, допустим, в столбце ФИО ширина была соответствующей. Метка %data% будет заменена сгенерированной разметкой ячеек. Метка %filter% — на случай, если понадобится встраивать автофильтр.
Сохраняем, закрываем — болванка готова. Далее нам надо трансформировать ее в BLOB. Для этого нам понадобится библиотечная функция lib_utils.get_blob_from_file (на всякий случай напомню, что lib_utils — это функции из библиотеки по ссылке в начале поста). Функция принимает два параметра: директорию и имя файла. Поскольку это слегка неочевидно, поясню, что под директорией подразумевается оракловый объект DIRECTORY. В нашем примере назовем директорию FILE_DIR. То есть вызов будет выглядеть примерно так:
В более простом случае с бланком квитанции (или аналогичном случае), просто используем функцию lib_utils.multi_replace. Библиотека все сделает за вас.
Для сложного случая я построил свою конструкцию. В основе ее лежит составной рукописный тип данных, являющий собой комплексное описание содержимого листа Excel. Поскольку тип составной, пойдем снизу вверх:
Последний тип напрямую не задействован в построении tp_table, но все равно далее будет нужен. Поясню элементы типа tp_cell.
Итоговая сигнатура моей процедуры компоновки файла выглядит так:
Процедура принимает следующие параметры:
И прежде чем начать подробный разбор основной процедуры, выложу вспомогательные функции:
Сразу хочу кое-что пояснить. Поскольку данных в файле может быть много, работать с varchar-ообразными типами нельзя, их просто не хватит. Поэтому приходится затачивать свое решение под CLOB. В целом же, пока что ничего сверхъестественного. Но — к делу.
Собственно, всё. Ну и да, хочу сделать оговорку: эта процедура периодически дополняется в соответствии с текущими потребностями.
Часть 2. Строки, разметка
Часть 3. Редактирование через PL/SQL
Доброго дня. Третья часть разговора про формат XLSX подоспела. Я не случайно начал со внутреннего устройства файла. Не понимая где что находится и как выглядит, сложно понять, для чего я сделал то-то и то-то. К тому же, теперь я могу сделать несколько замечаний:
Первое. Если я не упомянул какой-то элемент, который нужен именно вам, — создайте пустой файл XLSX, сделайте нужный элемент и сохраните. Теперь вы знаете, где искать код, определяющий этот элемент.
Второе. OpenXML допускает наличие в разметке произвольного текста, если он не нарушает структуру тегов (этим мы будем очень активно пользоваться). Сейчас проиллюстрирую. Вот так делать можно:
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<si><t>Первая строка</t></si>
<si><t>Вторая строка</t></si>
$Здесь был Вася$
</sst>
А вот так — нельзя:
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<si><t>Первая строка</t></si>
<si><t>Вторая строка</t></si>
<Здесь был Вася>
</sst>
Но к делу. Строго говоря, есть два возможных случая. Либо мы имеем некий шаблон, который нам необходимо заполнить данными, либо сами вынуждены составлять файл, что называется, с нуля. Первый случай проще, второй — интереснее. Но при этом оба случая требуют от нас наличия файла-заготовки: поскольку файл .XLSX состоит не только из .XML-файлов, создать его «руками», увы, не выйдет.
Вообще методика, которой я пользуюсь, в значительной степени основывается на библиотеке 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 — флажок необходимости закрепления области. Поскольку в моем случае требуется закрепление только первой строки, у меня генерируемый из-за этого флага код всегда будет одинаковым.
И прежде чем начать подробный разбор основной процедуры, выложу вспомогательные функции:
Вспомогательные функции
/* Возвращает буквенный эквивалент числа (имена столбцов Excel) */
function get_literal(i_number number) return varchar2
is
begin
-- Таких не бывает
if i_number < 1 or is_number(i_number) = false then
return '#';
-- 1-символьная выдача
elsif i_number > 0 and i_number < 29 then
return chr(64 + i_number);
-- 2-символьная выдача
else
return chr(64 + trunc(i_number / 28))||chr(64 + (i_number - (28 * trunc(i_number / 28))));
end if;
end get_literal;
/* Проверка: число или строка */
function is_number(i_char char) return boolean
is
begin
if (to_number(i_char) = to_number(i_char)) then
return true;
end if;
exception
when others then
return false;
end is_number;
/* Поиск в текстовом массиве */
function array_search(i_source tp_string,
i_value varchar2) return number
is
begin
for i in 1 .. i_source.count loop
if i_value = i_source(i) then
return i;
end if;
end loop;
return -1;
end array_search;
/* Аккуратное "дописывание" небольшой (до 32767 символов) строки к CLOB-у в нужной кодировке */
procedure clob_append(i_dest in out clob,
i_src in varchar2,
i_encode in varchar2 default 'utf8')
is
begin
if i_src is not null then
if i_dest is null then
i_dest := to_clob(convert(i_src, i_encode));
else
dbms_lob.write(i_dest, length(convert(i_src, i_encode)), length(i_dest) + 1, convert(i_src, i_encode));
end if;
end if;
end clob_append;
Сразу хочу кое-что пояснить. Поскольку данных в файле может быть много, работать с varchar-ообразными типами нельзя, их просто не хватит. Поэтому приходится затачивать свое решение под CLOB. В целом же, пока что ничего сверхъестественного. Но — к делу.
Основная процедура
procedure build_file(i_content tp_table,
i_filename varchar2,
i_filter number default 0,
i_attach number default 0)
is
v_blobsrc blob; -- Заготовка в формате BLOB
v_blobres blob; -- Результат в формате BLOB
c_namesrc constant varchar2(50) := 'src_blank.xlsx'; -- Имя файла-заготовки
v_stringarr tp_string; -- Массив уникальных строковых значений
v_numarr tp_number; -- Массив ширин столбцов
v_index number; -- Для поиска в массиве
v_clobmarkup clob; -- Разметка ячеек
v_clobstring clob; -- Разметка sharedStrings.xml
v_clobcolumns clob; -- Разметка шиниры столбцов
c_letsize constant number := 3; -- Ширина 1 символа в У.Е.
c_padding constant number := 1; -- Запас ширины ячейки
v_rowcount number; -- Фактическое кол-во строк
v_colcount number; -- Фактическое кол-во столбцов
v_multiline number; -- Многострочность
v_filtertag varchar2(50); -- Текст тега фильтра
v_attachtag varchar2(150); -- Текст тега закрепления области
begin
/* Цикл по строкам в таблице */
for l_row in 1 .. i_content.count loop
v_rowcount := l_row; -- Для тега DIMENSION
/* Вычисляем, есть ли в строке многострочные ячейки. Если есть, растягиваем строку по высоте на максимальную величину многострочности */
v_multiline := 1;
for l_col in 1 .. i_content(l_row).count loop
if i_content(l_row)(l_col).lines > v_multiline then
v_multiline := i_content(l_row)(l_col).lines;
end if;
end loop;
clob_append(v_clobmarkup, '<row r="'||l_row||'"'||case when v_multiline > 1 then ' ht="'||(15 * v_multiline)||'" customHeight="1"' else null end||'>'||chr(10));
/* Цикл по ячейкам в строке */
for l_cells in 1 .. i_content(l_row).count loop
v_colcount := l_cells;
-- Пустая ячейка
if i_content(l_row)(l_cells).val is null then
clob_append(v_clobmarkup, '<c r="'||i_content(l_row)(l_cells).address||'" s="'||i_content(l_row)(l_cells).style||'"><v></v></c>'||chr(10));
else
-- Числовые значения идут как есть
if is_number(i_content(l_row)(l_cells).val) then
clob_append(v_clobmarkup, '<c r="'||i_content(l_row)(l_cells).address||'" s="'||i_content(l_row)(l_cells).style||'"><v>'||i_content(l_row)(l_cells).val||'</v></c>'||chr(10));
else
/* Формируем список строк: "-1" - надо добавить, в противном случае это будет ссылка на индекс записи */
v_index := array_search(v_stringarr, i_content(l_row)(l_cells).val);
if v_index = -1 then
v_index := v_stringarr.count + 1;
v_stringarr(v_index) := i_content(l_row)(l_cells).val;
end if;
--
-- Формируем код ячейки со ссылкой на массив строк
clob_append(v_clobmarkup, '<c r="'||i_content(l_row)(l_cells).address||'" s="'||i_content(l_row)(l_cells).style||'" t="s"><v>'||(v_index - 1)||'</v></c>'||chr(10));
end if;
end if;
/* Подсчет максимальной длины содержимого столбца для рассчета ширины столбца */
if v_numarr.count >= l_cells then
if length(i_content(l_row)(l_cells).val) > v_numarr(l_cells) then
v_numarr(l_cells) := length(i_content(l_row)(l_cells).val);
end if;
elsif v_numarr.count = l_cells - 1 then
v_numarr(l_cells) := length(i_content(l_row)(l_cells).val);
end if;
end loop;
-- Закрываем строку таблицы
clob_append(v_clobmarkup, chr(10)||'</row>');
end loop;
-- Формируем код размеров столбцов
clob_append(v_clobcolumns, '<cols>');
for l_cnt in 1 .. v_numarr.count loop
clob_append(v_clobcolumns, '<col min="'||l_cnt||'" max="'||l_cnt||'" width="'||round((v_numarr(l_cnt) * c_letsize / 2) + c_padding)||'" style="1" customWidth="1"/>');
end loop;
clob_append(v_clobcolumns, '</cols>');
-- Формируем код массива строк
for l_cnt in 1 .. v_stringarr.count loop
clob_append(v_clobstring, '<si><t>'||v_stringarr(l_cnt)||'</t></si>'||chr(10));
end loop;
/* Фильтр и закрепление - при необходимости */
if i_filter = 1 then
v_filtertag := '<autoFilter ref="A1:'||get_literal(v_colcount)||'1"/>';
end if;
if i_attach = 1 then
v_attachtag := '<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>';
end if;
-- Имплантируем
v_blobsrc := lib_utils.get_blob_from_file('FILE_DIR', c_namesrc);
v_blobres := lib_utils.get_file_from_template(v_blobsrc,
lib_utils.t_str_array('%colsize%',
'%data%',
'%strings%',
'%filter%',
'%attach%',
'<dimension ref="A1:B2"/>'),
lib_utils.t_str_array(v_clobcolumns,
v_clobmarkup,
v_clobstring,
v_filtertag,
v_attachtag,
'<dimension ref="A1:'||get_literal(v_colcount)||v_rowcount||'"/>'));
lib_utils.save_blob_to_file('FILE_DIR', i_filename, v_blobres);
exception
when others then
dbms_output.put_line('Ошибка при генерации файла Excel: '||sqlerrm);
end build_file;
Собственно, всё. Ну и да, хочу сделать оговорку: эта процедура периодически дополняется в соответствии с текущими потребностями.
NoRegrets
Тот случай, когда в руках молоток.