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

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

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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

/**
 * Insert a row at a given index./*  w  ww.j ava2s .co m*/
 *
 * @param createAtIndex row-number of the cell at which to create a new row
 * @param sourceRow     the row to insert
 */
public void insertRowAt(int createAtIndex, Row sourceRow) {
    Row newRow = getRow(createAtIndex);
    if (newRow != null) {
        // shift all rows >= createAtIndex up by one
        getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1);
    } else {
        newRow = getSheet().createRow(createAtIndex);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < getSheet().getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            getSheet().addMergedRegion(newCellRangeAddress);
        }
    }
}

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

@Override
public void judgeRepeatExcle(InputStream is, int firstRowNum, int colNum, String fileName) throws Exception {
    Map<String, Object> sheetValues = new HashedMap();
    Workbook workbook = null;/*from   ww w  .  jav a  2  s .  c  om*/
    if (fileName.endsWith("xls")) {
        POIFSFileSystem fs = new POIFSFileSystem(is);
        workbook = new HSSFWorkbook(fs);
    } else if (fileName.endsWith("xlsx")) {
        workbook = new XSSFWorkbook(is);
    }
    Sheet sheet = workbook.getSheetAt(0);
    for (int i = firstRowNum; i < sheet.getLastRowNum() + 1; i++) {
        Row row = sheet.getRow(i - 1);
        System.out.println(i);
        Cell cell = row.getCell(colNum - 1);
        if (null != cell && null != POIUtils.getStringCellValue(cell)
                && !POIUtils.getStringCellValue(cell).equals("")) {
            if (sheetValues.containsKey(POIUtils.getStringCellValue(cell))) {
                throw new RuntimeException(
                        POIUtils.getStringCellValue(cell) + "??????");
            } else {
                sheetValues.put(POIUtils.getStringCellValue(cell), i);
            }
        }
    }
}

From source file:com.liferay.dynamic.data.lists.exporter.test.DDLExporterTest.java

License:Open Source License

@Test
public void testXLSExport() throws Exception {
    DDMForm ddmForm = DDMFormTestUtil.createDDMForm(_availableLocales, _defaultLocale);

    createDDMFormFields(ddmForm);/*from www.  ja  v a2  s  . c o m*/

    DDMFormValues ddmFormValues = DDMFormValuesTestUtil.createDDMFormValues(ddmForm, _availableLocales,
            _defaultLocale);

    createDDMFormFieldValues(ddmFormValues);

    DDLRecordSetTestHelper recordSetTestHelper = new DDLRecordSetTestHelper(_group);

    DDLRecordSet recordSet = recordSetTestHelper.addRecordSet(ddmForm);

    DDLRecordTestHelper recordTestHelper = new DDLRecordTestHelper(_group, recordSet);

    DDLRecord record = recordTestHelper.addRecord(ddmFormValues, WorkflowConstants.ACTION_PUBLISH);

    DDLRecordVersion recordVersion = record.getRecordVersion();

    DDLExporter ddlExporter = _ddlExporterFactory.getDDLExporter("xls");

    byte[] bytes = ddlExporter.export(recordSet.getRecordSetId());

    try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
            HSSFWorkbook workbook = new HSSFWorkbook(byteArrayInputStream)) {

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(0);

        Cell cell = null;

        for (int i = 0; i < 13; i++) {
            cell = row.getCell(i);

            Assert.assertEquals("Field" + i, cell.getStringCellValue());
        }

        row = sheet.getRow(1);

        cell = row.getCell(0);

        Assert.assertEquals("No", cell.getStringCellValue());

        cell = row.getCell(1);

        Assert.assertEquals("1/1/70", cell.getStringCellValue());

        cell = row.getCell(2);

        Assert.assertEquals("1", cell.getStringCellValue());

        cell = row.getCell(3);

        Assert.assertEquals("file.txt", cell.getStringCellValue());

        cell = row.getCell(4);

        Assert.assertEquals("Latitude: -8.035, Longitude: -34.918", cell.getStringCellValue());

        cell = row.getCell(5);

        Assert.assertEquals("2", cell.getStringCellValue());

        cell = row.getCell(6);

        Assert.assertEquals("Link to Page content", cell.getStringCellValue());

        cell = row.getCell(7);

        Assert.assertEquals("3", cell.getStringCellValue());

        cell = row.getCell(8);

        Assert.assertEquals("Option 1", cell.getStringCellValue());

        cell = row.getCell(9);

        Assert.assertEquals("Option 1", cell.getStringCellValue());

        cell = row.getCell(10);

        Assert.assertEquals("Text content", cell.getStringCellValue());

        cell = row.getCell(11);

        Assert.assertEquals("Text Area content", cell.getStringCellValue());

        cell = row.getCell(12);

        Assert.assertEquals("Text HTML content", cell.getStringCellValue());

        cell = row.getCell(13);

        Assert.assertEquals("Approved", cell.getStringCellValue());

        cell = row.getCell(14);

        Assert.assertEquals(formatDate(recordVersion.getStatusDate()), cell.getStringCellValue());

        cell = row.getCell(15);

        Assert.assertEquals(recordVersion.getUserName(), cell.getStringCellValue());
    }
}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<SmallScaleLng> populateSmallScaleLng(Sheet sheet, Tab tab) {

    logger.info("Class - ReadExcelFile - populateSmallScaleLng()");
    int columnNo = 0;
    List<SmallScaleLng> smallScaleLngList = new ArrayList<SmallScaleLng>();
    int rowCount = sheet.getLastRowNum();

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer();
    Set<String> columnNamesSet = new HashSet<String>();
    //      DataFormatter df=new DataFormatter();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        SmallScaleLng smallScaleLng = new SmallScaleLng();
        try {/*from w  w w.jav  a  2  s.  c  om*/
            columnNo = 0;
            smallScaleLng.setStatus(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            smallScaleLng.setType(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            smallScaleLng.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            smallScaleLng.setRegion(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));
            columnNo = 4;
            smallScaleLng.setLocation(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK
                            : df.formatCellValue(row.getCell(4)));
            columnNo = 5;
            smallScaleLng.setTerminalName(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? BLANK
                            : df.formatCellValue(row.getCell(5)));
            columnNo = 6;
            smallScaleLng.setStartDate(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? 0
                            : Integer.valueOf(df.formatCellValue(row.getCell(6))));
            columnNo = 7;
            smallScaleLng.setCompany(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? BLANK
                            : df.formatCellValue(row.getCell(7)));
            columnNo = 8;
            smallScaleLng.setTechnologyProviderCompany(
                    null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK
                                    : df.formatCellValue(row.getCell(8)));
            columnNo = 9;
            smallScaleLng.setTechnology(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? BLANK
                            : df.formatCellValue(row.getCell(9)));
            columnNo = 10;
            smallScaleLng.setLiquefactionCapacity(
                    null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? 0
                                    : Double.valueOf(df.formatCellValue(row.getCell(10))));
            columnNo = 11;
            smallScaleLng.setLiquefactionCapcityUnit(
                    null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? BLANK
                                    : df.formatCellValue(row.getCell(11)));
            columnNo = 12;
            smallScaleLng.setRegasificationCapacity(
                    null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? 0
                                    : Double.valueOf(df.formatCellValue(row.getCell(12))));
            columnNo = 13;
            smallScaleLng.setRegasificationCapcityUnit(
                    null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? BLANK
                                    : df.formatCellValue(row.getCell(13)));
            columnNo = 14;
            smallScaleLng
                    .setBunkeringCapacity(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? 0
                                    : Double.valueOf(df.formatCellValue(row.getCell(14))));
            columnNo = 15;
            smallScaleLng.setBunkeringCapacityUnit(
                    null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? BLANK
                                    : df.formatCellValue(row.getCell(15)));
            columnNo = 16;
            smallScaleLng.setSource(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? BLANK
                            : df.formatCellValue(row.getCell(16)));
            columnNo = 17;
            smallScaleLng
                    .setStorageCapacity(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? 0
                                    : Double.valueOf(df.formatCellValue(row.getCell(17))));
            columnNo = 18;
            smallScaleLng.setEpc(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? BLANK
                            : df.formatCellValue(row.getCell(18)));
            columnNo = 19;
            smallScaleLng.setCapex(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(19))));
            columnNo = 20;
            smallScaleLng
                    .setDistributionType(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? BLANK
                                    : df.formatCellValue(row.getCell(20)));
            columnNo = 21;
            smallScaleLng.setOtherDetails(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? BLANK
                            : df.formatCellValue(row.getCell(21)));
            columnNo = 22;
            smallScaleLng.setSources(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(22))) ? BLANK
                            : df.formatCellValue(row.getCell(22)));
            columnNo = 23;
            smallScaleLng
                    .setTerminalOtherName(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(23))) ? BLANK
                                    : df.formatCellValue(row.getCell(23)));
            columnNo = 24;
            smallScaleLng
                    .setContactOrEmailId(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(24))) ? BLANK
                                    : df.formatCellValue(row.getCell(24)));

            smallScaleLngList.add(smallScaleLng);
        } catch (Exception e) {
            logger.info("Class - ReadExcelFile - populateSmallScaleLng():" + e);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);

        }
    }
    if (totalRecords > 0) {

        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("smallScaleLng reading excel failure");
        logger.info("smallScaleLng totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    return smallScaleLngList;

}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<CompanyOilGas> populateProductionCompanyOilGas(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateProductionCompanyOilGas()");
    int columnNo = 0;
    List<CompanyOilGas> companyOilGasList = new ArrayList<CompanyOilGas>();
    int rowCount = sheet.getLastRowNum();

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer();
    Set<String> columnNamesSet = new HashSet<String>();
    //      DataFormatter df=new DataFormatter();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        CompanyOilGas companyOilGas = new CompanyOilGas();
        try {/*ww w .  j a  va2 s  .  c  om*/
            columnNo = 0;
            companyOilGas.setName(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            companyOilGas.setRegion(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            companyOilGas.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            companyOilGas.setType(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));

            columnNo = 4;
            companyOilGas.setYear2005(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(4))));
            columnNo = 5;
            companyOilGas.setYear2006(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(5))));
            columnNo = 6;
            companyOilGas.setYear2007(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(6))));
            columnNo = 7;
            companyOilGas.setYear2008(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(7))));
            columnNo = 8;
            companyOilGas.setYear2009(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(8))));
            columnNo = 9;
            companyOilGas.setYear2010(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(9))));
            columnNo = 10;
            companyOilGas.setYear2011(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(10))));
            columnNo = 11;
            companyOilGas.setYear2012(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(11))));
            columnNo = 12;
            companyOilGas.setYear2013(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(12))));
            columnNo = 13;
            companyOilGas.setYear2014(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(13))));
            columnNo = 14;
            companyOilGas.setYear2015(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(14))));
            columnNo = 15;
            companyOilGas.setYear2016(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(15))));
            columnNo = 16;
            companyOilGas.setYear2017(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(16))));

            columnNo = 17;
            companyOilGas.setNotes(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? BLANK
                            : df.formatCellValue(row.getCell(17)));

            columnNo = 18;
            companyOilGas.setSource2005(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? BLANK
                            : df.formatCellValue(row.getCell(18)));
            columnNo = 19;
            companyOilGas.setSource2006(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? BLANK
                            : df.formatCellValue(row.getCell(19)));
            columnNo = 20;
            companyOilGas.setSource2007(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? BLANK
                            : df.formatCellValue(row.getCell(20)));
            columnNo = 21;
            companyOilGas.setSource2008(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? BLANK
                            : df.formatCellValue(row.getCell(21)));
            columnNo = 22;
            companyOilGas.setSource2009(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(22))) ? BLANK
                            : df.formatCellValue(row.getCell(22)));
            columnNo = 23;
            companyOilGas.setSource2010(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(23))) ? BLANK
                            : df.formatCellValue(row.getCell(23)));
            columnNo = 24;
            companyOilGas.setSource2011(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(24))) ? BLANK
                            : df.formatCellValue(row.getCell(24)));
            columnNo = 25;
            companyOilGas.setSource2012(null == row.getCell(25) || null == df.formatCellValue(row.getCell(25))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(25))) ? BLANK
                            : df.formatCellValue(row.getCell(25)));
            columnNo = 26;
            companyOilGas.setSource2013(null == row.getCell(26) || null == df.formatCellValue(row.getCell(26))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(26))) ? BLANK
                            : df.formatCellValue(row.getCell(26)));
            columnNo = 27;
            companyOilGas.setSource2014(null == row.getCell(27) || null == df.formatCellValue(row.getCell(27))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(27))) ? BLANK
                            : df.formatCellValue(row.getCell(27)));
            columnNo = 28;
            companyOilGas.setSource2015(null == row.getCell(28) || null == df.formatCellValue(row.getCell(28))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(28))) ? BLANK
                            : df.formatCellValue(row.getCell(28)));
            columnNo = 29;
            companyOilGas.setSource2016(null == row.getCell(29) || null == df.formatCellValue(row.getCell(29))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(29))) ? BLANK
                            : df.formatCellValue(row.getCell(29)));
            columnNo = 30;
            companyOilGas.setSource2017(null == row.getCell(30) || null == df.formatCellValue(row.getCell(30))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(30))) ? BLANK
                            : df.formatCellValue(row.getCell(30)));
            companyOilGasList.add(companyOilGas);
        } catch (Exception e) {
            logger.info("Class - ReadExcelFile - populateProductionCompanyOilGas():" + e);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);

        }
    }
    if (totalRecords > 0) {

        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("CompanyOilGas totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    return companyOilGasList;
}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<Contracts> populateContractsData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateContractsData()");
    int columnNo = 0;
    List<Contracts> contractsList = new ArrayList<Contracts>();
    int rowCount = sheet.getLastRowNum();

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer(BLANK);
    Set<String> columnNamesSet = new HashSet<String>();
    //      DataFormatter df=new DataFormatter();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        Contracts contracts = new Contracts();
        try {/*from   ww  w.  j  a  v a 2s.  c  o m*/
            columnNo = 0;
            contracts.setContractIndicator(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            contracts.setYear(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(1))));
            columnNo = 2;
            contracts.setExportTerminal(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            contracts.setExportCountry(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));
            columnNo = 4;
            contracts.setExportCompany(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK
                            : df.formatCellValue(row.getCell(4)));
            columnNo = 5;
            contracts.setImportTerminal(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? BLANK
                            : df.formatCellValue(row.getCell(5)));
            columnNo = 6;
            contracts.setImportCountry(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? BLANK
                            : df.formatCellValue(row.getCell(6)));
            columnNo = 7;
            contracts.setImportCompany(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? BLANK
                            : df.formatCellValue(row.getCell(7)));
            columnNo = 8;
            contracts.setContractedQuantity(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(8))));
            columnNo = 9;
            contracts.setContractAgreementDate(
                    null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? BLANK
                                    : df.formatCellValue(row.getCell(9)));
            columnNo = 10;
            contracts
                    .setContractStartFrom(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? 0
                                    : Integer.parseInt(df.formatCellValue(row.getCell(10))));
            columnNo = 11;
            contracts.setContractEndsIn(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(11))));
            columnNo = 12;
            contracts.setTypeOfTransportation(
                    null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? BLANK
                                    : df.formatCellValue(row.getCell(12)));
            columnNo = 13;
            contracts.setContractAgreementStatus(
                    null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? BLANK
                                    : df.formatCellValue(row.getCell(13)));
            columnNo = 14;
            contracts.setContractAdditionalDetails(
                    null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? BLANK
                                    : df.formatCellValue(row.getCell(14)));
            columnNo = 15;
            contracts.setSources(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? BLANK
                            : df.formatCellValue(row.getCell(15)));
            contractsList.add(contracts);
        } catch (Exception e) {
            logger.info("Class - ReadExcelFile - populateContractsData():" + e);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);

        }

    }
    if (totalRecords > 0) {

        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("Contracts totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for NaturalGas:" + contractsList.size());
    return contractsList;
}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<SupplyDemand> populateSupplyDemandData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateSupplyDemandData");
    int columnNo = 0;
    List<SupplyDemand> supplyDemandList = new ArrayList<SupplyDemand>();
    int rowCount = sheet.getLastRowNum();
    final String hypen = "-";
    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer(BLANK);
    Set<String> columnNamesSet = new HashSet<String>();
    int linebreak = 1;
    //      DataFormatter df=new DataFormatter();
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        SupplyDemand sd = new SupplyDemand();
        try {//from   w  ww .  j  a  v  a 2s  . co m

            columnNo = 0;
            sd.setTradeType(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            sd.setRegion(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            sd.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));

            columnNo = 3;
            if (null != row.getCell(3) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(3)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(3))))
                sd.setYear2000(Float.valueOf(df.formatCellValue(row.getCell(3))));
            columnNo = 4;
            if (null != row.getCell(4) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(4)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(4))))
                sd.setYear2001(Float.valueOf(df.formatCellValue(row.getCell(4))));
            columnNo = 5;
            if (null != row.getCell(5) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(5)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(5))))
                sd.setYear2002(Float.valueOf(df.formatCellValue(row.getCell(5))));
            columnNo = 6;
            if (null != row.getCell(6) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(6)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(6))))
                sd.setYear2003(Float.valueOf(df.formatCellValue(row.getCell(6))));
            columnNo = 7;
            if (null != row.getCell(7) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(7)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(7))))
                sd.setYear2004(Float.valueOf(df.formatCellValue(row.getCell(7))));
            columnNo = 8;
            if (null != row.getCell(8) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(8)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(8))))
                sd.setYear2005(Float.valueOf(df.formatCellValue(row.getCell(8))));
            columnNo = 9;
            if (null != row.getCell(9) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(9)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(9))))
                sd.setYear2006(Float.valueOf(df.formatCellValue(row.getCell(9))));
            columnNo = 10;
            if (null != row.getCell(10) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(10)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(10))))
                sd.setYear2007(Float.valueOf(df.formatCellValue(row.getCell(10))));

            columnNo = 11;
            if (null != row.getCell(11) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(11)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(11))))
                sd.setYear2008(Float.valueOf(df.formatCellValue(row.getCell(11))));
            columnNo = 12;
            if (null != row.getCell(12) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(12)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(12))))
                sd.setYear2009(Float.valueOf(df.formatCellValue(row.getCell(12))));
            columnNo = 13;
            if (null != row.getCell(13) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(13)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(13))))
                sd.setYear2010(Float.valueOf(df.formatCellValue(row.getCell(13))));
            columnNo = 14;
            if (null != row.getCell(14) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(14)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(14))))
                sd.setYear2011(Float.valueOf(df.formatCellValue(row.getCell(14))));
            columnNo = 15;
            if (null != row.getCell(15) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(15)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(15))))
                sd.setYear2012(Float.valueOf(df.formatCellValue(row.getCell(15))));
            columnNo = 16;
            if (null != row.getCell(16) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(16)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(16))))
                sd.setYear2013(Float.valueOf(df.formatCellValue(row.getCell(16))));
            columnNo = 17;
            if (null != row.getCell(17) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(17)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(17))))
                sd.setYear2014(Float.valueOf(df.formatCellValue(row.getCell(17))));
            columnNo = 18;
            if (null != row.getCell(18) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(18)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(18))))
                sd.setYear2015(Float.valueOf(df.formatCellValue(row.getCell(18))));
            columnNo = 19;
            if (null != row.getCell(19) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(19)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(19))))
                sd.setYear2016(Float.valueOf(df.formatCellValue(row.getCell(19))));
            columnNo = 20;
            if (null != row.getCell(20) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(20)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(20))))
                sd.setYear2017(Float.valueOf(df.formatCellValue(row.getCell(20))));

            columnNo = 21;
            if (null != row.getCell(21) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(21)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(21))))
                sd.setYear2018(Float.valueOf(df.formatCellValue(row.getCell(21))));
            columnNo = 22;
            if (null != row.getCell(22) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(22)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(22))))
                sd.setYear2019(Float.valueOf(df.formatCellValue(row.getCell(22))));
            columnNo = 23;
            if (null != row.getCell(23) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(23)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(23))))
                sd.setYear2020(Float.valueOf(df.formatCellValue(row.getCell(23))));
            columnNo = 24;
            if (null != row.getCell(24) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(24)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(24))))
                sd.setYear2021(Float.valueOf(df.formatCellValue(row.getCell(24))));
            columnNo = 25;
            if (null != row.getCell(25) && !hypen.equalsIgnoreCase(df.formatCellValue(row.getCell(25)))
                    && !BLANK.equalsIgnoreCase(df.formatCellValue(row.getCell(25))))
                sd.setYear2022(Float.valueOf(df.formatCellValue(row.getCell(25))));

            //            columnNo=4;sd.setYear2001((null==row.getCell(4) || hypen.equalsIgnoreCase(row.getCell(4).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(4).getNumericCellValue()).floatValue());
            //            columnNo=5;sd.setYear2002((null==row.getCell(5) || hypen.equalsIgnoreCase(row.getCell(5).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(5).getNumericCellValue()).floatValue());
            //            columnNo=6;sd.setYear2003((null==row.getCell(6) || hypen.equalsIgnoreCase(row.getCell(6).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(6).getNumericCellValue()).floatValue());
            //            columnNo=7;sd.setYear2004((null==row.getCell(7) || hypen.equalsIgnoreCase(row.getCell(7).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(7).getNumericCellValue()).floatValue());
            //            columnNo=8;sd.setYear2005((null==row.getCell(8) || hypen.equalsIgnoreCase(row.getCell(8).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(8).getNumericCellValue()).floatValue());
            //            columnNo=9;sd.setYear2006((null==row.getCell(9) || hypen.equalsIgnoreCase(row.getCell(9).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(9).getNumericCellValue()).floatValue());
            //            columnNo=10;sd.setYear2007((null==row.getCell(10) || hypen.equalsIgnoreCase(row.getCell(10).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(10).getNumericCellValue()).floatValue());
            //            columnNo=11;sd.setYear2008((null==row.getCell(11) || hypen.equalsIgnoreCase(row.getCell(11).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(11).getNumericCellValue()).floatValue());
            //            columnNo=12;sd.setYear2009((null==row.getCell(12) || hypen.equalsIgnoreCase(row.getCell(12).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(12).getNumericCellValue()).floatValue());
            //            columnNo=13;sd.setYear2010((null==row.getCell(13) || hypen.equalsIgnoreCase(row.getCell(13).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(13).getNumericCellValue()).floatValue());
            //            columnNo=14;sd.setYear2011((null==row.getCell(14) || hypen.equalsIgnoreCase(row.getCell(14).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(14).getNumericCellValue()).floatValue());
            //            columnNo=15;sd.setYear2012((null==row.getCell(15) || hypen.equalsIgnoreCase(row.getCell(15).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(15).getNumericCellValue()).floatValue());
            //            columnNo=16;sd.setYear2013((null==row.getCell(16) || hypen.equalsIgnoreCase(row.getCell(16).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(16).getNumericCellValue()).floatValue());
            //            columnNo=17;sd.setYear2014((null==row.getCell(17) || hypen.equalsIgnoreCase(row.getCell(17).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(17).getNumericCellValue()).floatValue());
            //            columnNo=18;sd.setYear2015((null==row.getCell(18) || hypen.equalsIgnoreCase(row.getCell(18).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(18).getNumericCellValue()).floatValue());
            //            
            //            columnNo=19;sd.setYear2016((null==row.getCell(19) || hypen.equalsIgnoreCase(row.getCell(19).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(19).getNumericCellValue()).floatValue());
            //            columnNo=20;sd.setYear2017((null==row.getCell(20) || hypen.equalsIgnoreCase(row.getCell(20).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(20).getNumericCellValue()).floatValue());
            //            columnNo=21;sd.setYear2018((null==row.getCell(21) || hypen.equalsIgnoreCase(row.getCell(21).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(21).getNumericCellValue()).floatValue());
            //            columnNo=22;sd.setYear2019((null==row.getCell(22) || hypen.equalsIgnoreCase(row.getCell(22).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(22).getNumericCellValue()).floatValue());
            //            columnNo=23;sd.setYear2020((null==row.getCell(23) || hypen.equalsIgnoreCase(row.getCell(23).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(23).getNumericCellValue()).floatValue());
            //            columnNo=24;sd.setYear2021((null==row.getCell(24) || hypen.equalsIgnoreCase(row.getCell(24).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(24).getNumericCellValue()).floatValue());
            //            columnNo=25;sd.setYear2022((null==row.getCell(25) || hypen.equalsIgnoreCase(row.getCell(25).getStringCellValue()))?0:BigDecimal.valueOf(row.getCell(25).getNumericCellValue()).floatValue());

            supplyDemandList.add(sd);
        } catch (Exception e) {
            logger.error("Exception in ReadExcelFile - populateSupplyDemandData():" + e);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            if (columnName.contains(".0"))
                columnName = columnName.substring(0, columnName.length() - 2);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);

        }

    }
    if (totalRecords > 0) {

        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("SupplyDemand totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for SupplyDemand:" + supplyDemandList.size());
    return supplyDemandList;
}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<NaturalGas> populateNaturalGasData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateNaturalGasData()");
    int columnNo = 0;
    List<NaturalGas> naturalList = new ArrayList<NaturalGas>();
    int rowCount = sheet.getLastRowNum();

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer(BLANK);
    Set<String> columnNamesSet = new HashSet<String>();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        NaturalGas n = new NaturalGas();
        try {//from www  .  j  a  va 2  s .  c o m
            columnNo = 0;
            n.setField(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            n.setRegion(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            n.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            n.setLocation(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));
            columnNo = 4;
            n.setBasin(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK
                            : df.formatCellValue(row.getCell(4)));
            columnNo = 5;
            n.setStatus(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? BLANK
                            : df.formatCellValue(row.getCell(5)));
            columnNo = 6;
            n.setOnshoreOrOffshore(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? BLANK
                            : df.formatCellValue(row.getCell(6)));
            columnNo = 7;
            n.setProductionStartDate(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? null
                            : new Date(df.formatCellValue(row.getCell(7))));
            columnNo = 8;
            n.setCurrentOperator(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK
                            : df.formatCellValue(row.getCell(8)));
            columnNo = 9;
            n.setCurrentOwners(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? BLANK
                            : df.formatCellValue(row.getCell(9)));
            columnNo = 10;
            n.setCurrentOwnership(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(10))));

            //            System.out.println("NaturalGas first");

            columnNo = 11;
            n.setHistoricOperator(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? BLANK
                            : df.formatCellValue(row.getCell(11)));
            columnNo = 12;
            n.setHistoricOwners(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? BLANK
                            : df.formatCellValue(row.getCell(12)));
            columnNo = 13;
            n.setHistoricOwnership(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(13))));
            columnNo = 14;
            n.setHistoricEquityYear(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(14))));
            columnNo = 15;
            n.setSourceEquity(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? BLANK
                            : df.formatCellValue(row.getCell(15)));
            columnNo = 16;
            n.setYear2005(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(16))));
            columnNo = 17;
            n.setYear2006(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(17))));
            columnNo = 18;
            n.setYear2007(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(18))));
            columnNo = 19;
            n.setYear2008(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(19))));
            columnNo = 20;
            n.setYear2009(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(20))));

            //            System.out.println("NaturalGas second");

            columnNo = 21;
            n.setYear2010(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(21))));
            columnNo = 22;
            n.setYear2011(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(22))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(22))));
            columnNo = 23;
            n.setYear2012(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(23))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(23))));
            columnNo = 24;
            n.setYear2013(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(24))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(24))));
            columnNo = 25;
            n.setYear2014(null == row.getCell(25) || null == df.formatCellValue(row.getCell(25))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(25))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(25))));
            columnNo = 26;
            n.setNotes(null == row.getCell(26) || null == df.formatCellValue(row.getCell(26))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(26))) ? BLANK
                            : df.formatCellValue(row.getCell(26)));

            naturalList.add(n);
        } catch (Exception e) {
            logger.info("Class - ReadExcelFile - populateNaturalGasData():" + e);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);

        }

    }
    if (totalRecords > 0) {

        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("NaturalGas totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for NaturalGas:" + naturalList.size());
    return naturalList;

}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<CrudeOil> populateCrudeOilData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateCrudeOilData()");
    int columnNo = 0;
    List<CrudeOil> crudeOilList = new ArrayList<CrudeOil>();
    int rowCount = sheet.getLastRowNum();

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer(BLANK);
    Set<String> columnNamesSet = new HashSet<String>();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        CrudeOil c = new CrudeOil();
        try {/*from   w  ww .  j a v  a  2s  . com*/
            columnNo = 0;
            c.setField(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            c.setRegion(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            c.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            c.setLocation(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));
            columnNo = 4;
            c.setBasin(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK
                            : df.formatCellValue(row.getCell(4)));
            columnNo = 5;
            c.setStatus(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? BLANK
                            : df.formatCellValue(row.getCell(5)));
            columnNo = 6;
            c.setOnshoreOrOffshore(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? BLANK
                            : df.formatCellValue(row.getCell(6)));
            columnNo = 7;
            c.setProductionStartDate(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? null
                            : new Date(df.formatCellValue(row.getCell(7))));
            columnNo = 8;
            c.setCurrentOperator(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK
                            : df.formatCellValue(row.getCell(8)));
            columnNo = 9;
            c.setCurrentOwners(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? BLANK
                            : df.formatCellValue(row.getCell(9)));
            columnNo = 10;
            c.setCurrentOwnership(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(10))));

            //            System.out.println("crude Oil first");

            columnNo = 11;
            c.setHistoricOperator(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? BLANK
                            : df.formatCellValue(row.getCell(11)));
            columnNo = 12;
            c.setHistoricOwners(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? BLANK
                            : df.formatCellValue(row.getCell(12)));
            columnNo = 13;
            c.setHistoricOwnership(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(13))));
            columnNo = 14;
            c.setHistoricEquityYear(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(14))));
            columnNo = 15;
            c.setSourceEquity(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? BLANK
                            : df.formatCellValue(row.getCell(15)));
            columnNo = 16;
            c.setYear2005(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(16))));
            columnNo = 17;
            c.setYear2006(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(17))));
            columnNo = 18;
            c.setYear2007(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(18))));
            columnNo = 19;
            c.setYear2008(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(19))));
            columnNo = 20;
            c.setYear2009(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(20))));

            //            System.out.println("crude Oil second");

            columnNo = 21;
            c.setYear2010(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(21))));
            columnNo = 22;
            c.setYear2011(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(22))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(22))));
            columnNo = 23;
            c.setYear2012(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(23))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(23))));
            columnNo = 24;
            c.setYear2013(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(24))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(24))));
            columnNo = 25;
            c.setYear2014(null == row.getCell(25) || null == df.formatCellValue(row.getCell(25))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(25))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(25))));
            columnNo = 26;
            c.setNotes(null == row.getCell(26) || null == df.formatCellValue(row.getCell(26))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(26))) ? BLANK
                            : df.formatCellValue(row.getCell(26)));

            crudeOilList.add(c);
        } catch (Exception e) {
            logger.error("Exception in ReadExcelFile - populateCrudeOilData():" + e);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);

        }

    }
    if (totalRecords > 0) {

        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("CrudeOil totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for CrudeOil:" + crudeOilList.size());
    return crudeOilList;

}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<PipeLine> populatePipeLinesData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populatePipeLinesData()");
    int columnNo = 0;
    List<PipeLine> pipeLineList = new ArrayList<PipeLine>();
    int rowCount = sheet.getLastRowNum();

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer(BLANK);
    Set<String> columnNamesSet = new HashSet<String>();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        PipeLine p = new PipeLine();
        try {/* ww  w.  j a  v  a  2s .  co m*/
            columnNo = 0;
            p.setPipeline(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            p.setSubPipelines(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            p.setParentChildRelation(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            p.setStatus(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));
            columnNo = 4;
            p.setCommodity(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK
                            : df.formatCellValue(row.getCell(4)));
            columnNo = 5;
            p.setStartPoint(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? BLANK
                            : df.formatCellValue(row.getCell(5)));
            columnNo = 6;
            p.setEndPoint(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? BLANK
                            : df.formatCellValue(row.getCell(6)));
            columnNo = 7;
            p.setCountry(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? BLANK
                            : df.formatCellValue(row.getCell(7)));
            columnNo = 8;
            p.setRegion(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK
                            : df.formatCellValue(row.getCell(8)));
            columnNo = 9;
            p.setStartCountry(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? BLANK
                            : df.formatCellValue(row.getCell(9)));
            columnNo = 10;
            p.setStartRegion(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? BLANK
                            : df.formatCellValue(row.getCell(10)));
            columnNo = 11;
            p.setEndCountry(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? BLANK
                            : df.formatCellValue(row.getCell(11)));

            //            System.out.println("first pipelines");

            columnNo = 12;
            p.setEndRegion(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? BLANK
                            : df.formatCellValue(row.getCell(12)));
            columnNo = 13;
            p.setRoute(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? BLANK
                            : df.formatCellValue(row.getCell(13)));
            columnNo = 14;
            p.setPipelineType(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? BLANK
                            : df.formatCellValue(row.getCell(14)));
            columnNo = 15;
            p.setOnshoreOrOffshore(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? BLANK
                            : df.formatCellValue(row.getCell(15)));
            columnNo = 16;
            p.setStartDate(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? null
                            : new Date(df.formatCellValue(row.getCell(16))));
            columnNo = 17;
            p.setCommodityDetails(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? BLANK
                            : df.formatCellValue(row.getCell(17)));
            columnNo = 18;
            p.setBasicDetailsSource(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? BLANK
                            : df.formatCellValue(row.getCell(18)));
            columnNo = 19;
            p.setBasicDetailNotes(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? BLANK
                            : df.formatCellValue(row.getCell(19)));
            columnNo = 20;
            p.setOperator(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? BLANK
                            : df.formatCellValue(row.getCell(20)));
            columnNo = 21;
            p.setEquityPartners(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? BLANK
                            : df.formatCellValue(row.getCell(21)));

            //            System.out.println("second pipelines");
            System.out.println(row.getCell(22));
            columnNo = 22;
            p.setEquityStakes(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(22))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(22))));
            columnNo = 23;
            p.setCompanySource(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(23))) ? BLANK
                            : df.formatCellValue(row.getCell(23)));
            columnNo = 24;
            p.setCompanyNotes(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(24))) ? BLANK
                            : df.formatCellValue(row.getCell(24)));
            columnNo = 25;
            p.setLength(null == row.getCell(25) || null == df.formatCellValue(row.getCell(25))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(25))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(25))));
            columnNo = 26;
            p.setDiameter(null == row.getCell(26) || null == df.formatCellValue(row.getCell(26))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(26))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(26))));
            //            if(row.getCell(25)!=null)
            //            {
            //               if(row.getCell(25).getCellType()== Cell.CELL_TYPE_STRING)
            //               {   
            //                     p.setDiameter(row.getCell(25).getStringCellValue());
            //               }   
            //               else if(row.getCell(25).getCellType()==Cell.CELL_TYPE_NUMERIC)
            //               {   
            //                  p.setDiameter(String.valueOf(row.getCell(25).getNumericCellValue()));
            //               }   
            //            }
            //            else
            //               p.setDiameter("");

            columnNo = 27;
            p.setCapacity(null == row.getCell(27) || null == df.formatCellValue(row.getCell(27))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(27))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(27))));
            columnNo = 28;
            p.setCapex(null == row.getCell(28) || null == df.formatCellValue(row.getCell(28))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(28))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(28))));
            columnNo = 29;
            p.setParameterSource(null == row.getCell(29) || null == df.formatCellValue(row.getCell(29))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(29))) ? BLANK
                            : df.formatCellValue(row.getCell(29)));
            columnNo = 30;
            p.setParameterNotes(null == row.getCell(30) || null == df.formatCellValue(row.getCell(30))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(30))) ? BLANK
                            : df.formatCellValue(row.getCell(30)));

            pipeLineList.add(p);
        } catch (Exception e) {
            logger.error("Exception in ReadExcelFile -populatePipeLinesData():" + e);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);

        }

    }
    if (totalRecords > 0) {

        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("PipeLine totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for PipeLine:" + pipeLineList.size());
    return pipeLineList;

}