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

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

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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

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

Usage

From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java

License:Apache License

private PoiProxySheet makeSheet(Workbook wb, FormulaParsingWorkbook ewb) {
    Sheet wbSheet = wb.getSheetAt(0);//  w  ww  .  j av  a2  s .  c o m
    PoiProxySheet sheet = new PoiProxySheet(wbSheet.getSheetName());

    for (int r = 0; r <= wbSheet.getLastRowNum(); r++) {
        Row wbSheetRow = wbSheet.getRow(r);
        if (wbSheetRow == null) {
            continue;
        }

        for (int c = 0; c <= wbSheetRow.getLastCellNum(); c++) {
            Cell wbSheetRowCell = wbSheetRow.getCell(c);
            if (wbSheetRowCell == null) {
                continue;
            }

            final Ptg[] tokens = CELL_TYPE_FORMULA == wbSheetRowCell.getCellType()
                    ? FormulaParser.parse(wbSheetRowCell.getCellFormula(), ewb, FormulaType.CELL, 0)
                    : null;
            sheet.setCell(PoiProxyCell.makeCell(sheet, wbSheetRowCell, tokens));
        }
    }

    return sheet;
}

From source file:com.dataart.spreadsheetanalytics.functions.poi.data.FuncexecFunction.java

License:Apache License

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {

    log.debug("In evaluate() of FUNCEXEC function. Args = {}", Arrays.toString(args));

    if (!(args[0] instanceof StringEval) && !(args[0] instanceof RefEval)) {
        log.warn(//from w w w .  j  av  a 2 s  . c o m
                "The first argument of FUNCEXEC function must be a string (or a reference to a cell) - name of DEFINE function.");
        return ErrorEval.VALUE_INVALID;
    }

    String defineFunctionName;
    try {
        defineFunctionName = (String) coerceValueTo(
                getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()));
    } catch (EvaluationException e) {
        log.error(String.format("Cannot get the value of DEFINE functuion name: %s", args[0]), e);
        return ErrorEval.VALUE_INVALID;
    }
    defineFunctionName = defineFunctionName.toUpperCase(Locale.getDefault());

    MetaFunctionAccessor defines = (MetaFunctionAccessor) ec.getCustomEvaluationContext()
            .get(MetaFunctionAccessor.class);
    if (defines == null) {
        defines = this.external.getMetaFunctionAccessor();
    }

    if (defines.get(defineFunctionName) == null) {
        log.warn("No DEFINE function with name {} is found.", defineFunctionName);
        return ErrorEval.NAME_INVALID;
    }

    List<ValueEval> inputValues = new LinkedList<>();
    List<ValueEval> inArgs = new ArrayList(Arrays.asList(args));
    inArgs.remove(0); //remove define function name

    try {
        for (ValueEval v : ICustomFunction.prepareQueryArgs(inArgs)) {
            inputValues.add(getSingleValue(v, ec.getRowIndex(), ec.getColumnIndex()));
        }
    } catch (EvaluationException e) {
        log.error("Cannot resolve input values for FUNCEXEC function", e);
        return ErrorEval.VALUE_INVALID;
    }

    final DefineFunctionMeta meta = (DefineFunctionMeta) defines.get(defineFunctionName);
    log.info("Found DEFINE function to invoke. Name = {}.", defineFunctionName);

    if (meta.getInputs().size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for FUNCEXEC+DEFINE. Expected: {}, Actual: {}.",
                meta.getInputs().size(), args.length - 1);
        return ErrorEval.VALUE_INVALID;
    }

    List<IA1Address> inputAddresses = meta.getInputs();
    log.debug("Input Addresses for DEFINE: {}, Input Values for DEFINE: {}.", inputAddresses, inputValues);

    if (inputAddresses.size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for {} function.", defineFunctionName);
        return ErrorEval.VALUE_INVALID;
    }

    DataModelAccessor dataModels = (DataModelAccessor) ec.getCustomEvaluationContext()
            .get(DataModelAccessor.class);
    if (dataModels == null) {
        dataModels = this.external.getDataModelAccessor();
    }

    IDataModel dmWithDefine = dataModels.get(meta.getDataModelId());

    Workbook book = toWorkbook(dmWithDefine);
    EvaluationWorkbook defineBook = toEvaluationWorkbook(book);

    Sheet s = book.getSheetAt(0); //TODO one sheet support
    for (int i = 0; i < inputAddresses.size(); i++) {

        Row defineRow = s.getRow(inputAddresses.get(i).row());
        if (defineRow == null) {
            defineRow = s.createRow(inputAddresses.get(i).row());
        }
        Cell defineCell = defineRow.getCell(inputAddresses.get(i).column());
        if (defineCell == null) {
            defineCell = defineRow.createCell(inputAddresses.get(i).column());
        }

        populateCellValue(defineCell, inputValues.get(i));
        updateCell(defineBook, defineCell);
    }

    WorkbookEvaluator defineEvaluator = new WorkbookEvaluator(defineBook,
            IStabilityClassifier.TOTALLY_IMMUTABLE, null);
    List<ValueEval> outputValues = meta.getOutputs().stream().map(
            a -> defineEvaluator.evaluate(getEvaluationCell(defineBook, a), ec.getCustomEvaluationContext()))
            .collect(Collectors.<ValueEval>toList());

    log.debug("Output Values of DEFINE execution: {}.", outputValues);

    return outputValues.size() == 1 ? outputValues.get(0) : toArrayEval(outputValues);
}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * ?/*from ww w.j  a  va  2 s  . c  o m*/
 * @param sheetRow
 * @param length
 * @return
 */
public static boolean isEmpty(Row sheetRow, int length) {
    if (sheetRow != null) {
        for (int i = 0; i < length; i++) {
            Cell cell = sheetRow.getCell(i);
            if (!isEmpty(cell)) {
                return false;
            }
        }
    }
    return true;
}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * ??hash?/*from www .  ja  va 2s  .co m*/
 * @param sheet
 * @return
 */
public static long getHashVal(Sheet sheet) {
    Row sheetRow = sheet.getRow(HASH_ROW);
    Cell cell = sheetRow.getCell(0);
    return ((Double) cell.getNumericCellValue()).longValue();
}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * sheet ?/*w w w  .j a v a2s  . c o  m*/
 * @param sheet
 * @param row
 * @param length
 * @param data
 */
public static void addTitle(Sheet sheet, int row, int length, String data) {
    if (data == null || data.equals("") || data.equals("null")) {
        return;
    }
    Row sheetRow = sheet.createRow(row);
    for (int i = 0; i < length; i++) {
        sheetRow.createCell(i);
    }
    CellStyle style = sheet.getWorkbook().createCellStyle(); // ?
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 
    CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1);
    sheet.addMergedRegion(cellRangeAddress);
    Cell cell = sheetRow.getCell(0);
    cell.setCellStyle(style);
    cell.setCellValue(data);
}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * ??// w w w .j  a  v  a  2 s.  com
 * @param sheetRow
 * @param col
 * @return
 */
public static Cell getCell(Row sheetRow, int col) {
    Cell cell = sheetRow.getCell(col);
    if (cell == null) {
        cell = sheetRow.createCell(col);
    }
    return cell;
}

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyRows(Sheet st, int startRow, int endRow, int pPosition) {
    int pStartRow = startRow;
    int pEndRow = endRow;
    int targetRowFrom;
    int targetRowTo;
    int columnCount;
    CellRangeAddress region = null;/* www .j  ava2 s . c  o m*/
    int i;
    int j;
    for (i = 0; i < st.getNumMergedRegions(); i++) {
        region = st.getMergedRegion(i);
        if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) {
            targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
            targetRowTo = region.getLastRow() - pStartRow + pPosition;

            CellRangeAddress newRegion = region.copy();

            newRegion.setFirstRow(targetRowFrom);
            newRegion.setFirstColumn(region.getFirstColumn());
            newRegion.setLastRow(targetRowTo);
            newRegion.setLastColumn(region.getLastColumn());
            st.addMergedRegion(newRegion);
        }
    }
    //set the column height and value
    for (i = pStartRow; i <= pEndRow; i++) {
        Row sourceRow = st.getRow(i);
        columnCount = sourceRow.getLastCellNum();
        if (sourceRow != null) {
            Row newRow = st.createRow(pPosition + i);
            newRow.setHeight(sourceRow.getHeight());
            for (j = 0; j < columnCount; j++) {
                Cell templateCell = sourceRow.getCell(j);
                if (templateCell != null) {
                    Cell newCell = newRow.createCell(j);
                    copyCell(templateCell, newCell);
                }
            }
        }
    }
}

From source file:com.dituiba.excel.ImportTableService.java

License:Apache License

/**
 * ExcelTableBean/*  ww w .  ja  v a  2s.  c om*/
 */
public void doImport() {
    int rowNum = sheet.getLastRowNum() + 1;
    int columnNum = 0;
    for (int i = 0; i < rowNum; i++) {
        if (sheet.getRow(i) != null) {
            int last = sheet.getRow(i).getLastCellNum();
            columnNum = last > columnNum ? last : columnNum;
        }
    }
    tableBean = new TableBean(rowNum, columnNum);
    Collection<CellBean> cellBeans = new ArrayList<CellBean>();
    for (int r = startRow; r < rowNum; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    String cellValue = null;
                    if (cellHandlerMap.containsKey(c)) {
                        cellValue = cellHandlerMap.get(c).readCell(cell) + "";
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        Integer type = forceCellType.get(c);
                        if (type != null) {
                            cell.setCellType(type);
                        }
                        if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                            cellValue = cell.getBooleanCellValue() + "";
                        } else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
                            try {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            } catch (IllegalStateException e) {
                                cellValue = String.valueOf(cell.getRichStringCellValue()).trim();
                            }
                        } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                            if (DateUtil.isCellDateFormatted(cell)) {
                                Date date2 = cell.getDateCellValue();
                                SimpleDateFormat dff = new SimpleDateFormat(dateFormat);
                                cellValue = dff.format(date2); //
                            } else {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            }
                        } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                            cellValue = cell.getStringCellValue();
                        }
                        if (cellValue != null && cellValue instanceof String) {
                            cellValue = cellValue.toString().trim();
                        }
                    }
                    CellBean cellBean = new CellBean(cellValue, r, c);
                    cellBean.setCell(cell);
                    cellBeans.add(cellBean);
                }
            }
        }
    }
    tableBean.setCellBeans(cellBeans);
}

From source file:com.diversityarrays.kdxplore.design.ExcelRowDataProvider.java

License:Open Source License

/**
 * Return null at EOF.//from   ww w. java2s  .c  om
 * @return
 */
private String[] nextLine() {
    String[] result = null;

    DecimalFormat dfmt = new DecimalFormat("#.000");
    if ((rowIndex + 1) < rowCount) {
        result = EMPTY;
        ++rowIndex;
        lineNumber = rowIndex + 1;
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            int nCells = ExcelUtil.getCellCount(row);
            if (nCells > 0) {
                List<String> list = new ArrayList<>();
                boolean anyNonBlank = false;
                for (int colIndex = 0; colIndex < nCells; ++colIndex) {
                    Cell cell = row.getCell(colIndex);
                    String v = null;
                    if (cell != null) {
                        v = ExcelUtil.getCellStringValue(cell, "", dfmt);
                    }

                    if (Check.isEmpty(v)) {
                        list.add("");
                    } else {
                        anyNonBlank = true;
                        list.add(v);
                    }
                }
                if (!list.isEmpty() && anyNonBlank) {
                    result = list.toArray(new String[list.size()]);
                }
            }
        }
    }
    return result;
}

From source file:com.diversityarrays.kdxplore.importdata.bms.BmsExcelImportHelper.java

License:Open Source License

public void parsePlotsAndSamples(BmsXlsTrialImportResult trialImportResult, PlotValueConsumer plotValueConsumer)
        throws IOException, IllegalArgumentException, ImportDataException, CreateItemException {

    int rowCount = ExcelUtil.getRowCount(observation);
    int expectedCellCount = 0;

    //      final Date trialPlantingDate = trialImportResult.trial.getTrialPlantingDate();
    //      do NOT do this check NOW as there may not actually be any data for these Traits
    //      If there is then we will throw an InvalidValueException in the PlotValueConsumer.
    //      if (trialPlantingDate == null) {
    //         List<String> traitNames = new ArrayList<>();
    //         for (Trait trait : trialImportResult.trialTraits.values()) {
    //            if (TraitDataType.ELAPSED_DAYS == trait.getTraitDataType()) {
    //               traitNames.add(trait.getTraitName());
    //            }
    //         }//from  w ww .j a va2s . co  m
    //         if (! traitNames.isEmpty()) {
    //            throw new IOException(StringUtil.join("Planting Date required:", ",", traitNames));
    //         }
    //      }

    Map<Integer, String> headingByCellNumber = new TreeMap<>();
    List<ConsumerAndCellnum> consumerAndCellNum = null; // Gets set when rownum==0

    for (int rownum = 0; rownum < rowCount; ++rownum) {
        Row row = observation.getRow(rownum);
        if (row == null)
            continue;

        int cellCount = ExcelUtil.getCellCount(row);
        if (rownum == 0) {
            expectedCellCount = cellCount;
            consumerAndCellNum = buildConsumerByCellnum(trialImportResult, row, cellCount, headingByCellNumber);

            for (ConsumerAndCellnum cc : consumerAndCellNum) {
                if (cc.cellValueConsumer instanceof TraitConsumer) {
                    TraitConsumer tc = (TraitConsumer) cc.cellValueConsumer;
                    plotValueConsumer.createTraitInstance(tc.traitName);
                }
                System.out.println(cc.cellnum + "\t" + cc.cellValueConsumer.toString());
            }
        } else if (cellCount > 0) {

            if (cellCount != expectedCellCount) {
                String msg = "expected " + expectedCellCount + " columns but got " + cellCount;
                plotValueConsumer.warn(rownum + 1, msg);
            }

            Map<Integer, String> cellValueByCellnum = new HashMap<>();

            for (int cellnum = 0; cellnum < cellCount; ++cellnum) {
                Cell cell = row.getCell(cellnum);
                String cellValue = ""; //$NON-NLS-1$
                if (cell != null) {
                    cellValue = ExcelUtil.getCellStringValue(cell, "");
                }
                cellValueByCellnum.put(cellnum, cellValue);
            }

            Plot plot = new Plot();
            plot.setTrialId(trialImportResult.trial.getTrialId());

            // New collection because we are going to modify
            // cellValueByCellnum
            //            List<Integer> cellNumbers = new ArrayList<>(
            //                  cellValueByCellnum.keySet());
            //            Collections.sort(cellNumbers);

            boolean completed = false;
            for (ConsumerAndCellnum cnc : consumerAndCellNum) {
                int cellnum = cnc.cellnum;
                CellValueConsumer consumer = cnc.cellValueConsumer;
                if (consumer != null) {
                    if (!completed && !consumer.isPlotField()) {
                        plotValueConsumer.plotComplete(rownum + 1, plot);
                        completed = true;
                    }
                    String cellValue = cellValueByCellnum.remove(cellnum);
                    if (cellValue == null) {
                        plotValueConsumer.warn(rownum + 1, "Null value for cell#" + cellnum);
                    } else if (cellValue.isEmpty()) {
                        cellValue = null;
                    }
                    consumer.consume(plot, cellValue, plotValueConsumer);
                } else {
                    System.err.println("% no CellValueConsumer found for cellnum=" + cellnum + ", value="
                            + cellValueByCellnum.get(cellnum));
                    ;
                }
            }
            if (!completed) {
                plotValueConsumer.plotComplete(rownum + 1, plot);
            }

            if (!cellValueByCellnum.isEmpty()) {
                List<String> unused = new ArrayList<>();
                for (Integer cellnum : cellValueByCellnum.keySet()) {
                    unused.add(headingByCellNumber.get(cellnum) + " (#" //$NON-NLS-1$
                            + cellnum + ")"); //$NON-NLS-1$
                }
                String msg = StringUtil.join("Unused Data: ", ",", unused); //$NON-NLS-2$
                plotValueConsumer.warn(rownum + 1, msg);
            }
        }
    }
}