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

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

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

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void setForEeachCellValue(boolean isCreate, Row row, int columnIndex, Object t,
        List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns, Map<String, Object> map)
        throws Exception {
    for (int i = 0, max = columnIndex + columns.size(); i < max; i++) {
        if (row.getCell(i) == null)
            row.createCell(i);//from  w  w w. j a  v  a  2 s.  com
    }
    for (int i = 0, max = columns.size(); i < max; i++) {
        boolean isNumber = false;
        String tempStr = new String(columns.get(i).getName());
        if (isNumber(tempStr)) {
            isNumber = true;
            tempStr = tempStr.replace(PoiElUtil.NUMBER_SYMBOL, "");
        }
        map.put(teplateParams.getTempParams(), t);
        String val = PoiElUtil.eval(tempStr, map).toString();
        if (isNumber && StringUtils.isNotEmpty(val)) {
            row.getCell(i + columnIndex).setCellValue(Double.parseDouble(val));
            row.getCell(i + columnIndex).setCellType(Cell.CELL_TYPE_NUMERIC);
        } else {
            row.getCell(i + columnIndex).setCellValue(val);
        }
        row.getCell(i + columnIndex).setCellStyle(columns.get(i).getCellStyle());
        tempCreateCellSet.add(row.getRowNum() + "_" + (i + columnIndex));
    }

}

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/***
 * ?List?//www .j a va 2 s . com
 *
 * @param exclusions
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object,
        com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param, Row row,
        Map<Integer, String> titlemap, String targetId, Map<String, PictureData> pictures,
        com.qihang.winter.poi.excel.entity.ImportParams params) throws Exception {
    Collection collection = (Collection) com.qihang.winter.poi.util.PoiPublicUtil
            .getMethod(param.getName(), object.getClass()).invoke(object, new Object[] {});
    Object entity = com.qihang.winter.poi.util.PoiPublicUtil.createObject(param.getType(), targetId);
    String picId;
    boolean isUsed = false;// ??
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);
        String titleString = (String) titlemap.get(i);
        if (param.getExcelParams().containsKey(titleString)) {
            if (param.getExcelParams().get(titleString).getType() == 2) {
                picId = row.getRowNum() + "_" + i;
                saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
            } else {
                saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
            }
            isUsed = true;
        }
    }
    if (isUsed) {
        collection.add(entity);
    }
}

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass,
        com.qihang.winter.poi.excel.entity.ImportParams params, Map<String, PictureData> pictures)
        throws Exception {
    List collection = new ArrayList();
    Map<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity> excelParams = new HashMap<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity>();
    List<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams> excelCollection = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams>();
    String targetId = null;//from www  .j a v a2  s.c  o  m
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = com.qihang.winter.poi.util.PoiPublicUtil.getClassFields(pojoClass);
        com.qihang.winter.poi.excel.annotation.ExcelTarget etarget = pojoClass
                .getAnnotation(com.qihang.winter.poi.excel.annotation.ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        row = rows.next();
        // ???,?,?
        if ((row.getCell(params.getKeyIndex()) == null
                || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
            for (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) {
                addListContinue(object, param, row, titlemap, targetId, pictures, params);
            }
        } else {
            object = com.qihang.winter.poi.util.PoiPublicUtil.createObject(pojoClass, targetId);
            try {
                for (int i = row.getFirstCellNum(), le = row.getLastCellNum(); i < le; i++) {
                    Cell cell = row.getCell(i);
                    String titleString = (String) titlemap.get(i);
                    if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
                        if (excelParams.get(titleString) != null
                                && excelParams.get(titleString).getType() == 2) {
                            picId = row.getRowNum() + "_" + i;
                            saveImage(object, picId, excelParams, titleString, pictures, params);
                        } else {
                            saveFieldValue(params, object, cell, excelParams, titleString, row);
                        }
                    }
                }

                for (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                }
                collection.add(object);
            } catch (com.qihang.winter.poi.exception.excel.ExcelImportException e) {
                if (!e.getType()
                        .equals(com.qihang.winter.poi.exception.excel.enums.ExcelImportEnum.VERIFY_ERROR)) {
                    throw new com.qihang.winter.poi.exception.excel.ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}

From source file:com.qualogy.qafe.service.DocumentServiceImpl.java

License:Apache License

private DocumentOutput handleExcelData(Sheet sheetData, boolean hasRowHeader) {
    DocumentOutput docOutput = new DocumentOutput();

    // Determine the column names
    List<String> columnNameList = new ArrayList<String>();
    if (sheetData.rowIterator().hasNext()) {
        Row row = sheetData.rowIterator().next();
        int emptyColCountChain = 0;
        String colName = null;//from www  .ja v a 2 s.  c  om
        for (Iterator<Cell> itr = row.cellIterator(); itr.hasNext();) {
            Cell cell = itr.next();
            boolean cellHasData = (cell.getCellType() != Cell.CELL_TYPE_BLANK);
            if (hasRowHeader && cellHasData) {
                colName = getCellValue(cell);
            } else {
                colName = DEFAULT_FIELD_NAME + cell.getColumnIndex();
            }
            columnNameList.add(colName);

            if (cellHasData) {
                emptyColCountChain = 0;
            } else {
                emptyColCountChain++;
            }
            if (emptyColCountChain > EMPTY_NUMCOLUMNS_TOLERANCE) {
                break;
            }
        }
    }

    // Get the data from sheet
    List<Map<String, String>> data = new ArrayList<Map<String, String>>();
    boolean[] columnsHaveData = new boolean[columnNameList.size()];
    for (Iterator<Row> itr = sheetData.rowIterator(); itr.hasNext();) {
        Row row = itr.next();
        if (hasRowHeader && (row.getRowNum() == 0)) {
            continue;
        }
        Map<String, String> rowData = new LinkedHashMap<String, String>();
        boolean rowHasData = false;
        for (Iterator<Cell> itr2 = row.cellIterator(); itr2.hasNext();) {
            Cell cell = itr2.next();
            if (cell.getColumnIndex() < columnNameList.size()) {
                String colName = columnNameList.get(cell.getColumnIndex());
                String cellValue = null;
                if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cellValue = getCellValue(cell);
                }
                boolean cellHasData = ((cellValue != null) && (cellValue.length() > 0));
                columnsHaveData[cell.getColumnIndex()] = columnsHaveData[cell.getColumnIndex()] || cellHasData;
                rowHasData = rowHasData || cellHasData;
                rowData.put(colName, cellValue);
            } else {
                break;
            }
        }
        if (rowHasData) {
            data.add(rowData);
        }
    }

    removeEmptyColumns(columnNameList, data, columnsHaveData);

    printData(data);
    docOutput.setData(data);
    return docOutput;
}

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body.// w ww .j a  v a2  s.  c  o m
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private void merge(Workbook excerptWB, Workbook fullWB, String[] sheetsToMerge, OutputStream output)
        throws IOException {
    // Identify the sheets in both workbooks
    List<Sheet> sourceSheets = identifySheets(sheetsToMerge, excerptWB);
    List<Sheet> destSheets = identifySheets(sheetsToMerge, fullWB);

    // Process each sheet from the excerpt in turn
    for (int i = 0; i < sheetsToMerge.length; i++) {
        Sheet source = sourceSheets.get(i);
        Sheet dest = destSheets.get(i);/*  w  ww . ja  v a2 s.  c  o m*/

        for (Row srcR : source) {
            for (Cell srcC : srcR) {
                if (srcC.getCellType() == Cell.CELL_TYPE_FORMULA
                        || srcC.getCellType() == Cell.CELL_TYPE_ERROR) {
                    // Don't merge these kinds of cells
                } else {
                    Row destR = dest.getRow(srcR.getRowNum());
                    if (destR == null) {
                        // Newly added row to the excerpt file, skip this
                    } else {
                        Cell destC = destR.getCell(srcC.getColumnIndex());
                        if (destC == null && srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                            // Both are empty, don't need to do anything
                        } else {
                            if (destC == null)
                                destC = destR.createCell(srcC.getColumnIndex(), srcC.getCellType());

                            // Sync contents
                            if (srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                                destC.setCellType(Cell.CELL_TYPE_BLANK);
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                destC.setCellValue(srcC.getBooleanCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                destC.setCellValue(srcC.getNumericCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_STRING) {
                                destC.setCellValue(srcC.getStringCellValue());
                            }

                            // Sync formatting rules
                            // TODO
                        }
                    }
                }
            }
        }
    }

    // Re-evaluate all the formulas in the destination workbook, now that
    //  we have updated cells in it
    FormulaEvaluator eval = fullWB.getCreationHelper().createFormulaEvaluator();
    eval.evaluateAll();

    // Save the new file
    fullWB.write(output);
}

From source file:com.radaee.excel.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();//from   w ww .  j  a v a2s.  c  om

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    //                        CellFormat cf = CellFormat.getInstance(
                    //                                style.getDataFormatString());
                    //                        CellFormatResult result = cf.apply(cell);
                    //                        content = result.text;
                    content = getText(cell);

                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

From source file:com.read.main.LeerPDF.java

/**
 * @param args the command line arguments
 *//* w w w  .j  a v  a 2s . c om*/
public static void main(String[] args) throws IOException {
    try {

        FileInputStream file = new FileInputStream(new File("/home/aaron/Escritorio/Example.xlsx"));
        XSSFWorkbook workbook2 = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook2.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            System.out.println("Numero de Columnas: " + row.getLastCellNum());

            System.out.println(row.getRowNum());

            if (row.getRowNum() == 0) {
                System.out.println("Fila Cero");
            } else {

                int numColumna = 0;

                while (numColumna < row.getLastCellNum()) {

                    Cell cell = row.getCell(numColumna);

                    try {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(numColumna + ".- BOOLEAN: ");
                            System.out.print(cell.getBooleanCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(numColumna + ".- NUMERIC: ");
                            System.out.print(cell.getNumericCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(numColumna + ".- STRING: ");
                            System.out.print(cell.getStringCellValue() + "\t\t");
                            break;
                        }
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                    ;

                    numColumna++;
                }
            }

            System.out.println("");
        }
        file.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.runwaysdk.dataaccess.io.ExcelImporter.java

License:Open Source License

/**
 * Reads a row and captures and Exceptions or Problems associated with it
 * //from   w  w w.j  a va 2 s  .c  o  m
 * @param context
 *          TODO
 * @param row
 */
@Transaction
private void readRow(ImportContext context, Row row) {
    if (!rowHasValues(row)) {
        return;
    }

    int previousErrorCount = context.getErrorCount();

    try {
        context.readRow(row, this.log);
    } catch (Exception e) {
        RunwayLogUtil.logToLevel(LogLevel.ERROR, "Excel import exception", e);

        context.addException(e);
    }

    // Loop over any problems we encountered and wrap them as ExcelProblems
    List<ProblemIF> problemsInTransaction = RequestState.getProblemsInCurrentRequest();

    for (ProblemIF problem : problemsInTransaction) {
        if (isEmptyValueProblem(context, problem, row.getRowNum() + 1))
            continue;

        context.addProblem(problem);
    }
    // We've rewrapped and stored these problems, so clear them out of the
    // Session buffer
    problemsInTransaction.clear();

    // If there are new problems, then this row has failed. Append it to the
    // error file
    if (previousErrorCount != context.getErrorCount()) {
        context.addErrorRow(row);

        // We don't want the transaction to commit, so this is thrown to ensure
        // that it doesn't. It gets caught one layer up.
        throw new StopTransactionException();
    }
}

From source file:com.safeway.app.appcert.util.smoketester.TestCaseReader.java

public List<TestScriptTemplate> readExcel() throws Exception {

    FileInputStream file = new FileInputStream(
            new File("C:\\Users\\nbret00\\Documents\\SeleniumSmokeTest\\TestCases.xlsx"));

    List<TestScriptTemplate> tstList = new ArrayList<TestScriptTemplate>();
    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();

    List<TestScriptTemplate> TestScriptTemplateList = new ArrayList();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        //skip until row 5
        if (row.getRowNum() > 4) {

            TestScriptTemplate tscripttemp = new TestScriptTemplate();

            //Cell appcode = row.getCell(0); //this should be the item # on the list
            //System.out.println("application name #: "+itemnum.getStringCellValue());
            tscripttemp.setAppCode(getCellValueStr(row.getCell(1)));
            tscripttemp.setAppURL(getCellValueStr(row.getCell(2)));
            tscripttemp.setAppUserID(getCellValueStr(row.getCell(3)));
            tscripttemp.setAppPassword(getCellValueStr(row.getCell(4)));
            tscripttemp.setHomePageTitle(getCellValueStr(row.getCell(5)));
            tscripttemp.setHomePageElementType(getCellValueStr(row.getCell(6)));
            tscripttemp.setHomePageElement(getCellValueStr(row.getCell(7)));
            tscripttemp.setLevel1URL(getCellValueStr(row.getCell(8)));
            tscripttemp.setLevel1PageTitle(getCellValueStr(row.getCell(9)));
            tscripttemp.setLevel1ElementType(getCellValueStr(row.getCell(10)));
            tscripttemp.setLevel1Element(getCellValueStr(row.getCell(11)));

            TestScriptTemplateList.add(tscripttemp);
            System.out.println("this to string: " + tscripttemp.toString());
        }/*from w w w .  j  av a2 s .  com*/

    }
    file.close();
    return TestScriptTemplateList;
}