CREATE OR REPLACE PACKAGE plsql2excel
IS
  function test1 return number;
END plsql2excel;
/


CREATE OR REPLACE PACKAGE BODY plsql2excel IS

  function test1 return number
  IS
    excelReport   ExcelDocumentType := ExcelDocumentType();
    documentArray ExcelDocumentLine := ExcelDocumentLine();

    lnCntRange      number;
    lnMaxIme        number;
    lnMaxPrezime    number;
    lnMaxEmail      NUMBER;
    
    v_file   UTL_FILE.FILE_TYPE;

  BEGIN
    -- Open the Excel-Document:
    excelReport.documentOpen;

    -- Configure Styles:
    excelReport.stylesOpen;

      -- Include default style:
      excelReport.defaultStyle;

      -- Add custom styles:
      /* Style for columnd header row */
      excelReport.createStyle(
        p_style_id  => 'ColumnHeader',
        p_font      => 'Verdana',
        p_ffamily   => 'Swiss',
        p_fsize     => '12',
        p_bold      => 'Y',
        p_align_horizontal => 'Center',
        p_align_vertical => 'Bottom',
        p_cell_color => '#FF6600',
        p_cell_pattern => 'Solid',
        p_custom_xml => '<Borders>'||
                        '<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="3"/>'||
                        '<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="3"/>'||
                        '</Borders>'
      );

      excelReport.createStyle(
        p_style_id  => 'WithLinesString',
        p_font      => 'Verdana',
        p_ffamily   => 'Swiss',
        p_fsize     => '12',
        p_custom_xml => '<Borders>'||
                        '<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '</Borders>'
      );

      excelReport.createStyle(
        p_style_id  => 'WithLinesNumber',
        p_font      => 'Verdana',
        p_ffamily   => 'Swiss',
        p_fsize     => '12',
        p_align_horizontal => 'Right',
        p_custom_xml => '<Borders>'||
                        '<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'||
                        '</Borders>'
      );

      excelReport.createStyle(
        p_style_id  => 'ClosingRow',
        p_custom_xml => '<Borders>'||
                        '<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="3"/>'||
                        '</Borders>'
      );

      excelReport.createStyle(
        p_style_id  => 'ClosingRowNumber',
        p_number_format => '###',
        p_custom_xml => '<Borders>'||
                        '<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="3"/>'||
                        '</Borders>'
      );


    -- Close Styles
    excelReport.stylesClose;

    -- Get values for the settings (range and column width):
    select count(*),
           max(length(ime)) as max_ime,
           max(length(prezime)) as max_prezime,
           max(length(email)) as max_email
      into lnCntRange, lnMaxIme, lnMaxPrezime, lnMaxEmail
      from oracle2excel;

    -- Open Worksheet:
    excelReport.worksheetOpen('Popis osoba');

    -- Define Sheet Conditional Formatting values:

    excelReport.worksheetCondFormatOpen(p_range => 'R2C1:R'||TO_CHAR(lnCntRange + 1)||'C4');

    excelReport.createCondFormat(
      p_qualifier => NULL,
      p_value     => 'MOD(ROW(),2)=0',
      p_format_style => 'background:#555555;color:#EEEEEE'
    );
    excelReport.createCondFormat(
      p_qualifier => NULL,
      p_value     => 'MOD(ROW(),2)=1',
      p_format_style => 'background:#DDDDDD;color:#444444'
    );

    excelReport.worksheetCondFormatClose;

    -- Define columns:
    excelReport.defineColumn(p_index => '1', p_width => 13);
    excelReport.defineColumn(p_index => '2', p_width => lnMaxIme + 5);
    excelReport.defineColumn(p_index => '3', p_width => lnMaxPrezime + 5);
    excelReport.defineColumn(p_index => '4', p_width => lnMaxEmail + 5);

    -- Define header row:
    excelReport.rowOpen;

      -- Define header row data cells:
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Br. ');
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Ime ');
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Prezime ');
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Email ');

    excelReport.rowClose;

    -- Populate rows and cells:
    FOR rec IN(select *
                 from oracle2excel
                order by id
              )
    LOOP
      excelReport.rowOpen;

        excelReport.addCell(
          p_style => 'WithLinesNumber',
          p_data_type => 'Number',
          p_data => rec.id
          --p_formula => 'SUM(RC[-5]:RC[-1])'
        );
        excelReport.addCell(
          p_style => 'WithLinesString',
          p_data_type => 'String',
          p_data => rec.ime
        );
        excelReport.addCell(
          p_style => 'WithLinesString',
          p_data_type => 'String',
          p_data => rec.prezime
        );
        excelReport.addCell(
          p_style => 'WithLinesString',
          p_data_type => 'String',
          p_data => rec.email,
          p_HRef => 'mailto:'||rec.email
        );

        --lnUngerade := lnUnGerade + 1;

      excelReport.rowClose;
    END LOOP;

    -- Add closing row:
    excelReport.rowOpen;
    excelReport.addCell(p_style => 'ClosingRowNumber', p_data_type => 'Number');
    excelReport.addCell(p_style => 'ClosingRow', p_data_type => 'String');
    excelReport.addCell(p_style => 'ClosingRow', p_data_type => 'String');
    excelReport.addCell(p_style => 'ClosingRow', p_data_type => 'String');
    excelReport.rowClose;

    -- Close the worksheet:
    excelReport.worksheetClose;

    -- Close the document:
    excelReport.documentClose;

    -- Get the document content:
    documentArray := excelReport.getDocumentData;

    v_file := utl_file.fopen('EXCEL_DIR', 'oracle2excel.xls', 'w', 4000);

    FOR i IN 1..documentArray.COUNT LOOP
      UTL_FILE.put_line(v_file, documentArray(i));
    END LOOP;

    utl_file.fclose(v_file);

    return 1; 


  EXCEPTION
    WHEN OTHERS THEN
      IF utl_file.is_open(v_file) THEN
        utl_file.fclose(v_file);
      END IF;
      dbms_output.put_line(SQLERRM);
      return 0; 
  END test1;


END plsql2excel;
/