Generación de reportes de Excel en formato XMLNS

CREATE OR REPLACE PACKAGE GeneraExcel IS

debug_flag              BOOLEAN := TRUE;


-- constantes para los estilos definidos
xTITULO                   CONSTANT VARCHAR2 (100) := 'TITULO';
xNEGRITAPEQUEÑA           CONSTANT VARCHAR2 (100) := 'NEGRITAPEQUENA';
xPEQUEÑABORDES            CONSTANT VARCHAR2 (100) := 'PEQUENABORDES';
xENCABEZADO               CONSTANT VARCHAR2 (100) := 'ENCABEZADO';
xENCABEZADO2              CONSTANT VARCHAR2 (100) := 'ENCABEZADO2';
xENCABEZADO_IZQUIERDA     CONSTANT VARCHAR2 (100) := 'ENCABEZADO_IZQUIERDA';
xDETALLE                  CONSTANT VARCHAR2 (100) := 'DETALLE';
xNORMAL                   CONSTANT VARCHAR2 (100) := 'NORMAL';
xTOTAL                    CONSTANT VARCHAR2 (100) := 'TOTAL';
xNEGRITA14                CONSTANT VARCHAR2 (100) := 'NEGRITA14';
xNORMALNEGRITA            CONSTANT VARCHAR2 (100) := 'NORMALNEGRITA';
xNEGRITAFONDOGRIS         CONSTANT VARCHAR2 (100) := 'NEGRITAFONDOGRIS';
xNEGRITABORDES            CONSTANT VARCHAR2 (100) := 'NEGRITABORDES';
xNEGRITAFONDOAMARILLO     CONSTANT VARCHAR2 (100) := 'NEGRITAFONDOAMARILLO';
xAZULNEGRITABORDES        CONSTANT VARCHAR2 (100) := 'AZULNEGRITABORDES';
xNORMALNEGRITACENTRADA    CONSTANT VARCHAR2 (100) := 'NORMALNEGRITACENTRADA';
xBORDESUPERIOR            CONSTANT VARCHAR2 (100) := 'BORDESUPERIOR';
xBORDESUPERIORPEQUEÑA     CONSTANT VARCHAR2 (100) := 'BORDESUPERIORPEQUENA';
xNEGRITABORDEINFERIOR     CONSTANT VARCHAR2 (100) := 'NEGRITABORDEINFERIOR';
xNEGRITABORDESUPERIOR     CONSTANT VARCHAR2 (100) := 'NEGRITABORDESUPERIOR';
xNORMALDERECHA            CONSTANT VARCHAR2 (100) := 'NORMALDERECHA';
xNEGRITAIZQUI             CONSTANT VARCHAR2 (100) := 'NEGRITAIZQUI';


--Funciones para invocar los estilos desde forms
function sTITULO return varchar2;

function sNEGRITAPEQUEÑA return varchar2;

function sPEQUEÑABORDES return varchar2;

function sENCABEZADO return varchar2;

function sENCABEZADO2 return varchar2;

function sENCABEZADO_IZQUIERDA return varchar2;

function sDETALLE return varchar2;

function sNORMAL return varchar2;

function sTOTAL return varchar2;

function sNEGRITA14 return varchar2;

function sNORMALNEGRITA return varchar2;

function sNEGRITAFONDOGRIS return varchar2;

function sNEGRITABORDES return varchar2;

function sNEGRITAFONDOAMARILLO return varchar2;

function sAZULNEGRITABORDES return varchar2;

function sNORMALNEGRITACENTRADA return varchar2;

function sNEGRITABORDEINFERIOR return varchar2;

function sBORDESUPERIOR return varchar2;

function sBORDESUPERIORPEQUEÑA return varchar2;

function sNEGRITABORDESUPERIOR return varchar2;

function sNORMALDERECHA return varchar2;

function sNEGRITAIZQUI return varchar2;

-- Procedimiento que crea estilos predefinidos para facilidad de uso
PROCEDURE CrearEstilos;

-- Procedimiento que se encarga de crear el archivo de excel en formato XMLNS
PROCEDURE create_excel (p_directory   IN VARCHAR2 DEFAULT NULL,
p_file_name   IN VARCHAR2 DEFAULT NULL);

PROCEDURE create_excel_apps;

-- Procedimiento para la creación de estilos
PROCEDURE create_style (p_style_name   IN VARCHAR2,
p_fontname     IN VARCHAR2 DEFAULT NULL,
p_fontcolor    IN VARCHAR2 DEFAULT 'Black',
p_fontsize     IN NUMBER DEFAULT NULL,
p_bold         IN BOOLEAN DEFAULT FALSE,
p_italic       IN BOOLEAN DEFAULT FALSE,
p_underline    IN VARCHAR2 DEFAULT NULL,
p_backcolor    IN VARCHAR2 DEFAULT NULL,
p_horizontal   IN VARCHAR2 DEFAULT NULL,
p_vertical     IN VARCHAR2 DEFAULT NULL,
p_borde_b      IN BOOLEAN DEFAULT FALSE,
p_borde_l      IN BOOLEAN DEFAULT FALSE,
p_borde_r      IN BOOLEAN DEFAULT FALSE,
p_borde_t      IN BOOLEAN DEFAULT FALSE,
P_dolar        IN BOOLEAN DEFAULT FALSE,
p_miles        IN BOOLEAN DEFAULT FALSE,
p_grosorborde  IN NUMBER  DEFAULT 1);

-- Procedimiento que termina de armar el XML y cierra el archivo
PROCEDURE close_file;

-- Procedimiento que crea una Hoja en el Excel.
PROCEDURE create_worksheet (p_worksheet_name IN VARCHAR2,
p_vertical       in number default null,
p_horizontal     in number default null,
v_zoom           in number default null,
p_area_horizontal in number default null,
p_area_vertical   in number default null);

-- Procedimiento para pintar celdas numericas en el excel.
PROCEDURE write_cell_num (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_value            IN NUMBER,
p_style            IN VARCHAR2 DEFAULT NULL);

-- Procedimiento para pintar celdas String en el excel.
PROCEDURE write_cell_char (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_value            IN VARCHAR2,
p_style            IN VARCHAR2 DEFAULT NULL);

-- Procedimiento para insertar celdas con valores nulos en excel.
PROCEDURE write_cell_null (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_style            IN VARCHAR2);

-- Procedimiento para crear formulas a celdas en el excel.
PROCEDURE write_cell_Total (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_Formula          IN VARCHAR2);

-- Procedimiento para setear el alto de una Fila en el excel.
PROCEDURE set_row_height (p_row         IN NUMBER,
p_height      IN NUMBER,
p_worksheet   IN VARCHAR2);

-- Procedimiento para setear el ancho de una columna en el Excel.
PROCEDURE set_column_width (p_column      IN NUMBER,
p_width       IN NUMBER,
p_worksheet   IN VARCHAR2);

-- Procedimiento para generar subtotales en el excel.
PROCEDURE write_cell_SubTotal (p_row                 NUMBER,
p_column              NUMBER,
p_posAnterior         NUMBER,
p_worksheet_name   IN VARCHAR2);
--SAMO Procedimiento para generar formulas en el excel que va a contener un numerico.                                      
PROCEDURE write_cell_Formula (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_Formula          IN VARCHAR2,
p_style            IN VARCHAR2);
-- Funcion para obtener el valor de una celda en el excel.
FUNCTION cell_value (p_r IN NUMBER, p_c IN NUMBER, p_w IN VARCHAR2)
RETURN VARCHAR2;

-- Procedimiento para realizar la combinación de celdas en el excel.
PROCEDURE write_cell_COMBINE (p_row                 NUMBER,
p_column              NUMBER,
P_ROW_FINAL           NUMBER,
P_COLUMN_FINAL        NUMBER,
p_worksheet_name   IN VARCHAR2,
P_VALUE            IN VARCHAR2,
p_style            IN VARCHAR2);

-- Función utilizada para la alineación de los datos en la definición de los estilos.
FUNCTION Alinear (pAlinearH VARCHAR2, pAlinearV VARCHAR2)
RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY PA.GeneraExcel
IS
l_file               UTL_FILE.file_type;
--l_file CLIENT_TEXT_IO.file_type;
--SAMO

g_apps_env           VARCHAR2 (1) := 'U';             -- unset at the start

TYPE tbl_excel_data IS TABLE OF long--VARCHAR2 (4000)
INDEX BY BINARY_INTEGER;

g_excel_data         tbl_excel_data;
g_null_data          tbl_excel_data;
g_data_count         NUMBER;


TYPE rec_styles IS RECORD
(
s     VARCHAR2 (1000),
def   VARCHAR2 (4000)
);

TYPE tbl_style IS TABLE OF rec_styles
INDEX BY BINARY_INTEGER;

g_styles             tbl_style;
g_null_styles        tbl_style;
g_style_count        NUMBER := 0;

TYPE rec_worksheets IS RECORD
(
w      VARCHAR2 (1000),
whdr   VARCHAR2 (300),
wftr   VARCHAR2 (4000)
);

TYPE tbl_worksheets IS TABLE OF rec_worksheets
INDEX BY BINARY_INTEGER;

g_worksheets         tbl_worksheets;
g_null_worksheets    tbl_worksheets;
g_worksheets_count   NUMBER := 0;

TYPE rec_cell_data IS RECORD
(
r    NUMBER,
c    NUMBER,
v    VARCHAR2 (4000),
s    VARCHAR2 (4000),
w    VARCHAR2 (500),
dt   VARCHAR2 (100)
);

TYPE tbl_cell_data IS TABLE OF rec_cell_data
INDEX BY BINARY_INTEGER;

g_cells              tbl_cell_data;
g_null_cells         tbl_cell_data;
g_cell_count         NUMBER := 0;

TYPE rec_columns_data IS RECORD
(
c    NUMBER,
wd   NUMBER,
w    VARCHAR2 (1000)
);

TYPE tbl_columns_data IS TABLE OF rec_columns_data
INDEX BY BINARY_INTEGER;

g_columns            tbl_columns_data;
g_null_columns       tbl_columns_data;
g_column_count       NUMBER;


TYPE rec_rows_data IS RECORD
(
r    NUMBER,
ht   NUMBER,
w    VARCHAR2 (1000)
);

TYPE tbl_rows_data IS TABLE OF rec_rows_data
INDEX BY BINARY_INTEGER;

g_rows               tbl_ROWS_data;
g_null_rows          tbl_rows_data;
g_ROW_count          NUMBER;

--
PROCEDURE CrearEstilos
IS
BEGIN
GeneraExcel.create_style ('TITULO',
'Courier',
'black',
10,
TRUE, -- NEGRITA
p_backcolor    => 'LightGray',
P_underline    => 'Single',
P_Horizontal   => 'C',
P_VERTICAL     => 'C');
GeneraExcel.create_style ('NEGRITA14',
'Arial',
'black',
14,
TRUE, -- NEGRITA
P_Horizontal   => 'C',
P_VERTICAL     => 'C');
GeneraExcel.create_style ('NORMAL',
'Courier',
'black',
9,
P_Horizontal   => 'L',
P_VERTICAL     => 'T');
GeneraExcel.create_style ('NORMALNEGRITA',
'Courier',
'black',
9,
TRUE,
P_Horizontal   => 'L',
P_VERTICAL     => 'T');
GeneraExcel.create_style ('NORMALNEGRITACENTRADA',
'Courier',
'black',
9,
TRUE,
P_Horizontal   => 'C',
P_VERTICAL     => 'C');
GeneraExcel.create_style ('NEGRITAPEQUENA',
'Courier',
'black',
7,
TRUE,
P_Horizontal   => 'L',
P_VERTICAL     => 'T');
GeneraExcel.create_style ('NEGRITAFONDOGRIS',
'Courier',
'black',
10,
TRUE,
P_BACKCOLOR => '#D8E4BC',
P_Horizontal   => 'C',
P_VERTICAL     => 'B');
GeneraExcel.create_style ('ENCABEZADO',
'Courier',
'black',
9,
TRUE,
P_Horizontal   => 'C',
P_VERTICAL     => 'C');
GeneraExcel.create_style ('ENCABEZADO2',
'Courier',
'blue',
8,
TRUE,
P_Horizontal   => 'C',
P_VERTICAL     => 'C');
GeneraExcel.create_style ('ENCABEZADO_IZQUIERDA',
'Courier',
'blue',
8,
TRUE,
P_Horizontal   => 'L',
P_VERTICAL     => 'C');


GeneraExcel.create_style ('DETALLE',
'Times New Roman',
'black',
8,
NULL);

GeneraExcel.create_style ('NEGRITAFONDOAMARILLO',
'Courier',
'black',
12,
TRUE,
P_BACKCOLOR => 'YELLOW',
P_Horizontal   => 'C',
P_VERTICAL     => 'C');

GeneraExcel.create_style ('NEGRITABORDES',
'Courier',
'black',
10,
TRUE,
P_BACKCOLOR => '#D8E4BC',
P_Horizontal   => 'C',
P_VERTICAL     => 'C',
p_borde_b      => true,
p_borde_l      => true,
p_borde_r      => true,
p_borde_t      => true);
GeneraExcel.create_style ('AZULNEGRITABORDES',
'ARIAL',
'#003366',
12,
TRUE,
p_borde_b      => true,
p_borde_l      => true,
p_borde_r      => true,
p_borde_t      => true);
GeneraExcel.create_style ('PEQUENABORDES',
'Courier',
'black',
12,
TRUE,
P_Horizontal   => 'L',
P_VERTICAL     => 'T',
p_borde_b      => true,
p_borde_l      => FALSE,
p_borde_r      => FALSE,
p_borde_t      => true);
GeneraExcel.create_style ('BORDESUPERIOR',
'Courier',
'black',
9,
TRUE,
P_Horizontal   => 'L',
P_VERTICAL     => 'T',
p_borde_b      => FALSE,
p_borde_l      => FALSE,
p_borde_r      => FALSE,
p_borde_t      => true);
GeneraExcel.create_style ('BORDESUPERIORPEQUENA',
'Times New Roman',
'black',
8,
FALSE,
P_Horizontal   => 'C',
P_VERTICAL     => 'C',
p_borde_b      => FALSE,
p_borde_l      => FALSE,
p_borde_r      => FALSE,
p_borde_t      => true);
GeneraExcel.create_style ('NEGRITABORDEINFERIOR',
'Times New Roman',
'black',
8,
TRUE,
P_Horizontal   => 'C',
P_VERTICAL     => 'C',
p_borde_b      => TRUE,
p_borde_l      => FALSE,
p_borde_r      => FALSE,
p_borde_t      => FALSE);
GeneraExcel.create_style ('NEGRITABORDESUPERIOR',
'Times New Roman',
'black',
8,
TRUE,
P_Horizontal   => 'C',
P_VERTICAL     => 'C',
p_borde_b      => FALSE,
p_borde_l      => FALSE,
p_borde_r      => FALSE,
p_borde_t      => TRUE);
GeneraExcel.create_style ('NORMALDERECHA',
'Times New Roman',
'black',
8,
P_Horizontal   => 'R',
P_VERTICAL     => 'T');
GeneraExcel.create_style ('NEGRITAIZQUI',
'Courier',
'black',
7,
TRUE,
P_Horizontal   => 'R',
P_VERTICAL     => 'T');
END;


/*--JFAL
   PROCEDURE p ( p_string IN VARCHAR2) is
   begin
           IF debug_flag = TRUE THEN
             RAISE_APPLICATION_ERROR(-1000, p_string) ;
           END IF;
   END ;
   --JFAL*/
FUNCTION style_defined (p_style IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
FOR i IN 1 .. g_style_count
LOOP
IF g_styles (i).s = p_style
THEN
RETURN TRUE;
END IF;
END LOOP;

RETURN FALSE;
END;

-------------------------------------------------------------------------------------------------------------
-- Function : cell_used   returns : BOOLEAN
--  Description : Cell_used FUNCTION returns TRUE IF that cell IS already used
--  Called BY : write_Cell_char, write_cell_num
--  ??? right now it IS NOT called BY write_Cell_null , this needs TO be evaluated
-------------------------------------------------------------------------------------------------------------

FUNCTION cell_used (p_r IN NUMBER, p_c IN NUMBER, p_w IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
FOR i IN 1 .. g_cell_count
LOOP
IF (    g_cells (i).r = p_r
AND g_cells (i).c = p_c
AND g_cells (i).w = p_w)
THEN
RETURN TRUE;
END IF;
END LOOP;
 
RETURN FALSE;
END;
 
PROCEDURE initialize_collections
IS
--- following lines resets the cell data and the cell count as it was
-- observed that the data is retained across the two runs within same seseion.
BEGIN
g_cells := g_null_cells;
g_Cell_count := 0;
 
g_styles := g_null_styles;
g_style_count := 0;
 
g_rows := g_null_rows;
g_ROW_count := 0;
 
g_columns := g_null_columns;
g_column_count := 0;
 
g_excel_data := g_null_data;
g_data_count := 0;
 
g_apps_env := 'U';
 
g_worksheets := g_null_worksheets;
g_worksheets_count := 0;
END;
 
PROCEDURE create_excel_apps
IS
BEGIN
-- CHECK the env value
IF g_apps_env = 'N'
THEN
RAISE_APPLICATION_ERROR ( '-20001 -You have already called create_excel ( Non Apps ) procedure, Can not set env to create_excel_apps.' );
--MESSAGE ('-20001 -You have already called create_excel ( Non Apps ) procedure, Can not set env to create_excel_apps.');
END IF;
 
initialize_collections;
g_apps_env := 'Y';
END;
 
PROCEDURE create_excel (p_directory   IN VARCHAR2 DEFAULT NULL,
p_file_name   IN VARCHAR2 DEFAULT NULL)
IS
--
vDirectorio varchar2(1000);
BEGIN
vDirectorio := P_DIRECTORY;
-- CHECK the env value
IF g_apps_env = 'Y'
THEN
--MESSAGE ('-20001 -You have already called procedure create_excel_apps , Can not set env to Non-Apps create_excel.');
RAISE_APPLICATION_ERROR ( '-20001 -You have already called procedure create_excel_apps , Can not set env to Non-Apps create_excel.' );
END IF;
 
initialize_collections;
g_apps_env := 'N';
 
IF (vDirectorio IS NULL OR p_file_name IS NULL)
THEN
RAISE_APPLICATION_ERROR ( '-20001 -p_directory and p_file_name must be not null' );
--MESSAGE ('-20001 -p_directory and p_file_name must be not null');
END IF;
 
BEGIN
-------------------------------------------
-- Open the FILE IN the specified directory
-- -----------------------------------------
-- l_file := CLIENT_TEXT_IO.fopen (p_directory || p_file_name, 'w','UTF-8');
--SAMO
 
--NULL;
l_file :=   utl_file.fopen( vDirectorio, p_file_name , 'w') ;
EXCEPTION
WHEN OTHERS
THEN
/*WHEN utl_file.write_error THEN */
RAISE_APPLICATION_ERROR ( '-20001 -UTL_FILE raised write error, check if file is already open or directory access' );
--MESSAGE ('-20001 -UTL_FILE raised write error, check if file is already open or directory access');
/*WHEN utl_file.INVALID_OPERATION THEN
      message( '-20001 -UTL_FILE could not open file or operate on it, check if file is already open.');
      WHEN utl_file.invalid_path THEN
      message( '-20001 -UTL_FILE raised invalid path, check the directory passed is correct and you have access to it.');
      WHEN others THEN
      message( '-20001 -UTL_FILE raised others exception ');*/
END;
--JFALp( 'File '||p_file_name ||' created successfully');
END;
 
 
PROCEDURE create_style (p_style_name   IN VARCHAR2,
p_fontname     IN VARCHAR2 DEFAULT NULL,
p_fontcolor    IN VARCHAR2 DEFAULT 'Black',
p_fontsize     IN NUMBER DEFAULT NULL,
p_bold         IN BOOLEAN DEFAULT FALSE,
p_italic       IN BOOLEAN DEFAULT FALSE,
p_underline    IN VARCHAR2 DEFAULT NULL,
p_backcolor    IN VARCHAR2 DEFAULT NULL,
P_Horizontal   IN VARCHAR2 DEFAULT NULL,
p_Vertical     IN VARCHAR2 DEFAULT NULL,
p_borde_b      IN BOOLEAN DEFAULT FALSE,
p_borde_l      IN BOOLEAN DEFAULT FALSE,
p_borde_r      IN BOOLEAN DEFAULT FALSE,
p_borde_t      IN BOOLEAN DEFAULT FALSE,
p_dolar        IN BOOLEAN DEFAULT FALSE,
p_miles        IN BOOLEAN DEFAULT FALSE,
p_grosorborde  IN NUMBER  DEFAULT 1)
IS
l_style   VARCHAR2 (2000);
l_font    VARCHAR2 (1200);
l_bordes  VARCHAR2 (2000);
BEGIN
--------------------------------------------------------------------
--- CHECK IF this style IS already defined AND RAISE   ERROR IF yes
--------------------------------------------------------------------
IF style_defined (p_style_name)
THEN
RAISE_APPLICATION_ERROR ( '-20001 -> Style "' || p_style_name || '" is already defined.' );
--MESSAGE ('-20001 -> Style "' || p_style_name || '" is already defined.');
END IF;
 
g_style_count := g_style_count + 1;
 
---- ??? pass ANY value OF underline AND it will only use single underlines
-- ??? pattern IS NOT handleed
IF UPPER (p_style_name) = 'DEFAULT'
THEN
RAISE_APPLICATION_ERROR ( '-20001 -Style name DEFAULT is not allowed ' );
--MESSAGE ('-20001 -Style name DEFAULT is not allowed ');
END IF;
 
IF UPPER (p_style_name) IS NULL
THEN
RAISE_APPLICATION_ERROR ( '-20001 -Style name can not be null ' );
--MESSAGE ('-20001 -Style name can not be null ');
END IF;
 
g_styles (g_style_count).s := p_style_name;
g_styles (g_style_count).def :=
' <Style ss:ID="' || p_style_name || '"> ';
 
 
 
IF p_borde_b   OR p_borde_l  OR p_borde_r  OR p_borde_t  THEN
l_bordes :=' <Borders>';
IF p_borde_b  THEN
l_bordes :=l_bordes ||' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="'||p_grosorborde||'"/>';
END IF;
IF p_borde_l  THEN
l_bordes :=l_bordes ||'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="'||p_grosorborde||'"/>';
END IF;
IF p_borde_r  THEN
l_bordes :=l_bordes ||'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="'||p_grosorborde||'"/>';
END IF;
IF p_borde_t  THEN
l_bordes :=l_bordes ||'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="'||p_grosorborde||'"/>';
END IF;
l_bordes :=l_bordes ||' </Borders>';
END IF;
g_styles (g_style_count).def :=
g_styles (g_style_count).def || l_bordes;
 
l_font := ' <Font ';
 
IF p_fontname IS NOT NULL
THEN
l_font := l_font || 'ss:FontName="' || p_fontname || '" ';
END IF;
 
IF p_fontsize IS NOT NULL
THEN
l_font := l_font || ' ss:Size="' || p_fontsize || '" ';
END IF;
 
IF p_fontcolor IS NOT NULL
THEN
l_font := l_font || ' ss:Color="' || p_fontcolor || '" ';
ELSE
l_font := l_font || ' ss:Color="Black" ';
END IF;
 
IF p_bold = TRUE
THEN
l_font := l_font || ' ss:Bold="1" ';
END IF;
 
IF p_italic = TRUE
THEN
l_font := l_font || ' ss:Italic="1" ';
END IF;
 
IF p_underline IS NOT NULL
THEN
l_font := l_font || ' ss:Underline="Single" ';
END IF;
 
g_styles (g_style_count).def :=
g_styles (g_style_count).def || l_font || '/>';
 
IF p_backcolor IS NOT NULL
THEN
g_styles (g_style_count).def :=
g_styles (g_style_count).def
|| ' <Interior ss:Color="'
|| p_backcolor
|| '" ss:Pattern="Solid"/>';
ELSE
g_styles (g_style_count).def :=
g_styles (g_style_count).def || ' <Interior/>';
END IF;
if instr(upper(p_style_name),'DATE')>0 then
g_styles (g_style_count).def :=
g_styles (g_style_count).def ||'<NumberFormat ss:Format="Short Date"/>';
end if;
 
IF p_dolar THEN
g_styles (g_style_count).def :=
g_styles (g_style_count).def || '<NumberFormat ss:Format="[$-409]#,##0.00;[$-409]#,##0.00"/>';
END IF;
IF p_miles THEN--"#,##0.00;#,##0.00"
g_styles (g_style_count).def :=
g_styles (g_style_count).def || '<NumberFormat ss:Format="Standard"/>';
END IF;
IF p_Horizontal IS NOT NULL OR p_Vertical IS NOT NULL
THEN
g_styles (g_style_count).def :=
g_styles (g_style_count).def
|| ' <Alignment '
|| Alinear (p_Horizontal, p_Vertical)
|| ' ss:WrapText="1"/> ';
else
IF p_style_name='DETALLE'
THEN
g_styles (g_style_count).def :=
g_styles (g_style_count).def
|| ' <Alignment '
|| Alinear (p_Horizontal, p_Vertical)
|| ' ss:WrapText="1"/> ';
END IF;
END IF;
 
g_styles (g_style_count).def :=
g_styles (g_style_count).def || ' </Style>';
END;
 
 
PROCEDURE close_file
IS
l_last_row      NUMBER := 0;
l_dt            CHAR; -- ??? Variable TO store the datatype ;  this IS NOT used at this time but may be needed IF the memory
-- issue IS there FOR example IF there IS big array
l_style         VARCHAR2 (4000);
l_row_change    VARCHAR2 (100);
 
l_file_header   long-- VARCHAR2 (2000) 
:= '<?xml version="1.0" encoding="ISO-8859-1"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<LastAuthor>a</LastAuthor>
<Created>1996-10-14T23:33:28Z</Created>
<LastSaved>2007-05-10T04:00:57Z</LastSaved>
<Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9300</WindowHeight>
<WindowWidth>15135</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>120</WindowTopY>
<AcceptLabelsInFormulas/>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>';
BEGIN
--JFAL    IF GeneraExcel.g_Cell_count = 0 THEN
--JFAL        message( '-20007 -No cells have been written, this version of GeneraExcel needs at least one cell to be written');
--JFAL    END IF;
--JFAL    IF GeneraExcel.g_worksheets_count = 0 THEN
--JFAL        message( '-20008 -No worksheets have been created, this version does not support automatic worksheet creation');
--JFAL    END IF;
--JFALp( GeneraExcel.g_Cell_count) ;
-----------------------------------------
-- Write the header xml part IN the FILE.
------------------------------------------
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := l_file_header;
 
--JFALp( 'Headers written');
 
 
FOR i IN 1 .. g_style_count
LOOP
--JFALp( ' writing style number : '||i);
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := g_styles (i).def;
END LOOP;
 
-- CLOSE the styles tag
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := ' </Styles>';
 
--JFALp( 'worksheet count '|| g_worksheets_count );
FOR j IN 1 .. g_worksheets_count
LOOP
l_last_row := 0; --- FOR every worksheet we need TO CREATE START OF the row
--JFALp( '()()------------------------------------------------------------ last row '||l_last_row );
--- write the header first
-- write the COLUMN widhts first
-- write the cells
-- write the worksheet footer
l_row_change := NULL;
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
' <Worksheet ss:Name="' || g_worksheets (j).w || '"> ';
--JFALp( '-------------------------------------------------------------');
--JFALp( '****************.Generated sheet '|| g_worksheets( j).w);
--JFALp( '-------------------------------------------------------------');
 
-- write the TABLE structure ??? change the LINE here TO include tha maxrow AND cell
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
'<Table ss:ExpandedColumnCount="500" ss:ExpandedRowCount="5000000" x:FullColumns="1"  x:FullRows="1">';
 
FOR i IN 1 .. g_column_count
LOOP
IF g_columns (i).w = g_worksheets (j).w
THEN
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
' <Column ss:Index="'
|| g_columns (i).c
|| '" ss:AutoFitWidth="0" ss:Width="'
|| g_columns (i).wd
|| '"/> ';
END IF;
END LOOP;
 
---------------------------------------------
-- write the cells data
---------------------------------------------
 
FOR i IN 1 .. g_cell_count
LOOP                                     ------  LOOP OF g_cell_count
--JFALp( '()()()()()()()()()()()()  '|| i);
--- we will write only IF the cells belongs TO the worksheet that we are writing.
IF g_cells (i).w <> g_worksheets (j).w
THEN
--JFALp( '........................Cell : W :'|| g_worksheets( j).w ||'=> r='|| g_cells(i).r ||',c ='|| g_cells(i).c||',w='|| g_cells(i).w );
--JFALp( '...Not in this worksheet ');
--                l_last_row := l_last_row -1 ;
NULL;                                                    --JFAL
ELSE
--JFALp( '........................Cell : W :'|| g_worksheets( j).w ||'=> r='|| g_cells(i).r ||',c ='|| g_cells(i).c||',w='|| g_cells(i).w );
IF     g_cells (i).s IS NOT NULL
AND NOT style_defined (g_cells (i).s)
AND g_cells (i).s NOT LIKE '%ss:Formula%'
AND g_cells (i).s NOT LIKE '%ss:Merge%'
THEN
--                p(g_cells(i).s) ;
RAISE_APPLICATION_ERROR ( '-20001' || ' Style "' || g_cells (i).s|| '" is not defined, Note : Styles are case sensative and check spaces used while passing style');
--MESSAGE ('-20001' || ' Style "' || g_cells (i).s|| '" is not defined, Note : Styles are case sensative and check spaces used while passing style');
END IF;
 
--JFALp( '()()------------------------------------------------------------ last row '||l_last_row );
IF l_last_row = 0
THEN
--
FOR t IN 1 .. g_row_count
LOOP
--JFALp( '...Height check => Row =' ||g_rows(t).r ||', w='||g_rows(t).w);
IF g_rows (t).r = g_cells (i).r
AND g_rows (t).w = g_worksheets (j).w
THEN
--JFALp( '...Changing height') ;
l_row_change :=
' ss:AutoFitHeight="0" ss:Height="'
|| g_rows (t).ht
|| '" ';
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
' <Row ss:Index="'
|| g_cells (i).r
|| '"'
|| l_row_change
|| '>';
l_last_row := g_cells (i).r;
EXIT;
ELSE
--JFALp( '...NO change height') ;
l_row_change := NULL;
END IF;
END LOOP;
 
IF l_ROW_CHANGE IS NULL
THEN
g_data_count := g_data_count + 1;
--JFALp( '...Creating new row ');
g_excel_data (g_data_count) :=
' <Row ss:Index="'
|| g_cells (i).r
|| '"'
|| l_row_change
|| '>';
l_last_row := g_cells (i).r;
END IF;
END IF;
 
IF g_cells (i).s IS NOT NULL
THEN
--JFALp( '...Adding style ');  }
IF g_cells (i).s LIKE '%ss:Formula%'
OR g_cells (i).s LIKE '%ss:Merge%'
THEN
l_style := ' ' || g_cells (i).s;
ELSE
l_style := ' ss:StyleID="' || g_cells (i).s || '"';
END IF;
ELSE
--JFALp( '...No style for this cell ');
l_style := NULL;
END IF;
 
--JFALp( '()()------------------------------------------------------------ last row '||l_last_row );
IF g_cells (i).r <> l_last_row
THEN
--JFALp('...closing the row.'||g_cells(i).r);
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := '  </Row>';
 
--JFALp( 'ROWCOUNT : '||g_row_count );
FOR t IN 1 .. g_ROW_count
LOOP
--JFALp( '.....Height check => Row =' ||g_rows(t).r ||', w='||g_rows(t).w);
IF g_rows (t).r = g_cells (i).r
AND g_rows (t).w = g_worksheets (j).w
THEN
--JFALp( '.....Changing height') ;
l_row_change :=
' ss:AutoFitHeight="0" ss:Height="'
|| g_rows (t).ht
|| '" ';
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
' <Row ss:Index="'
|| g_cells (i).r
|| '"'
|| l_row_change
|| '>';
EXIT;
ELSE
--JFALp( '.....NO change height') ;
l_row_change := NULL;
END IF;
END LOOP;
 
--             P( 'Row :'||g_cells(i).r ||'->'|| l_ROW_CHANGE);
IF l_row_change IS NULL
THEN
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
' <Row ss:Index="'
|| g_cells (i).r
|| '"'
|| l_row_change
|| '>';
END IF;
 
IF g_cells (i).v IS NULL
THEN
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
'<Cell ss:Index="'
|| g_cells (i).c
|| '"'
|| l_style
|| ' ></Cell>';
ELSE
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
'<Cell ss:Index="'
|| g_cells (i).c
|| '"'
|| l_style
|| ' ><Data ss:Type="'
|| g_cells (i).dt
|| '">'
|| g_cells (i).v
|| '</Data></Cell>';
END IF;
 
l_last_row := g_cells (i).r;
ELSE
IF g_cells (i).v IS NULL
THEN
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
'<Cell ss:Index="'
|| g_cells (i).c
|| '"'
|| l_style
|| ' > </Cell>';
ELSE
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) :=
'<Cell ss:Index="'
|| g_cells (i).c
|| '"'
|| l_style
|| ' ><Data ss:Type="'
|| g_cells (i).dt
|| '">'
|| g_cells (i).v
|| '</Data></Cell>';
END IF;
END IF;
END IF;
 
NULL;
END LOOP;                                    -- LOOP OF g_cells_count
 
--JFALp('...closing the row.');
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := '  </Row>';
 
-- ??? does following COMMENT will have sheet NAME FOR debugging
--JFALp( '-------------------------------------------------------------');
--JFALp( '....End of writing cell data, closing table tag');
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := '  </Table>';
 
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := g_worksheets (j).wftr;
--JFALp( '..Closed the worksheet '|| g_worksheets( j).w );
END LOOP;
 
g_data_count := g_data_count + 1;
g_excel_data (g_data_count) := '</Workbook>';
 
--JFALp( 'Closed the workbook tag');
 
IF g_apps_env = 'N'
THEN
FOR i IN 1 .. g_data_count
LOOP
--CLIENT_TEXT_IO.put_line (l_file, g_excel_data (i));
utl_file.put_line( l_file , g_excel_data(i));
/*IF MOD(i,100)=0 then
                SYNCHRONIZE;
                end if;*/
END LOOP;
utl_file.Fclose( l_file);
--CLIENT_TEXT_IO.fclose (l_file);
--JFALp( 'File closed ');
--    ELSIF g_apps_env = 'Y' THEN
--        FOR i IN 1..g_data_count LOOP
--            fnd_file.put_line( fnd_file.output , g_excel_data(i));
--            fnd_file.put_line( fnd_file.log , g_excel_data(i));
--        END LOOP ;
ELSE
RAISE_APPLICATION_ERROR ('-20001 ->Env not set, ( Apps or not Apps ) Contact Support.');
--MESSAGE ('-20001 ->Env not set, ( Apps or not Apps ) Contact Support.');
END IF;
END;
 
PROCEDURE create_worksheet (p_worksheet_name IN VARCHAR2,
p_vertical       in number default null,
p_horizontal     in number default null,
v_zoom           in number default null,
p_area_horizontal in number default null,
p_area_vertical   in number default null)
IS
v_SplitPanel varchar2(4000):= null;
v_DescHorizontal varchar2(1000) := null;
v_DescVertical varchar2(1000):= null;
v_ActiveRow varchar2(1000):= null;
v_ActiveCol varchar2(1000):= null;
v_ActivePane varchar2(1000):= null;
v_Area varchar2(4000):= null;
BEGIN
---           
if nvl(p_vertical,0) > 0  or nvl(p_horizontal,0) > 0 then
if nvl(p_Horizontal,0) > 0 then
v_DescHorizontal := '<SplitHorizontal>'||to_char(p_horizontal)||'</SplitHorizontal><TopRowBottomPane>'||to_char(p_horizontal)||'</TopRowBottomPane>';
v_ActiveRow := '<ActiveRow>'||to_char(p_Horizontal + 1)||'</ActiveRow>';
v_ActivePane :=  '<ActivePane>2</ActivePane>'||
'<Pane>
<Number>1</Number>
</Pane>
<Pane>
<Number>2</Number>'||
v_ActiveRow||'<ActiveCol>1</ActiveCol>'
||'</Pane>
';
else
v_ActiveRow := null;
end if;
if nvl(p_vertical,0) > 0 then
v_DescVertical := '<SplitVertical>'||to_char(p_vertical)||'</SplitVertical><LeftColumnRightPane>'||to_char(p_vertical)||'</LeftColumnRightPane>';
v_ActiveCol := '<ActiveCol>'||to_char(p_Vertical + 1)||'</ActiveCol>';
v_ActivePane :=  '<ActivePane>1</ActivePane>'||'
<Pane>
<Number>1</Number><ActiveRow>1</ActiveRow>'||
v_ActiveCol
||'</Pane>
';
else
v_ActiveCol := null;
end if;
if v_DescHorizontal is not null and v_DescVertical is not null then
v_ActivePane :=  '<ActivePane>0</ActivePane>'||
'<Pane>
<Number>0</Number>'||v_ActiveRow||v_ActiveCol||'
</Pane>';
end if;
v_SplitPanel := '<FreezePanes/><FrozenNoSplit/>'||v_DescHorizontal||v_DescVertical||v_ActivePane;
 
 
 
 
else
v_SplitPanel := null;
end if;
---
g_worksheets_count := g_worksheets_count + 1;
 
g_worksheets (g_worksheets_count).w := p_worksheet_name;
g_worksheets (g_worksheets_count).whdr :=
'<Worksheet ss:Name=" ' || p_worksheet_name || ' ">';
v_area:= '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">';
if v_zoom IS NOT NULL then
v_area:=v_area||'<Zoom>'||v_zoom||'</Zoom>';
end if;
if p_area_horizontal is not null or p_area_vertical is not null then
v_area:=v_area||'<Print>
<ValidPrinterInfo/>
<Scale>86</Scale>
<HorizontalResolution>'||p_area_horizontal||'</HorizontalResolution>
<VerticalResolution>'||p_area_vertical||'</VerticalResolution>
</Print>
<ShowPageBreakZoom/>';
end if;
v_area:=v_area||'<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>'||v_SplitPanel||
'</WorksheetOptions>
</Worksheet>';
g_worksheets (g_worksheets_count).wftr :=v_area;
END;
 
PROCEDURE write_cell_char (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_value            IN VARCHAR2,
p_style            IN VARCHAR2 DEFAULT NULL)
IS
l_ws_exist    BOOLEAN;
l_worksheet   VARCHAR2 (2000);
BEGIN
-- CHECK IF this cell has been used previously.
--JFAL    IF cell_used( p_row , p_column , p_worksheet_name ) THEN
--JFAL        message( '-20001 ->The cell ( Row: '||p_row ||' Column:'||p_column ||' Worksheet:'||p_worksheet_name ||') is already used.Check if you have missed to increment row number in your code. ');
--JFAL    END IF;
 
-- IF worksheet NAME IS NOT passed THEN use first USER created sheet ELSE use DEFAULT sheet
-- this PROCEDURE just adds the data INTO the g_cells TABLE
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := PA.PA_BUSCA_CARACTER (p_value,'EXCEL');
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s := p_style;
g_cells (g_cell_count).dt := 'String';
/*if instr(upper(p_style),'DATE')> 0 then
          g_cells (g_cell_count).dt := 'DateTime';
      else
        g_cells (g_cell_count).dt := 'String';
      end if;*/
END;
 
PROCEDURE write_cell_num (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_value            IN NUMBER,
p_style            IN VARCHAR2 DEFAULT NULL)
IS
l_ws_exist    BOOLEAN;
l_worksheet   VARCHAR2 (2000);
BEGIN
--  ???  IF worksheet NAME IS NOT passed THEN use first USER created sheet ELSE use DEFAULT sheet
-- this PROCEDURE just adds the data INTO the g_cells TABLE
---
-- CHECK IF this cell has been used previously.
--JFAL    IF cell_used( p_row , p_column , p_worksheet_name ) THEN
--JFAL        message( '-20001 ->The cell ( Row: '||p_row ||' Column:'||p_column ||' Worksheet:'||p_worksheet_name ||') is already used. Check if you have missed to increment row number in your code.');
--JFAL    END IF;
 
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := p_value;
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s := p_style;
g_cells (g_cell_count).dt := 'Number';
END;
 
PROCEDURE write_cell_null (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_style            IN VARCHAR2)
IS
BEGIN
-- ????    NULL IS allowed here FOR time being. one OPTION IS TO warn USER that NULL IS passed but otherwise
-- the excel generates without error
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := NULL;
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s := p_style;
g_cells (g_cell_count).dt := NULL;
END;
 
PROCEDURE write_cell_Total (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_Formula          IN VARCHAR2)
IS
v_style   VARCHAR2 (4000);
BEGIN
-- ????    NULL IS allowed here FOR time being. one OPTION IS TO warn USER that NULL IS passed but otherwise
-- the excel generates without error
 
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := 0;
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s := 'ss:StyleID="BORDESUPERIOR" ss:Formula="=' || p_formula || '"'; --v_style ;
g_cells (g_cell_count).dt := 'Number';
END;
 
 
PROCEDURE write_cell_Formula (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_Formula          IN VARCHAR2,
p_style            IN VARCHAR2)
IS
v_style   VARCHAR2 (4000);
BEGIN
-- ????    NULL IS allowed here FOR time being. one OPTION IS TO warn USER that NULL IS passed but otherwise
-- the excel generates without error
 
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := 0;
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s := 'ss:StyleID="'||p_style||'" ss:Formula="=' || p_formula || '"'; --v_style ;
g_cells (g_cell_count).dt := 'Number';
END;
 
PROCEDURE write_cell_Formula_char (p_row                 NUMBER,
p_column              NUMBER,
p_worksheet_name   IN VARCHAR2,
p_Formula          IN VARCHAR2,
p_style            IN VARCHAR2)
IS
v_style   VARCHAR2 (4000);
BEGIN
-- ????    NULL IS allowed here FOR time being. one OPTION IS TO warn USER that NULL IS passed but otherwise
-- the excel generates without error
 
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := ' ';
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s := 'ss:StyleID="'||p_style||'" ss:Formula="=' || p_formula || '"'; --v_style ;
g_cells (g_cell_count).dt := 'String';
END;
 
PROCEDURE set_row_height (p_row         IN NUMBER,
p_height      IN NUMBER,
p_worksheet   IN VARCHAR2)
IS
BEGIN
g_ROW_count := g_ROW_count + 1;
g_rows (g_row_count).r := p_row;
g_rows (g_row_count).ht := p_height;
g_rows (g_row_count).w := p_worksheet;
END;
 
PROCEDURE set_column_width (p_column      IN NUMBER,
p_width       IN NUMBER,
p_worksheet   IN VARCHAR2)
IS
BEGIN
g_column_count := g_column_count + 1;
g_columns (g_column_count).c := p_column;
g_columns (g_column_count).wd := p_width;
g_columns (g_column_count).w := p_worksheet;
END;
 
PROCEDURE write_cell_SubTotal (p_row                 NUMBER,
p_column              NUMBER,
p_posAnterior         NUMBER,
p_worksheet_name   IN VARCHAR2)
IS
--v_style varchar2(4000);
vRangoSup   NUMBER := 0;
BEGIN
vRangoSup := (p_PosAnterior);
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := 0;
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s :=
'ss:StyleID="BORDESUPERIOR" ss:Formula="=SUM(R[-' || TO_CHAR (vRangoSup) || ']C:R[-1]C)"'; --v_style ;
g_cells (g_cell_count).dt := 'Number';
END;
 
FUNCTION cell_value (p_r IN NUMBER, p_c IN NUMBER, p_w IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
FOR i IN 1 .. g_cell_count
LOOP
IF (    g_cells (i).r = p_r
AND g_cells (i).c = p_c
AND g_cells (i).w = p_w)
THEN
RETURN g_cells (i).v;
END IF;
END LOOP;
 
RETURN ' ';
END;
 
PROCEDURE write_cell_COMBINE (p_row                 NUMBER,
p_column              NUMBER,
P_ROW_FINAL           NUMBER,
P_COLUMN_FINAL        NUMBER,
p_worksheet_name   IN VARCHAR2,
P_VALUE            IN VARCHAR2,
p_style            IN VARCHAR2)
IS
v_style   VARCHAR2 (4000);
BEGIN
g_cell_count := g_cell_count + 1;
g_cells (g_cell_count).r := p_row;
g_cells (g_cell_count).c := p_column;
g_cells (g_cell_count).v := p_value;
g_cells (g_cell_count).w := p_worksheet_name;
g_cells (g_cell_count).s :=
'ss:MergeAcross="'
|| TO_CHAR (P_COLUMN_FINAL - P_COLUMN)
|| '" ss:MergeDown="'
|| TO_CHAR (P_ROW_FINAL - P_ROW)
|| '" ss:StyleID="'
|| p_style
|| '"';
g_cells (g_cell_count).dt := 'String';
END;
 
FUNCTION Alinear (pAlinearH VARCHAR2, pAlinearV VARCHAR2)
RETURN VARCHAR2
IS
vEstiloAlinear   VARCHAR2 (1000);
BEGIN
IF pAlinearH IS NOT NULL
THEN
IF pAlinearH = 'C'
THEN
vEstiloAlinear := vEstiloAlinear || 'ss:Horizontal="Center" ';
ELSIF pAlinearH = 'L'
THEN
vEstiloAlinear := vEstiloAlinear || 'ss:Horizontal="Left" ';
ELSIF pAlinearH = 'R'
THEN
vEstiloAlinear := vEstiloAlinear || 'ss:Horizontal="Right" ';
END IF;
END IF;
 
IF pAlinearV IS NOT NULL
THEN
IF pAlinearV = 'C'
THEN
vEstiloAlinear := vEstiloAlinear || 'ss:Vertical="Center" ';
ELSIF pAlinearV = 'T'
THEN
vEstiloAlinear := vEstiloAlinear || 'ss:Vertical="Top" ';
ELSIF pAlinearV = 'B'
THEN
vEstiloAlinear := vEstiloAlinear || 'ss:Vertical="Bottom" ';
END IF;
END IF;
 
RETURN vEstiloAlinear;
END;
 
 
 
function sTITULO return varchar2
is
begin
return xTITULO;
end;
 
function sNEGRITAPEQUEÑA return varchar2
is
begin
return xNEGRITAPEQUEÑA;
end;
function sPEQUEÑABORDES return varchar2
is
begin
return xPEQUEÑABORDES;
end;
function sENCABEZADO return varchar2
is
begin
return xENCABEZADO;
end;
function sENCABEZADO2 return varchar2
is
begin
return xENCABEZADO2;
end;
function sENCABEZADO_IZQUIERDA return varchar2
is
begin
return xENCABEZADO_IZQUIERDA;
end;
function sDETALLE return varchar2
is
begin
return xDETALLE;
end;
function sNORMAL return varchar2
is
begin
return xNORMAL;
end;
function sTOTAL return varchar2
is
begin
return xTOTAL;
end;
function sNEGRITA14 return varchar2
is
begin
return xNEGRITA14;
end;
function sNORMALNEGRITA return varchar2
is
begin
return xNORMALNEGRITA;
end;
function sNEGRITAFONDOGRIS return varchar2
is
begin
return xNEGRITAFONDOGRIS;
end;
function sNEGRITABORDES return varchar2
is
begin
return xNEGRITABORDES;
end;
function sNEGRITAFONDOAMARILLO return varchar2
is
begin
return xNEGRITAFONDOAMARILLO;
end;
function sAZULNEGRITABORDES return varchar2
is
begin
return xAZULNEGRITABORDES;
end;
function sNORMALNEGRITACENTRADA return varchar2
is
begin
return xNORMALNEGRITACENTRADA;
end;
function sNEGRITABORDEINFERIOR return varchar2
is
begin
return xNEGRITABORDEINFERIOR;
end;
function sBORDESUPERIOR return varchar2
is
begin
return xBORDESUPERIOR;
end;
function sBORDESUPERIORPEQUEÑA return varchar2
is
begin
return xBORDESUPERIORPEQUEÑA;
end;
function sNEGRITABORDESUPERIOR return varchar2
is
begin
return xNEGRITABORDESUPERIOR;
end;
Function sNORMALDERECHA return varchar2
is
begin
return xNORMALDERECHA;
end;
function sNEGRITAIZQUI return varchar2
is
begin
return xNEGRITAIZQUI;
end;
END;
/

EJEMPLO DE UTILIZACIÓN
--Procedimiento que crea el archivo de parámetros para la ejecución del reporte en Excel.
PROCEDURE CREAR_ARCHPARAM_XML
IS


--=== Variables ===--      
vEmpresa    varchar2(1000):= :variables.CodEmpresa;
vFechaHoy   varchar2(1000):= 'Fecha: '||to_char( sysdate, 'DD/MM/YYYY HH:MI:SS AM' ) ;
vFecha_Ini  varchar2(1000):= to_char(:Variables.fec_inicio,'dd/mm/yyyy');
vFecha_Fin  varchar2(1000):= to_char(:Variables.fec_Final,'dd/mm/yyyy');
vNomEmpresa varchar2(1000):= :variables.NOMEMPRESA;
vTitSistema varchar2(1000):= :variables.TITSISTEMA;
vTitulo     varchar2(1000):= 'REPORTE DE PRUEBAS DEL '||vFecha_Ini||' AL '||vFecha_Fin;
vSesion     varchar2(1000):= :variables.sesion;
vRuta        varchar2(1000);

--variables para cortes
vEstado varchar2(10);
vMoneda varchar2(10);
vFormaPago varchar2(1000);
vMontoAsegurado  number(10,2);
vMontoCargoTotal  number(10,2);

-- Variables de control de la posición actual de despliegue
vcFila number := 1;
vFilaInicio number;
phoja varchar2(1000) := 'REPORTE';

BEGIN

-- Creación del Archivo en el USERPROFILE.

--CLIENT_TOOL_ENV.GETVAR('USERPROFILE',vruta);
TOOL_ENV.GETVAR('USERPROFILE', vruta);

GeneraExcel.create_excel (vRuta,'\REPORTE'||vSesion||'.xls');
GeneraExcel.create_worksheet (phoja);
GeneraExcel.CrearEstilos;

-- Formateo de las Columnas del Reporte.
GeneraExcel.set_column_width( 1, 100, phoja  );
GeneraExcel.set_column_width( 2, 100, phoja );
GeneraExcel.set_column_width( 3, 300, phoja );
GeneraExcel.set_column_width( 4, 250, phoja );
GeneraExcel.set_column_width( 5, 100, phoja  );
GeneraExcel.set_column_width( 6, 100, phoja  );
GeneraExcel.set_column_width( 7, 300, phoja  );
GeneraExcel.set_column_width( 8, 125, phoja  );
GeneraExcel.set_column_width( 9, 125, phoja  );
GeneraExcel.set_column_width( 10, 400, phoja  );
GeneraExcel.set_column_width( 11, 250, phoja  );
GeneraExcel.set_column_width( 12, 500, phoja  );
GeneraExcel.set_column_width( 13, 100, phoja  );
GeneraExcel.set_column_width( 14, 100, phoja  );
GeneraExcel.set_column_width( 15, 250, phoja  );
GeneraExcel.set_column_width( 16, 250, phoja  );
GeneraExcel.set_column_width( 17, 125, phoja  );
GeneraExcel.set_column_width( 18, 125, phoja  );
GeneraExcel.set_column_width( 19, 125, phoja  );
GeneraExcel.set_column_width( 20, 350, phoja  );
GeneraExcel.set_column_width( 21, 100, phoja  );
GeneraExcel.set_column_width( 22, 125, phoja  );

-- Creación del Encabezado del Reporte.   
Encabezado(
vcFila,
vFechaHoy,
vNomEmpresa,
vTitSistema,
vTitulo,
phoja);

vEstado := 'X';
vMoneda := 'X';
vFormaPago := null;
vMontoAsegurado := 0;
vMontoCargoTotal := 0;

vFilaInicio := vcFila;

-- Ejecución de la Consulta del Reporte.   
for rec in(   [SELECT CURSOR CON DATOS PARA EL REPORTE] )

Loop

-- Creación de Cortes del Reporte (Moneda y Estado).   
If vMoneda != to_char(rec.MONEDA) Then
If vMoneda = 'X' Then

INSERTA_MONEDA (vcFila, pHoja, rec.Moneda, rec.Desc_Moneda);

Inserta_Estado (vcFila, pHoja, vEmpresa, rec.Estado);

Encabezado_Poliza(pHoja, vcFila);
vMoneda := rec.MONEDA;

End If;
END IF;

If vEstado != rec.ESTADO Then
If vEstado != 'X' Then

CORTE_ESTADO(phoja, vcFila, vMontoCargoTotal);

If vMoneda != to_char(rec.MONEDA) Then
If vMoneda != 'X' Then
INSERTA_MONEDA (vcFila, pHoja, rec.Moneda, rec.Desc_Moneda);
vMoneda := rec.MONEDA;
END IF;
END IF;

Inserta_Estado (vcFila, pHoja, vEmpresa, rec.Estado);

Encabezado_Poliza(pHoja, vcFila);
End If;

vEstado := rec.ESTADO;

vFilaInicio := vcFila;

Else
If vMoneda != to_char(rec.MONEDA) Then
If vMoneda != 'X' Then

CORTE_ESTADO(phoja, vcFila, vMontoCargoTotal);

INSERTA_MONEDA (vcFila, pHoja, rec.Moneda, rec.Desc_Moneda);

Inserta_Estado (vcFila, pHoja, vEmpresa, rec.Estado);

Encabezado_Poliza(pHoja, vcFila);

vMoneda := rec.MONEDA;

END IF;
End if;
End If;

vcFila := vcFila + 1;

 

-- Se registra cada una de las columnas del reporte, con la información obtenida en las consultas.
GeneraExcel.write_cell_char (vcFila,
1,
pHoja,
NVL(rec.CAMPO1,'-'),
GENERAEXCEL.SDETALLE);

GeneraExcel.write_cell_char (vcFila,
2,
pHoja,
round(NVL(rec.CAMPO2,0),2),
GENERAEXCEL.SDETALLE);

GeneraExcel.write_cell_char (vcFila,
3,
pHoja,
nvl(rec.CAMPO3,'-'),
GENERAEXCEL.SDETALLE);

End Loop;

-- Se realiza el corte para el último estado.
If vEstado != 'X' Then
CORTE_ESTADO(phoja, vcFila, vMontoCargoTotal);
End If;

vFilaInicio := vcFila;

-- Se cierra el archivo creado.
GeneraExcel.close_file;

-- Mensaje final, indicando la finalización en la generación y la ruta donde buscar el reporte.
If (FORM_SUCCESS) Then
UTILITARIOS.Mensaje('000001', :Variables.CodIdioma, :Variables.CodSistema, null, 'Reporte Generado con Exito (Ruta: '||vruta||'\'||'PORPODES'||vSesion||'.xls'||')');
--Raise Form_Trigger_Failure;
End if;

EXCEPTION WHEN OTHERS then
MESSAGE(SQLERRM);
MESSAGE(' ', no_acknowledge);
END;