If you've worked with an application that outputs plain CSV files for users, you may have encountered the issue where Excel will turn any value that looks vaguely numeric into a number cell. Sometimes it just makes the alignment look odd, other times you may lose some leading zeros or trailing zeroes after a 'dot' when you weren't expecting it.
It's not a bug in Excel. Excel deals with spreadsheets which are just a collection of independent cells. Just because a cell is surrounded by numbers doesn't mean it should be a number, or vice versa. It is an annoyance though.
I was working with Apex, and needed a routine to extract some data into Excel. I didn't want this issue to crop up, and I also was looking for a way to have the columns sized automatically. So like any decent Oracle developer looking for a solution, I started at AskTom, and dug up owa_sylk
Then I tried it and found it mucked up with strings and numbers too. In this case it was a to_number test in the PL/SQL causing the problem, and not Excel, so I switched the code to use the col_type from the DBMS_SQL.DESCRIBE.
Unfortunately the SYLK format is under-documented (at least as far as I can Google). I've put a couple of comments in where I have worked out what does what, but if you are going to use it you may have to put some effort in. For example, I added some code to allow for a carriage return in a column header based on making the change in Excel and saving it as a .slk and seeing what it put in. I've no idea whether it will work for other spreadsheet applications, or break them. If you have some spare money, googling sylksheets will turn up an organisation that are willing to explain it.
I'll point out another couple of 'More Excel-than-CSV' solutions from the Apex forums, one being Denes' package using HTML here and an XML based solution here
create or replace
package owa_sylk as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
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 65000,
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_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 65000,
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' );
/*
Copyright (c) 1991, 1996, 1997 by Oracle Corporation
NAME
owasylk.sql - Dump to Spreadsheet with formatting
DESCRIPTION
This package provides an API to generate a file in the
SYLK file format. This allow for formatting in a
spreadsheet with only a ascii text file. This version
of owa_sylk is specific to Oracle8.
NOTES
MODIFIED (MM/DD/YY)
clbeck 04/08/98 - Created.
May/2007 - Use col_type, remove blank header line and 1st col.
*/
--
end owa_sylk;
/
create or replace
PACKAGE BODY owa_sylk AS
--
g_desc_t DBMS_SQL.desc_tab2;
g_lengths owa.vc_arr;
g_sums owa.vc_arr;
--
--
PROCEDURE debug (p_text IN VARCHAR2) IS
BEGIN
null;--For any debugging tracing you may use
END debug;
--
PROCEDURE p( p_str IN VARCHAR2 ) IS
BEGIN
IF p_str IS NOT NULL THEN
htp.p( p_str );
END IF;
EXCEPTION
WHEN OTHERS THEN
debug('Error 'sqlerrm' in p');
RAISE;
END p;
--
PROCEDURE print_comment( p_comment VARCHAR2) IS
--This appears to be a debugging routine to include comment lines in output
BEGIN
debug(p_comment);
RETURN;
p( ';' chr(10) '; ' p_comment chr(10) ';' );
END print_comment;
--
FUNCTION build_cursor(
p_query IN VARCHAR2,
p_variable IN owaSylkArray,
p_bind_val IN owaSylkArray ) RETURN INTEGER
IS
v_cursor INTEGER := DBMS_SQL.OPEN_CURSOR;
i NUMBER := 1;
BEGIN
dbms_sql.parse (v_cursor, p_query, dbms_sql.native);
WHILE i <= p_variable.count LOOP
DBMS_SQL.bind_variable( v_cursor, p_variable(i), p_bind_val(i) );
i := i + 1;
END LOOP;
RETURN v_cursor;
EXCEPTION
WHEN OTHERS THEN
debug('Error '||sqlerrm||' in build_cursor');
RAISE;
END build_cursor;
--
--
FUNCTION str_html ( p_line IN VARCHAR2 ) RETURN VARCHAR2
IS
v_out_line VARCHAR2(32767) := NULL;
v_char VARCHAR2(1);
in_html BOOLEAN := FALSE;
BEGIN
IF p_line IS NULL THEN
RETURN p_line;
END IF;
FOR I IN 1 .. LENGTH( p_line ) LOOP
v_char := substr( p_line, i, 1 );
IF v_char = '>' AND (in_html = TRUE) THEN
in_html := FALSE;
ELSIF v_char = '<' AND (in_html = FALSE) THEN
in_html := TRUE;
END IF;
--
IF NOT in_html AND v_char != '>' then
v_out_line := v_out_line || v_char;
END IF;
END LOOP;
--
RETURN v_out_line;
EXCEPTION
WHEN OTHERS THEN
debug('Error '||sqlerrm||' in str_html');
RAISE;
END str_html;
--
PROCEDURE print_heading(
p_font IN VARCHAR2,
p_grid IN VARCHAR2,
p_col_heading IN VARCHAR2,
p_titles IN owaSylkArray,
p_show_null_as IN VARCHAR2 )
IS
l_title varchar2(2000);
v_line VARCHAR2(32767);
v_header_row_height NUMBER := 1;
BEGIN
p( 'ID;ORACLE' );
print_comment( 'Fonts' );
--Size 10, use M160 for size 8, M240 for size 12 etc
p( 'P;F' p_font ';M200' );
p( 'P;F' p_font ';M200;SB' ); --Style
p( 'P;F' p_font ';M200;SUB' ); --Style Bold Underline
--
print_comment( 'Global Formatting' );
IF upper(p_grid)!='YES' THEN
p('F;G');
END IF;
IF upper(p_col_heading)!='YES' THEN
p('F;H');
END IF;
p(v_line);
FOR i IN 1 .. g_desc_t.COUNT LOOP
--(;F) Format (G) General (0) Zero decimal Places (G) Text left/Numbers right
--(;S) Style (M) ? (0) 1st line of 'P's [because it is zero based]
p( 'F;C' to_char(i) ';FG0G;SM0' );
IF p_titles.exists(i) THEN
l_title := p_titles(i);
ELSE
l_title := g_desc_t(i).col_name;
END IF;
IF instr(l_title,chr(10)) != 0 THEN
v_header_row_height := greatest(v_header_row_height,
(length(l_title)+1) - length(replace(l_title,chr(10))));
END IF;
END LOOP;
--
print_comment( 'Title Row' );
--(;F) Format (G) General (0) Zero decimal Places (C) Centre
--(;S) Style (M) ? (2) 2nd line of 'P's [because it is zero based]
IF v_header_row_height = 1 THEN
p( 'F;R1;FG0C;SDM2' );
ELSIF v_header_row_height = 2 THEN
p( 'F;R1;FG0C;SDM2;M540');
ELSE
p( 'F;R1;FG0C;SDM2;M800');
END IF;
p( 'C;Y1;X1'); --Set default starting point
--Title Lines
FOR i IN 1 .. g_desc_t.COUNT LOOP
g_sums(i) := 0;
IF p_titles.exists(i) THEN
l_title := p_titles(i);
ELSE
l_title := g_desc_t(i).col_name;
END IF;
IF instr(l_title,chr(10)) = 0 THEN
g_lengths(i) := greatest(length(l_title),nvl(length(p_show_null_as),0));
ELSE
g_lengths(i) := greatest(instr(l_title,chr(10)),
length(substr(l_title,instr(l_title,chr(10)))),
nvl(length(p_show_null_as),0));
END IF;
l_title := replace(l_title,chr(10),chr(27)' :');
p( 'F;X' to_char(i));
p( 'C;K"' l_title '"' );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
debug('Error 'sqlerrm' in print_heading');
RAISE;
END print_heading;
--
FUNCTION print_rows(
p_cursor IN INTEGER,
p_max_rows IN NUMBER,
p_sum_columns IN owaSylkArray,
p_show_null_as IN VARCHAR2,
p_strip_html IN VARCHAR2 ) RETURN NUMBER
IS
v_row_cnt NUMBER := 0;
v_line VARCHAR2(32767) := NULL;
n NUMBER;
v_col_value VARCHAR2(32767);
BEGIN
LOOP
EXIT WHEN v_row_cnt >= p_max_rows;
n:= DBMS_SQL.FETCH_ROWS( p_cursor );
EXIT WHEN n = 0;
--
v_row_cnt := v_row_cnt + 1;
print_comment( 'Row ' v_row_cnt );
p( 'C;Y' TO_CHAR(v_row_cnt+1) ); --Add 1 to allow for header row
--
FOR col_seq IN 1 .. g_desc_t.COUNT LOOP
-- Get the value
dbms_sql.column_value( p_cursor, col_seq, v_col_value );
--Remove line breaks and semi-colons
v_col_value := translate( v_col_value, chr(10)chr(9)';', ' ' );
IF upper( p_strip_html ) = 'YES' THEN
v_col_value := str_html( v_col_value );
END IF;
-- Output the value
v_line := 'C;X' to_char(col_seq) ';K';
IF v_col_value IS NULL THEN
v_line := v_line '"'p_show_null_as'"';
ELSIF g_desc_t(col_seq).col_type = 2 THEN --This is a number column
v_line := v_line v_col_value;
ELSE
v_line := v_line '"'v_col_value'"';
END IF;
p( v_line );
-- Keep track of the largest length
g_lengths(col_seq) := greatest( nvl(length(v_col_value),0 ), g_lengths(col_seq) );
-- Keep the sums going 9if required)
IF v_col_value IS NOT NULL
AND p_sum_columns.EXISTS(col_seq) THEN
IF UPPER( p_sum_columns(col_seq)) = 'Y' THEN
BEGIN
--Remove the currency and thousands separators
n := to_number( replace(ltrim(v_col_value,'$'),',') );
g_sums(col_seq) := g_sums(col_seq) + n;
EXCEPTION
WHEN value_error THEN null; --Not a number
END;
END IF;
END IF;
END LOOP;
--
END LOOP;
RETURN v_row_cnt;
EXCEPTION
WHEN OTHERS THEN
debug('Error 'sqlerrm' in print_rows at 'v_row_cnt);
RAISE;
END print_rows;
--
PROCEDURE print_sums(
p_sum_columns in owaSylkArray,
p_row_cnt in number ) IS
BEGIN
IF p_sum_columns.count = 0 THEN
RETURN;
END IF;
--
print_comment( 'Totals Row' );
p( 'C;Y' to_char(p_row_cnt + 4) );
p( 'C;X1;K"Totals:"' );
--
FOR i IN 1 .. p_sum_columns.COUNT LOOP
IF UPPER(p_sum_columns(i)) = 'Y' then
p( 'C;X' to_char(i+1) ';ESUM(R3C:R'
to_char(p_row_cnt+2) 'C)' );
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
debug('Error 'sqlerrm' in print_sums');
RAISE;
END print_sums;
--
PROCEDURE print_widths( p_widths owaSylkArray ) IS
BEGIN
print_comment( 'Format Column Widths' );
p( 'F;W1 1 7' );
FOR i IN 1 .. g_desc_t.COUNT LOOP
IF p_widths.exists(i) THEN
p( 'F;W' to_char(i) ' '
to_char(i) ' '
to_char(to_number(p_widths(i))) );
ELSIF g_sums.exists(i) THEN
p( 'F;W' to_char(i) ' '
to_char(i) ' '
greatest( g_lengths(i), length( g_sums(i) )));
ELSE
p( 'F;W' to_char(i) ' '
to_char(i) ' '
g_lengths(i));
END IF;
END LOOP;
p( 'E' );
EXCEPTION
WHEN OTHERS THEN
debug('Error 'sqlerrm' in print_widths');
RAISE;
END print_widths;
--
PROCEDURE show(
p_cursor IN INTEGER,
p_sum_column IN owaSylkArray DEFAULT owaSylkArray(),
p_max_rows IN NUMBER DEFAULT 65000,
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;
v_val VARCHAR2(32767);
BEGIN
dbms_sql.describe_columns2( p_cursor, l_col_cnt, g_desc_t );
--
FOR i IN 1 .. g_desc_t.COUNT LOOP
dbms_sql.define_column( p_cursor, i, v_val, 32765);
END LOOP;
--
print_heading( p_font_name,
p_show_grid,
p_show_col_headers,
p_titles,
p_show_null_as );
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 );
EXCEPTION
WHEN OTHERS THEN
debug('Error 'sqlerrm' in show (cursor)');
RAISE;
END show;
--
PROCEDURE show(
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 65000,
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_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 );
EXCEPTION
WHEN OTHERS THEN
debug('Error 'sqlerrm' in show (query)');
RAISE;
END show;
--
END owa_sylk;
/
And from Apex, you can call this with a process based on the following PL/SQL block.
create or replace
package owa_sylk as
declare
v_filename varchar2(50);
begin
htmldb_application.g_page_text_generated := true;
wwv_flow.g_excel_format := true;
--
v_filename := 'extract_'to_char(sysdate,'YY_MM_DD')'.slk';
htp.p('Content-type: text/spreadsheet');
htp.p('Content-Disposition: attachment; filename="'v_filename'"');
owa_util.mime_header( 'text/spreadsheet');
--
owa_sylk.show(
p_query =>
'select table_name "Table 'chr(10)'Name", 'chr(10)
' column_id "Column Position", column_name "Column Name", 'chr(10)
' data_type "Data Type" 'chr(10)
' from user_tab_columns order by 1,2');
--
owa_util.http_header_close;
end;
1 comment:
Hi Gary,
the code examples are really hard to read in Firefox 2.0. They appear light gray on white. In IE 7 it looks ok.
Just to let you know
Patrick
Post a Comment