Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getRowNum.

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ??// w  ww.j a  va  2s  .co m
 * @param cell
 * @param name
 * @param mergedRegionHelper 
 * @return
 */
private Object[] getAllDataColumns(Cell cell, String name, MergedRegionHelper mergedRegionHelper) {
    List<ExcelForEachParams> columns = new ArrayList<ExcelForEachParams>();
    cell.setCellValue("");
    columns.add(getExcelTemplateParams(name.replace(END_STR, EMPTY), cell, mergedRegionHelper));
    int rowspan = 1, colspan = 1;
    if (!name.contains(END_STR)) {
        int index = cell.getColumnIndex();
        //?col 
        int startIndex = cell.getColumnIndex();
        Row row = cell.getRow();
        while (true) {
            int colSpan = columns.get(columns.size() - 1) != null ? columns.get(columns.size() - 1).getColspan()
                    : 1;
            index += colSpan;
            for (int i = 1; i < colSpan; i++) {
                //??,???,,?
                columns.add(null);
                continue;
            }
            cell = row.getCell(index);
            //???
            if (cell == null) {
                //?,
                columns.add(null);
                continue;
            }
            String cellStringString;
            try {//?? ?
                cellStringString = cell.getStringCellValue();
                if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) {
                    throw new ExcelExportException("for each ,?");
                } else if (StringUtils.isBlank(cellStringString) && colspan + startIndex > index) {
                    //?,,?,?,?
                    columns.add(new ExcelForEachParams(null, cell.getCellStyle(), (short) 0));
                    continue;
                }
            } catch (Exception e) {
                throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, e);
            }
            //?cell 
            cell.setCellValue("");
            if (cellStringString.contains(END_STR)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(END_STR, EMPTY), cell,
                        mergedRegionHelper));
                break;
            } else if (cellStringString.contains(WRAP)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
                //??,??
                colspan = index - startIndex + 1;
                index = startIndex - 1;
                row = row.getSheet().getRow(row.getRowNum() + 1);
                rowspan++;
            } else {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
            }
        }
    }
    colspan = 0;
    for (int i = 0; i < columns.size(); i++) {
        colspan += columns.get(i) != null ? columns.get(i).getColspan() : 0;
    }
    colspan = colspan / rowspan;
    return new Object[] { rowspan, colspan, columns };
}

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

/***
 * ?List?/*from  w  ww.j av  a2  s .  c  om*/
 * 
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap,
        String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception {
    Collection collection = (Collection) PoiReflectorUtil.fromCache(object.getClass()).getValue(object,
            param.getName());
    Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
    String picId;
    boolean isUsed = false;// ??
    for (int i = row.getFirstCellNum(); i < param.getExcelParams().size(); i++) {
        Cell cell = row.getCell(i);
        String titleString = (String) titlemap.get(i);
        if (param.getExcelParams().containsKey(titleString)) {
            if (param.getExcelParams().get(titleString).getType() == 2) {
                picId = row.getRowNum() + "_" + i;
                saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
            } else {
                saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
            }
            isUsed = true;
        }
    }
    if (isUsed) {
        collection.add(entity);
    }
}

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,
        Map<String, PictureData> pictures) throws Exception {
    List collection = new ArrayList();
    Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
    List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
    String targetId = null;//from www  .  ja v a 2 s  .  com
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        row = rows.next();
        // ???,?,?
        // keyIndex ??,??
        if (params.getKeyIndex() != null
                && (row.getCell(params.getKeyIndex()) == null
                        || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex()))))
                && object != null) {
            for (ExcelCollectionParams param : excelCollection) {
                addListContinue(object, param, row, titlemap, targetId, pictures, params);
            }
        } else {
            object = PoiPublicUtil.createObject(pojoClass, targetId);
            try {
                for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) {
                    Cell cell = row.getCell(i);
                    String titleString = (String) titlemap.get(i);
                    if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
                        if (excelParams.get(titleString) != null
                                && excelParams.get(titleString).getType() == 2) {
                            picId = row.getRowNum() + "_" + i;
                            saveImage(object, picId, excelParams, titleString, pictures, params);
                        } else {
                            saveFieldValue(params, object, cell, excelParams, titleString, row);
                        }
                    }
                }

                for (ExcelCollectionParams param : excelCollection) {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                }
                if (verifyingDataValidity(object, row, params, pojoClass)) {
                    collection.add(object);
                }
            } catch (ExcelImportException e) {
                if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                    throw new ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();/*w ww . ja v a 2s  .  c  om*/

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

From source file:cn.hanbell.war.control.InvmasImportManagedBean.java

@Override
public void handleFileUploadWhenNew(FileUploadEvent event) {
    super.handleFileUploadWhenNew(event);
    if (this.fileName != null) {
        InvmasImport e;/*  w ww .  j ava  2s .  c  o m*/
        if (addedList != null) {
            addedList.clear();
        }
        try {
            InputStream is = new FileInputStream(getAppResPath() + "/" + fileName);
            Workbook wb = WorkbookFactory.create(is);
            Sheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
            Cell c;
            for (Row r : sheet) {
                if (r.getRowNum() == 0) {
                    continue;
                }
                e = new InvmasImport();
                c = r.getCell(0);
                e.setItcls(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(1);
                e.setItnbr(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(2);
                e.setItdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(3);
                e.setSpdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(4);
                e.setEitdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(5);
                e.setEspdsc(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(6);
                e.setKind(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(7);
                e.setUnmsr1(BaseLib.convertExcelCell(String.class, c).trim());
                c = r.getCell(8);
                e.setUsed(BaseLib.convertExcelCell(String.class, c).trim());
                e.setStatus("N");
                e.setCreator(userManagedBean.getCurrentUser().getUsername());
                e.setCredateToNow();
                addedList.add(e);
            }
            doAdd = true;
            showInfoMsg("Info", "?");
        } catch (IOException | InvalidFormatException ex) {
            showErrorMsg("Info", ",??");
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * @param workbook /*w w  w.  j av a2 s . co m*/
 * @param fieldNames ??
 * @param sheetName ???
 * @return
 */
private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) {
    String[] strKey = fieldNames.split(",");
    List<Map<String, String>> listMap = new ArrayList<>();
    int i = 1;
    try {
        Sheet sheet;
        if (sheetName.length == 0) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName[0]);
        }
        while (true) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            Map<String, String> map = new HashMap<String, String>();
            map.put("rowid", String.valueOf(row.getRowNum()));
            for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) {
                Cell cell;
                cell = row.getCell(keyIndex);
                String cellValue = "";
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // ?cell?Date
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // Date?CellDate
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        // 
                        else {
                            // ??Cell
                            Integer num = new Integer((int) cell.getNumericCellValue());
                            cellValue = String.valueOf(num);
                        }
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getRichStringCellValue().getString();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    default:
                        cellValue = " ";
                    }
                }
                map.put(strKey[keyIndex], cellValue);
            }
            listMap.add(map);
            i++;
        }
    } catch (Exception e) {
        logger.debug("?" + i + "??");
        throw new RuntimeException(e);
    }
    return listMap;
}

From source file:cn.orignzmn.shopkepper.common.utils.excel.ExportExcel.java

License:Open Source License

public <E> ExportExcel setDataList(List<E> list) {
    for (E e : list) {
        int colunm = 0;
        Row row = this.addRow();
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            ExcelField ef = (ExcelField) os[0];
            Object val = null;
            // Get entity value
            try {
                if (StringUtils.isNotBlank(ef.value())) {
                    val = Reflections.invokeGetter(e, ef.value());
                } else {
                    if (os[1] instanceof Field) {
                        val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {},
                                new Object[] {});
                    }//w w  w .ja  v  a 2  s .c  o m
                }
            } catch (Exception ex) {
                // Failure to ignore
                log.info(ex.toString());
                val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
        }
        log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
    }
    return this;
}

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);/*from   ww w  .  ja  va  2 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(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);//from   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 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);//from w w  w . j  av a 2 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 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);
}