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.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

/**
 * ????//from  ww w. ja  va  2  s. c  o  m
 * @param sheet
 * @param rowIndex
 * @param style
 * @return
 */
public boolean setRowStyle(int rowIndex, CellStyle style) {
    Sheet sheet = excelSheet.sheet;
    if (sheet != null) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            short firstColumnIndex = row.getFirstCellNum();
            short lastColumnIndex = row.getLastCellNum();
            for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) {
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                Cell cell = row.getCell(colunmIndex);
                if (cell != null) {
                    cellStyle.cloneStyleFrom(cell.getCellStyle());
                    cellStyle.cloneStyleFrom(style);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    return true;
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

/**
 * ???//from w ww  .  j a  v a2s .c  o m
 * @param sheet
 * @param rowIndex
 * @param columnIndex
 * @param style
 * @return
 */
public boolean setCellStyle(int rowIndex, int columnIndex, CellStyle style) {
    Sheet sheet = excelSheet.sheet;
    if (sheet == null)
        return false;
    if (rowIndex < 0 || columnIndex <= 0)
        return false;

    Cell cell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (cell == null)
        return false;

    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    newCellStyle.cloneStyleFrom(cell.getCellStyle());
    newCellStyle.cloneStyleFrom(style);
    cell.setCellStyle(newCellStyle);
    return true;
}

---

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java.//from  w  w  w .  ja va 2  s .  co m
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, List<String> titleList, Class<T> beanType)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // ??Workbook
    Workbook wb = getWorkbookByFileContentType(uploadFileName);

    // ?
    List<T> datas = new ArrayList<T>();

    // ????
    ClassInfo classInfo = ClassUtil.getClassInfo(beanType);

    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(0);
    int colNum = titleRow.getLastCellNum();

    for (int i = 2; i <= rowNum; i++) {
        Row row = sheet.getRow(i);

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();

        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titleList.get(j));
            if (reflexField == null)
                continue;
            if (cell != null) {

                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String dd = row.getCell(j).getStringCellValue().trim();

                if (StringUtil.isNotEmpty(dd)) {

                    // ??beanExcel??
                    if (reflexField.getPropertyType().getName().equals("java.sql.Date")) {
                        // 
                        Date date = sdf.parse(dd);
                        reflexField.setValue(retObject, new java.sql.Date(date.getTime()));
                    } else {

                        reflexField.setValue(retObject,
                                ValueObjectUtil.typeCast(dd, reflexField.getPropertyType()));
                    }

                }
            }

        }

        datas.add(retObject);
    }

    return datas;
}

---

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java.//from  w w  w .j  a v  a 2 s  .c o m
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, int titlerow, int datarow, Class<T> beanType)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // ??Workbook
    Workbook wb = getWorkbookByFileContentType(uploadFileName);

    // ?
    List<T> datas = new ArrayList<T>();

    // ????
    ClassInfo classInfo = ClassUtil.getClassInfo(beanType);
    PropertieDescription rowidField = classInfo.getPropertyDescriptor("rowid");
    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(titlerow);

    int colNum = titleRow.getLastCellNum();
    String[] titles = new String[colNum];
    for (int i = 0; i < colNum; i++) {
        titles[i] = titleRow.getCell(i).getStringCellValue().trim();
    }

    for (int i = datarow; i <= rowNum; i++) {
        Row row = sheet.getRow(i);

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();
        if (rowidField != null)
            rowidField.setValue(retObject, i);
        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titles[j]);
            if (reflexField == null)
                continue;
            if (cell != null) {

                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String dd = row.getCell(j).getStringCellValue().trim();

                if (StringUtil.isNotEmpty(dd)) {

                    // ??beanExcel??
                    if (reflexField.getPropertyType().getName().equals("java.sql.Date")) {
                        // 
                        Date date = sdf.parse(dd);
                        reflexField.setValue(retObject, new java.sql.Date(date.getTime()));
                    } else {

                        reflexField.setValue(retObject,
                                ValueObjectUtil.typeCast(dd, reflexField.getPropertyType()));
                    }

                }
            }

        }

        datas.add(retObject);
    }

    return datas;
}

---

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to update the embedded Excel workbook. As the format and structire
 * of the workbook are known in advance, all this code attempts to do is
 * write a new value into the first cell on the first row of the first
 * worksheet. Prior to executing this method, that cell will contain the
 * value 1./*from w  ww. j a  va  2  s .c om*/
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {

                // Get an InputStream from the pacage part and pass that
                // to the create method of the WorkbookFactory class. Update
                // the resulting Workbook and then stream that out again
                // using an OutputStream obtained from the same PackagePart.
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                cell.setCellValue(NEW_VALUE);
                workbook.write(pPart.getOutputStream());
            }
        }

        // Finally, write the newly modified Word document out to file.
        this.doc.write(new FileOutputStream(this.docFile));
    }
}

---

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to test whether or not the embedded workbook was correctly
 * updated. This method simply recovers the first cell from the first row
 * of the first workbook and tests the value it contains.
 * <p/>//from   w  w w. ja va  2s .  com
 * Note that execution will not continue up to the assertion as the
 * embedded workbook is now corrupted and causes an IllegalArgumentException
 * with the following message
 * <p/>
 * <em>java.lang.IllegalArgumentException: Your InputStream was neither an
 * OLE2 stream, nor an OOXML stream</em>
 * <p/>
 * to be thrown when the WorkbookFactory.createWorkbook(InputStream) method
 * is executed.
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void checkUpdatedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                //assertEquals(cell.getNumericCellValue(), NEW_VALUE, 0.0001);
            }
        }
    }
}

---

From source file:com.ggvaidya.scinames.ui.DatasetImporterController.java

License:Open Source License

private void displayPreview() {
    filePreviewTextArea.setText("");
    if (currentFile == null)
        return;//  w ww  . j a v  a2 s .  co m

    if (currentFile.getName().endsWith("xls") || currentFile.getName().endsWith("xlsx")) {
        // Excel files are special! We need to load it special and then preview it.
        ExcelImporter imp;

        String excelPreviewText;
        try {
            imp = new ExcelImporter(currentFile);
            List<Sheet> sheets = imp.getWorksheets();

            StringBuffer preview = new StringBuffer();
            preview.append("Excel file version " + imp.getWorkbook().getSpreadsheetVersion() + " containing "
                    + sheets.size() + " sheets.\n");
            for (Sheet sh : sheets) {
                preview.append(
                        " - " + sh.getSheetName() + " contains " + sh.getPhysicalNumberOfRows() + " rows.\n");

                // No rows?
                if (sh.getPhysicalNumberOfRows() == 0)
                    continue;

                // Header row?
                Row headerRow = sh.getRow(0);
                boolean headerEmitted = false;

                for (int rowIndex = 1; rowIndex < sh.getPhysicalNumberOfRows(); rowIndex++) {
                    if (rowIndex >= 10)
                        break;

                    Row row = sh.getRow(rowIndex);

                    if (!headerEmitted) {
                        preview.append(
                                "  - " + String.join("\t", ExcelImporter.getCellsAsValues(headerRow)) + "\n");
                        headerEmitted = true;
                    }
                    preview.append("  - " + String.join("\t", ExcelImporter.getCellsAsValues(row)) + "\n");
                }

                preview.append("\n");
            }

            excelPreviewText = preview.toString();
        } catch (IOException ex) {
            excelPreviewText = "Could not open '" + currentFile + "': " + ex;
        }

        filePreviewTextArea.setText(excelPreviewText);

        return;
    }

    // If we're here, then this is some sort of text file, so let's preview the text content directly.
    try {
        LineNumberReader reader = new LineNumberReader(new BufferedReader(new FileReader(currentFile)));

        // Load the first ten lines.
        StringBuffer head = new StringBuffer();
        for (int x = 0; x < 10; x++) {
            head.append(reader.readLine());
            head.append('\n');
        }

        reader.close();
        filePreviewTextArea.setText(head.toString());
    } catch (IOException ex) {
        filePreviewTextArea.setBackground(BACKGROUND_RED);
        filePreviewTextArea.setText("ERROR: Could not load file '" + currentFile + "': " + ex);
    }
}

---

From source file:com.github.camaral.sheeco.Sheeco.java

License:Apache License

private Row createRow(final Sheet sheet) {
    sheet.createRow(0);
    final Row row = sheet.getRow(0);
    return row;
}

---

From source file:com.github.camaral.sheeco.SheecoTest.java

License:Apache License

private void assertCatHeaders(Workbook wb) {
    Sheet sheet = wb.getSheet("Cat");
    Assert.assertNotNull(sheet, "Sheet must be created");
    Row row = sheet.getRow(0);
    Assert.assertNotNull(sheet, "Header row must be created");
    assertHeader(row.getCell(0), "name");
    assertHeader(row.getCell(1), "Male?");
    assertHeader(row.getCell(2), "Birth date");
    assertHeader(row.getCell(3), "hairLength");
    assertHeader(row.getCell(4), "hairColor");
    assertHeader(row.getCell(5), "hairLength");
    assertHeader(row.getCell(6), "hairColor");
}

---

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine, int limitLine,
        int sheetIndex) throws Excel4JException {

    Sheet sheet = workbook.getSheetAt(sheetIndex);
    Row row = sheet.getRow(offsetLine);
    List<T> list = new ArrayList<>();
    Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz);
    if (maps == null || maps.size() <= 0)
        throw new Excel4jReadException(
                "The Excel format to read is not correct, and check to see if the appropriate rows are set");
    long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : sheet.getLastRowNum();//from w  w w  .j  a v a 2  s . c om

    for (int i = offsetLine + 1; i <= maxLine; i++) {
        row = sheet.getRow(i);
        if (null == row)
            continue;
        T obj;
        try {
            obj = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            throw new Excel4JException(e);
        }
        for (Cell cell : row) {
            int ci = cell.getColumnIndex();
            ExcelHeader header = maps.get(ci);
            if (null == header)
                continue;
            String val = Utils.getCellValue(cell);
            Object value;
            String filed = header.getFiled();
            // ??
            if (null != header.getReadConverter()
                    && header.getReadConverter().getClass() != DefaultConvertible.class) {
                value = header.getReadConverter().execRead(val);
            } else {
                // ?
                value = Utils.str2TargetClass(val, header.getFiledClazz());
            }
            Utils.copyProperty(obj, filed, value);
        }
        list.add(obj);
    }
    return list;
}

---

• «
• 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
• »