How to generate Excel output using Oracle PLSQL

Looking for an article on How to generate excel output using Oracle PLSQL, me too. Searched on Google, but could not find easy to understand article. So thought of writing one.

There is some OWA API/package which can directly write data in excel format. We will use API dbms_output.put_line, utl_file available in Oracle. The file generated using some of these methods may not be actual excel, but yes, you can open/edit/save them in Microsoft Excel.

Contents

Method 1

CSV (Comma Separated Values) or Pipe delimited files are flat files where a character like Comma, PIPE or any other character separates words. Check out an article How to open files in excel.

PL/SQL is a procedural extension for SQL. We can use dbms_ouput.put_line or utl_file API to create delimited files which easily opens in excel.

SET FEEDBACK OFF
SET SERVEROUTPUT ON
SPOOL employee_list.dat
DECLARE
CURSOR employee
IS
SELECT
first_name ,
last_name,
email,
phone_number,
hire_Date
FROM
employees;
BEGIN
DBMS_OUTPUT.put_line('First Name|Last Name|Email|Phone Number|Hire Date');
FOR rec_employee IN employee
LOOP
dbms_output.put_line(rec_employee.first_name||'|'||rec_employee.last_name ||'|'||
rec_employee.email || '|' || rec_employee.phone_number||'|' ||
rec_employee.hire_Date);
END LOOP;
END;
/
SPOOL OFF;

Method 2

This is bit lengthy method, it actually creates the structure of excel using XML. The problem with this method is file size is big, as it generates tags for each cell and row.

Let’s design program for this. This is XML excel spreadsheet 2003 standard.

SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 100000
SPOOL employee_data.xls
DECLARE
l_header VARCHAR2(100);
CURSOR employee
IS
SELECT
'<Row>' start_row ,
'<Cell><Data ss:Type="String">' || first_name || '</Data></Cell>' first_name,
'<Cell><Data ss:Type="String">' ||last_name || '</Data></Cell>' last_name,
'<Cell><Data ss:Type="String">' ||email || '</Data></Cell>' email ,
'</Row>' end_row
FROM
employees;
BEGIN
dbms_output.put_line('
<?xml version="1.0"?>
<?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>ATT</LastAuthor>
<Created>2016-10-10T18:17:29Z</Created>
<LastSaved>2016-10-10T18:13:54Z</LastSaved>
<Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7005</WindowHeight>
<WindowWidth>10005</WindowWidth>
<WindowTopX>105</WindowTopX>
<WindowTopY>105</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<NumberFormat/>
</Style>
<Style ss:ID="s24">
<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
</Style>
<Style ss:ID="s26">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>
<Font x:Family="Swiss" ss:Color="#000000" ss:Bold="1"/>
<Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
</Style>
</Styles>');
dbms_output.put_line('<Worksheet ss:Name="Employee">');
dbms_output.put_line('
<Table ss:ExpandedColumnCount="2000" ss:ExpandedRowCount="64000" x:FullColumns="1" x:FullRows="1">');
dbms_output.put_line('<Column ss:AutoFitWidth="0" ss:Width="56.25" />
<Column ss:AutoFitWidth="0" ss:Width="50.00" />
<Column ss:AutoFitWidth="0" ss:Width="100.00" />');
dbms_output.put_line('<Row ss:AutoFitHeight="0" ss:Height="24.75" ss:StyleID="s24">
<Cell ss:StyleID="s26"><Data ss:Type="String">Fist Name</Data></Cell>
<Cell ss:StyleID="s26"><Data ss:Type="String">Last Name</Data></Cell>
<Cell ss:StyleID="s26"><Data ss:Type="String">Email</Data></Cell>
</Row>');
FOR rec_employee IN employee
LOOP
dbms_output.put_line(rec_employee.start_row);
dbms_output.put_line(rec_employee.first_name);
dbms_output.put_line(rec_employee.last_name);
dbms_output.put_line(rec_employee.email);
dbms_output.put_line(rec_employee.end_row);
END LOOP;
dbms_output.put_line( '</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0"/>
<Footer x:Margin="0"/>
</PageSetup>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<PageBreakZoom>100</PageBreakZoom>
<Selected/>
<LeftColumnVisible>41</LeftColumnVisible>
<Panes>
<Pane>
<Number>3</Number>
<ActiveCol>48</ActiveCol>
<RangeSelection>C49</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>' );
dbms_output.put_line('</Workbook>');
END;
/
SPOOL OFF

Final Output

This is final output in excel format.

generate-excel-output-using-oracle-plsql

These are the ways to generate excel output using Oracle PLSQL. Let me know if you found this useful or I am missing any other method.

Related Post

One Comment

Comments are closed.