Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * //from w ww  . java2s .com
 * @param cell
 * @param map
 * @param name
 * @throws Exception
 */
private void foreachCol(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = name.contains(FOREACH_COL_VALUE);
    name = name.replace(FOREACH_COL_VALUE, EMPTY).replace(FOREACH_COL, EMPTY).replace(START_STR, EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);
    Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper);
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];
    @SuppressWarnings("unchecked")
    List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];
    while (its.hasNext()) {
        Object t = its.next();
        setForEeachRowCellValue(true, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan,
                mergedRegionHelper);
        cell = cell.getRow().getCell(cell.getColumnIndex() + colspan);
    }
    if (isCreate) {
        cell = cell.getRow().getCell(cell.getColumnIndex() - 1);
        cell.setCellValue(cell.getStringCellValue() + END_STR);
    }
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * foreach?//from ww w  .ja  v a 2  s . c  om
 * @param cell 
 * @param map
 * @param name
 * @throws Exception 
 */
private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = !name.contains(FOREACH_NOT_CREATE);
    boolean isShift = name.contains(FOREACH_AND_SHIFT);
    name = name.replace(FOREACH_NOT_CREATE, EMPTY).replace(FOREACH_AND_SHIFT, EMPTY).replace(FOREACH, EMPTY)
            .replace(START_STR, EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);
    Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper);
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];
    @SuppressWarnings("unchecked")
    List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];
    Row row = null;
    int rowIndex = cell.getRow().getRowNum() + 1;
    //??
    if (its.hasNext()) {
        Object t = its.next();
        setForEeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan,
                colspan, mergedRegionHelper);
        rowIndex += rowspan - 1;
    }
    if (isShift && datas.size() * rowspan > 1) {
        cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan,
                cell.getRow().getSheet().getLastRowNum(), (datas.size() - 1) * rowspan, true, true);
        /* cell.getRow().getSheet().shiftRows(cell.getRowIndex() + 1,
        cell.getRow().getSheet().getLastRowNum(), datas.size() * rowspan - 1, true, true);*/
        templateSumHanlder.shiftRows(cell.getRowIndex(), (datas.size() - 1) * rowspan);
    }
    while (its.hasNext()) {
        Object t = its.next();
        row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);
        setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan, colspan,
                mergedRegionHelper);
        rowIndex += rowspan;
    }
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ??/* w ww  . ja va  2s  .  c om*/
 * @param cell
 * @param name
 * @param mergedRegionHelper 
 * @return
 */
private Object[] getAllDataColumns(Cell cell, String name, MergedRegionHelper mergedRegionHelper) {
    List<ExcelForEachParams> columns = new ArrayList<ExcelForEachParams>();
    cell.setCellValue("");
    columns.add(getExcelTemplateParams(name.replace(END_STR, EMPTY), cell, mergedRegionHelper));
    int rowspan = 1, colspan = 1;
    if (!name.contains(END_STR)) {
        int index = cell.getColumnIndex();
        //?col 
        int startIndex = cell.getColumnIndex();
        Row row = cell.getRow();
        while (true) {
            int colSpan = columns.get(columns.size() - 1) != null ? columns.get(columns.size() - 1).getColspan()
                    : 1;
            index += colSpan;
            for (int i = 1; i < colSpan; i++) {
                //??,???,,?
                columns.add(null);
                continue;
            }
            cell = row.getCell(index);
            //???
            if (cell == null) {
                //?,
                columns.add(null);
                continue;
            }
            String cellStringString;
            try {//?? ?
                cellStringString = cell.getStringCellValue();
                if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) {
                    throw new ExcelExportException("for each ,?");
                } else if (StringUtils.isBlank(cellStringString) && colspan + startIndex > index) {
                    //?,,?,?,?
                    columns.add(new ExcelForEachParams(null, cell.getCellStyle(), (short) 0));
                    continue;
                }
            } catch (Exception e) {
                throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, e);
            }
            //?cell 
            cell.setCellValue("");
            if (cellStringString.contains(END_STR)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(END_STR, EMPTY), cell,
                        mergedRegionHelper));
                break;
            } else if (cellStringString.contains(WRAP)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
                //??,??
                colspan = index - startIndex + 1;
                index = startIndex - 1;
                row = row.getSheet().getRow(row.getRowNum() + 1);
                rowspan++;
            } else {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
            }
        }
    }
    colspan = 0;
    for (int i = 0; i < columns.size(); i++) {
        colspan += columns.get(i) != null ? columns.get(i).getColspan() : 0;
    }
    colspan = colspan / rowspan;
    return new Object[] { rowspan, colspan, columns };
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ???// w w  w.  j  av  a 2s  .  c om
 * @param name
 * @param cell
 * @param mergedRegionHelper 
 * @return
 */
private ExcelForEachParams getExcelTemplateParams(String name, Cell cell,
        MergedRegionHelper mergedRegionHelper) {
    name = name.trim();
    ExcelForEachParams params = new ExcelForEachParams(name, cell.getCellStyle(), cell.getRow().getHeight());
    //??
    if (name.startsWith(CONST) && name.endsWith(CONST)) {
        params.setName(null);
        params.setConstValue(name.substring(1, name.length() - 1));
    }
    //?
    if (NULL.equals(name)) {
        params.setName(null);
        params.setConstValue(EMPTY);
    }
    //????
    if (mergedRegionHelper.isMergedRegion(cell.getRowIndex() + 1, cell.getColumnIndex())) {
        Integer[] colAndrow = mergedRegionHelper.getRowAndColSpan(cell.getRowIndex() + 1,
                cell.getColumnIndex());
        params.setRowspan(colAndrow[0]);
        params.setColspan(colAndrow[1]);
    }
    params.setNeedSum(templateSumHanlder.isSumKey(params.getName()));
    return params;
}

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ????/*from  w w w. ja va2s .com*/
 * @param rows
 * @param params
 * @param excelCollection
 * @return
 */
private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,
        List<ExcelCollectionParams> excelCollection) {
    Map<Integer, String> titlemap = new HashMap<Integer, String>();
    Iterator<Cell> cellTitle;
    String collectionName = null;
    ExcelCollectionParams collectionParams = null;
    Row row = null;
    for (int j = 0; j < params.getHeadRows(); j++) {
        row = rows.next();
        if (row == null) {
            continue;
        }
        cellTitle = row.cellIterator();
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = getKeyValue(cell);
            int i = cell.getColumnIndex();
            //????
            if (StringUtils.isNotEmpty(value)) {
                if (titlemap.containsKey(i)) {
                    collectionName = titlemap.get(i);
                    collectionParams = getCollectionParams(excelCollection, collectionName);
                    titlemap.put(i, collectionName + "_" + value);
                } else if (StringUtils.isNotEmpty(collectionName)
                        && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {
                    titlemap.put(i, collectionName + "_" + value);
                } else {
                    collectionName = null;
                    collectionParams = null;
                }
                if (StringUtils.isEmpty(collectionName)) {
                    titlemap.put(i, value);
                }
            }
        }
    }
    return titlemap;
}

From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java

License:Apache License

private static <T> List<T> parse(Workbook workbook, FormulaEvaluator evaluator, Class<T> type, Locale locale) {
    MetaInfo metaInfo = MetaInfo.forType(type, locale);
    Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Iterator<Row> rows = sheet.rowIterator();
    if (!rows.hasNext()) {
        return Collections.emptyList();
    }/*from w w w .  j a  va 2s. c o m*/
    Row firstRow = rows.next();
    Map<Integer, String> columnIndexToFieldName = Maps.newHashMapWithExpectedSize(metaInfo.size());
    for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) {
        Cell cell = it.next();
        JsonElement jsonElement = parseAsJsonElement(cell, evaluator);
        if (jsonElement != null) {
            Field field = metaInfo.getField(jsonElement.getAsString());
            if (field != null) {
                String name = field.getName();
                int index = cell.getColumnIndex();
                columnIndexToFieldName.put(index, name);
            }
        }
    }
    if (columnIndexToFieldName.isEmpty()) {
        return Collections.emptyList();
    }
    List<T> result = new ArrayList<>(sheet.getLastRowNum() - sheet.getFirstRowNum());
    while (rows.hasNext()) {
        result.add(parseRow(evaluator, rows.next(), columnIndexToFieldName, type));
    }
    return result;
}

From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java

License:Apache License

private static <T> T parseRow(FormulaEvaluator evaluator, Row row, Map<Integer, String> fields, Class<T> type) {
    JsonObject jsonObject = new JsonObject();
    for (Iterator<Cell> it = row.cellIterator(); it.hasNext();) {
        Cell cell = it.next();
        String name = fields.get(cell.getColumnIndex());
        if (name != null) {
            JsonElement cellValue = parseAsJsonElement(cell, evaluator);
            if (cellValue != null) {
                jsonObject.add(name, cellValue);
            }/*from  w w w .  j  av a  2  s  .c  o  m*/
        }
    }
    return GsonHolder.GSON.fromJson(jsonObject, type);
}

From source file:co.foldingmap.data.ExcelDataConnector.java

License:Open Source License

/**
 * Parses the cells from the workbook into a TabulaData class.
 * //from  w w  w.  j  a va 2 s  . c om
 * @param workingSheet
 * @return 
 */
private TabularData parseWorkbook() {
    ArrayList<ArrayList<DataCell>> rows;
    ArrayList<DataCell> cells;
    int columnIndex, currentCellColumnIndex, lastCellcolumnIndex;
    int numberOfCells, previousRowLength, rowIndex;
    TabularData dataFile;

    dataFile = new TabularData();
    previousRowLength = 0;
    rows = new ArrayList<ArrayList<DataCell>>();
    rowIndex = -1;

    try {
        for (Row row : workingSheet) {
            cells = new ArrayList<DataCell>();
            columnIndex = row.getFirstCellNum();
            lastCellcolumnIndex = -1;
            numberOfCells = row.getPhysicalNumberOfCells();
            rowIndex++;

            //add blank cells
            for (int i = 0; i < columnIndex; i++)
                cells.add(new DataCell(""));

            for (Cell cell : row) {
                currentCellColumnIndex = cell.getColumnIndex();

                if ((lastCellcolumnIndex + 1) == currentCellColumnIndex) {
                    cells.add(getCellText(cell));
                    lastCellcolumnIndex = currentCellColumnIndex;
                } else {
                    for (int i = (lastCellcolumnIndex + 1); i < currentCellColumnIndex; i++) {
                        cells.add(new DataCell(""));
                    }

                    cells.add(getCellText(cell));
                    lastCellcolumnIndex = currentCellColumnIndex;
                }
            }

            //if this row does not match the length of the last one, add blank cells
            for (int i = (lastCellcolumnIndex + 1); i <= previousRowLength; i++) {
                cells.add(new DataCell(""));
                lastCellcolumnIndex = i;
            }

            previousRowLength = lastCellcolumnIndex;
            rows.add(cells);
        }
    } catch (Exception e) {
        Logger.log(Logger.ERR, "Error in ExcelDataConnector.parseWorkbook(Sheet) - " + e);
    }

    dataFile.loadData(rows);

    return dataFile;
}

From source file:com.accenture.bean.PlanoExcel.java

public void extraiPlanilha() {
    try {//from ww  w .  ja v a2 s.  com
        //Leitura
        FileInputStream arquivo = new FileInputStream(new File(fileName));

        // Carregando workbook
        XSSFWorkbook wb = new XSSFWorkbook(arquivo);
        // Selecionando a primeira aba
        XSSFSheet s = wb.getSheetAt(1);

        // Caso queira pegar valor por referencia
        CellReference cellReference = new CellReference("M8");
        Row row = s.getRow(cellReference.getRow());
        Cell cell = row.getCell(cellReference.getCol());
        System.out.println("Valor Refe:" + cell.getStringCellValue());

        // Fazendo um loop em todas as linhas
        for (Row rowFor : s) {
            // FAzendo loop em todas as colunas
            for (Cell cellFor : rowFor) {
                try {
                    // Verifica o tipo de dado
                    if (cellFor.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        // Na coluna 6 tenho um formato de data
                        if (cellFor.getColumnIndex() == 6) {
                            // Se estiver no formato de data
                            if (DateUtil.isCellDateFormatted(cellFor)) {
                                // Formatar para o padrao brasileiro
                                Date d = cellFor.getDateCellValue();
                                DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                                System.out.println(df.format(d));
                            }
                        } else {
                            // Mostrar numerico
                            System.out.println(cellFor.getNumericCellValue());
                        }
                    } else {
                        // Mostrar String
                        System.out.println(cellFor.getStringCellValue());
                    }
                } catch (Exception e) {
                    // Mostrar Erro
                    System.out.println(e.getMessage());
                }
            }
            // Mostrar pulo de linha
            System.out.println("------------------------");
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.accenture.control.ExcelDAO.java

public String[] carregaPlanilhaFuncionalidade() throws IOException, ClassNotFoundException, SQLException {

    Plano plano = new Plano();
    ManipulaDadosSQLite banco = new ManipulaDadosSQLite();
    String[] funcionalidade = null;
    try {//from www.ja  va2 s . c  om
        FileInputStream arquivo = new FileInputStream(new File(ExcelDAO.fileName));
        XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
        //setado a planilha de configuraes
        XSSFSheet sheetPlano = workbook.getSheetAt(2);
        //linha pa
        int linha = 1;
        int coluna = 4;

        funcionalidade = new String[sheetPlano.getLastRowNum()];
        int index = 0;
        for (int count = 1; count < sheetPlano.getLastRowNum(); count++) {
            Row row = sheetPlano.getRow(count);
            for (int countColuna = 0; countColuna < 1; countColuna++) {
                Cell cell = row.getCell(coluna, Row.CREATE_NULL_AS_BLANK);
                System.out.println(cell.getColumnIndex() + "-" + cell.getRowIndex());
                if (cell.getCellType() == CELL_TYPE_BLANK) {
                    System.out.println("Campo vazio");
                } else if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                    double valor = cell.getNumericCellValue();

                    System.out.println(valor);
                } else {
                    String valor = cell.getStringCellValue();
                    System.out.println(valor);
                    funcionalidade[index] = valor;
                    System.out.println(funcionalidade[index]);
                    banco.insertTabelaConf("TB_FUNCIONALIDADE", "DESC_FUNCIONALIDADE", valor);
                    index++;
                }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelDAO.class.getName()).log(Level.SEVERE, null, ex);

    }
    return funcionalidade;
}