2004 г.

Oracle Forms. Экспорт данных в Excel

Павел Лузанов Источник: сайт П.Лузанова, 01 Ноября 2003г., http://www.geocities.com/luzanovp/frm2xls.html,
Oracle Magazine RE ,Январь/Февраль 2004

Как и следует из названия, речь пойдёт о создании утилиты для экспорта данных из приложений Oracle Forms в MS Excel.

• Введение или Почему пользователя так любят Excel
• Постановка задачи. Требования и допущения
• Технические аспекты решения
• Решение
• Приложение

Введение или Почему пользователя так любят Excel

Ну почему пользователи так любят Excel!

Почему, после того как им установили новую систему (разработанную собственными силами или купленную) они непременно хотят получить данные в Excel.

Казалось бы, в системе предусмотрено множество отчетных форм (причем по количеству и внешнему виду согласованные с этими же пользователями!), но они всё равно "хотят Excel". Им, видите ли, так привычнее. Издеваются, не иначе :-)

Однако попробуем разобраться, для чего же вот этот Excel может быть действительно полезен.

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

Каким образом, пользователь может сформулировать, какие данные ему нужны? Да во многих случаях, он просто будет исходить из того, как он видит эти данные в экранных формах. Учитывая, что экранные формы Oracle Forms, как правило, позволяют выполнять нерегламентированные запросы (режим ввода запроса), а экранных форм в серьёзной системе не мало, то получается, что данные, которые пользователь может получить в экранных формах, представляют собой солидный источник данных для нерегламентированных отчетов.

Исходя из этого, экспорт данных из экранной формы в Excel выглядит весьма перспективно. Ведь чтобы не говорили противники Microsoft, Excel, действительно, весьма удобный продукт для обработки табличных данных.

Это особенно актуально для такой системы как Oracle Applications (Oracle E-Business Suite), где подавляющее большинство экранных форм используют однозаписные блоки данных, где в каждый момент времени на экране находится только одна запись, а работа с формой начинается с ввода и выполнения запроса. Желание посмотреть на все отобранные записи в виде списка - весьма законное!

Еще одной распространенной задачей, которую мог бы решить экспорт данных в Excel, является печать справочных данных (справочников). В любой системе существует большое количество различного рода справочников. Экранные формы для ввода в них данных - всегда присутствуют (иначе, как данные будут попадать в систему), а вот про печать справочных данных частенько забывают.

Итак, возможность экспорта данных из экранных форм в Excel, не такая уж и плохая идея. Она позволяет решить проблемы с выводом на печать справочников, с просмотром в виде списка данных из однозаписных блоков Oracle Forms и в какой-то степени помогла бы решить проблему с нерегламентированными отчетами.

Осталось лишь определиться, как же эту возможность реализовать.

Постановка задачи. Требования и допущения

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

Требования

Совместимость с последующими версиями Forms

Разработка велась для версии 6i, но с "прицелом" на возможный переход на 9i(10G)

Независимость от платформы и способа эксплуатации

Утилита должна работать как для режима клиент-сервер, так и для эксплуатации через Forms Server, причем не важно на какой платформе: будь то Windows или *nux Единственное условие, это наличие на клиентском компьютере MS Excel (а иначе куда экспортировать? :-)

Универсальность

Утилита должна работать для всех существующих форм и для всех форм, которые будут написаны в будущем

Допущения

Речь не идет о разработке генератора отчетов

Говоря об экспорте данных, имеется в виду именно экспорт данных, а не создание готового отчета с заголовками, формулами, красивым форматированием. Не нужно думать о пользователях плохо. Получив "сырые" данные в Excel они смогут в течении пары минут "раскрасить" их до готового отчета.

Передача данных обратно из Excel в Forms - не требуется

В противном случае непонятно зачем же нам нужен Forms, если данные могут корректироваться в Excel. Forms становится просто не нужной прослойкой между БД и Excel.

Но, как говорится, лучше один раз увидеть. Так вот, нам нужна утилита которая позволяет, имея вот это:

 

В результате получить вот это:

Технические аспекты решения

Итак, теперь нужно выбрать конкретный вариант реализации.

Существует два базовых подхода к созданию файлов Excel из внешних приложений.

Первый подразумевает использование "родных" технологий Microsoft (DDE, OLE, VBX, ActiveX, ...). И Forms6i позволяет их использовать, но этот вариант не годится из-за требования совместимости с последующими версиями Forms. А, как известно, в версии 9i пакетов VBX, OLE2, DDE больше нет.

Второй подход заключается в том, чтобы создать файл в формате, который MS Excel сможет прочитать. Такими форматами являются: html, csv|tsv (comma|tab separated values).

Вот этим то мы и воспользуемся! Из предложенных форматов выбираем tsv (html требует больше места, а "," может встречаться в данных).

Экспортировать будем данные текущего блока формы (в котором находится курсор). Именно это позволит сделать утилиту универсальной. Ведь всегда можно программно определить первый, последний, следующий, предыдущий элементы текущего блока и перемещаться по записям вне зависимости от того, в каком блоке, какой формы, какого приложения Oracle Forms мы находимся!

Другое дело, что не всегда нужно экспортировать значения всех элементов блока (например, не нужно экспортировать невидимые пользователю элементы), поэтому, было принято решение о том, что экспортироваться будут только те элементы, для которых справедливо:

• тип элемента один из TEXT ITEM, DISPLAY ITEM, LIST, CHECKBOX, RADIO GROUP
• элемент отображается на какой-либо канве и имеет свойства VISIBLE, DISPLAYED установленными в TRUE

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

Если вы используете общее меню для всего приложения, то для вызова утилиты удобнее всего создать специальный пункт меню "Экспорт в Excel". При желании его можно вывести как иконку в панель инструментов (Visible In Horizontal Menu Toolbar = Yes).

Для формирования файлов будем пользоваться пакетом TEXT_IO. Сами файлы должны иметь расширение xls для того, чтобы их можно было сразу открыть:

host('cmd /C start excel filename.xls');

Но это будет работать только для клиент-серверной эксплуатации. Как же быть с Forms Server, да и еще скажем на Solaris? Ведь TEXT_IO будет писать на сервер приложения, а не на клиентскую машину.

Здесь, опять же, возможны варианты.

Счастливые обладатели Forms9i могут попробовать новую утилиту WebUtil для доступа к клиентскому компьютеру. Хотя ни что не мешает вам написать, что-то подобное на Java и в версии Forms6i.

А можно пойти и другим путем.

Будем исходить из того, что раз мы используем Forms Server, значит здесь же имеется работающий http сервер (Apache). Мы можем написать небольшой cgi скрипт, который будет брать выгружаемый из Forms файл и выдавать его содержимое пользователю, предварительно указав, что это файл в формате Excel:

----- Start script --------------------------------------
#!/bin/sh
echo Content-type: application/vnd.ms-excel
echo
cat /tempdata/$1
rm -f /tempdata/$1
----- End script -------------------------

Если назвать такой скрипт exp2xls и положить в каталог $ORACLE_HOME/Apache/Apache/cgi-bin , то вызывать его из Forms можно примерно так:

WEB.SHOW_DOCUMENT('/cgi-bin/exp2xls' || '?' ||
 filename.xls, '_blank');

Те, кто используют Windows в качестве OS для сервера приложений, думаю, без труда смогут переписать этот скрипт в bat или cmd файл. Нужно лишь заменить cat на type и rm -f на del

Имя экспортного файла в такой многопользовательской среде лучше генерировать произвольно. Для этого можно воспользоваться генератором случайных чисел.

Остается лишь позаботиться, чтобы Forms и cgi-скрипт "договорились" куда (каталог) один будет выкладывать файл и откуда второй его будет брать.

А пользовательский браузер понимает, что для показа такой страницы нужно запускать Excel.

Нужно заметить, что у данного способа есть один недостаток. Ведь теоретически, для того, чтобы получить в браузере excel-файл, и, соответственно, получить доступ к данным, необязательно заходить в систему. Можно просто открыть браузер и пытаться подбирать url таким образом, чтобы угадать имя временного файла. Это, конечно, маловероятно, но, тем не менее, стоит подумать, допустим ли для вас такой вариант.

Решение

В принципе, у вас есть уже всё, что необходимо для реализации задуманного. И написать подобную утилиту вы наверняка сможете сами. И возможно даже лучше чем то, что предложено ниже.

Собственно решением является plsql пакет fp_exp с единственной public процедурой to_excel, которая вызывается без всяких параметров, например, в пункте меню "Экспорт в Excel":

BEGIN
   fp_exp.to_excel;
END;

Перед началом использования нужно выполнить предварительную настройку пакета, установив значения константам, находящимся в спецификации пакета.

Утилита изначально разрабатывалась для использования с Forms Server, работающим под Linux. Затем была перенесена в среду Oracle Applications (та же архитектура, но под SUN Solaris). Поэтому приводится вариант пакета для web реализации. Для работы в клиент-сервер, тело процедуры to_excel нужно лишь чуть-чуть подправить, что не должно вызвать у вас затруднений.

PACKAGE fp_exp
/*
-- fp_exp  15-JUL-2003 by pal (Pavel Luzanovv)
-- НАЗНАЧЕНИЕ
--  Экспорт данных текущего блока в MS Excell
-- МОДИФИКАЦИЯ
--  15-JUL-2003 pal  Создание
*/
IS
   -- На что заменять символ табуляции в
 значении элемента формы
   chr9subst_pc CONSTANT VARCHAR2(3) := '   ';
   
   -- На что заменять символ перевода строки
 в значении элемента формы
   chr10subst_pc CONSTANT VARCHAR2(1) := ' ';
   
   -- В каком формате выводить элементы типа даты
   date_format_pc CONSTANT VARCHAR2(8) := 'DD.MM.YY';
   
   -- каталог на сервере приложения, где
 будут создаваться файлы экспорта
   tempdir_pc CONSTANT VARCHAR2(255) := '/oraapp/tempdata/';
   
   -- относительный путь до cgi-скрипта,
 для использования 
   -- в WEB.SHOW_DOCUMENT
   cgi_script_pc CONSTANT VARCHAR2(255) :=
 '/cgi-bin/exp2xls';
   
   PROCEDURE to_excel;
END fp_exp;

PACKAGE BODY fp_exp
IS
   TYPE item_rectype IS RECORD (
      name VARCHAR2(30),
      prompt VARCHAR2(255),
      datatype VARCHAR2(255)
   );
   TYPE item_tabtype IS TABLE OF item_rectype
 INDEX BY BINARY_INTEGER;
   item_tab item_tabtype;
   
   /* --------- LOCAL  MODULES --------- */
   PROCEDURE show_progress (message_in IN VARCHAR2)
   IS
   BEGIN
      MESSAGE(message_in, NO_ACKNOWLEDGE); 
      SYNCHRONIZE;
   END show_progress;
   
   FUNCTION tempfilename RETURN VARCHAR2
   IS
   BEGIN
      RETURN (USER||DBMS_RANDOM.STRING('U', 10));
   END tempfilename;
   
   FUNCTION  setup_items (blk_in IN VARCHAR2)
 RETURN PLS_INTEGER
      /* Определим находящиеся на экране 
столбцы текущего блока */
   IS
      cur_item_v VARCHAR2(30);
      last_item_v VARCHAR2(30);
      index_v PLS_INTEGER := 0;
   BEGIN
      cur_item_v := GET_BLOCK_PROPERTY (blk_in, FIRST_ITEM);
      last_item_v := GET_BLOCK_PROPERTY (blk_in, LAST_ITEM);
      item_tab.DELETE;
      
      LOOP
         IF GET_ITEM_PROPERTY (blk_in||'.'||
cur_item_v, DISPLAYED) = 'TRUE' AND
            GET_ITEM_PROPERTY (blk_in||'.'||
cur_item_v, VISIBLE) = 'TRUE' AND
            GET_ITEM_PROPERTY (blk_in||'.'||
cur_item_v, ITEM_CANVAS) IS NOT NULL AND
            GET_ITEM_PROPERTY (blk_in||'.'||
cur_item_v, ITEM_TYPE) 
               IN ('TEXT ITEM', 'LIST', 
'DISPLAY ITEM','CHECKBOX', 'RADIO GROUP')
         THEN
            index_v := index_v + 1;
            item_tab(index_v).name := 
GET_ITEM_PROPERTY(blk_in||'.'||cur_item_v, ITEM_NAME);
            IF GET_ITEM_PROPERTY (blk_in||
'.'||cur_item_v, ITEM_TYPE) = 'CHECKBOX'
            THEN
               item_tab(index_v).prompt := 
GET_ITEM_PROPERTY(blk_in||'.'||cur_item_v, LABEL);
            ELSE
               item_tab(index_v).prompt := 
GET_ITEM_PROPERTY(blk_in||'.'||cur_item_v, PROMPT_TEXT);
            END IF;
            item_tab(index_v).datatype := 
               GET_ITEM_PROPERTY(blk_in||'.'||
cur_item_v, DATATYPE);
         END IF;
         
         EXIT WHEN cur_item_v = last_item_v;
         cur_item_v := GET_ITEM_PROPERTY (blk_in||
'.'||cur_item_v, NEXTITEM);
      END LOOP;
      
      RETURN (index_v);
   END setup_items;
   
   FUNCTION format_value (
      value_in    IN VARCHAR2,
      datatype_in IN VARCHAR2 DEFAULT NULL
   ) RETURN VARCHAR2
   IS
      retval VARCHAR2(32767) := value_in;
   BEGIN
      IF datatype_in = 'DATE'
      THEN
         retval := TO_CHAR(TO_DATE(retval),
 date_format_pc);
      ELSE
         retval := REPLACE (retval, CHR(9),
 chr9subst_pc);
         retval := REPLACE (retval, CHR(10),
 chr10subst_pc);
         IF SUBSTR(retval, 1, 1) IN 
('-', '+', '=')
         THEN
            retval := ' ' || retval;
         END IF;
      END IF;
      
      RETURN (retval);
   END format_value;
   /* ---------- PUBLIC MODULES ----------- */
   PROCEDURE to_excel
   IS
      cur_block_c CONSTANT VARCHAR2(30)
 := NAME_IN ('SYSTEM.CURSOR_BLOCK');
      cur_item_c CONSTANT VARCHAR2(61) 
:= NAME_IN ('SYSTEM.CURSOR_ITEM');
      cur_record_c CONSTANT VARCHAR2(30) 
:= NAME_IN ('SYSTEM.CURSOR_RECORD');
      outfilename_v VARCHAR2(255);
         -- := 'c:\tmp\'||USER||'.xls';
 -- test in C-S mode
      outfile_v TEXT_IO.FILE_TYPE;
      num_items_v PLS_INTEGER;
      line_v VARCHAR2(32767);
      counter_v PLS_INTEGER := 0;
      
      uncommited_changes EXCEPTION;
   BEGIN
      IF NAME_IN('SYSTEM.FORM_STATUS') <> 'QUERY'
      THEN
         RAISE uncommited_changes;
      END IF;
      
      FIRST_RECORD;
      
      show_progress ('Формирование 
списка полей для экспорта...');
      num_items_v := setup_items 
(cur_block_c);

      show_progress ('Открытие временного файла...');
      -- for C-S mode make adjustments below
      outfilename_v := tempfilename;
      outfile_v := TEXT_IO.FOPEN 
(tempdir_pc||outfilename_v, 'w');

      show_progress ('Формирование 
строки заголовков столбцов...');
      line_v := NULL;
      FOR i IN 1 .. num_items_v
      LOOP
         line_v := line_v || format_value
 (item_tab(i).prompt);
         IF i < num_items_v THEN line_v
 := line_v || CHR(9); END IF;
      END LOOP;
      TEXT_IO.PUT_LINE(outfile_v, line_v);

      LOOP
         IF MOD (counter_v, 100) = 0
         THEN
            show_progress (
               'Форматирование записей блока 
('|| TO_CHAR(counter_v,'99990') 
               || ')...'
            );
         END IF;

         counter_v := counter_v + 1;
         line_v := NULL;
         FOR i IN 1 .. num_items_v
         LOOP
            line_v := line_v || 
               format_value (
                  NAME_IN (cur_block_c||'.
'||item_tab(i).name),
                  item_tab(i).datatype
               );
            IF i < num_items_v THEN line_v 
:= line_v || CHR(9); END IF;
         END LOOP;
         TEXT_IO.PUT_LINE(outfile_v, line_v);
      
         EXIT WHEN NAME_IN('SYSTEM.LAST_RECORD')
 = 'TRUE';
         NEXT_RECORD;
      END LOOP;
      TEXT_IO.FCLOSE(outfile_v);
      item_tab.DELETE;
      GO_RECORD(TO_NUMBER(cur_record_c));
      GO_ITEM (cur_item_c);
   
      show_progress (
         'Всего экспортировано ' ||
 TO_CHAR(counter_v) || ' записей'
      );
      WEB.SHOW_DOCUMENT(cgi_script_pc || 
'?' || outfilename_v, '_blank');
      --host('cmd /C start excel '||
outfilename_v); -- test in C-S mode
   EXCEPTION
      WHEN uncommited_changes
      THEN
         message('Перед выполнеием экспорта, 
сохраните сделанные изменения!');
      WHEN OTHERS
      THEN
         IF TEXT_IO.IS_OPEN(outfile_v) 
THEN TEXT_IO.FCLOSE(outfile_v); END IF;
         RAISE;
   END to_excel;
END fp_exp;

Не забудьте про cgi скрипт!

Приложение

Здесь описаны некоторые советы по возможному дальнейшему совершенствованию предложенного механизма экспорта данных в Excel.

Вместо предложенного формата файла, можно попробовать использовать sylk формат, описание которого можно найти в Интернет. Excel также понимает этот формат, предоставляющий большие возможности по форматированию данных.

В качестве первой строки в файл можно записывать заголовок окна формы. Получится своеобразный заголовок отчета в Excel.

Блок формы может содержать десятки, сотни тысяч записей, а то и больше. В таких случаях выгружать все записи блока - задача не реальная. Для таких случаев можно реализовать ограничитель на количество выгружаемых записей. Т.е. условием окончания записи в файл сделать не только достижение последней записи в блоке, но и дополнительно к этому, достижение максимально возможного количества записей для выгрузки, если таковое установлено.

В качестве значения ячейки можно передавать формулы. Для этого значение должно начинаться с символа "=". В формулах можно использовать арифметические операции и ссылки на ячейки, например вот так =A2+B2+C2. Но почему-то не получается использовать в формулах функции, например =СУММ(A2:A10). В приведенной реализации использование формул запрещено. Если значение начинается с символа "=", то оно дополняется пробелом слева.