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

Introduction

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

Prototype

@Override
public void shiftRows(int startRow, int endRow, int n) 

Source Link

Document

Shifts rows between startRow and endRow n number of rows.

Usage

From source file:Main3.java

public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum = sheet.getLastRowNum();
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }/*from  w ww. ja v  a  2s  .co m*/
    if (rowIndex == lastRowNum) {
        HSSFRow removingRow = sheet.getRow(rowIndex);
        if (removingRow != null) {
            sheet.removeRow(removingRow);
        }
    }
}

---

From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java

/**
 * post processes the XLS for creating//from   ww w  .  j  a  va2s .  c o  m
 *
 * @param document xls-doc
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    sheet.shiftRows(0, sheet.getLastRowNum(), 2);

    HSSFRow topRow = sheet.createRow(0);

    topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear());
    topRow.createCell(3).setCellValue("von " + selectedUser.getPersName());
    sheet.createRow(1).createCell(0).setCellValue(" ");

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

    HSSFRow header = sheet.getRow(2);
    HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        cell = footer.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

---

From source file:at.htlpinkafeld.beans.UserDetailsBean.java

public void postProcessXLS(Object document) {
        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);

        sheet.shiftRows(0, sheet.getLastRowNum(), 2);

        HSSFRow topRow = sheet.createRow(0);

        topRow.createCell(0)//from  www. j a  v a 2s.  co m
                .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy")));
        topRow.createCell(7).setCellValue("von " + selectedUser);
        sheet.createRow(1).createCell(0).setCellValue(" ");

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        HSSFRow header = sheet.getRow(2);
        HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = header.getCell(i);
            cell.setCellStyle(cellStyle);

            cell = footer.getCell(i);
            cell.setCellStyle(cellStyle);

            sheet.autoSizeColumn(i);
        }

        HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
        bottomRow.createCell(0)
                .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

    }

---

From source file:br.solutio.licita.controlador.ControladorPregao.java

public void editandoXlsParaExportar(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet planilha = wb.getSheetAt(0);

    //Move as celulas selecionadas para baixo de acordo com o valor informado
    planilha.shiftRows(planilha.getFirstRowNum(), planilha.getLastRowNum(), 5);

    HSSFRow linha0 = planilha.createRow(0);
    linha0.createCell(0).setCellValue("Instituio Licitadora:");
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
    linha0.createCell(2)//from w w w. j av  a2  s  .c  o m
            .setCellValue(" " + getEntidade().getInstituicaoLicitadora().getPessoaJuridica().getNomeFantasia());
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));

    HSSFRow linha1 = planilha.createRow(1);
    linha1.createCell(0).setCellValue("Numero do Pregao:");
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
    linha1.createCell(2).setCellValue(" " + getEntidade().getNumeroPregao());
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 2, 6));

    HSSFRow linha2 = planilha.createRow(2);
    linha2.createCell(0).setCellValue("Numero do Processo:");
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
    linha2.createCell(2).setCellValue(" " + getEntidade().getNumeroProcesso());
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 2, 6));

    HSSFRow linha3 = planilha.createRow(3);
    linha3.createCell(0).setCellValue("Empresa Licitante:");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
    linha3.createCell(2).setCellValue("Preencha com o nome de sua Empresa");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 2, 6));

    HSSFRow linha4 = planilha.createRow(4);

    //Nova coluna para a empresas adicionarem seus valores
    HSSFRow linha5 = planilha.getRow(5);
    HSSFCell celula5 = linha5.createCell(5);
    celula5.setCellValue("Valor do Licitante");

    //for para ajustar automaticamente o tamnho das colunas
    for (int i = 0; i < 6; i++) {
        planilha.autoSizeColumn(i);
    }

    //Cor da linha de titulos da tabela
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < linha5.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = linha5.getCell(i);

        cell.setCellStyle(cellStyle);
    }

    CellStyle unlockedCellStyle = wb.createCellStyle();
    unlockedCellStyle.setLocked(false);

    HSSFCell celula2 = linha3.getCell(2);
    celula2.setCellStyle(unlockedCellStyle);

}

---

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

License:Open Source License

private static void writeEffortTotal() {
    HSSFSheet templateSheet = templateWbk.getSheet("Effort Total");
    String lastProjectName = null;
    Date startDateValue = null;/*  w  w  w.jav  a 2s.  c  o  m*/
    Date endDateValue = null;
    double totalDaysValue = 0;
    for (int i = 4; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell projectNameCell = row.getCell((short) 1);
            HSSFCell startDateCell = row.getCell((short) 2);
            HSSFCell endDateCell = row.getCell((short) 3);
            HSSFCell totalDaysCell = row.getCell((short) 4);
            if (totalDaysCell == null) {
                break;
            }
            String newProjectName = projectNameCell.getStringCellValue();
            Date _startDateValue = startDateCell.getDateCellValue();
            Date _endDateValue = endDateCell.getDateCellValue();
            double _totalDaysValue = totalDaysCell.getNumericCellValue();
            if (lastProjectName == null) {
                lastProjectName = newProjectName;
                startDateValue = _startDateValue;
                endDateValue = _endDateValue;
                totalDaysValue = _totalDaysValue;
            } else {
                if (newProjectName.equals(lastProjectName)) {
                    totalDaysValue += _totalDaysValue;
                    templateSheet.getRow(i - 1).getCell((short) 4).setCellValue(totalDaysValue);
                    if (startDateValue.compareTo(_startDateValue) > 0) {
                        startDateValue = _startDateValue;
                        templateSheet.getRow(i - 1).getCell((short) 2).setCellValue(startDateValue);
                    }
                    if (endDateValue.compareTo(_endDateValue) < 0) {
                        endDateValue = _endDateValue;
                        templateSheet.getRow(i - 1).getCell((short) 3).setCellValue(endDateValue);
                    }
                    templateSheet.removeRow(row);
                    templateSheet.shiftRows(i + 1, 109, -1);
                    i--;
                } else {
                    lastProjectName = newProjectName;
                    startDateValue = _startDateValue;
                    endDateValue = _endDateValue;
                    totalDaysValue = _totalDaysValue;
                }
            }
        }
    }
}

---

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);//from  w ww. ja  v a2s  .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);//from ww w.ja va  2s  .com
    }
    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);// w  ww . ja  va2 s .com
    }
    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);//from   ww  w  .  j a  va 2s  .c  o m
    }
    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);//w w w  .ja  v a 2  s  . 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);
}

---

• «
• 1
• 2
• 3
• 4
• »