Example usage for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat

List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFDataFormat
  6. getFormat

Introduction

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

Prototype

public String getFormat(short index)

Source Link

Document

get the format string that matches the given format index

Usage

From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java

private void getStockInvoiceSummary(HSSFWorkbook wb, List stockInvoiceSummary) {
    String sheetStockInvoiceSummary = "StockInvoiceSummary";// name of sheet

    UtilityExcelFunction excelFunction = new UtilityExcelFunction();

    HSSFSheet sheet = wb.createSheet(sheetStockInvoiceSummary);

    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(styleC23.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.setVerticalAlignment(styleC25.VERTICAL_CENTER);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    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(styleC22.ALIGN_CENTER);

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

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

    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.setVerticalAlignment(styleC29.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.setVerticalAlignment(styleC30.VERTICAL_CENTER);

    HSSFCellStyle styleC31 = wb.createCellStyle();
    styleC31.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC31.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC32 = wb.createCellStyle();
    styleC32.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC32.setAlignment(styleC32.ALIGN_CENTER);
    styleC32.setVerticalAlignment(styleC32.VERTICAL_CENTER);
    styleC32.setWrapText(true);/*from   w  w w . j a v a2 s . co  m*/

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

    StockInvoiceSummaryView dataheader = (StockInvoiceSummaryView) stockInvoiceSummary.get(0);

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

    // Row 2
    HSSFRow row2 = sheet.createRow(1);
    HSSFCell cell21 = row2.createCell(0);
    cell21.setCellValue("Product : ");
    cell21.setCellStyle(styleC21);
    HSSFCell cell22 = row2.createCell(1);
    cell22.setCellValue(dataheader.getProductHeader());
    cell22.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
    HSSFCell cell23 = row2.createCell(4);
    cell23.setCellValue("Inv To : ");
    cell23.setCellStyle(styleC21);
    HSSFCell cell24 = row2.createCell(5);
    cell24.setCellValue(dataheader.getInvtoHeader());
    cell24.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F2:I2"));

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(0);
    cell31.setCellValue("Effective Date : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(1);
    cell32.setCellValue(dataheader.getEffectivedateHeader());
    cell32.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
    HSSFCell cell33 = row3.createCell(4);
    cell33.setCellValue("Invoice Date : ");
    cell33.setCellStyle(styleC21);
    HSSFCell cell34 = row3.createCell(5);
    cell34.setCellValue(dataheader.getInvoicedateHeader());
    cell34.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F3:I3"));

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

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

    HSSFCellStyle styletop = wb.createCellStyle();
    styletop.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styletop.setAlignment(styletop.ALIGN_CENTER);
    styletop.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styletop.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFCellStyle stylebottom = wb.createCellStyle();
    stylebottom.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stylebottom.setAlignment(styletop.ALIGN_CENTER);
    stylebottom.setFont(excelFunction.getHeaderTable(wb.createFont()));
    stylebottom.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFRow row5 = sheet.createRow(5);
    HSSFCell cell51 = row5.createCell(0);
    cell51.setCellValue("Item No");
    cell51.setCellStyle(styletop);
    HSSFCell cell62 = row5.createCell(1);
    cell62.setCellValue("Item type");
    cell62.setCellStyle(styletop);
    HSSFCell cell63 = row5.createCell(2);
    cell63.setCellValue("Ref No");
    cell63.setCellStyle(styletop);
    HSSFCell cell64 = row5.createCell(3);
    cell64.setCellValue("Owner");
    cell64.setCellStyle(styletop);
    HSSFCell cell65 = row5.createCell(4);
    cell65.setCellValue("Inv No");
    cell65.setCellStyle(styletop);
    HSSFCell cell66 = row5.createCell(5);
    cell66.setCellValue("Inv Name");
    cell66.setCellStyle(styletop);
    sheet.autoSizeColumn(5);
    HSSFCell cell67 = row5.createCell(6);
    cell67.setCellValue("Inv Date");
    cell67.setCellStyle(styletop);
    HSSFCell cell68 = row5.createCell(7);
    cell68.setCellValue("Cost");
    cell68.setCellStyle(styletop);
    HSSFCell cell69 = row5.createCell(8);
    cell69.setCellValue("Sale Price");
    cell69.setCellStyle(styletop);
    HSSFCell cell610 = row5.createCell(9);
    cell610.setCellValue("Profit");
    cell610.setCellStyle(styletop);
    HSSFCell cell611 = row5.createCell(10);
    cell611.setCellValue("Stock No");
    cell611.setCellStyle(styletop);

    //Detail of Table
    int count = 6;
    boolean isMerge = false;
    int hMerge = 7;
    int countMerge = 0;

    for (int i = 0; i < stockInvoiceSummary.size(); i++) {
        StockInvoiceSummaryView data = (StockInvoiceSummaryView) stockInvoiceSummary.get(i);
        StockInvoiceSummaryView dataTemp = new StockInvoiceSummaryView();
        if (i != stockInvoiceSummary.size() - 1) {
            dataTemp = (StockInvoiceSummaryView) stockInvoiceSummary.get(i + 1);

        } else {
            dataTemp = null;
        }

        HSSFRow row = sheet.createRow(count + i);
        String id = (data.getId() != null ? data.getId() : "");
        String idTemp = (dataTemp != null ? dataTemp.getId() : "");
        countMerge++;

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(
                !"".equalsIgnoreCase(data.getItemno()) && data.getItemno() != null ? data.getItemno() : "");
        celldata0.setCellStyle(styleC30);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(
                !"".equalsIgnoreCase(data.getItemtype()) && data.getItemtype() != null ? data.getItemtype()
                        : "");
        celldata1.setCellStyle(styleC30);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(
                !"".equalsIgnoreCase(data.getRefno()) && data.getRefno() != null ? data.getRefno() : "");
        celldata2.setCellStyle(styleC30);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(
                !"".equalsIgnoreCase(data.getOwner()) && data.getOwner() != null ? data.getOwner() : "");
        celldata3.setCellStyle(styleC30);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(
                !"".equalsIgnoreCase(data.getInvno()) && data.getInvno() != null ? data.getInvno() : "");
        celldata4.setCellStyle(styleC33);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(
                !"".equalsIgnoreCase(data.getInvname()) && data.getInvname() != null ? data.getInvname() : "");
        celldata5.setCellStyle(styleC29);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue(
                !"".equalsIgnoreCase(data.getInvdate()) && data.getInvdate() != null ? data.getInvdate() : "");
        celldata6.setCellStyle(styleC33);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue(!"".equalsIgnoreCase(data.getCost()) && data.getCost() != null
                ? new BigDecimal(data.getCost()).doubleValue()
                : 0);
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata8 = row.createCell(8);
        celldata8.setCellValue(!"".equalsIgnoreCase(data.getSaleprice()) && data.getSaleprice() != null
                ? new BigDecimal(data.getSaleprice()).doubleValue()
                : 0);
        celldata8.setCellStyle(styleC25);

        HSSFCell celldata9 = row.createCell(9);
        celldata9.setCellValue(!"".equalsIgnoreCase(data.getProfit()) && data.getProfit() != null
                ? new BigDecimal(data.getProfit()).doubleValue()
                : 0);
        celldata9.setCellStyle(styleC25);

        HSSFCell celldata10 = row.createCell(10);
        celldata10.setCellValue(
                !"".equalsIgnoreCase(data.getStockno()) && data.getStockno() != null ? data.getStockno() : "");
        celldata10.setCellStyle(styleC29);

        //            HSSFCell celldata11 = row.createCell(11);
        //            celldata11.setCellValue(!"".equalsIgnoreCase(data.getId()) && data.getId() != null ? data.getId() : "");
        //            celldata11.setCellStyle(styleC29);

        if (!id.equalsIgnoreCase(idTemp) && (!"".equalsIgnoreCase(id) || !"".equalsIgnoreCase(idTemp))) {
            if (countMerge > 1) {
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("H" + (hMerge) + ":H" + (hMerge + (countMerge - 1))));
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("I" + (hMerge) + ":I" + (hMerge + (countMerge - 1))));
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("J" + (hMerge) + ":J" + (hMerge + (countMerge - 1))));
                sheet.addMergedRegion(
                        CellRangeAddress.valueOf("K" + (hMerge) + ":K" + (hMerge + (countMerge - 1))));
            }

            hMerge = 7 + i + 1;
            countMerge = 0;
        }

        //            HSSFCell celldata11 = row.createCell(11);
        //            celldata11.setCellValue(!"".equalsIgnoreCase(data.getId()) && data.getId() != null ? data.getId() : "");
        //            celldata11.setCellStyle(styleC29);

    }

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

    //        sheet.setColumnWidth(0, 256*15);
    //        sheet.setColumnWidth(1, 256*15);
    //        sheet.setColumnWidth(2, 256*15);
    sheet.setColumnWidth(6, 256 * 10);
    sheet.setColumnWidth(7, 256 * 15);
    sheet.setColumnWidth(8, 256 * 15);
    sheet.setColumnWidth(9, 256 * 15);
    sheet.setColumnWidth(10, 256 * 60);
    //        sheet.setColumnWidth(11, 256*15);
    //        sheet.setColumnWidth(12, 256*15);
    //        sheet.setColumnWidth(13, 256*15);
    //        sheet.setColumnWidth(14, 256*15);
    //        sheet.setColumnWidth(15, 256*15);
    //        sheet.setColumnWidth(16, 256*15);
    //        sheet.setColumnWidth(17, 256*15);
    //        sheet.setColumnWidth(18, 256*15);
    //        sheet.setColumnWidth(19, 256*15);

}

From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java

private void getStockNonInvoiceSummary(HSSFWorkbook wb, List stockNonInvoiceSummary) {
    String sheetStockNonInvoiceSummary = "StockNonInvoiceSummary";// name of sheet

    UtilityExcelFunction excelFunction = new UtilityExcelFunction();

    HSSFSheet sheet = wb.createSheet(sheetStockNonInvoiceSummary);

    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(styleC23.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.setVerticalAlignment(styleC25.VERTICAL_CENTER);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    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);

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

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

    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.setVerticalAlignment(styleC29.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.setVerticalAlignment(styleC30.VERTICAL_CENTER);

    HSSFCellStyle styleC31 = wb.createCellStyle();
    styleC31.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC31.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC31.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFCellStyle styleC32 = wb.createCellStyle();
    styleC32.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC32.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC32.setAlignment(styleC32.ALIGN_CENTER);
    styleC32.setVerticalAlignment(styleC32.VERTICAL_CENTER);
    styleC32.setWrapText(true);//from ww w  .  j  ava 2s. co  m

    StockNonInvoiceSummaryView dataheader = (StockNonInvoiceSummaryView) stockNonInvoiceSummary.get(0);

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

    // Row 2
    HSSFRow row2 = sheet.createRow(1);
    HSSFCell cell21 = row2.createCell(0);
    cell21.setCellValue("Product : ");
    cell21.setCellStyle(styleC21);
    HSSFCell cell22 = row2.createCell(1);
    cell22.setCellValue(dataheader.getProductHeader());
    cell22.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
    HSSFCell cell23 = row2.createCell(4);
    cell23.setCellValue("Invoice Sup : ");
    cell23.setCellStyle(styleC21);
    HSSFCell cell24 = row2.createCell(5);
    cell24.setCellValue(dataheader.getInvoicesupHeader());
    cell24.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F2:H2"));

    // Row 3
    HSSFRow row3 = sheet.createRow(2);
    HSSFCell cell31 = row3.createCell(0);
    cell31.setCellValue("Effective Date : ");
    cell31.setCellStyle(styleC21);
    HSSFCell cell32 = row3.createCell(1);
    cell32.setCellValue(dataheader.getEffectivedateHeader());
    cell32.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
    HSSFCell cell33 = row3.createCell(4);
    cell33.setCellValue("Pay Date : ");
    cell33.setCellStyle(styleC21);
    HSSFCell cell34 = row3.createCell(5);
    cell34.setCellValue(dataheader.getPaydateHeader());
    cell34.setCellStyle(styleC22);
    sheet.addMergedRegion(CellRangeAddress.valueOf("F3:H3"));

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

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

    HSSFCellStyle styletop = wb.createCellStyle();
    styletop.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styletop.setAlignment(styletop.ALIGN_CENTER);
    styletop.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styletop.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFCellStyle stylebottom = wb.createCellStyle();
    stylebottom.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stylebottom.setAlignment(styletop.ALIGN_CENTER);
    stylebottom.setFont(excelFunction.getHeaderTable(wb.createFont()));
    stylebottom.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFRow row5 = sheet.createRow(5);
    HSSFCell cell51 = row5.createCell(0);
    cell51.setCellValue("Item No");
    cell51.setCellStyle(styletop);
    HSSFCell cell62 = row5.createCell(1);
    cell62.setCellValue("Item Type");
    cell62.setCellStyle(styletop);
    HSSFCell cell63 = row5.createCell(2);
    cell63.setCellValue("Ref No");
    cell63.setCellStyle(styletop);
    HSSFCell cell64 = row5.createCell(3);
    cell64.setCellValue("Owner");
    cell64.setCellStyle(styletop);
    HSSFCell cell65 = row5.createCell(4);
    cell65.setCellValue("Pay No");
    cell65.setCellStyle(styletop);
    HSSFCell cell66 = row5.createCell(5);
    cell66.setCellValue("Pay Date");
    cell66.setCellStyle(styletop);
    HSSFCell cell67 = row5.createCell(6);
    cell67.setCellValue("Invoice Sup");
    cell67.setCellStyle(styletop);
    HSSFCell cell68 = row5.createCell(7);
    cell68.setCellValue("Cost");
    cell68.setCellStyle(styletop);
    HSSFCell cell69 = row5.createCell(8);
    cell69.setCellValue("Stock No");
    cell69.setCellStyle(styletop);

    //Detail of Table
    int count = 6;
    for (int i = 0; i < stockNonInvoiceSummary.size(); i++) {
        StockNonInvoiceSummaryView data = (StockNonInvoiceSummaryView) stockNonInvoiceSummary.get(i);
        HSSFRow row = sheet.createRow(count + i);

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(
                !"".equalsIgnoreCase(data.getItemno()) && data.getItemno() != null ? data.getItemno() : "");
        celldata0.setCellStyle(styleC29);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(
                !"".equalsIgnoreCase(data.getItemtype()) && data.getItemtype() != null ? data.getItemtype()
                        : "");
        celldata1.setCellStyle(styleC30);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(
                !"".equalsIgnoreCase(data.getRefno()) && data.getRefno() != null ? data.getRefno() : "");
        celldata2.setCellStyle(styleC30);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(
                !"".equalsIgnoreCase(data.getOwner()) && data.getOwner() != null ? data.getOwner() : "");
        celldata3.setCellStyle(styleC29);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(
                !"".equalsIgnoreCase(data.getPayno()) && data.getPayno() != null ? data.getPayno() : "");
        celldata4.setCellStyle(styleC32);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(
                !"".equalsIgnoreCase(data.getPaydate()) && data.getPaydate() != null ? data.getPaydate() : "");
        celldata5.setCellStyle(styleC30);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue(!"".equalsIgnoreCase(data.getInvoicesup()) && data.getInvoicesup() != null
                ? data.getInvoicesup()
                : "");
        celldata6.setCellStyle(styleC29);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue(!"".equalsIgnoreCase(data.getCost()) && data.getCost() != null
                ? new BigDecimal(data.getCost()).doubleValue()
                : 0);
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata8 = row.createCell(8);
        celldata8.setCellValue(
                !"".equalsIgnoreCase(data.getStockno()) && data.getStockno() != null ? data.getStockno() : "");
        celldata8.setCellStyle(styleC29);

        HSSFCell celldata9 = row.createCell(9);
        celldata9
                .setCellValue(!"".equalsIgnoreCase(data.getPaymentstockno()) && data.getPaymentstockno() != null
                        ? data.getPaymentstockno()
                        : "");
        celldata9.setCellStyle(styleC29);

    }

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

    //        sheet.setColumnWidth(0, 256*15);
    //        sheet.setColumnWidth(1, 256*15);
    //        sheet.setColumnWidth(2, 256*15);
    //        sheet.setColumnWidth(3, 256*15);
    //        sheet.setColumnWidth(4, 256*25);
    //        sheet.setColumnWidth(5, 256*25);
    //        sheet.setColumnWidth(6, 256*15);
    //        sheet.setColumnWidth(10, 256*15);
    //        sheet.setColumnWidth(11, 256*15);
    //        sheet.setColumnWidth(12, 256*15);
    //        sheet.setColumnWidth(13, 256*15);
    //        sheet.setColumnWidth(14, 256*15);
    //        sheet.setColumnWidth(15, 256*15);
    //        sheet.setColumnWidth(16, 256*15);
    //        sheet.setColumnWidth(17, 256*15);
    //        sheet.setColumnWidth(18, 256*15);
    //        sheet.setColumnWidth(19, 256*15);

}

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportTravoxReport(List<MainMigrateModel> list) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFSheet sheet = wb.createSheet("TravoxReport");

    HSSFRow row2 = sheet.createRow(0);/*from  w  ww  . j  av a  2 s .c o  m*/
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("GJ");
    cell20.setCellStyle(styleC3Center);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("PAY NO");
    cell21.setCellStyle(styleC3Center);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("NAME");
    cell22.setCellStyle(styleC3Center);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("AP CODE");
    cell23.setCellStyle(styleC3Center);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("REFDOC");
    cell24.setCellStyle(styleC3Center);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("SYSTEM_DATE");
    cell25.setCellStyle(styleC3Center);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("DUE DATE");
    cell26.setCellStyle(styleC3Center);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("INVOICE NUM");
    cell27.setCellStyle(styleC3Center);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("MAIN DESCRIPTION");
    cell28.setCellStyle(styleC3Center);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("CODE");
    cell29.setCellStyle(styleC3Center);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("TYPE PRODUCT");
    cell30.setCellStyle(styleC3Center);
    HSSFCell cell31 = row2.createCell(11);
    cell31.setCellValue("DESCRIPTION");
    cell31.setCellStyle(styleC3Center);
    HSSFCell cell32 = row2.createCell(12);
    cell32.setCellValue("TOTAL AMOUNT");
    cell32.setCellStyle(styleC3Center);
    HSSFCell cell33 = row2.createCell(13);
    cell33.setCellValue("TOTAL VAT");
    cell33.setCellStyle(styleC3Center);
    HSSFCell cell34 = row2.createCell(14);
    cell34.setCellValue("CUR");
    cell34.setCellStyle(styleC3Center);
    HSSFCell cell35 = row2.createCell(15);
    cell35.setCellValue("AMOUNT");
    cell35.setCellStyle(styleC3Center);
    HSSFCell cell36 = row2.createCell(16);
    cell36.setCellValue("DEPARTMENT");
    cell36.setCellStyle(styleC3Center);
    HSSFCell cell37 = row2.createCell(17);
    cell37.setCellValue("ACC NO");
    cell37.setCellStyle(styleC3Center);
    HSSFCell cell38 = row2.createCell(18);
    cell38.setCellValue("EXPENSE DATE");
    cell38.setCellStyle(styleC3Center);
    HSSFCell cell39 = row2.createCell(19);
    cell39.setCellValue("VOUCHER NO");
    cell39.setCellStyle(styleC3Center);
    HSSFCell cell40 = row2.createCell(20);
    cell40.setCellValue("VOUCHER AMOUNT");
    cell40.setCellStyle(styleC3Center);

    if (list != null) {
        int count = 1;
        for (int i = 0; i < list.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) list.get(i);
            HSSFRow row = sheet.createRow(count + i);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getGj());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getPayno());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getName());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(data.getApcode());
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(data.getRefdoc());
            cell3.setCellStyle(styleC24);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getSystemdate());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getDuedate());
            cell5.setCellStyle(styleC24);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getInvoicenum());
            cell6.setCellStyle(styleC24);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getMaindescription());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getCode());
            cell8.setCellStyle(styleC24);
            HSSFCell cell9 = row.createCell(10);
            cell9.setCellValue(data.getTypeproduct());
            cell9.setCellStyle(styleC24);
            HSSFCell cell11 = row.createCell(11);
            cell11.setCellValue(data.getDescription());
            cell11.setCellStyle(styleC24);
            HSSFCell cell12 = row.createCell(12);
            cell12.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalamount()))
                    ? (new BigDecimal(data.getTotalamount())).doubleValue()
                    : 0);
            cell12.setCellStyle(styleC25);
            HSSFCell cell013 = row.createCell(13);
            cell013.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalvat()))
                    ? (new BigDecimal(data.getTotalvat())).doubleValue()
                    : 0);
            cell013.setCellStyle(styleC25);
            HSSFCell cell14 = row.createCell(14);
            cell14.setCellValue(data.getCur());
            cell14.setCellStyle(styleC23);
            HSSFCell cell15 = row.createCell(15);
            cell15.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getAmount()))
                    ? (new BigDecimal(data.getAmount())).doubleValue()
                    : 0);
            cell15.setCellStyle(styleC25);
            HSSFCell cell16 = row.createCell(16);
            cell16.setCellValue(data.getDepartment());
            cell16.setCellStyle(styleC24);
            HSSFCell cell17 = row.createCell(17);
            cell17.setCellValue(data.getAccno());
            cell17.setCellStyle(styleC24);
            HSSFCell cell18 = row.createCell(18);
            cell18.setCellValue(data.getExpensedate());
            cell18.setCellStyle(styleC24);
            HSSFCell cell19 = row.createCell(19);
            cell19.setCellValue(data.getVoucherno());
            cell19.setCellStyle(styleC24);
            HSSFCell cell020 = row.createCell(20);
            cell020.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getVoucheramount()))
                    ? (new BigDecimal(data.getVoucheramount())).doubleValue()
                    : 0);
            cell020.setCellStyle(styleC25);
        }
    }
    for (int x = 0; x < 21; x++) {
        sheet.autoSizeColumn(x);
    }
    sheet.setColumnWidth(2, 256 * 30);
    sheet.setColumnWidth(8, 256 * 30);
    sheet.setColumnWidth(11, 256 * 30);
    exportFileExcel("TravoxReport", wb);
}

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportInvoiceReport(List<MainMigrateModel> listInv) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    HSSFSheet sheet = wb.createSheet("Invoice");

    HSSFRow row2 = sheet.createRow(0);//from w  w w. j a v  a  2  s . c om
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("ID");
    cell20.setCellStyle(styleC3Center);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("INV NO");
    cell21.setCellStyle(styleC3Center);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("NAME");
    cell22.setCellStyle(styleC3Center);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("INV DATE");
    cell23.setCellStyle(styleC3Center);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("GRAND TOTAL");
    cell24.setCellStyle(styleC3Center);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("GRAND TOTAL GROSS");
    cell25.setCellStyle(styleC3Center);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("GRAND TOTAL VAT");
    cell26.setCellStyle(styleC3Center);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("CUR");
    cell27.setCellStyle(styleC3Center);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("DEPARTMENT");
    cell28.setCellStyle(styleC3Center);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("ACC NO");
    cell29.setCellStyle(styleC3Center);

    if (listInv != null) {
        int count = 1;
        for (int i = 0; i < listInv.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) listInv.get(i);
            HSSFRow row = sheet.createRow(count + i);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getId());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getInvno());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getName());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(data.getInvdate());
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            //                cell3.setCellValue(data.getGrandtotal());
            cell3.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotal()))
                    ? (new BigDecimal(data.getGrandtotal())).doubleValue()
                    : 0);
            cell3.setCellStyle(styleC25);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalgross()))
                    ? (new BigDecimal(data.getGrandtotalgross())).doubleValue()
                    : 0);
            cell4.setCellStyle(styleC25);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalvat()))
                    ? (new BigDecimal(data.getGrandtotalvat())).doubleValue()
                    : 0);
            cell5.setCellStyle(styleC25);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getCur());
            cell6.setCellStyle(styleC23);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getDepartment());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getAccno());
            cell8.setCellStyle(styleC24);
        }
    }
    for (int x = 0; x < 10; x++) {
        sheet.autoSizeColumn(x);
    }
    sheet.setColumnWidth(2, 256 * 30);
    exportFileExcel("Invoice", wb);
}

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportAPReport(List<MainMigrateModel> listAP) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    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"));

    String datetemp = "";
    if (listAP != null) {
        HSSFSheet sheet = wb.createSheet(listAP.get(0).getPaydate().substring(3, 10).replaceAll("-", ""));
        int count = 1;
        for (int i = 0; i < listAP.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) listAP.get(i);
            if (!"".equalsIgnoreCase(datetemp)
                    && !datetemp.equalsIgnoreCase(data.getPaydate().substring(3, 10))) {
                sheet = wb.createSheet(data.getPaydate().substring(3, 10).replaceAll("-", ""));
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("PAYID");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("PAY NO");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("AP CODE");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("NAME");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("PAY DATE");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("DEPARTMENT");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("VAT TYPE");
                cell26.setCellStyle(styleC3Center);
                HSSFCell cell27 = row2.createCell(7);
                cell27.setCellValue("TAX NO");
                cell27.setCellStyle(styleC3Center);
                HSSFCell cell28 = row2.createCell(8);
                cell28.setCellValue("BRANCH");
                cell28.setCellStyle(styleC3Center);
                HSSFCell cell29 = row2.createCell(9);
                cell29.setCellValue("BRANCH NO");
                cell29.setCellStyle(styleC3Center);
                count = 1;//from   w w  w  .j a  v  a  2 s .  c o m
                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 15);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 25);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
                sheet.setColumnWidth(7, 256 * 15);
                sheet.setColumnWidth(8, 256 * 15);
                sheet.setColumnWidth(9, 256 * 15);
            } else if ("".equalsIgnoreCase(datetemp)) {
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("PAYID");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("PAY NO");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("AP CODE");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("NAME");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("PAY DATE");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("DEPARTMENT");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("VAT TYPE");
                cell26.setCellStyle(styleC3Center);
                HSSFCell cell27 = row2.createCell(7);
                cell27.setCellValue("TAX NO");
                cell27.setCellStyle(styleC3Center);
                HSSFCell cell28 = row2.createCell(8);
                cell28.setCellValue("BRANCH");
                cell28.setCellStyle(styleC3Center);
                HSSFCell cell29 = row2.createCell(9);
                cell29.setCellValue("BRANCH NO");
                cell29.setCellStyle(styleC3Center);

                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 15);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 25);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
                sheet.setColumnWidth(7, 256 * 15);
                sheet.setColumnWidth(8, 256 * 15);
                sheet.setColumnWidth(9, 256 * 15);

            }

            HSSFRow row = sheet.createRow(count);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getPayid());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getPayno());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getApCode());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(String.valueOf(data.getApname()));
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(String.valueOf(data.getPaydate()));
            cell3.setCellStyle(styleC24);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getDepartment());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getVattype());
            cell5.setCellStyle(styleC24);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getTaxno());
            cell6.setCellStyle(styleC24);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getBranch());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getBranchno());
            cell8.setCellStyle(styleC24);
            datetemp = data.getPaydate().substring(3, 10);
            count++;
        }
    }
    exportFileExcel("APReport", wb);
}

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportARReport(List<MainMigrateModel> listAR) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);
    // Header Table
    HSSFCellStyle styleC3Center = wb.createCellStyle();
    styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER);
    styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    HSSFCellStyle styleC24 = wb.createCellStyle();
    styleC24.setAlignment(styleC24.ALIGN_LEFT);
    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"));

    String datetemp = "";
    if (listAR != null) {
        //            HSSFSheet sheet = wb.createSheet(listAR.get(0).getInvdate().substring(3,10).replaceAll("-", ""));
        HSSFSheet sheet = wb.createSheet("ARReport");
        int count = 1;
        for (int i = 0; i < listAR.size(); i++) {
            MainMigrateModel data = (MainMigrateModel) listAR.get(i);
            if (!"".equalsIgnoreCase(datetemp)
                    && !datetemp.equalsIgnoreCase(data.getInvdate().substring(3, 10))) {
                sheet = wb.createSheet(data.getInvdate().substring(3, 10).replaceAll("-", ""));
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("CODE");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("INV NAME");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("INV NO");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("INV DATE");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("TAX NO");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("BRANCH");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("BRANCH NO");
                cell26.setCellStyle(styleC3Center);

                count = 1;/*  w  w w.ja v a 2s.com*/
                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 25);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 15);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
            } else if ("".equalsIgnoreCase(datetemp)) {
                HSSFRow row2 = sheet.createRow(0);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("CODE");
                cell20.setCellStyle(styleC3Center);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue("INV NAME");
                cell21.setCellStyle(styleC3Center);
                HSSFCell cell22 = row2.createCell(2);
                cell22.setCellValue("INV NO");
                cell22.setCellStyle(styleC3Center);
                HSSFCell cell23 = row2.createCell(3);
                cell23.setCellValue("INV DATE");
                cell23.setCellStyle(styleC3Center);
                HSSFCell cell24 = row2.createCell(4);
                cell24.setCellValue("TAX NO");
                cell24.setCellStyle(styleC3Center);
                HSSFCell cell25 = row2.createCell(5);
                cell25.setCellValue("BRANCH");
                cell25.setCellStyle(styleC3Center);
                HSSFCell cell26 = row2.createCell(6);
                cell26.setCellValue("BRANCH NO");
                cell26.setCellStyle(styleC3Center);

                sheet.setColumnWidth(0, 256 * 15);
                sheet.setColumnWidth(1, 256 * 25);
                sheet.setColumnWidth(2, 256 * 15);
                sheet.setColumnWidth(3, 256 * 15);
                sheet.setColumnWidth(4, 256 * 15);
                sheet.setColumnWidth(5, 256 * 15);
                sheet.setColumnWidth(6, 256 * 15);
            }

            HSSFRow row = sheet.createRow(count);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getCode());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getInvname());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getInvno());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(String.valueOf(data.getInvdate()));
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(data.getTaxno());
            cell3.setCellStyle(styleC24);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getBranch());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getBranchno());
            cell5.setCellStyle(styleC24);

            //                    datetemp = data.getInvdate().substring(3,10);
            count++;
        }
    }
    exportFileExcel("ARReport", wb);
}

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportTaxinvoiceReport(List reptax) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("TaxInvoice");

    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    HSSFRow row1 = sheet.createRow(0);//w  w  w .  ja v  a  2s . c  o  m
    HSSFCell cellStart = row1.createCell(0);
    cellStart.setCellValue("Tax Invoice Report");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cellStart.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    // 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 row2 = sheet.createRow(2);
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("ID");
    cell20.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(0);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("TAX ID");
    cell21.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(1);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("TAX NO");
    cell22.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(2);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("TAX DATE");
    cell23.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(3);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("CODE AP");
    cell24.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(4);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("DESCRIPTION");
    cell25.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(5);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("GROSS AMOUNT");
    cell26.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(6);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("VAT AMOUNT");
    cell27.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(7);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("AMOUNT");
    cell28.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(8);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("FLAG TYPE");
    cell29.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(9);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("INVOICE TYPE");
    cell30.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(10);
    HSSFCell cell31 = row2.createCell(11);
    cell31.setCellValue("TAX NO 1");
    cell31.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(11);
    HSSFCell cell32 = row2.createCell(12);
    cell32.setCellValue("BRANCH");
    cell32.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(12);
    HSSFCell cell33 = row2.createCell(13);
    cell33.setCellValue("BRANCH NO");
    cell33.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(13);

    int count = 3;

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.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 < reptax.size(); i++) {
        ReportTaxInvoice data = (ReportTaxInvoice) reptax.get(i);
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(data.getId());
        cell0.setCellStyle(styleC23);
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(data.getTaxid());
        cell1.setCellStyle(styleC23);
        HSSFCell cell13 = row.createCell(2);
        cell13.setCellValue(data.getTaxno());
        cell13.setCellStyle(styleC23);
        HSSFCell cell2 = row.createCell(3);
        cell2.setCellValue(String.valueOf(data.getTaxdate()));
        cell2.setCellStyle(styleC23);
        HSSFCell cell3 = row.createCell(4);
        cell3.setCellValue(data.getCodeap());
        cell3.setCellStyle(styleC24);
        HSSFCell cell4 = row.createCell(5);
        cell4.setCellValue(data.getDescription().trim());
        cell4.setCellStyle(styleC24);
        HSSFCell cell5 = row.createCell(6);
        cell5.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getGrossamount()))
                ? (data.getGrossamount()).doubleValue()
                : 0);
        cell5.setCellStyle(styleC25);
        HSSFCell cell6 = row.createCell(7);
        cell6.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getVatamount()))
                ? (data.getVatamount()).doubleValue()
                : 0);
        cell6.setCellStyle(styleC25);
        HSSFCell cell7 = row.createCell(8);
        cell7.setCellValue(
                !"null".equalsIgnoreCase(String.valueOf(data.getAmount())) ? (data.getAmount()).doubleValue()
                        : 0);
        cell7.setCellStyle(styleC25);
        HSSFCell cell8 = row.createCell(9);
        cell8.setCellValue(data.getFlagtype());
        cell8.setCellStyle(styleC23);
        HSSFCell cell9 = row.createCell(10);
        cell9.setCellValue(data.getInvoicetype());
        cell9.setCellStyle(styleC24);
        HSSFCell cell10 = row.createCell(11);
        cell10.setCellValue(data.getTaxno1());
        cell10.setCellStyle(styleC24);
        HSSFCell cell11 = row.createCell(12);
        cell11.setCellValue(data.getBranch());
        cell11.setCellStyle(styleC24);
        HSSFCell cell12 = row.createCell(13);
        cell12.setCellValue(data.getBranchno());
        cell12.setCellStyle(styleC23);

    }
    for (int j = 0; j < 15; j++) {
        sheet.autoSizeColumn(j);
    }
    sheet.setColumnWidth(5, 256 * 40);//27
    exportFileExcel("TaxInvoiceReport", wb);
}

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportAgentReport(List repAgent) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Agent");

    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    HSSFRow row1 = sheet.createRow(0);//w ww .  j  a  v  a2  s  . c om
    HSSFCell cellStart = row1.createCell(0);
    cellStart.setCellValue("Agent Report");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cellStart.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    // 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 row2 = sheet.createRow(2);
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("ID");
    cell20.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(0);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("SYSTEM DATE");
    cell21.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(1);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("SYSTEM STAFF");
    cell22.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(2);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("CODE");
    cell23.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(3);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("NAME");
    cell24.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(4);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("ADDRESS");
    cell25.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(5);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("TEL");
    cell26.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(6);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("FAX");
    cell27.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(7);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("DESCRIPTION");
    cell28.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(8);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("NAME T");
    cell29.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(9);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("DESCRIPTION T");
    cell30.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(10);
    HSSFCell cell31 = row2.createCell(11);
    cell31.setCellValue("ADDRESS T");
    cell31.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(11);
    HSSFCell cell32 = row2.createCell(12);
    cell32.setCellValue("EMAIL");
    cell32.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(12);
    HSSFCell cell33 = row2.createCell(13);
    cell33.setCellValue("WEB");
    cell33.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(13);
    HSSFCell cell34 = row2.createCell(14);
    cell34.setCellValue("REMARKS");
    cell34.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(14);
    HSSFCell cell35 = row2.createCell(15);
    cell35.setCellValue("WARNING");
    cell35.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(15);
    HSSFCell cell36 = row2.createCell(16);
    cell36.setCellValue("REF ID");
    cell36.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(16);
    HSSFCell cell37 = row2.createCell(17);
    cell37.setCellValue("BRANCH");
    cell37.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(17);
    HSSFCell cell38 = row2.createCell(18);
    cell38.setCellValue("BRANCH NO");
    cell38.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(18);
    HSSFCell cell39 = row2.createCell(19);
    cell39.setCellValue("TAX NO");
    cell39.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(19);

    int count = 3;

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.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 < repAgent.size(); i++) {
        ReportAgent data = (ReportAgent) repAgent.get(i);
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(data.getId());
        cell0.setCellStyle(styleC23);
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(String.valueOf(data.getSystemdate()));
        cell1.setCellStyle(styleC23);
        HSSFCell cell13 = row.createCell(2);
        cell13.setCellValue(data.getSystemstaff());
        cell13.setCellStyle(styleC24);
        HSSFCell cell2 = row.createCell(3);
        cell2.setCellValue(data.getCode());
        cell2.setCellStyle(styleC24);
        HSSFCell cell3 = row.createCell(4);
        cell3.setCellValue(data.getName());
        cell3.setCellStyle(styleC24);
        HSSFCell cell4 = row.createCell(5);
        cell4.setCellValue(data.getAddress().trim());
        cell4.setCellStyle(styleC24);
        HSSFCell cell5 = row.createCell(6);
        cell5.setCellValue(data.getTel());
        cell5.setCellStyle(styleC24);
        HSSFCell cell6 = row.createCell(7);
        cell6.setCellValue(data.getFax());
        cell6.setCellStyle(styleC24);
        HSSFCell cell7 = row.createCell(8);
        cell7.setCellValue(data.getDescription().trim());
        cell7.setCellStyle(styleC24);
        HSSFCell cell8 = row.createCell(9);
        cell8.setCellValue(data.getNameT());
        cell8.setCellStyle(styleC24);
        HSSFCell cell9 = row.createCell(10);
        cell9.setCellValue(data.getDescriptionT().trim());
        cell9.setCellStyle(styleC24);
        HSSFCell cell10 = row.createCell(11);
        cell10.setCellValue(data.getAddressT().trim());
        cell10.setCellStyle(styleC24);
        HSSFCell cell11 = row.createCell(12);
        cell11.setCellValue(data.getEmail());
        cell11.setCellStyle(styleC24);
        HSSFCell cell12 = row.createCell(13);
        cell12.setCellValue(data.getWeb());
        cell12.setCellStyle(styleC24);
        HSSFCell cell14 = row.createCell(14);
        cell14.setCellValue(data.getRemarks());
        cell14.setCellStyle(styleC24);
        HSSFCell cell15 = row.createCell(15);
        cell15.setCellValue(data.getWarning());
        cell15.setCellStyle(styleC24);
        HSSFCell cell16 = row.createCell(16);
        cell16.setCellValue(data.getRefid());
        cell16.setCellStyle(styleC24);
        HSSFCell cell17 = row.createCell(17);
        cell17.setCellValue(data.getBranch());
        cell17.setCellStyle(styleC24);
        HSSFCell cell18 = row.createCell(18);
        cell18.setCellValue(data.getBranchno());
        cell18.setCellStyle(styleC24);
        HSSFCell cell19 = row.createCell(19);
        cell19.setCellValue(data.getTaxno());
        cell19.setCellStyle(styleC23);
    }

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

    for (int k = 4; k < 21; k++) {
        if (k != 6 && k != 7 && k != 12 && k != 13 && k < 16) {
            sheet.setColumnWidth(k, 256 * 35);//27
        } else {
            sheet.setColumnWidth(k, 256 * 20);//27
        }
    }
    exportFileExcel("AgentReport", wb);
}

From source file:com.smi.travel.migration.MainMigrate.java

public static void ExportStaffReport(List repStaff) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Staff");

    HSSFCellStyle styleC1 = wb.createCellStyle();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    HSSFRow row1 = sheet.createRow(0);//from ww w .ja  v a  2 s. com
    HSSFCell cellStart = row1.createCell(0);
    cellStart.setCellValue("Staff Report");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cellStart.setCellStyle(styleC1);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    // 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 row2 = sheet.createRow(2);
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("ID");
    cell20.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(0);
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("SYSTEM DATE");
    cell21.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(1);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("SYSTEM STAFF");
    cell22.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(2);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("CODE");
    cell23.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(3);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("NAME");
    cell24.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(4);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("PASSWD");
    cell25.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(5);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("POSITION");
    cell26.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(6);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("DEPARTMENT ID");
    cell27.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(7);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("TEL");
    cell28.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(8);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("CAR");
    cell29.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(9);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("STATUS");
    cell30.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(10);
    HSSFCell cell31 = row2.createCell(11);
    cell31.setCellValue("SIGNATURE");
    cell31.setCellStyle(styleC3Center);
    sheet.autoSizeColumn(11);
    int count = 3;

    HSSFDataFormat currency = wb.createDataFormat();
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.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 < repStaff.size(); i++) {
        ReportStaff data = (ReportStaff) repStaff.get(i);
        HSSFRow row = sheet.createRow(count + i);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(data.getId());
        cell0.setCellStyle(styleC23);
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(String.valueOf(data.getSystemdate()));
        cell1.setCellStyle(styleC23);
        HSSFCell cell13 = row.createCell(2);
        cell13.setCellValue(data.getSystemstaff());
        cell13.setCellStyle(styleC24);
        HSSFCell cell2 = row.createCell(3);
        cell2.setCellValue(data.getCode());
        cell2.setCellStyle(styleC24);
        HSSFCell cell3 = row.createCell(4);
        cell3.setCellValue(data.getName());
        cell3.setCellStyle(styleC24);
        HSSFCell cell4 = row.createCell(5);
        cell4.setCellValue(data.getPasswd());
        cell4.setCellStyle(styleC24);
        HSSFCell cell5 = row.createCell(6);
        cell5.setCellValue(data.getPosition());
        cell5.setCellStyle(styleC24);
        HSSFCell cell6 = row.createCell(7);
        cell6.setCellValue(data.getDepartmentid());
        cell6.setCellStyle(styleC24);
        HSSFCell cell7 = row.createCell(8);
        cell7.setCellValue(data.getTel());
        cell7.setCellStyle(styleC24);
        HSSFCell cell8 = row.createCell(9);
        cell8.setCellValue(data.getCar());
        cell8.setCellStyle(styleC24);
        HSSFCell cell9 = row.createCell(10);
        cell9.setCellValue(data.getStatus());
        cell9.setCellStyle(styleC24);
        HSSFCell cell10 = row.createCell(11);
        cell10.setCellValue(data.getSignature());
        cell10.setCellStyle(styleC24);
    }
    for (int j = 0; j < 12; j++) {
        sheet.autoSizeColumn(j);
    }
    //        sheet.setColumnWidth(8, 256*40);//27
    //        sheet.setColumnWidth(10, 256*40);//27
    exportFileExcel("StaffReport", wb);
}

From source file:com.softtek.mdm.web.admin.IndexController.java

private void exportExcel(String sheetName, String[] headNames, List<OrganizationModel> lists,
        HttpServletResponse response) {//w  w  w .  j a  va  2s .c  om

    OutputStream out = null;
    try {
        HSSFWorkbook workbook = new HSSFWorkbook(); //   
        HSSFSheet sheet = workbook.createSheet(sheetName); //   
        //   
        HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTiltle = rowm.createCell(0);
        //sheet??getColumnTopStyle()/getStyle()? - ?  - ?  
        HSSFCellStyle columnTopStyle = CommUtil.getColumnTopStyle(workbook);//??  
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headNames.length - 1)));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(sheetName);
        //   
        int columnNum = headNames.length;
        HSSFRow rowRowName = sheet.createRow(2); // 2?()  
        // sheet?  
        for (int n = 0; n < columnNum; n++) {
            HSSFCell cellRowName = rowRowName.createCell(n); //?  
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //??  
            HSSFRichTextString text = new HSSFRichTextString(headNames[n]);
            cellRowName.setCellValue(text); //?  
            cellRowName.setCellStyle(columnTopStyle); //??  
        }
        //?sheet?  
        HSSFDataFormat format = workbook.createDataFormat();
        short formatDate = format.getFormat("yyyy-MM-dd hh:mm:ss");
        for (int i = 0; i < lists.size(); i++) {
            HSSFRow row = sheet.createRow(i + 3);//  
            OrganizationModel obj = lists.get(i);//???  
            /*row.createCell(0).setCellValue(obj.getOrgType());*/
            row.createCell(0).setCellValue(obj.getName());
            row.createCell(1).setCellValue(obj.getCreateName());
            row.createCell(2).setCellValue(obj.getTotalUsers() == null ? 0 : obj.getTotalUsers());
            row.createCell(3).setCellValue(obj.getTotalDevices() == null ? 0 : obj.getTotalDevices());
            row.createCell(4).setCellValue(obj.getLicenseCount() == null ? 0 : obj.getLicenseCount());
            row.createCell(5).setCellValue(obj.getUseUsers() == null ? 0 : obj.getUseUsers());
            HSSFCell cell = row.createCell(6);
            cell.setCellValue(obj.getCreateTime());
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(formatDate);
            cell.setCellStyle(cellStyle);
        }
        //??  
        for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                //?  
                currentRow = (sheet.getRow(rowNum) == null) ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(colNum, (colNum == 0) ? (columnWidth * 256) : ((columnWidth + 10) * 256));
        }
        if (workbook != null) {
            try {
                String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13)
                        + ".xls";
                String headStr = "attachment; filename=\"" + fileName + "\"";
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition", headStr);
                out = response.getOutputStream();
                workbook.write(out);
            } catch (IOException e) {
                logger.error(e.getMessage());
            } finally {
                if (out != null) {
                    out.close();
                }
            }
        }
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
}