Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

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);//from w w  w.  ja v a2 s  .com
        SmallScaleLng smallScaleLng = new SmallScaleLng();
        try {
            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);//from   ww w .j av a 2 s  .  com
        CompanyOilGas companyOilGas = new CompanyOilGas();
        try {
            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);/* w w w .j ava  2  s  .c om*/
        Contracts contracts = new Contracts();
        try {
            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);//w w w  .j  av a  2s  .  com
        SupplyDemand sd = new SupplyDemand();
        try {

            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);/* w ww .  ja v  a2s  .  c o  m*/
        NaturalGas n = new NaturalGas();
        try {
            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);/*from w w w  .  j  a  va2  s  .  com*/
        CrudeOil c = new CrudeOil();
        try {
            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);/*  w w w .  j  a va  2  s.  c om*/
        PipeLine p = new PipeLine();
        try {
            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;

}

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

private List<Lng> populateLngData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateLngData()");
    int columnNo = 0;
    List<Lng> lngList = new ArrayList<Lng>();
    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++) {
        if (i == 191)
            System.out.println(i);
        Row row = sheet.getRow(i);/*from   w  ww .  ja v  a 2 s .  c  o  m*/
        Lng l = new Lng();
        try {
            columnNo = 0;
            l.setName(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equals(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            l.setCountry(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equals(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            l.setArea(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equals(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            l.setRegion(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equals(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));
            columnNo = 4;
            l.setType(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equals(df.formatCellValue(row.getCell(4))) ? BLANK
                            : df.formatCellValue(row.getCell(4)));
            columnNo = 5;
            l.setStatus(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equals(df.formatCellValue(row.getCell(5))) ? BLANK
                            : df.formatCellValue(row.getCell(5)));
            columnNo = 6;
            l.setFeedDetails(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equals(df.formatCellValue(row.getCell(6))) ? BLANK
                            : df.formatCellValue(row.getCell(6)));
            columnNo = 7;
            l.setFidDetails(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equals(df.formatCellValue(row.getCell(7))) ? BLANK
                            : df.formatCellValue(row.getCell(7)));
            columnNo = 8;
            l.setConstructionStatusDetails(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                    || (BLANK).equals(df.formatCellValue(row.getCell(8))) ? BLANK
                            : df.formatCellValue(row.getCell(8)));
            columnNo = 9;
            l.setSources(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                    || (BLANK).equals(df.formatCellValue(row.getCell(9))) ? BLANK
                            : df.formatCellValue(row.getCell(9)));
            columnNo = 10;
            l.setOtherStatusDetails(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equals(df.formatCellValue(row.getCell(10))) ? BLANK
                            : df.formatCellValue(row.getCell(10)));

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

            columnNo = 11;
            l.setStatusDate(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equals(df.formatCellValue(row.getCell(11))) ? null
                            : new Date(df.formatCellValue(row.getCell(11))));
            columnNo = 12;
            l.setStatusSource(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                    || (BLANK).equals(df.formatCellValue(row.getCell(12))) ? BLANK
                            : df.formatCellValue(row.getCell(12)));
            columnNo = 13;
            l.setOnshoreOrOffshore(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                    || (BLANK).equals(df.formatCellValue(row.getCell(13))) ? BLANK
                            : df.formatCellValue(row.getCell(13)));
            columnNo = 14;
            l.setExpectedStartYear(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                    || (BLANK).equals(df.formatCellValue(row.getCell(14))) ? null
                            : new Date(df.formatCellValue(row.getCell(14))));
            columnNo = 15;
            l.setScheduledStartYear(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                    || (BLANK).equals(df.formatCellValue(row.getCell(15))) ? null
                            : new Date(df.formatCellValue(row.getCell(15))));
            columnNo = 16;
            l.setDelayOrInitialStartYear(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16))
                    || (BLANK).equals(df.formatCellValue(row.getCell(16))) ? null
                            : new Date(df.formatCellValue(row.getCell(16))));
            columnNo = 17;
            l.setDelayDetails(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17))
                    || (BLANK).equals(df.formatCellValue(row.getCell(17))) ? BLANK
                            : df.formatCellValue(row.getCell(17)));
            columnNo = 18;
            l.setTechnologyDetails(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18))
                    || (BLANK).equals(df.formatCellValue(row.getCell(18))) ? BLANK
                            : df.formatCellValue(row.getCell(18)));
            columnNo = 19;
            l.setNumberOfTrainsOrNumberOfVaporizers(
                    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;
            l.setCapacity(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("LNG second");

            columnNo = 21;
            l.setCapacityYear(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(21))));
            columnNo = 22;
            l.setExpansionDetail(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22))
                    || (BLANK).equals(df.formatCellValue(row.getCell(22))) ? BLANK
                            : df.formatCellValue(row.getCell(22)));
            columnNo = 23;
            l.setSource(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23))
                    || (BLANK).equals(df.formatCellValue(row.getCell(23))) ? BLANK
                            : df.formatCellValue(row.getCell(23)));
            columnNo = 24;
            l.setAdditionalProducts(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24))
                    || (BLANK).equals(df.formatCellValue(row.getCell(24))) ? BLANK
                            : df.formatCellValue(row.getCell(24)));
            columnNo = 25;
            l.setAdditionalProductsProduction(
                    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;
            l.setAdditionalProductsProductionUnit(
                    null == row.getCell(26) || null == df.formatCellValue(row.getCell(26))
                            || (BLANK).equals(df.formatCellValue(row.getCell(26))) ? BLANK
                                    : df.formatCellValue(row.getCell(26)));
            columnNo = 27;
            l.setNumberOfStorageTanks(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;
            l.setTotalLngStorageCapacity(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;
            l.setSourcesOther(null == row.getCell(29) || null == df.formatCellValue(row.getCell(29))
                    || (BLANK).equals(df.formatCellValue(row.getCell(29))) ? BLANK
                            : df.formatCellValue(row.getCell(29)));
            columnNo = 30;
            l.setOperator(null == row.getCell(30) || null == df.formatCellValue(row.getCell(30))
                    || (BLANK).equals(df.formatCellValue(row.getCell(30))) ? BLANK
                            : df.formatCellValue(row.getCell(30)));
            //               
            ////               System.out.println("LNG third");
            //               
            columnNo = 31;
            l.setEquityPartners(null == row.getCell(31) || null == df.formatCellValue(row.getCell(31))
                    || (BLANK).equals(df.formatCellValue(row.getCell(31))) ? BLANK
                            : df.formatCellValue(row.getCell(31)));
            columnNo = 32;
            l.setEquityStakes(null == row.getCell(32) || null == df.formatCellValue(row.getCell(32))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(32))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(32))));
            columnNo = 33;
            l.setEquityNotes(null == row.getCell(33) || null == df.formatCellValue(row.getCell(33))
                    || (BLANK).equals(df.formatCellValue(row.getCell(33))) ? BLANK
                            : df.formatCellValue(row.getCell(33)));
            columnNo = 34;
            l.setHistoricOperator(null == row.getCell(34) || null == df.formatCellValue(row.getCell(34))
                    || (BLANK).equals(df.formatCellValue(row.getCell(34))) ? BLANK
                            : df.formatCellValue(row.getCell(34)));
            columnNo = 35;
            l.setHistoricEquityPartner(null == row.getCell(35) || null == df.formatCellValue(row.getCell(35))
                    || (BLANK).equals(df.formatCellValue(row.getCell(35))) ? BLANK
                            : df.formatCellValue(row.getCell(35)));
            columnNo = 36;
            l.setHistoricEquityStake(null == row.getCell(36) || null == df.formatCellValue(row.getCell(36))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(36))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(36))));
            columnNo = 37;
            l.setHistoricEquityYear(null == row.getCell(37) || null == df.formatCellValue(row.getCell(37))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(37))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(37))));
            columnNo = 38;
            l.setEquitySources(null == row.getCell(38) || null == df.formatCellValue(row.getCell(38))
                    || (BLANK).equals(df.formatCellValue(row.getCell(38))) ? BLANK
                            : df.formatCellValue(row.getCell(38)));
            columnNo = 39;
            l.setFeedOrInputType(null == row.getCell(39) || null == df.formatCellValue(row.getCell(39))
                    || (BLANK).equals(df.formatCellValue(row.getCell(39))) ? BLANK
                            : df.formatCellValue(row.getCell(39)));
            columnNo = 40;
            l.setFeedOrInputName(null == row.getCell(40) || null == df.formatCellValue(row.getCell(40))
                    || (BLANK).equals(df.formatCellValue(row.getCell(40))) ? BLANK
                            : df.formatCellValue(row.getCell(40)));
            //               
            ////               System.out.println("LNG fourth");
            //               
            columnNo = 41;
            l.setFeedOrInputDetails(null == row.getCell(41) || null == df.formatCellValue(row.getCell(41))
                    || (BLANK).equals(df.formatCellValue(row.getCell(41))) ? BLANK
                            : df.formatCellValue(row.getCell(41)));
            columnNo = 42;
            l.setDisttributionOrOutputType(
                    null == row.getCell(42) || null == df.formatCellValue(row.getCell(42))
                            || (BLANK).equals(df.formatCellValue(row.getCell(42))) ? BLANK
                                    : df.formatCellValue(row.getCell(42)));
            columnNo = 43;
            l.setDisttributionOrOutputName(
                    null == row.getCell(43) || null == df.formatCellValue(row.getCell(43))
                            || (BLANK).equals(df.formatCellValue(row.getCell(43))) ? BLANK
                                    : df.formatCellValue(row.getCell(43)));
            columnNo = 44;
            l.setDisttributionOrOutputDetails(
                    null == row.getCell(44) || null == df.formatCellValue(row.getCell(44))
                            || (BLANK).equals(df.formatCellValue(row.getCell(44))) ? BLANK
                                    : df.formatCellValue(row.getCell(44)));
            columnNo = 45;
            l.setJettyInfo_m3(null == row.getCell(45) || null == df.formatCellValue(row.getCell(45))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(45))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(45))));
            columnNo = 46;
            l.setCapex(null == row.getCell(46) || null == df.formatCellValue(row.getCell(46))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(46))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(46))));
            columnNo = 47;
            l.setCapexYear(null == row.getCell(47) || null == df.formatCellValue(row.getCell(47))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(47))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(47))));
            columnNo = 48;
            l.setCapexDetails(null == row.getCell(48) || null == df.formatCellValue(row.getCell(48))
                    || (BLANK).equals(df.formatCellValue(row.getCell(48))) ? BLANK
                            : df.formatCellValue(row.getCell(48)));
            columnNo = 49;
            l.setSources1(null == row.getCell(49) || null == df.formatCellValue(row.getCell(49))
                    || (BLANK).equals(df.formatCellValue(row.getCell(49))) ? BLANK
                            : df.formatCellValue(row.getCell(49)));
            columnNo = 50;
            l.setConstructionStart(null == row.getCell(50) || null == df.formatCellValue(row.getCell(50))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(50))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(50))));
            //               
            ////               System.out.println("LNG five");
            //               
            columnNo = 51;
            l.setConstructionEnd(null == row.getCell(51) || null == df.formatCellValue(row.getCell(51))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(51))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(51))));
            columnNo = 52;
            l.setConstructionCompanyName(null == row.getCell(52) || null == df.formatCellValue(row.getCell(52))
                    || (BLANK).equals(df.formatCellValue(row.getCell(52))) ? BLANK
                            : df.formatCellValue(row.getCell(52)));
            columnNo = 53;
            l.setConstructionContractDetails(
                    null == row.getCell(53) || null == df.formatCellValue(row.getCell(53))
                            || (BLANK).equals(df.formatCellValue(row.getCell(53))) ? BLANK
                                    : df.formatCellValue(row.getCell(53)));
            columnNo = 54;
            l.setOtherContractsDetails(null == row.getCell(54) || null == df.formatCellValue(row.getCell(54))
                    || (BLANK).equals(df.formatCellValue(row.getCell(54))) ? BLANK
                            : df.formatCellValue(row.getCell(54)));
            columnNo = 55;
            l.setLngTerminalOtherNames(null == row.getCell(55) || null == df.formatCellValue(row.getCell(55))
                    || (BLANK).equals(df.formatCellValue(row.getCell(55))) ? BLANK
                            : df.formatCellValue(row.getCell(55)));
            columnNo = 56;
            l.setRelatedCompanies(null == row.getCell(56) || null == df.formatCellValue(row.getCell(56))
                    || (BLANK).equals(df.formatCellValue(row.getCell(56))) ? BLANK
                            : df.formatCellValue(row.getCell(56)));
            columnNo = 57;
            l.setRelatedCompanyDetails(null == row.getCell(57) || null == df.formatCellValue(row.getCell(57))
                    || (BLANK).equals(df.formatCellValue(row.getCell(57))) ? BLANK
                            : df.formatCellValue(row.getCell(57)));
            columnNo = 58;
            l.setSources2(null == row.getCell(58) || null == df.formatCellValue(row.getCell(58))
                    || (BLANK).equals(df.formatCellValue(row.getCell(58))) ? BLANK
                            : df.formatCellValue(row.getCell(58)));
            columnNo = 59;
            l.setMoreInfo(null == row.getCell(59) || null == df.formatCellValue(row.getCell(59))
                    || (BLANK).equals(df.formatCellValue(row.getCell(59))) ? BLANK
                            : df.formatCellValue(row.getCell(59)));
            columnNo = 60;
            l.setUnits(null == row.getCell(60) || null == df.formatCellValue(row.getCell(60))
                    || (BLANK).equals(df.formatCellValue(row.getCell(60))) ? BLANK
                            : df.formatCellValue(row.getCell(60)));

            lngList.add(l);
        } catch (Exception e) {

            logger.error("Exception in ReadExcelFile - populateLngData():" + 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("Lng totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for Lng:" + lngList.size());
    return lngList;

}

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

private List<Storage> populateStorageData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateStorageData()");
    int columnNo = 0;
    List<Storage> storageList = new ArrayList<Storage>();
    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);// ww w  .j  av  a  2  s.c om
        Storage s = new Storage();

        try {
            columnNo = 0;
            s.setTankFarm(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;
            s.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;
            s.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;
            s.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;
            s.setStatus(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;
            s.setCommencementDate(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? null
                            : new Date(df.formatCellValue(row.getCell(5))));
            columnNo = 6;
            s.setCommencementSource(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;
            s.setCurrentOperator(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;
            s.setCurrentOwners(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;
            s.setCurrentOwnership(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;
            s.setOwnershipNotes(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? BLANK
                            : df.formatCellValue(row.getCell(10)));

            //      System.out.println("Storage First");

            columnNo = 11;
            s.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;
            s.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;
            s.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;
            s.setHistoricalEquityYear(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;
            s.setEquitySource(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;
            s.setProductsStored(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;
            s.setModeOfAccess(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;
            s.setCapacityM3(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;
            s.setCapacityYear(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(19))));
            columnNo = 20;
            s.setTanks(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("Storage second");

            columnNo = 21;
            s.setTankSizeRange_min_m3(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;
            s.setTankSizeRange_max_m3(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;
            s.setCapacitySource(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;
            s.setCapitalInvestment(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;
            s.setCapexSource(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;
            s.setNotes(null == row.getCell(26) || null == df.formatCellValue(row.getCell(26))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(26))) ? BLANK
                            : df.formatCellValue(row.getCell(26)));

            storageList.add(s);
        } catch (Exception e) {
            logger.error("Exception in ReadExcelFile - populateStorageData():" + 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("Storage totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for Storage:" + storageList.size());
    return storageList;

}

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

private List<Refinery> populateRefineryData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateRefineryData()");
    int columnNo = 0;
    List<Refinery> refineryList = new ArrayList<Refinery>();
    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);/*from w  ww.  j a  v  a2s  . c  o  m*/
        Refinery r = new Refinery();
        try {
            //            System.out.println(row.getRowNum());
            columnNo = 0;
            r.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;
            r.setLocation(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;
            r.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;
            r.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;
            r.setStatus(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;
            r.setCapacityYear(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(5))));
            columnNo = 6;
            r.setStatusDetails(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;
            r.setStatusDate(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)))); // Later this one needs to change to String
            columnNo = 8;
            r.setStatusSource(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;
            r.setMoreInfo(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;
            r.setType(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? BLANK
                            : df.formatCellValue(row.getCell(10)));

            //            System.out.println(" Refinery First");

            columnNo = 11;
            r.setStartYear(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? null
                            : new Date(df.formatCellValue(row.getCell(11))));
            columnNo = 12;
            r.setDecomissionedYear(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? null
                            : new Date(df.formatCellValue(row.getCell(12))));
            columnNo = 13;
            r.setAssetOrStartSource(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;
            r.setCurrentOperator(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;
            r.setCurrentEquityPartners(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;
            r.setCurrentEquityStakes(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;
            r.setEquityDetails(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;
            r.setHistoricOperator(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;
            r.setHistoricEquityPartners(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;
            r.setHistoricEquityStakes(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("Refinery second");

            columnNo = 21;
            r.setHistoricEquityYear(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(21))));
            columnNo = 22;
            r.setEquitySource(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;
            r.setRefiningCapacity(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;
            r.setVduCapacity(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;
            r.setCokingCapacity(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;
            r.setFcc(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))));
            columnNo = 27;
            r.setHydroCrackingCapacity(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;
            r.setSourceCapacities(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;
            r.setCrudeStorageUnitOrTanksNo(
                    null == row.getCell(29) || null == df.formatCellValue(row.getCell(29))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(29))) ? 0
                                    : Double.valueOf(df.formatCellValue(row.getCell(29))));
            columnNo = 30;
            r.setCrudeStorageCapacity(null == row.getCell(30) || null == df.formatCellValue(row.getCell(30))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(30))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(30))));

            //            System.out.println("Refinery Three");

            columnNo = 31;
            r.setNelsonComplexityIndex(null == row.getCell(31) || null == df.formatCellValue(row.getCell(31))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(31))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(31))));
            columnNo = 32;
            r.setCrudeThroughput(null == row.getCell(32) || null == df.formatCellValue(row.getCell(32))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(32))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(32))));
            columnNo = 33;
            r.setCrudeType(null == row.getCell(33) || null == df.formatCellValue(row.getCell(33))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(33))) ? BLANK
                            : df.formatCellValue(row.getCell(33)));
            columnNo = 34;
            r.setApi(null == row.getCell(34) || null == df.formatCellValue(row.getCell(34))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(34))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(34))));
            columnNo = 35;
            r.setSulphur(null == row.getCell(35) || null == df.formatCellValue(row.getCell(35))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(35))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(35))));
            columnNo = 36;
            r.setSourceInput(null == row.getCell(36) || null == df.formatCellValue(row.getCell(36))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(36))) ? BLANK
                            : df.formatCellValue(row.getCell(36)));
            columnNo = 37;
            r.setVisbreakingCapacity(null == row.getCell(37) || null == df.formatCellValue(row.getCell(37))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(37))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(37))));
            columnNo = 38;
            r.setReformerCapacity(null == row.getCell(38) || null == df.formatCellValue(row.getCell(38))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(38))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(38))));
            columnNo = 39;
            r.setHydrotreatingCapacity(null == row.getCell(39) || null == df.formatCellValue(row.getCell(39))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(39))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(39))));
            columnNo = 40;
            r.setAlkylationCapacity(null == row.getCell(40) || null == df.formatCellValue(row.getCell(40))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(40))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(40))));

            //            System.out.println("Refinery Four");

            columnNo = 41;
            r.setAlkylationCapacityUnit(null == row.getCell(41) || null == df.formatCellValue(row.getCell(41))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(41))) ? BLANK
                            : df.formatCellValue(row.getCell(41)));
            columnNo = 42;
            r.setAromaticsCapacity(null == row.getCell(42) || null == df.formatCellValue(row.getCell(42))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(42))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(42))));
            columnNo = 43;
            r.setAromaticsCapacityUnit(null == row.getCell(43) || null == df.formatCellValue(row.getCell(43))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(43))) ? BLANK
                            : df.formatCellValue(row.getCell(43)));
            columnNo = 44;
            r.setIsomerizationCapacity(null == row.getCell(44) || null == df.formatCellValue(row.getCell(44))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(44))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(44))));
            columnNo = 45;
            r.setPolymerizationCapacity(null == row.getCell(45) || null == df.formatCellValue(row.getCell(45))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(45))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(45))));
            columnNo = 46;
            r.setPolymerizationCapacityUnit(
                    null == row.getCell(46) || null == df.formatCellValue(row.getCell(46))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(46))) ? BLANK
                                    : df.formatCellValue(row.getCell(46)));
            columnNo = 47;
            r.setLubesCapacity(null == row.getCell(47) || null == df.formatCellValue(row.getCell(47))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(47))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(47))));
            columnNo = 48;
            r.setLubesCapacityUnit(null == row.getCell(48) || null == df.formatCellValue(row.getCell(48))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(48))) ? BLANK
                            : df.formatCellValue(row.getCell(48)));
            columnNo = 49;
            r.setOxygenatesCapacity(null == row.getCell(49) || null == df.formatCellValue(row.getCell(49))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(49))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(49))));
            columnNo = 50;
            r.setOxygenatesCapacityUnit(null == row.getCell(50) || null == df.formatCellValue(row.getCell(50))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(50))) ? BLANK
                            : df.formatCellValue(row.getCell(50)));

            //            System.out.println("Refinery five");

            columnNo = 51;
            r.setCokeCapacity(null == row.getCell(51) || null == df.formatCellValue(row.getCell(51))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(51))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(51))));
            columnNo = 52;
            r.setCokeCapacityUnit(null == row.getCell(52) || null == df.formatCellValue(row.getCell(52))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(52))) ? BLANK
                            : df.formatCellValue(row.getCell(52)));
            columnNo = 53;
            r.setSulphurCapacity(null == row.getCell(53) || null == df.formatCellValue(row.getCell(53))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(53))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(53))));
            columnNo = 54;
            r.setSulphurCapacityUnit(null == row.getCell(54) || null == df.formatCellValue(row.getCell(54))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(54))) ? BLANK
                            : df.formatCellValue(row.getCell(54)));
            columnNo = 55;
            r.setHydrogenCapacity(null == row.getCell(55) || null == df.formatCellValue(row.getCell(55))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(55))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(55))));
            columnNo = 56;
            r.setHydrogenCapacityUnit(null == row.getCell(56) || null == df.formatCellValue(row.getCell(56))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(56))) ? BLANK
                            : df.formatCellValue(row.getCell(56)));
            columnNo = 57;
            r.setAsphaltCapacity(null == row.getCell(57) || null == df.formatCellValue(row.getCell(57))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(57))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(57))));
            columnNo = 58;
            r.setAsphaltCapacityUnit(null == row.getCell(58) || null == df.formatCellValue(row.getCell(58))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(58))) ? BLANK
                            : df.formatCellValue(row.getCell(58)));
            columnNo = 59;
            r.setOthersCapacity(null == row.getCell(59) || null == df.formatCellValue(row.getCell(59))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(59))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(59))));
            columnNo = 60;
            r.setRefineryProducts(null == row.getCell(60) || null == df.formatCellValue(row.getCell(60))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(60))) ? BLANK
                            : df.formatCellValue(row.getCell(60)));

            //            System.out.println("Refinery six");

            columnNo = 61;
            r.setSourceProducts(null == row.getCell(61) || null == df.formatCellValue(row.getCell(61))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(61))) ? BLANK
                            : df.formatCellValue(row.getCell(61)));
            columnNo = 62;
            r.setRefineryUtilization(null == row.getCell(62) || null == df.formatCellValue(row.getCell(62))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(62))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(62))));
            columnNo = 63;
            r.setGrm(null == row.getCell(63) || null == df.formatCellValue(row.getCell(63))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(63))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(63))));
            columnNo = 64;
            r.setCapex(null == row.getCell(64) || null == df.formatCellValue(row.getCell(64))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(64))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(64))));
            columnNo = 65;
            r.setCapexYear(null == row.getCell(65) || null == df.formatCellValue(row.getCell(65))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(65))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(65))));// This is also an int or text
            columnNo = 66;
            r.setCapexDetails(null == row.getCell(66) || null == df.formatCellValue(row.getCell(66))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(66))) ? BLANK
                            : df.formatCellValue(row.getCell(66)));
            columnNo = 67;
            r.setSourceCapex(null == row.getCell(67) || null == df.formatCellValue(row.getCell(67))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(67))) ? BLANK
                            : df.formatCellValue(row.getCell(67)));
            columnNo = 68;
            r.setConstructionCompanyName(null == row.getCell(68) || null == df.formatCellValue(row.getCell(68))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(68))) ? BLANK
                            : df.formatCellValue(row.getCell(68)));
            columnNo = 69;
            r.setConstructionContractDetails(
                    null == row.getCell(69) || null == df.formatCellValue(row.getCell(69))
                            || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(69))) ? BLANK
                                    : df.formatCellValue(row.getCell(69)));
            columnNo = 70;
            r.setRefineryOtherNames(null == row.getCell(70) || null == df.formatCellValue(row.getCell(70))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(70))) ? BLANK
                            : df.formatCellValue(row.getCell(70)));

            //            System.out.println("Refinery seven");

            columnNo = 71;
            r.setOtherSources(null == row.getCell(71) || null == df.formatCellValue(row.getCell(71))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(71))) ? BLANK
                            : df.formatCellValue(row.getCell(71)));
            columnNo = 72;
            r.setContact(null == row.getCell(72) || null == df.formatCellValue(row.getCell(72))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(72))) ? BLANK
                            : df.formatCellValue(row.getCell(72)));
            columnNo = 73;
            r.setGasolinePetrol(null == row.getCell(73) || null == df.formatCellValue(row.getCell(73))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(73))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(73))));
            columnNo = 74;
            r.setLpg(null == row.getCell(74) || null == df.formatCellValue(row.getCell(74))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(74))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(74))));
            columnNo = 75;
            r.setKerosine(null == row.getCell(75) || null == df.formatCellValue(row.getCell(75))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(75))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(75))));
            columnNo = 76;
            r.setJetFuel(null == row.getCell(76) || null == df.formatCellValue(row.getCell(76))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(76))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(76))));
            columnNo = 77;
            r.setDiesel(null == row.getCell(77) || null == df.formatCellValue(row.getCell(77))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(77))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(77))));
            columnNo = 78;
            r.setPropylene(null == row.getCell(78) || null == df.formatCellValue(row.getCell(78))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(78))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(78))));
            columnNo = 79;
            r.setLightNaphtha(null == row.getCell(79) || null == df.formatCellValue(row.getCell(79))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(79))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(79))));
            columnNo = 80;
            r.setHeavyNaphtha(null == row.getCell(80) || null == df.formatCellValue(row.getCell(80))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(80))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(80))));

            //            System.out.println("Refinery eight");

            columnNo = 81;
            r.setKerojet(null == row.getCell(81) || null == df.formatCellValue(row.getCell(81))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(81))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(81))));
            columnNo = 82;
            r.setBenzeneSaturationUnit(null == row.getCell(82) || null == df.formatCellValue(row.getCell(82))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(82))) ? BLANK
                            : df.formatCellValue(row.getCell(82)));
            columnNo = 83;
            r.setProductsSources(null == row.getCell(83) || null == df.formatCellValue(row.getCell(83))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(83))) ? BLANK
                            : df.formatCellValue(row.getCell(83)));
            columnNo = 84;
            r.setLicensors(null == row.getCell(84) || null == df.formatCellValue(row.getCell(84))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(84))) ? BLANK
                            : df.formatCellValue(row.getCell(84)));
            columnNo = 85;
            r.setMaintananceStartDate(null == row.getCell(85) || null == df.formatCellValue(row.getCell(85))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(85))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(85))));
            columnNo = 86;
            r.setMaintananceEndDate(null == row.getCell(86) || null == df.formatCellValue(row.getCell(86))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(86))) ? 0
                            : Integer.parseInt(df.formatCellValue(row.getCell(86))));
            columnNo = 87;
            r.setMaintananceNote(null == row.getCell(87) || null == df.formatCellValue(row.getCell(87))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(87))) ? BLANK
                            : df.formatCellValue(row.getCell(87)));
            columnNo = 88;
            r.setCduProducingCapacity(null == row.getCell(88) || null == df.formatCellValue(row.getCell(88))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(88))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(88))));
            refineryList.add(r);
        } catch (Exception e) {
            logger.error("Exception in ReadExcelFile - populateRefineryData():" + 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("Refinery totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }
    logger.info("Total objects set for refinery:" + refineryList.size());
    return refineryList;

}