Sample JAVA Code: Excel reporting - richly formatted data

package com.pagos.sample;

 

import java.awt.Color;

import java.io.File;

import java.io.InputStream;

import javax.servlet.ServletContext;

import javax.servlet.http.HttpServletResponse;

import com.pagos.Application;

import com.pagos.Range;

import com.pagos.Style;

import com.pagos.Workbook;

import com.pagos.Worksheet;

 

public class EmployeeDataFormat {

 

      private String sessionId;

     

      private HttpServletResponse response;

     

      private ServletContext servlet;

     

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

     

      public EmployeeDataFormat(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]);

                  }

            }

           

            //Apply styles

            Range a1i6 = ws.getRange("A1:I6");

            a1i6.getFont().setName("Arial");

            a1i6.getFont().setSize(8);

            a1i6.setHorizontalAlignment(Style.HALIGN_CENTER);

 

            Range a1i1 = ws.getRange("A1:I1");

            a1i1.getFont().setBold(true);

            a1i1.getInterior().setColor(new Color(0x33CCCC));

 

            Range a6i6 = ws.getRange("A6:I6");

            a6i6.getFont().setBold(true);

            a6i6.getInterior().setColor(new Color(0x33CCCC));

 

            String numberFormat = "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)";   

            Range c2c6 = ws.getRange("C2:C6");

            Range e2e6 = ws.getRange("E2:E6");

            Range g2g6 = ws.getRange("G2:G6");

            Range i2i6 = ws.getRange("I2:I6");

            c2c6.setNumberFormat(numberFormat);

            e2e6.setNumberFormat(numberFormat);

            g2g6.setNumberFormat(numberFormat);

            i2i6.setNumberFormat(numberFormat);

           

           

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

            file.mkdirs();

 

            //Save workbook to a temporary file for streaming

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

           

            //Prepare response for streaming        

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

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

                       

            //Stream workbook to response

            InputStream is = servlet.getResourceAsStream("/Spreadsheets/Temp/EmployeeDataFormat" +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/EmployeeDataFormat" +sessionId + ".xml");

           

            //Delete temporary file

            file.delete();

 

            //Close workbook

            wb.close();

 

            //End response

            response.flushBuffer();

      }

     

}