Gerar planilha excel

fwein · Seg, 20 Mar 2006 8:00 am

Pessoal, preciso gerar um planilha excel a partir de uma query. Quero fazer algo do tipo que pl/sql developer faz. Você executa uma query pressiona o botão de exportar para o excel. A planilha é aberta com as colunas devidamente formatadas.
vocês podem me dar um ajuda?

Seg, 20 Mar 2006 3:53 pm

Depende...você quer fazer isso uma só vez?
Ou é uma rotina que o usuário clica um botão e o programa gera o arquivo?

Aqui tem um link que explica como fazer isso no sql*plus, por exemplo:
http://www.glufke.net/oracle/viewtopic.php?t=76

fwein · Seg, 20 Mar 2006 4:09 pm

Uma rotina que ao pressionar um botão abre o excel e exibe as colunas.

Seg, 20 Mar 2006 5:09 pm

Se for no FORMS Cliente Servidor, você usa TEXT_IO pra criar um arquivo delimitado com ponto-e-vírgula, por exemplo... Crie ele com extensão CSV, daí o excel abre numa boa.

Se for no FORMS WEB, terá que usar a WEB_UTIL pra fazer isso.

Basicamente, é essa a idéia: Gerar um TXT delimitado.

Zida · Ter, 28 Mar 2006 9:24 pm

Melhor ainda, você pode usar a biblioteca DDE e dizer em qual coluna, qual linha você quer colocar tal texto em tal formato. Com isso você cria o seu excel em tempo de execucao exatametno como o pl/sql developer faz ... você cria o arquivo, edita ele e depois de tudo pronto mostra o resultado para seu usuario.

Na empresa em que trabalho nós criamos uma pack que por ela é possivel criar um arquivo excel ou ler um arquivo ...

passe o nome do arquivo sem extensao...

Selecionar tudo

	PROCEDURE ABRE_ARQUIVO(V_NM_DIRETORIO IN VARCHAR2,
	                       V_NM_ARQUIVO   IN VARCHAR2,
	                       V_TP_OPERACAO  IN VARCHAR2 DEFAULT 'W',
	                       I_MENSAGEM    OUT VARCHAR2) IS
  --------------------------------------------------------------------
  ---- PROCESSOS DE INICIALIZAÇÃO DO ARQUIVO DO EXCEL
  --------------------------------------------------------------------
  BEGIN
		IF NVL(V_TP_OPERACAO, 'W') = 'R' THEN /* SE FOR LEITURA */
		  V_NM_DESTINO := V_NM_DIRETORIO||V_NM_ARQUIVO||'.XLS';			
			CONV_ESTABLISHED := FALSE;
			V_DS_CLASSE := win_api_environment.read_registry('HKEY_LOCAL_MACHINE\SOFTWARE\Classes\.xls',null, FALSE);
		  V_DS_LOCAL  := win_api_environment.read_registry('HKEY_CLASSES_ROOT\'||V_DS_CLASSE||'\shell\open\command',null, FALSE);
			AppID := DDE.App_Begin (V_DS_LOCAL,DDE.App_Mode_minimized);
			WHILE NOT CONV_ESTABLISHED LOOP
				BEGIN
		    	DOCID := DDE.INITIATE('EXCEL','SYSTEM');
			  	CONV_ESTABLISHED := TRUE;
			  EXCEPTION
		 			WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
			   		CONV_ESTABLISHED := FALSE;
			  END;
			END LOOP;
			DDE.Execute(docID, '[Open("'||V_NM_DESTINO||'")]',10000);
			docid := DDE.INITIATE('EXCEL',V_NM_DESTINO);
		ELSIF NVL(V_TP_OPERACAO, 'W') = 'W' THEN /* SE FOR ESCRITA */
		  V_NM_DESTINO := V_NM_DIRETORIO||V_NM_ARQUIVO||'.XLS';
			conv_established := FALSE;
			-- busca local que o EXCEL esta instalado na maquina
			V_DS_CLASSE := win_api_environment.read_registry('HKEY_LOCAL_MACHINE\SOFTWARE\Classes\.xls',null, FALSE);
		  V_DS_LOCAL  := win_api_environment.read_registry('HKEY_CLASSES_ROOT\'||V_DS_CLASSE||'\shell\open\command',null, FALSE);
		  --inicia o excel
			AppID := DDE.App_Begin (V_DS_LOCAL,DDE.App_Mode_minimized/*maximized*/);
		  -- estabilizando a conexão
			WHILE NOT conv_established LOOP
				BEGIN
		    	docid := DDE.INITIATE('EXCEL','SYSTEM');
			  	conv_established := TRUE;
			  EXCEPTION
		 			WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
			   		conv_established := FALSE;
			  END;
			END LOOP;
		  -- abrindo novo arquivo do Excel
			DDE.Execute(docID, '[new]',10000);
		  -- salvando novo arquivo
			DDE.Execute(docID, '[Save.As("'||V_NM_DESTINO||'",1)]',10000);
		  -- iniciando arquivo com o novo nome
			docid := DDE.INITIATE('EXCEL',V_NM_DESTINO);
		END IF;
	EXCEPTION
		WHEN DDE.DDE_APP_FAILURE THEN
			I_MENSAGEM := 'WORD FOR WINDOWS CANNOT START.';
	  WHEN DDE.DDE_PARAM_ERR THEN
			I_MENSAGEM := 'Um Valor Nulo Foi Passado Ao DDE';
	  WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
			I_MENSAGEM := 'O DDE Não Pode Estabelecer Uma Conversação';
	  WHEN DDE.DMLERR_NOTPROCESSED THEN
			I_MENSAGEM := 'Ocorreu Falha Na Transação ';
		WHEN OTHERS THEN
			I_MENSAGEM := 'ERRO: '||SQLERRM;
		  DDE.TERMINATE(docid);
			dde.app_end(appid);
  END;

	PROCEDURE LE_LINHA (V_NR_COLUNA IN NUMBER, 
                      V_NR_LINHA  IN NUMBER, 
										  V_DS_DADO   OUT VARCHAR2,                      
                      I_MENSAGEM  OUT VARCHAR2) IS
	BEGIN
/*lembrando q se o excel for em ingles no lugar o 'L', deve se usar 'R' de row*/
		DDE.Request (docID, REPLACE('L'||V_NR_LINHA||'C'||V_NR_COLUNA,' '), V_DS_DADO, DDE.Cf_Text,  100000);
	EXCEPTION
		WHEN DDE.DDE_APP_FAILURE THEN
			I_MENSAGEM := 'WORD FOR WINDOWS CANNOT START.';
	  WHEN DDE.DDE_PARAM_ERR THEN
			I_MENSAGEM := 'Um Valor Nulo Foi Passado Ao DDE';
	  WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
			I_MENSAGEM := 'O DDE Não Pode Estabelecer Uma Conversação';
	  WHEN DDE.DMLERR_NOTPROCESSED THEN
			I_MENSAGEM := 'Ocorreu Falha Na Transação ';
		WHEN OTHERS THEN
			I_MENSAGEM := 'ERRO: '||SQLERRM;
		  DDE.TERMINATE(docid);
			dde.app_end(appid);
	END;
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	PROCEDURE GRAVA_LINHA(V_NR_COLUNA IN NUMBER, 
	                      V_NR_LINHA  IN NUMBER, 
	                      V_DS_ARQ    IN VARCHAR2,
	                      I_MENSAGEM  OUT VARCHAR2) IS
	BEGIN
/*lembrando q se o excel for em ingles no lugar o 'L', deve se usar 'R' de row*/
    DDE.POKE(DOCID,REPLACE('L'||V_NR_LINHA||'C'||V_NR_COLUNA,' '),V_DS_ARQ,DDE.CF_TEXT,10000);	  

	EXCEPTION
		WHEN DDE.DDE_APP_FAILURE THEN
			I_MENSAGEM := 'WORD FOR WINDOWS CANNOT START.';
	  WHEN DDE.DDE_PARAM_ERR THEN
			I_MENSAGEM := 'Um Valor Nulo Foi Passado Ao DDE';
	  WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
			I_MENSAGEM := 'O DDE Não Pode Estabelecer Uma Conversação';
	  WHEN DDE.DMLERR_NOTPROCESSED THEN
			I_MENSAGEM := 'Ocorreu Falha Na Transação ';
		WHEN OTHERS THEN
			I_MENSAGEM := 'ERRO: '||SQLERRM;
		  DDE.TERMINATE(docid);
			dde.app_end(appid);
  END; 
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	--------------------------------------------------------------------------------------
	PROCEDURE FECHA_ARQUIVO(I_MENSAGEM  OUT VARCHAR2,
													V_TP_OPERACAO IN VARCHAR2 DEFAULT 'W') IS
  --------------------------------------------------------------------
	-- Comandos para o Encerramento do Arquivo
	--------------------------------------------------------------------
  BEGIN
		IF NVL(V_TP_OPERACAO,'W') = 'W' THEN /* SE FOR PARA ESCRITA */
		  -- SALVA ARQUIVO
		  DDE.Execute(docID, '[Save]',10000);
			-- TERMINA A CONEXÃO
		  DDE.TERMINATE(docID);
		  -- FECHA O ARQUIVO
			dde.app_end(appid);
		  -- EXECUTA O ARQUIVO
		  win_api_shell.winexec(V_DS_LOCAL||' '||V_NM_DESTINO,WIN_API.SW_SHOWmaximized,TRUE);
		ELSIF NVL(V_TP_OPERACAO,'W') = 'R' THEN /* SE FOR PARA LEITURA */
		  DDE.TERMINATE(docID);
		  -- FECHA O ARQUIVO
			dde.app_end(appid);
		END IF;
	EXCEPTION
		WHEN DDE.DDE_APP_FAILURE THEN
			I_MENSAGEM := 'WORD FOR WINDOWS CANNOT START.';
	  WHEN DDE.DDE_PARAM_ERR THEN
			I_MENSAGEM := 'Um Valor Nulo Foi Passado Ao DDE';
	  WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
			I_MENSAGEM := 'O DDE Não Pode Estabelecer Uma Conversação';
	  WHEN DDE.DMLERR_NOTPROCESSED THEN
			I_MENSAGEM := 'Ocorreu Falha Na Transação ';
		WHEN OTHERS THEN
			I_MENSAGEM := 'ERRO: '||SQLERRM;
		  DDE.TERMINATE(docid);
			dde.app_end(appid);
  END;

ricardorauber · Qui, 22 Jun 2006 12:02 am

existe um jeito muito simples que é salvar em formatação HTML mas com a extensão XLS que o excel entende perfeitamente.

Obs: quando o arquivo é modificado e salvo, o excel transforma em formatação XML

Zida · Qui, 22 Jun 2006 10:15 pm

Bom, ai é questao de gosto, se você prefere salvar em modo html(ou se entende mais de html do que xls), sem ter controle do que esta fazendo ... boa sorte ...

.

Aqui você controla qualquer coluna/linha da sua planilha.
não vejo modo mais simples de se implementar usando HTML para o necessário que nós tivemos de usar. Mas cada caso é um caso ..

Flow
Zida.

prata · Qui, 10 Ago 2006 8:52 am

Bom dia a todos!
Estou com um problema parecido, mas com a diferença que quero a partir de uma planilha em excel gravar os dados da planilha numa tabela no oracle. Usei o código exemplo parecido com o que foi postado, mas no meu caso não funicona, dá erro no comando DDE.INITIATE. Alguém poderia me ajudar?Eu tenho que instalar alguma coisa do pacote DDE? Ou não precisa?
Abraço a todos.

vindalencio · Qua, 16 Ago 2006 9:11 am

Caro ZIDA, se puder mandar a PACKAGE completa, do seu exemplo eu agradeço, pois tem muitas váriáveis que não estão declaradas..

Eu gostaria de fazer alguns testes..

Flow...

bertosro · Sex, 18 Ago 2006 11:18 am

Selecionar tudo

DECLARE
	OUT_FILE   TEXT_IO.FILE_TYPE;
	LINEBUF    VARCHAR2(1000);
	
	CURSOR NOTA_FISCAL IS
		SELECT A.cod_dep_ex||' '||B.des LOCALIDADE,
       	   A.cod_trn||' '||C.rzo_soc TRANSP,
       		 A.agrp_cod CARREGAMENTO,
       		 A.num NF,
       		 A.ser SERIE_NF,
       		 TO_CHAR(A.dat_sda,'DD/MM/YYYY') DATA_SAIDA_NF,
       		 A.rza_soc CLIENTE, 
       		 A.uf_etg UF_CLI,
       		 A.cid_etg CID_ENTREGA,
       		 TO_CHAR(A.DAT_SDA,'DD/MM/YYYY') DATA_SAIDA,
       		 TO_CHAR(A.dat_exp,'DD/MM/YYYY') DATA_EXPEDICAO,
       		 A.COD_STA COD_STAUS,
       		 D.des_sta DESC_STATUS,
       		 TO_CHAR(A.dat_ent,'DD/MM/YYYY') DATA_ENTREGA
  		FROM FRT_FTA     A,
       		 FRT_LDD     B,
       		 FRT_TRN     C,
       		 FRT_STA_NTA D
 		 WHERE A.cod_dep_ex = NVL(:BLK_CONTROLE.COD_DEP_EX_AUX,A.cod_dep_ex)    --'53006'
   		 AND A.cod_trn    = NVL(:BLK_CONTROLE.COD_TRN,A.cod_trn)              --'577225'
   		 AND A.agrp_cod   = NVL(:BLK_CONTROLE.W_NUM_CRG,A.agrp_cod)           --A.agrp_cod
   		 AND A.num        = NVL(:BLK_CONTROLE.NR_NF,A.num)                    --'412901'    
   		 AND A.ser        = NVL(:BLK_CONTROLE.CD_SERIE,A.ser)                 --'02'
   		 AND A.cod_dep_ex = B.cod
   		 AND A.cod_trn    = C.cod
   		 AND A.cod_sta    = D.cod_sta(+)
   		 AND TRUNC(A.dat_sda) >=  TRUNC(NVL(:blk_controle.dt_inicial,A.dat_sda))
   		 AND TRUNC(A.dat_sda) <=  TRUNC(NVL(:blk_controle.dt_FINAL,A.dat_sda))
   		 AND ( (:blk_controle.filter = 'A' AND A.dat_ent is null) OR
   	   	     (:blk_controle.filter = 'B' AND A.cod_sta is null) OR
   		       (:blk_controle.filter = 'C' AND A.cod_sta is not null AND A.dat_ent is null) OR
   		       (:blk_controle.filter = 'D' AND A.dat_ent is not null) OR
   		       (:blk_controle.FILTER = 'E')
   	       );
BEGIN
	MSG_ALERT('BEGIN','I',FALSE);
   IF Text_IO.Is_Open(out_file) THEN
      Text_IO.Fclose(out_file);
   END IF;

   BEGIN
      OUT_FILE := TEXT_IO.FOPEN('C:\TEMP\'||:NOME_ARQUIVO, 'w');
   EXCEPTION
   	WHEN OTHERS THEN
       MSG_ALERT('Diretório ou arquivo inválido.', 'E', TRUE);
   END;
--   MSG_ALERT('INI CABEÇARIO','I',FALSE);
--AQUI FICA CABEÇARIO DO ARQUIVO   
   LINEBUF  :=  'LOCALIDADE'       ||';'||         
   						  'TRASNPORTADORA'   ||';'||
               	'CARREGAMENTO'     ||';'|| 
               	'N NOTA FISCAL'    ||';'||
                'SERIE NF'         ||';'||     
                'DATA SAIDA NF'    ||';'||  
                'CLIENTE'          ||';'|| 
                'UF CLIENTE'       ||';'|| 
                'CID. ENTREGA'     ||';'||         
                'DATA EMIS. NF'    ||';'||         
                'DATA EXP. NF'     ||';'||         
                'COD STATUS'       ||';'||         
                'DESC. STATUS'     ||';'||         
                'DATA ENTREGA';
   
 --  MSG_ALERT('FIM CABEÇARIO','I',FALSE);
   TEXT_IO.PUT_LINE(OUT_FILE, LINEBUF);
   SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'BUSY');
   
  -- MSG_ALERT('VAI ENTRAR NO LOOP','I',FALSE);
   FOR Z IN NOTA_FISCAL
   LOOP
 --  MSG_ALERT('ENTROU NO LOOP','I',FALSE);
         LINEBUF  := Z.LOCALIDADE        ||';'||
       	             Z.TRANSP            ||';'||
       		           Z.CARREGAMENTO      ||';'||
       		           Z.NF                ||';'||
       		           Z.SERIE_NF          ||';'||
       		           Z.DATA_SAIDA_NF     ||';'||
       		           Z.CLIENTE           ||';'||
       		           Z.UF_CLI            ||';'||
       		           Z.CID_ENTREGA       ||';'||
       		           Z.DATA_SAIDA        ||';'||
       		           Z.DATA_EXPEDICAO    ||';'||
       		           Z.COD_STAUS         ||';'||
       		           Z.DESC_STATUS       ||';'||
       		           Z.DATA_ENTREGA;
 --   MSG_ALERT('ESCREVEU LINHA','I',FALSE);
         TEXT_IO.PUT_LINE(out_file, linebuf);
   
   END LOOP;
 --   MSG_ALERT('SIU DO LOOP','I',FALSE);
   SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT');
   TEXT_IO.FCLOSE(out_file);
 --MSG_ALERT('´FECHOU ARQUIVO','I',FALSE);
   MSG_ALERT('Arquivo gerado com sucesso.', 'I', false);
   
   
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    TEXT_IO.PUT_LINE('Closing the file...');
    TEXT_IO.FCLOSE(out_file);

  WHEN OTHERS THEN
    MSG_ALERT('Erro: '||SQLERRM,'E',TRUE);
    RAISE form_trigger_failure;
   
END;

jctq · Sáb, 02 Set 2006 1:12 pm

Gente , se for uma formatação simples no Excell , poderiamos fazer um PL para jogar as informações para um arquivo .csv ...ao abrir este arquivo as informações já aparecem formatadas ..

Abraços

TBou · Qua, 16 Jan 2008 5:07 pm

Boa tarde,

é o seguinte Esta pack que o Zida passou fui eu que criei mas ela tem uma pequena falha e hoje descobri a causa dela,
quando o excdel já estava aberto e eu executava o programa o forms abria outrop excel e gerava uma planilha em cada excel que estava aberto ou seja dava erro de duplicidade de arquivo no segundo excel.
descobri que quando já esta aberto o excel na maquina eu não devo executar o comando

Selecionar tudo

AppID := DDE.App_Begin (V_DS_LOCAL,DDE.App_Mode_minimized/*maximized*/);

eu se encontra na procedure

Selecionar tudo

ABRE_ARQUIVO

pois o mesmo serve para abrir o excel.

agora eu teria que ver se existe alguma mandeira de verificar se há algum excel aberto na maquina, algum função do windows ou algo assim para que eu possa fazer um if se estiver aberto não executar o comando de abrir o excel.

hygormcunha · Sex, 11 Abr 2008 5:11 pm

O problema do DDE é que eu não consigo formatar a planilha... como por exmplo mesclar celulas.... por html eu posso usando colspan por exemplo :wink:

jader.barros · Qui, 10 Jun 2010 2:47 pm

Zida,

Gostaria tb que me mandasse a PK completa pois realmente faltam variáveis que estão na declaração do pacote.

Valeu!

Qui, 11 Ago 2011 6:58 pm

Pessoal,

Só para contribuir com o forum,

Sobre a geração de arquivos EXCEL via PL/SQL, no forum do ASKTOM existe uma package desenvolvida por ele mesmo (OWA_SYLK), que permite gerar arquivos em formatos excel.

O formato SYLK é um padrão adotado pela microsoft para permitir a troca de dados entre aplicações, especificamente planilhas:

Selecionar tudo

WIKIPEDIA
Symbolic Link (SYLK) is a Microsoft file format typically used to exchange data between applications, specifically spreadsheets. SYLK files conventionally have a .slk suffix. Composed of only displayable ANSI characters, it can be easily created and processed by other applications, such as databases.
Microsoft does not publish a SYLK specification. Variants of the format are supported by Multiplan, Microsoft Excel, Microsoft Works, OpenOffice.org, and Gnumeric."

A package do ASKTOM foi desenvolvida para a versão 8i, mas creio que ela deve servir para versões posteriores. Ela se encontra em seu formato mais simples (creio que pode ser melhorada). Mas na forma atual, já permite gerar formulários EXCEL.

Tom Kyte comenta que a package pode ser usada por Oracle Forms, bastando trocar o método UTL_FILE por TEXT_IO.

Selecionar tudo

You asked...
Tom 
I have a form , and the requirements is after the user queries the forms, he should have the ability to click a button which will write the data from the block to a  1.flat file , 2.Exel work sheet.
Can you provide me withe the information as to how we can achieve this.
Thanks
Vasaku 

And we said...
1) and 2) are basically the same -- since a .CSV (comma separated values) file is both a flat file and something Excel will gladly open.

You would use TEXT_IO in forms to open and write the text file.  You would use forms built-ins to navigate the block you wanted to save (first_record, next_record built-ins).

If you wanted to get "fancy", you could use the SYLK file format for the worksheet.  Lets you use fonts and such in the spreadsheet.  Search this site for OWA_SYLK UTL_FILE to see an example from the database (you would replace utl_file with TEXT_IO).

Esta conversa completa pode ser consultada neste link.

http://asktom.oracle.com/pls/apex/f?p=1 ... 2427289280

O fonte completo (para a versão 8i) se encontra abaixo:

Selecionar tudo

REM You have to call owa_util.mime_header first with the appropriate mime type and then it 
REM does the rest.  Here is the code:

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 </a> 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.


    begin
      owa_sylk.show(
        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' );
    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;
    begin
      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_cursor => l_cursor,
        p_sum_column =>
            owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
        p_show_grid => 'NO' );
      dbms_sql.close_cursor( l_cursor );
    end;

*/

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 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_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;
  g_lengths owa.você_arr;
  g_sums owa.você_arr;
--
--

  procedure p( p_str in varchar2 )
  is
  begin
    htp.p( 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_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
    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_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_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

Abraços,

Sergio Coutinho

Gerar planilha excel

Quem está online