Examples with CellReference org.apache.poi.hssf.util.CellReference used on opensource projects

Example 6 with CellReference

use of org.apache.poi.hssf.util.CellReference in project oc-explorer by devgateway.

the class XExcelFileReader method getDataRow.

private String[] getDataRow() throws XMLStreamException {
    List<String> rowValues = new ArrayList<String>();
    while (xmlReader.hasNext()) {
        xmlReader.next();
        if (xmlReader.isStartElement()) {
            if (xmlReader.getLocalName().equals("c")) {
                CellReference cellReference = new CellReference(xmlReader.getAttributeValue(null, "r"));
                // Fill in the possible blank cells!
                while (rowValues.size() < cellReference.getCol()) {
                    rowValues.add("");
                }
                String cellType = xmlReader.getAttributeValue(null, "t");
                rowValues.add(getCellValue(cellType));
            }
        } else if (xmlReader.isEndElement() && xmlReader.getLocalName().equals("row")) {
            break;
        }
    }
    return rowValues.toArray(new String[rowValues.size()]);
}

Example 7 with CellReference

use of org.apache.poi.hssf.util.CellReference in project my_curd by qinyou.

the class ExcelHelper method excelFileConvertToList.

/**
 * @param filename
 * @param inputStream
 * @return
 * @throws Exception
 */
public List<List> excelFileConvertToList(String filename, FileInputStream inputStream) throws Exception {
    Workbook wb = null;
    // 此处为兼容2003 与2oo7
    String ext = getExtensionName(filename);
    if (ext.toLowerCase().equals("xlsx")) {
        wb = new XSSFWorkbook(inputStream);
    } else {
        wb = WorkbookFactory.create(inputStream);
    }
    Sheet sheet = wb.getSheetAt(0);
    List<List> rows = new ArrayList<List>();
    for (Row row : sheet) {
        List<Object> cells = new ArrayList<Object>();
        for (Cell cell : row) {
            Object obj = null;
            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
            switch(cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    obj = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        obj = new JDateTime(cell.getDateCellValue());
                    } else {
                        obj = cell.getNumericCellValue();
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    obj = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    obj = cell.getNumericCellValue();
                    break;
                default:
                    obj = null;
            }
            cells.add(obj);
        }
        rows.add(cells);
    }
    return rows;
}

Example 8 with CellReference

use of org.apache.poi.hssf.util.CellReference in project poi by apache.

the class TableRecord method toString.

public String toString() {
    StringBuffer buffer = new StringBuffer();
    buffer.append("[TABLE]\n");
    buffer.append("    .range    = ").append(getRange()).append("\n");
    buffer.append("    .flags    = ").append(HexDump.byteToHex(field_5_flags)).append("\n");
    buffer.append("    .alwaysClc= ").append(isAlwaysCalc()).append("\n");
    buffer.append("    .reserved = ").append(HexDump.intToHex(field_6_res)).append("\n");
    CellReference crRowInput = cr(field_7_rowInputRow, field_8_colInputRow);
    CellReference crColInput = cr(field_9_rowInputCol, field_10_colInputCol);
    buffer.append("    .rowInput = ").append(crRowInput.formatAsString()).append("\n");
    buffer.append("    .colInput = ").append(crColInput.formatAsString()).append("\n");
    buffer.append("[/TABLE]\n");
    return buffer.toString();
}

Example 9 with CellReference

use of org.apache.poi.hssf.util.CellReference in project poi by apache.

the class TestExternalNameReference method testReadCalcSheet.

/**
	 * tests <tt>NameXPtg for external cell reference by name</tt> and logic in Workbook below that   
	 */
public void testReadCalcSheet() {
    try {
        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("XRefCalc.xls");
        assertEquals("Sheet1!$A$2", wb.getName("QUANT").getRefersToFormula());
        assertEquals("Sheet1!$B$2", wb.getName("PART").getRefersToFormula());
        assertEquals("x123", wb.getSheet("Sheet1").getRow(1).getCell(1).getStringCellValue());
        assertEquals("Sheet1!$C$2", wb.getName("UNITCOST").getRefersToFormula());
        CellReference cellRef = new CellReference(wb.getName("UNITCOST").getRefersToFormula());
        HSSFCell cell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
        assertEquals("VLOOKUP(PART,COSTS,2,FALSE)", cell.getCellFormula());
        assertEquals("Sheet1!$D$2", wb.getName("COST").getRefersToFormula());
        cellRef = new CellReference(wb.getName("COST").getRefersToFormula());
        cell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
        assertEquals("UNITCOST*Quant", cell.getCellFormula());
        assertEquals("Sheet1!$E$2", wb.getName("TOTALCOST").getRefersToFormula());
        cellRef = new CellReference(wb.getName("TOTALCOST").getRefersToFormula());
        cell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
        assertEquals("Cost*Markup_Cost", cell.getCellFormula());
    } catch (Exception e) {
        fail();
    }
}

Example 10 with CellReference

use of org.apache.poi.hssf.util.CellReference in project poi by apache.

the class HSSFPatriarch method preSerialize.

/**
     * check if any shapes contain wrong data
     * At now(13.08.2010) check if patriarch contains 2 or more comments with same coordinates
     */
protected void preSerialize() {
    Map<Integer, NoteRecord> tailRecords = _boundAggregate.getTailRecords();
    /**
         * contains coordinates of comments we iterate over
         */
    Set<String> coordinates = new HashSet<String>(tailRecords.size());
    for (NoteRecord rec : tailRecords.values()) {
        String noteRef = new CellReference(rec.getRow(), rec.getColumn()).formatAsString();
        if (coordinates.contains(noteRef)) {
            throw new IllegalStateException("found multiple cell comments for cell " + noteRef);
        } else {
            coordinates.add(noteRef);
        }
    }
}