May 7 2015 at 09:43

Генерация XLSX средствами PL/SQL: шаблонный подход

Invite pending

Ничего оригинального: копипаст открытого кода + общеизвестная идея = PL/SQL пакет AM_FILL, задуманный и реализованный в качестве альтернативы генераторам отчетов к простым приложениям в среде APEX Oracle-XE.

Изделие базируется на коде пакетов анализа и генерации xlsx-файлов, опубликованных под GPL(MIT) (автор Anton Scheffer):
— AS_ZIP (http://technology.amis.nl/wp-content/uploads/2010/06/as_zip7.txt );
— AS_XLSX и AS_READ_XLSX (http://technology.amis.nl/blog/author/anton-scheffer/ ).
Пакет AM_FILL позволяет заполнять xlsx-шаблон данными Oracle типов NUMBER, DATE, VARCHAR2 в режимах наложения и вставки строк, дублировать листы шаблона.

В качестве примера используем бланк знакомого большинству документа:

Заполним его, используя процедуры пакета:

Procedure am_fill_example
( p_xlsx in out nocopy BLOB  -- шаблон листка
)
as
type tp_names is table of varchar2(100);
type tp_sums is table of number(10,2);
-- Для вывода переменной части листка необходима таблица (матрица) значений,
-- которая для простоты представлена набором векторов. Расчетная часть опущена:)
l_inames tp_names := tp_names('Оклад','Премия', 'Отпуск','всего начислено'); 
l_isums  tp_sums := tp_sums(60000, 120000, 40000, 220000); 
l_idays  tp_names := tp_names('32 дн','I кв','20 дн');
l_rnames tp_names := tp_names('НДФЛ','','','всего удержано');
l_rsums  tp_sums := tp_sums(28600, null, null, 28600); 
begin
-- инициализация шаблоном с разрешением exception в случае ошибки имени диапазона
am_fill.init(p_xlsx,'e');
-- заполнение именованных диапазонов титульной части 
am_fill.in_field(sysdate, 'Расчетная_дата');
am_fill.in_field('Привалов Александр Иванович', 'ФИО_сотрудника');
am_fill.in_field('заведующий', 'Должность'); 
am_fill.in_field('вычислительный центр', 'Подразделение'); 
am_fill.in_field(l_isums(1), 'Оклад'); 
-- заполнение переменной части в режиме последовательной вставки строк 
for i in 1..3 loop
-- колонки (поля) шаблона предпочтительно именовать
  am_fill.in_field(l_inames(i), 'Нач_Вид', 'i'); 
  am_fill.in_field(l_isums(i),  'C8', 'i'); 
  am_fill.in_field(l_idays(i),  'D8', 'i'); 
  am_fill.in_field(l_rnames(i), 'E8', 'i');
  am_fill.in_field(l_rsums(i),  'G8', 'i');
end loop;
-- заполнение итоговой части
am_fill.in_field(l_isums(4), 'C9');
am_fill.in_field(l_rsums(4), 'G9');
am_fill.in_field(l_isums(4)-l_rsums(4), 'К_выплате');
-- формирование документа
am_fill.finish(p_xlsx);
end;

Сформированный xlsx-документ:

Помимо небогатых функций пакет имеет дополнительные ограничения:
— в режиме вставки строк обрезается вертикальное объединение ячеек;
— из заполняемых листов удаляются данные, отличные от чисел, строк и дат (формулы попадают под раздачу первыми), но нет препятствий для выгрузки в скрытые листы, а в видимых использовать весь функционал Excel.

Hubs:

Oracle