Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createCellStyle

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle.

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table.

Usage

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.BaseSS.java

/**
 * Set the passed in cell to blue//from w  w  w .j  av  a  2s  . c  o m
 * @param cell
 * @return
 */
protected void styleBlue(SpreadsheetCell cell) {
    cell.setCallbackStyle(new SpreadsheetCellStyleCallBackI() {
        public HSSFCellStyle getCellStyle(HSSFWorkbook wb, SpreadSheet sheet, HSSFCell cell) {

            HSSFFont font = fonts.get("blue");
            if (font == null) {
                String[] s = BLUE.split(",");
                HSSFColor c = sheet.setColor(wb, Integer.parseInt(s[0]), Integer.parseInt(s[1]),
                        Integer.parseInt(s[2]));
                font = wb.createFont();
                if (c != null) {
                    font.setColor(c.getIndex());
                }
                font.setItalic(true);
                fonts.put("blue", font);
            }
            if (styleQuantityBlue == null) {
                HSSFCellStyle style = wb.createCellStyle();
                style.setFont(font);
                styleQuantityBlue = style;
            }
            return styleQuantityBlue;
        }
    });
}

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.BaseSS.java

/**
 * Sheet title //from  w  w  w.java  2  s  .c om
 * @param sheet
 * @param startCol
 * @param title
 * @param Number of columns to span
 */
protected void outputTitle(SpreadSheet sheet, int startCol, String title, int columnCount) throws Exception {

    sheet.addRowFormat(FORMAT_BOLD);
    sheet.setCol(startCol);
    sheet.addColumnFormat(ALIGN_CENTER);

    SpreadsheetCell cell = sheet.addCell(title, STYLE_TITLE);

    cell.setCallback(new SpreadsheetCellCallBackI() {
        public HSSFRichTextString getCellValue(HSSFWorkbook wb, SpreadSheet sheet, HSSFCell cell,
                String value) {
            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short) 16);
            HSSFCellStyle style = wb.createCellStyle();
            style.setFont(font);
            style.setAlignment((short) ExtendedFormatRecord.CENTER);
            cell.setCellStyle(style);

            HSSFRichTextString richString = new HSSFRichTextString(value);
            return richString;
        }
    });

    mergeColumns(sheet, cell, columnCount);
    sheet.incrementRow();
}

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheet.java

/**
  * Get a default cell format//  w  w w .  j  ava  2 s.  co  m
  * @param wb
  * @return
  */
public HSSFCellStyle getCellStyleDefault(HSSFWorkbook wb, int row, int col) {
    if (styles.containsKey(DEFAULT_STYLE_ID)) {
        return styles.get(DEFAULT_STYLE_ID);
    }
    HSSFCellStyle style = wb.createCellStyle();
    setCellStyle(wb, style, null, DEFAULT_STYLE_ID);
    return style;
}

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadsheetCell.java

/**
 * Get cell format<p>/*from   w  ww  .java 2  s .  c om*/
 * 
 * Thanks to http://stackoverflow.com/questions/15248284/using-poi-how-to-set-the-cell-type-as-number
 * @param wb
 * @return
 */
public HSSFCellStyle getCellStyle(HSSFWorkbook wb) {

    //EX1
    if (sheet.containsStyleId(styleId)) {
        return sheet.getStyle(styleId);
    }

    HSSFCellStyle style = wb.createCellStyle();
    Integer clazzX = clazz != null ? clazz : (headerCell != null ? headerCell.clazz : null);

    switch (clazzX != null ? clazzX : CLASS_STRING) {
    case CLASS_DATE:
        if (!isHeader()) {
            CreationHelper createHelper = wb.getCreationHelper();
            style = wb.createCellStyle();
            style.setDataFormat(createHelper.createDataFormat()
                    .getFormat(sheet.getDateFormat() != null ? sheet.getDateFormat() : "m/d/yy"));
        }
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_PERCENTAGE:
        style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_INTEGER:
    case CLASS_LONG:
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        break;

    case CLASS_DOUBLE:
    case CLASS_UKURS:
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        break;

    case CLASS_STRING:
    case CLASS_INTEGER_LEFT:
    case CLASS_BOOLEAN:
    case CLASS_CHARACTER:
    default:
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    }

    sheet.setCellStyle(wb, style, this, styleId);
    return style;
}

From source file:com.sigueme.frontend.controller.ExportController.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//from   w ww  .j av  a 2  s.c o m
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());

    for (Row row : sheet) {
        for (Cell cell : row) {
            cell.setCellValue(cell.getStringCellValue().toUpperCase());
            cell.setCellStyle(style);

        }
    }
}

From source file:com.simeosoft.util.XlsUtils.java

License:Open Source License

public static void addXlsWorksheet(HSSFWorkbook wb, String sheetName, ArrayList<ArrayList<Object>> data) {
    HSSFSheet s = wb.createSheet(sheetName);
    HSSFRow r = null;/*from  w  w w  .j  a  v  a 2s  . co m*/
    HSSFCell c = null;
    int i = 0;
    HSSFDataFormat df = wb.createDataFormat();
    HSSFCellStyle cs = wb.createCellStyle();
    for (ArrayList<Object> record : data) {
        r = s.createRow(i);
        i++;
        short y = 0;
        /*
         * tipi dato previsti: null,String,Date,Integer,Time,Timestamp
         * - gli altri tipi restituiscono errore
         */
        for (Object obj : record) {
            c = r.createCell(y);
            y++;
            if (obj == null) {
                c.setCellValue("");
                continue;
            }
            if (obj instanceof String) {
                c.setCellValue((String) obj);
                continue;
            }
            // MODIF - java.util.Date o java.sql.Date?                
            if (obj instanceof java.sql.Date || obj instanceof java.sql.Timestamp
                    || obj instanceof java.sql.Time) {
                HSSFCellStyle csd = wb.createCellStyle();
                csd.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy"));
                c.setCellValue((java.sql.Date) obj);
                c.setCellStyle(csd);
                continue;
            }
            if (obj instanceof Integer) {
                c.setCellValue(((Integer) obj).doubleValue());
                continue;
            }
            if (obj instanceof BigDecimal) {
                c.setCellValue(((BigDecimal) obj).doubleValue());
                cs.setDataFormat(df.getFormat("######0.0000"));
                c.setCellStyle(cs);
                continue;
            }
            // default - non previsto                
            c.setCellValue("ERRORE: TIPO NON PREVISTO: " + obj.getClass());
        }
    }
}

From source file:com.siva.javamultithreading.ExcelUtil.java

public static void copyCell(HSSFWorkbook newWorkbook, HSSFCell oldCell, HSSFCell newCell,
        Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        int stHashCode = oldCell.getCellStyle().hashCode();
        HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
        if (newCellStyle == null) {
            newCellStyle = newWorkbook.createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            styleMap.put(stHashCode, newCellStyle);
        }/*from  w  ww  . j a v a 2  s .c  om*/
        newCell.setCellStyle(newCellStyle);
    }
    switch (oldCell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getRichStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }
}

From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java

public void getChangeARReport(HSSFWorkbook wb, List changeARReport) {
    List<ARNirvana> listAR = changeARReport;
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    // set Header Report (Row 1)
    HSSFCellStyle styleC1 = wb.createCellStyle();
    HSSFRow row1 = sheet.createRow(0);/*from  w  ww. j  a  va  2 s .  c om*/
    HSSFCell cell1 = row1.createCell(1);
    cell1.setCellValue("CHANGE AR REPORT");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cell1.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B1:I1"));

    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleNumber = wb.createCellStyle();
    styleNumber.setAlignment(styleC21.ALIGN_RIGHT);
    styleNumber.setDataFormat(currency.getFormat("#,##0.00"));
    //        styleNumber.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0"));

    ARNirvana arTemp = new ARNirvana();
    if (!listAR.isEmpty()) {
        arTemp = (ARNirvana) listAR.get(0);
    }
    // Row 2
    HSSFRow row2 = sheet.createRow(1);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("Print on : ");
    cell21.setCellStyle(styleC21);
    HSSFCell cell22 = row2.createCell(2);
    Date date = new Date();
    SimpleDateFormat sm = new SimpleDateFormat("dd/MM/yyyy");
    String strDate = sm.format(date);
    cell22.setCellValue(strDate);
    cell22.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("C2:E2"));

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(1);
    cell31.setCellValue("Report Of : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(2);
    cell32.setCellValue(arTemp.getPrintofdatePage());
    cell32.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("C3:D3"));

    // Row 4
    HSSFRow row4 = sheet.createRow(3);
    HSSFCell cell41 = row4.createCell(1);
    cell41.setCellValue("Department : ");
    cell41.setCellStyle(styleC21);
    HSSFCell cell42 = row4.createCell(2);
    cell42.setCellValue(arTemp.getDepartmentPage());
    cell42.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("C4:E4"));

    // Header Table
    HSSFCellStyle styleHeader = wb.createCellStyle();
    styleHeader.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleHeader.setAlignment(styleHeader.ALIGN_CENTER);
    styleHeader.setBorderTop(styleHeader.BORDER_THIN);
    styleHeader.setBorderLeft(styleHeader.BORDER_THIN);
    styleHeader.setBorderBottom(styleHeader.BORDER_THIN);
    styleHeader.setBorderRight(styleHeader.BORDER_THIN);
    HSSFCellStyle styleDetailTable = wb.createCellStyle();
    styleDetailTable.setAlignment(styleDetailTable.ALIGN_LEFT);
    styleDetailTable.setBorderLeft(styleDetailTable.BORDER_THIN);
    styleDetailTable.setBorderRight(styleDetailTable.BORDER_THIN);
    HSSFCellStyle styleDetailTableNumber = wb.createCellStyle();
    styleDetailTableNumber.setAlignment(styleDetailTableNumber.ALIGN_RIGHT);
    styleDetailTableNumber.setBorderLeft(styleDetailTableNumber.BORDER_THIN);
    styleDetailTableNumber.setBorderRight(styleDetailTableNumber.BORDER_THIN);
    styleDetailTableNumber.setDataFormat(currency.getFormat("#,##0.00"));
    HSSFCellStyle styleDetailTableBorderBottom = wb.createCellStyle();
    styleDetailTableBorderBottom.setBorderTop(styleDetailTableBorderBottom.BORDER_THIN);

    HSSFRow row6 = sheet.createRow(8);
    HSSFCell cell61 = row6.createCell(1);
    cell61.setCellValue("No.");
    cell61.setCellStyle(styleHeader);
    sheet.autoSizeColumn(1);
    HSSFCell cell62 = row6.createCell(2);
    cell62.setCellValue("Inv No");
    cell62.setCellStyle(styleHeader);
    sheet.autoSizeColumn(2);
    HSSFCell cell63 = row6.createCell(3);
    cell63.setCellValue("AR Code");
    cell63.setCellStyle(styleHeader);
    sheet.autoSizeColumn(3);
    HSSFCell cell64 = row6.createCell(4);
    cell64.setCellValue("Inv To");
    cell64.setCellStyle(styleHeader);
    sheet.autoSizeColumn(4);
    HSSFCell cell65 = row6.createCell(5);
    cell65.setCellValue("Acc Code");
    cell65.setCellStyle(styleHeader);
    sheet.autoSizeColumn(5);
    HSSFCell cell66 = row6.createCell(6);
    cell66.setCellValue("Gross");
    cell66.setCellStyle(styleHeader);
    sheet.autoSizeColumn(6);
    HSSFCell cell67 = row6.createCell(7);
    cell67.setCellValue("Amount");
    cell67.setCellStyle(styleHeader);
    sheet.autoSizeColumn(7);
    HSSFCell cell68 = row6.createCell(8);
    cell68.setCellValue("Cur");
    cell68.setCellStyle(styleHeader);
    sheet.autoSizeColumn(8);

    int count = 9 + listAR.size();
    int start = 11;
    int end = 0;
    int num = 0;
    for (int r = 9; r < count; r++) {
        if (num <= (listAR.size() - 1)) {
            HSSFRow row = sheet.createRow(r);
            HSSFCell cell5 = row.createCell(1);
            cell5.setCellValue((num + 1));
            cell5.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(1);
            HSSFCell cell6 = row.createCell(2);
            cell6.setCellValue(listAR.get(num).getIntreference());
            cell6.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(2);
            HSSFCell cell7 = row.createCell(3);
            if (listAR.get(num).getCustomerid() != null) {
                cell7.setCellValue(listAR.get(num).getCustomerid());
                sheet.autoSizeColumn(3);
            } else {
                cell7.setCellValue("");
                sheet.autoSizeColumn(3);
            }
            cell7.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(3);
            HSSFCell cell8 = row.createCell(4);
            if (listAR.get(num).getCustomername() != null) {
                cell8.setCellValue(listAR.get(num).getCustomername());
                sheet.autoSizeColumn(4);
            } else {
                cell8.setCellValue("");
                sheet.autoSizeColumn(4);
            }
            cell8.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(4);
            HSSFCell cell9 = row.createCell(5);
            if (listAR.get(num).getSalesaccount1() != null) {
                cell9.setCellValue(listAR.get(num).getSalesaccount1());
                sheet.autoSizeColumn(5);
            } else {
                cell9.setCellValue("");
                sheet.autoSizeColumn(5);
            }
            cell9.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(5);
            HSSFCell cell10 = row.createCell(6);
            if (listAR.get(num).getSalesamt() != null) {
                cell10.setCellValue(listAR.get(num).getSalesamt().doubleValue());
                sheet.autoSizeColumn(6);
            } else {
                cell10.setCellValue(0.00);
                sheet.autoSizeColumn(6);
            }
            cell10.setCellStyle(styleDetailTableNumber);
            sheet.autoSizeColumn(6);
            HSSFCell cell11 = row.createCell(7);
            if (listAR.get(num).getAramt() != null) {
                cell11.setCellValue(listAR.get(num).getAramt().doubleValue());
                sheet.autoSizeColumn(7);
            } else {
                cell11.setCellValue(0.00);
                sheet.autoSizeColumn(7);
            }
            cell11.setCellStyle(styleDetailTableNumber);
            HSSFCell cell12 = row.createCell(8);
            cell12.setCellValue(listAR.get(num).getCurrencyid());
            cell12.setCellStyle(styleDetailTable);
            sheet.autoSizeColumn(8);
            num++;
        }
    }

    HSSFRow row = sheet.createRow(count);
    row.createCell(1).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(2).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(3).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(4).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(5).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(6).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(7).setCellStyle(styleDetailTableBorderBottom);
    row.createCell(8).setCellStyle(styleDetailTableBorderBottom);

}

From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java

public void genCollectionReport(HSSFWorkbook wb, List collectionNirvanaList) {
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);
    CollectionNirvana dataheader = new CollectionNirvana();
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC22.ALIGN_CENTER);

    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));
    styleC25.setWrapText(true);/*ww  w. j  a  v  a  2s. c o  m*/
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    HSSFCellStyle styleC26 = wb.createCellStyle();
    styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC26.setDataFormat(currency.getFormat("#,##0"));
    styleC26.setAlignment(styleC26.ALIGN_CENTER);
    styleC26.setWrapText(true);
    styleC26.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    HSSFCellStyle styleC27 = wb.createCellStyle();
    styleC27.setAlignment(styleC27.ALIGN_RIGHT);
    styleC27.setDataFormat(currency.getFormat("#,##0.00"));
    styleC27.setWrapText(true);
    styleC27.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    HSSFCellStyle styleC28 = wb.createCellStyle();
    styleC28.setAlignment(styleC28.ALIGN_CENTER);
    styleC28.setDataFormat(currency.getFormat("#,##0"));
    styleC28.setWrapText(true);
    styleC28.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    HSSFCellStyle styleC29 = wb.createCellStyle();
    styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC29.setWrapText(true);
    styleC29.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    HSSFCellStyle styleC30 = wb.createCellStyle();
    styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC30.setAlignment(styleC30.ALIGN_CENTER);
    styleC30.setWrapText(true);
    styleC30.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    if (!collectionNirvanaList.isEmpty()) {
        dataheader = (CollectionNirvana) collectionNirvanaList.get(0);

        // set Header Report (Row 1)
        HSSFCellStyle styleC1 = wb.createCellStyle();
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("COLLECTION REPORT");
        styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
        cell1.setCellStyle(styleC1);
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:E1"));

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Print On : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getSystemdate());
        cell22.setCellStyle(styleC22);
        //            sheet.addMergedRegion(CellRangeAddress.valueOf("B2:C2"));
        HSSFCell cell23 = row2.createCell(2);
        cell23.setCellValue("By : " + dataheader.getUser());
        cell23.setCellStyle(styleC22);
        //            HSSFCell cell24 = row2.createCell(3);
        //            cell24.setCellValue(dataheader.getUser());
        //            cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("Report of : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        if (!"".equalsIgnoreCase(dataheader.getFrom())) {
            cell32.setCellValue(dataheader.getFrom());
            cell32.setCellStyle(styleC22);
            //            sheet.addMergedRegion(CellRangeAddress.valueOf("B3:C3"));
        }
        //            HSSFCell cell33 = row3.createCell(2);
        //            cell33.setCellValue("To : ");
        //            cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(2);
        if (!"".equalsIgnoreCase(dataheader.getTo())) {
            cell34.setCellValue("To : " + dataheader.getTo());
            cell34.setCellStyle(styleC22);
        }

        // Row 4
        HSSFRow row4 = sheet.createRow(3);
        HSSFCell cell41 = row4.createCell(0);
        cell41.setCellValue("Department : ");
        cell41.setCellStyle(styleC21);
        HSSFCell cell42 = row4.createCell(1);
        cell42.setCellValue(dataheader.getHeaderdepartment());
        cell42.setCellStyle(styleC22);
        //            sheet.addMergedRegion(CellRangeAddress.valueOf("B4:C4"));

        // Header Table
        HSSFCellStyle styleC3 = wb.createCellStyle();
        styleC3.setFont(excelFunction.getHeaderTable(wb.createFont()));
        styleC3.setAlignment(styleC3.ALIGN_CENTER);
        styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFRow row6 = sheet.createRow(5);
        HSSFCell cell61 = row6.createCell(0);
        cell61.setCellValue("No");
        cell61.setCellStyle(styleC3);
        sheet.autoSizeColumn(0);
        HSSFCell cell62 = row6.createCell(1);
        cell62.setCellValue("Receipt No");
        cell62.setCellStyle(styleC3);
        sheet.autoSizeColumn(1);
        HSSFCell cell63 = row6.createCell(2);
        cell63.setCellValue("Inv No");
        sheet.autoSizeColumn(2);
        cell63.setCellStyle(styleC3);
        HSSFCell cell64 = row6.createCell(3);
        cell64.setCellValue("Ar Code");
        cell64.setCellStyle(styleC3);
        sheet.autoSizeColumn(3);
        HSSFCell cell65 = row6.createCell(4);
        cell65.setCellValue("Inv To");
        cell65.setCellStyle(styleC3);
        sheet.autoSizeColumn(4);
        HSSFCell cell66 = row6.createCell(5);
        cell66.setCellValue("Acc Code");
        cell66.setCellStyle(styleC3);
        sheet.autoSizeColumn(5);
        HSSFCell cell67 = row6.createCell(6);
        cell67.setCellValue("Inv Amount");
        cell67.setCellStyle(styleC3);
        sheet.autoSizeColumn(6);
        HSSFCell cell68 = row6.createCell(7);
        cell68.setCellValue("Sum Inv");
        cell68.setCellStyle(styleC3);
        sheet.autoSizeColumn(7);
        HSSFCell cell69 = row6.createCell(8);
        cell69.setCellValue("Diff");
        cell69.setCellStyle(styleC3);
        sheet.autoSizeColumn(8);
        HSSFCell cell70 = row6.createCell(9);
        cell70.setCellValue("Sum Rec");
        cell70.setCellStyle(styleC3);
        sheet.autoSizeColumn(9);
        HSSFCell cell71 = row6.createCell(10);
        cell71.setCellValue("Cur");
        cell71.setCellStyle(styleC3);
        sheet.autoSizeColumn(10);
        HSSFCell cell72 = row6.createCell(11);
        cell72.setCellValue("Withholding Tax");
        cell72.setCellStyle(styleC3);
        sheet.autoSizeColumn(11);
        HSSFCell cell73 = row6.createCell(12);
        cell73.setCellValue("Pay By");
        cell73.setCellStyle(styleC3);
        sheet.autoSizeColumn(12);
        HSSFCell cell74 = row6.createCell(13);
        cell74.setCellValue("Status");
        cell74.setCellStyle(styleC3);
        sheet.autoSizeColumn(13);
    }
    //Detail of Table
    int count = 6;
    int no = 1;
    for (int i = 0; i < collectionNirvanaList.size(); i++) {
        CollectionNirvana data = (CollectionNirvana) collectionNirvanaList.get(i);
        HSSFRow row = sheet.createRow(count + i);

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(String.valueOf(no));
        celldata0.setCellStyle(styleC30);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(String.valueOf(data.getRecno()));
        celldata1.setCellStyle(styleC29);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(String.valueOf(data.getInvno()));
        celldata2.setCellStyle(styleC29);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(String.valueOf(data.getArcode()));
        celldata3.setCellStyle(styleC29);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(String.valueOf(data.getInvto()));
        celldata4.setCellStyle(styleC29);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(String.valueOf(data.getAcccode()));
        celldata5.setCellStyle(styleC29);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue(String.valueOf(data.getInvoiceamount()));
        celldata6.setCellStyle(styleC25);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInvamount())) ? 0
                : (data.getInvamount()).doubleValue());
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata8 = row.createCell(8);
        celldata8.setCellValue(
                "".equalsIgnoreCase(String.valueOf(data.getDiff())) ? 0 : (data.getDiff()).doubleValue());
        celldata8.setCellStyle(styleC25);

        HSSFCell celldata9 = row.createCell(9);
        celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getRecamount())) ? 0
                : (data.getRecamount()).doubleValue());
        celldata9.setCellStyle(styleC25);

        HSSFCell celldata10 = row.createCell(10);
        celldata10.setCellValue(String.valueOf(data.getCur()));
        celldata10.setCellStyle(styleC30);

        HSSFCell celldata11 = row.createCell(11);
        celldata11.setCellValue(
                "".equalsIgnoreCase(String.valueOf(data.getWithtax())) ? 0 : (data.getWithtax()).doubleValue());
        celldata11.setCellStyle(styleC25);

        HSSFCell celldata12 = row.createCell(12);
        celldata12.setCellValue(String.valueOf(data.getPayby()));
        celldata12.setCellStyle(styleC29);

        HSSFCell celldata13 = row.createCell(13);
        celldata13.setCellValue(String.valueOf(data.getCollectionStatus()));
        celldata13.setCellStyle(styleC30);

        if (i == (collectionNirvanaList.size() - 1)) {
            row = sheet.createRow(count + i + 3);
            HSSFCellStyle styleSum = wb.createCellStyle();
            styleSum.setAlignment(styleSum.ALIGN_RIGHT);
            styleSum.setDataFormat(currency.getFormat("#,##0.00"));

            HSSFCell cellTotal00 = row.createCell(1);
            cellTotal00.setCellValue("Cash : ");
            cellTotal00.setCellStyle(styleSum);
            HSSFCell cellTotal01 = row.createCell(2);
            cellTotal01.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(data.getCash())) ? 0 : (data.getCash()).doubleValue());
            cellTotal01.setCellStyle(styleSum);
            HSSFCell cellTotal000 = row.createCell(11);
            cellTotal000.setCellValue("Total Amount : ");
            cellTotal000.setCellStyle(styleSum);
            HSSFCell cellTotal001 = row.createCell(12);
            cellTotal001.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamount())) ? 0
                    : (data.getTotalamount()).doubleValue());
            cellTotal001.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 4);
            HSSFCell cellTotal02 = row.createCell(1);
            cellTotal02.setCellValue("Chq : ");
            cellTotal02.setCellStyle(styleSum);
            HSSFCell cellTotal03 = row.createCell(2);
            cellTotal03.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(data.getChq())) ? 0 : (data.getChq()).doubleValue());
            cellTotal03.setCellStyle(styleSum);
            HSSFCell cellTotal002 = row.createCell(11);
            cellTotal002.setCellValue("Total Amount (Wait) : ");
            cellTotal002.setCellStyle(styleSum);
            HSSFCell cellTotal003 = row.createCell(12);
            cellTotal003.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountwait())) ? 0
                    : (data.getTotalamountwait()).doubleValue());
            cellTotal003.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 5);
            HSSFCell cellTotal04 = row.createCell(1);
            cellTotal04.setCellValue("Credit Card : ");
            cellTotal04.setCellStyle(styleSum);
            HSSFCell cellTotal05 = row.createCell(2);
            cellTotal05.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCredit())) ? 0
                    : (data.getCredit()).doubleValue());
            cellTotal05.setCellStyle(styleSum);
            HSSFCell cellTotal004 = row.createCell(11);
            cellTotal004.setCellValue("Total Amount (Void) : ");
            cellTotal004.setCellStyle(styleSum);
            HSSFCell cellTotal005 = row.createCell(12);
            cellTotal005.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountvoid())) ? 0
                    : (data.getTotalamountvoid()).doubleValue());
            cellTotal005.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 6);
            HSSFCell cellTotal06 = row.createCell(1);
            cellTotal06.setCellValue("Bank Transfer : ");
            cellTotal06.setCellStyle(styleSum);
            HSSFCell cellTotal07 = row.createCell(2);
            cellTotal07.setCellValue("".equalsIgnoreCase(String.valueOf(data.getBanktransfer())) ? 0
                    : (data.getBanktransfer()).doubleValue());
            cellTotal07.setCellStyle(styleSum);
            HSSFCell cellTotal006 = row.createCell(11);
            cellTotal006.setCellValue("Total Amount (Invoice) : ");
            cellTotal006.setCellStyle(styleSum);
            HSSFCell cellTotal007 = row.createCell(12);
            cellTotal007.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountinvoice())) ? 0
                    : (data.getTotalamountinvoice()).doubleValue());
            cellTotal007.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 8);
            HSSFCell cellTotal08 = row.createCell(1);
            cellTotal08.setCellValue("W/T : ");
            cellTotal08.setCellStyle(styleSum);
            HSSFCell cellTotal09 = row.createCell(2);
            cellTotal09.setCellValue(
                    "".equalsIgnoreCase(String.valueOf(data.getWt())) ? 0 : (data.getWt()).doubleValue());
            cellTotal09.setCellStyle(styleSum);
            HSSFCell cellTotal008 = row.createCell(11);
            cellTotal008.setCellValue("Total Amount (Diff) : ");
            cellTotal008.setCellStyle(styleSum);
            HSSFCell cellTotal009 = row.createCell(12);
            cellTotal009.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalamountdiff())) ? 0
                    : (data.getTotalamountdiff()).doubleValue());
            cellTotal009.setCellStyle(styleSum);

            row = sheet.createRow(count + i + 9);
            HSSFCell cellTotal10 = row.createCell(1);
            cellTotal10.setCellValue("Cash (---) : ");
            cellTotal10.setCellStyle(styleSum);
            HSSFCell cellTotal11 = row.createCell(2);
            cellTotal11.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCashminus())) ? 0
                    : (data.getCashminus()).doubleValue());
            cellTotal11.setCellStyle(styleSum);
        }

        for (int j = 0; j < 13; j++) {
            sheet.autoSizeColumn(j);
        }
        no++;
    }
}

From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java

private void getApReport(HSSFWorkbook wb, List ApNirvana) {
    UtilityFunction util = new UtilityFunction();
    SimpleDateFormat df = new SimpleDateFormat();
    df.applyPattern("dd/MM/yyyy");
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);

    APNirvana dataheader = new APNirvana();

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC20 = wb.createCellStyle();
    styleC20.setAlignment(styleC20.ALIGN_CENTER);
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    if (ApNirvana != null) {
        dataheader = (APNirvana) ApNirvana.get(0);
    }//from   w ww.  j  av a2s  .  co m

    // set Header Report (Row 1)
    HSSFCellStyle styleC1 = wb.createCellStyle();
    HSSFRow row1 = sheet.createRow(0);
    HSSFCell cellStart = row1.createCell(0);
    cellStart.setCellValue("Change Ap Report");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cellStart.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    // Row 2
    HSSFRow row2 = sheet.createRow(1);
    HSSFCell cell21 = row2.createCell(0);
    cell21.setCellValue("Print On : ");
    cell21.setCellStyle(styleC21);
    HSSFCell cell22 = row2.createCell(1);
    cell22.setCellValue(dataheader.getSystemdate());
    cell22.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
    HSSFCell cell23 = row2.createCell(4);
    cell23.setCellValue("By : ");
    cell23.setCellStyle(styleC21);
    HSSFCell cell24 = row2.createCell(5);
    cell24.setCellValue(dataheader.getUser());
    cell24.setCellStyle(styleC22);

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(0);
    cell31.setCellValue("Report of : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(1);
    cell32.setCellValue(dataheader.getDatefrom());
    cell32.setCellStyle(styleC22);
    if (!"".equalsIgnoreCase(dataheader.getDateto())) {
        HSSFCell cell33 = row3.createCell(2);
        cell33.setCellValue("To");
        cell33.setCellStyle(styleC20);
        HSSFCell cell34 = row3.createCell(3);
        cell34.setCellValue(dataheader.getDateto());
        cell34.setCellStyle(styleC22);
    }
    //        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));

    // Row 4
    HSSFRow row4 = sheet.createRow(3);
    HSSFCell cell41 = row4.createCell(0);
    cell41.setCellValue("Department : ");
    cell41.setCellStyle(styleC21);
    HSSFCell cell42 = row4.createCell(1);
    cell42.setCellValue(dataheader.getDepartmentheader());
    cell42.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));

    // Row 5
    HSSFRow row5 = sheet.createRow(4);
    HSSFCell cell51 = row5.createCell(0);
    cell51.setCellValue("Page : ");
    cell51.setCellStyle(styleC21);
    HSSFCell cell52 = row5.createCell(5);
    cell52.setCellValue("");
    cell52.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5"));

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN);
    HSSFRow row6 = sheet.createRow(8);
    HSSFCell cell61 = row6.createCell(0);
    cell61.setCellValue("No.");
    cell61.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(0);
    HSSFCell cell62 = row6.createCell(1);
    cell62.setCellValue("Ref InvNo");
    cell62.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(1);
    HSSFCell cell63 = row6.createCell(2);
    cell63.setCellValue("Invoice Date");
    sheet.autoSizeColumn(2);
    cell63.setCellStyle(styleC3Center);
    HSSFCell cell64 = row6.createCell(3);
    cell64.setCellValue("Pay No.");
    cell64.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(3);
    HSSFCell cell65 = row6.createCell(4);
    cell65.setCellValue("Ap Code");
    cell65.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(4);
    HSSFCell cell66 = row6.createCell(5);
    cell66.setCellValue("Invoice Sup");
    cell66.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(5);

    HSSFCellStyle styleC3Right = wb.createCellStyle();
    styleC3Right.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Right.setAlignment(styleC3Right.ALIGN_RIGHT);
    styleC3Right.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3Right.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3Right.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3Right.setBorderTop(HSSFCellStyle.BORDER_THIN);
    HSSFCell cell67 = row6.createCell(6);
    cell67.setCellValue("Acc Code");
    cell67.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(6);
    HSSFCell cell68 = row6.createCell(7);
    cell68.setCellValue("Cur");
    cell68.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(7);
    HSSFCell cell69 = row6.createCell(8);
    cell69.setCellValue("Gross");
    cell69.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(8);
    HSSFCell cell70 = row6.createCell(9);
    cell70.setCellValue("Amount");
    cell70.setCellStyle(styleC3Right);
    sheet.autoSizeColumn(9);

    //Detail of Table
    int count = 9;
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC22.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));
    for (int i = 0; i < ApNirvana.size(); i++) {
        APNirvana data = (APNirvana) ApNirvana.get(i);
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(i + 1);
        cell0.setCellStyle(styleC23);
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(data.getRefinvoiceno());
        cell1.setCellStyle(styleC24);
        HSSFCell cell2 = row.createCell(2);
        cell2.setCellValue(
                data.getTransdate() == null ? "" : util.ConvertString(df.format(data.getTransdate())));
        cell2.setCellStyle(styleC24);
        HSSFCell cell3 = row.createCell(3);
        cell3.setCellValue(data.getPayno());
        cell3.setCellStyle(styleC24);
        HSSFCell cell4 = row.createCell(4);
        cell4.setCellValue(data.getVendorid());
        cell4.setCellStyle(styleC24);
        HSSFCell cell5 = row.createCell(5);
        cell5.setCellValue(data.getVendorname());
        cell5.setCellStyle(styleC24);
        HSSFCell cell6 = row.createCell(6);
        cell6.setCellValue(data.getPuraccount1());
        cell6.setCellStyle(styleC24);
        HSSFCell cell7 = row.createCell(7);
        cell7.setCellValue(data.getCurrencyid());
        cell7.setCellStyle(styleC23);
        HSSFCell cell8 = row.createCell(8);
        cell8.setCellValue((data.getVatamt() != null) ? data.getVatamt().doubleValue()
                : new BigDecimal("0").doubleValue());
        cell8.setCellStyle(styleC25);
        HSSFCell cell9 = row.createCell(9);
        cell9.setCellValue((data.getBasevatamt() != null) ? data.getBasevatamt().doubleValue()
                : new BigDecimal("0").doubleValue());
        cell9.setCellStyle(styleC25);

        if (i == (ApNirvana.size() - 1)) {
            row = sheet.createRow(count + i + 1);
            for (int k = 0; k < 7; k++) {
                HSSFCellStyle styleSum = wb.createCellStyle();
                styleSum.setAlignment(styleC24.ALIGN_RIGHT);
                styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN);
                styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                HSSFCell cellSum = row.createCell(k);
                if (k == 0) {
                    styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                }
                cellSum.setCellStyle(styleSum);
            }
            HSSFCellStyle styleSum = wb.createCellStyle();
            styleSum.setAlignment(styleSum.ALIGN_RIGHT);
            styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            styleSum.setDataFormat(currency.getFormat("#,##0.00"));

            String sumGross = "SUM(I" + 10 + ":I" + (count + i + 1) + ")";
            String sumAmount = "SUM(J" + 10 + ":J" + (count + i + 1) + ")";

            HSSFCell cell5Sum = row.createCell(7);
            cell5Sum.setCellValue("Total");
            cell5Sum.setCellStyle(styleSum);
            HSSFCell cell6Sum = row.createCell(8);
            cell6Sum.setCellFormula(sumGross);
            cell6Sum.setCellStyle(styleSum);
            HSSFCell cell7Sum = row.createCell(9);
            cell7Sum.setCellFormula(sumAmount);
            cell7Sum.setCellStyle(styleSum);

        }
        for (int j = 0; j < 13; j++) {
            sheet.autoSizeColumn(j);
        }
    }
}