Tuesday, July 16, 2013

Export to Excel in Java



Export to Excel is one of the common feature in a typical web based application. There are many approaches to accomplish it. The approach which I am proposing does not need any third party library to export to excel. It used XML based excel file. Means it would be a plain text (xml). No need for any office API or third party API.

 Create a JSP page(ExportToExcel.jsp) for exporting data to Excel.  Source page will direct to ExportToExcel.jsp page.

Set appropriate Content Type and Header in ExportToExcel.jsp file.
response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=ExcelFile.xml"); 
Output excel file would be ExcelFile.xml. You can change file name as per your need. However file extension should be "xml". Though it's extension is XML, window OS will treat it as Excel file based on the content of the file. We will see that below.
"attachment": this option will let user download excel file. If you choose to open excel file then change "attachment" to "inline".

Windows OS will treat xml file as excel based on  in XML content.
In the below example has 13 columns. All the rows are generated in for loop.





<%@page  contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"
import="java.text.*,java.util.Date"%>
    <%
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
    %>
    
<?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">
  <Author>Admin</Author>
  <LastAuthor>Admin</LastAuthor>
  <Created>2013-07-01T21:23:44Z</Created>
  <LastSaved>2013-07-03T15:58:55Z</LastSaved>
  <Version>14.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>13740</WindowHeight>
  <WindowWidth>22995</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s64">
   <NumberFormat ss:Format="Short Date"/>
  </Style>
  <Style ss:ID="s65">
   <NumberFormat ss:Format="@"/>
  </Style>
  <Style ss:ID="s66">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s67">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <NumberFormat ss:Format="Short Date"/>
  </Style>
  <Style ss:ID="s68">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   
   
    <Column ss:Index="2" ss:Width="180"/> <!-- Inbox -->
   <Column ss:Width="123"/> <!-- Current Step -->
   <Column ss:StyleID="s68" ss:Width="110"/>    <!-- Launch Time -->
  
   <Column ss:Width="77.25"/> <!-- Current User Id -->
   <Column ss:Width="77.25"/> <!-- Case Number  -->
   
   <Column ss:Width="120"/> <!-- Primary Borrower Name  -->
   <Column ss:Width="100"/> <!-- Relationship Name -->
   <Column ss:Width="70"/> <!-- Deal Type -->
   <Column ss:Width="60"/> <!--  Division -->
   <Column ss:Width="120"/> <!-- Step Status -->
   <Column ss:Width="80"/> <!-- Assigned Unit -->
   <Column ss:Width="80"/> <!--  Service Unit -->
   <Column  ss:Width="110"/> <!-- Approval Date -->
   
   <Row ss:AutoFitHeight="0"/>
   
   <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s62"><Data ss:Type="String">Column1</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Column2</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column3</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column4</Data></Cell>
    
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column5</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column6</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column7</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column8</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column9</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column10</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column11</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column12</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Column13</Data></Cell>
   </Row>
   
   <%
   
       
 
// Read values here
     
       for (int x = 0 ; x < rows.length ; x++) { // Assume "rows" collection object has all the required data
           String row = rows[x];
           // todo: read data in to Java varaibles; field1 to field13 for this example
       
           %>
               <Row ss:AutoFitHeight="0">
               

               
    <Cell ss:Index="2"><Data ss:Type="String"> <![CDATA[ <%=field1%> ]]> </Data></Cell> 
    <Cell><Data ss:Type="String"><![CDATA[ <%=field2%> ]]></Data></Cell>
    
    <Cell ss:StyleID="s67"><Data ss:Type="String"><![CDATA[ <%=field3%> ]]>  </Data></Cell>
    <Cell><Data ss:Type="String"><![CDATA[ <%=field4%> ]]></Data></Cell>
    <Cell ss:StyleID="s65"><Data ss:Type="String"><![CDATA[ <%=field5%> ]]></Data></Cell>
    
    <Cell><Data ss:Type="String"><![CDATA[ <%=field6%> ]]></Data></Cell>
    <Cell><Data ss:Type="String"><![CDATA[ <%=field7%> ]]></Data></Cell>
    <Cell><Data ss:Type="String"><![CDATA[ <%=field8%> ]]></Data></Cell>
    <Cell><Data ss:Type="String"><![CDATA[ <%=field9%> ]]></Data></Cell>
    <Cell><Data ss:Type="String"><![CDATA[ <%=field10%> ]]></Data></Cell>
    <Cell><Data ss:Type="String"><![CDATA[ <%=field11%> ]]></Data></Cell>
    <Cell><Data ss:Type="String"><![CDATA[ <%=field12%> ]]></Data></Cell>
    <Cell ss:StyleID="s64"><Data ss:Type="String"><![CDATA[ <%=field13%> ]]></Data></Cell>
    

   </Row>
           <% 
       }        
   
   %>
   
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>10</ActiveRow>
     <ActiveCol>12</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row ss:AutoFitHeight="0"/>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row ss:AutoFitHeight="0"/>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>




    

1 comment:

  1. Thanks! Your article helped me to name the file exported as .xls

    ReplyDelete