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

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

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

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:cn.fql.template.poi.PoiTest.java

License:Open Source License

public static void writeEffort(int index, String sheetName) {
    HSSFSheet templateSheet = templateWbk.getSheet(sheetName);
    String lastCellValue = null;/*w  w  w  .j a va  2s.  c  o m*/
    for (int i = 1118; i < 1232; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell cell = row.getCell((short) index);
            if (lastCellValue == null) {
                lastCellValue = cell.getRichStringCellValue().getString();
            } else {
                String newCellValue = cell.getRichStringCellValue().getString();
                if (cell != null) {
                    if (lastCellValue.equals(newCellValue)) {
                        Region region = new Region();
                        region.setRowTo(i);
                        region.setRowFrom(i - 1);
                        region.setColumnFrom((short) index);
                        region.setColumnTo((short) index);
                        templateSheet.addMergedRegion(region);
                    } else {
                        lastCellValue = newCellValue;
                    }
                }
            }
        }
    }
}

From source file:cn.vlabs.umt.common.xls.UserXLSParser.java

License:Apache License

private UserVO parseLine(HSSFSheet sheet, int row) {
    // ??Sheet/*from w w w.  j a v  a  2 s. c o  m*/
    HSSFRow rowline = sheet.getRow(row);

    // ??
    UserVO u = new UserVO();
    u.setUmtId(readCellValue(rowline.getCell((short) 0)));
    u.setTrueName(readCellValue(rowline.getCell((short) 1)));
    u.setPassword(readCellValue(rowline.getCell((short) 2)));
    u.setCstnetId(readCellValue(rowline.getCell((short) 3)));
    if (StringUtils.isEmpty(u.getCstnetId())) {
        u.setCstnetId(u.getUmtId());
    }
    return u;
}

From source file:com.accenture.control.GerenciaPlanilhaTS.java

public void geraNovaPlanilhaTS(String dir, List<TesteCaseTSBean> listTS)
        throws FileNotFoundException, IOException {

    FileInputStream arquivo = new FileInputStream(new File(dir));
    HSSFWorkbook workbook = new HSSFWorkbook(arquivo);
    HSSFSheet sheetTS = workbook.getSheetAt(0);

    HSSFDataFormat format = workbook.createDataFormat();
    HSSFCellStyle estilo = workbook.createCellStyle();
    String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);/*  ww  w . j a  va 2  s. com*/
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);

    for (int i = 0; i < listTS.size(); i++) {

        estilo.setDataFormat(format.getFormat(formatData));
        estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);

        descriptionPlan.setCellValue(listTS.get(i).getTestPlan());
        prj.setCellValue(listTS.get(i).getSTIPRJ());
        fase.setCellValue(listTS.get(i).getFASE());
        testPhase.setCellValue(listTS.get(i).getTestPhase());
        testScriptName.setCellValue(listTS.get(i).getTestScriptName());
        testScriptDescription.setCellValue(listTS.get(i).getTestScriptDescription());
        stepNo.setCellValue(listTS.get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(listTS.get(i).getStepDescription());
        expectedResults.setCellValue(listTS.get(i).getExpectedResults());
        product.setCellValue(listTS.get(i).getProduct());
        dataPlanejada.setCellValue(listTS.get(i).getDataPlanejada());

        dataPlanejada.setCellStyle(estilo);

        linha = linha + 2;

    }

    FileOutputStream fileOut = new FileOutputStream(new File(dir));
    workbook.write(fileOut);
    fileOut.close();
    arquivo.close();

}

From source file:com.accounting.accountMBean.DifferentAccReports.java

public void postProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");

    ndc = new NepaliDateConverter();
    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);
    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);/*w ww.j a v  a  2 s .c o  m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(PageName);
    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 generatedDate = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("To Date: " + startDateString);
    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 totalDr = 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.parseDouble(cost.replace(",", "")));
    //                    totalDr += Double.parseDouble(cost.replace(",", ""));
    //                }
    //            }
    //        }

}

From source file:com.accounting.accountMBean.DifferentAccReports.java

public void postProcessXLSAgeingReport(Object document) {
    ndc = new NepaliDateConverter();
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateReading = 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);
    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);// w  ww  .j a  v a 2 s. c  o m
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(PageName);
    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 generatedDate = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("From: " + startDateString + " To: " + endDateReading);
    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) {
            String cost = row.getCell(4).getStringCellValue();
            if (cost != null && !cost.isEmpty()) {
                row.getCell(4).setCellType(HSSFCell.CELL_TYPE_BLANK);
                row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                row.getCell(4).setCellValue(Double.parseDouble(cost.replace(",", "")));
            }
        }
    }
}

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);
    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);/*from ww  w.  j av  a2  s .c o m*/
    }
    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);
    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);/*w  w  w. ja v a2  s  .  c om*/
    }
    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);
    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);/*from w ww  .  j av  a2s  .co m*/
    }
    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);
    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);/*w  w  w . jav  a 2  s.  c  om*/
    }
    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);
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/* w  w  w  . j  a v a 2s.  c  o m*/
    }
    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));

}