To Prepare Report / Consolidate data in Excel Format

Using JAVA APIs, excel report is generated with proper headers and appropriate width for each column. Font and style is defined for header and details data. Sample excel report attached hereby is generated using code snippet. Code can be enhanced to freeze the pane and group the columns and rows.

private void createCasesReport(Object[] arrOpenCases) throws SQLException {
  log.debug("Start of createTBCasesReport method...");

  int arrLength = arrOpenCases.length;
  log.debug("length of arrOpenCases ARRAY : " + arrLength);

  String summaryFileDirectoy = appProperties.getProperty("OneTimeTBReportDir");
  log.debug("summaryFileDirectoy: " + summaryFileDirectoy);

  String summaryFilePath = summaryFileDirectoy + "TB_OpenCases" + ".xls";
  log.debug("summaryFilePath: " + summaryFilePath);

  try {
   int rownum = 0;
   OutputStream os = new FileOutputStream(summaryFilePath);

   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet = wb.createSheet("One Time TB Report");

   /****************Creating Text Region - Start*****************/
   HSSFRow textRow = null;
   HSSFCell textRowCell = null;
   HSSFCell valueCell = null;
   HSSFCellStyle textRowCellStyle = wb.createCellStyle();
   HSSFFont textRowFont = wb.createFont();
   textRowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   textRowCellStyle.setFont(textRowFont);

   textRow = sheet.createRow(rownum);

   /***************Creating Header Row for the table - Start*********************/
   HSSFFont headerFont = wb.createFont();
   headerFont.setFontName("Courier New");
   headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   headerFont.setFontHeightInPoints((short) 10);

   HSSFCellStyle headerCellStyle = wb.createCellStyle();
   headerCellStyle.setFont(headerFont);
   headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
   headerCellStyle.setWrapText(true);

   HSSFCellStyle numericCellStyle = wb.createCellStyle();
   HSSFDataFormat numericFormat = wb.createDataFormat();
   numericCellStyle.setDataFormat(numericFormat.getFormat("#0.00"));

   textRowCell = textRow.createCell((short) 0);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("Sr. No.");

   textRowCell = textRow.createCell((short) 1);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("Mkt Div Code");

   textRowCell = textRow.createCell((short) 2);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("Case Number");
   //sheet.autoSizeColumn((short) 3);
   
   textRowCell = textRow.createCell((short) 3);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("Control Account");
   //sheet.autoSizeColumn((short) 4);
      
   textRowCell = textRow.createCell((short) 4);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("DR/CR");
   //sheet.autoSizeColumn((short) 5);

   textRowCell = textRow.createCell((short) 5);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("Case Balance");
   //sheet.autoSizeColumn((short) 6);
   
   textRowCell = textRow.createCell((short) 6);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("Running DR/CR");
   //sheet.autoSizeColumn((short) 6);
   
   textRowCell = textRow.createCell((short) 7);
   textRowCell.setCellStyle(headerCellStyle);
   textRowCell.setCellValue("Running Case Balance");
   //sheet.autoSizeColumn((short) 6);

   rownum += 1;
   
   log.debug("Before for loop...");
   for (int i = 0; i < arrLength; i++) {

    STRUCT openCaseStruct = (STRUCT) arrOpenCases[i];
    Object[] openCaseArr = openCaseStruct.getAttributes();
       
    textRow = sheet.createRow(rownum);

    textRowCell = textRow.createCell((short) 0);
    textRowCell.setCellValue((new Integer(i)).toString());

    textRowCell = textRow.createCell((short) 1);
    textRowCell.setCellValue(openCaseArr[0] !=null ? openCaseArr[0].toString() : ""); //Mkt Div Code
    //sheet.autoSizeColumn((short) 2);

    textRowCell = textRow.createCell((short) 2);
    textRowCell.setCellValue(openCaseArr[1] !=null ? openCaseArr[1].toString() : ""); //Case Number
    //sheet.autoSizeColumn((short) 2);
    
    textRowCell = textRow.createCell((short) 3);
    textRowCell.setCellValue(openCaseArr[2] !=null ? openCaseArr[2].toString() : ""); //Control Account
    //sheet.autoSizeColumn((short) 2);

    textRowCell = textRow.createCell((short) 4);
    textRowCell.setCellValue(openCaseArr[3] !=null ? openCaseArr[3].toString() : ""); //DR/CR

    textRowCell = textRow.createCell((short) 5);
    textRowCell.setCellStyle(numericCellStyle);
    textRowCell.setCellValue( openCaseArr[4] !=null ? ((BigDecimal)openCaseArr[4]).doubleValue() : 0.0 ); //Case Balance
    
    textRowCell = textRow.createCell((short) 6);
    textRowCell.setCellValue(openCaseArr[5] !=null ? openCaseArr[5].toString() : ""); //Running DR/CR
    
    textRowCell = textRow.createCell((short) 7);
    textRowCell.setCellStyle(numericCellStyle);
    textRowCell.setCellValue(openCaseArr[6] !=null ? ((BigDecimal)openCaseArr[6]).doubleValue() : 0.0 ); //Running Case Balance

    rownum += 1;
   }

   wb.write(os);
   os.close();
  }
  catch (Exception e) {
   log.error("Exception occured while creating one time TB report: ", e);
   return;
  }

  log.debug("End of createTBCasesReport method...");
 }