Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:cht.Parser.java

License:Apache License

public static void main(String[] args) throws IOException {

    // TODO get from google drive
    boolean isUnicode = false;
    boolean isRemoveInputFileOnComplete = false;
    int rowNum;// w ww  .j a  v  a2 s . c om
    int colNum;
    Gson gson = new GsonBuilder().setPrettyPrinting().create();

    Properties prop = new Properties();

    try {
        prop.load(new FileInputStream("config.txt"));
    } catch (IOException ex) {
        ex.printStackTrace();
    }

    String inputFilePath = prop.getProperty("inputFile");
    String outputDirectory = prop.getProperty("outputDirectory");
    System.out.println(outputDirectory);
    // optional
    String unicode = prop.getProperty("unicode");
    String removeInputFileOnComplete = prop.getProperty("removeInputFileOnComplete");

    inputFilePath = inputFilePath.trim();
    outputDirectory = outputDirectory.trim();

    if (unicode != null) {
        isUnicode = Boolean.parseBoolean(unicode.trim());
    }
    if (removeInputFileOnComplete != null) {
        isRemoveInputFileOnComplete = Boolean.parseBoolean(removeInputFileOnComplete.trim());
    }

    Writer out = null;
    FileInputStream in = null;
    final String newLine = System.getProperty("line.separator").toString();
    final String separator = File.separator;
    try {
        in = new FileInputStream(inputFilePath);

        Workbook workbook = new XSSFWorkbook(in);

        Sheet sheet = workbook.getSheetAt(0);

        rowNum = sheet.getLastRowNum() + 1;
        colNum = sheet.getRow(0).getPhysicalNumberOfCells();

        for (int j = 1; j < colNum; ++j) {
            String outputFilename = sheet.getRow(0).getCell(j).getStringCellValue();
            // guess directory
            int slash = outputFilename.indexOf('/');
            if (slash != -1) { // has directory
                outputFilename = outputFilename.substring(0, slash) + separator
                        + outputFilename.substring(slash + 1);
            }

            String outputPath = FilenameUtils.concat(outputDirectory, outputFilename);
            System.out.println("--Writing " + outputPath);
            out = new OutputStreamWriter(new FileOutputStream(outputPath), "UTF-8");
            TreeMap<String, Object> map = new TreeMap<String, Object>();
            for (int i = 1; i < rowNum; i++) {
                try {
                    String key = sheet.getRow(i).getCell(0).getStringCellValue();
                    //String value = "";
                    Cell tmp = sheet.getRow(i).getCell(j);
                    if (tmp != null) {
                        // not empty string!
                        value = sheet.getRow(i).getCell(j).getStringCellValue();
                    }
                    if (!key.equals("") && !key.startsWith("#") && !key.startsWith(".")) {
                        value = isUnicode ? StringEscapeUtils.escapeJava(value) : value;

                        int firstdot = key.indexOf(".");
                        String keyName, keyAttribute;
                        if (firstdot > 0) {// a.b.c.d 
                            keyName = key.substring(0, firstdot); // a
                            keyAttribute = key.substring(firstdot + 1); // b.c.d
                            TreeMap oldhash = null;
                            Object old = null;
                            if (map.get(keyName) != null) {
                                old = map.get(keyName);
                                if (old instanceof TreeMap == false) {
                                    System.out.println("different type of key:" + key);
                                    continue;
                                }
                                oldhash = (TreeMap) old;
                            } else {
                                oldhash = new TreeMap();
                            }

                            int firstdot2 = keyAttribute.indexOf(".");
                            String rootName, childName;
                            if (firstdot2 > 0) {// c, d.f --> d, f
                                rootName = keyAttribute.substring(0, firstdot2);
                                childName = keyAttribute.substring(firstdot2 + 1);
                            } else {// c, d  -> d, null
                                rootName = keyAttribute;
                                childName = null;
                            }

                            TreeMap<String, Object> object = myPut(oldhash, rootName, childName);
                            map.put(keyName, object);

                        } else {// c, d  -> d, null
                            keyName = key;
                            keyAttribute = null;
                            // simple string mode
                            map.put(key, value);
                        }

                    }

                } catch (Exception e) {
                    // just ingore empty rows
                }

            }
            String json = gson.toJson(map);
            // output json
            out.write(json + newLine);
            out.close();
        }
        in.close();

        System.out.println("\n---Complete!---");
        System.out.println("Read input file from " + inputFilePath);
        System.out.println(colNum - 1 + " output files ate generated at " + outputDirectory);
        System.out.println(rowNum + " records are generated for each output file.");
        System.out.println("output file is ecoded as unicode? " + (isUnicode ? "yes" : "no"));
        if (isRemoveInputFileOnComplete) {
            File input = new File(inputFilePath);
            input.deleteOnExit();
            System.out.println("Deleted " + inputFilePath);
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (in != null) {
            in.close();
        }
    }

}

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

License:Apache License

/**
 *  ? Cells// w  w w.java2s  . c o  m
 */
public int[] createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams,
        Sheet sheet, Workbook workbook, short rowHeight, int cellNum) {
    try {
        ExcelExportEntity entity;
        Row row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);
        if (rowHeight != -1) {
            row.setHeight(rowHeight);
        }
        int maxHeight = 1, listMaxHeight = 1;
        // ????
        int margeCellNum = cellNum;
        int indexKey = createIndexCell(row, index, excelParams.get(0));
        cellNum += indexKey;
        for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
            entity = excelParams.get(k);
            if (entity.getList() != null) {
                Collection<?> list = getListCellValue(entity, t);
                int listIndex = 0, tmpListHeight = 0;
                if (list != null && list.size() > 0) {
                    int tempCellNum = 0;
                    for (Object obj : list) {
                        int[] temp = createCells(patriarch, index + listIndex, obj, entity.getList(), sheet,
                                workbook, rowHeight, cellNum);
                        tempCellNum = temp[1];
                        tmpListHeight += temp[0];
                        listIndex++;
                    }
                    cellNum = tempCellNum;
                    listMaxHeight = Math.max(listMaxHeight, tmpListHeight);
                }
            } else {
                Object value = getCellValue(entity, t);

                if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
                    createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                            index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);

                } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
                    createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                            index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
                } else {
                    createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(),
                            t);
                }
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(
                            row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value));
                }
            }
        }
        maxHeight += listMaxHeight - 1;
        if (indexKey == 1 && excelParams.get(1).isNeedMerge()) {
            excelParams.get(0).setNeedMerge(true);
        }
        for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
            entity = excelParams.get(k);
            if (entity.getList() != null) {
                margeCellNum += entity.getList().size();
            } else if (entity.isNeedMerge() && maxHeight > 1) {
                for (int i = index + 1; i < index + maxHeight; i++) {
                    if (sheet.getRow(i) == null) {
                        sheet.createRow(i);
                    }
                    sheet.getRow(i).createCell(margeCellNum);
                    sheet.getRow(i).getCell(margeCellNum).setCellStyle(getStyles(false, entity));
                }
                PoiMergeCellUtil.addMergedRegion(sheet, index, index + maxHeight - 1, margeCellNum,
                        margeCellNum);
                margeCellNum++;
            }
        }
        return new int[] { maxHeight, cellNum };
    } catch (Exception e) {
        LOGGER.error("excel cell export error ,data is :{}", ReflectionToStringBuilder.toString(t));
        LOGGER.error(e.getMessage(), e);
        throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);
    }

}

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

License:Apache License

/**
 * List??Cells/*from   ww w  . ja v a  2  s . c om*/
 */
public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
        List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight) throws Exception {
    ExcelExportEntity entity;
    Row row;
    if (sheet.getRow(index) == null) {
        row = sheet.createRow(index);
        if (rowHeight != -1) {
            row.setHeight(rowHeight);
        }
    } else {
        row = sheet.getRow(index);
        if (rowHeight != -1) {
            row.setHeight(rowHeight);
        }
    }
    for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        Object value = getCellValue(entity, obj);
        if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
            createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                    row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            if (entity.isHyperlink()) {
                row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
            createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                    index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            if (entity.isHyperlink()) {
                row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink(
                        row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value));
            }
        } else {
            createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj);
        }
    }
}

From source file:cn.afterturn.easypoi.excel.export.ExcelExportService.java

License:Apache License

/**
 * //from  w w  w  .  j  av a2 s.  c  om
 */
private int createHeaderRow(ExportParams title, Sheet sheet, Workbook workbook, int index,
        List<ExcelExportEntity> excelParams, int cellIndex) {
    Row row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);
    int rows = getRowNums(excelParams, true);
    row.setHeight(title.getHeaderHeight());
    Row listRow = null;
    if (rows >= 2) {
        listRow = sheet.createRow(index + 1);
        listRow.setHeight(title.getHeaderHeight());
    }
    int groupCellLength = 0;
    CellStyle titleStyle = getExcelExportStyler().getTitleStyle(title.getColor());
    for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {
        ExcelExportEntity entity = excelParams.get(i);
        // ?groupName???
        if (StringUtils.isBlank(entity.getGroupName()) || i == 0
                || !entity.getGroupName().equals(excelParams.get(i - 1).getGroupName())) {
            if (groupCellLength > 1) {
                sheet.addMergedRegion(
                        new CellRangeAddress(index, index, cellIndex - groupCellLength, cellIndex - 1));
            }
            groupCellLength = 0;
        }
        if (StringUtils.isNotBlank(entity.getGroupName())) {
            createStringCell(row, cellIndex, entity.getGroupName(), titleStyle, entity);
            createStringCell(listRow, cellIndex, entity.getName(), titleStyle, entity);
            groupCellLength++;
        } else if (StringUtils.isNotBlank(entity.getName())) {
            createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);
        }
        if (entity.getList() != null) {
            // ???
            int tempCellIndex = cellIndex;
            cellIndex = createHeaderRow(title, sheet, workbook, rows == 1 ? index : index + 1, entity.getList(),
                    cellIndex);
            List<ExcelExportEntity> sTitel = entity.getList();
            if (StringUtils.isNotBlank(entity.getName()) && sTitel.size() > 1) {
                PoiMergeCellUtil.addMergedRegion(sheet, index, index, tempCellIndex,
                        tempCellIndex + sTitel.size() - 1);
            }
            /*for (int j = 0, size = sTitel.size(); j < size; j++) {
                    
            createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(),
                    titleStyle, entity);
            cellIndex++;
            }*/
            cellIndex--;
        } else if (rows > 1 && StringUtils.isBlank(entity.getGroupName())) {
            createStringCell(listRow, cellIndex, "", titleStyle, entity);
            PoiMergeCellUtil.addMergedRegion(sheet, index, index + rows - 1, cellIndex, cellIndex);
        }
        cellIndex++;
    }
    if (groupCellLength > 1) {
        PoiMergeCellUtil.addMergedRegion(sheet, index, index, cellIndex - groupCellLength, cellIndex - 1);
    }
    return cellIndex;

}

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

License:Apache License

/**
 * ??,??/*w ww.j  a  v a 2 s  . co  m*/
 *
 * @param sheet
 * @return
 */
private Map<String, Integer> getTitleMap(Sheet sheet) {
    Row row = null;
    Iterator<Cell> cellTitle;
    Map<String, Integer> titlemap = new HashMap<String, Integer>();
    for (int j = 0; j < teplateParams.getHeadingRows(); j++) {
        row = sheet.getRow(j + teplateParams.getHeadingStartRow());
        cellTitle = row.cellIterator();
        int i = row.getFirstCellNum();
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = cell.getStringCellValue();
            if (!StringUtils.isEmpty(value)) {
                titlemap.put(value, i);
            }
            i = i + 1;
        }
    }
    return titlemap;

}

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

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception {
    if (sheet.getWorkbook() instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }//from w w w  . j  a  v  a2  s.  c  om
    deleteCell(sheet, map);
    mergedRegionHelper = new MergedRegionHelper(sheet);
    templateSumHandler = new TemplateSumHandler(sheet);
    if (colForeach) {
        colForeach(sheet, map);
    }
    Row row = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            if (row.getCell(i) != null
                    && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                setValueForCellByMap(row.getCell(i), map);
            }
        }
    }

    //??
    handlerSumCell(sheet);
}

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

License:Apache License

private void handlerSumCell(Sheet sheet) {
    for (TemplateSumEntity sumEntity : templateSumHandler.getDataList()) {
        Cell cell = sheet.getRow(sumEntity.getRow()).getCell(sumEntity.getCol());
        if (cell.getStringCellValue().contains(sumEntity.getSumKey())) {
            cell.setCellValue(cell.getStringCellValue().replace("sum:(" + sumEntity.getSumKey() + ")",
                    sumEntity.getValue() + ""));
        } else {//from w w  w  .j  a v a  2 s. com
            cell.setCellValue(cell.getStringCellValue() + sumEntity.getValue());
        }
    }
}

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

License:Apache License

/**
 * ,??//from   w  w  w.  j ava  2s  .  c o m
 *
 * @param sheet
 * @param map
 */
private void colForeach(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null
                    && (cell.getCellType() == CellType.STRING || cell.getCellType() == CellType.NUMERIC)) {
                String text = PoiCellUtil.getCellValue(cell);
                if (text.contains(FOREACH_COL) || text.contains(FOREACH_COL_VALUE)) {
                    foreachCol(cell, map, text);
                }
            }
        }
    }
}

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

License:Apache License

/**
 * ,??/* w ww .  j a v  a  2  s .co  m*/
 *
 * @param sheet
 * @param map
 * @throws Exception
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null
                    && (cell.getCellType() == CellType.STRING || cell.getCellType() == CellType.NUMERIC)) {
                cell.setCellType(CellType.STRING);
                String text = cell.getStringCellValue();
                if (text.contains(IF_DELETE)) {
                    if (Boolean.valueOf(
                            eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map)
                                    .toString())) {
                        PoiSheetUtil.deleteColumn(sheet, i);
                        i--;
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

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

License:Apache License

/**
 * Row//from w  w  w.ja  v a2  s. c  o m
 *
 * @param sheet
 * @param rowIndex
 * @param isCreate
 * @param rows
 * @return
 */
private Row createRow(int rowIndex, Sheet sheet, boolean isCreate, int rows) {
    for (int i = 0; i < rows; i++) {
        if (isCreate) {
            sheet.createRow(rowIndex++);
        } else if (sheet.getRow(rowIndex++) == null) {
            sheet.createRow(rowIndex - 1);
        }
    }
    return sheet.getRow(rowIndex - rows);
}