List of usage examples for org.apache.poi.ss.usermodel Row getRowNum
int getRowNum();
From source file:org.drugepi.table.ExcelUtils.java
License:Mozilla Public License
public static String getRowId(Row row) throws Exception { if (row == null) return null; String id = String.format("R%d", row.getRowNum()); return id;// w w w . j a v a 2 s . c om }
From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetImporterImpl.java
License:Open Source License
/** * Extracts the information from the row and sets the value on the given root EObject. */// ww w . j a va2 s .c o m // BEGIN COMPLEX CODE private void extractRowInformation(final Row dmrRow, final Row eObjectRow, final EObject eObject, SpreadsheetImportResult errorReports, String sheetname, int sheetId, Map<String, VDomainModelReference> sheetColumnToDMRMap, Map<VDomainModelReference, EMFFormsSpreadsheetValueConverter> converterMap, MigrationInformation information) { for (int columnId = 1; columnId < dmrRow.getLastCellNum(); columnId++) { final String sheetColId = sheetId + "_" + columnId; //$NON-NLS-1$ final Cell cell = dmrRow.getCell(columnId); if (!sheetColumnToDMRMap.containsKey(sheetColId)) { final VDomainModelReference dmr = getDomainModelReference(cell, errorReports, eObject, sheetname, columnId, information); sheetColumnToDMRMap.put(sheetColId, dmr); } final VDomainModelReference dmr = sheetColumnToDMRMap.get(sheetColId); if (dmr == null) { continue; } /* resolve dmr */ if (!resolveDMR(dmr, eObject)) { errorReports.reportError(Severity.ERROR, LocalizationServiceHelper.getString(getClass(), "ImportError_DMRResolvementFailed"), //$NON-NLS-1$ ErrorFactory.eINSTANCE.createEMFLocation(eObject, ErrorFactory.eINSTANCE.createDMRLocation(dmr)), ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, 0, getStringCellValue(cell))); continue; } /* initiate databinding */ Setting setting; try { setting = getSetting(dmr, eObject); } catch (final DatabindingFailedException ex) { errorReports.reportError(Severity.ERROR, LocalizationServiceHelper.getString(getClass(), MessageFormat.format("ImportError_DatabindingFailed", ex.getMessage())), //$NON-NLS-1$ ErrorFactory.eINSTANCE.createEMFLocation(eObject, ErrorFactory.eINSTANCE.createDMRLocation(dmr)), ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, 0, getStringCellValue(cell))); continue; } /* access value converter */ if (!converterMap.containsKey(dmr)) { try { final EMFFormsSpreadsheetValueConverter converter = getValueConverter(dmr, eObject); converterMap.put(dmr, converter); } catch (final EMFFormsConverterException ex) { errorReports.reportError(Severity.ERROR, LocalizationServiceHelper.getString(getClass(), "ImportError_NoValueConverter"), //$NON-NLS-1$ ErrorFactory.eINSTANCE.createEMFLocation(eObject, ErrorFactory.eINSTANCE.createDMRLocation(dmr)), ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, 0, getStringCellValue(cell))); continue; } } final EMFFormsSpreadsheetValueConverter converter = converterMap.get(dmr); final EStructuralFeature feature = setting.getEStructuralFeature(); /* access cell with value */ Cell rowCell; if (feature.isUnsettable()) { rowCell = eObjectRow.getCell(columnId, Row.RETURN_NULL_AND_BLANK); } else { rowCell = eObjectRow.getCell(columnId, Row.CREATE_NULL_AS_BLANK); } if (rowCell == null) { /* no error -> unsettable feature */ errorReports.getSettingToSheetMap() .add(ErrorFactory.eINSTANCE.createSettingToSheetMapping(createSettingLocation(setting), ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, eObjectRow.getRowNum(), getStringCellValue(cell)))); continue; } /* convert value */ Object convertedValue; try { convertedValue = converter.getCellValue(rowCell, feature); } catch (final EMFFormsConverterException ex) { errorReports.reportError(Severity.ERROR, MessageFormat.format( LocalizationServiceHelper.getString(getClass(), "ImportError_ValueConversionFailed"), //$NON-NLS-1$ ex.getMessage()), ErrorFactory.eINSTANCE.createEMFLocation(eObject, createSettingLocation(setting), ErrorFactory.eINSTANCE.createDMRLocation(dmr)), ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, eObjectRow.getRowNum(), getStringCellValue(cell))); continue; } /* check converted value */ if (convertedValue != null) { if (!checkTypes(feature, convertedValue)) { errorReports.reportError(Severity.ERROR, LocalizationServiceHelper.getString(getClass(), "ImportError_InvalidType"), //$NON-NLS-1$ ErrorFactory.eINSTANCE.createEMFLocation(eObject, createSettingLocation(setting), ErrorFactory.eINSTANCE.createDMRLocation(dmr)), ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, eObjectRow.getRowNum(), getStringCellValue(cell))); continue; } } /* set value */ setting.set(convertedValue); errorReports.getSettingToSheetMap() .add(ErrorFactory.eINSTANCE.createSettingToSheetMapping(createSettingLocation(setting), ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, eObjectRow.getRowNum(), getStringCellValue(cell)))); } }
From source file:org.efaps.esjp.common.file.FileUtil_Base.java
License:Apache License
/** * Copy row./*from w w w. j a v a2s . c o m*/ * * @param _srcSheet the src sheet * @param _destSheet the dest sheet * @param _srcRow the src row * @param _destRow the dest row * @param _styleMap the style map */ protected void copyRow(final Sheet _srcSheet, final Sheet _destSheet, final Row _srcRow, final Row _destRow, final Map<Integer, CellStyle> _styleMap) { final Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<>(); _destRow.setHeight(_srcRow.getHeight()); final int deltaRows = _destRow.getRowNum() - _srcRow.getRowNum(); for (int j = _srcRow.getFirstCellNum(); j <= _srcRow.getLastCellNum(); j++) { final Cell oldCell = _srcRow.getCell(j); // ancienne cell Cell newCell = _destRow.getCell(j); // new cell if (oldCell != null) { if (newCell == null) { newCell = _destRow.createCell(j); } copyCell(oldCell, newCell, _styleMap); final CellRangeAddress mergedRegion = getMergedRegion(_srcSheet, _srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { final CellRangeAddress newMergedRegion = new CellRangeAddress( mergedRegion.getFirstRow() + deltaRows, mergedRegion.getLastRow() + deltaRows, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn()); final CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); _destSheet.addMergedRegion(wrapper.range); } } } } }
From source file:org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java
License:Open Source License
private void loadRows(Sheet _xlsSheet, SheetBuilder _sheetBuilder) { int currentRowIndex = 0; for (final Row row : _xlsSheet) { final int rowIndex = row.getRowNum(); while (rowIndex > currentRowIndex) { _sheetBuilder.beginRow();/*from ww w .j a v a2 s . co m*/ _sheetBuilder.endRow(); currentRowIndex++; } final RowBuilder rowBuilder = _sheetBuilder.beginRow(); int currentColIndex = 0; for (Cell cell : row) { final int columnIndex = cell.getColumnIndex(); while (columnIndex > currentColIndex) { rowBuilder.addEmptyCell(); currentColIndex++; } loadCell(cell, rowBuilder); currentColIndex++; } rowBuilder.endRow(); currentRowIndex++; } }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
private static <T> void read(ExcelProcessControllerImpl controller, ExcelReadContext<T> context, Sheet sheet, int startRow, Integer pageSize, Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMapping, Class<T> targetClass, ExcelReadRowProcessor<T> processor, boolean isTrimSpace) { Assert.isTrue(sheet != null, "sheet can't be null"); Assert.isTrue(startRow >= 0, "startRow must greater than or equal to 0"); Assert.isTrue(pageSize == null || pageSize >= 1, "pageSize == null || pageSize >= 1"); Assert.isTrue(fieldMapping != null, "fieldMapping can't be null"); // Assert.isTrue(targetClass != null, "clazz can't be null"); List<T> list = context.getDataList(); if (sheet.getPhysicalNumberOfRows() == 0) { return;//from w w w .j a v a 2 s . c om } // int endRow = sheet.getLastRowNum(); if (pageSize != null) { endRow = startRow + pageSize - 1; } for (int i = startRow; i <= endRow; i++) { Row row = sheet.getRow(i); // proc row context.setCurRow(row); context.setCurRowIndex(i); context.setCurCell(null); context.setCurColIndex(null); T t = null; if (!fieldMapping.isEmpty()) { t = readRow(context, row, fieldMapping, targetClass, processor, isTrimSpace); } if (processor != null) { try { controller.reset(); t = processor.process(controller, context, row, t); } catch (RuntimeException re) { if (re instanceof ExcelReadException) { ExcelReadException ere = (ExcelReadException) re; ere.setRowIndex(row.getRowNum()); // ere.setColIndex(); throw ere; } else { ExcelReadException e = new ExcelReadException(re); e.setRowIndex(row.getRowNum()); e.setColIndex(null); e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION); throw e; } } } if (!controller.isDoSkip()) { list.add(t); } if (controller.isDoBreak()) { break; } } }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
private static Object procValueConvert(ExcelReadContext<?> context, Row row, Cell cell, ExcelReadFieldMappingAttribute entry, String fieldName, Object value) { Object convertedValue = value; if (entry.getValueMapping() != null) { ExcelReadCellValueMapping valueMapping = entry.getValueMapping(); String strValue = TypeUtils.castToString(value); convertedValue = valueMapping.get(strValue); if (convertedValue == null) { if (!valueMapping.containsKey(strValue)) { if (valueMapping.isSettedDefaultValue()) { if (valueMapping.isSettedDefaultValueWithDefaultInput()) { convertedValue = value; } else { convertedValue = valueMapping.getDefaultValue(); }//from w w w. j a v a2 s .com } else if (valueMapping.getDefaultProcessor() != null) { try { convertedValue = valueMapping.getDefaultProcessor().process(context, cell, new ExcelCellValue(value)); } catch (RuntimeException re) { if (re instanceof ExcelReadException) { ExcelReadException ere = (ExcelReadException) re; ere.setRowIndex(row.getRowNum()); ere.setColIndex(cell.getColumnIndex()); throw ere; } else { ExcelReadException e = new ExcelReadException(re); e.setRowIndex(row.getRowNum()); e.setColIndex(cell.getColumnIndex()); e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION); throw e; } } if (convertedValue != null && convertedValue instanceof ExcelCellValue) { convertedValue = value; } } else { ExcelReadException e = new ExcelReadException("Cell value is value " + strValue); e.setRowIndex(row.getRowNum()); e.setColIndex(cell.getColumnIndex()); e.setCode(ExcelReadException.CODE_OF_CELL_VALUE_NOT_MATCHED); throw e; } } } } else if (entry.getCellProcessor() != null) { try { convertedValue = entry.getCellProcessor().process(context, cell, new ExcelCellValue(value)); } catch (RuntimeException re) { if (re instanceof ExcelReadException) { ExcelReadException ere = (ExcelReadException) re; ere.setRowIndex(row.getRowNum()); ere.setColIndex(cell.getColumnIndex()); throw ere; } else { ExcelReadException e = new ExcelReadException(re); e.setRowIndex(row.getRowNum()); e.setColIndex(cell.getColumnIndex()); e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION); throw e; } } if (convertedValue != null && convertedValue instanceof ExcelCellValue) { convertedValue = value; } } if (convertedValue == null && entry.isRequired()) { ExcelReadException e = new ExcelReadException("Cell value is null"); e.setRowIndex(row.getRowNum()); e.setColIndex(cell.getColumnIndex()); e.setCode(ExcelReadException.CODE_OF_CELL_VALUE_REQUIRED); throw e; } else { return convertedValue; } }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings({ "rawtypes", "unchecked" })
private static void writeRow(ExcelWriteContext context, InnerRow templateRow, Row row, Object rowData,
ExcelWriteSheetProcessor sheetProcessor) {
boolean useTemplate = false;
if (templateRow != null) {
useTemplate = true;/*from ww w .j av a2 s . c o m*/
}
ExcelWriteFieldMapping fieldMapping = sheetProcessor.getFieldMapping();
for (Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> entry : fieldMapping.export()
.entrySet()) {
String fieldName = entry.getKey();
Map<Integer, ExcelWriteFieldMappingAttribute> map = entry.getValue();
for (Map.Entry<Integer, ExcelWriteFieldMappingAttribute> fieldValueMapping : map.entrySet()) {
Integer colIndex = fieldValueMapping.getKey();
ExcelWriteFieldMappingAttribute attribute = fieldValueMapping.getValue();
Object val = null;
if (rowData != null) {
val = getFieldValue(rowData, fieldName, sheetProcessor.isTrimSpace());
}
// proc cell
Cell cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
if (templateRow != null) {
InnerCell tempalteCell = templateRow.getCell(colIndex);
if (tempalteCell != null) {
cell.setCellStyle(tempalteCell.getCellStyle());
cell.setCellType(tempalteCell.getCellType());
}
}
context.setCurColIndex(colIndex);
context.setCurCell(cell);
ExcelWriteCellValueMapping valueMapping = attribute.getValueMapping();
ExcelWriteCellProcessor processor = attribute.getCellProcessor();
if (valueMapping != null) {
String key = null;
if (val != null) {
key = val.toString();
}
Object cval = valueMapping.get(key);
if (cval != null) {
writeCell(row.getRowNum(), colIndex, cell, cval, useTemplate, attribute, rowData);
} else {
if (!valueMapping.containsKey(key)) {
if (valueMapping.isSettedDefaultValue()) {
if (valueMapping.isSettedDefaultValueWithDefaultInput()) {
writeCell(row.getRowNum(), colIndex, cell, val, useTemplate, attribute,
rowData);
} else {
writeCell(row.getRowNum(), colIndex, cell, valueMapping.getDefaultValue(),
useTemplate, attribute, rowData);
}
} else if (valueMapping.getDefaultProcessor() != null) {
valueMapping.getDefaultProcessor().process(context, rowData, cell);
} else {
ExcelWriteException ex = new ExcelWriteException("Field value is " + key);
ex.setCode(ExcelWriteException.CODE_OF_FIELD_VALUE_NOT_MATCHED);
ex.setColIndex(colIndex);
ex.setRowIndex(row.getRowNum());
throw ex;
}
} else {
// contains null
// ok
}
}
} else if (processor != null) {
writeCell(cell, val, useTemplate, attribute, rowData);
try {
processor.process(context, val, cell);
} catch (RuntimeException e) {
if (e instanceof ExcelWriteException) {
ExcelWriteException ewe = (ExcelWriteException) e;
ewe.setColIndex(colIndex);
ewe.setRowIndex(row.getRowNum());
throw ewe;
} else {
ExcelWriteException ewe = new ExcelWriteException(e);
ewe.setColIndex(colIndex);
ewe.setCode(ExcelWriteException.CODE_OF_PROCESS_EXCEPTION);
ewe.setRowIndex(row.getRowNum());
throw ewe;
}
}
} else {
writeCell(cell, val, useTemplate, attribute, rowData);
}
}
}
}
From source file:org.isisaddons.module.excel.dom.CellMarshaller.java
License:Apache License
private static void setCellComment(final Cell cell, final String commentText) { Sheet sheet = cell.getSheet();/* w w w.ja v a 2 s. c o m*/ Row row = cell.getRow(); Workbook workbook = sheet.getWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 3); Drawing drawing = sheet.createDrawingPatriarch(); Comment comment1 = drawing.createCellComment(anchor); RichTextString commentRtf = creationHelper.createRichTextString(commentText); comment1.setString(commentRtf); Comment comment = comment1; cell.setCellComment(comment); }
From source file:org.isisaddons.module.excel.dom.ExcelConverter.java
License:Apache License
<T> List<T> fromBytes(final Class<T> cls, final byte[] bs, final DomainObjectContainer container) throws IOException, InvalidFormatException { final List<T> importedItems = Lists.newArrayList(); final ObjectSpecification objectSpec = specificationLoader.loadSpecification(cls); final ViewModelFacet viewModelFacet = objectSpec.getFacet(ViewModelFacet.class); try (ByteArrayInputStream bais = new ByteArrayInputStream(bs)) { final Workbook wb = org.apache.poi.ss.usermodel.WorkbookFactory.create(bais); final CellMarshaller cellMarshaller = this.newCellMarshaller(wb); final Sheet sheet = wb.getSheetAt(0); boolean header = true; final Map<Integer, Property> propertyByColumn = Maps.newHashMap(); for (final Row row : sheet) { if (header) { for (final Cell cell : row) { if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { final int columnIndex = cell.getColumnIndex(); final String propertyName = cellMarshaller.getStringCellValue(cell); final OneToOneAssociation property = getAssociation(objectSpec, propertyName); if (property != null) { final Class<?> propertyType = property.getSpecification().getCorrespondingClass(); propertyByColumn.put(columnIndex, new Property(propertyName, property, propertyType)); }//from www .j a va2 s.com } } header = false; } else { // detail try { // Let's require at least one column to be not null for detecting a blank row. // Excel can have physical rows with cells empty that it seem do not existent for the user. ObjectAdapter templateAdapter = null; T imported = null; for (final Cell cell : row) { final int columnIndex = cell.getColumnIndex(); final Property property = propertyByColumn.get(columnIndex); if (property != null) { final OneToOneAssociation otoa = property.getOneToOneAssociation(); final Object value = cellMarshaller.getCellValue(cell, otoa); if (value != null) { if (imported == null) { // copy the row into a new object imported = container.newTransientInstance(cls); templateAdapter = this.adapterManager.adapterFor(imported); } final ObjectAdapter valueAdapter = this.adapterManager.adapterFor(value); otoa.set(templateAdapter, valueAdapter); } } else { // not expected; just ignore. } } if (imported != null) { if (viewModelFacet != null) { // if there is a view model, then use the imported object as a template // in order to create a regular view model. final String memento = viewModelFacet.memento(imported); final T viewModel = container.newViewModelInstance(cls, memento); importedItems.add(viewModel); } else { // else, just return the imported items as simple transient instances. importedItems.add(imported); } } } catch (final Exception e) { bais.close(); throw new ExcelService.Exception( String.format("Error processing Excel row nr. %d. Message: %s", row.getRowNum(), e.getMessage()), e); } } } } return importedItems; }
From source file:org.isisaddons.module.excel.dom.util.ExcelConverter.java
License:Apache License
<T> List<T> fromBytes(final Class<T> cls, final byte[] bs, final DomainObjectContainer container, final ExcelServiceImpl.SheetLookupPolicy sheetLookupPolicy) throws IOException, InvalidFormatException { final List<T> importedItems = Lists.newArrayList(); final ObjectSpecification objectSpec = specificationLoader.loadSpecification(cls); final ViewModelFacet viewModelFacet = objectSpec.getFacet(ViewModelFacet.class); try (ByteArrayInputStream bais = new ByteArrayInputStream(bs)) { final Workbook wb = org.apache.poi.ss.usermodel.WorkbookFactory.create(bais); final CellMarshaller cellMarshaller = this.newCellMarshaller(wb); final List<String> sheetNames = determineSheetNames(cls); final Sheet sheet = lookupSheet(wb, sheetNames, sheetLookupPolicy); boolean header = true; final Map<Integer, Property> propertyByColumn = Maps.newHashMap(); for (final Row row : sheet) { if (header) { for (final Cell cell : row) { if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { final int columnIndex = cell.getColumnIndex(); final String propertyName = cellMarshaller.getStringCellValue(cell); final OneToOneAssociation property = getAssociation(objectSpec, propertyName); if (property != null) { final Class<?> propertyType = property.getSpecification().getCorrespondingClass(); propertyByColumn.put(columnIndex, new Property(propertyName, property, propertyType)); }//from w w w. j ava2 s . c o m } } header = false; } else { // detail try { // Let's require at least one column to be not null for detecting a blank row. // Excel can have physical rows with cells empty that it seem do not existent for the user. ObjectAdapter templateAdapter = null; T imported = null; for (final Cell cell : row) { final int columnIndex = cell.getColumnIndex(); final Property property = propertyByColumn.get(columnIndex); if (property != null) { final OneToOneAssociation otoa = property.getOneToOneAssociation(); final Object value = cellMarshaller.getCellValue(cell, otoa); if (value != null) { if (imported == null) { // copy the row into a new object imported = container.newTransientInstance(cls); templateAdapter = this.adapterManager.adapterFor(imported); } final ObjectAdapter valueAdapter = this.adapterManager.adapterFor(value); otoa.set(templateAdapter, valueAdapter, InteractionInitiatedBy.USER); } } else { // not expected; just ignore. } } if (imported != null) { if (viewModelFacet != null) { // if there is a view model, then use the imported object as a template // in order to create a regular view model. final String memento = viewModelFacet.memento(imported); final T viewModel = container.newViewModelInstance(cls, memento); importedItems.add(viewModel); } else { // else, just return the imported items as simple transient instances. importedItems.add(imported); } } } catch (final Exception e) { bais.close(); throw new ExcelService.Exception( String.format("Error processing Excel row nr. %d. Message: %s", row.getRowNum(), e.getMessage()), e); } } } } return importedItems; }