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

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

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

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:com.itrus.ca.common.utils.excel.ExportExcel.java

License:Open Source License

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

From source file:com.jkoolcloud.tnt4j.streams.parsers.ActivityExcelRowParser.java

License:Apache License

/**
 * Gets field raw data value resolved by locator.
 *
 * @param locator//  www .ja v  a2 s  . c  om
 *            activity field locator
 * @param cData
 *            MS Excel document row representing activity object data fields
 * @param formattingNeeded
 *            flag to set if value formatting is not needed
 * @return raw value resolved by locator, or {@code null} if value is not resolved
 *
 * @throws ParseException
 *             if exception occurs while resolving raw data value
 */
@Override
protected Object resolveLocatorValue(ActivityFieldLocator locator, ActivityContext cData,
        AtomicBoolean formattingNeeded) throws ParseException {
    Object val = null;
    String locStr = locator.getLocator();
    Row row = cData.getData();

    if (StringUtils.isNotEmpty(locStr)) {
        int cellIndex = CellReference.convertColStringToIndex(locStr);
        if (cellIndex < 0) {
            throw new ParseException(
                    StreamsResources.getStringFormatted(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME,
                            "ActivityExcelRowParser.unresolved.cell.reference", locStr),
                    row.getRowNum());
        }
        Cell cell = row.getCell(cellIndex);
        boolean cellFound = false;
        if (cell != null) {
            val = getCellValue(cell);
            cellFound = true;
        }

        logger().log(OpLevel.TRACE,
                StreamsResources.getString(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME,
                        "ActivityExcelRowParser.resolved.cell.value"),
                locStr, row.getSheet().getWorkbook().getMissingCellPolicy(), toString(val));
    }

    return val;
}

From source file:com.ksa.myanmarlottery.service.parser.ExcelFileParser.java

@Override
public List<Result> getResult(InputStream in) throws FileNotFoundException, IOException, ParseException {
    List<Prize> prizes = null;
    List<Result> resultList = new ArrayList<>();
    SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); // 01-May-2017
    try {//  www. j  a  v a  2 s  .  co  m
        Workbook workbook = new XSSFWorkbook(in);
        Sheet datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Cell cell0 = currentRow.getCell(0); // get first cell.

            if (cell0.getCellTypeEnum() == CellType.NUMERIC) {
                int numberic = (int) cell0.getNumericCellValue();
                log.info("Numberic - " + numberic);

                // check lottery type
                if (ConstantUtil.OLD_LOTTERY_TYPE == numberic || ConstantUtil.NEW_LOTTERY_TYPE == numberic) { // for lottery type result
                    Result result = new Result();
                    result.setType(numberic);
                    result.setNumberOfTimes((int) currentRow.getCell(1).getNumericCellValue());
                    //                        result.setResultFor(format.parse(currentRow.getCell(2).toString()));
                    result.setResultFor(currentRow.getCell(2).getDateCellValue());
                    result.setDataProvider(currentRow.getCell(3).getStringCellValue());
                    result.setCompanyName(currentRow.getCell(4).getStringCellValue());

                    prizes = new ArrayList<>();
                    result.setPrizes(prizes);
                    resultList.add(result);
                }

            } else if (cell0.getCellTypeEnum() == CellType.STRING) { // result data
                String character = cell0.getStringCellValue();
                log.info("character - " + character);

                // check validation for character.
                String value = charMap.get(character);
                if (value == null) {
                    throw new ParseException(
                            "Character is Not valid at Row: " + currentRow.getRowNum() + " > column:" + 0, 400);
                }
                Cell cell1 = currentRow.getCell(1);
                if (cell1.getCellTypeEnum() != CellType.NUMERIC) {
                    throw new ParseException(
                            "Should be Number at Row: " + currentRow.getRowNum() + " > column:" + 1, 400);
                }
                log.info("Cell Type " + cell1.getCellTypeEnum());
                int code = (int) cell1.getNumericCellValue();
                log.info("code - " + code + " Row:" + currentRow.getRowNum() + " > column:" + 1);
                String prizeTitle = currentRow.getCell(2).getStringCellValue();
                log.info("prizeTitle - " + prizeTitle);
                String prizeDesc = currentRow.getCell(4).getStringCellValue();
                log.info("prizeDesc - " + prizeDesc);
                prizes.add(new Prize(character, code, prizeTitle, prizeDesc));
            }
        }
        log.info("resultList size: " + resultList.size());
        for (Result r : resultList) {
            log.info("prizeList size: " + r.getPrizes().size());
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
        throw e;
    } catch (IOException e) {
        e.printStackTrace();
        throw e;
    }
    return resultList;
}

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

License:Open Source License

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

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

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

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

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

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

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

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

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

From source file:com.lapis.jsfexporter.excel.ExcelExportType.java

License:Apache License

@Override
public Row exportRow(IExportRow row) {
    Row xlsRow = sheet.createRow(rowCount++);
    int cellIndex = 0;
    for (IExportCell cell : row.getCells()) {
        boolean cellIsUsed;
        do {//  w  ww. j  a  va  2 s  . c o  m
            cellIsUsed = false;
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress region = sheet.getMergedRegion(i);
                if (region.isInRange(xlsRow.getRowNum(), cellIndex)) {
                    cellIsUsed = true;
                    cellIndex += region.getLastColumn() - region.getFirstColumn() + 1;
                }
            }
        } while (cellIsUsed);

        Cell xlsCell = xlsRow.createCell(cellIndex++);
        xlsCell.setCellValue(cell.getValue());

        if (cell.getColumnSpanCount() > 1 || cell.getRowSpanCount() > 1) {
            sheet.addMergedRegion(new CellRangeAddress(xlsCell.getRowIndex(),
                    xlsCell.getRowIndex() + cell.getRowSpanCount() - 1, xlsCell.getColumnIndex(),
                    xlsCell.getColumnIndex() + cell.getColumnSpanCount() - 1));
            cellIndex += cell.getColumnSpanCount() - 1;
        }
    }
    return xlsRow;
}

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

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

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

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

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

        }
    }
    if (totalRecords > 0) {

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

}

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

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

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

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

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

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

        }
    }
    if (totalRecords > 0) {

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

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

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

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

        }

    }
    if (totalRecords > 0) {

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

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

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

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

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

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

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

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

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

        }

    }
    if (totalRecords > 0) {

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

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

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

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer(BLANK);
    Set<String> columnNamesSet = new HashSet<String>();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        NaturalGas n = new NaturalGas();
        try {//ww w .j  av  a2  s. c om
            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;

}