Home > PL/SQL > PL/SQL Excel File Generator

PL/SQL Excel File Generator

I’ve done a lot with ExcelTable lately and achieved pretty much all I wanted to do.

Any ideas or enhancement requests are still welcome but I’ll now focus on a new “recreational” project : ExcelGen.

Yes… Excel stuff again 😛

There are already some PL/SQL tools out there, so nothing new under the sun really, but since I did ExcelTable, why not do the opposite : generate Excel files from relational data.

ExcelGen is a rework of unpublished code I made a while ago, with some enhancements of course.

The main goal is to generate Excel files out of SQL data sources, provided in the form of query strings or REF cursors.

I’ve implemented a styling framework but that will play a secondary role for now, as formatting options are only provided for table headers in this first version.

As usual, everything’s available on GitHub :
 
/mbleron/ExcelGen
 

Examples

  1. Single query to sheet mapping, with header formatting : employees.xlsx
  2. declare
    
      sqlQuery   varchar2(32767) := 'select * from hr.employees';
      sheetName  varchar2(31 char) := 'Sheet1';
      ctxId      ExcelGen.ctxHandle;
      
    begin
      
      ctxId := ExcelGen.createContext();  
      ExcelGen.addSheetFromQuery(ctxId, sheetName, sqlQuery);
          
      ExcelGen.setHeader(
        ctxId
      , sheetName
      , p_style => ExcelGen.makeCellStyle(
                     p_ctxId => ctxId
                   , p_font  => ExcelGen.makeFont('Calibri',11,true)
                   , p_fill  => ExcelGen.makePatternFill('solid','LightGray')
                   )
      , p_frozen     => true
      , p_autoFilter => true
      );
      
      ExcelGen.setDateFormat(ctxId, 'dd/mm/yyyy');
      
      ExcelGen.createFile(ctxId, 'TEST_DIR', 'employees.xlsx');
      ExcelGen.closeContext(ctxId);
      
    end;
    /
    

     

  3. Multiple queries, with table layout : dept_emp.xlsx
  4. declare
      ctxId      ExcelGen.ctxHandle;
    begin
      ctxId := ExcelGen.createContext();
      
      -- add dept sheet
      ExcelGen.addSheetFromQuery(ctxId, 'dept', 'select * from hr.departments');
      ExcelGen.setHeader(ctxId, 'dept', p_autoFilter => true);
      ExcelGen.setTableFormat(ctxId, 'dept', 'TableStyleLight2');
    
      -- add emp sheet
      ExcelGen.addSheetFromQuery(ctxId, 'emp', 'select * from hr.employees where salary >= :1 order by salary desc');
      ExcelGen.setBindVariable(ctxId, 'emp', '1', 7000);  
      ExcelGen.setHeader(ctxId, 'emp', p_autoFilter => true);
      ExcelGen.setTableFormat(ctxId, 'emp', 'TableStyleLight7');
      
      ExcelGen.setDateFormat(ctxId, 'dd/mm/yyyy');
      
      ExcelGen.createFile(ctxId, 'TEST_DIR', 'dept_emp.xlsx');
      ExcelGen.closeContext(ctxId);
      
    end;
    /
    

     

  5. Ref cursor paginated over multiple sheets : all_objects.xlsx
  6. declare
      rc         sys_refcursor;
      sheetName  varchar2(128) := 'sheet${PNUM}';
      ctxId      ExcelGen.ctxHandle;
    begin
      
      open rc for 
      select * from all_objects where owner = 'SYS';
    
      ctxId := ExcelGen.createContext();
      
      ExcelGen.addSheetFromCursor(
        p_ctxId     => ctxId
      , p_sheetName => sheetName
      , p_rc        => rc
      , p_tabColor  => 'DeepPink'
      , p_paginate  => true
      , p_pageSize  => 10000
      );
        
      ExcelGen.setHeader(
        ctxId
      , sheetName
      , p_style  => ExcelGen.makeCellStyle(ctxId, p_fill => ExcelGen.makePatternFill('solid','LightGray'))
      , p_frozen => true
      );
      
      ExcelGen.createFile(ctxId, 'TEST_DIR', 'all_objects.xlsx');
      ExcelGen.closeContext(ctxId);
      
    end;
    /
    

 

Coming next

  • Support for password-encrypted files
  • XLSB file format
  • ODF file format

 

Categories: PL/SQL
  1. May 22, 2020 at 16:06

    Create stuff as always, Marc.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: