Как сформировать сложный XLS - отчет средствами PL/SQL

  Предположим, вам поступила задача по выдаче сложного отчета с замысловатой шапкой через ваш веб-клиент. Как это сделать? Предлагаемый способ довольно прост и позволяет реализовать отчеты любой сложности, используя только PL/SQL и XML.

  Задача: Сформировать отчет на базе XLS-шаблона в веб-клиенте.
   Условия:
       1) отчет должен иметь строго определенную шапку
       2) определенные ячейки нужно заполнять динамически
       3) в отчет необходимо динамически добавлять строки

Шаблон отчета в Excel:

Решение.

  Чтобы легко манипулировать содержимым отчета,  будем использовать XML - формат. Но прежде надо немного подготовить исходный шаблон для выполнения условия 2). Чтобы легко находить и заполнять нужную ячейку новым значением, расставим в отчете "маячки". Я кодировал "маячок" как  №строки*1000000000+№заполняемой колонки.  См.пример:

   Для отображения нужного нам шаблона отчета, сохраняем наш XLS файл в формате "XML-таблица 2003". Затем открываем XML как текстовый файл и копируем все строки в поле CLOB. Например, с помощью PL/SQL Developer:

! Важно. Корректно вставляются только данные в кодировке UTF-8. Неявную перекодировку можно производить например в Блокноте (Открыли в блокноте ANSI, скопировали в буфер, вставили в таблицу).

! Важно. Копируем во вкладку Text

  Теперь мы можем обращаться с отчетом как с обычным текстовым файлом: склеивать его куски, выполнять поиск и замену по подстроке и т.д.

В хранимой процедуре "вытаскиваем" наш XML-шаблон в переменную:

select clob_content into myclob from t_app_clob where clobid = 1;

   Функция поиска и замены позволит нам легко выполнить условие 2) : "определенные ячейки нужно заполнять динамически". Вот примерный текст:

--------
function replaceclob(pclob clob,sourcesubstr varchar2, destsubstr varchar2) return clob is
   outclob clob;
   vpos number;
  begin
    outclob:= pclob;
    vpos := instr(outclob,sourcesubstr);
    while (vpos > 0) loop
        outclob := substr(outclob,1,vpos-1)||destsubstr||substr(outclob,vpos+length(sourcesubstr));
        vpos := instr(outclob,sourcesubstr);
    end loop;
    return outclob;
  end;
---------

Используем функцию с нашими "маячками":

begin
   myclob := replaceclob(myclob,'8000000001',v_newvalue);
end;

Таким образом заполняем все требуемые значения.  Теперь необходимо выполнить условие 3) и добавить новые строки к отчету. Для этого придется вручную "сверстать" требуемые строки в XML. Например:

--------
 myclob := myclob|| '   <Row ss:AutoFitHeight="0" ss:Height="13.6875">'||chr(10)||
    '<Cell ss:StyleID="s310"><Data ss:Type="String">'||to_char(myi)||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="21" ss:StyleID="m76547168"><Data ss:Type="String">'||upper(trim(nn.fio))||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="8" ss:StyleID="m76547188"><Data ss:Type="String"></Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="9" ss:StyleID="m76547208"><Data ss:Type="String"></Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="13" ss:StyleID="m76547228"><Data ss:Type="Number">'||nn.dfallper||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="10" ss:StyleID="m76547248"><Data ss:Type="Number">'||nn.df1mes||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="11" ss:StyleID="m76547268"><Data ss:Type="Number">'||nn.df2mes||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="9" ss:StyleID="m76547392"><Data ss:Type="Number">'||nn.df3mes||'</Data></Cell>'||chr(10)||
   '</Row>'||chr(10);
---------

Когда наш XML полностью заполнен нужными данными, надо вывести его на web-страницу. Сделаем это с помощью пакета HTP.

 Примерный код процедуры вывода текста:

-------------------
 procedure WriteToWeb( c clob ) is

  tmp varchar2(32000);
  pos number := 1;
  len number;

begin
  len := dbms_lob.getlength(c);
  len := NVL(len,0);
  -- а вот здесь задача выдачи содержимого clob через htp.p
  loop
    exit when pos > len;
    tmp := dbms_lob.substr(c, 32000, pos);
    htp.prn( tmp );
    pos := pos + 32000;
  end loop;
exception when no_data_found then
--  htp.p('данных не найдено');
  null;
end;
--------------------


Осталось пара штрихов:

    owa_util.mime_header( 'text/html', FALSE, 'utf-8' );
 -- Set the name of the file
 htp.p('Content-Disposition: attachment; filename="REPORT.xls"');
 -- Close the HTTP Header
 owa_util.http_header_close;  
-- загружаем наш отчет как вложение на веб-странице
  WriteToWeb(myclob);


Вот и все. Такой способ позволяет сформировать отчет ЛЮБОЙ сложности.   Удачной вам разработки :)

PS: Данный метод хорошо показал себя при работе с Excel - шаблонами.  Как быть если вам нужно сформировать документ MS Word -  см. следующую заметку.