Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex) 

Source Link

Document

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

Usage

From source file:javaapplication2.Frame1.java

void fillData(File file) {
    int index = -1;
    HSSFWorkbook workbook = null;//  w  w  w.java  2s.  c o  m
    try {
        try {
            FileInputStream inputStream = new FileInputStream(file);
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException ex) {
            Logger.getLogger(Frame1.class.getName()).log(Level.SEVERE, null, ex);
        }

        String[] strs = new String[workbook.getNumberOfSheets()];
        //get all sheet names from selected workbook
        for (int i = 0; i < strs.length; i++) {
            strs[i] = workbook.getSheetName(i);
        }
        JFrame frame = new JFrame("Input Dialog");

        String selectedsheet = (String) JOptionPane.showInputDialog(frame,
                "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null,
                strs, strs[0]);

        if (selectedsheet != null) {
            for (int i = 0; i < strs.length; i++) {
                if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
                    index = i;
            }
            HSSFSheet sheet = workbook.getSheetAt(index);
            HSSFRow row = sheet.getRow(0);

            headers.clear();
            //int value=row.getLastCellNum();
            for (int i = 0; i < row.getLastCellNum(); i++) {
                HSSFCell cell1 = row.getCell(i);
                headers.add(cell1.toString());

            }

            data.clear();
            for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                Vector d = new Vector();
                row = sheet.getRow(j);
                int noofrows = row.getLastCellNum();
                for (int i = 0; i < noofrows; i++) { //To handle empty excel cells 
                    HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
                    d.add(cell.toString());
                }
                d.add("\n");
                data.add(d);
            }
        } else {
            return;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public long[] getFieldsLen() {

    //        Integer dat = new Integer(7);
    int iCols = getColumns();
    long[] fieldsLen = new long[iCols];
    for (int i = 0; i < iCols; i++) {
        fieldsLen[i] = -1;/*www.  ja va2 s.  com*/
    }
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    for (int i = iFirstRow + 1; i <= iLastRow; i++) {
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        for (int j = iFirstCol; j < iLastCol; j++) {
            HSSFCell cell = row.getCell(j);
            int cellType = cell.getCellType();
            if (HSSFCell.CELL_TYPE_STRING == cellType) {
                long tmpLen = cell.getStringCellValue().length();
                if (fieldsLen[j - iFirstCol] < tmpLen) {
                    fieldsLen[j - iFirstCol] = tmpLen;
                }
            } else if (HSSFCell.CELL_TYPE_NUMERIC == cellType) {
                fieldsLen[j - iFirstCol] = -1;
            } else {

            }
        }
    }

    return fieldsLen;
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public boolean isDBFormat() {
    boolean ret = true;
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    List<Integer> fieldsType = getFieldsType();
    if (null == fieldsType) {
        ret = false;/*www  .  j  a  va2 s  . c om*/
        return ret;
    }
    for (int i = iFirstRow + 1; i <= iLastRow; i++) {
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        for (int j = iFirstCol; j < iLastCol; j++) {
            HSSFCell cell = row.getCell(j);
            //                String cessStr = cell.toString();
            int cellType = cell.getCellType();
            //                if(HSSFCell.CELL_TYPE_BLANK == cellType
            //                        || HSSFCell.CELL_TYPE_ERROR == cellType){
            Integer colType = fieldsType.get(j);
            if (colType.intValue() != cellType) {
                ret = false;
                break;
            }
        }

        if (false == ret) {
            break;
        }
    }

    return ret;
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public int getColumns() {
    int ret = 0;/*w  ww  .j  av a  2 s .  co  m*/
    HSSFSheet activeSheet = wb.getSheetAt(0);
    HSSFRow row2 = activeSheet.getRow(1);//second row
    ret = row2.getPhysicalNumberOfCells();
    //        CellRangeAddress cra = activeSheet.getMergedRegion(0);
    //        ret = cra.getLastColumn() - cra.getFirstColumn() + 1;

    return ret;
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

private List<Integer> getFieldsType() {
    List<Integer> fieldsType = null;

    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    HSSFRow row = activeSheet.getRow(iFirstRow + 1);
    int iFirstCol = row.getFirstCellNum();
    int iLastCol = row.getLastCellNum();
    int iCols = row.getPhysicalNumberOfCells();
    if (0 != iCols) {
        fieldsType = new ArrayList<>();
    }/*w ww.  j  av a 2 s.  co m*/
    for (int j = iFirstCol; j < iLastCol; j++) {
        HSSFCell cell = row.getCell(j);
        int cellType = cell.getCellType();
        fieldsType.add(cellType);
    }

    return fieldsType;
    //        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public void insertDataFromExcel() {
    String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'h111', 'h222');";
    //        String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES ('2', 'h111', 'h222');";
    String sqlHead = "";
    sqlHead += "INSERT INTO `" + fields.get(0) + "` (`id`,";
    int i = 0;/*from w ww. j  ava  2  s .  c  om*/
    for (i = 0; i < fields.size() - 2; i++) {
        sqlHead += " `" + fields.get(i + 1) + "`,";
    }
    sqlHead += " `" + fields.get(i + 1) + "`) VALUES (NULL,";

    PreparedStatement pst = null;
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    for (i = iFirstRow + 1; i <= iLastRow; i++) {
        String sql = sqlHead;
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        int j = 0;
        for (j = iFirstCol; j < iLastCol - 1; j++) {
            HSSFCell cell = row.getCell(j);
            String cessStr = cell.toString();
            sql += " '" + cessStr + "',";
        }
        HSSFCell cell = row.getCell(j);
        String cessStr = cell.toString();
        sql += " '" + cessStr + "');";
        try {
            pst = mysqlConn.prepareStatement(sql);
            pst.execute();
        } catch (SQLException ex) {
            Logger.getLogger(JavaExecelProcess.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("insert data exception.");
        }
    }
    //        sql += "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'f1111', 'f2222');";

}

---

From source file:jp.dbcls.bp3d.kaorif.visiblebody.nerves.VisibleBodyNerves.java

License:Open Source License

/**
 * ???(manuallyMapped.txt)?/*w ww  .  j a v a2 s  .  c  om*/
 * @throws Exception
 */
public void readManuallyMapped() throws Exception {
    POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.MANUALLY_MAPPED));
    HSSFWorkbook wb = new HSSFWorkbook(filein);
    HSSFSheet sheet = wb.getSheet("manuallyMapped");

    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        HSSFRow row = sheet.getRow(i);

        HSSFCell cell = null;

        String original = "";
        cell = row.getCell(0);
        if (cell != null) {
            original = cell.getRichStringCellValue().toString().trim();
        }

        String av = "";
        cell = row.getCell(1);
        if (cell != null) {
            av = cell.getRichStringCellValue().toString().trim();
        }

        String renamed = "";
        cell = row.getCell(2);
        if (cell != null) {
            renamed = cell.getRichStringCellValue().toString().trim();
        }

        String remark = "";
        for (int j = 3; j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell != null) {
                remark += cell.getRichStringCellValue().toString().trim() + "\t";
            }
        }

        VisibleBodyManuallyMapped mm = new VisibleBodyManuallyMapped();
        mm.setOriginal(original);
        mm.setAv(av);
        mm.setRenamed(renamed);
        mm.setRemark(remark);

        this.manuallyMapped.add(mm);

    }
}

---

From source file:jp.dbcls.bp3d.kaorif.visiblebody.vessels.VisibleBodyVessels.java

License:Open Source License

/**
 * this.ORDERED_BY_KAORIF???//from w  w w.j a  va 2s.co m
 * 
 * @throws Exception
 */
public void readOrderedByKaorif() throws Exception {
    POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.ORDERED_BY_KAORIF));
    HSSFWorkbook wb = new HSSFWorkbook(filein);
    HSSFSheet sheet = wb.getSheet("CirculatorySystem");

    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        HSSFRow row = sheet.getRow(i);

        boolean isRequired = row.getCell(0).getBooleanCellValue();

        String en = "";
        HSSFCell cell = row.getCell(1);
        if (cell != null) {
            en = cell.getRichStringCellValue().toString().trim();
        }

        String av = "";
        cell = row.getCell(2);
        if (cell != null) {
            av = cell.getRichStringCellValue().toString().trim();
        }

        VisibleBodyEntry vbe = getEntry(en, av);
        if (vbe != null) {
            vbe.setOrderedByKaorif(isRequired);
        } else {
            System.out.println("readOrderedByKaorif not found=" + en + " " + av);
        }
    }

}

---

From source file:jp.dbcls.bp3d.ta.bits.TABits.java

License:Open Source License

/**
 * o101_TAJwFMA.xls?/*  w  w w  .ja  va  2  s .c  o  m*/
 * @throws Exception
 */
public void readXls() throws Exception {
    POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(INFILE));
    HSSFWorkbook wb = new HSSFWorkbook(filein);
    HSSFSheet sheet = wb.getSheet(SHEET);

    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        HSSFRow row = sheet.getRow(i);

        int j = 0;
        String edit = "";
        if (row.getCell(j) != null) {
            edit = row.getCell(j).getRichStringCellValue().toString().trim();
        }
        j++;

        /** edit=DELETE????? **/
        if (edit.equals(TABitsEntry.DELETE)) {
            continue;
        }

        String taId = row.getCell(j++).getRichStringCellValue().toString().trim();

        double taTab = 0.0;
        int cellType = row.getCell(j).getCellType();
        if (cellType == Cell.CELL_TYPE_NUMERIC) {
            taTab = row.getCell(j++).getNumericCellValue();
        } else if (cellType == Cell.CELL_TYPE_STRING) {
            taTab = Double.parseDouble(
                    row.getCell(j++).getRichStringCellValue().toString().trim().replaceAll(">", ""));
        } else {
            j++;
            System.out.println("[Error]@readXls.TA:Unknown CellType" + cellType);
        }

        String taKanji = row.getCell(j++).getRichStringCellValue().toString().trim();
        String taEn = row.getCell(j++).getRichStringCellValue().toString().trim().replace("[*]", "");

        String taKana = "";
        if (japaneseDisambiguatedForm2kana.containsKey(taKanji)) {
            taKana = japaneseDisambiguatedForm2kana.get(taKanji);
        } else if (japaneseEquivalentForm2kana.containsKey(taKanji)) {
            taKana = japaneseEquivalentForm2kana.get(taKanji);
        } else if (en2kana.containsKey(taEn)) {
            taKana = en2kana.get(taEn);
        }

        List<String> fmaIds = Arrays.asList(
                row.getCell(j++).getRichStringCellValue().toString().replaceAll(":", "").trim().split("[|]"));
        String fmaOBOName = row.getCell(j++).getRichStringCellValue().toString().trim().replaceAll(":", "");

        TABitsEntry template = createTAEntry();
        template.setTaId(taId);
        template.setTaTab(taTab);
        template.setTaEn(taEn);
        template.setTaKanji(taKanji);
        template.setTaKana(taKana);

        if (fmaOBOName.contains("NONE")) { // FMAOBONAME="NONE"????TA?fmaobo2??                  
            Set<FMAOBOEntry> hits = new HashSet<FMAOBOEntry>();
            for (String en : Arrays.asList(taEn.split("[;]"))) {
                en = en.replaceAll("[*]", "").trim();
                if (fmaobo.contains(en)) { //TA?fmaobo?????
                    hits.add(fmaobo.getByName(en));
                }
            }
            if (hits.size() == 0) { // FMA?????
                TABitsEntry ent = (TABitsEntry) template.clone();
                ent.setClassification(TABitsEntry.NOFMA);
                entries.add(ent);
            } else {
                for (FMAOBOEntry fmaEnt : hits) {
                    TABitsEntry ent = (TABitsEntry) template.clone();
                    ent.setFma(fmaEnt);
                    if (fmaIds.contains(fmaEnt.getId())) {
                        ent.setClassification(TABitsEntry.IDENTICAL); // FMA????????
                    } else {
                        ent.setClassification(TABitsEntry.NOTIDENTICAL); // FMA???????
                    }
                    entries.add(ent);
                }
            }
        } else { // FMAOBONAME="NONE"??????????
            for (String fmaId : fmaIds) {
                TABitsEntry ent = (TABitsEntry) template.clone();
                ent.setClassification(TABitsEntry.ORIGINAL);
                if (fmaobo.contains(fmaId)) {
                    ent.setFma(fmaobo.getById(fmaId));
                    ent.setClassification(TABitsEntry.ORIGINAL);
                } else {
                    ent.setClassification(TABitsEntry.NOFMAOBO2);
                    System.out.println("[Warning]@TABits.readXLs:" + fmaId + ":" + ent.getTaEn()
                            + " is not found in fmaobo2");
                }

                if (!edit.isEmpty()) {
                    ent.setEdit(edit);
                }

                entries.add(ent);
            }
        }
    }
}

---

From source file:kr.co.blackducksoftware.rg.displayexcel.Style.java

public static void finalizeWorksheet(HSSFSheet currentWorkSheet) {
    HSSFRow currentRow = currentWorkSheet.getRow(0);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »