Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

From source file:com.accounting.inventory.InventorySalesMBean.java

public void postProcessXLSSalesBetDate(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);/* w  w w  .j  a v a2  s.c  om*/
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES INVOICE REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            for (int i = 4; i < 7; i++) {
                String cost1 = row.getCell(i).getStringCellValue();
                if (!cost1.isEmpty()) {
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    row.getCell(i).setCellValue(Double.valueOf(cost1));

                }
            }
        }
    }
}

From source file:com.accounting.inventory.InventorySalesMBean.java

public void postProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);//  ww w  .ja  v a 2 s  .c  o  m
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);
    if (!displayAll) {
        if (stage == 1) {
            HSSFRow fourthRow = sheet.createRow(2);
            fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
            fourthRow.getCell(0).setCellStyle(headerCellStyle);
        }
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));

    HSSFRow lastRow;
    double totalSold = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            String cost = row.getCell(3).getStringCellValue();
            if (cost != null && !cost.isEmpty()) {
                row.getCell(3).setCellType(HSSFCell.CELL_TYPE_BLANK);
                row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                row.getCell(3).setCellValue(Double.valueOf(cost));
                totalSold += Double.parseDouble(cost.replace(",", ""));
            }
        }
    }
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            for (int i = 4; i < 5; i++) {
                String cost1 = row.getCell(i).getStringCellValue();
                row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                row.getCell(i).setCellValue(Double.valueOf(cost1));
                //                    totalSold += Double.valueOf(row.getCell(3).getStringCellValue());

            }
        }
    }

    lastRow = sheet.createRow(sheet.getLastRowNum() + 1);
    HSSFCell totalSumTextCell = lastRow.createCell(2);
    totalSumTextCell.setCellValue("Total Sales Amount: ");

    HSSFCell totalUnitsCell = lastRow.createCell(3);
    totalUnitsCell.setCellValue(totalSold);
}

From source file:com.accounting.inventory.InventorySalesMBean.java

public void postProcessXLSSalesReturn(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);/*from  ww w .  j a v a  2  s  .com*/
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES RETURN REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    if (stage == 1) {
        HSSFRow fourthRow = sheet.createRow(2);
        fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
        fourthRow.getCell(0).setCellStyle(headerCellStyle);
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4));

    HSSFRow lastRow;
    double totalSold = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {

            String cost = row.getCell(4).getStringCellValue();
            row.getCell(4).setCellType(HSSFCell.CELL_TYPE_BLANK);
            row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            row.getCell(4).setCellValue(Double.valueOf(cost));
            totalSold += Double.valueOf(cost);

        }
    }
    lastRow = sheet.createRow(sheet.getLastRowNum() + 1);
    HSSFCell totalSumTextCell = lastRow.createCell(3);
    totalSumTextCell.setCellValue("Total : ");

    HSSFCell totalUnitsCell = lastRow.createCell(4);
    totalUnitsCell.setCellValue(totalSold);
}

From source file:com.accounting.inventory.InventorySalesMBean.java

public void postProcessXLSItemIssue(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);//from   w  w w  .jav a 2  s  .c o m
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("Item Issue REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    if (stage == 1) {
        HSSFRow fourthRow = sheet.createRow(2);
        fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
        fourthRow.getCell(0).setCellStyle(headerCellStyle);
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4));
}

From source file:com.accounting.inventory.InventorySalesMBean.java

public void simplePostProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);//from  w w w  .  j a  v  a  2s.co m
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 5);

    HSSFRow firstRow = sheet.createRow(0);
    firstRow.createCell(0).setCellValue("SALES ANALYSIS REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(1);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(2);
    String date = ndc.convertToNepaliDate(new Date());
    thirdRow.createCell(0).setCellValue("Generated on:" + date + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow fourthRow = sheet.createRow(3);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow fifthRow = sheet.createRow(4);
    fifthRow.createCell(0).setCellValue("Customer name ");
    fifthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 7));

}

From source file:com.accounting.inventory.InventoryStockMBean.java

public void postProcessXLS(Object document) {

    ndc = new NepaliDateConverter();
    String start = ndc.convertToNepaliDate(new Date());
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);//w  w  w.  j a  v a2 s . c  o  m
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(0);
    firstRow.createCell(0).setCellValue("Inventory Stock Report");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(1);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(2);
    String date = ndc.convertToNepaliDate(new Date());
    thirdRow.createCell(0).setCellValue("As of date: " + start);
    thirdRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow fourthRow = sheet.createRow(3);
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    fourthRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    fourthRow.getCell(0).setCellStyle(headerCellStyle2);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));

    HSSFRow lastRow;
}

From source file:com.accounting.office.EmployeeController.java

public void simplePostProcessXLSEmployeeReport(Object document) {
    System.out.println("page head name is" + employeeStatus);
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);//from w  w  w .  ja v  a  2s  .co  m
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 3);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(employeeStatus);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(2);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 9));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 9));
}

From source file:com.accounting.office.PayRollManagedBean.java

public void simplePostProcessXLSEmployeeReport(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);/*from  ww  w  . j  av  a  2  s  . co  m*/
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 3);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("Salary Posting Report");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(2);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 5));
    for (Row row : sheet) {
        if (row.getRowNum() > 3) {
            for (int i = 2; i < 6; i++) {
                String cost = row.getCell(i).getStringCellValue();
                if (cost != null && !cost.isEmpty()) {
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", "")));
                }
            }
        }
    }
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void postProcessXLSGeneral(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    //        headerFont.setFontHeightInPoints((short) 20);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    //         Font headerFont1 = wb.createFont();
    //        headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    //        headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    //        headerCellStyle1.setFont(headerFont);
    //        headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);//from  www. j  av a  2  s. c o  m
    sheet.shiftRows(0, sheet.getLastRowNum(), 6);
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    sheet.autoSizeColumn(2);
    sheet.autoSizeColumn(3);
    sheet.autoSizeColumn(4);
    sheet.autoSizeColumn(5);
    sheet.autoSizeColumn(6);

    HSSFRow firstRow = sheet.createRow(0);
    firstRow.createCell(0).setCellValue(HelperUtil.getLoggedInOffice().getName());
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(1);
    secondRow.createCell(0).setCellValue(HelperUtil.getLoggedInOffice().getAddress());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(2);
    thirdRow.createCell(0).setCellValue(HelperUtil.getPageName().getPageName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow forthRow = sheet.createRow(4);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    forthRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    forthRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fifthRow = sheet.createRow(3);
    fifthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fifthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 12));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSSalesSummary(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate7");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);/*from   ww w.  ja va 2s . c  om*/
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("Sales Report Summary Wise");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}