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:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* @param colIndex - Index of col 0,1,2 etc.
* 
*///from ww  w. ja  v a  2 s . com
private String readExcelCol(int sheetIndex, int rowIndex, int colIndex) {
    String cellContents = "";
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        cellContents = cell.getStringCellValue();

    } catch (IOException e) {
        // TODO Auto-generated catch block
        WmLog.printMessage("ERROR in reading =" + this.fileName + "Excel Sheet Index=" + sheetIndex
                + " Excel Row Index=" + rowIndex + "Excel Col Index=" + colIndex);
        e.printStackTrace();
        return null;
    }

    return (cellContents);

}

---

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* 
*//*w w w  . ja  v a  2  s  . c o m*/
private List<String> readExcelRow(int sheetIndex, int rowIndex) {
    String cellContents = "";
    ArrayList<String> rowVal = new ArrayList<String>();
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        Iterator<Cell> colIt = row.cellIterator();
        while (colIt.hasNext()) {
            Cell cell = colIt.next();
            cellContents = cell.getStringCellValue();
            rowVal.add(cellContents);
        }

    } catch (IOException e) {
        WmLog.printMessage("ERROR in reading Excel File=" + this.fileName + " Sheet Index=" + sheetIndex
                + " Excel Row Index=" + rowIndex + " " + e.getMessage());
        // TODO Auto-generated catch block
        e.printStackTrace();
        return null;
    }

    return (rowVal);

}

---

From source file:com.thingtrack.xbom.parser.XBomParserApplication.java

License:Apache License

private void parseXbom(InputStream file) throws FileNotFoundException, IOException, XbomParsingException {

    //Clear previous importations
    assemblingPartNodes.clear();//from  w  w  w  .  ja v  a2 s  . c om

    // Load XLS file
    POIFSFileSystem fs = new POIFSFileSystem(file);
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheet(XBOM_SHEET_CAPTION);

    // Parsing assembling parts
    for (int i = 11; i < sheet.getLastRowNum(); i++) {

        HSSFRow row = (HSSFRow) sheet.getRow(i);

        try {
            assemblingPartNodes.add(getAssemblingPart(row));
        }
        //The assembling part parsing has ended
        catch (NoAssemblingPartException e) {
            break;
        }
    }

    if (assemblingPartNodes.size() > 2) {
        //Build tree relationship
        buildTreeRelationship(assemblingPartNodes.get(0),
                assemblingPartNodes.subList(1, assemblingPartNodes.size()));
        loadTreeTable(assemblingPartNodes.get(0));
    }

    assemblingPartNodes.size();
}

---

From source file:com.util.tools.FeraExporter.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);/*from  w  w w .  j  a v a 2 s . c  om*/
    }
}

---

From source file:com.verticon.treatment.poi.handlers.EventImportHandler.java

License:Open Source License

private int processWorkSheet(Object o, Program program, EditingDomain ed, IProgressMonitor monitor)
        throws Exception {
    File f = convert(o);/*  w w  w.  j  av  a2  s  .  com*/
    HSSFSheet ws = getWorkSheet(f);
    ExecutableProcreator procreator;
    if (PoiUtils.isWorkSheetMatch(PoiUtils.TESTLOG_HEADER, ws)) {
        procreator = ExecutableProcreatorFactory.newTestEventProcreator();
        System.out.printf("Processing Test Event Log with %s rows%n", ws.getLastRowNum());

    } else {
        procreator = ExecutableProcreatorFactory.newTreatmentEventProcreator();
        System.out.printf("Processing Treatment Event Log with %s rows%n", ws.getLastRowNum());
    }
    // Exception ex = null;
    int count = 0;

    try {
        int rowsInSheet = ws.getPhysicalNumberOfRows();
        for (int i = 1; i < rowsInSheet; i++) {
            System.out.printf("Processing row %s in spreadsheet with %s rows.%n", i + 1, rowsInSheet);

            monitor.worked(1);
            procreator.prepare(program, ws.getRow(i), ed);

            count++;
        }
        procreator.execute(ed);
        message.append(procreator.getStatus());
    } catch (Exception e) {
        ex = e;
    } finally {
        procreator.dispose();
    }
    if (ex != null) {
        throw ex;
    }

    return count;
}

---

From source file:com.verticon.treatment.poi.handlers.NamingImportHandler.java

License:Open Source License

private int processWorkSheet(Object o, Map<String, String> nameMap, IProgressMonitor monitor) throws Exception {
    File f = convert(o);/* w w w  .  java  2  s  . co m*/
    HSSFSheet ws = getWorkSheet(f);

    // Exception ex = null;
    int count = 0;

    try {
        for (int i = 1; i < ws.getLastRowNum() + 1; i++) {
            System.out.printf("Processing row %s%n", i);

            monitor.worked(1);

            String account = getStringValue(ws.getRow(i), PoiUtils.ACCOUNT_COL);

            if (account == null || account.length() == 0) {
                break;
            } else {
                String fName = getStringValue(ws.getRow(i), PoiUtils.FNAME_COL);
                if (fName == null || fName.length() == 0) {
                    throw new Exception("Failed to find first Name in Row " + i);
                }
                String lName = getStringValue(ws.getRow(i), PoiUtils.LNAME_COL);
                if (lName == null || lName.length() == 0) {
                    throw new Exception("Failed to find last Name in Row " + i);
                }
                StringBuilder builder = new StringBuilder(fName).append(' ').append(lName);
                System.out.printf("Adding row %s account=%s name=%s %n", i, account, builder.toString());
                nameMap.put(account, builder.toString());
            }

            count++;
        }

    } catch (Exception e) {
        e.printStackTrace();
        ex = e;
    }
    if (ex != null) {
        throw ex;
    }

    return count;
}

---

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

static boolean isWorkSheetMatch(String[] header, HSSFSheet workSheet) {
    int rowNum = workSheet.getFirstRowNum();
    Row row = workSheet.getRow(rowNum);
    for (int i = 0; i < header.length; i++) {
        Cell cell = row.getCell(i);/* www.  j  a  v  a 2 s . c o m*/
        // Must be a String
        if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
            System.out.println(
                    "Header Column Type Error: " + cell.getCellType() + " != " + Cell.CELL_TYPE_STRING);
            return false;
        }
        if (!header[i].equalsIgnoreCase(cell.getRichStringCellValue().getString())) {
            // System.out.println("Header Column Name Error: " + header[i]
            // + " != " + cell.getRichStringCellValue().getString());
            return false;
        }

    }
    return true;
}

---

From source file:com.viettel.vsaadmin.database.DAO.UsersDAO.java

License:Open Source License

public String importStaffFromExcel() throws Exception {
    List customInfo = new ArrayList();//lst users
    Long attachId = Long.parseLong(getRequest().getParameter("attachId"));//get attactId
    VoAttachs att = (VoAttachs) getSession().get("com.viettel.voffice.database.BO.VoAttachs", attachId);//Attachs BO
    if (att == null) {// if att null return error users
        customInfo.add("error");
    } else {//from   ww w .ja  v  a2s . c o  m

        ResourceBundle rb = ResourceBundle.getBundle("config");//get link tuong doi
        String dir = rb.getString("directory");
        String linkFile = att.getAttachPath();
        linkFile = dir + linkFile;
        InputStream myxls = new FileInputStream(linkFile);//get file excel
        //Get the workbook instance for XLS file
        HSSFWorkbook wb = new HSSFWorkbook(myxls);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        //            HSSFRow firstRow = sheet.getRow(1);
        int rowNums = sheet.getLastRowNum();
        //            UsersDAOHE sdhe = new UsersDAOHE();
        SimpleDateFormat formatter = new SimpleDateFormat("dd/mm/yyyy");
        String userError = "";

        for (int i = 1; i <= rowNums; i++) {
            try {
                row = sheet.getRow(i);
                if (row != null) {
                    Users entity = new Users();
                    HSSFCell cellUserName = row.getCell((short) 0);
                    HSSFCell cellFullName = row.getCell((short) 1);
                    HSSFCell cellEmail = row.getCell((short) 2);
                    HSSFCell cellCellPhone = row.getCell((short) 3);
                    HSSFCell cellDeptName = row.getCell((short) 4);
                    HSSFCell cellBusiness = row.getCell((short) 5);
                    HSSFCell cellPosition = row.getCell((short) 6);
                    HSSFCell cellGender = row.getCell((short) 7);
                    HSSFCell cellTelephone = row.getCell((short) 8);
                    HSSFCell cellFax = row.getCell((short) 9);
                    HSSFCell cellDateOfBirth = row.getCell((short) 10);
                    HSSFCell cellBirthPlace = row.getCell((short) 11);
                    HSSFCell cellStaffCode = row.getCell((short) 12);
                    HSSFCell cellIdentityCard = row.getCell((short) 13);
                    HSSFCell cellIssueDateIdent = row.getCell((short) 14);
                    HSSFCell cellIssuePlaceIdent = row.getCell((short) 15);
                    HSSFCell cellDescription = row.getCell((short) 16);
                    //validate input
                    if (cellUserName != null) {
                        entity.setUserName(cellUserName.toString());
                    } else {
                        userError += i + " li Ti khon,";
                        customInfo.add(userError);
                    }

                    if (cellFullName != null) {
                        entity.setFullName(cellFullName.toString());
                    } else {
                        userError += i + " li Tn y ,";
                        customInfo.add(userError);
                    }

                    if (cellEmail.toString() != null && cellEmail.toString().length() > 0) {
                        entity.setEmail(cellEmail.toString());
                    }

                    if (cellCellPhone.toString() != null && cellCellPhone.toString().length() > 0) {
                        entity.setCellphone(cellCellPhone.toString());
                    }
                    //get dept
                    DepartmentDAOHE deptdhe = new DepartmentDAOHE();
                    Department deptBo = deptdhe.findByName(cellDeptName.toString());
                    if (deptBo != null) {
                        entity.setDeptName(deptBo.getDeptName());
                        entity.setDeptId(deptBo.getDeptId());
                    }
                    //get business
                    BusinessDAOHE busdhe = new BusinessDAOHE();
                    Business busbo = busdhe.findByName(cellBusiness.toString());
                    if (busbo != null) {
                        entity.setBusinessId(busbo.getBusinessId());
                        entity.setBusinessName(busbo.getBusinessName());
                    }
                    //get posId
                    PositionDAOHE posdhe = new PositionDAOHE();
                    Position pos = posdhe.findByName(cellPosition.toString());
                    if (pos != null) {
                        entity.setPosId(pos.getPosId());
                    } else {
                        userError += i + " li Chc v,";
                        customInfo.add(userError);
                    }
                    if (cellTelephone != null) {
                        entity.setTelephone(cellTelephone.toString());
                    }
                    if (cellFax != null) {
                        entity.setFax(cellFax.toString());
                    }
                    if (cellBirthPlace != null) {
                        entity.setBirthPlace(cellBirthPlace.toString());
                    }
                    if (cellStaffCode != null) {
                        entity.setStaffCode(cellStaffCode.toString());
                    }
                    if (cellIdentityCard != null) {
                        entity.setIdentityCard(cellIdentityCard.toString());
                    }
                    if (cellIssuePlaceIdent != null) {
                        entity.setIssuePlaceIdent(cellIssuePlaceIdent.toString());
                    }
                    if (cellIssueDateIdent != null && cellIssueDateIdent.toString().length() > 0) {
                        entity.setIssueDateIdent(formatter.parse(cellIssueDateIdent.toString()));
                    }
                    if (cellDateOfBirth != null) {
                        entity.setDateOfBirth(formatter.parse(cellDateOfBirth.toString()));
                    }
                    if (cellDescription != null) {
                        entity.setDescription(cellDescription.toString());
                    }
                    // end validate input
                    String passwordEncrypt = PasswordService.getInstance().encrypt("Attp@123");
                    entity.setPassword(passwordEncrypt);
                    entity.setPasswordchanged(0L);
                    entity.setStatus(1L);
                    //                        entity.setDeptId(Long.parseLong(cellDeptId.toString()));
                    //                        entity.setPosId(Long.parseLong(cellPosId.toString()));
                    //                        entity.setStatus(Long.parseLong(cellStatus.toString()));
                    //                        entity.setGender(Long.parseLong(cellGender.toString()));
                    String gender;
                    if (cellGender == null) {
                        userError += i + " li Gii tnh,";
                        customInfo.add(userError);
                    } else {
                        gender = cellGender.toString().trim().toLowerCase();
                        if (gender.contains("Nam") || gender.contains("man") || gender.contains("male")) {
                            entity.setGender(0L);
                        } else {
                            entity.setGender(1L);
                        }
                    }
                    if (entity != null) {
                        getSession().saveOrUpdate(entity);
                        RoleUser roleUser = new RoleUser();
                        roleUser.setIsActive(1L);
                        roleUser.setIsAdmin(0L);
                        roleUser.setUserId(entity.getUserId());
                        roleUser.setRoleId(323L);
                        roleUser.setRoleUserPK(new RoleUserPK(322, entity.getUserId()));
                        getSession().saveOrUpdate(roleUser);
                        customInfo.add("success");
                    } else {
                        userError += i + ",";
                        customInfo.add(userError);
                    }

                } // end if row != null
            } // end if att != null
            catch (Exception ex) {
                LogUtil.addLog(ex);//binhnt sonar a160901
                userError += i + ",";
                customInfo.add(userError);
                //                    jsonDataGrid.setCustomInfo(customInfo);
                //                    return "gridData";
            }
        } // end loop

    }
    this.jsonDataGrid.setCustomInfo(customInfo);
    return "gridData";
}

---

From source file:com.wangzhu.poi.ExcelToHtmlConverter.java

License:Apache License

protected void processSheet(HSSFSheet sheet) {
    // this.processSheetHeader(this.htmlDocumentFacade.getBody(), sheet);

    final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    if (physicalNumberOfRows <= 0) {
        return;// w  ww .j  a v a  2s . c  o m
    }

    Element table = this.htmlDocumentFacade.createTable();
    this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable,
            "border-collapse:collapse;border-spacing:0;");

    Element tableBody = this.htmlDocumentFacade.createTableBody();

    final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet);

    final List emptyRowElements = new ArrayList(physicalNumberOfRows);
    int maxSheetColumns = 1;
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        HSSFRow row = sheet.getRow(r);

        if (row == null) {
            continue;
        }

        if (!this.isOutputHiddenRows() && row.getZeroHeight()) {
            continue;
        }

        Element tableRowElement = this.htmlDocumentFacade.createTableRow();
        this.htmlDocumentFacade.addStyleClass(tableRowElement, this.cssClassPrefixRow,
                "height:" + (row.getHeight() / 20f) + "pt;");

        int maxRowColumnNumber = this.processRow(mergedRanges, row, tableRowElement);

        if (maxRowColumnNumber == 0) {
            emptyRowElements.add(tableRowElement);
        } else {
            if (!emptyRowElements.isEmpty()) {
                for (Iterator iterator = emptyRowElements.iterator(); iterator.hasNext();) {
                    Element emptyRowElement = (Element) iterator.next();
                    tableBody.appendChild(emptyRowElement);
                }
                emptyRowElements.clear();
            }

            tableBody.appendChild(tableRowElement);
        }
        maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber);
    }

    this.processColumnWidths(sheet, maxSheetColumns, table);

    if (this.isOutputColumnHeaders()) {
        this.processColumnHeaders(sheet, maxSheetColumns, table);
    }

    table.appendChild(tableBody);

    this.htmlDocumentFacade.getBody().appendChild(table);

    if (null != this.getExcelImageManager()) {

        table = this.htmlDocumentFacade.createTable();
        this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable,
                "border-collapse:collapse;border-spacing:0;");

        tableBody = this.htmlDocumentFacade.createTableBody();
        List<String> urlPaths = this.getExcelImageManager()
                .getImagePath(sheet.getDrawingPatriarch().getChildren());
        if ((urlPaths != null) && (urlPaths.size() != 0)) {
            Document document = this.htmlDocumentFacade.getDocument();

            for (int i = 0, size = urlPaths.size(); i < size; i++) {
                Element tableRowElement = this.htmlDocumentFacade.createTableRow();
                String[] urlPathArr = urlPaths.get(i).split("@");
                Element result = document.createElement("img");
                result.setAttribute("src", urlPathArr[0]);
                String imageWidth = urlPathArr[1];
                String imageHeight = urlPathArr[2];
                result.setAttribute("style",
                        "width:" + imageWidth + "in;height:" + imageHeight + "in;vertical-align:text-bottom;");

                Element tableCellElement = this.htmlDocumentFacade.createTableCell();
                tableCellElement.appendChild(result);
                tableRowElement.appendChild(tableCellElement);
                tableBody.appendChild(tableRowElement);
            }
            table.appendChild(tableBody);
            this.htmlDocumentFacade.getBody().appendChild(table);
        }
    }

}

---

From source file:com.weibo.datasys.parser.office.extractor.ExcelParse.java

License:Open Source License

private FileData extractor(File filePath) {
    FileData fData = new FileData();
    fData.setName(filePath.getName());/*from   www. ja v  a  2 s .c  o m*/
    StringBuffer sBuffer = new StringBuffer();
    HSSFWorkbook workbook = null;
    try {
        workbook = new HSSFWorkbook(new FileInputStream(filePath));
        for (int iSheets = 0; iSheets < workbook.getNumberOfSheets(); ++iSheets) {
            HSSFSheet sheet = workbook.getSheetAt(iSheets);
            for (int iRow = 0; iRow < sheet.getLastRowNum(); ++iRow) {
                HSSFRow row = sheet.getRow(iRow);
                for (int iCell = 0; iCell < row.getLastCellNum(); ++iCell) {
                    HSSFCell cell = row.getCell(iCell);
                    if (null != cell) {
                        if (0 == cell.getCellType()) {
                            sBuffer.append(String.valueOf(cell.getNumericCellValue()));
                            sBuffer.append(SEGMENT_CHAR);
                        } else if (1 == cell.getCellType()) {
                            sBuffer.append(cell.getStringCellValue().trim());
                            sBuffer.append(SEGMENT_CHAR);
                        }
                    }
                }
            }
        }
        fData.setContent(sBuffer.toString());
    } catch (Exception e) {
        LOG.error("", e);
    }
    return fData;
}

---

• «
• 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
• »