美洲杯波胆_美洲杯波胆赔率
做最好的网站
来自 计算机教程 2020-05-02 18:42 的文章
当前位置: 美洲杯波胆 > 计算机教程 > 正文

Oracle导出excel数据男篮世界杯波胆:

男篮世界杯波胆,推荐阅读:Oracle读取excel数据

oracle导出excel(非csv)的方法有两种,1、使用sqlplus  spool,2、使用包体 现将网上相关代码整理后贴出以备不时之需:

oracle导出excel的方法有两种,1、使用sqlplus spool,2、使用包体

使用sqlplus:

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

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

使用sqlplus:

去除冗余信息,main.sql

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

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

sql脚本,get_tables.sql

去除冗余信息,main.sql

--get_tables.sqlselect * from all_objects where rownum<=1000;
--main.sql 注意,需要在sqlplus下运行 非plsql命令行下set linesize 200set term off verify off feedback off pagesize 999 set markup html on entmap ON spool on preformat offspool test_tables.xls@get_tables.sqlspool offexit 

然后在sqlplus下运行main.sql

sql脚本,get_tables.sql

使用包体编程:

--get_tables.sqlselect * from all_objects where rownum=1000; 

 

然后在sqlplus下运行main.sql

男篮世界杯波胆 1男篮世界杯波胆 2

使用包体编程:

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报错*/ --------- /********************************************** ****************************************************************************** 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_integer := null); -- procedure cell(p_col    pls_integer,         p_row    pls_integer,         p_value   date,         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 hyperlink(p_col  pls_integer,           p_row  pls_integer,           p_url  varchar2,           p_value varchar2 := null,           p_sheet pls_integer := null); -- procedure comment(p_col  pls_integer,          p_row  pls_integer,          p_text  varchar2,          p_author varchar2 := null,          p_width pls_integer := 150 -- pixels          ,          p_height pls_integer := 100 -- pixels          ,          p_sheet pls_integer := null); -- procedure mergecells(p_tl_col pls_integer -- top left           ,            p_tl_row pls_integer,            p_br_col pls_integer -- bottom right           ,            p_br_row pls_integer,            p_sheet pls_integer := null); -- procedure list_validation(p_sqref_col  pls_integer,              p_sqref_row  pls_integer,              p_tl_col   pls_integer -- top left              ,              p_tl_row   pls_integer,              p_br_col   pls_integer -- bottom right              ,              p_br_row   pls_integer,              p_style    varchar2 := 'stop' -- stop, warning, information              ,              p_title    varchar2 := null,              p_prompt   varchar := null,              p_show_error boolean := false,              p_error_title varchar2 := null,              p_error_txt  varchar2 := null,              p_sheet    pls_integer := null); -- procedure list_validation(p_sqref_col  pls_integer,              p_sqref_row  pls_integer,              p_defined_name varchar2,              p_style    varchar2 := 'stop' -- stop, warning, information              ,              p_title    varchar2 := null,              p_prompt    varchar := null,              p_show_error  boolean := false,              p_error_title varchar2 := null,              p_error_txt  varchar2 := null,              p_sheet    pls_integer := null); -- procedure defined_name(p_tl_col   pls_integer -- top left            ,             p_tl_row   pls_integer,             p_br_col   pls_integer -- bottom right            ,             p_br_row   pls_integer,             p_name    varchar2,             p_sheet   pls_integer := null,             p_localsheet pls_integer := null); -- procedure set_column_width(p_col  pls_integer,               p_width number,               p_sheet pls_integer := null); -- procedure set_column(p_col    pls_integer,            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 set_row(p_row    pls_integer,          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 freeze_rows(p_nr_rows pls_integer := 1,            p_sheet  pls_integer := null); -- procedure freeze_cols(p_nr_cols pls_integer := 1,            p_sheet  pls_integer := null); -- procedure freeze_pane(p_col  pls_integer,            p_row  pls_integer,            p_sheet pls_integer := null); -- procedure set_autofilter(p_column_start pls_integer := null,              p_column_end  pls_integer := null,              p_row_start  pls_integer := null,              p_row_end   pls_integer := null,              p_sheet    pls_integer := null); -- function finish return blob; -- procedure save(p_directory varchar2, p_filename varchar2); -- ---当p_footer设定参数为真,这个g_query2sheet_footer有值,则会显示这个值的内容。没有则默认是Generated xxxx ---有一点说明的是,&ROWS 字段会被自动替换为结果返回的行数。 g_query2sheet_footer varchar2(1000); g_query2sheet_rows  number; --结果返回的是多少行记录 --这个是这个程序的"完全体" procedure query2sheet(p_sql      varchar2,            p_col_value_tab smt_xlsx_maker_pkg.tab_col_value ---运行的动态SQL的绑定变量            ,            p_column_headers boolean := true,            p_directory   varchar2 := null,            p_filename    varchar2 := null,            p_sheet     pls_integer := null,            p_footer     boolean := true,            x_retcode    out number ---0:成功 非0:失败( 或者:0:成功 1:警告  2:错误 ----注意:确定警告的时候要做什么动作)            ,            x_errbuf     out varchar2 ---具体的错误信息            ); ---默认用上绑定变量的逻辑! procedure query2sheet(p_sql      varchar2,            p_column_headers boolean := true,            p_directory   varchar2 := null,            p_filename    varchar2 := null,            p_sheet     pls_integer := null,            p_footer     boolean := true,            x_retcode    out number ---0:成功 非0:失败( 或者:0:成功 1:警告  2:错误 ----注意:确定警告的时候要做什么动作)            ,            x_errbuf     out varchar2 ---具体的错误信息            ); ---最简单的使用版本 procedure query2sheet(p_sql      varchar2,            p_column_headers boolean := true,            p_directory   varchar2 := null,            p_filename    varchar2 := null,            p_sheet     pls_integer := null,            p_footer     boolean := true); ---参考老外的,另外封装的一个好用的过程!游标直接输出excel。未测试过~ procedure cursor2sheet(p_sql      in sys_refcursor,             p_column_headers boolean := true,             p_directory   varchar2 := null,             p_filename    varchar2 := null,             p_sheet     pls_integer := null,             p_footer     boolean := true); --end;
create or replace package smt_xlsx_maker_pkg is/******************************************************************************NAME: SMT_XLSX_MAKER_PKGPURPOSE: XLSX 生成 Pkg,主要是从Oracle数据库端生成Xlsx二进制的文件。REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 2011/2/19 Anton Scheffer 1,New Create the pkg1.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 SchefferPermission is hereby granted, free of charge, to any person obtaining a copyof this software and associated documentation files (the "Software"), to dealin the Software without restriction, including without limitation the rightsto use, copy, modify, merge, publish, distribute, sublicense, and/or sellcopies of the Software, and to permit persons to whom the Software isfurnished to do so, subject to the following conditions:The above copyright notice and this permission notice shall be included inall copies or substantial portions of the Software.THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS ORIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THEAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHERLIABILITY, 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 INTHE SOFTWARE.************************************************************************************************************************** *//*

smt_xlsx_maker_pkg 包头部分

本文由美洲杯波胆发布于计算机教程,转载请注明出处:Oracle导出excel数据男篮世界杯波胆:

关键词: