Re: Best way to Extract Data from Oracle into Excel
Date: 17 Feb 2005 02:07:39 -0800
Message-ID: <1108634859.259596.189500_at_l41g2000cwc.googlegroups.com>
[Quoted] I have written a procedure in PL/SQL for Oracle Forms which takes a block and export its columns into an external Excel file. In fact, this file is an HTML with special tags recognized by the MS Office tools. With these tags you can virutally create a sheet with any functionality, event charts. An easy way to find which tag to use is to create you customized Excel file and save it is as "Web Page". Then you can edit the saved file in any text editor to view the generated tags. The below procedure can be adapted to run on the server too.
procedure cp_mwt_export_to_excel is
type t_format_mask is table of varchar2(30) index by binary_integer; type t_item_type is table of varchar2(15) index by binary_integer; type t_data_type is table of varchar2(15) index by binary_integer; type t_multi_line is table of varchar2(5) index by binary_integer; type t_display is table of boolean index by binary_integer; type t_item_id is table of item index by binary_integer; v_format_mask t_format_mask; v_item_type t_item_type; v_data_type t_data_type; v_multi_line t_multi_line; v_display t_display; v_item_id t_item_id; v_char varchar2(4000);
v_number number;
v_date date;
v_list_value varchar2(100); v_prompt_text varchar2(60); v_hint_text varchar2(60); v_label varchar2(60); v_user_url varchar2(50); v_user_directory varchar2(50); v_name varchar2(30); v_filename varchar2(16); v_status varchar2(15); v_visible varchar2(5); v_export_item varchar2(1); v_rows pls_integer; v_columns pls_integer; v_lines pls_integer; v_index pls_integer; v_list_count pls_integer; v_list_index pls_integer; v_length pls_integer; v_type pls_integer; v_null boolean; v_block_id block; v_connection exec_sql.conntype; v_cursor exec_sql.curstype;
v_handle text_io.file_type;
begin
enter;
if form_failure = true then
raise form_trigger_failure;
end if;
v_status := get_block_property('DATA',status);
if v_status = 'NEW' then
cp_mwt_display_error('There is no data to be exported.');
raise form_trigger_failure;
end if;
if v_status <> 'QUERY' then
cp_mwt_display_error('Save changes before exporting.');
raise form_trigger_failure;
end if;
cp_mwt_question('Export','Are you sure you want to export to Excel (maximum of 65,535 records) ?');
v_filename := cf_mwt_generate_filename;
tool_env.getvar('USER_DIRECTORY',v_user_directory);
v_handle :=
text_io.fopen(lower(v_user_directory||'\'||v_filename)||'.xls','W');
text_io.put_line(v_handle,'<html
xmlns:o="urn:schemas-microsoft-com:office:office"');
text_io.put_line(v_handle,'xmlns:x="urn:schemas-microsoft-com:office:excel"');
text_io.put_line(v_handle,'xmlns="http://www.w3.org/TR/REC-html40">');
text_io.put_line(v_handle,'<body>'); text_io.put_line(v_handle,'<table border=1>'); text_io.put_line(v_handle,'<tr>');
v_connection := exec_sql.default_connection;
v_cursor := exec_sql.open_cursor(v_connection);
exec_sql.parse(v_connection,v_cursor,replace(get_block_property('DATA',last_query),'ROWID,'));
v_block_id := find_block('EXPORT');
v_columns := 0;
loop
v_columns := v_columns + 1;
begin
exec_sql.describe_column(v_connection,v_cursor,v_columns,v_name,v_length,v_type);
exception
when exec_sql.invalid_column_number then
v_columns := v_columns - 1;
exit;
end;
v_item_id(v_columns) := find_item('DATA.'||v_name||'_DISPLAY');
if id_null(v_item_id(v_columns)) = true then
v_item_id(v_columns) := find_item('DATA.'||v_name||'_EDIT');
end if;
if id_null(v_item_id(v_columns)) = true then
v_item_id(v_columns) := find_item('DATA.'||v_name);
end if;
if id_null(v_block_id) = false then
v_export_item := name_in('EXPORT.'||v_name);
else
v_export_item := 'Y';
end if;
v_item_type(v_columns) :=
get_item_property(v_item_id(v_columns),item_type);
v_data_type(v_columns) :=
get_item_property(v_item_id(v_columns),datatype);
v_visible := get_item_property(v_item_id(v_columns),visible);
v_prompt_text :=
get_item_property(v_item_id(v_columns),prompt_text);
v_hint_text :=
get_item_property(v_item_id(v_columns),hint_text);
if v_item_type(v_columns) = 'TEXT ITEM' then
v_format_mask(v_columns) :=
get_item_property(v_item_id(v_columns),format_mask);
else
v_format_mask(v_columns) := null;
end if;
if v_hint_text is not null then
v_label := v_hint_text;
else
v_label := v_prompt_text;
end if;
if v_item_type(v_columns) <> 'DISPLAY ITEM' and v_visible =
'TRUE' and v_export_item = 'Y' then
v_display(v_columns) := true;
else
v_display(v_columns) := false;
end if;
if v_display(v_columns) = true then
text_io.put_line(v_handle,'<td bgcolor="#FFFF00"
x:autofilter="all"><b>'||v_label||'</b></td>');
end if;
if v_data_type(v_columns) = 'CHAR' then
if v_item_type(v_columns) = 'TEXT ITEM' then
v_multi_line(v_columns) :=
get_item_property(v_item_id(v_columns),multi_line);
else
v_multi_line(v_columns) := 'FALSE';
end if;
exec_sql.define_column(v_connection,v_cursor,v_columns,v_char,v_length);
end if;
if v_data_type(v_columns) = 'NUMBER' then
if v_format_mask(v_columns) is not null then
v_format_mask(v_columns) :=
replace(v_format_mask(v_columns),'9','#');
v_format_mask(v_columns) :=
replace(v_format_mask(v_columns),'G',',');
v_format_mask(v_columns) :=
replace(v_format_mask(v_columns),'D','.');
end if;
v_multi_line(v_columns) := 'FALSE';
exec_sql.define_column(v_connection,v_cursor,v_columns,v_number);
end if;
if v_data_type(v_columns) in ('DATE','DATETIME') then
v_multi_line(v_columns) := 'FALSE';
exec_sql.define_column(v_connection,v_cursor,v_columns,v_date);
end if;
end loop;
text_io.put_line(v_handle,'</tr>');
v_rows := exec_sql.execute(v_connection,v_cursor);
v_lines := 0;
while exec_sql.fetch_rows(v_connection,v_cursor) > 0 loop
v_lines := v_lines + 1;
text_io.put_line(v_handle,'<tr>');
for v_index in 1..v_columns loop
v_null := false;
if v_data_type(v_index) = 'CHAR' then
exec_sql.column_value(v_connection,v_cursor,v_index,v_char);
if v_char is null then
v_null := true;
end if;
end if;
if v_data_type(v_index) = 'NUMBER' then
exec_sql.column_value(v_connection,v_cursor,v_index,v_number);
if v_number is null then
v_null := true;
end if;
end if;
if v_data_type(v_index) in ('DATE','DATETIME') then
exec_sql.column_value(v_connection,v_cursor,v_index,v_date);
if v_date is null then
v_null := true;
end if;
end if;
if v_display(v_index) = true then
if v_null = true then
text_io.put_line(v_handle,'<td></td>');
else
if v_data_type(v_index) = 'CHAR' then
v_char := replace(v_char,' ',' ');
if v_item_type(v_index) = 'CHECKBOX' then
if v_char in ('Y','YES') then
v_char := 'YES';
else
v_char := 'NO';
end if;
end if;
if v_item_type(v_index) = 'LIST' then
v_list_count :=
get_list_element_count(v_item_id(v_index));
for v_list_index in 1..v_list_count loop
v_list_value :=
get_list_element_value(v_item_id(v_index),v_list_index);
if v_list_value = v_char then
v_char :=
get_list_element_label(v_item_id(v_index),v_list_index);
exit;
end if;
end loop;
end if;
if v_multi_line(v_index) = 'TRUE' then
text_io.put_line(v_handle,'<td
style=''white-space:wrap'' x:str>'||v_char||'</td>');
else
text_io.put_line(v_handle,'<td
style=''white-space:nowrap'' x:str>'||v_char||'</td>');
end if;
end if;
if v_data_type(v_index) = 'NUMBER' then
if v_format_mask(v_index) is not null then
text_io.put_line(v_handle,'<td
style=''mso-number-format:"'||v_format_mask(v_index)||'"''
x:num>'||to_char(v_number)||'</td>');
else
text_io.put_line(v_handle,'<td
x:num>'||to_char(v_number)||'</td>');
end if;
end if;
if v_data_type(v_index) = 'DATE' then
text_io.put_line(v_handle,'<td align=left
style=''mso-number-format:"dd-mmm-yyyy"''
x:num>'||to_char(v_date,'DD-MON-YYYY')||'</td>');
end if;
if v_data_type(v_index) = 'DATETIME' then
text_io.put_line(v_handle,'<td align=left
style=''mso-number-format:"dd-mmm-yyyy hh:mm:ss"''
x:num>'||to_char(v_date,'DD-MON-YYYY HH24:MI:SS')||'</td>');
end if;
end if;
end if;
end loop;
text_io.put_line(v_handle,'</tr>');
exit when v_lines = 65535;
end loop;
exec_sql.close_cursor(v_cursor);
exec_sql.close_connection(v_connection);
text_io.put_line(v_handle,'</table>'); text_io.put_line(v_handle,'</body>'); text_io.put_line(v_handle,'</html>');
text_io.fclose(v_handle);
if v_lines > 500 then
host('pkzip -add -silent
'||lower(v_user_directory||'\'||v_filename)||'.zip
'||lower(v_user_directory||'\'||v_filename)||'.xls');
host('del '||lower(v_user_directory||'\'||v_filename)||'.xls'); end if;
cp_mwt_display_message(to_char(v_lines)||' records were successfully exported - the file will now be downloaded.');
tool_env.getvar('USER_URL',v_user_url);
if v_lines > 500 then
web.show_document(v_user_url||lower(v_filename)||'.zip','_blank');
else
web.show_document(v_user_url||lower(v_filename)||'.xls','_blank');
end if;
end; Received on Thu Feb 17 2005 - 11:07:39 CET