Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum)

Source Link

Document

Get the hssfcell representing a given column (logical cell) 0-based.

Usage

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * /* w w w  . j a v  a 2  s .  c o m*/
 * @param
 * @return void
 */
private void buildStyle(HSSFWorkbook wb, HSSFWorkbook src, HSSFSheet sheet, int index,
        HashMap<String, HSSFCellStyle> ret, String key) {
    HSSFRow row = sheet.getRow(index);
    HSSFCell cell = row.getCell(1);
    HSSFCellStyle nstyle = wb.createCellStyle();
    ExcelUtils.copyCellStyle(wb, nstyle, src, cell.getCellStyle());
    ret.put(key, nstyle);
}

From source file:com.esd.cs.common.HExcelSheetParser.java

License:Open Source License

public List<List<Object>> getDatasInSheet(int sheetNumber) {
    List<List<Object>> result = new ArrayList<List<Object>>();
    // sheet/*from w  w  w  .j av  a  2 s. c o  m*/
    HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
    // sheet
    int rowCount = sheet.getLastRowNum();
    logger.info("found excel rows count:" + rowCount);
    if (rowCount < 1) {
        return result;
    }
    // ??row
    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        // 
        HSSFRow row = sheet.getRow(rowIndex);
        if (null != row) {
            List<Object> rowData = new ArrayList<Object>();
            // ?
            int cellCount = row.getLastCellNum();
            // ??cell
            for (int cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                HSSFCell cell = row.getCell(cellIndex);
                // ??
                Object cellStr = this.getCellString(cell);

                rowData.add(cellStr);
            }
            result.add(rowData);
        }
    }

    return result;
}

From source file:com.etest.view.tq.itemanalysis.FileUploadWindow.java

void readContentFromExcelFile(File excelFile) {
    try {/*from www . j a v a  2s .com*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
        HSSFRow row;
        HSSFCell cell;

        boolean stop = false;
        boolean nonBlankRowFound;
        int s;
        HSSFRow lastRow = null;

        while (stop == false) {
            nonBlankRowFound = false;
            lastRow = sheet.getRow(sheet.getLastRowNum());
            for (s = lastRow.getFirstCellNum(); s <= lastRow.getLastCellNum(); s++) {
                cell = lastRow.getCell(s);
                if (cell != null && lastRow.getCell(s).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                    nonBlankRowFound = true;
                }
            }
            if (nonBlankRowFound == true) {
                stop = true;
            } else {
                sheet.removeRow(lastRow);
            }
        }

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols)
                    cols = tmp;
            }
        }

        List<ItemAnalysis> itemAnalysisList = new ArrayList<>();
        List<Character> answer;
        ItemAnalysis itemAnalysis = null;

        for (int c = 0; c < cols; c++) {
            itemAnalysis = new ItemAnalysis();
            answer = new ArrayList<>();
            for (int r = 0; r < rows; r++) {
                row = sheet.getRow(r);
                if (row == null || row.toString().isEmpty()) {
                    ShowErrorNotification.error("Remove all blank/empty rows after the last Item!");
                    return;
                } else {
                    //                   if(row != null){
                    cell = row.getCell(c);
                    if (cell == null || cell.toString().isEmpty()) {
                        ShowErrorNotification.error("Remove all blank/empty columns after the last student!");
                        return;
                    } else {
                        //                       if(cell != null){                           
                        if (c != 0) {
                            if (r == 0) {
                                itemAnalysis.setStudentNumber(cell.toString().trim());
                            } else {
                                answer.add(cell.toString().trim().charAt(0));
                            }
                        } else {
                            if (r != 0) {
                                totalItems++;
                            }
                        }
                    }
                }
            }
            if (c != 0) {
                itemAnalysis.setAnswer(answer);
                itemAnalysisList.add(itemAnalysis);
            }
        }

        if (tq.getCellItemIdByTQCoverageId(getTqCoverageId()).size() != totalItems) {
            ShowErrorNotification.error("Total Items do not MATCH!");
            totalItems = 0;
            return;
        }

        studentNoAndTotalScore = new HashMap<>();
        studentNoAndAnswer = new HashMap<>();
        totalItems = 1;
        new Thread() {

            @Override
            public void run() {
                totalData = itemAnalysisList.size();
                for (ItemAnalysis i : itemAnalysisList) {
                    try {
                        Thread.sleep(50);
                        studentNoAndTotalScore.put(i.getStudentNumber(), ItemAnalysisInterpretation
                                .getTotalScoresOfAllStudent(tqCoverageId, i.getAnswer()));
                        studentNoAndAnswer.put(i.getStudentNumber(), i.getAnswer());

                        getUI().access(new Runnable() {

                            @Override
                            public void run() {
                                if (totalItems < itemAnalysisList.size()) {
                                    analyze.setValue("Analyzing data.. "
                                            + CommonUtilities.roundOffToTwoDecimal((current / totalData) * 100)
                                            + "%");
                                    current++;
                                } else {
                                    analyze.setValue("Data analyzed... 100%");
                                    getLowerAndUpperGroupStudent(studentNoAndTotalScore);

                                    HorizontalLayout h = new HorizontalLayout();
                                    h.setWidth("100%");

                                    h.addComponent(viewTableProportion());
                                    h.addComponent(viewStudentsTotalScore());
                                    h.addComponent(approveItemAnalysis());
                                    v.addComponent(h);

                                    v.addComponent(itemAnalysisGridPanel());
                                }
                            }

                        });

                        totalItems++;
                    } catch (InterruptedException ex) {
                        Logger.getLogger(FileUploadWindow.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }

        }.start();
        UI.getCurrent().setPollInterval(500);
    } catch (IOException ex) {
        Logger.getLogger(TQItemAnalysisUI.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.example.selenium.util.ApachePOIXLSReader.java

public static Object[][] excelRead(String filePath) throws Exception {
    File excel = new File(filePath);
    FileInputStream fis = new FileInputStream(excel);

    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet ws = wb.getSheet("Sheet1");

    int rowNum = ws.getLastRowNum() + 1;
    int colNum = ws.getRow(0).getLastCellNum();
    Object[][] data = new Object[(rowNum - 1)][colNum];

    int k = 0;/*from  w  ww . ja v a 2 s.c  o  m*/
    for (int i = 1; i < rowNum; i++) {
        HSSFRow row = ws.getRow(i);
        for (int j = 0; j < colNum; j++) {
            HSSFCell cell = row.getCell(j);
            Object value = cellToObject(cell);
            data[k][j] = value;
        }
        k++;
    }
    return data;
}

From source file:com.flexive.extractor.ExcelExtractor.java

License:Open Source License

private void processSheet(HSSFSheet sheet) {
    try {/*w  ww  .  ja v  a2s .  c  om*/
        // Use the HFFS functions for the number of rows & columns
        int rowCount = sheet.getPhysicalNumberOfRows();
        int colCount = sheet.getRow(0).getPhysicalNumberOfCells();
        HSSFRow row;
        HSSFCell cell;
        String cellValue;
        for (int i = 0; i < rowCount; i++) {
            row = sheet.getRow(i);
            for (short j = 0; j < colCount; j++) {
                cell = row.getCell(j);
                if (cell != null) {
                    try {
                        switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            cellValue = String.valueOf(cell.getNumericCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            // Doesnt make much sense to index a cell formula
                            cellValue = "";
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            cellValue = String.valueOf(cell.getErrorCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            cellValue = "";
                            break;
                        default:
                            cellValue = cell.getStringCellValue();
                        }
                    } catch (Exception exc) {
                        cellValue = "";
                    }
                    writer.write(FxSharedUtils.getBytes(cellValue));
                }
            }
        }
    } catch (Exception eN) {
        System.out.println("Error reading sheet:" + eN.toString());
    }
}

From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java./*  ww w  . j  a v a  2 s  .c  o  m*/
 * 
 * @param file
 * @return
 * @throws IOException
 */
public static List<Map<String, Object>> parseHSSFMapList(MultipartFile file) throws IOException {//
    POIFSFileSystem poiFs = new POIFSFileSystem(file.getInputStream());
    HSSFWorkbook wb = new HSSFWorkbook(poiFs);

    HSSFSheet sheet = wb.getSheetAt(0);

    int rowNum = sheet.getLastRowNum();
    HSSFRow titleRow = sheet.getRow(0);

    int colNum = titleRow.getLastCellNum();

    //?17
    List<String> titleList = new ArrayList<String>();
    for (int i = 0; i < colNum; i++) {
        String title = titleRow.getCell(i).getStringCellValue();
        titleList.add(trimTitle(title));
    }

    List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
    for (int i = 1; i <= rowNum; i++) {
        HSSFRow row = sheet.getRow(i);
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        for (int j = 0; j < colNum; j++) {
            HSSFCell cell = row.getCell(j);
            if (cell != null) {
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    double d = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    //?
                    if (HSSFDateUtil.isCellDateFormatted(cell) || (style != null
                            && (style.getDataFormat() == 57 || style.getDataFormat() == 58))) {
                        map.put(titleList.get(j), HSSFDateUtil.getJavaDate(d));
                    } else {
                        map.put(titleList.get(j), d);
                    }
                    break;

                default:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    map.put(titleList.get(j), row.getCell(j).getStringCellValue());
                    break;
                }
            } else {
                map.put(titleList.get(j), null);
            }
        }
        mapList.add(map);
    }

    return mapList;
}

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?// ww w  .  j a  v a 2  s .c  om
 * 
 * @param uploadFileName
 * @return
 *         2015112
 */
public static List<String> getTitlesList(InputStream inputStream) throws Exception {
    // ?excel2003
    POIFSFileSystem poiFs = new POIFSFileSystem(inputStream);
    HSSFWorkbook wb = new HSSFWorkbook(poiFs);

    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow titleRow = sheet.getRow(0);
    int colNum = titleRow.getLastCellNum();

    // ?
    List<String> titleList = new ArrayList<String>();
    for (int i = 0; i < colNum; i++) {
        String title = titleRow.getCell(i).getStringCellValue();
        titleList.add(trimTitle(title));
    }

    return titleList;
}

From source file:com.github.dougkelly88.FLIMPlateReaderGUI.SequencingClasses.Classes.FilterTableModel.java

public void loadFilterTableModelfromSpreadsheet() {
    ArrayList<FilterSetup> load = new ArrayList();
    HSSFSheet worksheet = HCAFLIMPluginFrame.wbLoad.getSheet("SpectralSequencing");
    int RowSize = worksheet.getPhysicalNumberOfRows();
    for (int RowNum = 0; RowNum < RowSize - 1; RowNum++) {
        HSSFRow row = worksheet.getRow(RowNum + 1);
        HSSFCell cell0 = row.getCell(0);
        HSSFCell cell1 = row.getCell(1);
        HSSFCell cell2 = row.getCell(2);
        HSSFCell cell3 = row.getCell(3);
        HSSFCell cell4 = row.getCell(4);
        HSSFCell cell5 = row.getCell(5);
        HSSFCell cell6 = row.getCell(6);
        HSSFCell cell7 = row.getCell(7);
        String delayss = cell7.getStringCellValue();
        // some initializations for changing String of numbers to arrayList        
        int strLength = delayss.length();
        int count = 0;
        ArrayList<Integer> delays = new ArrayList();
        String findStr = ",";
        int lastIndex = 0;
        String label = null;/*from w  ww .ja  va 2s  .  co  m*/
        int labelInt = 0;
        int bbb = 0;
        // counting entries in txt file for property
        while (lastIndex != -1) {

            lastIndex = delayss.indexOf(findStr, lastIndex);

            if (lastIndex != -1) {
                count++;
                lastIndex += findStr.length();
            }
        }

        String subStr1 = delayss.substring(1, strLength - 1);

        // writes every single value into arrayList
        for (int i = 0; i < count; i++) {
            bbb = subStr1.indexOf(",");
            label = subStr1.substring(0, bbb);
            labelInt = Integer.parseInt(label);
            delays.add(labelInt);
            subStr1 = subStr1.substring(bbb + 2);
        }
        // writes last entry  
        label = subStr1;
        labelInt = Integer.parseInt(label);
        delays.add(labelInt);

        //            FilterSetup fov = new FilterSetup( cell0.getStringCellValue(), cell1.getStringCellValue(), cell2.getStringCellValue(), cell3.getStringCellValue(), cell4.getStringCellValue(), cell5.getStringCellValue(),  (int) cell6.getNumericCellValue(), delays);
        //            load.add(fov);
    }
    data_ = load;
    fireTableDataChanged();

}

From source file:com.github.dougkelly88.FLIMPlateReaderGUI.SequencingClasses.Classes.FOVTableModel.java

public void loadFOVTableModelfromSpreadsheet() {
    ArrayList<FOV> load = new ArrayList();
    HSSFSheet worksheet = HCAFLIMPluginFrame.wbLoad.getSheet("XYSequencing");
    int RowSize = worksheet.getPhysicalNumberOfRows();
    for (int RowNum = 0; RowNum < RowSize - 1; RowNum++) {
        HSSFRow row = worksheet.getRow(RowNum + 1);
        HSSFCell cell0 = row.getCell(0);
        HSSFCell cell1 = row.getCell(1);
        HSSFCell cell2 = row.getCell(2);
        HSSFCell cell3 = row.getCell(3);
        HSSFCell cell4 = row.getCell(4);

        FOV fov = new FOV(cell1.getNumericCellValue(), cell2.getNumericCellValue(), cell3.getNumericCellValue(),
                cell0.getStringCellValue(), pp_);
        fov.setGroup(cell4.getStringCellValue());
        load.add(fov);//from   w  w w .  ja  v  a  2 s  . com
    }
    data_ = load;
    fireTableDataChanged();

}

From source file:com.glaf.base.modules.todo.TodoXlsReader.java

License:Apache License

public List<Todo> readXls(java.io.InputStream inputStream) {
    List<Todo> todos = new java.util.ArrayList<Todo>();
    HSSFWorkbook wb = null;/*from w w  w  .  ja  v a 2s  .  c  o  m*/
    try {
        wb = new HSSFWorkbook(inputStream);
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    }
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = sheet.getRow(1);
    Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>();
    Map<String, Object> dataMap = new java.util.HashMap<String, Object>();
    int cells = row.getPhysicalNumberOfCells();
    for (int colIndex = 0; colIndex < cells; colIndex++) {
        HSSFCell cell = row.getCell(colIndex);
        keyMap.put(colIndex, cell.getStringCellValue());
    }
    Set<String> keys = new HashSet<String>();
    for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
        HSSFRow rowx = sheet.getRow(rowIndex);
        if (rowx == null) {
            continue;
        }
        // System.out.println();
        dataMap.clear();
        for (int colIndex = 0; colIndex < cells; colIndex++) {
            String fieldName = keyMap.get(colIndex);
            HSSFCell cell = rowx.getCell(colIndex);
            if (cell == null) {
                continue;
            }
            Object cellValue = null;
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_STRING:
                if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) {
                    cellValue = cell.getRichStringCellValue().getString();
                }
                break;
            default:
                if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                    cellValue = cell.getStringCellValue();
                }
                break;
            }
            if (cellValue != null) {
                dataMap.put(fieldName, cellValue);
                if ("id".equals(fieldName)) {
                    cellValue = cellValue.toString().substring(0, cellValue.toString().indexOf("."));
                    dataMap.put(fieldName, cellValue);
                }
            }
        }

        if (dataMap.get("code") != null) {

            String id = ParamUtils.getString(dataMap, "id");
            if (!keys.contains(ParamUtils.getString(dataMap, "code"))) {
                if (id != null && StringUtils.isNotEmpty(id)) {
                    Todo model = new Todo();
                    Tools.populate(model, dataMap);
                    if (ParamUtils.getInt(dataMap, "limitDay") > 0) {
                        model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay"));
                    }
                    todos.add(model);
                    keys.add(model.getCode());
                }
            }
        }
    }

    return todos;
}