Example usage for org.apache.poi.ss.usermodel Sheet 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:com.projectswg.tools.SwgExcelConverter.java

License:Open Source License

public SWGFile convert(Sheet sheet) {
    Row header = sheet.getRow(sheet.getFirstRowNum());
    if (header == null)
        return null;

    int headerNum = header.getRowNum();

    // Create base datatable iff
    SWGFile swgFile = new SWGFile("DTII");
    swgFile.addForm("0001");
    // Create individual iff info
    int columns = createTableColumnData(swgFile, header);

    String[] types = createTableTypeData(swgFile, sheet.getRow(headerNum + 1), columns);
    if (types == null)
        return null;

    int rows = sheet.getPhysicalNumberOfRows();
    List<DatatableRow> rowList = new ArrayList<>();
    for (int i = headerNum + 2; i < rows; i++) {
        rowList.add(getDataTableRow(sheet.getRow(i), columns, types));
    }/*from  ww w .  ja v  a  2s.c  o  m*/

    createTableRowData(swgFile, rowList);

    return swgFile;
}

---

From source file:com.qihang.winter.poi.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 *  ? Cells// w  ww  . j  av  a 2 s. c o  m
 * 
 * @param styles
 * @param rowHeight
 * @throws Exception
 */
public int createCells(Drawing patriarch, int index, Object t,
        List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, Sheet sheet,
        Workbook workbook, short rowHeight) throws Exception {
    com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity;
    Row row = sheet.createRow(index);
    row.setHeight(rowHeight);
    int maxHeight = 1, cellNum = 0;
    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 listC = 0;
            for (Object obj : list) {
                createListCells(patriarch, index + listC, cellNum, obj, entity.getList(), sheet, workbook);
                listC++;
            }
            cellNum += entity.getList().size();
            if (list != null && list.size() > maxHeight) {
                maxHeight = list.size();
            }
        } else {
            Object value = getCellValue(entity, t);
            if (entity.getType() == 1) {
                createStringCell(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);
            }
        }
    }
    // ????
    cellNum = 0;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            cellNum += entity.getList().size();
        } else if (entity.isNeedMerge()) {
            for (int i = index + 1; i < index + maxHeight; i++) {
                sheet.getRow(i).createCell(cellNum);
                sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
            }
            sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum, cellNum));
            cellNum++;
        }
    }
    return maxHeight;

}

---

From source file:com.qihang.winter.poi.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * List??Cells// w  ww. jav  a  2s.c o m
 * 
 * @param styles
 */
public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
        List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, Sheet sheet,
        Workbook workbook) throws Exception {
    com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity;
    Row row;
    if (sheet.getRow(index) == null) {
        row = sheet.createRow(index);
        row.setHeight(getRowHeight(excelParams));
    } else {
        row = sheet.getRow(index);
    }
    for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        Object value = getCellValue(entity, obj);
        if (entity.getType() == 1) {
            createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                    row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
        } else {
            createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj);
        }
    }
}

---

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

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map) throws Exception {
    deleteCell(sheet, map);/*w w  w  .  ja  v a 2s.  c om*/
    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);
            }
        }
    }
}

---

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

License:Apache License

/**
 * ,??/*  ww  w .  j av  a  2 s.c o  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() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(PoiElUtil.IF_DELETE)) {
                    if (Boolean.valueOf(PoiElUtil.eval(text
                            .substring(text.indexOf(PoiElUtil.START_STR) + 2, text.indexOf(PoiElUtil.END_STR))
                            .trim(), map).toString())) {
                        com.qihang.winter.poi.util.PoiSheetUtility.deleteColumn(sheet, i);
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

---

From source file:com.qihang.winter.poi.util.PoiSheetUtility.java

License:Apache License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * //from   ww w  .  j  a  va  2s.  c  o  m
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = 0; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}

---

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);

        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());
                            }/*  ww w.  j av a  2 s . c o m*/

                            // 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.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptGoesReadOnly() throws Exception {
    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

        Sheet s = wb.createSheet("Test");

        // Numeric formulas
        Row r1 = s.createRow(0);/* w ww.j a  va2  s  .c  o m*/
        Cell c1 = r1.createCell(0);
        Cell c2 = r1.createCell(1);
        Cell c3 = r1.createCell(2);
        Cell c4 = r1.createCell(3);

        c1.setCellValue(1);
        c2.setCellValue(2);
        c3.setCellFormula("A1+B1");
        c4.setCellFormula("(A1+B1)*B1");

        // Strings, booleans and errors
        Row r2 = s.createRow(1);
        Cell c21 = r2.createCell(0);
        Cell c22 = r2.createCell(1);
        Cell c23 = r2.createCell(2);
        Cell c24 = r2.createCell(3);

        c21.setCellValue("Testing");
        c22.setCellFormula("CONCATENATE(A2,A2)");
        c23.setCellFormula("FALSE()");
        c24.setCellFormula("A1/0");

        // Ensure the formulas are current
        eval.evaluateAll();

        // Run the excerpt
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        excerpter.excerpt(new int[] { 0 }, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(1, newwb.getNumberOfSheets());

        s = newwb.getSheetAt(0);
        r1 = s.getRow(0);
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType());

        assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001);

        r2 = s.getRow(1);
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType());

        assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue());
        assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue());
        assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue());
    }
}

---

From source file:com.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java

License:Apache License

public static Cell getCell(Sheet sheet, int colId, int rowId) {
    Row row = sheet.getRow(rowId);
    return row.getCell(colId);
}

---

From source file:com.rarediscovery.services.logic.WorkPad.java

/**
 * /*from w w  w. j  a v a2  s .co m*/
 * Add a column of data to a worksheet
 * 
 * @param sheetName
 * @param dataArray
 * 
 * @param startingRow 
 * @param dataColumn
 */
public void addColumnData(String sheetName, String[] dataArray, int startingRow, int dataColumn) {
    Sheet s = addWorksheet(sheetName);

    CellStyle style = applySelectedStyle();

    for (int r = 0; r < dataArray.length; r++) {
        Row row = null;
        // When item requested is out of range of available rows
        if (s.getLastRowNum() < startingRow + r) {
            row = s.createRow(startingRow + r);

        } else {
            row = s.getRow(startingRow + r);
        }

        row.createCell(dataColumn).setCellValue(dataArray[r]);
        row.setRowStyle(style);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »