Home » Developer & Programmer » Forms » Generating queried data into Excel from Forms
| Generating queried data into Excel from Forms [message #77899] |
Mon, 10 December 2001 23:39  |
Iana
Messages: 6
Registered: December 2001
|
Junior Member |
|
|
Besides generating to CSV format, is there another way that I can put data in Excel during runtime in Forms5? And at the same time, format it with borders or customize the column width of the Excel file. Just in case, there is a possible solution for this.
----------------------------------------------------------------------
|
|
|
|
| Re: Generating queried data into Excel from Forms [message #77903 is a reply to message #77899] |
Tue, 11 December 2001 19:02  |
waris
Messages: 115
Registered: November 2001
|
Senior Member |
|
|
Hi Iana,
Please go thru this Fallowing code..i think this gets u going...
There are more
then one format we can use to write an excel file -- from CSV to
SYLK. I will demonstrate the SYLK format as I already have the
code and it offers the ability to do much fancier stuff like
fonts, headings, formulas and such.
We will use UTL_FILE (see the supplied packages guide for setup
info on that package. You need an init.ora parameter set for
this to work correctly). UTL_FILE allows us to write a file on
the server and since your workstation = server, this should work
nicely for you.
Here is the code with an example. It should get you going:
Rem
Rem $Id$
Rem
Rem Copyright (c) 1991, 1996, 1997 by Oracle Corporation
Rem NAME
Rem owasylk.sql - Dump to Spreadsheet with formatting
Rem DESCRIPTION
Rem This package provides an API to generate a file in the
Rem SYLK file format. This allow for formatting in a
Rem spreadsheet with only a ascii text file. This version
Rem of owa_sylk is specific to Oracle8.
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem clbeck 04/08/98 - Created.
Rem tkyte 09/10/00 - Made it use UTL_FILE.
Rem
Rem
/*
This package allows you to send the results of any query to
a spreadsheet using UTL_FILE
parameters:
p_query - a text string of the query. The query
can be parameterized
using the :VARAIBLE syntax. See example
below.
p_parm_names - an owaSylkArray of the paramter names
used as bind variables in p_query
p_parm_values - an owaSylkArray of the values of the
bind variable names. The values
muse reside in the same index as the
name it corresponds to.
p_cursor - an open cursor that has had the query
parsed already.
p_sum_column - a owaSylkArray of 'Y's and 'N's
corresponding to the location
of the columns selected in p_query.
A value of NYNYY will result
in the 2nd, 4th and 5th columns being
summed in the resulting
spreadsheet.
p_max_rows - the maxium number of row to return.
p_show_null_as - how to display nulls in the spreadsheet
p_show_grid - show/hide the grid in the spreadsheet.
p_show_col_headers - show/hide the row/column headers
in the spreadsheet.
p_font_name - the name of the font
p_widths - a owaSylkArray of column widths. This
will override the default column widths.
p_headings - a owaSylkArray of column titles.
This will override the default column
titles.
p_strip_html - this will remove the HTML tags from the
results before
displaying them in the spreadsheet cells.
Useful when the
query selects an anchor tag. Only the
text between <a href>
and [/url] tags will be sent to the
spreadsheet.
examples:
This example will create a spreadsheet of all the MANAGERS
in the scott.emp table and will sum up the salaries
and commissions for them. No grid will be in the
spreadsheet.
declare
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:temp', 'emp1.slk', 'w',32000
);
owa_sylk.show(
p_file => output,
p_query => 'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = :JOB ' ||
'and sal > :SAL',
p_parm_names =>
owa_sylk.owaSylkArray( 'JOB', 'SAL'),
p_parm_values =>
owa_sylk.owaSylkArray( 'MANAGER', '2000' ),
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
p_show_grid => 'NO' );
utl_file.fclose( output );
end;
This example will create the same spreadsheet but will
send in a pre-parsed cursor instead
declare
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:temp', 'emp2.slk', 'w',32000
);
dbms_sql.parse( l_cursor,
'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = ''MANAGER'' ' ||
'and sal > 2000',
dbms_sql.native );
owa_sylk.show(
p_file => output ,
p_cursor => l_cursor,
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
p_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;
*/
create or replace
package owa_sylk as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' );
--
procedure show(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' );
--
end owa_sylk;
/
show error
create or replace
package body owa_sylk as
--
g_cvalue varchar2(32767);
g_desc_t dbms_sql.desc_tab;
type vc_arr is table of varchar2(2000) index by
binary_integer;
g_lengths vc_arr;
g_sums vc_arr;
--
--
g_file utl_file.file_type;
procedure p( p_str in varchar2 )
is
begin
utl_file.put_line( g_file, p_str );
exception
when others then null;
end;
function build_cursor(
q in varchar2,
n in owaSylkArray,
v in owaSylkArray ) return integer is
c integer := dbms_sql.open_cursor;
i number := 1;
begin
dbms_sql.parse (c, q, dbms_sql.native);
loop
dbms_sql.bind_variable( c, n(i), v(i) );
i := i + 1;
end loop;
return c;
exception
when others then
return c;
end build_cursor;
--
--
function str_html ( line in varchar2 ) return varchar2 is
x varchar2(32767) := null;
in_html boolean := FALSE;
s varchar2(1);
begin
if line is null then
return line;
end if;
for i in 1 .. length( line ) loop
s := substr( line, i, 1 );
if in_html then
if s = '>' then
in_html := FALSE;
end if;
else
if s = '<' then
in_html := TRUE;
end if;
end if;
if not in_html and s != '>' then
x := x || s;
end if;
end loop;
return x;
end str_html;
--
function ite( b boolean,
t varchar2,
f varchar2 ) return varchar2 is
begin
if b then
return t;
else
return f;
end if;
end ite;
--
procedure print_comment( p_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || p_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray )
is
l_title varchar2(2000);
begin
p( 'ID;ORACLE' );
print_comment( 'Fonts' );
p( 'P;F' || font || ';M200' );
p( 'P;F' || font || ';M200;SB' );
p( 'P;F' || font || ';M200;SUB' );
--
print_comment( 'Global Formatting' );
p( 'F;C1;FG0R;SM1' ||
ite( upper(grid)='YES', '', ';G' ) ||
ite( upper(col_heading)='YES', '', ';H' ) );
for i in 1 .. g_desc_t.count loop
p( 'F;C' || to_char(i+1) || ';FG0R;SM0' );
end loop;
--
print_comment( 'Title Row' );
p( 'F;R1;FG0C;SM2' );
for i in 1 .. g_desc_t.count loop
g_lengths(i) := g_desc_t(i).col_name_len;
g_sums(i) := 0;
begin
l_title := titles(i);
exception
when others then
l_title := g_desc_t(i).col_name;
end;
if i = 1 then
p( 'C;Y1;X2;K"' || l_title || '"' );
else
p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
end if;
end loop;
end print_heading;
--
function print_rows(
c in integer,
max_rows in number,
sum_columns in owaSylkArray,
show_null_as in varchar2,
strip_html in varchar2 ) return number is
row_cnt number := 0;
line varchar2(32767) := null;
n number;
begin
loop
exit when ( row_cnt >= max_rows or
dbms_sql.fetch_rows( c ) <= 0 );
row_cnt := row_cnt + 1;
print_comment( 'Row ' || row_cnt );
--
p( 'C;Y' || to_char(row_cnt+2) );
for i in 1 .. g_desc_t.count loop
dbms_sql.column_value( c, i, g_cvalue );
g_cvalue := translate( g_cvalue,
chr(10)||chr(9)||';', ' ' );
g_cvalue := ite( upper( strip_html ) = 'YES',
str_html( g_cvalue ),
g_cvalue );
g_lengths(i) := greatest( nvl(length(g_cvalue),
nvl(length(show_null_as),0)),
g_lengths(i) );
line := 'C;X' || to_char(i+1);
line := line || ';K';
begin
n := to_number( g_cvalue );
if upper( sum_columns(i)) = 'Y' then
g_sums(i) := g_sums(i) + nvl(n,0);
end if;
exception
when others then
n := null;
end;
line := line ||
ite( n is null,
ite( g_cvalue is null,
'"'||show_null_as||
'"', '"'||g_cvalue||'"' ),
n );
p( line );
end loop;
--
end loop;
return row_cnt;
end print_rows;
--
procedure print_sums(
sum_columns in owaSylkArray,
row_cnt in number ) is
begin
if sum_columns.count = 0 then
return;
end if;
--
print_comment( 'Totals Row' );
p( 'C;Y' || to_char(row_cnt + 4) );
p( 'C;X1;K"Totals:"' );
--
for i in 1 .. g_desc_t.count loop
begin
if upper(sum_columns(i)) = 'Y' then
p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' ||
to_char(row_cnt+2) || 'C)' );
end if;
exception
when others then
null;
end;
end loop;
end print_sums;
--
procedure print_widths( widths owaSylkArray ) is
begin
print_comment( 'Format Column Widths' );
p( 'F;W1 1 7' );
for i in 1 .. g_desc_t.count loop
begin
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
to_char(to_number(widths(i))) );
exception
when others then
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
greatest( g_lengths(i), length( g_sums(i) )));
end;
end loop;
p( 'E' );
end print_widths;
--
procedure show(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
g_file := p_file;
dbms_sql.describe_columns( p_cursor, l_col_cnt, g_desc_t );
--
for i in 1 .. g_desc_t.count loop
dbms_sql.define_column( p_cursor, i, g_cvalue, 32765);
end loop;
--
print_heading( p_font_name,
p_show_grid,
p_show_col_headers,
p_titles );
l_status := dbms_sql.execute( p_cursor );
l_row_cnt := print_rows(
p_cursor,
p_max_rows,
p_sum_column,
p_show_null_as,
p_strip_html );
print_sums( p_sum_column, l_row_cnt );
print_widths( p_widths );
end show;
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' ) is
begin
show( p_file => p_file,
p_cursor => build_cursor( p_query,
p_parm_names,
p_parm_values ),
p_sum_column => p_sum_column,
p_max_rows => p_max_rows,
p_show_null_as => p_show_null_as,
p_show_grid => p_show_grid,
p_show_col_headers => p_show_col_headers,
p_font_name => p_font_name,
p_widths => p_widths,
p_titles => p_titles,
p_strip_html => p_strip_html );
end show;
--
end owa_sylk;
/
show error
Cheers
waris
----------------------------------------------------------------------
|
|
|
|
| Re: Generating queried data into Excel from Forms [message #77926 is a reply to message #77899] |
Mon, 17 December 2001 03:10 |
RajuKVG
Messages: 13
Registered: December 2001
|
Junior Member |
|
|
U can use OLE package to do this Job. which have set of functions to specify which row and which column to be posted. this can also be solved using Text_IO package. use this package and send the database data into a file whose extension is '.csv'. by doing this we can open the passed data to the file using a excel sheet.Still have some problem with it mail me. i shall send a sample program.
----------------------------------------------------------------------
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 31 20:38:58 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
