Иногда необходимо сделать быструю выгрузку данных в интерфейсе 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» для примера.. уточняю, т.к. это зарезервированное слово):
ссылка на оригинал статьи https://habr.com/ru/articles/569846/
Добавить комментарий