List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet removeRow
@Override public void removeRow(Row row)
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 . j a va2 s . 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.QcChartReport1Bean.java
License:Open Source License
private HSSFWorkbook fillWorkbook(HSSFWorkbook wb, ArrayList<Value> values, String qcName, String qcType, String qcFormat, Integer plotType, ReportStatus status) throws Exception { int rowIndex, sheetIndex, valueIndex; HSSFSheet sheet; Row row;//from w w w. j a v a 2 s .com String lastAnalyte, lastFormat; lastAnalyte = "___"; lastFormat = "___"; sheet = null; sheetIndex = 1; valueIndex = 0; try { baseFont = wb.createFont(); baseFont.setFontName("Arial"); baseFont.setFontHeightInPoints((short) 8); baseStyle = wb.createCellStyle(); baseStyle.setFont(baseFont); headerFont = wb.createFont(); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 8); headerStyle = wb.createCellStyle(); headerStyle.setAlignment(CellStyle.ALIGN_LEFT); headerStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex()); headerStyle.setFont(headerFont); maxChars = new ArrayList<Integer>(); worksheetColumnMap = new HashMap<String, HashMap<String, Integer>>(); worksheetHeaders = new ArrayList<String>(); worksheetHeaderNames = new ArrayList<String>(); if (qcColumns != null && !qcColumns.isEmpty()) rowIndex = 32; else rowIndex = 3; for (Value value : values) { valueIndex++; if ("N".equals(value.getIsPlot())) continue; if (!lastAnalyte.equals(value.getAnalyteName())) { if (!"___".equals(lastAnalyte)) { while (rowIndex < sheet.getLastRowNum()) { sheet.removeRow(sheet.getRow(rowIndex)); rowIndex++; } finishSheet(sheet, wb, qcName, qcType, lastAnalyte); } sheet = wb.getSheet("Sheet" + (sheetIndex++)); if (sheet == null) sheet = wb.createSheet(); lastAnalyte = value.getAnalyteName(); if (qcColumns != null && !qcColumns.isEmpty()) rowIndex = 32; else rowIndex = 3; lastFormat = "___"; if (Constants.dictionary().CHART_TYPE_FIXED.equals(plotType)) setStatisticCells(wb, sheet, value); } if (!lastFormat.equals(value.getWorksheetFormat())) { lastFormat = value.getWorksheetFormat(); if (qcColumns == null || qcColumns.isEmpty()) loadWorksheetFormat(lastFormat); } row = sheet.createRow(rowIndex++); setBaseCells(value, row); setResultCells(value, row, qcFormat, lastFormat); status.setPercentComplete(70 * (valueIndex / values.size()) + 20); session.setAttribute("qcChartReport", status); } finishSheet(sheet, wb, qcName, qcType, lastAnalyte); while (sheetIndex < wb.getNumberOfSheets()) wb.removeSheetAt(sheetIndex); } finally { baseFont = null; baseStyle = null; headerFont = null; headerStyle = null; maxChars = null; worksheetColumnMap = null; worksheetHeaders = null; worksheetHeaderNames = null; } return wb; }
From source file:org.openelis.bean.WorksheetExcelHelperBean.java
License:Open Source License
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED) public WorksheetManager1 exportToExcel(WorksheetManager1 manager) throws Exception { boolean isEditable; int r, i, a, o, aCount, aTotal; ArrayList<WorksheetAnalysisViewDO> waList; ArrayList<WorksheetResultViewDO> wrList; ArrayList<WorksheetQcResultViewDO> wqrList; String cellNameIndex, posNum, outFileName; File outFile;//from ww w .j a v a2 s.c o m FileInputStream in; FileOutputStream out; HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>> waMap; HashMap<Integer, ArrayList<WorksheetResultViewDO>> wrMap; HashMap<Integer, ArrayList<WorksheetQcResultViewDO>> wqrMap; HashMap<Integer, WorksheetAnalysisViewDO> waLinkMap; HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap; HashMap<String, String> tCellNames; Cell cell; CellRangeAddressList /*statusCells, */ reportableColumn; DVConstraint /*statusConstraint, */ reportableConstraint; HSSFDataValidation /*statusValidation, */ reportableValidation; HSSFSheet resultSheet, overrideSheet; HSSFWorkbook wb; Name cellName; Row row, oRow, tRow; DictionaryDO formatDO; ReportStatus status; SimpleDateFormat dateTimeFormat; WorksheetAnalysisDO waLinkDO; status = new ReportStatus(); status.setMessage("Exporting to Excel: Initializing"); status.setPercentComplete(0); session.setAttribute("ExportToExcelStatus", status); dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm"); outFileName = getWorksheetOutputFileName(manager.getWorksheet().getId(), manager.getWorksheet().getSystemUserId()); outFile = new File(outFileName); if (outFile.exists()) throw new Exception( "An Excel file for this worksheet already exists, please delete it before trying to export"); try { formatDO = dictionaryCache.getById(manager.getWorksheet().getFormatId()); } catch (NotFoundException nfE) { formatDO = new DictionaryDO(); formatDO.setEntry("DefaultTotal"); formatDO.setSystemName("wf_total"); } catch (Exception anyE) { throw new Exception("Error retrieving worksheet format: " + anyE.getMessage()); } try { in = new FileInputStream(getWorksheetTemplateFileName(formatDO)); wb = new HSSFWorkbook(in, true); createStyles(wb); } catch (FileNotFoundException fnfE) { try { wb = buildTemplate(formatDO); createStyles(wb); } catch (FileNotFoundException fnfE2) { throw new Exception("Error loading template file: " + fnfE2.getMessage()); } catch (IOException ioE) { throw new Exception("Error loading workbook from template file: " + ioE.getMessage()); } } loadStatuses(); // statusCells = new CellRangeAddressList(); tCellNames = loadNamesByCellReference(wb); resultSheet = wb.getSheet("Worksheet"); tRow = resultSheet.getRow(1); resultSheet.removeRow(tRow); overrideSheet = wb.getSheet("Overrides"); status.setPercentComplete(5); session.setAttribute("ExportToExcelStatus", status); r = 1; o = 1; i = 0; aCount = 0; apMap = new HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>>(); waMap = new HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>>(); waLinkMap = new HashMap<Integer, WorksheetAnalysisViewDO>(); wrMap = new HashMap<Integer, ArrayList<WorksheetResultViewDO>>(); wqrMap = new HashMap<Integer, ArrayList<WorksheetQcResultViewDO>>(); loadMaps(manager, waMap, waLinkMap, wrMap, wqrMap); aTotal = getAnalyses(manager).size(); if (getItems(manager) != null) { for (WorksheetItemDO wiDO : getItems(manager)) { a = 0; waList = waMap.get(wiDO.getId()); if (waList != null && waList.size() > 0) { for (WorksheetAnalysisViewDO waVDO : waList) { aCount++; status.setMessage("Exporting to Excel: Analysis " + aCount + " of " + aTotal); status.setPercentComplete((int) (((double) (aCount - 1) / aTotal) * 90) + 5); session.setAttribute("ExportToExcelStatus", status); waLinkDO = waLinkMap.get(waVDO.getWorksheetAnalysisId()); row = resultSheet.createRow(r); // position number posNum = wiDO.getPosition().toString(); cell = row.createCell(0); cell.setCellStyle(styles.get("row_no_edit")); if (a == 0) cell.setCellValue(posNum); // accession number cell = row.createCell(1); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getAccessionNumber()); cellNameIndex = i + "." + a; if (waVDO.getAnalysisId() != null) { isEditable = (waVDO.getFromOtherId() == null && !Constants.dictionary().ANALYSIS_INPREP.equals(waVDO.getStatusId()) && !Constants.dictionary().ANALYSIS_RELEASED.equals(waVDO.getStatusId()) && !Constants.dictionary().ANALYSIS_CANCELLED.equals(waVDO.getStatusId())); // description cell = row.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // qc link cell = row.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); if (waLinkDO != null) cell.setCellValue(waLinkDO.getAccessionNumber()); else cell.setCellValue(""); // test name cell = row.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getTestName()); // method name cell = row.createCell(5); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getMethodName()); // analysis status cell = row.createCell(6); // if (isEditable) // cell.setCellStyle(styles.get("row_edit")); // else cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(statusIdNameMap.get(waVDO.getStatusId())); cellName = wb.createName(); cellName.setNameName("analysis_status." + i + "." + a); cellName.setRefersToFormula("Worksheet!$" + CellReference.convertNumToColString(6) + "$" + (row.getRowNum() + 1)); // statusCells.addCellRangeAddress(r, 6, r, 6); wrList = wrMap.get(waVDO.getId()); if (wrList == null || wrList.size() == 0) { // analyte cell = row.createCell(7); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("NO ANALYTES DEFINED"); // reportable cell = row.createCell(8); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("N"); createEmptyCellsForFormat(row, tRow); r++; } else { r = createResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames, manager.getWorksheet(), waVDO, wrList, isEditable, apMap); } // // Add override row to override sheet // oRow = overrideSheet.createRow(o); // position number cell = oRow.createCell(0); cell.setCellStyle(styles.get("row_no_edit")); if (a == 0) cell.setCellValue(posNum); // accession number cell = oRow.createCell(1); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getAccessionNumber()); // description (override) cell = oRow.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // test name (overrride) cell = oRow.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getTestName()); // method name (override) cell = oRow.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getMethodName()); // users (override) cell = oRow.createCell(5); if (isEditable) cell.setCellStyle(styles.get("row_edit")); else cell.setCellStyle(styles.get("row_no_edit")); if (waVDO.getSystemUsers() != null) cell.setCellValue(waVDO.getSystemUsers()); cellName = wb.createName(); cellName.setNameName("analysis_users." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$" + (oRow.getRowNum() + 1)); // started (override) cell = oRow.createCell(6); if (isEditable) cell.setCellStyle(styles.get("datetime_edit")); else cell.setCellStyle(styles.get("datetime_no_edit")); if (waVDO.getStartedDate() != null) cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate())); cellName = wb.createName(); cellName.setNameName("analysis_started." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$" + (oRow.getRowNum() + 1)); // completed (override) cell = oRow.createCell(7); if (isEditable) cell.setCellStyle(styles.get("datetime_edit")); else cell.setCellStyle(styles.get("datetime_no_edit")); if (waVDO.getCompletedDate() != null) cell.setCellValue(dateTimeFormat.format(waVDO.getCompletedDate().getDate())); cellName = wb.createName(); cellName.setNameName("analysis_completed." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$" + (oRow.getRowNum() + 1)); o++; } else if (waVDO.getQcLotId() != null) { // description cell = row.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // qc link cell = row.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); if (waLinkDO != null) cell.setCellValue(waLinkDO.getAccessionNumber()); else cell.setCellValue(""); // test name cell = row.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // method name cell = row.createCell(5); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // analysis status cell = row.createCell(6); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); wqrList = wqrMap.get(waVDO.getId()); if (wqrList == null || wqrList.size() == 0) { // analyte cell = row.createCell(7); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("NO ANALYTES DEFINED"); // reportable cell = row.createCell(8); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue("N"); createEmptyCellsForFormat(row, tRow); r++; } else { cellNameIndex = i + "." + a; r = createQcResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames, manager.getWorksheet(), waVDO.getQcId(), wqrList, apMap); } // // Add override row to override sheet // oRow = overrideSheet.createRow(o); // position number cell = oRow.createCell(0); cell.setCellStyle(styles.get("row_no_edit")); if (a == 0) cell.setCellValue(posNum); // accession number cell = oRow.createCell(1); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getAccessionNumber()); // description (override) cell = oRow.createCell(2); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(waVDO.getDescription()); // test name (overrride) cell = oRow.createCell(3); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // method name (override) cell = oRow.createCell(4); cell.setCellStyle(styles.get("row_no_edit")); cell.setCellValue(""); // users (override) cell = oRow.createCell(5); cell.setCellStyle(styles.get("row_edit")); if (waVDO.getSystemUsers() != null) cell.setCellValue(waVDO.getSystemUsers()); cellName = wb.createName(); cellName.setNameName("analysis_users." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$" + (oRow.getRowNum() + 1)); // started (override) cell = oRow.createCell(6); cell.setCellStyle(styles.get("datetime_edit")); if (waVDO.getStartedDate() != null) cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate())); cellName = wb.createName(); cellName.setNameName("analysis_started." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$" + (oRow.getRowNum() + 1)); // completed (override) cell = oRow.createCell(7); cell.setCellStyle(styles.get("datetime_no_edit")); cellName = wb.createName(); cellName.setNameName("analysis_completed." + cellNameIndex); cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$" + (oRow.getRowNum() + 1)); o++; } a++; } } i++; } } status.setMessage("Exporting to Excel: Finalizing"); status.setPercentComplete(95); session.setAttribute("ExportToExcelStatus", status); // // Create validators // // statusConstraint = DVConstraint.createExplicitListConstraint(statuses); // statusValidation = new HSSFDataValidation(statusCells, statusConstraint); // statusValidation.setEmptyCellAllowed(true); // statusValidation.setSuppressDropDownArrow(false); // statusValidation.createPromptBox("Statuses", formatTooltip(statuses)); // statusValidation.setShowPromptBox(false); // resultSheet.addValidationData(statusValidation); reportableColumn = new CellRangeAddressList(1, resultSheet.getPhysicalNumberOfRows() - 1, 8, 8); reportableConstraint = DVConstraint.createExplicitListConstraint(new String[] { "Y", "N" }); reportableValidation = new HSSFDataValidation(reportableColumn, reportableConstraint); reportableValidation.setSuppressDropDownArrow(false); resultSheet.addValidationData(reportableValidation); // // Auto resize columns on result sheet and override sheet // resultSheet.autoSizeColumn(2, true); // Description resultSheet.autoSizeColumn(4, true); // Test resultSheet.autoSizeColumn(5, true); // Method resultSheet.autoSizeColumn(7, true); // Analyte overrideSheet.autoSizeColumn(2, true); // Description overrideSheet.autoSizeColumn(3, true); // Test overrideSheet.autoSizeColumn(4, true); // Method try { out = new FileOutputStream(outFileName); wb.write(out); out.close(); Runtime.getRuntime().exec("chmod go+rw " + outFileName); } catch (Exception anyE) { throw new Exception("Error writing Excel file: " + anyE.getMessage()); } status.setMessage("Exporting to Excel: Done"); status.setPercentComplete(100); session.setAttribute("ExportToExcelStatus", status); return manager; }
From source file:poi.hssf.usermodel.examples.HSSFReadWrite.java
License:Apache License
/** * Method main//from w ww . j av a2 s. c o m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (args.length == 2) { if (args[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); } } else if (args.length == 3 && args[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * Method main/* w ww. j a v a 2 s. c o m*/ * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } // wb.close(); } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); // wb.close(); } } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); // wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:ypcnv.views.impl.FileXLS.java
License:Open Source License
/** * Insert data from model into workbook object instance. * /*from www .ja v a2 s .co m*/ * @param headerRow * - row previous to row with real data, in fact this is row with * columns headers. */ private void insertDataToWorkbook(HSSFRow headerRow) { Contact2k3 currentContact; Iterator<Contact2k3> containerModelListIterator = dataContainer.getContacts().iterator(); HSSFSheet currentSheet = headerRow.getSheet(); HSSFRow currentRow; HSSFCell currentCell; int currentRowIdx = headerRow.getRowNum() + 1; int lastRowIdx = currentSheet.getLastRowNum(); int currentColumnIdx; // XXX - there is silent wipe of previous content of the sheet. for (int idx = currentRowIdx; idx <= lastRowIdx; idx++) { currentSheet.removeRow(currentSheet.getRow(idx)); } for (; containerModelListIterator.hasNext(); currentRowIdx++) { currentContact = containerModelListIterator.next(); currentSheet.createRow(currentRowIdx); currentRow = currentSheet.getRow(currentRowIdx); for (String dataFieldKeyName : dataColumnsSequenceMap.keySet()) { currentColumnIdx = dataColumnsSequenceMap.get(dataFieldKeyName); currentRow.createCell(currentColumnIdx); currentCell = currentRow.getCell(currentColumnIdx); currentCell.setCellType(Cell.CELL_TYPE_STRING); currentCell.setCellValue(currentContact.getFieldValuesMap().get(dataFieldKeyName)); } } }