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

Introduction

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

Prototype

int getPhysicalNumberOfCells();

Source Link

Document

Gets the number of defined cells (NOT number of cells in the actual row!).

Usage

From source file:org.isource.util.CSVUtils.java

public static void updateSheet(String filename) {

    try {/*from  w  w w  . ja v  a2s.c o m*/
        FileInputStream file = new FileInputStream(new File(filename));

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);

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

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int rowNum = 1;
        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();

            List<String> line = new ArrayList<String>();

            Cell newCell = row.createCell(row.getPhysicalNumberOfCells());

            if (rowNum == 1) {
                newCell.setCellValue("New Cell");
            } else {
                newCell.setCellType(Cell.CELL_TYPE_FORMULA);
                newCell.setCellFormula("SUM(B2:B9)");
            }

            rowNum++;
        }

        workbook = evaluateFormulas(workbook);
        FileOutputStream out = new FileOutputStream(new File(filename));
        workbook.write(out);
        out.close();
        file.close();

    } catch (FileNotFoundException ex) {
        Logger.getLogger(CSVUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(CSVUtils.class.getName()).log(Level.SEVERE, null, ex);
    }

}

---

From source file:org.isource.util.CSVUtils.java

private static HSSFWorkbook evaluateFormulas(HSSFWorkbook wb) {

    FormulaEvaluator evaluator = null;//from www  . j  av a  2s . c  om
    evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
        Sheet sheet = wb.getSheetAt(sheetNum);
        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    evaluator.evaluateFormulaCell(c);
                    if (sheetNum == 0 && c.getColumnIndex() == r.getPhysicalNumberOfCells() - 1) {
                        switch (c.getCachedFormulaResultType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            break;
                        case Cell.CELL_TYPE_STRING:
                            break;
                        }
                    }
                }
            }
        }
    }
    return wb;
}

---

From source file:org.isource.util.CSVUtils.java

public static String validate_formula(String formula) {

    List<String> allCols = Mapping.minify(Mapping.getMap());

    formula = new CSVUtils().translate_formula(formula, allCols).replace("#", "1");

    String v_msg = "valid";
    try {//from ww w.j a  va 2 s.  c  o m
        FileInputStream file = new FileInputStream(new File(Provider.getUpload_path() + "validation.xls"));

        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.createCell(row.getPhysicalNumberOfCells());
        cell.setCellType(Cell.CELL_TYPE_FORMULA);

        cell.setCellFormula(formula);

        workbook = evaluateFormulas(workbook);

        file.close();

        /*
        FileOutputStream outFile = new FileOutputStream(new File("/media/islam/55247aa2-2234-4e48-8a62-c1fabcb5c84d/opt/apache-tomcat-7.0.70/webapps/data/validation.xls"));
        workbook.write(outFile);
        outFile.close();
        */

    } catch (Exception e) {
        v_msg = e.getMessage();
    }
    return v_msg;
}

---

From source file:org.lisapark.octopus.util.json.ExcelSardineUtils.java

License:Open Source License

public static void main(String[] args) {

    Map<String, Integer> prodCellIndexMap = Maps.newHashMap();
    prodCellIndexMap.put(SHOP, 0);//w w w.ja  va 2  s .com
    prodCellIndexMap.put(SHIFT, 0);
    prodCellIndexMap.put(MACHINE, 0);
    prodCellIndexMap.put(PRODUCT, 0);
    prodCellIndexMap.put(PRODUCT_TYPE, 0);
    prodCellIndexMap.put(MATERIAL_TYPE, 0);
    prodCellIndexMap.put(RAW_MATERIAL, 4);
    prodCellIndexMap.put(TOTAL_MATERIALS, 5);
    prodCellIndexMap.put(TOTAL_PRODUCTS, 6);

    Map<String, Integer> wrhCellIndexMap = Maps.newHashMap();
    wrhCellIndexMap.put(WAREHOUSE, 1);
    wrhCellIndexMap.put(ITEM, 1);
    wrhCellIndexMap.put(ITEM_TYPE, 1);
    wrhCellIndexMap.put(BEGINING, 0);
    wrhCellIndexMap.put(INCOMING, 1);
    wrhCellIndexMap.put(OUTGOING, 2);
    wrhCellIndexMap.put(ENDING, 3);

    try {
        String excelFile = "http://173.72.110.131:8080/WebDavServer/iPlast/Warehouse/";

        // Get all xml files
        Sardine sardine = SardineFactory.begin("", "");
        List<DavResource> resources = sardine.getResources(excelFile);

        for (DavResource res : resources) {
            String url = res.getPath();
            //getAbsoluteUrl();
            if (res.isDirectory()) {
                continue;
            } else {
                Map<String, String> props = res.getCustomProps();
                if (props.get(PROCESSED) == null) {
                    InputStream isData = sardine.get(url);
                    HSSFWorkbook book = new HSSFWorkbook(isData);

                    int index = 0;
                    int increament = 1;
                    if (book.getNumberOfSheets() > index) {
                        if (increament == 0) {
                            //                                increament = PROD_OUTLINE_INCREAMENT;
                            increament = WRH_OUTLINE_INCREAMENT;
                        }
                        Sheet sheet = book.getSheetAt(index);
                        if (sheet == null) {
                            continue;
                        }

                        // Iterate through the rows.
                        int splitRowNumber = 0;

                        if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
                            splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
                        }

                        Map<String, Object> rowMap = Maps.newHashMap();

                        int start = 2;
                        Row dateRow = sheet.getRow(8);
                        int end = dateRow.getLastCellNum();

                        for (int dateShift = start; dateShift < end - 4; dateShift = dateShift + 4) {

                            rowMap.put(DATE, formatDate(dateRow.getCell(dateShift).getStringCellValue()));
                            System.out.println(dateRow.getCell(dateShift).getStringCellValue());

                            Sheet _sheet = book.getSheetAt(index);

                            for (Iterator<Row> rowsIt = _sheet.rowIterator(); rowsIt.hasNext();) {
                                Row row = rowsIt.next();
                                if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) {
                                    continue;
                                }

                                Cell cell = row.getCell(1);
                                int indent = cell.getCellStyle().getIndention();
                                int absIndent = indent / increament;
                                //                                
                                if (processRowWrhSs(rowMap, row, wrhCellIndexMap, absIndent, dateShift)) {
                                    System.out.println(rowMap);
                                }
                            }
                        }
                    }
                    props.put(PROCESSED, TRUE);
                    sardine.setCustomProps(url, props, null);
                } else {
                    System.out.println("Property PROCESSED: " + props.get(PROCESSED));
                    List<String> removeProps = new ArrayList<String>(1);
                    removeProps.add(PROCESSED);

                    sardine.setCustomProps(url, null, removeProps);
                }
                break;
            }
        }
    } catch (FileNotFoundException ex) {
        Exceptions.printStackTrace(ex);
    } catch (IOException ex) {
        Exceptions.printStackTrace(ex);
    }
}

---

From source file:org.lisapark.octopus.util.json.JsonUtils.java

License:Open Source License

/**
 * /*ww w  . ja v a2 s  .  c o m*/
 * @param sheet
 * @param ontology
 * @return
 * @throws JSONException 
 */
private String jsonFromSS(Sheet sheet, int increment) throws JSONException {
    // Return null, if sheet is null
    if (sheet == null)
        return null;

    String sheetName = sheet.getSheetName();
    if (sheetName.isEmpty()) {
        sheetName = SPREAD_SHEET_ROWS;
    }

    // Iterate through the rows.
    StringBuilder stringBuilderRows = new StringBuilder();
    List<String> stack = new ArrayList<String>();
    Boolean first = Boolean.TRUE;
    int splitRowNumber = 0;
    if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
        splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
    }
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        if (row.getPhysicalNumberOfCells() > 0 && row.getRowNum() >= splitRowNumber) {
            continue;
        }
        String stringCells = jsonFromRowAsString(row);
        if (stringCells.isEmpty()) {
            continue;
        }

        String stringRow = jsonFromRowAsTreeNode(row, stringCells, stack, increment);

        if (first) {
            stringBuilderRows.append("[").append(stringRow);
            first = Boolean.FALSE;
        } else {
            stringBuilderRows.append(",").append(stringRow);
        }

    }
    // Get the JSON text.
    stringBuilderRows = stringBuilderRows.append("]"
    //                + "}"
    );

    return //"{" + "\"" + sheetName + "\"" + " : " + 
    stringBuilderRows.toString();
}

---

From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java

License:Open Source License

public static String xmlTagAttributesFromSS(Sheet sheet, int increment) throws JSONException {
    // Return null, if sheet is null
    if (sheet == null) {
        return null;
    }/*from  w ww.j a v  a  2 s. c  o m*/

    // Iterate through the rows.
    StringBuilder stringBuilderRows = new StringBuilder();
    List<String> stack = new ArrayList<String>();
    Boolean first = Boolean.TRUE;
    int splitRowNumber = 0;
    if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
        splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
    }
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) {
            continue;
        }
        String tagAttributes = tagAttributesAsString(row);
        if (tagAttributes.isEmpty()) {
            continue;
        }
        String stringRow = xmlFromRowAsTreeAttributes(tagAttributes, row, stack, increment);
        if (first) {
            stringBuilderRows.append(IPLAST_LEFT_TAG).append(stringRow);
            first = Boolean.FALSE;
        } else {
            stringBuilderRows.append(stringRow);
        }
        System.out.println(stringRow);
    }

    // Close all opened tags from stack
    if (!stack.isEmpty()) {
        int n = stack.size();
        for (int i = n - 1; i >= 0; --i) {
            stringBuilderRows = stringBuilderRows.append(rightTag(stack.get(i)));
        }
    }
    // Get the XML text.
    stringBuilderRows = stringBuilderRows.append(IPLAST_RIGHT_TAG);

    return stringBuilderRows.toString();
}

---

From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java

License:Open Source License

private static List<String> getDateList(Sheet sheet, int start, int rangeLen) {
    List<String> dateList = Lists.newArrayList();

    if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
        int splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
        Row row = sheet.getRow(splitRowNumber - WAREHOUSE_DATE_ROW_SHIFT);
        int currCellNumber = row.getFirstCellNum() + start + 1;

        while (currCellNumber <= row.getPhysicalNumberOfCells()) {
            dateList.add(row.getCell(currCellNumber).getStringCellValue());
            currCellNumber += rangeLen;//  www  .  j  a  va2  s . com
        }
    } else {
        dateList = null;
    }
    return dateList;
}

---

From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java

License:Open Source License

private static String tagNodesAsString(Row row, int dataRangeStart, int dataRangeLen) throws JSONException {
    StringBuilder nodeStringBuilder = new StringBuilder();
    StringBuilder rowStringBuilder = new StringBuilder();

    int start;//from   w  w w  .j a  va 2  s  . c  o m
    int end;

    // Define start and end points of cell range to be converted
    if (dataRangeStart == -1) {
        start = row.getFirstCellNum();
    } else {
        start = row.getFirstCellNum() + dataRangeStart;
    }
    if (dataRangeLen == 0) {
        end = row.getPhysicalNumberOfCells();
    } else {
        end = start + dataRangeLen;
    }
    int i = 0;
    int j = 0;
    // Itarate over cell range and build xml nodes
    for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
        if (i >= end) {
            break;
        }
        Cell cell = cellsIT.next();
        // Skip cells that are out of range
        if (i > 0 && i < start) {
            i++;
            continue;
        }
        // Build all nodes from Spreadsheet row with specified cell range
        rowStringBuilder = rowStringBuilder.append(buidNodeAsString(cell, i, j));
        i++;
        j++;
    }
    return nodeStringBuilder.append(rowStringBuilder.toString()).toString();
}

---

From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java

License:Open Source License

private static String xmlTagNodesFromSSheet(Sheet sheet, int increment, int dataRangeStart, int dataRangeLen)
        throws JSONException {
    // Return null, if sheet is null
    if (sheet == null) {
        return null;
    }//w w w.  ja  v  a2s  . c  o  m

    // Iterate through the rows.
    StringBuilder stringBuilderRows = new StringBuilder();
    List<String> stack = new ArrayList<String>();
    //        Boolean first = Boolean.TRUE;        
    int splitRowNumber = 0;
    if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
        splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
    }
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) {
            continue;
        }
        String tagNodes = tagNodesAsString(row, dataRangeStart, dataRangeLen);
        if (tagNodes.isEmpty()) {
            continue;
        }
        String stringRow = xmlFromRowAsTreeNodes(tagNodes, row, stack, increment);
        stringBuilderRows.append(stringRow);
    }
    // Close all opened tags from stack
    if (!stack.isEmpty()) {
        int n = stack.size();
        for (int i = n - 1; i >= 0; --i) {
            stringBuilderRows = stringBuilderRows.append(rightTag(stack.get(i)));
        }
    }

    return stringBuilderRows.toString();
}

---

From source file:org.matonto.etl.rest.impl.DelimitedRestImpl.java

License:Open Source License

/**
 * Converts the specified number of rows of a Excel file into JSON and returns
 * them as a String./*from   w w  w  .j  a v  a 2 s  .c  om*/
 *
 * @param input the Excel file to convert into JSON
 * @param numRows the number of rows from the Excel file to convert
 * @return a string with the JSON of the Excel rows
 * @throws IOException excel file could not be read
 * @throws InvalidFormatException file is not in a valid excel format
 */
private String convertExcelRows(File input, int numRows) throws IOException, InvalidFormatException {
    Workbook wb = WorkbookFactory.create(input);
    // Only support single sheet files for now
    Sheet sheet = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();
    JSONArray rowList = new JSONArray();
    String[] columns;
    for (Row row : sheet) {
        if (row.getRowNum() <= numRows) {
            columns = new String[row.getPhysicalNumberOfCells()];
            int index = 0;
            for (Cell cell : row) {
                columns[index] = df.formatCellValue(cell);
                index++;
            }
            rowList.add(columns);
        }
    }

    return rowList.toString();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• »