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

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

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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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

private void jsXingzhengchufa(Hcrw hcrw, Sheet sheet) throws Exception {
    try {/*from www. j  a  va2  s  . c  o  m*/
        jsXzcfMapper.deleteByTaskId2(hcrw.getId());
        // 
        int rowNum = sheet.getLastRowNum();
        for (int i = 6; i < rowNum; i++) {
            if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim()
                    && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) {
                JsXzcf jsXzcf = new JsXzcf();
                jsXzcf.setId("");
                jsXzcf.setXydm(hcrw.getHcdwXydm());
                jsXzcf.setXzcfjdswh(POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)));
                jsXzcf.setWflx(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3)));
                jsXzcf.setXzcfnr(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4)));
                jsXzcf.setCfjg(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5)));
                jsXzcf.setCfrq(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(6))));
                jsXzcf.setBz(null);
                jsXzcf.setGssj(null);
                jsXzcfMapper.insert2(jsXzcf);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        MongoLogger.warn("?? ?? ? ",
                ExceptionUtils.getStackTrace(e), hcrw.getId());
        throw new RuntimeException("[??](?)??;");
    }
}

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

private void jsXingzhengxuke(Hcrw hcrw, Sheet sheet) throws Exception {
    try {//from   ww w .  ja  va  2  s . co m
        jsLicenseMapper.deleteByTaskId2(hcrw.getId());
        // 
        int rowNum = sheet.getLastRowNum();
        for (int i = 5; i < rowNum; i++) {
            if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim()
                    && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) {
                JsLicense jsXjsLicensecf = new JsLicense();
                jsXjsLicensecf.setId("");
                jsXjsLicensecf.setXydm(hcrw.getHcdwXydm());
                jsXjsLicensecf.setXkwjbh(POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)));
                jsXjsLicensecf.setXkwjmc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3)));
                jsXjsLicensecf.setYxqKs(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4))));
                jsXjsLicensecf.setYxqJs(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5))));
                jsXjsLicensecf.setXkjg(POIUtils.getStringCellValue(sheet.getRow(i).getCell(6)));
                jsXjsLicensecf.setXknr("");
                jsXjsLicensecf.setZt(POIUtils.getStringCellValue(sheet.getRow(i).getCell(7)));
                jsXjsLicensecf.setXq(null);
                jsXjsLicensecf.setHdrq(null);
                jsXjsLicensecf.setGssj(null);
                jsLicenseMapper.insert2(jsXjsLicensecf);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        MongoLogger.warn("?? ?????? ? ",
                ExceptionUtils.getStackTrace(e), hcrw.getId());
        throw new RuntimeException(
                "[??????](?)??;");
    }
}

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

private void jsZhishichanquan(Hcrw hcrw, Sheet sheet) throws Exception {
    try {//w ww. j av  a 2s. com
        jsZscqMapper.deleteByTaskId2(hcrw.getId());
        // 
        int rowNum = sheet.getLastRowNum();
        for (int i = 5; i < rowNum; i++) {
            if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim()
                    && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) {
                JsZscq jsZscq = new JsZscq();
                jsZscq.setId("");
                jsZscq.setXydm(hcrw.getHcdwXydm());
                jsZscq.setQymc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3)));
                jsZscq.setZl(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4)));
                jsZscq.setCzrmc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5)));
                jsZscq.setZqrmc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(6)));
                jsZscq.setZqdjrq(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(7))));
                jsZscq.setZt(POIUtils.getStringCellValue(sheet.getRow(i).getCell(8)));
                jsZscq.setBhqk(POIUtils.getStringCellValue(sheet.getRow(i).getCell(9)));
                jsZscq.setGssj(null);
                jsZscqMapper.insert2(jsZscq);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        MongoLogger.warn("?? ?? ? ",
                ExceptionUtils.getStackTrace(e), hcrw.getId());
        throw new RuntimeException("[??](?)??;");
    }
}

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

private BigDecimal getSumValue(Sheet sheet, int columnId, int startRow, int endRow) {
    BigDecimal result = new BigDecimal("0");
    for (int i = startRow; i <= endRow; i++) {
        result = result.add(parseBigDecimal(POIUtils.getCellFormatValue(sheet.getRow(i).getCell(columnId))));
    }/*from  w  w  w  . j  a v  a2  s  .  co  m*/
    return result;
}

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

private void validateExcel(String hcrwId, Workbook workbook) throws Exception {
    Sheet sheetZCFZB = workbook.getSheet("");
    Sheet sheetLRB = workbook.getSheet("");
    String errorMsg = "";

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4))).compareTo(new BigDecimal("0"))==0
        && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))).compareTo(new BigDecimal("0"))==0
        && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9))).compareTo(new BigDecimal("0"))==0
        && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10))).compareTo(new BigDecimal("0"))==0){
    errorMsg=errorMsg+"[E49 F49 J49 K49?0] ";
    }else{// ww  w.j  av a2 s. co  m
    }*/

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9)))) < 0.00000001) {

    } else {
        errorMsg = errorMsg
                + "?E49??(?)?J49";
    }

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10)))) < 0.00000001) {

    } else {
        errorMsg = errorMsg
                + "F49??(?)K49";
    }

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(3)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))) < 0.00000001
            || parseBigDecimal(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(3)))
                    .compareTo(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))
                            .subtract(parseBigDecimal(
                                    POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9))))) == 0) {

    } else {
        errorMsg = errorMsg
                + "???D41??K47";//  ???D41?K47-??J47]
    }

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(4)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9)))) < 0.00000001) {

    } else {
        errorMsg = errorMsg
                + "???E41???J47";
    }

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(4))).compareTo(getSumValue(sheetZCFZB,4,7,19))==0){
            
    }else{
    errorMsg=errorMsg+"E8-E20??E21";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(5))).compareTo(getSumValue(sheetZCFZB,5,7,19))==0){
            
    }else{
    errorMsg=errorMsg+"[F8-F20??F21] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(4))).compareTo(getSumValue(sheetZCFZB,4,22,38))==0){
            
    }else{
    errorMsg=errorMsg+"[E23-E39??E40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(5))).compareTo(getSumValue(sheetZCFZB,5,22,38))==0){
            
    }else{
    errorMsg=errorMsg+"[F23-F39??F40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(4))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(4)))))==0){
            
    }else{
    errorMsg=errorMsg+"[E49?E21+E40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(5))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(5)))))==0){
            
    }else{
    errorMsg=errorMsg+"[F49?F21+F40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(9))).compareTo(getSumValue(sheetZCFZB,9,7,20))==0){
            
    }else{
    errorMsg=errorMsg+"[J8-J21??J22] ";
    }*/

    /* if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(10))).compareTo(getSumValue(sheetZCFZB,10,7,20))==0){
            
     }else{
    errorMsg=errorMsg+"[k8-k21??k22] ";
     }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(9))).compareTo(getSumValue(sheetZCFZB,9,27,33).add(getSumValue(sheetZCFZB,9,23,24)))==0){
            
    }else{
    errorMsg=errorMsg+"[J24-J25J28-J34?J35] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(10))).compareTo(getSumValue(sheetZCFZB,10,27,33).add(getSumValue(sheetZCFZB,10,23,24)))==0){
            
    }else{
    errorMsg=errorMsg+"[K24-K25K28-K34?K35] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(35).getCell(9))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(9))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(9)))))==0){
            
    }else{
    errorMsg=errorMsg+"[J22+J35?J36] ";
    }*/

    /* if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(35).getCell(10))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(10))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(10)))))==0){
            
     }else{
    errorMsg=errorMsg+"[K22+K35?K36] ";
     }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(47).getCell(9))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(37).getCell(9))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(38).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(41).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(43).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(44).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(45).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9)))))==0){
    }else{
    errorMsg=errorMsg+"[J38+J39+J42+J44+J45+J46+J47?J48] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(47).getCell(10))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(37).getCell(10))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(38).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(41).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(43).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(44).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(45).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))))==0){
    }else{
    errorMsg=errorMsg+"[K38+K39+K42+K44+K45+K46+K47?K48] ";
    }*/

    /*if(Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4)))-parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9))))<0.00000001){
            
    }else{
    errorMsg=errorMsg+"[?E49??J49] ";
    }*/

    /*if(Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5)))-parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10))))<0.00000001){
            
    }else{
    errorMsg=errorMsg+"[F49?K49] ";
    }*/

    /*if(!errorMsg.equals("")){
    throw new RuntimeException(errorMsg);
    }*/
    if (null != errorMsg && !errorMsg.equals("")) {
        hcrwMapper.updateZcbResultByPrimaryKey(hcrwId,
                "      ??" + errorMsg);
    } else {
        hcrwMapper.updateZcbResultByPrimaryKey(hcrwId, "");
    }
}

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

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

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

License:Open Source License

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

    createDDMFormFields(ddmForm);//from   w w  w.j a  va 2s.c  om

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

    createDDMFormFieldValues(ddmFormValues);

    DDLRecordSetTestHelper recordSetTestHelper = new DDLRecordSetTestHelper(_group);

    DDLRecordSet recordSet = recordSetTestHelper.addRecordSet(ddmForm);

    DDLRecordTestHelper recordTestHelper = new DDLRecordTestHelper(_group, recordSet);

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

    DDLRecordVersion recordVersion = record.getRecordVersion();

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

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

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

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(0);

        Cell cell = null;

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

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

        row = sheet.getRow(1);

        cell = row.getCell(0);

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

        cell = row.getCell(1);

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

        cell = row.getCell(2);

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

        cell = row.getCell(3);

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

        cell = row.getCell(4);

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

        cell = row.getCell(5);

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

        cell = row.getCell(6);

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

        cell = row.getCell(7);

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

        cell = row.getCell(8);

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

        cell = row.getCell(9);

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

        cell = row.getCell(10);

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

        cell = row.getCell(11);

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

        cell = row.getCell(12);

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

        cell = row.getCell(13);

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

        cell = row.getCell(14);

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

        cell = row.getCell(15);

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

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

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

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

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer();
    Set<String> columnNamesSet = new HashSet<String>();
    //      DataFormatter df=new DataFormatter();
    int linebreak = 1;
    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        SmallScaleLng smallScaleLng = new SmallScaleLng();
        try {//from   w  w w .java2  s . com
            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   ww w .  j av  a 2s.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.  ja  va  2  s.  com*/
            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;
}