List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetName
String getSheetName(int sheet);
From source file:org.dbunit.dataset.excel.MyXlsDataSet.java
License:Open Source License
/** * Creates a new XlsDataSet object that loads the specified Excel document. *//* w ww . ja v a 2 s . c o m*/ public MyXlsDataSet(InputStream in) throws IOException, DataSetException { _tables = super.createTableNameMap(); Workbook workbook; try { workbook = WorkbookFactory.create(in); } catch (InvalidFormatException e) { throw new IOException(e); } int sheetCount = workbook.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { ITable table = new MyXlsTable(workbook.getSheetName(i), workbook.getSheetAt(i)); _tables.add(table.getTableMetaData().getTableName(), table); } }
From source file:org.dbunit.dataset.excel.XlsDataSet.java
License:Open Source License
/** * Creates a new XlsDataSet object that loads the specified Excel document. *//* w w w.ja v a2 s . c o m*/ public XlsDataSet(InputStream in) throws IOException, DataSetException { _tables = super.createTableNameMap(); Workbook workbook; try { workbook = WorkbookFactory.create(in); } catch (InvalidFormatException e) { throw new IOException(e); } int sheetCount = workbook.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { ITable table = new XlsTable(workbook.getSheetName(i), workbook.getSheetAt(i)); _tables.add(table.getTableMetaData().getTableName(), table); } }
From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetImporterImpl.java
License:Open Source License
/** * Returns a Map from EObject-ID to Sheet-ID to Row-ID. *//*from w ww . jav a 2 s.c om*/ private Map<String, Map<Integer, Integer>> parseIds(Workbook workbook, SpreadsheetImportResult errorReports) { final Map<String, Map<Integer, Integer>> result = new LinkedHashMap<String, Map<Integer, Integer>>(); for (int sheetId = 0; sheetId < workbook.getNumberOfSheets(); sheetId++) { final Sheet sheet = workbook.getSheetAt(sheetId); final Row labelRow = sheet.getRow(0); if (labelRow == null) { errorReports.reportError(Severity.ERROR, MessageFormat.format( LocalizationServiceHelper.getString(getClass(), "ImportError_SheetEmpty"), //$NON-NLS-1$ sheet.getSheetName()), ErrorFactory.eINSTANCE.createSheetLocation(workbook.getSheetName(sheetId), 0, 0, "NO CELL")); //$NON-NLS-1$ continue; } final Cell idColumnLabelCell = labelRow.getCell(0, Row.CREATE_NULL_AS_BLANK); final Comment cellComment = idColumnLabelCell.getCellComment(); if (cellComment != null && cellComment.getString() != null && IGNORE_SHEET.equals(cellComment.getString().getString())) { continue; } final String idColumnLabel = getStringCellValue(idColumnLabelCell); if (!EMFFormsIdProvider.ID_COLUMN.equals(idColumnLabel)) { /* ID Column is missing. We have to ignore this sheet */ errorReports.reportError(Severity.ERROR, MessageFormat.format( LocalizationServiceHelper.getString(getClass(), "ImportError_FirstColumnWrong"), //$NON-NLS-1$ EMFFormsIdProvider.ID_COLUMN, idColumnLabel), ErrorFactory.eINSTANCE.createSheetLocation(workbook.getSheetName(sheetId), 0, 0, "NO CELL")); //$NON-NLS-1$ continue; } for (int rowId = 3; rowId <= sheet.getLastRowNum(); rowId++) { final Row row = sheet.getRow(rowId); if (row == null) { errorReports.reportError(Severity.INFO, LocalizationServiceHelper.getString(getClass(), "ImportError_EmptyRow"), //$NON-NLS-1$ ErrorFactory.eINSTANCE.createSheetLocation(workbook.getSheetName(sheetId), 0, rowId, EMFFormsIdProvider.ID_COLUMN)); continue; } final String eObjectId = getStringCellValue(row.getCell(0, Row.CREATE_NULL_AS_BLANK)); if (eObjectId == null || eObjectId.isEmpty()) { /* EObject id deleted */ errorReports.reportError(Severity.ERROR, LocalizationServiceHelper.getString(getClass(), "ImportError_NoEObjectID"), //$NON-NLS-1$ ErrorFactory.eINSTANCE.createSheetLocation(workbook.getSheetName(sheetId), 0, rowId, EMFFormsIdProvider.ID_COLUMN)); continue; } if (!result.containsKey(eObjectId)) { result.put(eObjectId, new LinkedHashMap<Integer, Integer>()); } // each sheetid should only be mapped once to each eobjectid if (result.get(eObjectId).containsKey(sheetId)) { /* duplicate EObject ID */ errorReports.reportError(Severity.ERROR, LocalizationServiceHelper.getString(getClass(), "ImportError_DuplicateEObjectID"), //$NON-NLS-1$ ErrorFactory.eINSTANCE.createSheetLocation(workbook.getSheetName(sheetId), 0, rowId, EMFFormsIdProvider.ID_COLUMN)); continue; } result.get(eObjectId).put(sheetId, rowId); } } return result; }
From source file:org.joeffice.spreadsheet.SpreadsheetComponent.java
License:Apache License
public void load(Workbook workbook) { this.workbook = workbook; int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); String sheetName = workbook.getSheetName(i); JPanel sheetPanel = new SheetComponent(sheet, this); addTab(sheetName, sheetPanel);/* w ww . j ava2 s .co m*/ } setSelectedIndex(workbook.getActiveSheetIndex()); formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); }
From source file:org.jplus.compare.excel.service.CompareService.java
public static List<String> getSheetNames(String excelPath) { List<String> names = new ArrayList<String>(); InputStream inputStream = null; try {//from w w w .java2s. c o m File file = new File(excelPath); inputStream = new FileInputStream(file); Workbook workbook = new HSSFWorkbook(inputStream); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { names.add(workbook.getSheetName(i)); } } catch (IOException ex) { Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex); } finally { if (inputStream != null) { try { inputStream.close(); } catch (IOException ex) { Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex); } } } return names; }
From source file:org.mifos.dmt.excel.CreateExcel.java
License:Open Source License
public Workbook generate(String baseTemplate, DMTMigrationParams params) throws InvalidFormatException, IOException, DMTException { InputStream inp = new FileInputStream(baseTemplate); Workbook wb = WorkbookFactory.create(inp); ArrayList<String> sheetSpec = readSheetSpec(params); for (int i = wb.getNumberOfSheets(); i > 0; i--) { String sheetName = wb.getSheetName(i - 1); if (sheetSpec.contains(sheetName)) { wb.removeSheetAt(i - 1);/*from w ww . j a v a 2 s . c o m*/ } else { logger.info("Adding sheet : " + sheetName); } } Sheet configSheet = wb.getSheetAt(0); editConfigSheet(configSheet); return wb; }
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * OK action./*from w ww . j a v a 2 s . c o m*/ * * @throws ServiceException */ public void doOK() throws ServiceException { PersistenceManager pm = this.getPm(); ApplicationContext app = this.getApp(); this.doRefresh(); if (!this.hasPermission) { this.errorMessage = "no permission to run this wizard"; } else { String location = app.getTempFileName(UPLOAD_FILE_FIELD_NAME, ""); try { if (new File(location + ".INFO").exists() && new File(location).exists() && (new File(location).length() > 0)) { String contentMimeType = null; String contentName = null; try { // mimeType and name BufferedReader r = new BufferedReader(new FileReader(location + ".INFO")); contentMimeType = r.readLine(); contentName = r.readLine(); r.close(); new File(location + ".INFO").delete(); } catch (Exception ignore) { } if ((contentName != null) && !contentName.isEmpty() && (contentMimeType != null) && !contentMimeType.isEmpty()) { // the calling object determines which columns are optional/required in the spreadsheet ProductConfigurationTypeSet productConfigurationTypeSet = null; ProductConfigurationType productConfigurationType = null; PropertySet propertySet = null; CrxObject crxObject = null; String callerName = null; String callerParentName = null; ImportTarget importTarget = ImportTarget.NA; // case 1: // required: Property_name // ProductConfigurationTypeSet_name // ProductConfigurationType_name if (this.getObject() instanceof org.opencrx.kernel.product1.jmi1.Segment) { importTarget = ImportTarget.ProductSegment; } // case 2: // required: Property_name // ProductConfigurationType_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) else if (this.getObject() instanceof ProductConfigurationTypeSet) { importTarget = ImportTarget.ProductConfigurationTypeSet; productConfigurationTypeSet = (ProductConfigurationTypeSet) this.getObject(); callerName = ((ProductConfigurationTypeSet) this.getObject()).getName(); } // case 3: // required: Property_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) // ProductConfigurationType_name (if provided, then only Properties of matching ProductConfigurationTypes are considered) else if (this.getObject() instanceof ProductConfigurationType) { importTarget = ImportTarget.ProductConfigurationType; productConfigurationType = (ProductConfigurationType) this.getObject(); callerName = ((ProductConfigurationType) this.getObject()).getName(); RefObject_1_0 parentObj = (RefObject_1_0) pm .getObjectById(this.getObject().refGetPath().getParent().getParent()); if (parentObj instanceof ProductConfigurationTypeSet) { callerParentName = ((ProductConfigurationTypeSet) parentObj).getName(); } } // case 4: // required: Property_name // optional: PropertySet_name (if provided, then only Properties of matching PropertySets are considered) else if (this.getObject() instanceof PropertySet) { importTarget = ImportTarget.PropertySet; propertySet = (PropertySet) this.getObject(); callerName = propertySet.getName(); } // case 5: // required: PropertySet_name // Property_name else if (this.getObject() instanceof CrxObject) { importTarget = ImportTarget.CrxObject; crxObject = (CrxObject) this.getObject(); } // Get product segment org.opencrx.kernel.product1.jmi1.Segment productSegment = Products.getInstance() .getProductSegment(pm, this.getProviderName(), this.getSegmentName()); int idxProperty_dtype = -1; int idxProperty_name = -1; int idxProperty_description = -1; int idxProperty_value = -1; int idxPropertySet_name = -1; int idxPropertySet_description = -1; int idxProductConfigurationTypeSet_name = -1; int idxProductConfigurationTypeSet_description = -1; int idxProductConfigurationType_name = -1; int idxProductConfigurationType_description = -1; // verify whether File exists // Read workbook Workbook wb = null; try { wb = WorkbookFactory.create(new FileInputStream(location)); } catch (Exception e) { this.errorMessage = e.getMessage(); } if (wb != null) { //for (int i = 0; i < workbook.getNumberOfSheets(); i++) { // read first sheet only!!! for (int i = 0; i < 1; i++) { Sheet sheet = wb.getSheetAt(i); int linesRead = 0; int propertiesUpdated = 0; this.importReport += ""; Iterator<Row> rows = sheet.rowIterator(); int nRow = 0; int maxCell = 0; HSSFRow row = null; Map<String, String> attributeMap = new TreeMap<String, String>(); if (rows.hasNext()) { nRow += 1; // read first row with attribute names this.importReport += "<tr class='gridTableHeaderFull'>"; this.importReport += "<td>#</td>"; row = (HSSFRow) rows.next(); Iterator<Cell> cells = row.cellIterator(); int nCell = 0; while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > maxCell) { maxCell = nCell; } try { if ((cell.getCellType() == HSSFCell.CELL_TYPE_STRING) && (cell.getStringCellValue() != null)) { boolean isSearchAttribute = false; String cellValue = (cell.getStringCellValue().trim()); attributeMap.put(DECIMAL_FORMAT_0000.format(nCell), cellValue); // get idx of select attributes if (ATTR_PROPERTY_DTYPE.compareToIgnoreCase(cellValue) == 0) { idxProperty_dtype = nCell; } else if (ATTR_PROPERTY_NAME.compareToIgnoreCase(cellValue) == 0) { idxProperty_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTY_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProperty_description = nCell; } else if (ATTR_PROPERTY_VALUE .compareToIgnoreCase(cellValue) == 0) { idxProperty_value = nCell; } else if (ATTR_PROPERTYSET_NAME .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTYSET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_description = nCell; } this.importReport += "<td " + (isSearchAttribute ? "class='searchAttr' title='attribute used for matching'" : "") + ">" + cellValue + "</td>"; } else { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + "[not a string cell]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + ec.getMessage() + "</td>"; } } this.importReport += "</tr>"; } while (rows.hasNext()) { nRow += 1; linesRead += 1; row = (HSSFRow) rows.next(); String propertyType = null; String propertyName = null; String propertyDescription = null; HSSFCell propertyValue = null; String propertySetName = null; String propertySetDescription = null; String productConfigurationTypeSetName = null; String productConfigurationTypeSetDescription = null; String productConfigurationTypeName = null; String productConfigurationTypeDescription = null; String cellId = null; Map<String, Cell> valueMap = new TreeMap<String, Cell>( String.CASE_INSENSITIVE_ORDER); String appendErrorRow = null; this.importReport += "<tr class='gridTableRowFull'>"; this.importReport += "<td id='r" + nRow + "'><b>" + DECIMAL_FORMAT_0000.format(nRow) + "</b></td>"; String jsBuffer = ""; try { Iterator<Cell> cells = row.cellIterator(); int nCell = 0; int currentCell = 0; appendErrorRow = null; while (cells.hasNext()) { //HSSFCell cell = (HSSFCell)row.getCell((short)0); HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > currentCell) { this.importReport += "<td colspan=\"" + (nCell - currentCell) + "\" class=\"empty\"> </td>"; } currentCell = nCell + 1; try { cellId = "id='r" + nRow + (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString().toUpperCase() + "'"; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue().trim(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); if (nCell == idxProperty_dtype) { propertyType = cellValue; } else if (nCell == idxProperty_name) { propertyName = cellValue; } else if (nCell == idxProperty_description) { propertyDescription = cellValue; } else if (nCell == idxProperty_value) { propertyValue = cell; } else if (nCell == idxPropertySet_name) { propertySetName = cellValue; } else if (nCell == idxPropertySet_description) { propertySetDescription = cellValue; } else if (nCell == idxProductConfigurationTypeSet_name) { productConfigurationTypeSetName = cellValue; } else if (nCell == idxProductConfigurationTypeSet_description) { productConfigurationTypeSetDescription = cellValue; } else if (nCell == idxProductConfigurationType_name) { productConfigurationTypeName = cellValue; } else if (nCell == idxProductConfigurationType_description) { productConfigurationTypeDescription = cellValue; } this.importReport += "<td " + cellId + ">" + (cellValue != null ? (cellValue.replace("\r\n", EOL_HTML)).replace("\n", EOL_HTML) : "") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { if (nCell == idxProperty_value) { propertyValue = cell; } BigDecimal cellValue = new BigDecimal( cell.getNumericCellValue()); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + cellValue + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (nCell == idxProperty_value) { propertyValue = cell; } boolean cellValue = cell.getBooleanCellValue(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + (cellValue ? "TRUE" : "FALSE") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + " class=\"empty\"> </td>"; } else { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + "[cell-type (" + cell.getCellType() + ") not supported]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + cell.getCellFormula() + "</td>"; } } if (nCell < maxCell) { this.importReport += "<td colspan=\"" + (maxCell - nCell) + "\" class=\"empty\"></td>"; } } catch (Exception e) { this.importReport += "<td class='err' colspan=\"" + (maxCell + 2) + "\">ERROR in Attribute Row!</td>"; } // process row Property property = null; if (this.isSupportedDtypeValue(propertyType, propertyValue)) { /* case 1 */ if (importTarget == ImportTarget.ProductSegment && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeSetName != null && !productConfigurationTypeSetName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from Product Segment)';"; if (propertySetName == null || propertySetName.isEmpty()) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (org.opencrx.kernel.product1.jmi1.ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 2 */ } else if (importTarget == ImportTarget.ProductConfigurationTypeSet && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationTypeSet)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerName != null && productConfigurationTypeSetName != null && callerName.equals(productConfigurationTypeSetName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 3 */ } else if (importTarget == ImportTarget.ProductConfigurationType && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationType)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerParentName != null && productConfigurationTypeSetName != null && callerParentName .equals(productConfigurationTypeSetName))) && ((productConfigurationTypeName == null || productConfigurationTypeName.isEmpty()) || (callerName != null && productConfigurationTypeName != null && callerName .equals(productConfigurationTypeName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm.getObjectById( property.refGetPath().getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 4 */ } else if (importTarget == ImportTarget.PropertySet && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from PropertySet)';"; if (((propertySetName == null || propertySetName.isEmpty()) || (callerName != null && propertySetName != null && callerName.equals(propertySetName))) && (productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, null, propertySet, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 5 */ } else if (importTarget == ImportTarget.CrxObject && propertyName != null && !propertyName.isEmpty() && propertySetName != null && !propertySetName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from CrxObject)';"; if ((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { //createOrUpdatePropertyOfPropertySet property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, crxObject, null, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } } else { // incomplete and/or inconsistent row --> disregard this row jsBuffer += "$('r" + nRow + "').title += 'incomplete and/or inconsistent row';"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>CELL VALUE TYPE NOT SUPPORTED</td></tr>"; } this.importReport += "</tr>"; if (property != null) { propertiesUpdated++; cellId = "r" + nRow + ATTR_PROPERTY_NAME.toUpperCase(); jsBuffer += "try{$('r" + nRow + "').className += ' ok';$('" + cellId + "').className=' ok';$('" + cellId + "').innerHTML = '<a href=\"" + this.getSelectObjectHref(property) + "\" target=\"_blank\"><b>' + " + "$('" + cellId + "').innerHTML +" + "'</b></a>'}catch(e){};"; cellId = "r" + nRow + ATTR_PROPERTY_VALUE.toUpperCase(); if (propertyValue != null) { jsBuffer += "try{$('" + cellId + "').className='ok';}catch(e){};"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>VERIFY Property DTYPE/NAME/VALUE</td></tr>"; jsBuffer += "$('r" + nRow + "').className += 'nok';"; } if (appendErrorRow != null) { this.importReport += appendErrorRow; } valueMap = null; this.importReport += "<tr style=\"display:none;\">"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += jsBuffer.length() > 0 ? "<script language='javascript' type='text/javascript'>" + jsBuffer + "</script>" : ""; this.importReport += " </td>"; this.importReport += "</tr>"; } /* while */ // Spacer this.importReport += "<tr class='gridTableRowFull' style=\"background-color:white;\">"; this.importReport += " <td colspan='" + (maxCell + 2) + "'> </td>"; this.importReport += "</tr>"; // Summary this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += " Sheet: <b>" + wb.getSheetName(i) + "</b> |"; this.importReport += " data lines <b>read: " + linesRead + "</b><br>"; this.importReport += "</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td> </td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">Created / Updated</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td>Properties</td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">" + propertiesUpdated + "</td>"; this.importReport += "</tr>"; if (linesRead != propertiesUpdated) { this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td class=\"err\" colspan=\"" + (maxCell + 2) + "\">WARNING: some data lines were not processed due to data errors (e.g. multiple matches, missing name, etc.)</td>"; this.importReport += "</tr>"; } } } } } else { this.errorMessage = "No Excel workbook selected"; } } finally { new File(location).delete(); } } }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * @see ReportRenderer#render(ReportData, String, OutputStream) *///from w w w . jav a 2 s .c o m public void render(ReportData reportData, String argument, OutputStream out) throws IOException, RenderingException { try { log.debug("Attempting to render report with ExcelTemplateRenderer"); ReportDesign design = getDesign(argument); Workbook wb = getExcelTemplate(design); if (wb == null) { XlsReportRenderer xlsRenderer = new XlsReportRenderer(); xlsRenderer.render(reportData, argument, out); } else { //This should be changed to get the dataset name form a parameter DataSet ds = reportData.getDataSets().get("patient"); ArrayList<String> names = new ArrayList<String>(); for (DataSetColumn dataSetRow : ds.getMetaData().getColumns()) { names.add(dataSetRow.getName()); } Sheet s = wb.getSheetAt(0); //Trying to creat a row that has the replacement values pre-populated Row h = s.createRow(8); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); style.setFont(font); for (String name : names) { Cell c = h.createCell(names.indexOf(name)); String value = name.toUpperCase().replace("_", " "); c.setCellValue(value); c.setCellStyle(style); } Row r = s.getRow(9); for (String name : names) { Cell c = r.createCell(names.indexOf(name)); String value = "#patient." + name + "#"; c.setCellValue(value); } Map<String, String> repeatSections = getRepeatingSections(design); // Put together base set of replacements. Any dataSet with only one row is included. Map<String, Object> replacements = getBaseReplacementData(reportData, design); // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>(); Set<String> usedSheetNames = new HashSet<String>(); int numberOfSheets = wb.getNumberOfSheets(); for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) { Sheet currentSheet = wb.getSheetAt(sheetNum); String originalSheetName = wb.getSheetName(sheetNum); String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections); if (dataSetName != null) { DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements); int dataSetRowNum = 0; for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator .hasNext();) { DataSetRow dataSetRow = rowIterator.next(); dataSetRowNum++; Map<String, Object> newReplacements = getReplacementData(replacements, reportData, design, dataSetName, dataSetRow, dataSetRowNum); Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum)); sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements)); } } else { sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements)); } } // Then iterate across all of these and add them in for (int i = 0; i < sheetsToAdd.size(); i++) { addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections); } wb.write(out); } } catch (Exception e) { throw new RenderingException("Unable to render results due to: " + e, e); } }
From source file:org.openmrs.module.reporting.report.renderer.ExcelTemplateRenderer.java
License:Open Source License
/** * @see ReportRenderer#render(ReportData, String, OutputStream) *///w w w. j ava 2 s . c om public void render(ReportData reportData, String argument, OutputStream out) throws IOException, RenderingException { try { log.debug("Attempting to render report with ExcelTemplateRenderer"); ReportDesign design = getDesign(argument); Workbook wb = getExcelTemplate(design); if (wb == null) { XlsReportRenderer xlsRenderer = new XlsReportRenderer(); xlsRenderer.render(reportData, argument, out); } else { Map<String, String> repeatSections = getRepeatingSections(design); // Put together base set of replacements. Any dataSet with only one row is included. Map<String, Object> replacements = getBaseReplacementData(reportData, design); // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>(); Set<String> usedSheetNames = new HashSet<String>(); int numberOfSheets = wb.getNumberOfSheets(); for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) { Sheet currentSheet = wb.getSheetAt(sheetNum); String originalSheetName = wb.getSheetName(sheetNum); String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections); if (dataSetName != null) { DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements); int dataSetRowNum = 0; for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator .hasNext();) { DataSetRow dataSetRow = rowIterator.next(); dataSetRowNum++; Map<String, Object> newReplacements = getReplacementData(replacements, reportData, design, dataSetName, dataSetRow, dataSetRowNum); Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum)); sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements)); } } else { sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements)); } } // Then iterate across all of these and add them in for (int i = 0; i < sheetsToAdd.size(); i++) { addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections); } wb.write(out); } } catch (Exception e) { throw new RenderingException("Unable to render results due to: " + e, e); } }
From source file:org.openmrs.module.rwandareports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * @see ReportRenderer#render(ReportData, String, OutputStream) */// w w w. j a va 2 s . c o m public void render(ReportData reportData, String argument, OutputStream out) throws IOException, RenderingException { try { log.debug("Attempting to render report with ExcelTemplateRenderer"); ReportDesign design = getDesign(argument); Workbook wb = getExcelTemplate(design); if (wb == null) { XlsReportRenderer xlsRenderer = new XlsReportRenderer(); xlsRenderer.render(reportData, argument, out); } else { //This should be changed to get the dataset name form a parameter DataSet ds = reportData.getDataSets().get("patient"); ArrayList<String> names = new ArrayList<String>(); for (DataSetColumn dataSetRow : ds.getMetaData().getColumns()) { names.add(dataSetRow.getName()); } Sheet s = wb.getSheetAt(0); Row h = s.createRow(8); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); style.setFont(font); for (String name : names) { Cell c = h.createCell(names.indexOf(name)); String value = name.toUpperCase().replace("_", " "); c.setCellValue(value); c.setCellStyle(style); } //Trying to creat a row that has the replacement values pre-populated Row r = s.getRow(9); for (String name : names) { Cell c = r.createCell(names.indexOf(name)); String value = "#patient." + name + "#"; c.setCellValue(value); } ExcelUtil.formatRow(r); Map<String, String> repeatSections = getRepeatingSections(design); // Put together base set of replacements. Any dataSet with only one row is included. Map<String, Object> replacements = getBaseReplacementData(reportData, design); // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>(); Set<String> usedSheetNames = new HashSet<String>(); int numberOfSheets = wb.getNumberOfSheets(); for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) { Sheet currentSheet = wb.getSheetAt(sheetNum); String originalSheetName = wb.getSheetName(sheetNum); String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections); if (dataSetName != null) { DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements); int dataSetRowNum = 0; for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator .hasNext();) { DataSetRow dataSetRow = rowIterator.next(); dataSetRowNum++; Map<String, Object> newReplacements = getReplacementData(replacements, reportData, design, dataSetName, dataSetRow, dataSetRowNum); Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum)); sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements)); } } else { sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements)); } } // Then iterate across all of these and add them in for (int i = 0; i < sheetsToAdd.size(); i++) { addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections); } wb.write(out); } } catch (Exception e) { throw new RenderingException("Unable to render results due to: " + e, e); } }