Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

Introduction

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

Prototype

@Override
public XSSFRow getRow(int rownum) 

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporterImporterTest.java

License:Open Source License

@Test
public void test() throws Exception {
    AppUser appUser = authHelper.getCurrentUser();
    Service service = ajouterServiceToAppUser(appUser);
    List<ArticleStock> listeArticleStock = genererListeArticleStock(service);

    File exportFile = File.createTempFile("stock_test_export-", ".xlsx");
    logger.info("Export pour inventaire : " + exportFile);

    // Export//from   w w w  .  ja  v a 2  s  . c  om
    StockSpreadsheetExporter.exportToXls(service, listeArticleStock, catalogueService,
            new FileOutputStream(exportFile));
    XSSFWorkbook workbookExport = new XSSFWorkbook(exportFile);
    XSSFSheet worksheet = workbookExport.getSheet("Inventaire");
    Assert.assertNotNull(worksheet);
    // Verif
    for (int i = 0; i < worksheet.getLastRowNum(); i++) {
        XSSFRow row = worksheet.getRow(i);
        int col = 0;
        if (i == 0) {
            Assert.assertEquals("Photo", row.getCell(col++).getStringCellValue());
            Assert.assertEquals("Rfrence", row.getCell(col++).getStringCellValue());
            Assert.assertEquals("Libell", row.getCell(col++).getStringCellValue());
            Assert.assertEquals("Stock\n Appock", row.getCell(col++).getStringCellValue());
            Assert.assertEquals("Stock\n rel", row.getCell(col++).getStringCellValue());
        } else {
            col++;
            Assert.assertEquals("REF_" + (i - 1), row.getCell(col++).getStringCellValue());
            Assert.assertEquals("ARTICLE CATALOGUE " + (i - 1), row.getCell(col++).getStringCellValue());
            Assert.assertEquals(i, (long) row.getCell(col++).getNumericCellValue());
        }
    }

    // Import : toutes les quantits ont t augments de 1
    File importFile1 = File.createTempFile("stock_test_import1-", ".xlsx");
    logger.info("Import de l'inventaire : " + importFile1);
    OutputStream outputStream1 = new FileOutputStream(importFile1);
    XSSFWorkbook workbookImport = new XSSFWorkbook(exportFile);
    XSSFSheet worksheetImport = workbookImport.getSheet("Inventaire");
    for (int i = 1; i < worksheetImport.getLastRowNum() + 1; i++) {
        XSSFRow row = worksheetImport.getRow(i);
        if (i == 5) {
            // Cellule en erreur
            row.getCell(4).setCellValue(" 05 ");
        } else {
            row.getCell(4).setCellValue(row.getCell(3).getNumericCellValue() + 1);
        }
    }
    workbookImport.write(outputStream1);
    outputStream1.flush();
    outputStream1.close();
    List<String> warnings = StockSpreadsheetImporter.importFromXls(service, stockService,
            new FileInputStream(importFile1));
    // Verif
    Stock stock = stockService.findOne(service.getStock().getId());
    Assert.assertEquals(10, stock.getListeArticleStock().size());
    Assert.assertEquals(1, warnings.size());
    logger.info("Warning message : " + warnings.get(0));
    for (int i = 0; i < stock.getListeArticleStock().size(); i++) {
        ArticleStock articleStock = stock.getListeArticleStock().get(i);
        int oldQunatite = i + 1;
        if (i == 4) {
            // Quantit inchange
            Assert.assertEquals(oldQunatite, (int) articleStock.getQuantiteStock());
        } else {
            Assert.assertEquals(oldQunatite + 1, (int) articleStock.getQuantiteStock());
        }
    }
}

---

From source file:nc.noumea.mairie.appock.util.StockSpreadsheetImporter.java

License:Open Source License

private static void traiterLigne(int ligne, XSSFSheet worksheet, Service service, StockService stockService)
        throws ImportExcelException {
    XSSFCell referenceCell = worksheet.getRow(ligne).getCell(1);
    if (StringUtils.isBlank(referenceCell.getRawValue())) {
        throw new ImportExcelException(ligne + 1, "La rfrence est introuvable");
    }/*from www.  ja  v  a 2s.c  o  m*/
    String reference = referenceCell.getStringCellValue();

    XSSFCell stockReelCell = worksheet.getRow(ligne).getCell(4);
    if (StringUtils.isBlank(stockReelCell.getRawValue())) {
        throw new ImportExcelException(ligne + 1, reference, "La quantit relle n'est pas renseigne");
    }

    if (stockReelCell.getCellTypeEnum() != CellType.NUMERIC) {
        throw new ImportExcelException(ligne + 1, reference, "La cellule 'Stock rl' n'est pas un entier");
    }
    double value = stockReelCell.getNumericCellValue();
    int stockReel = (int) value;
    if (value != stockReel) {
        throw new ImportExcelException(ligne + 1, reference, "La cellule 'Stock rl' n'est pas un entier");
    }
    updateStock(service, reference, stockReel, stockService, ligne);
}

---

From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadXlsxExcelJob.java

License:Open Source License

private void loadExcel(final String file) {

    final File fil = new File(file);
    if (fil.exists()) {
        canRead = true;/*from   www. j a  v a 2 s  .co m*/
        if (grid != null) {
            try {
                InputStream inp = new FileInputStream(file);
                try {
                    wb = new XSSFWorkbook(inp);
                } catch (Exception e) {
                    MsgDialog.message("Wrong format!\nOnly Excel *.xlsx (2007-2010) is supported!");
                    canRead = false;
                    e.printStackTrace();
                }
                // wb = new HSSFWorkbook(inp);
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            if (canRead) {
                for (s = 0; s < wb.getNumberOfSheets(); s++) {
                    Display display = PlatformUI.getWorkbench().getDisplay();
                    display.syncExec(new Runnable() {

                        public void run() {

                            String name = fil.getName();
                            grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name);
                            SampleView.setGrid(grid);
                            XSSFSheet sheet = wb.getSheetAt(s);
                            int colCount = grid.getColumnCount();
                            int rowCount = grid.getItemCount();
                            int exelRow = endOfRow(sheet);
                            int exelColumn = endOfColumn(sheet);
                            // System.out.println(exelRow + " " + exelColumn
                            // + "---" + sheet.getPhysicalNumberOfRows() +
                            // " " +
                            // sheet.getRow(0).getPhysicalNumberOfCells());
                            if (colCount < exelColumn) {
                                int diff = exelColumn - colCount;
                                for (int i = 0; i < diff; i++) {
                                    GridColumn column = new GridColumn(grid, SWT.NONE);
                                    column.setText("C " + (i + 1 + colCount));
                                    column.setWidth(50);
                                }
                            }
                            if (rowCount < exelRow) {
                                int diff = exelRow - rowCount;
                                for (int i = 0; i < diff; i++) {
                                    new GridItem(grid, SWT.NONE).setHeight(16);
                                }
                            }
                            // Iterate over each row in the sheet
                            int rows = sheet.getPhysicalNumberOfRows();
                            for (int i = 0; i < exelRow; i++) {
                                XSSFRow row = sheet.getRow(i);
                                if (row == null) {
                                    for (int u = 0; u < exelColumn; u++) {
                                        grid.getItem(i).setText(u, " ");
                                    }
                                } else {
                                    for (int u = 0; u < exelColumn; u++) {
                                        XSSFCell cell = row.getCell(u);
                                        if (cell != null) {
                                            switch (cell.getCellType()) {
                                            case XSSFCell.CELL_TYPE_NUMERIC:
                                                String val = String.valueOf(cell.getNumericCellValue());
                                                grid.getItem(i).setText(u, val);
                                                break;
                                            case XSSFCell.CELL_TYPE_STRING:
                                                XSSFRichTextString st = cell.getRichStringCellValue();
                                                String val2 = st.getString();
                                                grid.getItem(i).setText(u, val2);
                                                break;
                                            case XSSFCell.CELL_TYPE_FORMULA:
                                                try {
                                                    String val3 = String.valueOf(cell.getRawValue());
                                                    grid.getItem(i).setText(u, val3);
                                                } catch (Exception e) {
                                                    // System.out.println(e.getMessage());
                                                    String s2 = cell.getCellFormula();
                                                    grid.getItem(i).setText(u, s2);
                                                }
                                                break;
                                            case XSSFCell.CELL_TYPE_BLANK:
                                                grid.getItem(i).setText(u, " ");
                                                break;
                                            case XSSFCell.CELL_TYPE_BOOLEAN:
                                                boolean s4 = cell.getBooleanCellValue();
                                                if (s4) {
                                                    grid.getItem(i).setText(u, "TRUE");
                                                } else {
                                                    grid.getItem(i).setText(u, "FALSE");
                                                }
                                                break;
                                            default:
                                                break;
                                            }
                                        } else {
                                            grid.getItem(i).setText(u, " ");
                                        }
                                    }
                                }
                            }
                        }
                    });
                }
                wb = null;
            }
        }
    } else {
        MsgDialog.message("File not found!");
    }
}

---

From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadXlsxExcelJob.java

License:Open Source License

public static int endOfColumn(XSSFSheet sheet) {

    int rowCount = endOfRow(sheet);
    int maxCellNum = 0;
    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        XSSFRow row = sheet.getRow(rowIndex);
        if (row != null) {
            maxCellNum = Math.max(maxCellNum, row.getLastCellNum());
        }// ww w.ja v  a2s.c o m
    }
    return maxCellNum;
}

---

From source file:net.sf.mzmine.util.io.XSSFExcelWriterReader.java

License:Open Source License

/**
 * Returns the cell of a given sheet at icol;irow. Former values are overwritten.
 *
 * @param sheet the sheet to write on/*w w w  .j  a v  a  2  s.c o m*/
 * @param icol index of column
 * @param irow index of row
 * @return the given cell at icol;irow
 * @see
 */
public Cell getCell(XSSFSheet sheet, int icol, int irow) {
    // try to get row
    Row row = sheet.getRow(irow);
    // if not exist: create row
    if (row == null)
        row = sheet.createRow(irow);
    // get cell
    return row.createCell(icol);
}

---

From source file:net.sf.mzmine.util.io.XSSFExcelWriterReader.java

License:Open Source License

/**
 * Returns the index of the first empty column
 *
 * @param sheet the sheet to write on/*from  w w w .  j  a v a 2  s  .  c om*/
 * @return a column index
 * @see
 */
public int getFirstEmptyColumn(XSSFSheet sheet) {
    Row row = sheet.getRow(0);
    if (row == null)
        return 0;
    int lastcoll = row.getLastCellNum();
    return (lastcoll == -1) ? 0 : lastcoll;
}

---

From source file:nl.architolk.ldt.processors.ExcelConverter.java

License:Open Source License

public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException {

    try {/* ww w  .  ja v  a2 s  . co m*/
        // Read binary content of Excel file
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os);
        readInputAsSAX(context, INPUT_DATA, base64ContentHandler);
        final byte[] fileContent = os.toByteArray();
        final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent);

        // Create workbook
        XSSFWorkbook workbook = new XSSFWorkbook(bais);
        DataFormatter formatter = new DataFormatter();
        XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);

        contentHandler.startDocument();
        contentHandler.startElement("", "workbook", "workbook", new AttributesImpl());

        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            XSSFSheet sheet = workbook.getSheetAt(s);
            AttributesImpl sheetAttr = new AttributesImpl();
            sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName());
            contentHandler.startElement("", "sheet", "sheet", sheetAttr);
            for (int r = 0; r <= sheet.getLastRowNum(); r++) {
                XSSFRow row = sheet.getRow(r);
                if (row != null) {
                    AttributesImpl rowAttr = new AttributesImpl();
                    rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r));
                    contentHandler.startElement("", "row", "row", rowAttr);
                    for (int c = 0; c < row.getLastCellNum(); c++) {
                        XSSFCell cell = row.getCell(c);
                        if (cell != null) {
                            try {
                                String cellvalue = formatter.formatCellValue(cell, evaluator);
                                if (cellvalue != "") {
                                    AttributesImpl columnAttr = new AttributesImpl();
                                    columnAttr.addAttribute("", "id", "id", "CDATA",
                                            Integer.toString(cell.getColumnIndex()));
                                    contentHandler.startElement("", "column", "column", columnAttr);
                                    contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length());
                                    contentHandler.endElement("", "column", "column");
                                }
                            } catch (Exception e) {
                            }
                        }
                    }
                    contentHandler.endElement("", "row", "row");
                }
            }
            contentHandler.endElement("", "sheet", "sheet");
        }

        contentHandler.endElement("", "workbook", "workbook");
        contentHandler.endDocument();

    } catch (IOException e) {
        throw new OXFException(e);
    }
}

---

From source file:nl.detoren.ijc.io.OutputExcel.java

License:Open Source License

private Cell getCell(XSSFSheet sheet, int row, int col) {
    Cell cell = null;/*w  ww.j av  a2s . co m*/

    // Retrieve the row and create when not valid
    XSSFRow sheetrow = sheet.getRow(row);
    if (sheetrow == null) {
        sheetrow = sheet.createRow(row);
    }
    // Retrieve the correct cell from the column
    cell = sheetrow.getCell(col);
    if (cell == null) {
        cell = sheetrow.createCell(col);
    }
    return cell;
}

---

From source file:nl.detoren.ijsco.io.ExcelExport.java

License:Open Source License

/**
 * Sorts (A-Z) rows by String column/*from  ww  w .  ja va  2s  . c o  m*/
 * @param sheet - sheet to sort
 * @param column - String column to sort by
 * @param rowStart - sorting from this row down
 */

private void sortSheet(XSSFSheet sheet, int column, int rowStart, int rowEnd) {
    try {
        FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
        logger.log(Level.INFO, "sorting sheet: " + sheet.getSheetName());
        boolean sorting = true;
        //int lastRow = sheet.getLastRowNum();
        while (sorting == true) {
            sorting = false;
            for (Row row : sheet) {
                // skip if this row is before first to sort
                if (row.getRowNum() < rowStart)
                    continue;
                // end if this is last row
                if (rowEnd == row.getRowNum())
                    break;
                Row row2 = sheet.getRow(row.getRowNum() + 1);
                if (row2 == null)
                    continue;
                int rownum1 = row.getRowNum();
                int rownum2 = row2.getRowNum();
                CellValue firstValue;
                CellValue secondValue;
                firstValue = evaluator.evaluate(row.getCell(column));
                secondValue = evaluator.evaluate(row2.getCell(column));
                //compare cell from current row and next row - and switch if secondValue should be before first
                if (secondValue.toString().compareToIgnoreCase(firstValue.toString()) < 0) {
                    logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2);
                    sheet.shiftRows(row2.getRowNum(), row2.getRowNum(), -1);
                    logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2);
                    sheet.shiftRows(row.getRowNum(), row.getRowNum(), 1);
                    sorting = true;
                }
            }
        }
    } catch (Exception ex) {
        logger.log(Level.WARNING, "Failing Shifting rows" + sheet.getSheetName() + "Error " + ex.getMessage());
    }
}

---

From source file:nl.mawoo.wcmscript.modules.excel.ExcelImportV1.java

License:Apache License

/**
 * Retrieves a Cell object from a certain adress on a certain sheet
 * @param sheet Excel sheet the cell is on
 * @param address Address of the cell E.G: A1
 * @return Cell which is found on the address
 *///from   ww w  . j  a  v  a  2 s .c o  m
public Cell getCell(XSSFSheet sheet, String address) {

    CellReference ref = new CellReference(address);
    Row r = sheet.getRow(ref.getRow());
    if (r != null) {
        return r.getCell(ref.getCol());
    } else {
        return null;
    }
}

---

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