Oracle导出excel数据

推荐阅读：Oracle读取excel数据

oracle导出excel（非csv）的方法有两种，1、使用sqlplus spool，2、使用包体

现将网上相关代码整理后贴出以备不时之需：

使用sqlplus：

使用sqlplus需要两个文件：sql脚本文件和格式设置文件。

去除冗余信息，main.sql

--main.sql 注意，需要在sqlplus下运行 非plsql命令行下
set linesize 200
set term off verify off feedback off pagesize 999 
set markup html on entmap ON spool on preformat off
spool test_tables.xls
@get_tables.sql
spool off
exit 

sql脚本，get_tables.sql

--get_tables.sql
select * from all_objects where rownum<=1000; 

然后在sqlplus下运行main.sql

使用包体编程：

create or replace package smt_xlsx_maker_pkg is

/******************************************************************************
NAME: SMT_XLSX_MAKER_PKG
PURPOSE: XLSX 生成 Pkg，主要是从Oracle数据库端生成Xlsx二进制的文件。

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2011/2/19 Anton Scheffer 1,New Create the pkg
1.1 2015/6/10 Sam.T 1.优化核心处理生成xlsx的代码，使得生成文档的执行效率大大提高！
1.1 2015/6/10 Sam.T 1.query2sheet增加绑定变量的可选输入参数。
1.2 2015/6/15 Sam.T 1.代码增加调试模式。直接设置G_DEBUG_MODE变量即可。
1.2 2015/6/20 Sam.T 1.为方便使用，再次封装一些简单易用的过程，生成xlsx文档
1.2 2015/7/5 Sam.T 1.单条SQL生成xlsx文档的内容，增加是否显示foot，以及显示的行数。
jinzhao 解决数据量较大情况下导出excel报错xml的问题。将dbms_lob.writeappend修改为dbms_lob.append
******************************************************************************/
---------
/**********************************************
******************************************************************************
Copyright (C) 2011, 2012 by Anton Scheffer
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.

******************************************************************************
******************************************** */
/*

使用方法：

declare
l_sql varchar2(30000);
begin
l_sql := 'select * from all_objects';
smt_xlsx_maker_pkg.query2sheet(l_sql,
true,
'XLS_DIR',
'Export2.xlsx');
end;

除此之外，基本上Excel 中有的效果它都可以生成。
以下一个例子，包括居中，合并单元格，底色，新增工作表等：

begin

smt_xlsx_maker_pkg.clear_workbook;
smt_xlsx_maker_pkg.new_sheet;
smt_xlsx_maker_pkg.cell(5, 1, 5);
smt_xlsx_maker_pkg.cell(3, 1, 3);
smt_xlsx_maker_pkg.cell(2, 2, 45);
smt_xlsx_maker_pkg.cell(3,
2,
'Anton Scheffer',
p_alignment => smt_xlsx_maker_pkg.get_alignment(p_wraptext => true));
smt_xlsx_maker_pkg.cell(1,
4,
sysdate,
p_fontid => smt_xlsx_maker_pkg.get_font('Calibri',
p_rgb => 'FFFF0000'));
smt_xlsx_maker_pkg.cell(2,
4,
sysdate,
p_numfmtid => smt_xlsx_maker_pkg.get_numfmt('dd/mm/yyyy h:mm'));
smt_xlsx_maker_pkg.cell(3,
4,
sysdate,
p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(smt_xlsx_maker_pkg.orafmt2excel('dd/mon/yyyy')));
smt_xlsx_maker_pkg.cell(5,
5,
75,
p_borderid => smt_xlsx_maker_pkg.get_border('double',
'double',
'double',
'double'));
smt_xlsx_maker_pkg.cell(2, 3, 33);
smt_xlsx_maker_pkg.hyperlink(1,
6,
'http://www.cnblogs.com/mellowsmile',
'jinzhao site');
smt_xlsx_maker_pkg.cell(1,
7,
'Some merged cells',
p_alignment => smt_xlsx_maker_pkg.get_alignment(p_horizontal => 'center'));
smt_xlsx_maker_pkg.mergecells(1, 7, 3, 7);
for i in 1 .. 5 loop
smt_xlsx_maker_pkg.comment(3, i + 3, 'Row ' || (i + 3), 'Anton');
end loop;
smt_xlsx_maker_pkg.new_sheet;
smt_xlsx_maker_pkg.set_row(1,
p_fillid => smt_xlsx_maker_pkg.get_fill('solid',
'FFFF0000'));
for i in 1 .. 5 loop
smt_xlsx_maker_pkg.cell(1, i, i);
smt_xlsx_maker_pkg.cell(2, i, i * 3);
smt_xlsx_maker_pkg.cell(3, i, 'x ' || i * 3);
end loop;
smt_xlsx_maker_pkg.query2sheet('select rownum, x.*
, case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
, case when mod( rownum, 2 ) = 1 then ''demo '' || rownum end demo2 from dual x connect by rownum <= 5');
smt_xlsx_maker_pkg.save('XLS_DIR', 'Export3.xlsx');
end;
*/
g_debug_mode boolean := false;
---DBMS_OUTPUT直接输出/FILE_OUTPUT文档输出/REQUEST_OUTPUT请求日志输出/CONTEXT_OUTPUT 将日志改为上下文输出
g_debug_type varchar2(240) := 'DBMS_OUTPUT';
---绑定变量用的临时表变量
type rec_col_value is record(
col_id number(3),
col_value varchar2(2400));
type tab_col_value is table of rec_col_value index by binary_integer;
--
type tp_alignment is record(
vertical varchar2(11),
horizontal varchar2(16),
wraptext boolean);
--
procedure clear_workbook;
--
procedure new_sheet(p_sheetname varchar2 := null);
--
function orafmt2excel(p_format varchar2 := null) return varchar2;
--
function get_numfmt(p_format varchar2 := null) return pls_integer;
--
function get_font(p_name varchar2,
p_family pls_integer := 2,
p_fontsize number := 11,
p_theme pls_integer := 1,
p_underline boolean := false,
p_italic boolean := false,
p_bold boolean := false,
p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value
) return pls_integer;
--
function get_fill(p_patterntype varchar2,
p_fgrgb varchar2 := null -- this is a hex ALPHA Red Green Blue value
) return pls_integer;
--
function get_border(p_top varchar2 := 'thin',
p_bottom varchar2 := 'thin',
p_left varchar2 := 'thin',
p_right varchar2 := 'thin')
/*
none
thin
medium
dashed
dotted
thick
double
hair
mediumDashed
dashDot
mediumDashDot
dashDotDot
mediumDashDotDot
slantDashDot
*/
return pls_integer;
--
function get_alignment(p_vertical varchar2 := null,
p_horizontal varchar2 := null,
p_wraptext boolean := null)
/* horizontal
center
centerContinuous
distributed
fill
general
justify
left
right
*/
/* vertical
bottom
center
distributed
justify
top
*/
return tp_alignment;
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value number,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null);
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value varchar2,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls