How to make Webserver output to Excel

How to make Webserver output to Excel

Post by G?ran Korsgre » Sat, 01 Nov 1997 04:00:00



Goran Korsgren wrote:

> > >Dan Nichols wrote:

> > >> Hi Everyone:

> > >> Using the Webserver PL/SQL agent, it it possible to send the outpu=
t of a
> > >> query to a file?

> With some slight modifications of the OWA_UTIL package you can use it
> to produce files in Excel-format and (depending on browser I assume)
> even have them launch Excel automaticly - the users love it!

> If you want to know details send mail.

> G=F6ran

Hi everyone!

I got so many emails so I think its better I post my solution to the news=
group:

Basicly, the trick is to start the 'page' with the appropriate mime_heade=
r
(see the example
in the end of this mail), and to change the table...-functions in the
OWA_UTIL-package to
output the data in the way you like to present it.

In the declaration file PUBUTIL.SQL (or a renamed copy) where the followi=
ng
paragraph is:
-----------------------------------------
   /* TABLE_TYPE constants */
   HTML_TABLE constant integer :=3D 1;
   PRE_TABLE  constant integer :=3D 2;
-----------------------------------------
add the constant:
-----------------------------------------
   EXCEL_TABLE  constant integer :=3D 3;
-----------------------------------------
or call it something else if you like.

In the procedure-file PRIVUTIL.SQL (or a renamed copy) change the procedu=
res

from tableOpen to tableClose to the following:
(as you can see, in a lot of the procedures, nothing is to be done if
ntable_type is EXCEL_TABLE)
(I know I could have written  'if (ntable_type =3D EXCEL_TABLE) then...' =
 in the
code to make it
more readable, maybe I do some day ...)
-------------------------------------------------------------------------=
-
   procedure tableOpen(cattributes in varchar2 DEFAULT NULL,
                       ntable_type in integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableOpen(NULL, NULL, NULL, NULL, cattributes);
      elsif (ntable_type =3D PRE_TABLE) then
         if (cattributes is not null)
         then
            table_border :=3D '|';
         else
            table_border :=3D ' ';
         end if;
         htp.print('<PRE>');
   end if;
   end;

   procedure tableCaption(ccaption    in varchar2,
                          calign      in varchar2 DEFAULT 'CENTER',
                          ntable_type in integer  DEFAULT HTML_TABLE) is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableCaption(ccaption, calign);
      elsif (ntable_type =3D PRE_TABLE) then
         htp.print(ccaption);
      end if;
   end;

   procedure tableHeaderRowOpen(crowstring  in out varchar2,
                                ntable_type in     integer DEFAULT
HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableRowOpen;
      elsif (ntable_type =3D PRE_TABLE) then
         crowstring :=3D table_border;
      else
         crowstring :=3D '';
   end if;
   end;

   procedure tableHeaderRowOpen(crowstring   in out varchar2,
                                ntable_width    out integer,
                                ntable_type  in     integer DEFAULT
HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableRowOpen;
      elsif (ntable_type =3D PRE_TABLE) then
         ntable_width :=3D 1;
         crowstring :=3D table_border;
       else
         crowstring :=3D '';
     end if;
   end;

   procedure tableHeader(ccolumn_name in     varchar2,
                         ncolumn_size in     integer,
                         calign       in     varchar2 DEFAULT 'CENTER',
                         crowstring   in out varchar2,
                         ntable_type  in     integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableHeader(ccolumn_name);
      elsif (ntable_type =3D PRE_TABLE) then
         crowstring :=3D crowstring||align(ccolumn_name,ncolumn_size,cali=
gn);
      else
  crowstring :=3D crowstring||ccolumn_name||' '; /* append a tab */
      end if;
   end;

   procedure tableHeader(ccolumn_name in     varchar2,
                         ncolumn_size in     integer,
                         calign       in     varchar2 DEFAULT 'CENTER',
                         crowstring   in out varchar2,
                         ntable_width in out integer,
                         ntable_type  in     integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableHeader(ccolumn_name);
      elsif (ntable_type =3D PRE_TABLE) then
         ntable_width :=3D ntable_width+ncolumn_size+3;
         crowstring :=3D crowstring||align(ccolumn_name,ncolumn_size,cali=
gn);
      else
  crowstring :=3D crowstring||ccolumn_name||' '; /* append a tab */
      end if;
   end;

   procedure tableHeaderRowClose(crowstring  in out varchar2,
                                 ntable_type in     integer DEFAULT
HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableRowClose;
      else
         htp.print(crowstring);
      end if;
   end;

   procedure tableHeaderRowClose(crowstring   in out varchar2,
                                 ntable_width in     integer,
                                 ntable_type  in     integer DEFAULT
HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableRowClose;
      elsif (ntable_type =3D PRE_TABLE) then
         if (table_border =3D '|')
         then
            htp.print(rpad('-',ntable_width,'-'));
            htp.print(crowstring);
            htp.print(rpad('-',ntable_width,'-'));
         else
            htp.print(' ');
            htp.print(crowstring);
            htp.print(' ');
         end if;
      else
         htp.print(crowstring);
   end if;
   end;

   procedure tableRowOpen(crowstring  in out varchar2,
                          ntable_type in     integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableRowOpen;
      elsif (ntable_type =3D PRE_TABLE) then
         crowstring :=3D table_border;
      else
         crowstring :=3D '';
      end if;
   end;

   procedure tableData(cdata        in     varchar2,
                       ncolumn_size in     integer,
                       calign       in     varchar2 DEFAULT 'LEFT',
                       crowstring   in out varchar2,
                       ntable_type  in     integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableData(cdata, calign);
      elsif (ntable_type =3D PRE_TABLE) then
         crowstring :=3D crowstring||align(translate(cdata,NL_CHAR,' '),
                                                   ncolumn_size, calign);=

      else
  crowstring :=3D crowstring||cdata||' '; /* append a tab */
   end if;
   end;

   procedure tableNoData(calign       in     varchar2 DEFAULT 'LEFT',
                         crowstring   in out varchar2,
                         nnum_cols    in     integer,
                         ntable_width in     integer,
                         ntable_type  in     integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableData('No data found', ccolspan=3D>nnum_cols);
      elsif (ntable_type =3D PRE_TABLE) then
         crowstring :=3D crowstring||align('No data
found',ntable_width-4,calign);
      else
         crowstring :=3D crowstring||'No data found ';
      end if;
   end;

   procedure tableRowClose(crowstring  in out varchar2,
                           ntable_type in     integer DEFAULT HTML_TABLE)=

    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableRowClose;
      else
         htp.print(crowstring);
      end if;
   end;

   procedure tableClose(ntable_type in     integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableClose;
      elsif (ntable_type =3D PRE_TABLE) then
         htp.print('</PRE>');
      end if;
   end;

   procedure tableClose(ntable_width in integer,
                        ntable_type  in integer DEFAULT HTML_TABLE)
    is
   begin
      if (ntable_type =3D HTML_TABLE)
      then
         htp.tableClose;
      elsif (ntable_type =3D PRE_TABLE) then
         if (table_border =3D '|')
         then
            htp.print(rpad('-',ntable_width,'-'));
         else
            htp.print(' ');
         end if;

         htp.print('</PRE>');
      end if;
   end;
-------------------------------------------------------------------------=
-

And finally in the calling procedure (in this example the user had
the choice to push a button 'Show in Excel' or 'Show in Browser',
and the modified packaged is named OWA_UTIL_MOD):
------------------------------------------------------
   if (display =3D 'Show in Excel') then
          ttype :=3D OWA_UTIL_MOD.EXCEL_TABLE;
          owa_util_mod.mime_header('application/excel');
   else
          ttype :=3D OWA_UTIL_MOD.HTML_TABLE;
          htp.htmlOpen;
  end if;
--------------------------------------------------------
where you use the ttype-variable as argument whereever a Tabletype-argume=
nt
is needed.

Hope this works for you!

G=F6ran