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:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Method writes vertical band// w  w w. j  a  v a2s .c o m
 * Note: no child support for vertical band ;)
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeVerticalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    CellReference[] crefs = getRangeContent(templateWorkbook, rangeName);

    Set<Integer> addedRowNumbers = new HashSet<Integer>();

    if (crefs != null) {
        addRangeBounds(band, crefs);

        Bounds thisBounds = templateBounds.get(band.getName());
        Bounds parentBounds = templateBounds.get(band.getParentBand().getName());
        Range parentRange = bandsToResultRanges.get(band.getParentBand());

        int localRowNum = parentBounds != null && parentRange != null
                ? parentRange.getFirstRow() - 1 + thisBounds.row0 - parentBounds.row0
                : rownum;

        colnum = colnum == 0 ? getCellFromReference(crefs[0], templateSheet).getColumnIndex() : colnum;
        copyMergeRegions(resultSheet, rangeName, localRowNum, colnum);

        int firstRow = crefs[0].getRow();
        int firstColumn = crefs[0].getCol();

        for (CellReference cref : crefs) {//create necessary rows
            int currentRow = cref.getRow();
            final int rowOffset = currentRow - firstRow;
            if (!rowExists(resultSheet, localRowNum + rowOffset)) {
                HSSFRow resultRow = resultSheet.createRow(localRowNum + rowOffset);
                copyPageBreaks(templateSheet, resultSheet, cref.getRow(), resultRow.getRowNum());
            }
            addedRowNumbers.add(cref.getRow());
        }

        CellReference topLeft = null;
        CellReference bottomRight = null;
        for (CellReference cref : crefs) {
            int currentRow = cref.getRow();
            int currentColumn = cref.getCol();
            final int rowOffset = currentRow - firstRow;
            final int columnOffset = currentColumn - firstColumn;

            HSSFCell templateCell = getCellFromReference(cref, templateSheet);
            resultSheet.setColumnWidth(colnum + columnOffset,
                    templateSheet.getColumnWidth(templateCell.getColumnIndex()));
            HSSFCell resultCell = copyCellFromTemplate(templateCell,
                    resultSheet.getRow(localRowNum + rowOffset), colnum + columnOffset, band);
            if (topLeft == null) {
                topLeft = new CellReference(resultCell);
            }
            bottomRight = new CellReference(resultCell);
        }

        colnum += crefs[crefs.length - 1].getCol() - firstColumn + 1;

        AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
        AreaReference resultRange = new AreaReference(topLeft, bottomRight);
        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.VERTICAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.VERTICAL, resultRange));
        bandsToResultRanges.put(band,
                new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1,
                        resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1,
                        resultRange.getLastCell().getRow() + 1));
    }

    //for first level vertical bands we should increase rownum by number of rows added by vertical band
    //nested vertical bands do not add rows, they use parent space
    if (BandData.ROOT_BAND_NAME.equals(band.getParentBand().getName())) {
        List<BandData> sameBands = band.getParentBand().getChildrenByName(band.getName());
        if (sameBands.size() > 0 && sameBands.get(sameBands.size() - 1) == band) {//check if this vertical band is last vertical band with same name
            rownum += addedRowNumbers.size();
            //      rowsAddedByVerticalBand = 0;
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

protected boolean rowExists(HSSFSheet sheet, int rowNumber) {
    return sheet.getRow(rowNumber) != null;
}

From source file:com.hp.action.CustomerAction.java

public String addCustomerFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*  w  w  w  .j  av  a  2s  .c o m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    int total = 0;
    int totalFail = 0;
    String reason = "";
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the customer id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Customer Object
                Customer customer = new Customer();
                //                    customer.setmStt((int)row.getCell(tmp++).getNumericCellValue());

                //System.out.println(row.getCell(1).getStringCellValue());

                if (row.getCell(1) != null && !row.getCell(1).getStringCellValue().equals("")) {
                    Customer customer2 = customerDAO.loadCustomer(row.getCell(1).getStringCellValue());
                    if (customer2 != null) {
                        reason += "Khch hng <b>" + row.getCell(1).getStringCellValue()
                                + "</b>  tn ti. ";
                    }
                } else
                    reason += "M Khch hng hng " + (i + 1) + " khng c trng. ";

                if (row.getCell(9) != null && !row.getCell(9).getStringCellValue().equals("")) {
                    Staff staff = staffDAO.loadStaff(row.getCell(9).getStringCellValue());
                    if (staff == null)
                        reason += "M Nhn vin <b>" + row.getCell(9).getStringCellValue()
                                + "</b> khng tn ti. ";
                } else
                    reason += "M Nhn vin hng " + (i + 1) + " khng c trng.";

                try {
                    if (row.getCell(1) != null)
                        customer.setMaDoiTuong(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        customer.setDoiTuong(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        customer.setTinhThanh(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        customer.setX(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        customer.setDiaChi(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        customer.setTuyenBanHangThu(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null)
                        customer.setDienThoai(row.getCell(7).getStringCellValue());
                    //System.out.println(row.getCell(7).getStringCellValue());

                    if (row.getCell(8) != null)
                        customer.setFax(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null)
                        customer.setMaNhanVien(row.getCell(9).getStringCellValue());

                    if (row.getCell(10) != null)
                        customer.setGhiChu(row.getCell(10).getStringCellValue());

                    if (row.getCell(11) != null) {
                        if (row.getCell(11).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateX(row.getCell(11).getNumericCellValue());
                        else
                            customer.setCoordinateX(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    if (row.getCell(12) != null) {
                        if (row.getCell(12).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateY(row.getCell(12).getNumericCellValue());
                        else
                            customer.setCoordinateY(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    //Add to database
                    if (customerDAO.saveOrUpdate(customer)) {
                        System.out.println("Add Object " + i);
                        total++;
                        customersTotal = total;
                    } else {
                        totalFail++;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    totalFail++;
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    reasonFail = reason;
    customersTotal = total;
    importFail = totalFail;

    return SUCCESS;
}

From source file:com.hp.action.ProductsAction.java

public String addProductFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//  w  w w  .ja v a2s  . c o  m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-product");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Product product = new Product();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setProductID(
                                    (new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            product.setProductID(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setBarcode(
                                    (new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            product.setBarcode(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        product.setProductName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        product.setBrand(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        product.setOrigin(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setPackingSpecifications(row.getCell(6).getNumericCellValue() + "");
                        else
                            product.setPackingSpecifications(row.getCell(6).getStringCellValue());

                    }
                    if (row.getCell(7) != null) {

                        product.setQuantification(row.getCell(7).getStringCellValue());

                    }

                    if (row.getCell(8) != null) {
                        if (row.getCell(8).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setVatTax((float) row.getCell(8).getNumericCellValue());
                        else
                            product.setVatTax(Float.parseFloat(row.getCell(8).getStringCellValue()));

                    }

                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setImportPrices((float) row.getCell(9).getNumericCellValue());
                        else
                            product.setImportPrices(Float.parseFloat(row.getCell(9).getStringCellValue()));

                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setExportPrices((float) row.getCell(10).getNumericCellValue());
                        else
                            product.setExportPrices(Float.parseFloat(row.getCell(10).getStringCellValue()));

                    }
                    if (row.getCell(11) != null)
                        product.setProvider(row.getCell(11).getStringCellValue());

                    if (row.getCell(12) != null)
                        product.setDescription(row.getCell(12).getStringCellValue());
                    if (row.getCell(13) != null)
                        product.setProductImage(row.getCell(13).getStringCellValue());

                    //product.setMProvider("nhacungcap1");
                    //Add to database
                    if (productDAO.saveOrUpdate(product)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        productsTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    productsTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.ProviderAction.java

public String addProviderFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*w w w.  j  av a2s  . c  o m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-provider");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Provider provider = new Provider();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setId((new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            provider.setId(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setName((new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            provider.setName(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        provider.setAddress(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        provider.setPhoneNumber(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        provider.setFax(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setNote(row.getCell(6).getNumericCellValue() + "");
                        else
                            provider.setNote(row.getCell(6).getStringCellValue());

                    }

                    //Add to database
                    if (providerDAO.saveOrUpdate(provider)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        providersTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    providersTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.StaffsAction.java

public String addStaffFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*from www  .  j  a va  2s .co  m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-staff");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the staff id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Staff Object                   
                Staff staff = new Staff();
                try {

                    if (row.getCell(1) != null)
                        staff.setId(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        staff.setPw(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        staff.setName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        staff.setAdress(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        staff.setJob(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        staff.setPhone(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null && row.getCell(7).getStringCellValue().compareTo("") != 0) {

                        staff.setDate(df.parse(row.getCell(7).getStringCellValue()));

                    }
                    if (row.getCell(8) != null)
                        staff.setManager(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setStatus(row.getCell(9).getNumericCellValue() == 1 ? true : false);
                        else
                            staff.setStatus(
                                    row.getCell(9).getStringCellValue().compareTo("1") == 0 ? true : false);
                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setPermission((int) row.getCell(10).getNumericCellValue());
                        else
                            staff.setPermission(Integer.parseInt(row.getCell(10).getStringCellValue()));
                    }

                    //Add to database
                    if (staffDAO.saveOrUpdate(staff)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        staffsTotal = total;
                    } else
                        continue;
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    staffsTotal = total;
    return SUCCESS;
}

From source file:com.hp.excelhandle.GetData.java

public void readExcel() {
    try {/*from  www .  j  av a2s.  com*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("database/customer.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns (max)
        int tmp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols)
                    cols = tmp;
            }
        }

        //            for(int r = 0; r < rows; r++) {
        //                row = sheet.getRow(r);
        //                if(row != null) {
        //                    for(int c = 0; c < cols; c++) {
        //                        cell = row.getCell((short)c);
        //                        if(cell != null) {
        //                            // Your code here
        //                        }
        //                    }
        //                }
        //            }

        row = sheet.getRow(8);
        if (row != null) {
            cell = row.getCell(1);
            if (cell != null)
                System.out.println("Row: " + 9 + ", Data: " + cell.getStringCellValue());
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}

From source file:com.hp.excelhandle.GetData.java

public ArrayList<Customer> loadCustomer() {
    ArrayList<Customer> listCustomer = new ArrayList();

    try {/*  ww  w. j ava2s  .  c om*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(mFileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);
        System.out.println("Cell value: " + sheet.getRow(rows - 1).getCell(0));

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 8; i < rows; i++) {

            row = sheet.getRow(i);
            if (row != null) {

                //If the customer id null
                if (row.getCell(ConfigFile.MA_DOI_TUONG_COL) == null
                        || row.getCell(ConfigFile.X_COORDINATES_COL) == null
                        || row.getCell(ConfigFile.Y_COORDINATES_COL) == null) {
                    continue;
                }

                //Init Customer Object
                Customer custumer = new Customer();
                custumer.setCoordinateX(row.getCell(ConfigFile.X_COORDINATES_COL).getNumericCellValue());
                custumer.setCoordinateY(row.getCell(ConfigFile.Y_COORDINATES_COL).getNumericCellValue());

                int tmp = 0;
                custumer.setStt((int) row.getCell(tmp++).getNumericCellValue());
                custumer.setTinhThanh(row.getCell(tmp++).getStringCellValue());
                custumer.setTuyenBanHangThu(row.getCell(tmp++).getStringCellValue());
                custumer.setMaNhanVien(row.getCell(tmp++).getStringCellValue());

                custumer.setX(row.getCell(tmp++).getStringCellValue());
                custumer.setMaDoiTuong(row.getCell(tmp++).getStringCellValue());
                custumer.setDoiTuong(row.getCell(tmp++).getStringCellValue());
                //                    custumer.setmNoDKy(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmCoDKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmNoTKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmTienBan(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmCoTKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmCKGG(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmNhapLai(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmNoCKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmCoCKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmDoanhThu(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmPhanTramNoChiaThu(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmNoToiDa(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmDaiDien(row.getCell(tmp++).getStringCellValue());

                custumer.setDiaChi(row.getCell(tmp++).getStringCellValue());
                custumer.setDienThoai(row.getCell(tmp++).getStringCellValue());
                custumer.setFax(row.getCell(tmp++).getStringCellValue());

                custumer.setGhiChu(row.getCell(tmp++).getStringCellValue());

                listCustomer.add(custumer);
                System.out.println("Add Object " + i);
            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return null;
    }

    return listCustomer;
}

From source file:com.hrr3.services.DayStarFileImportService.java

License:Apache License

public ImportDayStarData parseFileToObjects(String fileName, int hotelId)
        throws ApplicationException, IOException {

    HSSFWorkbook wb = this.readFile(fileName);
    ImportDayStarData dayStarData = null; //Container to save glance/response/summary
    SSRSnapshotDayStar glance = null; //To save glance data
    List<SSRSnapshotDayStarHotel> responses = null; //To save response data
    List<SSRSnapshotDayStarData> summaries = null; //To be implemented while reading excel in future version      

    //1. Validate Glance SheetName at tab#2
    if (wb.getNumberOfSheets() == 0 || wb.getSheet("Glance") == null)
        throw new ApplicationException("Excel file must contain a valid sheet called 'Glance'");

    //2. Validate Glance SheetName at tab#7 or tab#12
    if (wb.getNumberOfSheets() == 0 || wb.getSheet("Response") == null)
        throw new ApplicationException("Excel file must contain a valid sheet called 'Response'");

    //3. Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations
    HSSFSheet glanceSheet = wb.getSheet("Glance");
    HSSFSheet responseSheet = wb.getSheet("Response");

    //3.1 Instantiate main container
    dayStarData = new ImportDayStarData();

    //4. Fill out glance Data
    glance = new SSRSnapshotDayStar();
    glance.setHotelId(hotelId);//  w w  w  . j  av a 2  s  .co m
    glance.setCapHotel(getStringCellValue(glanceSheet.getRow(1).getCell(1)));//2B
    glance.setCapHotel2(getStringCellValue(glanceSheet.getRow(2).getCell(1)));//3B
    glance.setDateFrom(null);
    glance.setDateTo(null);
    glance.setCapWeek(getStringCellValue(glanceSheet.getRow(3).getCell(1)));//4B

    System.out.println(glance);

    //4.1 Fill out Summary Data (part of the same Glance sheet)   
    summaries = new ArrayList<SSRSnapshotDayStarData>();
    for (int iIndex = 1; iIndex <= 16; iIndex++) //16 is equals SUN - SAT by two weeks in the Glance sheet 
    {
        String sType = "";
        String sDateCurrYr = "";
        String sDateLastYr = "";
        String sDateRange = "";
        String sYearCurr = "";
        String sYearLast = "";

        String sOCCProp = "";
        String sOCCPropPC = "";
        String sOCCCompSet = "";
        String sOCCCompSetPC = "";
        String sOCCIndex = "";
        String sOCCIndexPC = "";

        String sARRProp = "";
        String sARRPropPC = "";
        String sARRCompSet = "";
        String sARRCompSetPC = "";
        String sARRIndex = "";
        String sARRIndexPC = "";

        String sRPProp = "";
        String sRPPropPC = "";
        String sRPCompSet = "";
        String sRPCompSetPC = "";
        String sRPIndex = "";
        String sRPIndexPC = "";

        int sCol = 0;//Index for left value
        int sColPC = 0;//Index for right value

        switch (iIndex) {

        case 1:
        case 9:
            sType = "SUN";
            sCol = 4;
            sColPC = 5;
            break;
        case 2:
        case 10:
            sType = "MON";
            sCol = 7;
            sColPC = 8;
            break;
        case 3:
        case 11:
            sType = "TUE";
            sCol = 10;
            sColPC = 11;
            break;
        case 4:
        case 12:
            sType = "WED";
            sCol = 13;
            sColPC = 14;
            break;
        case 5:
        case 13:
            sType = "THU";
            sCol = 16;
            sColPC = 17;
            break;
        case 6:
        case 14:
            sType = "FRI";
            sCol = 19;
            sColPC = 20;
            break;
        case 7:
        case 15:
            sType = "SAT";
            sCol = 22;
            sColPC = 23;
            break;
        case 8:
        case 16:
            sType = "SUBTOT";
            sCol = 25;
            sColPC = 26;
            break;

        }

        //Read Weekly section
        if (iIndex >= 1 && iIndex <= 8) {

            sType = "CURR" + sType;
            sDateRange = getStringCellValue(glanceSheet.getRow(5).getCell(1)).trim();//Read 6B

            sOCCProp = getStringCellValue(glanceSheet.getRow(9).getCell(sCol)).trim();
            sOCCPropPC = getStringCellValue(glanceSheet.getRow(9).getCell(sColPC)).trim();
            sOCCCompSet = getStringCellValue(glanceSheet.getRow(10).getCell(sCol)).trim();
            sOCCCompSetPC = getStringCellValue(glanceSheet.getRow(10).getCell(sColPC)).trim();
            sOCCIndex = getStringCellValue(glanceSheet.getRow(11).getCell(sCol)).trim();
            sOCCIndexPC = getStringCellValue(glanceSheet.getRow(11).getCell(sColPC)).trim();

            sARRProp = getStringCellValue(glanceSheet.getRow(13).getCell(sCol)).trim();
            sARRPropPC = getStringCellValue(glanceSheet.getRow(13).getCell(sColPC)).trim();
            sARRCompSet = getStringCellValue(glanceSheet.getRow(14).getCell(sCol)).trim();
            sARRCompSetPC = getStringCellValue(glanceSheet.getRow(14).getCell(sColPC)).trim();
            sARRIndex = getStringCellValue(glanceSheet.getRow(15).getCell(sCol)).trim();
            sARRIndexPC = getStringCellValue(glanceSheet.getRow(15).getCell(sColPC)).trim();

            sRPProp = getStringCellValue(glanceSheet.getRow(17).getCell(sCol)).trim();
            sRPPropPC = getStringCellValue(glanceSheet.getRow(17).getCell(sColPC)).trim();
            sRPCompSet = getStringCellValue(glanceSheet.getRow(18).getCell(sCol)).trim();
            sRPCompSetPC = getStringCellValue(glanceSheet.getRow(18).getCell(sColPC)).trim();
            sRPIndex = getStringCellValue(glanceSheet.getRow(19).getCell(sCol)).trim();
            sRPIndexPC = getStringCellValue(glanceSheet.getRow(19).getCell(sColPC)).trim();

        }
        //Read Running section
        else if (iIndex > 8) {

            sType = "RUN" + sType;
            sDateRange = getStringCellValue(glanceSheet.getRow(22).getCell(1)).trim();//Read 23B

            sOCCProp = getStringCellValue(glanceSheet.getRow(26).getCell(sCol)).trim();
            sOCCPropPC = getStringCellValue(glanceSheet.getRow(26).getCell(sColPC)).trim();
            sOCCCompSet = getStringCellValue(glanceSheet.getRow(27).getCell(sCol)).trim();
            sOCCCompSetPC = getStringCellValue(glanceSheet.getRow(27).getCell(sColPC)).trim();
            sOCCIndex = getStringCellValue(glanceSheet.getRow(28).getCell(sCol)).trim();
            sOCCIndexPC = getStringCellValue(glanceSheet.getRow(28).getCell(sColPC)).trim();

            sARRProp = getStringCellValue(glanceSheet.getRow(30).getCell(sCol)).trim();
            sARRPropPC = getStringCellValue(glanceSheet.getRow(30).getCell(sColPC)).trim();
            sARRCompSet = getStringCellValue(glanceSheet.getRow(31).getCell(sCol)).trim();
            sARRCompSetPC = getStringCellValue(glanceSheet.getRow(31).getCell(sColPC)).trim();
            sARRIndex = getStringCellValue(glanceSheet.getRow(32).getCell(sCol)).trim();
            sARRIndexPC = getStringCellValue(glanceSheet.getRow(32).getCell(sColPC)).trim();

            sRPProp = getStringCellValue(glanceSheet.getRow(34).getCell(sCol)).trim();
            sRPPropPC = getStringCellValue(glanceSheet.getRow(34).getCell(sColPC)).trim();
            sRPCompSet = getStringCellValue(glanceSheet.getRow(35).getCell(sCol)).trim();
            sRPCompSetPC = getStringCellValue(glanceSheet.getRow(35).getCell(sColPC)).trim();
            sRPIndex = getStringCellValue(glanceSheet.getRow(36).getCell(sCol)).trim();
            sRPIndexPC = getStringCellValue(glanceSheet.getRow(36).getCell(sColPC)).trim();

        }

        SSRSnapshotDayStarData summary = new SSRSnapshotDayStarData();

        summary.setType(sType);
        summary.setTab(2);
        summary.setSequence(iIndex);
        summary.setDatethisyr(sDateCurrYr);
        summary.setDatelastyr(sDateLastYr);
        summary.setCurrentyr(sYearCurr);
        summary.setLastyr(sYearLast);
        summary.setDaterange(sDateRange);

        summary.setOccProp(sOCCProp);
        summary.setOccPropPc(sOCCPropPC);
        summary.setOccCompset(sOCCCompSet);
        summary.setOccCompsetPc(sOCCCompSetPC);
        summary.setOccIndex(sOCCIndex);
        summary.setOccIndexPc(sOCCIndexPC);

        summary.setArrProp(sARRProp);
        summary.setArrPropPc(sARRPropPC);
        summary.setArrCompset(sARRCompSet);
        summary.setArrCompsetPc(sARRCompSetPC);
        summary.setArrIndex(sARRIndex);
        summary.setArrIndexPc(sARRIndexPC);

        summary.setRevparProp(sRPProp);
        summary.setRevparPropPc(sRPPropPC);
        summary.setRevparCompset(sRPCompSet);
        summary.setRevparCompsetPc(sRPCompSetPC);
        summary.setRevparIndex(sRPIndex);
        summary.setRevparIndexPc(sRPIndexPC);

        System.out.println(summary);
        summaries.add(summary);
    }

    //5. Find STR ID value on C (index=2) column
    int responseTotalRows = responseSheet.getPhysicalNumberOfRows();
    int strIDHeaderRow = -1;
    int currentRow = -1;
    int currentCol = -1;
    int strIDHeaderCol = 2; // Column C

    String strIDHeaderVal = "STR ID";
    String strHotelName = "Name";
    String columnNameForHotelName = "N/A";

    for (int i = 0; i < responseTotalRows; i++) {
        String cellValue = getStringCellValue(responseSheet.getRow(i).getCell(2));
        if (cellValue.equalsIgnoreCase(strIDHeaderVal)) {
            strIDHeaderRow = i;
            break;
        }
    }

    //Validate of Name is on column D or E
    if (getStringCellValue(responseSheet.getRow(strIDHeaderRow).getCell(3)).equalsIgnoreCase(strHotelName))
        columnNameForHotelName = "D";
    else if (getStringCellValue(responseSheet.getRow(strIDHeaderRow).getCell(4)).equalsIgnoreCase(strHotelName))
        columnNameForHotelName = "E";
    else
        throw new ApplicationException("Column for Hotel Name not found in row[" + strIDHeaderRow + "]");

    //6. If STRID could not be found, throws an error
    if (strIDHeaderRow == -1)
        throw new ApplicationException("Response Sheet must contain STR ID Header in Column C.");

    //7. Once STR ID was found, we look for QRSTUVW columns at index-> strIDHeaderRow
    String sDate1 = responseSheet.getRow(strIDHeaderRow).getCell(16).toString();
    String sDate2 = responseSheet.getRow(strIDHeaderRow).getCell(17).toString();
    String sDate3 = responseSheet.getRow(strIDHeaderRow).getCell(18).toString();
    String sDate4 = responseSheet.getRow(strIDHeaderRow).getCell(19).toString();
    String sDate5 = responseSheet.getRow(strIDHeaderRow).getCell(20).toString();
    String sDate6 = responseSheet.getRow(strIDHeaderRow).getCell(21).toString();
    String sDate7 = responseSheet.getRow(strIDHeaderRow).getCell(22).toString();

    //8. Move cursor to next row below to start reading Hotel compsets
    currentRow = ++strIDHeaderRow;
    currentCol = strIDHeaderCol;

    //9. Fill out response Data
    responses = new ArrayList<SSRSnapshotDayStarHotel>();

    //9.1 Process response Rows while cell value is not 0 or empty
    while (currentRow < responseTotalRows
            && (!getStringCellValue(responseSheet.getRow(currentRow).getCell(2)).equalsIgnoreCase("0")
                    && !getStringCellValue(responseSheet.getRow(currentRow).getCell(2)).isEmpty())) {

        SSRSnapshotDayStarHotel currentResponse = new SSRSnapshotDayStarHotel();

        //Date Data
        currentResponse.setDate1(sDate1);//sDate1
        currentResponse.setDate2(sDate2);//sDate2
        currentResponse.setDate3(sDate3);//sDate3
        currentResponse.setDate4(sDate4);//sDate4
        currentResponse.setDate5(sDate5);//sDate5
        currentResponse.setDate6(sDate6);//sDate6
        currentResponse.setDate7(sDate7);//sDate7

        //Str Data
        currentResponse.setStrId(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol)));
        currentCol = columnNameForHotelName.equalsIgnoreCase("D") ? currentCol + 1 : currentCol + 2;//Sum 1 if "Name" found in Column D, or Sum 2 if found in E
        currentResponse.setHotel(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//E
        currentResponse.setCity(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//F
        currentResponse.setZip(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//G
        currentResponse.setPhone(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//H
        currentResponse.setRooms(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//I
        currentResponse.setOpendate(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol)));
        currentCol += 7;//J

        //DayStar Data
        currentResponse
                .setDataDaystar1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//Q
        currentResponse
                .setDataDaystar2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//R
        currentResponse
                .setDataDaystar3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//S
        currentResponse
                .setDataDaystar4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//T
        currentResponse
                .setDataDaystar5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//U
        currentResponse
                .setDataDaystar6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//V
        currentResponse
                .setDataDaystar7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol)));
        currentCol += 3;//W

        //Segmented Data
        currentResponse.setDataSeg1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//Z
        currentResponse.setDataSeg2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AA
        currentResponse.setDataSeg3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AB
        currentResponse.setDataSeg4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AC
        currentResponse.setDataSeg5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AD
        currentResponse.setDataSeg6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AE
        currentResponse.setDataSeg7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol)));
        currentCol += 3;//AF

        //F&B Data
        currentResponse.setDataFb1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AI
        currentResponse.setDataFb2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AJ
        currentResponse.setDataFb3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AK
        currentResponse.setDataFb4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AL
        currentResponse.setDataFb5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AM
        currentResponse.setDataFb6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AN
        currentResponse.setDataFb7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AO

        System.out.println(currentResponse);

        responses.add(currentResponse);

        currentRow++;
        currentCol = strIDHeaderCol;
    }

    dayStarData.setGlance(glance);
    dayStarData.setSummaries(summaries);
    dayStarData.setResponse(responses);

    return dayStarData;

}

From source file:com.hrr3.services.FileImportService.java

License:Apache License

public List<ImportSSRLRRData> parseFileToObjects(String fileName, Date startDate, Date endDate)
        throws ApplicationException, IOException {

    HSSFWorkbook wb = this.readFile(fileName);
    List<ImportSSRLRRData> rowDataList = null;

    //1. Validate SheetName
    if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("LRR"))
        throw new ApplicationException("Excel file must contain a valid sheet called 'LRR'");

    //2. Validate row 3 (index 2) contains at least 14 columns
    if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 14)
        throw new ApplicationException(
                "'LRR setup sheet must contains at least 14 columns at Row#3 to identify SSR-LRR template.");

    //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations
    HSSFSheet lrrSheet = wb.getSheetAt(0);
    int totalRows = lrrSheet.getPhysicalNumberOfRows();
    int startIndex = 3;//DataIndex to start reading srr info
    int startDateIndex = -1; //To save row index for startdate
    int endDateIndex = -1;//To save row inxed for enddate
    int dateColumnIndex = 1;

    //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data
    for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) {
        HSSFCell currentDateCell = lrrSheet.getRow(rowIndex).getCell(dateColumnIndex);

        //3.1 If row is not latest one, validate Date and Format
        if (rowIndex < totalRows) {
            if (currentDateCell == null || currentDateCell.getDateCellValue() == null)
                throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty.");
            else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell))
                throw new ApplicationException(
                        "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format.");
        }//from w w w .j  a va2 s  .c  om

        //3.2 Get Start and End indexes according to date range requested by user
        if (currentDateCell.getDateCellValue().compareTo(startDate) == 0)
            startDateIndex = rowIndex;

        if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) {
            endDateIndex = rowIndex;
            break;
        }

    }

    System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************");
    System.out.println("******** StartDate INDEX: " + startDateIndex + " *************");
    System.out.println("******** EndDate INDEX: " + endDateIndex + " *************");

    //Create RowDataList to contain each row
    rowDataList = new ArrayList<ImportSSRLRRData>();

    //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects.      
    for (int i = startDateIndex; i <= endDateIndex; i++) {

        HSSFCell currentDateCell = lrrSheet.getRow(i).getCell(dateColumnIndex);
        Date statdateCol = currentDateCell.getDateCellValue();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        String statdate = dateFormat.format(statdateCol);
        String lrr1 = getStringCellValue(lrrSheet.getRow(i).getCell(3));
        String lrr2 = getStringCellValue(lrrSheet.getRow(i).getCell(4));
        String lrr3 = getStringCellValue(lrrSheet.getRow(i).getCell(5));
        String lrr4 = getStringCellValue(lrrSheet.getRow(i).getCell(6));
        String lrr5 = getStringCellValue(lrrSheet.getRow(i).getCell(7));
        String lrr6 = getStringCellValue(lrrSheet.getRow(i).getCell(8));
        String lrr7 = getStringCellValue(lrrSheet.getRow(i).getCell(9));
        String lrr8 = getStringCellValue(lrrSheet.getRow(i).getCell(10));
        String lrr9 = getStringCellValue(lrrSheet.getRow(i).getCell(11));
        String hp1 = getStringCellValue(lrrSheet.getRow(i).getCell(12));
        String hp2 = getStringCellValue(lrrSheet.getRow(i).getCell(13));

        //Fill rowData object
        ImportSSRLRRData rowData = new ImportSSRLRRData();

        rowData.setStatdate(statdate);
        rowData.setLrr1(new BigDecimal(lrr1));
        rowData.setLrr2(new BigDecimal(lrr2));
        rowData.setLrr3(new BigDecimal(lrr3));
        rowData.setLrr4(new BigDecimal(lrr4));
        rowData.setLrr5(new BigDecimal(lrr5));
        rowData.setLrr6(new BigDecimal(lrr6));
        rowData.setLrr7(new BigDecimal(lrr7));
        rowData.setLrr8(new BigDecimal(lrr8));
        rowData.setLrr9(new BigDecimal(lrr9));
        rowData.setLrrHp1(new BigDecimal(hp1));
        rowData.setLrrHp2(new BigDecimal(hp2));
        //Add row to the list
        rowDataList.add(rowData);
        System.out.println(rowData);

    }

    System.out.println("******** Rows to return => " + rowDataList.size() + " ********");

    return rowDataList;
}