List of usage examples for org.apache.poi.ss.usermodel Cell getAddress
CellAddress getAddress();
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected void readRowSchema(Row dataRow, int endColumnIndex) { String cellAddress = ""; String schemaColumnType = null; String currentCellDataType = null; try {// w w w. ja v a2s .c o m for (Cell cell : dataRow) { cellAddress = cell.getAddress().toString(); String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); int columnIndex = _columnAddress.indexOf(columnLetter); if (columnIndex == -1) { continue; } currentCellDataType = "String"; CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: currentCellDataType = "String"; break; case BOOLEAN: currentCellDataType = "Boolean"; break; case NUMERIC: currentCellDataType = "Double"; break; case FORMULA: currentCellDataType = "Object"; break; case BLANK: currentCellDataType = "String"; break; default: break; } // Object, String, Numeric, Boolean schemaColumnType = _dataSchema[columnIndex][1]; if (schemaColumnType == null) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Object".equals(schemaColumnType)) { // no change } else if ("String".equals(schemaColumnType) && "Object".equals(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Double".equals(schemaColumnType) && "Object|String".contains(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Boolean".equals(schemaColumnType) && "Object|String|Double".contains(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } _dataTypes[columnIndex] = DataUtilities.dataTypeToEnum(_dataSchema[columnIndex][1]); columnIndex++; } } catch (Exception ex) { throw new PieException(String.format("Error while reading Excel cell %s for its data type (%s). %s", cellAddress, currentCellDataType, ex.getMessage()), ex); } }
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected Object[] readExcelData(Row excelDataRow) { Object[] data = new Object[_columnCount]; String cellAddress = ""; int dataIndex = 0; try {/*w ww .j a v a 2 s .co m*/ for (Cell cell : excelDataRow) { cellAddress = cell.getAddress().toString(); String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); int columnIndex = _columnAddress.indexOf(columnLetter); if (columnIndex == -1) { continue; } CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: data[dataIndex] = cell.getStringCellValue(); break; case BOOLEAN: data[dataIndex] = _allTypesStrings ? Boolean.toString(cell.getBooleanCellValue()) : cell.getBooleanCellValue(); break; case NUMERIC: data[dataIndex] = _allTypesStrings ? Double.toString(cell.getNumericCellValue()) : cell.getNumericCellValue(); break; default: data[dataIndex] = _allTypesStrings ? "" : null; break; } dataIndex++; } if (_addFilename) data[data.length - 1] = _filenameOnly; } catch (Exception ex) { throw new PieException( String.format("Error while reading Excel data from cell %s. %s", cellAddress, ex.getMessage()), ex); } return data; }
From source file:nl.mawoo.wcmscript.modules.excel.ExcelImportV1.java
License:Apache License
/** * Returns a hashmap containing the cell adresses and their objects from a certain sheet * @param sheet Excel sheet the cells are on * @return A HashMap containing all the adresses as keys, and cells as values respectively. *//*from w w w . j a v a 2 s.c o m*/ public Map<String, Cell> getCells(XSSFSheet sheet) { HashMap<String, Cell> output = new HashMap<>(); Iterator<Row> rows = getRowIterator(sheet); while (rows.hasNext()) { Iterator<Cell> cells = getCellIterator(rows.next()); while (cells.hasNext()) { Cell c = cells.next(); output.put(c.getAddress().formatAsString(), c); } } return output; }
From source file:org.dhatim.fastexcel.reader.FastExcelReaderTest.java
License:Apache License
@ParameterizedTest
@ValueSource(strings = { "/xlsx/AutoFilter.xlsx", "/xlsx/calendar_stress_test.xlsx",
"/xlsx/cell_style_simple.xlsx", "/xlsx/comments_stress_test.xlsx", "/xlsx/custom_properties.xlsx",
"/xlsx/dates.xlsx", "/xlsx/defined_names_simple.xlsx", "/xlsx/ErrorTypes.xlsx",
"/xlsx/formula_stress_test.xlsx", "/xlsx/formulae_test_simple.xlsx", "/xlsx/hyperlink_no_rels.xlsx",
"/xlsx/hyperlink_stress_test_2011.xlsx", "/xlsx/interview.xlsx", "/xlsx/issue.xlsx",
// "/xlsx/large_strings.xlsx",
"/xlsx/LONumbers-2010.xlsx", "/xlsx/LONumbers-2011.xlsx", "/xlsx/LONumbers.xlsx",
"/xlsx/merge_cells.xlsx", "/xlsx/mixed_sheets.xlsx", "/xlsx/named_ranges_2011.xlsx",
"/xlsx/number_format_entities.xlsx", "/xlsx/phonetic_text.xlsx", "/xlsx/pivot_table_named_range.xlsx",
"/xlsx/rich_text_stress.xlsx", "/xlsx/RkNumber.xlsx", "/xlsx/smart_tags_2007.xlsx", "/xlsx/sushi.xlsx",
"/xlsx/text_and_numbers.xlsx", "/xlsx/world.xlsx", "/xlsx/write.xlsx",
// "/xlsx/xlsx-stream-d-date-cell.xlsx"
})
public void testFile(String file) {
LOGGER.info("Test " + file);
try (InputStream inputStream = open(file); InputStream inputStream2 = open(file)) {
try (ReadableWorkbook excel = new ReadableWorkbook(inputStream);
Workbook workbook = WorkbookFactory.create(inputStream2)) {
Iterator<Sheet> it = excel.getSheets().iterator();
while (it.hasNext()) {
Sheet sheetDef = it.next();
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(sheetDef.getIndex());
try (Stream<Row> data = sheetDef.openStream()) {
Iterator<Row> rowIt = data.iterator();
Iterator<org.apache.poi.ss.usermodel.Row> itr = sheet.iterator();
while (rowIt.hasNext()) {
Row row = rowIt.next();
org.apache.poi.ss.usermodel.Row expected = itr.next();
assertThat(row.getPhysicalCellCount()).as("physical cell")
.isEqualTo(expected.getPhysicalNumberOfCells());
assertThat(row.getCellCount()).as("logical cell")
.isEqualTo(expected.getLastCellNum() == -1 ? 0 : expected.getLastCellNum());
for (int i = 0; i < row.getCellCount(); i++) {
Cell cell = row.getCell(i);
org.apache.poi.ss.usermodel.Cell expCell = expected.getCell(i);
assertThat(cell == null).as("cell defined " + i).isEqualTo(expCell == null);
if (cell != null) {
String cellAddr = cell.getAddress().toString();
assertThat(toCode(cell.getType())).as("cell type code " + cellAddr)
.isEqualTo(expCell.getCellTypeEnum().getCode());
if (cell.getType() == CellType.NUMBER) {
BigDecimal n = cell.asNumber();
BigDecimal expN = new BigDecimal(getRawValue(expCell));
assertThat(n).as("Number " + cellAddr).isEqualTo(expN);
} else if (cell.getType() == CellType.STRING) {
String s = cell.asString();
String expS = expCell.getStringCellValue();
assertThat(s).as("String " + cellAddr).isEqualTo(expS);
}/*from www .j a v a2 s .com*/
}
}
}
} catch (Throwable e) {
throw new RuntimeException("On sheet " + sheetDef.getId() + " " + sheetDef.getName(), e);
}
}
}
} catch (Throwable e) {
throw new RuntimeException("On file " + file, e);
}
}
From source file:XlsUtils.XlsComparator.java
public static boolean comparaCelda(Cell celda1, Cell celda2, StringBuilder cache) { Object value1 = getCellValue(celda1); Object value2 = getCellValue(celda2); String adress;//from w w w .ja v a 2 s .com boolean res; // Esta primera comparacin nos libra de 3 casos, 1 de ellos problemtico: // 1) Son primitivos iguales por lo que no hay que hacer ms gestin // 2) Son el mismo objeto por lo que no hay que hacer ms gestin // 3) Son los 2 nulos, lo cual controlar podra ensuciar el cdigo y realmente eso significa que son iguales y no hay que hacer ms gestin if (value1 == value2) res = true; else { try { res = value1.equals(value2); adress = celda1.getAddress().formatAsString(); } catch (NullPointerException ex) { res = value2.equals(value1); adress = celda2.getAddress().formatAsString(); } if (cache != null && !res) cache.append("DEBUG:: El valor de ").append(adress).append(" es diferente en los 2 excel: ") .append("$Excel1$: ").append(value1).append(" || ").append("$Excel2$: ").append(value2) .append("\r\n"); } return res; }