Иногда необходимо сделать быструю выгрузку данных в интерфейсе Formspider при нажатии на кнопку "Export to XLS", к примеру. Для этого в Formspider создаётся объект ACTION в котором указывается пакет и его процедура, скачивающая нужные данные.
В теле процедуры объявлена переменная формата BLOB, которой присваивается значение, возвращаемое функцией, которой я и хочу поделиться с общественностью (выгрузка из PL/SQL делается при помощи пакетов DBMS_SQL и AS_XLSX).
Самый простой способ динамической выгрузки запроса в XLS посредством вышеуказанного пакета:
begin
as_xlsx.query2sheet( 'SELECT * FROM table_name');
as_xlsx.save('DIRECTORY', 'my.xlsx' );
end;
Однако когда вы откроете выгруженный файл, то в нём не будет красивых заголовков, выравнивания, границ ячеек и нужного шрифта.
Удобство функции заключается в том, что вы передаёте ей любой запрос и на выходе получаете эксель-файл с нужным форматом оформления и всеми столбцами, которые возвращает SELECT.
Код для создания функции
CREATE OR REPLACE FUNCTION get_xlsx_from_select(SELECT_STATEMENT in VARCHAR2)
RETURN BLOB is
nc NUMBER;
rc NUMBER;
ncol NUMBER;
headers VARCHAR2(69);
cols DBMS_SQL.DESC_TAB;
colvalue VARCHAR2(69);
v_file BLOB;
col_num NUMBER := 0;
row_num NUMBER := 2;
BEGIN
as_xlsx.clear_workbook;
as_xlsx.new_sheet('New');
nc := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(nc, select_statement, dbms_sql.native);
DBMS_SQL.DESCRIBE_COLUMNS(nc, ncol, cols);
--headers
FOR i in 1..ncol
LOOP
DBMS_SQL.DEFINE_COLUMN(nc, i, colvalue, 64);
headers := cols(i).col_name;
--format
col_num := col_num + 1;
as_xlsx.set_row(1, p_fontId => as_xlsx.get_font('Times New Roman', p_fontsize => 10, p_bold => TRUE),
p_fillId => as_xlsx.get_fill('solid', 'FFCC66'), p_borderId => as_xlsx.get_border);
--headers
as_xlsx.set_column_width(col_num, 14);
as_xlsx.cell(col_num, 1, headers, p_alignment => as_xlsx.get_alignment
(p_horizontal => 'center', p_vertical => 'center', p_wraptext => TRUE));
END LOOP;
rc := DBMS_SQL.EXECUTE(nc);
--rows
WHILE DBMS_SQL.FETCH_ROWS(nc) > 0
LOOP
for i in 1..ncol
loop
DBMS_SQL.COLUMN_VALUE(nc, i, colvalue);
--format
as_xlsx.set_row(row_num, p_fontId => as_xlsx.get_font('Times New Roman', p_fontsize => 9), p_borderId => as_xlsx.get_border);
--data
as_xlsx.cell(i, row_num, coalesce(colvalue, '-'),
p_alignment => as_xlsx.get_alignment(p_horizontal => 'center', p_vertical => 'center', p_wraptext => TRUE));
end loop;
row_num := row_num + 1;
END LOOP;
dbms_sql.close_cursor(nc);
v_file := as_xlsx.finish;
RETURN v_file;
END;
Вызывается функция очень просто (для экономии времени в будущем при работе с датами и текстом необходимо экранировать одинарные кавычки посредством q'[...]')
SELECT get_xlsx_from_select(q'[SELECT id, name, to_char(date, 'mm.dd.yyyy') FROM moex]') FROM dual;
Имеются некоторые особенности:
eсть ограничение в 32767 символов, при просмотре получившегося BLOB в тестовом окне PL/SQL при нажатии на "...". Если его сохранить в файловой системе, то файл откроется без проблем.
чтобы дата в выгруженном XLS была в нужном формате в селекте его надо указать для нужного поля TO_CHAR(birth_date, 'dd.mm.yyyy'), поскольку вышеуказанная функция формирования XLS возвращает все данные как текст.
Пример выгруженного файла (название поля "DATE" для примера.. уточняю, т.к. это зарезервированное слово):
