Sample JAVA Code: Excel reporting - data only

package com.pagos.sample;

 

import java.io.File;

import java.io.InputStream;

import javax.servlet.ServletContext;

import javax.servlet.http.HttpServletResponse;

import com.pagos.Application;

import com.pagos.Workbook;

import com.pagos.Worksheet;

 

public class EmployeeData {

 

      private String sessionId;

     

      private HttpServletResponse response;

     

      private ServletContext servlet;

     

      private final static String PATH = "C:/Sun/AppServer/domains/domain1/applications/j2ee-modules/PSC42Samples/";

     

      public EmployeeData(String sessionId, HttpServletResponse response, ServletContext servlet) {

            this.sessionId = sessionId;

            this.response = response;

            this.servlet = servlet;

      }

     

      public void executeSample() throws Exception

      {

 

            //Create an instance of the application

            Application app = new Application();

           

            //Add a workbook

            Workbook wb = app.getWorkbooks().add();

           

            //Load a worksheet from added workbook

            Worksheet ws = wb.getWorksheets().getItem("Sheet1");

 

            //Prepare worksheet data

            Object[][] data = new Object[][] {

                                                           { "Employee", "Volume Q1", "Sales Q1", "Volume Q2", "Sales Q2", "Volume Q3", "Sales Q3", "Volume Q4", "Sales Q4" }

                                                           ,{ "Helen", new Integer(5), new Float(7906.25F), new Integer(40), new Integer(63250), new Integer(69), new Float(109106.25F), new Integer(31), new Float(49018.75F) }

                                                           ,{ "Fred", new Integer(32), new Integer(45640), new Integer(80), new Integer(114100), new Integer(45), new Float(64181.25), new Integer(87), new Float(124083.75F) }

                                                           ,{ "John", new Integer(60), new Integer(107355), new Integer(65), new Float(116301.25F), new Integer(90), new Float(161032.5F), new Integer(50), new Float(89462.5F) }

                                                           ,{ "Jane", new Integer(40), new Integer(54770), new Integer(1), new Float(1369.25F), new Integer(22), new Float(30123.5F), new Integer(65), new Float(89001.25F) }

                                                           ,{ "Total", new Integer(137), new Float(215671.25F), new Integer(186), new Float(295020.5F), new Integer(226), new Float(364443.5F), new Integer(233), new Float(351566.25F) }

            };

 

            int row;

            int column;

 

            //Bind values to cells

            for (row = 0; row < data.length; row++)

            {

                  for (column = 0; column < data[0].length; column++)

                  {

                        ws.getCells(row, column).setValue(data[row][column]);

                  }

            }

           

            File file = new File(PATH+"Spreadsheets/Temp/");

            file.mkdirs();

 

            //Save workbook to a temporary file for streaming

            wb.saveAs(PATH+"Spreadsheets/Temp/EmployeeData" +sessionId + ".xml");      

           

            //Prepare response for streaming        

            response.setContentType("application/vnd.ms-excel");

            response.setHeader("Content-Disposition", "attachment; filename=EmployeeData.xml");      

           

            //Stream workbook to response

            InputStream is = servlet.getResourceAsStream("/Spreadsheets/Temp/EmployeeData" +sessionId + ".xml");       

            byte buffer[] = new byte[5000];

            int length;

            do

            {

                  length = is.read(buffer);

                  if(length > 0)

                        response.getOutputStream().write(buffer,0,length);

            }

            while(length != -1);

           

            is.close();

 

            file = new File(PATH+"Spreadsheets/Temp/EmployeeData" +sessionId + ".xml");

           

            //Delete temporary file

            file.delete();

 

            //Close workbook

            wb.close();

 

            //End response

            response.flushBuffer();

      }

     

}