List of usage examples for org.apache.poi.ss.usermodel Row getPhysicalNumberOfCells
int getPhysicalNumberOfCells();
From source file:org.matonto.etl.service.delimited.DelimitedConverterImpl.java
License:Open Source License
@Override public Model convert(ExcelConfig config) throws IOException, MatOntoException { String[] nextRow;/* www.jav a 2 s .c o m*/ Model convertedRDF = modelFactory.createModel(); ArrayList<ClassMapping> classMappings = parseClassMappings(config.getMapping()); try { Workbook wb = WorkbookFactory.create(config.getData()); Sheet sheet = wb.getSheetAt(0); DataFormatter df = new DataFormatter(); boolean containsHeaders = config.getContainsHeaders(); long offset = config.getOffset(); Optional<Long> limit = config.getLimit(); //Traverse each row and convert column into RDF for (Row row : sheet) { // If headers exist or the row is before the offset point, skip the row if ((containsHeaders && row.getRowNum() == 0) || row.getRowNum() - (containsHeaders ? 1 : 0) < offset || (limit.isPresent() && row.getRowNum() >= limit.get() + offset)) { continue; } nextRow = new String[row.getPhysicalNumberOfCells()]; int cellIndex = 0; for (Cell cell : row) { nextRow[cellIndex] = df.formatCellValue(cell); cellIndex++; } writeClassMappingsToModel(convertedRDF, nextRow, classMappings); } } catch (InvalidFormatException e) { throw new MatOntoException(e); } return convertedRDF; }
From source file:org.meveo.admin.parse.xls.XLSFile.java
License:Open Source License
public void parse() throws IOException { Workbook w;//from ww w .ja v a 2s . co m try { w = WorkbookFactory.create(new FileInputStream(file)); // Get the first sheet Sheet sheet = w.getSheetAt(0); // Loop over first 10 column and lines Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); String[] strs = new String[row.getPhysicalNumberOfCells()]; int cellCtr = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); strs[cellCtr++] = cell.getStringCellValue(); } contexts.add(strs); } } catch (InvalidFormatException e) { log.error("invalid file format ", e); } }
From source file:org.nekorp.workflow.desktop.control.imp.ProgramacionServicioWizardImp.java
License:Apache License
@Override public void importarArchivo(File archivo) { programacionMetadata.setDetalles(""); OPCPackage pkg = null;/*www.j a v a 2 s. c o m*/ try { addDetail("Leyendo archivo: " + archivo.getCanonicalPath()); pkg = OPCPackage.open(archivo); XSSFWorkbook wb = new XSSFWorkbook(pkg); //Workbook wb = WorkbookFactory.create(archivo); Sheet sheet = wb.getSheetAt(0); //Row encabezado = sheet.getRow(0); List<String> nombresServicios = null; boolean inicio = true; nuevosAutos = new LinkedList<>(); nuevosServicio = new LinkedList<>(); for (Row row : sheet) { if (inicio) { nombresServicios = procesarEncabezado(row); inicio = false; } else { if (row.getPhysicalNumberOfCells() == nombresServicios.size()) { Auto autoCargado = new Auto(); cargarDatosAuto(row, autoCargado); autoBridge.load(autoCargado, servicio.getAuto()); Servicio nuevoServicio = new Servicio(); if (buscarNuevoServicio(row, nuevoServicio, nombresServicios, rangoNuevo)) { //se intentan cargar servicioBridge.load(nuevoServicio, servicio); addDetail("Se encontro un nuevo servicio para el auto con numero de serie: " + autoCargado.getNumeroSerie()); addDetail("Descripcin del servicio encontrado:\n" + nuevoServicio.getDescripcion()); if (validacionGeneralDatosAuto.isValido()) { nuevosAutos.add(autoCargado); nuevosServicio.add(nuevoServicio); } else { addDetail( "los datos del nuevo servicio tienen los siguientes errores y no se dara de alta:"); addDetail(validacionDatosAuto.concatenaErrores()); } } List<AlertaServicio> nuevasAlertas = buscarAlertas(row, nombresServicios, rangoAlerta); for (AlertaServicio x : nuevasAlertas) { x.setMarcaAuto(autoCargado.getMarca()); x.setPlacasAuto(autoCargado.getPlacas()); x.setTipoAuto(autoCargado.getTipo()); x.setNombreCliente(servicio.getCliente().getNombre()); addDetail("Se encontro un servicio proximo para el auto con numero de serie: " + autoCargado.getNumeroSerie()); addDetail("Descripcin de la nueva alerta:\n" + x.getDescripcionServicio()); alertas.add(x); } AlertaVerificacion nuevaAlerta = buscarAlertasVerificacion(autoCargado.getPlacas()); if (nuevaAlerta != null) { addDetail("Se encontro un auto que pudiera necesitar verificacin: " + autoCargado.getNumeroSerie()); addDetail( "placas: " + nuevaAlerta.getPlacas() + " periodo: " + nuevaAlerta.getPeriodo()); alertasVerificacion.add(nuevaAlerta); } } } } if (nuevosServicio.size() > 0) { if (nuevosServicio.size() == 1) { addDetail("Se tiene listo para crear un nuevo servicio"); } else { addDetail("Se tienen listos para crear " + nuevosServicio.size() + " servicios nuevos"); } } else { addDetail("No se encontro ningun nuevo servicio"); } int cantidadAlertas = alertas.size() + alertasVerificacion.size(); if (cantidadAlertas > 0) { if (cantidadAlertas == 1) { addDetail("Se tiene lista para enviar una nueva alerta"); } else { addDetail("Se tienen listas para enviar " + cantidadAlertas + " alertas"); } } else { addDetail("No se encontro ninguna alerta"); } if (nuevosServicio.size() > 0 || cantidadAlertas > 0) { validacionGeneralProgramacion.setValido(true); } else { validacionGeneralProgramacion.setValido(false); } } catch (IOException | InvalidFormatException | IllegalArgumentException ex) { ProgramacionServicioWizardImp.LOGGER.error("exploto!!!", ex); addDetail("ocurrio un error inesperado al leer el archivo." + ex.getMessage()); } finally { if (pkg != null) { try { pkg.close(); } catch (IOException ex) { //ProgramacionServicioWizardImp.LOGGER.error("exploto!!!", ex); } } } }
From source file:org.ohdsi.jCdmBuilder.EtlReport.java
License:Apache License
private void addRow(XSSFSheet sheet, Object... values) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); for (Object value : values) { Cell cell = row.createCell(row.getPhysicalNumberOfCells()); if (value instanceof Integer && value instanceof Long && value instanceof Double) cell.setCellValue(Double.valueOf(value.toString())); else//from w ww .j a v a2s.com cell.setCellValue(value.toString()); } }
From source file:org.ohdsi.whiteRabbit.scan.SourceDataScan.java
License:Apache License
private void addRow(Sheet sheet, Object... values) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); for (Object value : values) { Cell cell = row.createCell(row.getPhysicalNumberOfCells()); if (value instanceof Integer || value instanceof Long || value instanceof Double) cell.setCellValue(Double.parseDouble(value.toString())); else/*w w w . ja va 2 s . c o m*/ cell.setCellValue(value.toString()); } }
From source file:org.openconcerto.erp.importer.DataImporter.java
License:Open Source License
public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException { final InputStream inputStream = new FileInputStream(xlsFile); final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream)); final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); final HSSFSheet sheet = workBook.getSheetAt(sheetNumber); Iterator<Row> rowsIterator = sheet.rowIterator(); int columnCount = 0; int rowCount = 0; while (rowsIterator.hasNext()) { Row row = rowsIterator.next(); int i = row.getPhysicalNumberOfCells(); if (i > columnCount) { columnCount = i;/*from w ww . j a v a2 s. co m*/ } rowCount++; } // Extract data rowsIterator = sheet.rowIterator(); int start = 0; if (skipFirstLine) { start = 1; rowsIterator.next(); } final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start); FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator(); while (rowsIterator.hasNext()) { final Row row = rowsIterator.next(); final List<Object> rowData = new ArrayList<Object>(); for (int i = 0; i < columnCount; i++) { final Cell cell = row.getCell(i); if (cell == null) { rowData.add(""); } else { CellValue cellValue = evaluator.evaluate(cell); if (cellValue == null) { rowData.add(""); } else { switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: rowData.add(Boolean.valueOf(cellValue.getBooleanValue())); break; case Cell.CELL_TYPE_NUMERIC: rowData.add(Double.valueOf(cellValue.getNumberValue())); break; case Cell.CELL_TYPE_STRING: rowData.add(cellValue.getStringValue()); break; case Cell.CELL_TYPE_FORMULA: rowData.add(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: rowData.add(""); break; default: rowData.add(cellValue.getStringValue()); break; } } } } rows.add(rowData); } inputStream.close(); return new ArrayTableModel(rows); }
From source file:org.openelis.bean.DataViewBean.java
License:Open Source License
private HSSFWorkbook getWorkbook(List<DataViewResultFetchVO> resultList, List<DataViewAuxDataFetchVO> auxDataList, List<DataViewResultFetchVO> noResAuxList, HashMap<Integer, HashMap<String, String>> analyteResultMap, HashMap<Integer, HashMap<String, String>> auxFieldValueMap, ArrayList<String> allCols, boolean runForWeb, boolean showReportableColumnsOnly, boolean addSampleCells, boolean addOrgCells, boolean addItemCells, boolean addAnalysisCells, boolean addEnvCells, boolean addSDWISCells, boolean addClinicalCells, DataViewVO data) throws Exception { boolean excludeOverride, excludeResults, excludeAuxData, sampleOverriden, anaOverriden, addResultRow, addAuxDataRow, addNoResAuxRow; int rowIndex, resIndex, auxIndex, noResAuxIndex, numResults, numAuxVals, numNoResAuxVals, i, lastColumn; Integer resAccNum, auxAccNum, sampleId, resSamId, auxSamId, itemId, analysisId, prevSamId, prevItemId, prevAnalysisId, rowGroup, prevRowGroup, sortOrder, currSortOrder, prevSortOrder, currColumn, anaIndex;/*from ww w .ja v a 2s .c o m*/ String resultVal, auxDataVal, domain, qaeNames, compByNames, relByNames, userName, anaName; StringBuffer buf; DataViewResultFetchVO res, noResAux; DataViewAuxDataFetchVO aux; HSSFWorkbook wb; HSSFSheet sheet; Row headerRow, resRow, auxRow, noResAuxRow, currRow, prevRow; Cell cell; CellStyle headerStyle; Datetime collDateTime, collDate, collTime; Date dc; SampleDO sam; SampleProjectViewDO proj; SampleOrganizationViewDO org; SampleEnvironmentalDO env; SampleSDWISViewDO sdwis; SampleClinicalViewDO clinical; PatientDO patientDO; ProviderDO providerDO; SampleItemViewDO item; AnalysisViewDO ana; AnalysisQaEventViewDO aqe; AnalysisUserViewDO anaUser; ReportStatus status; HashMap<Integer, PWSDO> pwsMap; HashMap<Integer, PatientDO> patientMap; HashMap<Integer, ProviderDO> providerMap; HashMap<Integer, ArrayList<ResultViewDO>> groupResMap; HashMap<String, Integer> colIndexAnaMap; ArrayList<Integer> sampleIds; ArrayList<SampleProjectViewDO> projList; ArrayList<AnalysisQaEventViewDO> aqeList; ArrayList<AnalysisUserViewDO> anaUserList; ArrayList<ResultViewDO> rowGrpResList; excludeOverride = "Y".equals(data.getExcludeResultOverride()); excludeResults = "Y".equals(data.getExcludeResults()); excludeAuxData = "Y".equals(data.getExcludeAuxData()); wb = new HSSFWorkbook(); sheet = wb.createSheet(); headerRow = sheet.createRow(0); headerStyle = createStyle(wb); // // add cells for the header and set their style // for (i = 0; i < allCols.size(); i++) { cell = headerRow.createCell(i); cell.setCellValue(allCols.get(i)); cell.setCellStyle(headerStyle); } rowIndex = 1; resIndex = 0; auxIndex = 0; noResAuxIndex = 0; sampleId = itemId = analysisId = null; domain = null; qaeNames = null; compByNames = null; relByNames = null; resultVal = null; auxDataVal = null; prevSamId = null; prevItemId = null; prevAnalysisId = null; collDateTime = null; sam = null; proj = null; org = null; env = null; sdwis = null; clinical = null; item = null; pwsMap = null; patientMap = new HashMap<Integer, PatientDO>(); providerMap = new HashMap<Integer, ProviderDO>(); addResultRow = false; addAuxDataRow = false; addNoResAuxRow = false; res = null; aux = null; noResAux = null; ana = null; aqeList = null; anaUserList = null; sampleOverriden = anaOverriden = false; groupResMap = null; rowGroup = prevRowGroup = null; rowGrpResList = null; colIndexAnaMap = new HashMap<String, Integer>(); lastColumn = 0; numResults = resultList == null ? 0 : resultList.size(); numAuxVals = auxDataList == null ? 0 : auxDataList.size(); numNoResAuxVals = noResAuxList == null ? 0 : noResAuxList.size(); currRow = prevRow = null; status = new ReportStatus(); status.setMessage(Messages.get().report_genDataView()); /* * the list of results and that of aux data are iterated through until * there are no more elements left in each of them to read from */ while (resIndex < numResults || auxIndex < numAuxVals || noResAuxIndex < numNoResAuxVals) { status.setPercentComplete( 100 * (resIndex + auxIndex + noResAuxIndex) / (numResults + numAuxVals + numNoResAuxVals)); session.setAttribute("DataViewReportStatus", status); if (excludeResults && excludeAuxData) { if (noResAuxIndex < numNoResAuxVals) { noResAux = noResAuxList.get(noResAuxIndex++); sampleId = noResAux.getSampleId(); domain = noResAux.getSampleDomain(); itemId = noResAux.getSampleItemId(); analysisId = noResAux.getAnalysisId(); addNoResAuxRow = true; } } else { if (resIndex < numResults && auxIndex < numAuxVals) { res = resultList.get(resIndex); aux = auxDataList.get(auxIndex); resAccNum = res.getSampleAccessionNumber(); auxAccNum = aux.getSampleAccessionNumber(); resSamId = res.getSampleId(); auxSamId = aux.getSampleId(); /* * If this result's accession number is less than or equal * to this aux data's then add a row for this result, * otherwise add a row for the aux data. This makes sure * that the results for a sample are shown before the aux * data. Every time a row for a result is added the index * keeping track of the next item in that list is * incremented and the same is done for the corresponding * index for aux data if a row for it is added. We compare * accession numbers instead of sample ids because the * former is the field shown in the sheet and not the * latter. */ if (resAccNum <= auxAccNum) { addResultRow = true; addAuxDataRow = false; resIndex++; sampleId = resSamId; domain = res.getSampleDomain(); itemId = res.getSampleItemId(); analysisId = res.getAnalysisId(); } else { addAuxDataRow = true; addResultRow = false; auxIndex++; sampleId = auxSamId; domain = aux.getSampleDomain(); } } else if (resIndex < numResults) { addResultRow = true; addAuxDataRow = false; // // no more aux data left to add to the sheet // res = resultList.get(resIndex); resIndex++; sampleId = res.getSampleId(); domain = res.getSampleDomain(); itemId = res.getSampleItemId(); analysisId = res.getAnalysisId(); } else if (auxIndex < numAuxVals) { addAuxDataRow = true; addResultRow = false; // // no more results left to add to the sheet // aux = auxDataList.get(auxIndex); auxIndex++; sampleId = aux.getSampleId(); domain = aux.getSampleDomain(); } } /* * skip showing any data for this sample if ths user asked to * exclude samples/analyses with results overriden and this sample * has such a qa event */ if (!sampleId.equals(prevSamId)) { try { sampleQaEvent.fetchResultOverrideBySampleId(sampleId); sampleOverriden = true; if (excludeOverride) { prevSamId = sampleId; continue; } } catch (NotFoundException e) { sampleOverriden = false; } sam = null; proj = null; org = null; env = null; sdwis = null; clinical = null; collDateTime = null; } else if (sampleOverriden && excludeOverride) { continue; } if (addResultRow) { /* * skip showing any data for this analysis if ths user asked to * exclude samples/analyses with results overriden and this * analysis has such a qa event */ if (!analysisId.equals(prevAnalysisId)) { anaOverriden = false; aqeList = null; try { aqeList = analysisQaEvent.fetchByAnalysisId(analysisId); for (i = 0; i < aqeList.size(); i++) { aqe = aqeList.get(i); if (Constants.dictionary().QAEVENT_OVERRIDE.equals(aqe.getTypeId())) { anaOverriden = true; if (excludeOverride) { addResultRow = false; prevAnalysisId = analysisId; } break; } } } catch (NotFoundException e) { anaOverriden = false; } } else if (anaOverriden && excludeOverride) { addResultRow = false; } } resRow = null; if (addResultRow) { /* * check to see if the value of this result was selected by the * user to be shown in the sheet and if it was add a row for it * to the sheet otherwise don't */ resultVal = getResultValue(analyteResultMap, res); if (resultVal != null) currRow = resRow = sheet.createRow(rowIndex++); else addResultRow = false; } auxRow = null; if (addAuxDataRow) { /* * check to see if the value of this aux data was selected by * the user to be shown in the sheet and if it was add a row for * it to the sheet otherwise don't */ auxDataVal = getAuxDataValue(auxFieldValueMap, aux); if (auxDataVal != null) currRow = auxRow = sheet.createRow(rowIndex++); else addAuxDataRow = false; } noResAuxRow = null; if (addNoResAuxRow) currRow = noResAuxRow = sheet.createRow(rowIndex++); if (addNoResAuxRow && !analysisId.equals(prevAnalysisId)) aqeList = null; if (!addResultRow && !addAuxDataRow && !addNoResAuxRow) continue; /* * The following code adds the cells to be shown under the headers * added previously to the sheet based on the fields selected by the * user. Cells are added even if there's no data to be shown for * given fields e.g. "Project Name" because all rows need to contain * the same number of cells. Also depending upon whether a row was * added for a result and/or an aux data, we set the values of some * cells to empty in a row because some fields don't make sense for * that row, e.g. the fields from sample item and analysis for aux * data. */ if (addSampleCells) { // // add cells for the selected fields belonging to samples // if (sam == null) sam = sample.fetchById(sampleId); if ("Y".equals(data.getProjectName()) && proj == null) { try { /* * we fetch the sample project here and not in the * method that adds the cells for the sample because the * data for the project needs to be fetched only once * for a sample and that method is called for each * analyte under a sample */ projList = sampleProject.fetchPermanentBySampleId(sampleId); proj = projList.get(0); } catch (NotFoundException e) { // ignore } } /* * since collection date and time are two separate fields in the * database, we have to put them together using an instance of * Datetime, thus we do it only once per sample to avoid * creating unnecessary objects for each row for that sample */ if ("Y".equals(data.getCollectionDate()) && collDateTime == null) { collDate = sam.getCollectionDate(); collTime = sam.getCollectionTime(); if (collDate != null) { dc = collDate.getDate(); if (collTime == null) { dc.setHours(0); dc.setMinutes(0); } else { dc.setHours(collTime.getDate().getHours()); dc.setMinutes(collTime.getDate().getMinutes()); } collDateTime = Datetime.getInstance(Datetime.YEAR, Datetime.MINUTE, dc); } } if (addResultRow) addSampleCells(resRow, resRow.getPhysicalNumberOfCells(), data, sam, collDateTime, proj); if (addAuxDataRow) addSampleCells(auxRow, auxRow.getPhysicalNumberOfCells(), data, sam, collDateTime, proj); if (addNoResAuxRow) addSampleCells(noResAuxRow, noResAuxRow.getPhysicalNumberOfCells(), data, sam, collDateTime, proj); } if (addOrgCells) { /* * add cells for the selected fields */ if (org == null) { try { org = sampleOrganization.fetchReportToBySampleId(sampleId); } catch (NotFoundException e) { // ignore } } if (addResultRow) addOrganizationCells(resRow, resRow.getPhysicalNumberOfCells(), data, org); if (addAuxDataRow) addOrganizationCells(auxRow, auxRow.getPhysicalNumberOfCells(), data, org); if (addNoResAuxRow) addOrganizationCells(noResAuxRow, noResAuxRow.getPhysicalNumberOfCells(), data, org); } if (addItemCells) { // // add cells for the selected fields belonging to sample item // if (addResultRow || addNoResAuxRow) { if (!itemId.equals(prevItemId)) { item = sampleItem.fetchById(itemId); prevItemId = itemId; } if (addResultRow) addSampleItemCells(resRow, resRow.getPhysicalNumberOfCells(), data, item); if (addNoResAuxRow) addSampleItemCells(noResAuxRow, noResAuxRow.getPhysicalNumberOfCells(), data, item); } if (addAuxDataRow) addSampleItemCells(auxRow, auxRow.getPhysicalNumberOfCells(), data, null); } if (addAnalysisCells) { /* * add cells for the selected fields belonging to sample * organization */ if (addResultRow || addNoResAuxRow) { if (!analysisId.equals(prevAnalysisId)) { groupResMap = new HashMap<Integer, ArrayList<ResultViewDO>>(); ana = analysis.fetchById(analysisId); anaUserList = null; qaeNames = null; compByNames = null; relByNames = null; if ("Y".equals(data.getAnalysisQaName())) { /* * if this analysis has any qa events linked to it, * fetch them and create a string by concatinating * their names together */ if (aqeList == null) { try { aqeList = analysisQaEvent.fetchByAnalysisId(analysisId); } catch (NotFoundException ignE) { qaeNames = null; } } if (aqeList != null) { buf = new StringBuffer(); for (i = 0; i < aqeList.size(); i++) { aqe = aqeList.get(i); /* * if the file is being generated for an * external client then we show the * reporting text and not name of the qa * event and we show it only if the qa event * is not internal */ if (runForWeb) { if (!DataBaseUtil.isSame(Constants.dictionary().QAEVENT_INTERNAL, aqe.getTypeId())) { if (buf.length() > 0) buf.append(" "); buf.append(aqe.getQaEventReportingText()); } } else { if (buf.length() > 0) buf.append(", "); buf.append(aqe.getQaEventName()); } } qaeNames = buf.toString(); } } if ("Y".equals(data.getAnalysisCompletedBy()) && anaUserList == null) { try { anaUserList = analysisUser.fetchByAnalysisId(analysisId); buf = new StringBuffer(); for (i = 0; i < anaUserList.size(); i++) { anaUser = anaUserList.get(i); if (!DataBaseUtil.isSame(Constants.dictionary().AN_USER_AC_COMPLETED, anaUser.getActionId())) continue; if (buf.length() > 0) buf.append(", "); userName = anaUser.getSystemUser(); /* * the user's login name could be null in * this DO if there was a problem with * fetching the data from security */ if (userName != null) buf.append(userName); } compByNames = buf.toString(); } catch (NotFoundException ignE) { // ignore } } if ("Y".equals(data.getAnalysisReleasedBy())) { if (anaUserList == null) { try { anaUserList = analysisUser.fetchByAnalysisId(analysisId); } catch (NotFoundException ignE) { // ignore } } if (anaUserList != null && relByNames == null) { for (i = 0; i < anaUserList.size(); i++) { anaUser = anaUserList.get(i); if (DataBaseUtil.isSame(Constants.dictionary().AN_USER_AC_RELEASED, anaUser.getActionId())) { relByNames = anaUser.getSystemUser(); break; } } } } } if (addResultRow) addAnalysisCells(resRow, resRow.getPhysicalNumberOfCells(), data, runForWeb, ana, qaeNames, compByNames, relByNames); if (addNoResAuxRow) addAnalysisCells(noResAuxRow, noResAuxRow.getPhysicalNumberOfCells(), data, runForWeb, ana, qaeNames, compByNames, relByNames); } if (addAuxDataRow) addAnalysisCells(auxRow, auxRow.getPhysicalNumberOfCells(), data, runForWeb, null, null, null, null); } /* * we need to make sure that a given sample is of a given domain * before fetching the data for that domain, but we need to add * cells (filled or not) for the fields from that domain in the file * for a given row regardless, if the user selected them to be shown */ if (addEnvCells) { if ("E".equals(domain) && env == null) env = sampleEnvironmental.fetchBySampleId(sampleId); if (addResultRow) addEnvironmentalCells(resRow, resRow.getPhysicalNumberOfCells(), data, env); if (addAuxDataRow) addEnvironmentalCells(auxRow, auxRow.getPhysicalNumberOfCells(), data, env); if (addNoResAuxRow) addEnvironmentalCells(noResAuxRow, noResAuxRow.getPhysicalNumberOfCells(), data, env); } if (addSDWISCells) { if ("S".equals(domain) && sdwis == null) { sdwis = sampleSDWIS.fetchBySampleId(sampleId); if ("Y".equals(data.getSampleSDWISPwsId()) && pwsMap == null) pwsMap = new HashMap<Integer, PWSDO>(); } if (addResultRow) addSDWISCells(resRow, resRow.getPhysicalNumberOfCells(), data, sdwis, pwsMap); if (addAuxDataRow) addSDWISCells(auxRow, auxRow.getPhysicalNumberOfCells(), data, sdwis, pwsMap); if (addNoResAuxRow) addSDWISCells(noResAuxRow, noResAuxRow.getPhysicalNumberOfCells(), data, sdwis, pwsMap); } if (addClinicalCells) { if ("C".equals(domain) && clinical == null) { sampleIds = new ArrayList<Integer>(); sampleIds.add(sampleId); clinical = sampleClinical.fetchBySampleIds(sampleIds).get(0); if (clinical.getPatientId() != null) { patientDO = patientMap.get(clinical.getPatientId()); if (patientDO == null) { patientDO = patient.fetchById(clinical.getPatientId()); patientMap.put(clinical.getPatientId(), patientDO); } clinical.setPatient(patientDO); } if (clinical.getProviderId() != null) { providerDO = providerMap.get(clinical.getProviderId()); if (providerDO == null) { providerDO = provider.fetchById(clinical.getProviderId()); providerMap.put(clinical.getProviderId(), providerDO); } clinical.setProvider(providerDO); } } if (addResultRow) addClinicalCells(resRow, resRow.getPhysicalNumberOfCells(), data, clinical); if (addAuxDataRow) addClinicalCells(auxRow, auxRow.getPhysicalNumberOfCells(), data, clinical); if (addNoResAuxRow) addClinicalCells(noResAuxRow, noResAuxRow.getPhysicalNumberOfCells(), data, clinical); } if (addResultRow) { // // set the analyte's name and the result's value // cell = resRow.createCell(resRow.getPhysicalNumberOfCells()); cell.setCellValue(res.getAnalyteName()); cell = resRow.createCell(resRow.getPhysicalNumberOfCells()); /* * results for an analysis are not shown if it or the sample * that it belongs to has a qa event of type "result override" */ if (!anaOverriden && !sampleOverriden) cell.setCellValue(resultVal); sortOrder = (Integer) res.getResultSortOrder(); rowGroup = (Integer) res.getResultTestAnalyteRowGroup(); if (!analysisId.equals(prevAnalysisId)) { groupResMap = new HashMap<Integer, ArrayList<ResultViewDO>>(); rowGrpResList = null; } else if (!rowGroup.equals(prevRowGroup)) { rowGrpResList = groupResMap.get(rowGroup); } // // fetch the column analytes if there are any // if (rowGrpResList == null) { try { /* * this is the list of all the results belonging to the * same row group as the test analyte of this result and * for which is_column = "Y" */ rowGrpResList = result.fetchForDataViewByAnalysisIdAndRowGroup(analysisId, rowGroup); groupResMap.put(rowGroup, rowGrpResList); } catch (NotFoundException e) { // ignore } } /* * if there are column analytes with values then the names of * the analytes are added to the header such that if an analyte * B is found first for any reason then it's added to the header * before another analyte A even if A's column appears to the * left of B's in this test or any other */ if (rowGrpResList != null) { if (lastColumn == 0) lastColumn = resRow.getPhysicalNumberOfCells(); currColumn = resRow.getPhysicalNumberOfCells(); prevSortOrder = sortOrder; for (ResultViewDO rvdo : rowGrpResList) { currSortOrder = rvdo.getSortOrder(); if (showReportableColumnsOnly && "N".equals(rvdo.getIsReportable())) { prevSortOrder = currSortOrder; continue; } /* * we only show those analytes' values in this row in * the sheet that belong to the row in the test starting * with the analyte selected by the user and none before * it */ if (currSortOrder < sortOrder) { prevSortOrder = currSortOrder; continue; } /* * The first check is done to know when the row starting * with the selected analyte has ended (the sort order * of the next analyte is 2 more than the previous * one's, i.e. the next one is a column analyte in the * next row). The second check is done to know when the * row starting with the selected analyte begins, i.e. * the first column analyte's sort order is one more * than that of the selected analyte. */ if (currSortOrder > prevSortOrder + 1 && currSortOrder > sortOrder + 1) break; anaName = rvdo.getAnalyte(); anaIndex = colIndexAnaMap.get(anaName); if (anaIndex == null) { /* * If an analyte's name is not found in the map then * we create a new column in the header row and set * its value as the name. We also start adding * values under that column */ anaIndex = lastColumn++; colIndexAnaMap.put(anaName, anaIndex); cell = headerRow.createCell(anaIndex); cell.setCellValue(anaName); cell.setCellStyle(headerStyle); resultVal = getValue(rvdo.getValue(), rvdo.getTypeId()); cell = resRow.createCell(anaIndex); } else if (anaIndex == currColumn) { /* * we set the value in this cell if this result's * analyte is shown in this column */ resultVal = getValue(rvdo.getValue(), rvdo.getTypeId()); cell = resRow.createCell(currColumn++); } else { /* * if this result's analyte is not shown in this * column then we set the value in the appropriate * column */ resultVal = getValue(rvdo.getValue(), rvdo.getTypeId()); cell = resRow.createCell(anaIndex); } /* * results for an analysis are not shown if it or the * sample that it belongs to has a qa event of type * "result override" */ if (!anaOverriden && !sampleOverriden) cell.setCellValue(resultVal); prevSortOrder = currSortOrder; } } } if (addAuxDataRow) { // // set the analyte's name and the aux data's value // cell = auxRow.createCell(auxRow.getPhysicalNumberOfCells()); cell.setCellValue(aux.getAnalyteName()); cell = auxRow.createCell(auxRow.getPhysicalNumberOfCells()); cell.setCellValue(auxDataVal); } prevAnalysisId = analysisId; prevSamId = sampleId; prevRowGroup = rowGroup; /* * An empty row can't be created and then added it to the sheet, it * has to be obtained from the sheet. Thus it has to be removed if * we don't want to show it. We do so if two consecutive rows have * the same data in all cells. It can happen if, for example, a user * chose to see sample items but all the ones under a sample have * the same container and sample type and those were the only fields * chosen to be shown. */ if (isSameDataInRows(currRow, prevRow)) { sheet.removeRow(currRow); rowIndex--; } else { prevRow = currRow; } } // // make each column wide enough to show the longest string in it // for (i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) sheet.autoSizeColumn(i); return wb; }
From source file:org.openelis.bean.DataViewBean.java
License:Open Source License
private boolean isSameDataInRows(Row currRow, Row prevRow) { int prevType, currType; Cell prevCell, currCell;/*from ww w . ja v a 2s . c o m*/ if (currRow == null || prevRow == null) return false; for (int i = 0; i < prevRow.getPhysicalNumberOfCells(); i++) { prevCell = prevRow.getCell(i); currCell = currRow.getCell(i); if (prevCell == null) { if (currCell == null) continue; else return false; } else if (currCell == null) { return false; } prevType = prevCell.getCellType(); currType = currCell.getCellType(); if (prevType != currType) return false; switch (prevType) { case Cell.CELL_TYPE_STRING: if (!DataBaseUtil.isSame(prevCell.getStringCellValue(), currCell.getStringCellValue())) return false; break; case Cell.CELL_TYPE_NUMERIC: if (!DataBaseUtil.isSame(prevCell.getNumericCellValue(), currCell.getNumericCellValue())) return false; break; case Cell.CELL_TYPE_BOOLEAN: if (!DataBaseUtil.isSame(prevCell.getBooleanCellValue(), currCell.getBooleanCellValue())) return false; break; } } return true; }
From source file:org.openelis.bean.DataViewReportBean.java
License:Open Source License
/** * Creates and returns a workbook that gets converted to an Excel file; each * row in the workbook shows fields from some part of a sample and analytes * and values//w w w . java2 s. c o m * * @param results * the list of VOs containing result info to be shown * @param auxiliary * the list of VOs containing aux data info to be shown * @param noResAux * the list of VOs containing info to be shown when both results and * aux data are excluded * @param testAnaResMap * the map containing result analytes and values selected by the * user; if an analyte or value is not in the map, the result is not * shown * @param auxFieldValMap * the map containing aux data analytes and values selected by the * user; if an analyte or value is not in the map, the aux data is * not shown * @param moduleName * the name of a security module for the logged in user; the module's * clause is used to restrict the fetched data to specific records * e.g. organizations * @param showReportableColumnsOnly * if true, only reportable column analytes are shown * @param headers * the list of labels for the column headers * @param data * the VO containing the user's choices for the data shown e.g. the * meta keys for selected columns and "include" and "exclude" flags * @param smMap * the map that provides the data for the columns belonging to * various parts of a sample e.g. domain, organization, project etc. * @param status * the percent completion in this ReportStatus is updated every time * a new row is added to the workbook */ private XSSFWorkbook getWorkbook(List<DataViewResultVO> results, List<DataViewResultVO> auxiliary, List<DataViewResultVO> noResAux, HashMap<Integer, HashSet<String>> testAnaResMap, HashMap<Integer, HashSet<String>> auxFieldValMap, String moduleName, boolean showReportableColumnsOnly, ArrayList<String> headers, DataView1VO data, HashMap<Integer, SampleManager1> smMap, ReportStatus status) throws Exception { boolean excludeOverride, excludeRes, excludeAux, samOverridden, anaOverridden, addRow; int i, j, resIndex, auxIndex, noResAuxIndex, rowIndex, numRes, numAux, numNoResAux, lastCol, currCol; Integer samId, prevSamId, resAccNum, auxAccNum, itemId, anaId, prevAnaId, anaIndex; String value; SampleManager1 sm; XSSFWorkbook wb; XSSFSheet sheet; DataViewResultVO res; ResultViewDO rowRes, colRes; Row currRow, prevRow; RowData rd; Cell cell; ArrayList<Integer> maxChars; ArrayList<ResultViewDO> smResults; HashMap<String, Integer> colAnaMap; HashMap<Integer, HashSet<String>> anaValMap; numRes = results == null ? 0 : results.size(); numAux = auxiliary == null ? 0 : auxiliary.size(); numNoResAux = noResAux == null ? 0 : noResAux.size(); excludeOverride = "Y".equals(data.getExcludeResultOverride()); excludeRes = "Y".equals(data.getExcludeResults()); excludeAux = "Y".equals(data.getExcludeAuxData()); resIndex = 0; auxIndex = 0; noResAuxIndex = 0; lastCol = 0; currCol = 0; rowIndex = 1; prevSamId = null; prevAnaId = null; anaIndex = null; samOverridden = false; anaOverridden = false; currRow = null; prevRow = null; sm = null; wb = new XSSFWorkbook(); sheet = wb.createSheet(); colAnaMap = new HashMap<String, Integer>(); maxChars = new ArrayList<Integer>(); rd = new RowData(); status.setMessage(Messages.get().report_genDataView()); status.setPercentComplete(0); session.setAttribute("DataViewReportStatus", status); /* * the lists of results and aux data are iterated through until there * are no more elements left in each of them to read from */ while (resIndex < numRes || auxIndex < numAux || noResAuxIndex < numNoResAux) { /* * the user wants to stop the report */ if (ReportStatus.Status.CANCEL.equals(status.getStatus())) { status.setMessage(Messages.get().report_stopped()); return null; } status.setPercentComplete( 100 * (resIndex + auxIndex + noResAuxIndex) / (numRes + numAux + numNoResAux)); res = null; anaValMap = null; value = null; if (excludeRes && excludeAux) { res = noResAux.get(noResAuxIndex++); } else { if (resIndex < numRes && auxIndex < numAux) { resAccNum = results.get(resIndex).getSampleAccessionNumber(); auxAccNum = auxiliary.get(auxIndex).getSampleAccessionNumber(); /* * if this result's accession number is less than or equal * to this aux data's, add a row for this result, otherwise * add a row for the aux data; this makes sure that the * results for a sample are shown before the aux data; * accession numbers are compared instead of sample ids * because the former is the field shown in the report and * not the latter */ if (resAccNum <= auxAccNum) { res = results.get(resIndex++); anaValMap = testAnaResMap; } else { res = auxiliary.get(auxIndex++); anaValMap = auxFieldValMap; } } else if (resIndex < numRes) { /* * no more aux data left to show */ res = results.get(resIndex++); anaValMap = testAnaResMap; } else if (auxIndex < numAux) { /* * no more results left to show */ res = auxiliary.get(auxIndex++); anaValMap = auxFieldValMap; } } samId = res.getSampleId(); itemId = res.getSampleItemId(); anaId = res.getAnalysisId(); if (!samId.equals(prevSamId)) { /* * don't show any data for this sample if it's overridden and * such samples are excluded; whether the sample is overridden * is checked even if such samples are not excluded because * overridden result values are not shown in the report */ sm = smMap.get(samId); samOverridden = false; if ((getSampleQAs(sm) != null)) { for (SampleQaEventViewDO sqa : getSampleQAs(sm)) { if (Constants.dictionary().QAEVENT_OVERRIDE.equals(sqa.getTypeId())) { samOverridden = true; if (excludeOverride) prevSamId = samId; break; } } } } if (samOverridden && excludeOverride) { prevSamId = samId; continue; } /* * don't show any data for this analysis if it's overridden and such * analyses are excluded; whether the analysis is overridden is * checked even if such analyses are not excluded because overridden * values are not shown in the report */ if (anaId != null) { if (!anaId.equals(prevAnaId)) { anaOverridden = false; if ((getAnalysisQAs(sm) != null)) { for (AnalysisQaEventViewDO aqa : getAnalysisQAs(sm)) { if (aqa.getAnalysisId().equals(anaId) && Constants.dictionary().QAEVENT_OVERRIDE.equals(aqa.getTypeId())) { anaOverridden = true; if (excludeOverride) break; } } } } if (anaOverridden && excludeOverride) { prevSamId = samId; prevAnaId = anaId; continue; } } if (anaValMap != null) { /* * show this result or aux data only if its value was selected * by the user */ value = getValue(anaValMap, res.getAnalyteId(), res.getValue(), res.getTypeId()); if (value == null) { prevSamId = samId; prevAnaId = anaId; continue; } } currRow = sheet.createRow(rowIndex++); /* * fill the passed row's cells for all columns except the ones for * analytes and values */ setBaseCells(sm, itemId, anaId, rd, data.getColumns(), moduleName != null, wb, currRow, maxChars); if (value != null) { /* * this row is for either a result or aux data; show the analyte */ cell = currRow.createCell(currRow.getPhysicalNumberOfCells()); setCellValue(cell, res.getAnalyteName(), null); setMaxChars(cell.getColumnIndex(), res.getAnalyteName(), maxChars, null); cell = currRow.createCell(currRow.getPhysicalNumberOfCells()); if (anaId != null && !excludeRes) { /* * this row is for a result; show the value only if the * analysis and sample are not overridden */ if (!anaOverridden && !samOverridden) setCellValue(cell, value, null); setMaxChars(cell.getColumnIndex(), cell.getStringCellValue(), maxChars, null); /* * if this analyte has column analytes, show them in the * header and their values in the columns; results for a * sample can be null if it has no results with values but * has aux data with values and aux data is not excluded */ smResults = getResults(sm); if (smResults != null) { for (i = 0; i < smResults.size(); i++) { rowRes = smResults.get(i); if (!res.getId().equals(rowRes.getId())) continue; j = i + 1; if (j < smResults.size() && "Y".equals(smResults.get(j).getIsColumn())) { /* * this analyte has column analytes; "lastCol" * is the right-most column in the workbook; if * an analyte doesn't have a column yet, that * column will be added after "lastCol"; * "currCol" keeps track of the current column */ if (lastCol == 0) lastCol = currRow.getPhysicalNumberOfCells(); currCol = currRow.getPhysicalNumberOfCells(); while (j < smResults.size()) { colRes = smResults.get(j++); if ("N".equals(colRes.getIsColumn())) break; if (showReportableColumnsOnly && "N".equals(colRes.getIsReportable())) continue; anaIndex = colAnaMap.get(colRes.getAnalyte()); /* * if this column analyte's name is not * found in the map, create a new column and * start adding values in it; set the value * in this cell if the analyte is shown in * this column; if the analyte is not shown * in this column, find the column in which * it is shown and set the value */ if (anaIndex == null) { anaIndex = lastCol++; colAnaMap.put(colRes.getAnalyte(), anaIndex); headers.add(colRes.getAnalyte()); setMaxChars(cell.getColumnIndex(), colRes.getAnalyte(), maxChars, null); cell = currRow.createCell(anaIndex); } else if (anaIndex == currCol) { cell = currRow.createCell(currCol++); } else { cell = currRow.createCell(anaIndex); } /* * set the value if the analysis and sample * are not overridden */ if (!anaOverridden && !samOverridden) setCellValue(cell, getValue(colRes.getValue(), colRes.getTypeId()), null); setMaxChars(cell.getColumnIndex(), cell.getStringCellValue(), maxChars, null); } } } } } else { /* * this row is for an aux data; show the value */ setCellValue(cell, value, null); setMaxChars(cell.getColumnIndex(), value, maxChars, null); } } prevAnaId = anaId; prevSamId = samId; /* * an empty row can't be created and then added to the sheet, it has * to be obtained from the sheet; thus it has to be removed if it * shouldn't be shown because it has the same data as the previous * row in all cells; this can happen if e.g. a user selects only * container and sample type but all sample items in a sample have * the same values for these fields */ if (isSameDataInRows(currRow, prevRow)) { sheet.removeRow(currRow); rowIndex--; } else { prevRow = currRow; } } /* * add the header row and set the header labels for all columns */ setHeaderCells(sheet, wb, headers, maxChars); /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 0; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); return wb; }
From source file:org.openelis.bean.DataViewReportBean.java
License:Open Source License
/** * Determines if each cell in "currRow" has the same data as the * corresponding cell in "prevRow"/* w ww . j ava2s .c om*/ * * @param currRow * the current row in the spreadsheet * @param prevRow * the previous row in the spreadsheet * @return true if the corresponding cells in both rows have the same data; * false otherwise */ private boolean isSameDataInRows(Row currRow, Row prevRow) { int prevType, currType; Cell prevCell, currCell; if (currRow == null || prevRow == null) return false; for (int i = 0; i < prevRow.getPhysicalNumberOfCells(); i++) { prevCell = prevRow.getCell(i); currCell = currRow.getCell(i); if (prevCell == null) { if (currCell == null) continue; else return false; } else if (currCell == null) { return false; } prevType = prevCell.getCellType(); currType = currCell.getCellType(); if (prevType != currType) return false; switch (prevType) { case Cell.CELL_TYPE_STRING: if (!DataBaseUtil.isSame(prevCell.getStringCellValue(), currCell.getStringCellValue())) return false; break; case Cell.CELL_TYPE_NUMERIC: if (!DataBaseUtil.isSame(prevCell.getNumericCellValue(), currCell.getNumericCellValue())) return false; break; } } return true; }