Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

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 . ja v  a 2  s. 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.controller.excel.ExportDataToExcelView.java

@Override
protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    response.setHeader("Content-disposition", "attachment; filename=" + "exported.xls");

    Map<String, String> revenueData = (Map<String, String>) model.get("revenueData");
    //create a wordsheet
    HSSFSheet sheet = workbook.createSheet("Revenue Report");

    HSSFRow header = sheet.createRow(0);
    header.createCell(0).setCellValue("Month");
    header.createCell(1).setCellValue("Revenue");

    int rowNum = 1;
    for (Map.Entry<String, String> entry : revenueData.entrySet()) {
        //create the row data
        HSSFRow row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(entry.getKey());
        row.createCell(1).setCellValue(entry.getValue());
    }/*  w w w  .  j a v a 2 s .co m*/
}

From source file:com.smi.travel.datalayer.view.dao.impl.APNirvanaImpl.java

private String genReport(List<APNirvana> apDataList, String fullFileName, List<APNirvana> APList) {
    SimpleDateFormat df = new SimpleDateFormat();
    df.applyPattern("dd/MM/yyyy");
    String status = "";
    UtilityFunction util = new UtilityFunction();
    try {//from  ww w.  j ava  2 s  . co  m
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        int rownum = 0;
        for (APNirvana ap : apDataList) {
            HSSFRow dataRow = sheet.createRow(rownum++);
            int cellnum = 0;
            HSSFCell cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getRefinvoiceno());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getIntreference());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVendorid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVendorname());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getDivisionid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getProjectid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getTranscode());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getTransdate() == null ? ""
                    : util.ConvertString(
                            df.format(util.convertStringToDate(String.valueOf(ap.getTransdate())))));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getDuedate() == null ? ""
                    : util.ConvertString(df.format(util.convertStringToDate(String.valueOf(ap.getDuedate())))));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getCurrencyid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getHomerate()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getForeignrate()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevatamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevathmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getVatamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getVathmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getTransamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getTranshmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVatflag());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVatid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getWhtflag());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getWhtid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhtamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhthmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getWhtamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getWhthmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getYear() == null ? "" : ap.getYear().toString());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPeriod() == null ? "" : ap.getPeriod().toString());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getNote());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt1()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt1()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt2()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt2()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt3()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt3()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount4());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision4());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject4());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt4()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt4()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount5());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision5());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject5());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt5()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt5()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount6());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision6());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject6());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt6()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt6()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount7());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision7());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject7());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt7()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt7()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount8());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision8());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject8());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt8()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt8()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount9());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision9());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject9());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt9()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt9()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount10());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision10());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject10());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt10()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt10()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getService());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getApaccount());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPrefix());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVoucherno());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getTaxid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVendor_branch() == null ? "0" : ap.getVendor_branch().toString());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getCompany_branch());
            //                for(int j =0;j<100;j++){
            //                    sheet.autoSizeColumn(j);
            //                }
        }

        FileOutputStream out = new FileOutputStream(new File(fullFileName + ".xls"));
        workbook.write(out);
        out.close();
        status = "success";
    } catch (Exception e) {
        e.printStackTrace();
        for (APNirvana ap : APList) {
            if (!"".equals(status)) {
                status += ", ";
            }
            status += ap.getPayment_detail_id();
        }
    }
    return status;
}

From source file:com.smi.travel.datalayer.view.dao.impl.ARNirvanaImpl.java

private String genReport(List<ARNirvana> arDataList, String fullFileName, List<ARNirvana> ARList) {
    UtilityFunction util = new UtilityFunction();
    String status = "";
    SimpleDateFormat df = new SimpleDateFormat();
    df.applyPattern("dd/MM/yyyy");
    try {//from w  ww  . ja  v a 2 s.  c  o m
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        int rownum = 0;
        for (ARNirvana ar : arDataList) {
            HSSFRow dataRow = sheet.createRow(rownum++);
            int cellnum = 0;
            HSSFCell cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getIntreference());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesmanid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getCustomerid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getCustomername());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getDivisionid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getProjectid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getTranscode());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getTransdate() == null ? ""
                    : util.ConvertString(
                            df.format(util.convertStringToDate(String.valueOf(ar.getTransdate())))));
            cell = dataRow.createCell(cellnum++);
            if (ar.getDuedate() == null) {
                cell.setCellValue("");
            } else {
                cell.setCellValue(util
                        .ConvertString(df.format(util.convertStringToDate(String.valueOf(ar.getDuedate())))));
            }
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getCurrencyid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getHomerate()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getForeignrate()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getVatamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getVathmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getAramt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getArhmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getVatflag());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getVatid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getWhtflag());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getWhtid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getBasewhtamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getBasewhthmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getWhtamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getWhthmamt()));
            cell = dataRow.createCell(cellnum++);
            if (ar.getYear() == null) {
                cell.setCellValue("");
            } else {
                cell.setCellValue(ar.getYear());
            }
            cell = dataRow.createCell(cellnum++);
            if (ar.getPeriod() == null) {
                cell.setCellValue("");
            } else {
                cell.setCellValue(ar.getPeriod());
            }

            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getNote());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesaccount1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesdivision1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesproject1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt1()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt1()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesaccount2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesdivision2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesproject2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt2()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt2()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesaccount3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesdivision3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getSalesproject3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt3()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt3()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getService());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getAraccount());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getPrefix());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getDocumentno());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getArtrans());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getCust_taxid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getCust_branch() == null ? "" : ar.getCust_branch().toString());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ar.getCompany_branch());
            //                for(int j =0;j<100;j++){
            //                    sheet.autoSizeColumn(j);
            //                }
        }
        FileOutputStream out = new FileOutputStream(new File(fullFileName + ".xls"));
        workbook.write(out);
        out.close();
        status = "success";
    } catch (Exception e) {
        e.printStackTrace();
        for (ARNirvana ar : ARList) {
            if (!"".equals(status)) {
                status += ", ";
            }
            status += ar.getReceive_detail_id();
        }
    }
    return status;
}

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);
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("GJ");
    cell20.setCellStyle(styleC3Center);// ww  w.  ja v  a2  s .c  om
    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);
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("ID");
    cell20.setCellStyle(styleC3Center);/*w  w  w .  j a  va  2s  .c o m*/
    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 ExportDeptorInvoiceReport(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("DeptorInvoice");

    HSSFRow row2 = sheet.createRow(0);
    HSSFCell cell20 = row2.createCell(0);
    cell20.setCellValue("INV NO");
    cell20.setCellStyle(styleC3Center);/*from   w w w .ja v a  2  s . c om*/
    HSSFCell cell21 = row2.createCell(1);
    cell21.setCellValue("DATE");
    cell21.setCellStyle(styleC3Center);
    HSSFCell cell22 = row2.createCell(2);
    cell22.setCellValue("NAME");
    cell22.setCellStyle(styleC3Center);
    HSSFCell cell23 = row2.createCell(3);
    cell23.setCellValue("DETAIL");
    cell23.setCellStyle(styleC3Center);
    HSSFCell cell24 = row2.createCell(4);
    cell24.setCellValue("INV AMOUNT");
    cell24.setCellStyle(styleC3Center);
    HSSFCell cell25 = row2.createCell(5);
    cell25.setCellValue("RECEIVE NO");
    cell25.setCellStyle(styleC3Center);
    HSSFCell cell26 = row2.createCell(6);
    cell26.setCellValue("RECEIVE AMOUNT");
    cell26.setCellStyle(styleC3Center);
    HSSFCell cell27 = row2.createCell(7);
    cell27.setCellValue("REMAIN AMOUNT");
    cell27.setCellStyle(styleC3Center);
    HSSFCell cell28 = row2.createCell(8);
    cell28.setCellValue("CODE");
    cell28.setCellStyle(styleC3Center);
    HSSFCell cell29 = row2.createCell(9);
    cell29.setCellValue("DEPARTMENT");
    cell29.setCellStyle(styleC3Center);
    HSSFCell cell30 = row2.createCell(10);
    cell30.setCellValue("GRAND TOTAL VATAMT");
    cell30.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.getInvoiceno());
            cell0.setCellStyle(styleC24);
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getInvoicedate());
            cell1.setCellStyle(styleC24);
            HSSFCell cell13 = row.createCell(2);
            cell13.setCellValue(data.getInvoicename());
            cell13.setCellStyle(styleC24);
            HSSFCell cell2 = row.createCell(3);
            cell2.setCellValue(data.getInvoicedetail());
            cell2.setCellStyle(styleC24);
            HSSFCell cell3 = row.createCell(4);
            cell3.setCellValue(data.getInvoiceamount());
            cell3.setCellStyle(styleC25);
            HSSFCell cell4 = row.createCell(5);
            cell4.setCellValue(data.getReceiveno());
            cell4.setCellStyle(styleC24);
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue(data.getReceiveamount());
            cell5.setCellStyle(styleC25);
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue(data.getRemainamount());
            cell6.setCellStyle(styleC25);
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue(data.getCode());
            cell7.setCellStyle(styleC24);
            HSSFCell cell8 = row.createCell(9);
            cell8.setCellValue(data.getDepartment());
            cell8.setCellStyle(styleC24);
            HSSFCell cell9 = row.createCell(10);
            cell9.setCellValue(data.getGrandtotal());
            cell9.setCellStyle(styleC25);
        }
    }
    for (int x = 0; x < 11; x++) {
        sheet.autoSizeColumn(x);
    }
    sheet.setColumnWidth(2, 256 * 30);
    exportFileExcel("DeptorInvoice", 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;//ww  w  . ja  v  a  2 s .c  om
                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;//from www.  j a  v  a 2  s  .c  o  m
                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);
    HSSFCell cellStart = row1.createCell(0);
    cellStart.setCellValue("Tax Invoice Report");
    styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
    cellStart.setCellStyle(styleC1);//from   w  w w.j  a  v a 2s.  c o m
    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);
}