Часть 1. Введение, стили
Часть 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. Сама библиотека огромна, и если кроме как для целей, описанных ниже, она вам не нужна, то лучше имплантировать ее выборочно.

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

  1. Трансформируем файл-заготовку в BLOB;
  2. Заменяем условные метки в XML-файлах внутри него на наши данные;
  3. Сохраняем измененный BLOB как новый файл;
  4. Возвращаем измененный файл пользователю.

Пройдемся по этим пунктам подробнее. Чтобы различать библиотечные средства и самописные, представим, что библиотечный код я разместил в умозрительном пакете 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;


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

Комментарии (1)


  1. NoRegrets
    21.02.2018 20:08

    Тот случай, когда в руках молоток.