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

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. 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:nl.mawoo.wcmscript.modules.excel.ExcelImportV1.java

License:Apache License

/**
 * Retrieves the (calculated) value of a certain cell as a String.
 * @param sheet Excel sheet the cell is on
 * @param address Adress of the cell E.G: A1
 * @return Returns the cell value as a String
 *//*from  w w  w.  j av  a2  s  . c o m*/
public String getCellValue(XSSFSheet sheet, String address) {
    CellReference ref = new CellReference(address);
    Row r = sheet.getRow(ref.getRow());
    if (r != null) {
        Cell cell = r.getCell(ref.getCol());
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            switch (cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                getScriptLogger().error("Error selecting cells");
            }
        } else {
            return cell.toString();
        }
    }
    return null;
}

From source file:Opm_Package.OpenFileName.java

public List<String> readReposNames(String file) throws Exception {
    int x = 0;/*w  w  w.  j a v a 2s .c o  m*/
    OpenFileName fname = new OpenFileName();
    // array list to store the Repos names
    ArrayList<String> list = new ArrayList<String>();
    //calling the file name.....
    XSSFWorkbook workbook = readFileName(file);
    // setting the sheet number...
    XSSFSheet spreadsheet = workbook.getSheetAt(x);
    String sname = workbook.getSheetName(x);

    Row row;
    Cell cell = null;
    for (int j = 0; j < spreadsheet.getLastRowNum() + 1; ++j) {//To loop thru the rows in a sheet
        row = spreadsheet.getRow(j);
        cell = row.getCell(0); //forks are in the eighth column...
        switch (cell.getCellType()) {
        //Checking for strings values inthe cells..
        case Cell.CELL_TYPE_STRING:
            if (!cell.getStringCellValue().equals("")) {
                // adding the call value to the arraylist called forksList 
                list.add(cell.getStringCellValue());
            } //end of if statement...
            break;
        //Checking for numeric values inthe cells..
        case Cell.CELL_TYPE_NUMERIC:
            list.add(String.valueOf(cell.getNumericCellValue()));
            break;
        //Checking for bank in the cells..
        case Cell.CELL_TYPE_BLANK:
            break;
        }//end of switch statement

    } // end of  for loop for the rows..

    //returns the arraylist to the main class....
    return list;
}

From source file:opn.greenwebs.FXMLDocumentController.java

private File createStockFile(List<ItemDB> list) {
    int nSize = list.size();
    XSSFWorkbook wbs = createStockWorkbook();

    XSSFSheet sheetStock = wbs.getSheet("Digital Version");
    List<XSSFTable> lTables = sheetStock.getTables();
    // Create a FormulaEvaluator to use
    FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper()
            .createFormulaEvaluator();//w  ww. j av a  2  s .  c o m
    File fStock = createFilename("STK", "");
    Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault()));
    Row rowed = sheetStock.getRow(6);
    Cell celled = rowed.getCell(10);
    CellStyle cellStyle = celled.getCellStyle();
    XSSFFont font = sheetStock.getWorkbook().createFont();
    font.setFontHeight(14);
    cellStyle.setFont(font);
    celled.setCellValue(Date.from(instant));
    celled.setCellStyle(cellStyle);
    rowed = sheetStock.getRow(10);
    celled = rowed.getCell(2);
    celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5));
    if (!lTables.isEmpty()) {
        XSSFTable table = lTables.get(0);
        table.getCTTable()
                .setRef(new CellRangeAddress(table.getStartCellReference().getRow(),
                        table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(),
                        table.getEndCellReference().getCol()).formatAsString());
        XSSFRow row;
        XSSFCell cell;
        font = sheetStock.getWorkbook().createFont();
        font.setFontHeight(14);
        int nCellRef = table.getStartCellReference().getRow() + 1;
        for (ItemDB itemdb : list) {
            row = sheetStock.createRow(nCellRef++);
            cell = row.createCell(0);
            cellStyle = cell.getCellStyle();
            cell.setCellValue(itemdb.getDblQty());
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(1);
            cell.setCellValue(itemdb.getStrMfr());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(2);
            cell.setCellValue(itemdb.getStrSKU());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(3);
            cell.setCellValue(itemdb.getStrDescrip());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(4);
            cell.setCellValue(itemdb.getStrSupplier());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(5);
            cell.setCellValue(itemdb.getStrSupPart());
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //cell.setCellStyle(cellStyle);
            cell = row.createCell(6);
            cell.setCellValue(itemdb.getDblSalePrice());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(7);
            cell.setCellValue(itemdb.getDblCost());
            cell.setCellStyle(cellStyle);
            /*cell = row.createCell(8);
            cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(9);
            cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            CellStyle style = wbs.createCellStyle();
            style.setDataFormat(wbs.createDataFormat().getFormat("0%"));
            cell.setCellStyle(style);*/
            mainWorkbookEvaluator.evaluateAll();
        }

        try {
            try (FileOutputStream fileOut = new FileOutputStream(fStock)) {
                wbs.write(fileOut);
                return fStock;
            }
        } catch (FileNotFoundException ex) {
            logger.info(ex.getLocalizedMessage());
        } catch (IOException ex) {
            logger.info(ex.getLocalizedMessage());
        }
    }
    return null;
}

From source file:opn.greenwebs.FXMLDocumentController.java

private void inject(XSSFWorkbook wb, Object obj, int row, int col) {
    if (wb == null) {
        System.out.println("wb is null");
    }/*from  ww  w  .  j av  a  2s  .  c o  m*/
    XSSFSheet sheet = wb.getSheet("Digital Version");
    Row rowed = sheet.getRow(row);
    Cell cell = rowed.getCell(col);
    CellStyle cellStyle = cell.getCellStyle();
    XSSFFont font = sheet.getWorkbook().createFont();
    font.setFontHeight(14);
    cellStyle.setFont(font);
    if (obj instanceof String) {
        cell.setCellValue((String) obj);
    } else if (obj instanceof Boolean) {
        cell.setCellValue((Boolean) obj);
    } else if (obj instanceof Date) {
        CreationHelper createHelper = wb.getCreationHelper();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy"));
        cell.setCellValue((Date) obj);
    } else if (obj instanceof Double) {
        cell.setCellValue((Double) obj);
    } else if (obj instanceof Integer) {
        cell.setCellValue((int) obj);
    }
    cell.setCellStyle(cellStyle);
}

From source file:opn.greenwebs.FXMLDocumentController.java

private Object getCellData(File fSheet, int row, int coll) {
    try {//from www  . java2  s.  co m
        XSSFWorkbook book = new XSSFWorkbook(fSheet);
        FormulaEvaluator eval = book.getCreationHelper().createFormulaEvaluator();
        XSSFSheet xSheet = book.getSheet("Digital Version");
        Cell celled = xSheet.getRow(row).getCell(coll);
        if (celled != null) {
            CellValue cellval = eval.evaluate(celled);
            if (cellval == null) {
                //System.out.println("cellval is null at line 918 " + fSheet.getAbsolutePath() + " " + row + " " + coll);
                return "";
            } else {
                switch (cellval.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    logger.info("got a blank");
                    return "";
                case Cell.CELL_TYPE_BOOLEAN:
                    logger.info("got a boolean");
                    return cellval.getBooleanValue();
                case Cell.CELL_TYPE_ERROR:
                    return cellval.getErrorValue();
                case Cell.CELL_TYPE_FORMULA:
                    return cellval.getStringValue();
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(celled)) {
                        return HSSFDateUtil.getJavaDate(cellval.getNumberValue());
                    } else {
                        return cellval.getNumberValue();
                    }
                case Cell.CELL_TYPE_STRING:
                    return cellval.getStringValue();
                default:
                    return "";
                }
            }
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
    }
    return "";
}

From source file:opn.greenwebs.FXMLDocumentController.java

private List<ItemDB> createListFromTable(File fStock) {
    List<ItemDB> lstMain = new ArrayList<>();
    logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath());
    try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) {
        XSSFSheet sheet = book.getSheet("Digital Version");
        logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName());
        FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator();
        List lstItem;//  w ww.  ja v  a 2  s .c  o  m
        List<XSSFTable> lstTables = sheet.getTables();
        logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size());
        if (!lstTables.isEmpty()) {
            logger.log(Level.INFO, "the table is not empty");
            for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0)
                    .getEndCellReference().getRow(); j++) {
                ItemDB itmLine = new ItemDB();
                lstItem = new ArrayList();
                logger.log(Level.INFO, "we have some rows");
                if (j > lstTables.get(0).getStartCellReference().getRow()) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        Iterator<Cell> cellIt = row.cellIterator();
                        logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext());
                        while (cellIt.hasNext()) {
                            Cell cell = cellIt.next();
                            CellValue cellvalue = evaluator2.evaluate(cell);
                            if (cellvalue == null) {
                                lstItem.add("");
                            } else {
                                switch (cellvalue.getCellType()) {
                                case Cell.CELL_TYPE_STRING:
                                    lstItem.add(cellvalue.getStringValue());
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    lstItem.add(cellvalue.getNumberValue());
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    lstItem.add(cellvalue.getBooleanValue());
                                case Cell.CELL_TYPE_BLANK:
                                    lstItem.add("");
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    lstItem.add("You found a formula");
                                    break;
                                default:
                                }
                            }
                        }
                        logger.log(Level.ALL, "lstItem is {0} and {1}",
                                new Object[] { lstItem.size(), lstItem.toString() });
                        itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString()));
                        logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString());
                        itmLine.setStrMfr(lstItem.get(1).toString());
                        itmLine.setStrSKU(lstItem.get(2).toString());
                        itmLine.setStrDescrip(lstItem.get(3).toString());
                        itmLine.setStrSupplier(lstItem.get(4).toString());
                        itmLine.setStrSupPart(lstItem.get(5).toString());
                        logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5));
                        if (lstItem.size() > 6) {
                            if (!lstItem.get(6).toString().isEmpty()) {
                                itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString()));
                            }
                        }
                        //System.out.println("Added item " + itmLine.getStrDescrip());
                        lstMain.add(itmLine);
                    }

                }
            }
        }
    } catch (IOException ex) {
        logger.info(ex.getLocalizedMessage());

    }
    logger.log(Level.INFO, "returning {0}", lstMain.size());
    return lstMain;
}

From source file:opn.greenwebs.FXMLDocumentController.java

private void shootTables(XSSFSheet sheet, List<XSSFTable> list) {

    for (XSSFTable xTable : list) {
        for (int j = xTable.getStartCellReference().getRow(); j < xTable.getEndCellReference().getRow(); j++) {
            Row row = sheet.getRow(j + 1);
            List lstItem = new ArrayList();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    String strCell = cell.getStringCellValue();
                    lstItem.add(strCell);
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    lstItem.add(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    lstItem.add(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    lstItem.add("");
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    lstItem.add(cell.getCellFormula());
                    break;
                default:
                }//ww  w  .j  a v a  2  s  .c  o m
            }
            insertData(lstItem, true);
        }
    }
}

From source file:org.addition.epanet.network.io.input.ExcelParser.java

License:Open Source License

private int parseWorksheet(Network net, List<XSSFSheet> sheets, Pattern tagPattern, int errSum)
        throws ENException {
    for (XSSFSheet sheet : sheets) {

        boolean lastRowNull = true;
        boolean lastRowHeader = false;
        Network.SectType lastType = null;

        for (int rowCount = 0, tRowId = 0; rowCount < sheet.getPhysicalNumberOfRows(); tRowId++) {
            XSSFRow row = sheet.getRow(tRowId);

            if (row != null) {
                List<String> tokens = new ArrayList<String>();

                String comments = "";
                boolean allAreBold = true;

                for (int cellCount = 0, tCellId = 0; cellCount < row.getPhysicalNumberOfCells(); tCellId++) {
                    XSSFCell cell = row.getCell(tCellId);
                    if (cell != null) {
                        String value = convertCell(cell);
                        if (value.startsWith(";")) {
                            comments += value;
                        } else
                            tokens.add(value);

                        allAreBold = allAreBold & cell.getCellStyle().getFont().getBold(); // TODO remover

                        cellCount++;/*from   w w w  .jav  a 2s  .co m*/
                    }
                }

                if (tokens.size() > 0) {
                    if (lastRowNull && tagPattern.matcher(tokens.get(0)).matches()) {
                        lastType = Network.SectType.parse(tokens.get(0));
                        lastRowHeader = true;
                    } else {
                        String[] tokArray = tokens.toArray(new String[tokens.size()]);

                        if (lastRowHeader && allAreBold) {
                            //System.out.println("Formating Header : " + tokens.toArray(new String[tokens.size()]));
                        } else {
                            try {
                                parseSect(net, lastType, comments, tokArray);
                            } catch (ENException e) {
                                String line = "";
                                for (String tk : tokArray)
                                    line += tk + " ";

                                logException(lastType, e, line, tokArray);
                                errSum++;
                            }
                        }
                    }
                }

                lastRowNull = false;
                rowCount++;
            }

            if (row == null || row != null && row.getPhysicalNumberOfCells() == 0) {
                lastRowNull = true;
                continue;
            }

        }
    }
    return errSum;
}

From source file:org.ado.minesync.translation.ExportFile.java

License:Open Source License

private String getCodeEntryName(XSSFSheet sheet, Cell cell) {
    return sheet.getRow(CODE_COL).getCell(cell.getColumnIndex()).getStringCellValue();
}

From source file:org.apache.commons.g.QueryStorePicUI.java

/**
 * ??//from  www  .j a v a  2 s.c  o m
 * @param path
 * @throws Exception
 */
public void queryStorePic(String filePath, String outPath) throws Exception {

    final MyDialog dialog = new MyDialog(mContext, " ...");

    final List<String> noPics = new ArrayList<String>();
    final List<String> noStores = new ArrayList<String>();

    InputStream in = new FileInputStream(filePath);
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
    final XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    int rows = xssfSheet.getLastRowNum();
    if (rows > 100) {
        rows = 100;
    }

    System.out.println();
    System.out.println("  " + rows + "...");
    dialog.setWorkText("  " + rows + "...");

    HashMap<String, String> headers = new HashMap<String, String>();
    headers.put("Content-Type", "application/json");
    headers.put("Cookie", Config.JSESSIONID);
    headers.put("Referer", "https://homesis.homecredit.cn/hsis/index.html");

    final XSSFCell cell0 = xssfSheet.getRow(0).createCell(0);
    cell0.setCellValue("");

    System.out.println("?? |  | ? |    ??    | ??");
    for (int i = 1; i <= rows; i++) {
        final XSSFRow xssfRow = xssfSheet.getRow(i);

        if (xssfRow == null)
            continue;

        // ??
        String visitName = XssfUtil.getCellValue(xssfRow.getCell(0));
        if (TextUtils.isEmpty(visitName))
            continue;

        // ?
        final String storeCode = XssfUtil.getCellValue(xssfRow.getCell(3));
        final String data = "{\"code\":\"" + storeCode
                + "\",\"name\":null,\"licenseNumber\":null,\"salesDistrictId\":null,\"offset\":0,\"limit\":15}";

        // ??
        final String storeName = XssfUtil.getCellValue(xssfRow.getCell(10));

        final XSSFCell cell = xssfRow.createCell(0);

        String space2 = "     ";
        // ??2
        if (storeName.length() < 20)
            space2 += Util.getFormateSpace(20 - storeName.length());
        ;

        String space = "     ";
        // ??2
        if (visitName.length() == 3)
            space = space.substring(2);
        // ?????
        if (i < 10) {
            System.out.print(
                    "    " + i + "      " + visitName + space + storeCode + "        " + storeName + space2);
        } else {
            System.out.print(
                    "    " + i + "     " + visitName + space + storeCode + "        " + storeName + space2);
        }
        dialog.setWorkText(" " + storeCode + "-" + storeName);
        Thread.sleep(1234);

        String store = null;
        boolean run = true;
        while (run) {
            try {
                // ?
                HttpPost seachStore = mHttpUtil
                        .doPost("https://homesis.homecredit.cn/hsis/api/salesroom/search", headers, data);
                store = mHttpUtil.executeForBody(seachStore);
                //System.out.println(store);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        if (store.contains("HTTP/1.1 500 Internal Server Error")) {
            System.out.println("?(?)");
            break;
        }
        JSONObject json = null;
        try {
            json = JSONObject.parseObject(store);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println(store);
            continue;
        }
        if (json == null) {
            System.out.println("?");
            continue;
        }

        JSONArray array = json.getJSONArray("data");
        if (array.size() == 0) {
            noStores.add(storeCode);
            cell.setCellValue("?");
            System.out.println("?");
            continue;
        }
        JSONObject obj = (JSONObject) array.get(0);

        //  ID
        int id = obj.getIntValue("id");
        //System.out.println(id);

        String storePic = null;
        run = true;
        while (run) {
            try {
                // 
                HttpGet seachPic = mHttpUtil
                        .doGet("https://homesis.homecredit.cn/hsis/api/document/salesroom/all/" + id, headers);
                storePic = mHttpUtil.executeForBody(seachPic);
                //                    System.out.println(storePic);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        json = JSONObject.parseObject(storePic);
        array = json.getJSONArray("documents");
        if (array.size() == 0) {
            noPics.add(storeCode);
            cell.setCellValue("");
            System.out.println("");
            continue;
        }

        boolean print = true;
        for (int j = 0; j < array.size(); j++) {
            obj = (JSONObject) array.get(j);
            //  ID
            int picid = obj.getIntValue("id");
            String fileName = obj.getString("fileName");
            if (fileName.contains("POSRepID"))
                continue;
            //                fileName = fileName.substring(0, fileName.indexOf("-"));//+fileName.substring(fileName.indexOf("."));
            run = true;
            while (run) {
                try {
                    // 
                    HttpGet down = mHttpUtil.doGet(
                            "https://homesis.homecredit.cn/hsis/api/document/download/" + picid, headers);
                    String result = mHttpUtil.downPic(down, Util
                            .mkdirsIfNeed(outPath + File.separator + visitName, storeCode + "-" + fileName));
                    if (print) {
                        print = false;
                        System.out.println(fileName.substring(0, fileName.indexOf("-")));
                    }
                    run = false;
                } catch (Exception e) {
                    System.out.println();
                    e.printStackTrace();
                    System.out.println();
                    System.out.println("?/");
                    System.out.println();
                }
            }
        }
    }
    xssfWorkbook.write(new FileOutputStream(filePath.replace(".xlsx", "_pic.xlsx")));
    xssfWorkbook.close();
    xssfWorkbook = null;

    in.close();
    in = null;

    if (noStores.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("??");
        System.out.println(
                "");
        for (int k = 0; k < noStores.size(); k++) {
            System.out.println(noStores.get(k));
        }
    }

    if (noPics.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("?");
        System.out.println(
                "");
        for (int k = 0; k < noPics.size(); k++) {
            System.out.println(noPics.get(k));
        }
    }

    dialog.dispose(2);
}