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:cherry.parser.worksheet.SheetBasedParser.java

License:Apache License

private TypeDef parseSheet(Sheet sheet) {

    State state = State.HEAD;
    int coldefFirstCellNum = -1;
    Map<Integer, String> coldef = new TreeMap<Integer, String>();

    TypeDef typeDef = new TypeDef();
    typeDef.setSheetName(sheet.getSheetName());
    for (Row row : sheet) {

        int firstCellNum = row.getFirstCellNum();
        if (firstCellNum < 0) {
            continue;
        }//w w w  .j  av  a 2s  .c o  m

        if (state == State.HEAD) {

            String directive = getCellValueAsString(row.getCell(firstCellNum));
            if ("##FQCN".equals(directive)) {

                String fqcn = getCellValueAsString(row.getCell(firstCellNum + 1));
                typeDef.setFullyQualifiedClassName(fqcn);
            } else if ("##ATTR".equals(directive)) {

                String key = getCellValueAsString(row.getCell(firstCellNum + 1));
                String value = getCellValueAsString(row.getCell(firstCellNum + 2));
                typeDef.put(key, value);
            } else if ("##COLDEF".equals(directive)) {

                for (Cell cell : row) {
                    if (cell.getColumnIndex() == firstCellNum) {
                        continue;
                    }
                    if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                        continue;
                    }
                    coldef.put(cell.getColumnIndex(), getCellValueAsString(cell));
                }

                coldefFirstCellNum = firstCellNum;
                state = State.ITEM;
            } else {
                // IGNORE UNKNOWN DIRECTIVES
            }
        } else if (state == State.ITEM) {

            ItemDef item = null;
            for (Cell cell : row) {

                if (cell.getColumnIndex() == coldefFirstCellNum) {
                    item = new ItemDef();
                    continue;
                }
                if (item == null) {
                    continue;
                }
                String key = coldef.get(cell.getColumnIndex());
                if (key == null) {
                    continue;
                }

                String value = getCellValueAsString(cell);
                if (value != null) {
                    item.put(key, value);
                }
            }

            if (item != null) {
                typeDef.getItemDef().add(item);
            }
        } else {
            // IGNORE UNKNOWN STATE
        }
    }

    return typeDef;
}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell/* w  ww  .  j  a  va2 s.co m*/
 */
public void createImageCell(Cell cell, double height, String imagePath, byte[] data) throws Exception {
    if (height > cell.getRow().getHeight()) {
        cell.getRow().setHeight((short) height);
    }
    ClientAnchor anchor;
    if (type.equals(ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
    }
    if (StringUtils.isNotEmpty(imagePath)) {
        data = ImageCache.getImage(imagePath);
    }
    if (data != null) {
        PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
    }

}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell//from  w  w w.j  a  v a2 s.c  o  m
 */
public void createImageCell(Cell cell, double height, int rowspan, int colspan, String imagePath, byte[] data)
        throws Exception {
    if (height > cell.getRow().getHeight()) {
        cell.getRow().setHeight((short) height);
    }
    ClientAnchor anchor;
    if (type.equals(ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);
    }
    if (StringUtils.isNotEmpty(imagePath)) {
        data = ImageCache.getImage(imagePath);
    }
    if (data != null) {
        PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
    }

}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * foreach?/*  w  w  w  .  j a v  a 2s  .  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.getRowIndex() + rowspan < cell.getRow().getSheet().getLastRowNum()) {
        int lastRowNum = cell.getRow().getSheet().getLastRowNum();
        cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, lastRowNum,
                (datas.size() - 1) * rowspan, true, true);
        mergedRegionHelper.shiftRows(cell.getSheet(), cell.getRowIndex() + rowspan,
                (datas.size() - 1) * rowspan);
        templateSumHandler.shiftRows(cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan);
        PoiExcelTempUtil.reset(cell.getSheet(), cell.getRowIndex() + rowspan + (datas.size() - 1) * rowspan,
                cell.getRow().getSheet().getLastRowNum());
    }
    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.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * /*from w  w w.  j av  a2  s  .  c o m*/
 *
 * @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);
        if (cell.getRow().getCell(cell.getColumnIndex() + colspan) == null) {
            cell.getRow().createCell(cell.getColumnIndex() + colspan);
        }
        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.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ?Cell??set//from   w ww  . j a v  a  2 s  .c om
 *
 * @param cell
 * @param map
 */
private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
    CellType cellType = cell.getCellType();
    if (cellType != CellType.STRING && cellType != CellType.NUMERIC) {
        return;
    }
    String oldString;
    oldString = cell.getStringCellValue();
    if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) {
        // step 2. ???
        String params = null;
        boolean isNumber = false;
        if (isNumber(oldString)) {
            isNumber = true;
            oldString = oldString.replaceFirst(NUMBER_SYMBOL, "");
        }
        boolean isStyleBySelf = false;
        if (isStyleBySelf(oldString)) {
            isStyleBySelf = true;
            oldString = oldString.replaceFirst(NUMBER_SYMBOL, "");
        }
        Object obj = PoiPublicUtil.getRealValue(oldString, map);
        // ,// 
        if (obj instanceof ImageEntity) {
            ImageEntity img = (ImageEntity) obj;
            cell.setCellValue("");
            if (img.getRowspan() > 1 || img.getColspan() > 1) {
                img.setHeight(0);
                PoiMergeCellUtil.addMergedRegion(cell.getSheet(), cell.getRowIndex(),
                        cell.getRowIndex() + img.getRowspan() - 1, cell.getColumnIndex(),
                        cell.getColumnIndex() + img.getColspan() - 1);
            }
            createImageCell(cell, img.getHeight(), img.getRowspan(), img.getColspan(), img.getUrl(),
                    img.getData());
        } else if (isNumber && StringUtils.isNotBlank(obj.toString())) {
            cell.setCellValue(Double.parseDouble(obj.toString()));
        } else {
            cell.setCellValue(obj.toString());
        }
    }
    //foreach ?
    if (oldString != null && oldString.contains(FOREACH)) {
        addListDataToExcel(cell, map, oldString.trim());
    }

}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ??//from w  ww  .  j a va2s . c  o m
 *
 * @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 (index < row.getLastCellNum()) {
            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));
                //cell(????)
                int lastCellColspan = columns.get(columns.size() - 1).getColspan();
                for (int i = 1; i < lastCellColspan; i++) {
                    //??,???,,?
                    columns.add(null);
                }
                break;
            } else if (cellStringString.contains(WRAP)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
                //??,??
                colspan = index - startIndex + 1;
                index = startIndex - columns.get(columns.size() - 1).getColspan();
                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.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ???//from w w w .j a v  a 2 s  .  c o  m
 *
 * @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(templateSumHandler.isSumKey(params.getName()));
    return params;
}

From source file:cn.afterturn.easypoi.excel.html.css.impl.WidthCssConverImpl.java

License:Apache License

@Override
public void convertToExcel(Cell cell, CellStyle cellStyle, CellStyleEntity style) {
    if (StringUtils.isNoneBlank(style.getWidth())) {
        int width = (int) Math.round(PoiCssUtils.getInt(style.getWidth()) * 2048 / 8.43F);
        Sheet sheet = cell.getSheet();/*from   w  ww.  j  a  v a2  s .  c o m*/
        int colIndex = cell.getColumnIndex();
        if (width > sheet.getColumnWidth(colIndex)) {
            if (width > 255 * 256) {
                width = 255 * 256;
            }
            sheet.setColumnWidth(colIndex, width);
        }
    }
}

From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

/**
 * ????/*from  w  ww  .j a v  a2 s .com*/
 */
private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,
        List<ExcelCollectionParams> excelCollection, Map<String, ExcelImportEntity> excelParams) {
    Map<Integer, String> titlemap = new LinkedHashMap<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);
            value = value.replace("\n", "");
            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 != null
                        && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {
                    titlemap.put(i, collectionName + "_" + value);
                } else {
                    collectionName = null;
                    collectionParams = null;
                }
                if (StringUtils.isEmpty(collectionName)) {
                    titlemap.put(i, value);
                }
            }
        }
    }

    // ?
    Set<String> keys = excelParams.keySet();
    for (String key : keys) {
        if (key.startsWith("FIXED_")) {
            String[] arr = key.split("_");
            titlemap.put(Integer.parseInt(arr[1]), key);
        }
    }
    return titlemap;
}