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

/* Возвращает буквенный эквивалент числа (имена столбцов 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;

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;