How to create Excel with multiple worksheets

As many of you might have come across a requirement like business wants to open the report output in Excel file.

There is no brainier in doing this, generate the report output and save the file with .csv extension; Problem solved. Occasionally it becomes tricky when the user request the output in single Excel file with multiple worksheets. This can be achieved in many ways.

Here is a simple method which I follow to achieve this functionality; As I more cautious on cost :) 

We can achieve this functionality by generating the output in XML file format.
Let’s first see the how XML structure look like for a excel output. If you can generate the output file in the below structure, then you can achieve multiple worksheets in a single Excel file. The choice of programming language is up to you.

<?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>Surendra</LastAuthor> 
  <Created>2009-05-11T06:30:00Z</Created> 
  <LastSaved>2009-06-16T00:50:38Z</LastSaved> 
  <Version>12.00</Version>   
 </DocumentProperties> 
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> 
  <WindowHeight>10005</WindowHeight> 
  <WindowWidth>10005</WindowWidth> 
  <WindowTopX>120</WindowTopX> 
  <WindowTopY>135</WindowTopY> 
  <ProtectStructure>False</ProtectStructure>  
  <ProtectWindows>False</ProtectWindows> 
</ExcelWorkbook> 
 <Styles> 
  <Style ss:ID="Default" ss:Name="Normal"> 
  <Alignment ss:Vertical="Bottom"/>  
  </Style> 
 </Styles>  
<Worksheet ss:Name="Sheet q"> 
<Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">  
<Row ss:AutoFitHeight="0" ss:Height="13.5">
<Cell>
<Data> 
</Data>
</Cell>
</Row> 
    </Worksheet>
</Workbook>

Here is a sample SQL*Plus script, this script to print TNAME, TABTYPE from table TAB. To show the multiple worksheets concept, i printed the same output in both the worksheets. You can run the script as is.


This script output is Excel_Ouput.xml, double click this file to open in Excel, you should be able to view 2 worksheets name First Work Sheet, Second Work Sheet. As a bonus in this script I added few color coding tags too. 


Have a look at it and use it as you like.
/* Begin SQL*Plus script */

Set heading off
Set feedback off
Set verify off
Set linesize 1000
Set pagesize 0
Set space 0
Set newpage 1
Set trimspool on
Set Termout off


SPOOL  EXCEL_OUTPUT.xml;
SELECT
'<?xml version="1.0"?> ' 
||CHR(10)||
'<?mso-application progid="Excel.Sheet"?> '
||CHR(10)||
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" '
||CHR(10)||
' xmlns:o="urn:schemas-microsoft-com:office:office" '
||CHR(10)||
' xmlns:x="urn:schemas-microsoft-com:office:excel" '
||CHR(10)||
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" '
||CHR(10)||
' xmlns:html="http://www.w3.org/TR/REC-html40"> '
||CHR(10)||
' <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> '
||CHR(10)||
'  <LastAuthor>Wipro Technologies</LastAuthor> '
||CHR(10)||
'  <Created>2009-05-11T06:30:00Z</Created> '
||CHR(10)||
'  <LastSaved>2009-06-16T00:50:38Z</LastSaved> '
||CHR(10)||
'  <Version>12.00</Version>  ' 
||CHR(10)||
' </DocumentProperties> '
||CHR(10)||
' <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> '
||CHR(10)||
'  <WindowHeight>10005</WindowHeight> '
||CHR(10)||
'  <WindowWidth>10005</WindowWidth> '
||CHR(10)||
'  <WindowTopX>120</WindowTopX> '
||CHR(10)||
'  <WindowTopY>135</WindowTopY> '
||CHR(10)||
'  <ProtectStructure>False</ProtectStructure>  '
||CHR(10)||
'  <ProtectWindows>False</ProtectWindows> '
||CHR(10)||
' </ExcelWorkbook> '
||CHR(10)||
' <Styles> '
||CHR(10)||
'  <Style ss:ID="Default" ss:Name="Normal"> '
||CHR(10)||
'   <Alignment ss:Vertical="Bottom"/>  '
||CHR(10)||
'  </Style> '
||'  <Style ss:ID="s77"> '
||CHR(10)||
'   <Interior ss:Color="#00B050" ss:Pattern="Solid"/> '
||CHR(10)||
'  </Style> '
||CHR(10)||
' </Styles> ' 
FROM DUAL;
/* Work Sheet 1 */
SELECT '<Worksheet ss:Name="First Work Sheet"> '
||CHR(10)||
'<Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">  '
||CHR(10)||
'<Row ss:AutoFitHeight="0" ss:Height="13.5">'
||CHR(10)||
'<Cell><Data ss:Type="String">'||'TNAME'||'</Data></Cell>'||CHR(10)||
'<Cell><Data ss:Type="String">'||'TABTYPE'||'</Data></Cell>'||CHR(10)||
'</Row>' 
FROM Dual;
SELECT  '<Row ss:AutoFitHeight="0" ss:Height="13.5">'||CHR(10)||
'<Cell><Data ss:Type="String">'||A.TNAME||'</Data></Cell>'||CHR(10)||
'<Cell ss:StyleID="s77"><Data ss:Type="String">'||A.TABTYPE||'</Data></Cell>'||
'</Row>'
FROM (SELECT   TNAME,TABTYPE FROM  TAB) A;
SELECT '</Table> ' 
||CHR(10)||
'</Worksheet>'
FROM DUAL;
/* Work Sheet 2 */
SELECT '<Worksheet ss:Name="Second Work Sheet"> '
||CHR(10)||
'<Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">  '
||CHR(10)||
'<Row ss:AutoFitHeight="0" ss:Height="13.5">'
||CHR(10)||
'<Cell><Data ss:Type="String">'||'TNAME'||'</Data></Cell>'||CHR(10)||
'<Cell><Data ss:Type="String">'||'TABTYPE'||'</Data></Cell>'||CHR(10)||
'</Row>' 
FROM Dual;
SELECT  '<Row ss:AutoFitHeight="0" ss:Height="13.5">'||CHR(10)||
'<Cell><Data ss:Type="String">'||A.TNAME||'</Data></Cell>'||CHR(10)||
'<Cell><Data ss:Type="String">'||A.TABTYPE||'</Data></Cell>'||
'</Row>'
FROM (SELECT   TNAME,TABTYPE FROM  TAB) A;
SELECT '</Table> ' 
||CHR(10)||
'</Worksheet>'
FROM DUAL;
SELECT '</Workbook>'  FROM DUAL;
SPOOL OFF;
exit;

/* End SQL*Plus script */