List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
From source file:org.ivan.service.ExcelExporter.java
public <T extends Object, E extends Object> File createExcelFromMap(Map<T, List<E>> objects, String fileName) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font);//from ww w.java2 s .c om for (T mapKey : objects.keySet()) { HSSFSheet sheet = workbook.createSheet(mapKey.toString()); int cellNum = 0; int rowNum = 0; List<String> headers = getHeadersFromGetMethods(objects.get(mapKey).get(0)); Row row = sheet.createRow(rowNum++); for (int i = 0; i < headers.size(); i++) { String cup = headers.get(i); Cell cell = row.createCell(cellNum++); cell.setCellValue(cup); cell.setCellStyle(style); } for (E object : objects.get(mapKey)) { cellNum = 0; List<String> parameters = getValuesRecursive(object); row = sheet.createRow(rowNum++); for (String parameter : parameters) { Cell cell = row.createCell(cellNum++); cell.setCellValue(parameter); sheet.autoSizeColumn(cellNum); } } } File file = new File(fileName + ".xls"); try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); out.close(); } catch (FileNotFoundException e) { } catch (IOException e) { } return null; }
From source file:org.kurator.validation.actors.io.AnalysisSpreadsheetBuilder.java
License:Open Source License
private void autoSizeColumns(HSSFSheet sheet, int cols) { for (int i = 0; i < cols; i++) { sheet.autoSizeColumn(i); }//from w ww . j a va2 s . c om }
From source file:org.ofbiz.webtools.ExcelConversionFilter.java
License:Open Source License
void applyStylesToSheet(Map<String, ? extends Object> context, HSSFWorkbook workBook, HSSFSheet sheet, ServletRequest request) {// w ww . j av a 2 s.c o m List headerKeys = new ArrayList(); int noOfheads = 0; headerKeys = UtilMisc.toList("mainHeader1", "mainHeader2", "mainHeader3", "mainHeader4", "mainHeader5"); Map<String, Object> stylesMap = FastMap.newInstance(); stylesMap = (Map) context.get("stylesMap"); ArrayList allRowAndColData = (ArrayList) context.get("allRowAndColData"); Integer mainHeadingCell = 5; Integer mainHeadercellHeight = null; String mainHeaderFontName = null; Integer mainHeaderFontSize = null; Boolean mainHeaderBold = true; Integer columnHeaderCellHeight = null; Boolean columnHeaderBold = true; Boolean columnHeaderBgColor = null; String columnHeaderFontName = null; Boolean autoSizeCell = true; Integer columnHeaderFontSize = null; mainHeadercellHeight = (Integer) stylesMap.get("mainHeadercellHeight"); mainHeaderFontName = (String) stylesMap.get("mainHeaderFontName"); mainHeaderFontSize = (Integer) stylesMap.get("mainHeaderFontSize"); if (stylesMap.get("mainHeaderBold") != null) { mainHeaderBold = (Boolean) stylesMap.get("mainHeaderBold"); } if (stylesMap.get("columnHeaderBold") != null) { columnHeaderBold = (Boolean) stylesMap.get("columnHeaderBold"); } if (stylesMap.get("autoSizeCell") != null) { autoSizeCell = (Boolean) stylesMap.get("autoSizeCell"); } if (stylesMap.get("mainHeadingCell") != null) { mainHeadingCell = (Integer) stylesMap.get("mainHeadingCell"); } columnHeaderCellHeight = (Integer) stylesMap.get("columnHeaderCellHeight"); columnHeaderBgColor = (Boolean) stylesMap.get("columnHeaderBgColor"); columnHeaderFontName = (String) stylesMap.get("columnHeaderFontName"); columnHeaderFontSize = (Integer) stylesMap.get("columnHeaderFontSize"); ArrayList styles = new ArrayList(stylesMap.keySet()); for (int i = 0; i < styles.size(); i++) { ArrayList tempArrayList = new ArrayList<String>(); if (headerKeys.contains(styles.get(i))) { ArrayList<?> innerData = (ArrayList<?>) allRowAndColData.get(i); tempArrayList.add(stylesMap.get(styles.get(i))); for (int j = 0; j < innerData.size() - 1; j++) { tempArrayList.add(""); } allRowAndColData.add(i, tempArrayList); ++noOfheads; } } try { for (int i = 0; i < allRowAndColData.size(); i++) { HSSFCellStyle style = workBook.createCellStyle(); HSSFFont font = workBook.createFont(); ArrayList<?> ardata = (ArrayList<?>) allRowAndColData.get(i); HSSFRow row = sheet.createRow(i); for (int k = 0; k < ardata.size(); k++) { HSSFCell cell = row.createCell(k); if (k == mainHeadingCell && i <= noOfheads) { if (UtilValidate.isNotEmpty(mainHeadercellHeight)) { row.setHeight((short) mainHeadercellHeight.shortValue()); } else { row.setHeight((short) 400); } if (UtilValidate.isNotEmpty(mainHeaderFontName)) { font.setFontName(mainHeaderFontName); } if (UtilValidate.isNotEmpty(mainHeaderBold) && mainHeaderBold) { font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (UtilValidate.isNotEmpty(mainHeaderFontSize)) { font.setFontHeightInPoints((short) mainHeaderFontSize.shortValue()); } else { font.setFontHeightInPoints((short) 12); //default value } style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFont(font); cell.setCellValue((ardata.get(0).toString()).replaceAll("\"", "")); cell.setCellStyle(style); } else if (i == noOfheads + 1) { if (UtilValidate.isNotEmpty(columnHeaderCellHeight)) { row.setHeight((short) columnHeaderCellHeight.shortValue()); } else { row.setHeight((short) 300); } if (UtilValidate.isNotEmpty(columnHeaderBold) && columnHeaderBold) { font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (UtilValidate.isNotEmpty(columnHeaderFontSize)) { font.setFontHeightInPoints((short) columnHeaderFontSize.shortValue()); } else { font.setFontHeightInPoints((short) 9); } if (UtilValidate.isNotEmpty(columnHeaderFontName)) { font.setFontName(columnHeaderFontName); } if (UtilValidate.isNotEmpty(columnHeaderBgColor) && columnHeaderBgColor) { style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFont(font); cell.setCellValue((ardata.get(k).toString()).replaceAll("\"", "")); cell.setCellStyle(style); } else if (i > noOfheads) { cell.setCellValue((ardata.get(k).toString()).replaceAll("\"", "")); } if (UtilValidate.isNotEmpty(autoSizeCell) && autoSizeCell) { sheet.autoSizeColumn(k); } } } } catch (Exception e) { Debug.logInfo(e.getMessage(), module); request.setAttribute("_ERROR_MESSAGE_", e.getMessage()); } }
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 w w w . j a va2s.c om*/ 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.openscada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor) throws IOException { final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFDataFormat dateFormat = workbook.createDataFormat(); final HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000")); try {/*from ww w . ja v a 2 s. c o m*/ monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size()); try { monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook); monitor.worked(1); final HSSFSheet sheet = createSheet(events, workbook, columns); monitor.worked(1); monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents); for (int i = 0; i < events.size(); i++) { final HSSFRow row = sheet.createRow(i + 1); final Event e = events.get(i); for (int j = 0; j < columns.size(); j++) { final Field field = columns.get(j); final ExcelCell cell = new ExcelCell(row, j, dateCellStyle); field.render(e, cell); } monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } workbook.setRepeatingRowsAndColumns(0, -1, -1, 0, 1); monitor.setTaskName("Auto sizing"); for (int i = 0; i < columns.size(); i++) { monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader())); sheet.autoSizeColumn(i); monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } } finally { monitor.subTask(Messages.ExportImpl_Progress_CloseFile); if (workbook != null) { makeDocInfo(workbook); final FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); } monitor.worked(1); } } finally { monitor.done(); } return Status.OK_STATUS; }
From source file:org.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Creates an Excel document with a given column name list, and column data list. * The String objects in the column name list are used as Map keys to look up the corresponding * column header and data. The column data to be exported is a List of Map objects where * the first Map element contains column headers, and the rest has all the column data. * @param workBookName a String object as Excel file name * @param workSheetName a String object as the name of the Excel sheet * @param columnNameList a List of String objects as column names, they usually correspond to entity field names * @param data a List of Map objects to be exported where the first Map element contains column headers, * and the rest has all the column data. * @throws IOException if an error occurs *///ww w .ja va 2 s . co m public static void saveToExcel(final String workBookName, final String workSheetName, final List<String> columnNameList, final List<Map<String, Object>> data) throws IOException { if (StringUtils.isEmpty(workBookName)) { throw new IllegalArgumentException("Argument workBookName can't be empty"); } if (StringUtils.isEmpty(workSheetName)) { throw new IllegalArgumentException("Argument workSheetName can't be empty"); } if (columnNameList == null || columnNameList.isEmpty()) { throw new IllegalArgumentException("Argument columnNameList can't be empty"); } // the data list should have at least one element for the column headers if (data == null || data.isEmpty()) { throw new IllegalArgumentException("Argument data can't be empty"); } FileOutputStream fileOut = new FileOutputStream(new File(workBookName)); assert fileOut != null; HSSFWorkbook workBook = new HSSFWorkbook(); assert workBook != null; HSSFSheet workSheet = workBook.createSheet(workSheetName); assert workSheet != null; // create the header row HSSFRow headerRow = workSheet.createRow(0); assert workSheet != null; HSSFFont headerFont = workBook.createFont(); assert headerFont != null; headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(HSSFColor.BLACK.index); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); assert headerCellStyle != null; headerCellStyle.setFont(headerFont); // the first data list element should always be the column header map Map<String, Object> columnHeaderMap = data.get(0); if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = headerRow.createCell(i); assert cell != null; cell.setCellStyle(headerCellStyle); Object columnHeaderTitle = columnHeaderMap.get(columnNameList.get(i)); if (columnHeaderTitle != null) { cell.setCellValue(new HSSFRichTextString(columnHeaderTitle.toString())); } } } // create data rows // column data starts from the second element if (data.size() > 1) { // Create the style used for dates. HSSFCellStyle dateCellStyle = workBook.createCellStyle(); String dateFormat = "mm/dd/yyyy hh:mm:ss"; HSSFDataFormat hsfDateFormat = workBook.createDataFormat(); short dateFormatIdx = hsfDateFormat.getFormat(dateFormat); if (dateFormatIdx == -1) { Debug.logWarning("Date format [" + dateFormat + "] could be found or created, try one of the pre-built instead:" + HSSFDataFormat.getBuiltinFormats(), MODULE); } dateCellStyle.setDataFormat(dateFormatIdx); for (int dataRowIndex = 1; dataRowIndex < data.size(); dataRowIndex++) { Map<String, Object> rowDataMap = data.get(dataRowIndex); if (rowDataMap == null) { continue; } HSSFRow dataRow = workSheet.createRow(dataRowIndex); assert dataRow != null; for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = dataRow.createCell(i); assert cell != null; Object cellData = rowDataMap.get(columnNameList.get(i)); if (cellData != null) { // Note: dates are actually numeric values in Excel and so the cell need to have // a special style set so it actually displays as a date if (cellData instanceof Calendar) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Calendar) cellData); } else if (cellData instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) cellData); } else if (cellData instanceof BigDecimal) { cell.setCellValue(((BigDecimal) cellData).doubleValue()); } else if (cellData instanceof Double) { cell.setCellValue(((Double) cellData).doubleValue()); } else if (cellData instanceof Integer) { cell.setCellValue(((Integer) cellData).doubleValue()); } else if (cellData instanceof BigInteger) { cell.setCellValue(((BigInteger) cellData).doubleValue()); } else { cell.setCellValue(new HSSFRichTextString(cellData.toString())); } } } } } // auto size the column width if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { workSheet.autoSizeColumn(i); } } // create the Excel file workBook.write(fileOut); fileOut.close(); }
From source file:org.rti.zcore.dar.report.StockUsageReport.java
License:Apache License
@Override public void getPatientRegister(Date beginDate, Date endDate, int siteId) { Connection conn = null;// w ww .j av a2 s .c o m try { conn = DatabaseUtils.getZEPRSConnection(org.rti.zcore.Constants.DATABASE_ADMIN_USERNAME); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldStyle.setFont(font); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setWrapText(true); headerStyle.setFont(font); //log.debug("Before getPatientStockMap:" + DateUtils.getTime()); HashMap<Long, List<StockControl>> stockMap = InventoryDAO.getPatientStockMap(conn, siteId, beginDate, endDate); //log.debug("Before EncountersDAO.getAll:" + DateUtils.getTime()); // loop through all of the items //List<Item> items = EncountersDAO.getAll(conn, Long.valueOf(131), "SQL_RETRIEVE_ALL_ADMIN131", Item.class); List<Item> items = null; Map queries = QueryLoader.instance().load("/" + Constants.SQL_GENERATED_PROPERTIES); String sql = (String) queries.get("SQL_RETRIEVE_ALL_ADMIN131") + " ORDER BY item.name"; ArrayList values = new ArrayList(); items = DatabaseUtils.getList(conn, Item.class, sql, values); //log.debug("Before looping through items:" + DateUtils.getTime()); //int j = 0; boolean generateReport = false; for (Item item : items) { Boolean useInReport = item.getUse_in_report(); Long itemId = item.getId(); useInReport = Boolean.TRUE; if ((useInReport != null) && (useInReport.booleanValue() == Boolean.TRUE)) { List<StockControl> patientStockChanges = stockMap.get(itemId); if (patientStockChanges == null) { patientStockChanges = new ArrayList<StockControl>(); } List<StockControl> stockChanges = InventoryDAO.getStockEncounterChanges(conn, itemId, siteId, beginDate, endDate, null, patientStockChanges); int stockChangesSize = stockChanges.size(); //if ((stockChangesSize > 0) || ((stockChangesSize == 0 && (item.getUse_in_report() != null) && (!item.getUse_in_report().equals(Boolean.FALSE))))) { if ((stockChangesSize > 0)) { generateReport = true; StockReport stockReport = InventoryDAO.generateStockSummary(conn, itemId, beginDate, stockChanges, false); // populate the common fields //HSSFSheet sheet = wb.getSheetAt(j); String fixedName = StringManipulation.escapeString(item.getName()).replace("/", " ") .replace("\\", " ").replace(",", "_").replace("[", "").replace("(", "-") .replace(")", "-"); int lenName = fixedName.length(); String itemIdString = String.valueOf(itemId); int lenItemId = itemIdString.length(); String uniqueName = null; if ((lenName + lenItemId) < 31) { uniqueName = fixedName + "_" + itemId; //log.debug(itemId + " size: " + uniqueName.length()); } else { int offset = (30 - lenItemId) - 1; if (lenName > offset) { uniqueName = fixedName.substring(0, offset) + "_" + itemIdString; //log.debug(itemId + " size: " + uniqueName.length()); } else { uniqueName = fixedName + "_" + itemId; } } HSSFSheet sheet = null; try { sheet = wb.createSheet(uniqueName); } catch (IllegalArgumentException e) { log.debug("Problem with name : " + uniqueName + " Error: " + e.getMessage()); //this.setType("error"); // e.printStackTrace(); } String code = ""; if (item.getCode() != null) { code = " (" + item.getCode() + ")"; } if (sheet != null) { //sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(item.getName() + code)); HSSFHeader header = sheet.getHeader(); header.setCenter(item.getName() + code); /*HSSFCell titleCell = sheet.createRow(0).createCell(0); titleCell.setCellStyle(boldStyle); titleCell.setCellValue(new HSSFRichTextString(item.getName() + code));*/ //sheet.createRow(2).createCell(0).setCellValue(new HSSFRichTextString("Beginning Balance")); HSSFRow row = sheet.createRow(0); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Beginning \nBalance")); //sheet.getRow(0).createCell(1).setCellValue(new HSSFRichTextString("Quantity Received this period")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \nReceived \nthis period")); //sheet.getRow(0).createCell(2).setCellValue(new HSSFRichTextString("Quantity dispensed this period")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \ndispensed \nthis period")); //sheet.getRow(0).createCell(3).setCellValue(new HSSFRichTextString("Total Issued to Patients")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Total Issued \nto Patients")); //sheet.getRow(0).createCell(4).setCellValue(new HSSFRichTextString("Positive Adjustments")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Positive \nAdjustments")); //sheet.getRow(0).createCell(5).setCellValue(new HSSFRichTextString("Negative Adjustments")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Negative \nAdjustments")); //sheet.getRow(0).createCell(6).setCellValue(new HSSFRichTextString("Ending Balance / Physical Count")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Ending Balance \nPhysical Count")); sheet.autoSizeColumn((short) 0); sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.createRow(1).createCell(0).setCellValue(stockReport.getBalanceBF()); sheet.getRow(1).createCell(1).setCellValue(stockReport.getAdditionsTotal()); sheet.getRow(1).createCell(2).setCellValue(stockReport.getDeletionsTotal()); sheet.getRow(1).createCell(3).setCellValue(stockReport.getTotalDispensed()); sheet.getRow(1).createCell(4).setCellValue(stockReport.getPosAdjustments()); sheet.getRow(1).createCell(5).setCellValue(stockReport.getNegAdjustments()); sheet.getRow(1).createCell(6).setCellValue(stockReport.getOnHand()); row = sheet.createRow(4); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); //sheet.createRow(4).createCell(0).setCellValue(new HSSFRichTextString("Date")); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Date")); //sheet.getRow(4).createCell(1).setCellValue(new HSSFRichTextString("Type of Stock Change")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Type of \nStock \nChange")); //sheet.getRow(4).createCell(2).setCellValue(new HSSFRichTextString("Expiry Date")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Expiry \nDate")); //sheet.getRow(4).createCell(3).setCellValue(new HSSFRichTextString("Reference / Notes")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Reference/ \n Notes")); //sheet.getRow(4).createCell(4).setCellValue(new HSSFRichTextString("Additions")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Additions")); //sheet.getRow(4).createCell(5).setCellValue(new HSSFRichTextString("Subtractions")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Subtractions")); //sheet.getRow(4).createCell(6).setCellValue(new HSSFRichTextString("Recorded Balance")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Recorded \nBalance")); //sheet.getRow(4).createCell(7).setCellValue(new HSSFRichTextString("Calculated Balance")); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Calculated \nBalance")); sheet.autoSizeColumn((short) 7); int k = 4; for (StockControl stockControl : stockChanges) { if (stockControl.getDate_of_record() != null) { k++; String stockChangeTypeString = null; Integer posAdjust = null; Integer negAdjust = null; if (stockControl.getType_of_change() != null) { int stockChangeType = stockControl.getType_of_change(); switch (stockChangeType) { case 3263: stockChangeTypeString = "Received"; posAdjust = stockControl.getChange_value(); break; case 3264: stockChangeTypeString = "Issued"; negAdjust = stockControl.getChange_value(); break; case 3265: stockChangeTypeString = "Losses"; negAdjust = stockControl.getChange_value(); break; case 3266: stockChangeTypeString = "Pos. Adjust."; posAdjust = stockControl.getChange_value(); break; case 3267: stockChangeTypeString = "Neg. Adjust."; negAdjust = stockControl.getChange_value(); break; case 3279: stockChangeTypeString = "Out-of-stock"; break; default: break; } } row = sheet.createRow(k); if (stockControl.getDate_of_record() != null) { Date dateRecord = stockControl.getDate_of_record(); row.createCell(0) .setCellValue(new HSSFRichTextString(dateRecord.toString())); } else { row.createCell(0).setCellValue(new HSSFRichTextString("")); } row.createCell(1).setCellValue(new HSSFRichTextString(stockChangeTypeString)); if (stockControl.getExpiry_date() != null) { Date expiryDate = stockControl.getExpiry_date(); row.createCell(2) .setCellValue(new HSSFRichTextString(expiryDate.toString())); } else { row.createCell(2).setCellValue(new HSSFRichTextString("")); } row.createCell(3).setCellValue(new HSSFRichTextString(stockControl.getNotes())); if (posAdjust != null) { row.createCell(4).setCellValue(posAdjust); } if (negAdjust != null) { row.createCell(5).setCellValue(negAdjust); } if (stockControl.getBalance() != null) { row.createCell(6).setCellValue(stockControl.getBalance()); } if (stockControl.getComputedBalance() != null) { row.createCell(7).setCellValue(stockControl.getComputedBalance()); } } } } } } //j++; } if (generateReport) { FileOutputStream stream = new FileOutputStream(this.getReportPath()); wb.write(stream); stream.close(); } else { this.setType("empty"); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { log.error(e); } } }
From source file:org.sakaiproject.jsf.spreadsheet.SpreadsheetDataFileWriterXls.java
License:Educational Community License
private HSSFWorkbook getAsWorkbook(List<List<Object>> spreadsheetData) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFCellStyle headerCs = wb.createCellStyle(); Iterator<List<Object>> dataIter = spreadsheetData.iterator(); // Set the header style headerCs.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerCs.setFillBackgroundColor(HSSFColor.BLUE_GREY.index); // Set the font HSSFCellStyle cellStyle = null;// ww w . ja v a 2 s . com String fontName = ServerConfigurationService.getString("spreadsheet.font"); if (fontName != null) { HSSFFont font = wb.createFont(); font.setFontName(fontName); headerCs.setFont(font); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); } // By convention, the first list in the list contains column headers. HSSFRow headerRow = sheet.createRow((short) 0); List<Object> headerList = dataIter.next(); for (short i = 0; i < headerList.size(); i++) { HSSFCell headerCell = createCell(headerRow, i); headerCell.setCellValue((String) headerList.get(i)); headerCell.setCellStyle(headerCs); sheet.autoSizeColumn(i); } short rowPos = 1; while (dataIter.hasNext()) { List<Object> rowData = dataIter.next(); HSSFRow row = sheet.createRow(rowPos++); for (short i = 0; i < rowData.size(); i++) { HSSFCell cell = createCell(row, i); Object data = rowData.get(i); if (data != null) { if (data instanceof Double) { cell.setCellValue(((Double) data).doubleValue()); } else { cell.setCellValue(data.toString()); } if (cellStyle != null) { cell.setCellStyle(cellStyle); } } } } return wb; }
From source file:org.sentilo.web.catalog.view.ExcelBuilder.java
License:Open Source License
@SuppressWarnings("unchecked") @Override// w ww . ja v a 2s . co m protected void buildExcelDocument(final Map<String, Object> model, final HSSFWorkbook workbook, final HttpServletRequest request, final HttpServletResponse response) throws Exception { final List<List<String>> resourceList = (List<List<String>>) model.get(Constants.RESULT_LIST); final List<String> columnsKeys = buildColumnKeys(model); final boolean ignoreFirstValue = (!CollectionUtils.isEmpty(resourceList) && columnsKeys.size() != resourceList.get(0).size()); final HSSFSheet sheet = workbook.createSheet("list"); final HSSFRow header = sheet.createRow(0); final HSSFCellStyle style = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); // Call method to put the Column names Headers getHeaderExcel(header, style, columnsKeys); int i = 0; for (final List<String> rowValues : resourceList) { final HSSFRow row = sheet.createRow(++i); // put the content in the rows toExcelRow(rowValues, row, sheet, ignoreFirstValue); } for (int j = 0; j <= columnsKeys.size(); j++) { sheet.autoSizeColumn(j); } }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.template.IndicatorEntryExcelTemplate.java
License:Open Source License
private void createDetailSheet(final IndicatorDTO indicator) { final boolean isQualitative = indicator.getAggregation() == IndicatorDTO.AGGREGATE_MULTINOMIAL; final HSSFSheet sheetEx = wb .createSheet(utils.normalizeAsLink(ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName())); int rowIndex = -1; final List<PivotTableData.Axis> leaves = data.getEntryMap().get(indicator.getId()).getRootColumn() .getLeaves();/* www . j a v a 2s .com*/ int numbOfLeaves = leaves.size(); int numbOfCols = 4; //back to list link row = sheetEx.createRow(++rowIndex); utils.createLinkCell(row.createCell(1), data.getLocalizedVersion("goToIndicatorsList"), data.getLocalizedVersion("flexibleElementIndicatorsList"), false); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); //title utils.putMainTitle(sheetEx, ++rowIndex, indicator.getName(), numbOfCols); //empty row utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); //put details putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("code"), indicator.getCode(), numbOfCols); putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("group"), data.getGroupMap().get(indicator.getGroupId()), numbOfCols); //type String type = null; ; if (isQualitative) { //qualitative type = data.getLocalizedVersion("qualitative"); } else { //quantitative type = data.getLocalizedVersion("quantitative"); } putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("type"), type, numbOfCols); //conditional if (isQualitative) { //qualitative //possible values row = sheetEx.createRow(++rowIndex); //key cell = utils.putHeader(row, 1, data.getLocalizedVersion("possibleValues")); cell.getCellStyle().setAlignment(CellStyle.ALIGN_RIGHT); //value final MultiItemText itemText = data.formatPossibleValues(indicator.getLabels()); utils.putBorderedBasicCell(sheetEx, rowIndex, 2, itemText.text); row.setHeightInPoints(itemText.lineCount * defHeight); region = new CellRangeAddress(rowIndex, rowIndex, 2, numbOfCols); sheetEx.addMergedRegion(utils.getBorderedRegion(region, sheetEx, wb)); } else { //quantitative //aggregation method String aggrMethod = null; if (indicator.getAggregation() == IndicatorDTO.AGGREGATE_AVG) aggrMethod = data.getLocalizedVersion("average"); else aggrMethod = data.getLocalizedVersion("sum"); putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("aggregationMethod"), aggrMethod, numbOfCols); //units putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("units"), indicator.getUnits(), numbOfCols); //target value putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("targetValue"), indicator.getObjective(), numbOfCols); } //source of ver putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("sourceOfVerification"), indicator.getSourceOfVerification(), numbOfCols); row.setHeightInPoints(utils.calculateLineCount(indicator.getSourceOfVerification(), 3 * 18) * defHeight); //comment putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("indicatorComments"), indicator.getDescription(), numbOfCols); row.setHeightInPoints(utils.calculateLineCount(indicator.getDescription(), 3 * 18) * defHeight); //value putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("value"), data.getFormattedValue(indicator), numbOfCols); //empty row utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); //data entry //header row = sheetEx.createRow(++rowIndex); row.setHeightInPoints(defHeight); int cellIndex = 0; utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("sideAndMonth")); Map<String, Integer> columnIndexMap = new HashMap<String, Integer>(); for (PivotTableData.Axis axis : leaves) { utils.putHeader(row, ++cellIndex, axis.getLabel()); columnIndexMap.put(axis.getLabel(), cellIndex); } //rows for (PivotTableData.Axis axis : data.getEntryMap().get(indicator.getId()).getRootRow().getChildren()) { row = sheetEx.createRow(++rowIndex); row.setHeightInPoints(defHeight); utils.putHeader(row, 1, axis.getLabel()); //populate empty cells for (int i = 0; i < numbOfLeaves; i++) { cell = utils.putBorderedBasicCell(sheetEx, rowIndex, i + 2, ""); } //insert values for (Map.Entry<PivotTableData.Axis, PivotTableData.Cell> entry : axis.getCells().entrySet()) { cellIndex = columnIndexMap.get(entry.getKey().getLabel()); Object value = null; boolean rightAligned = false; if (isQualitative) { value = data.getLabelByIndex(indicator.getLabels(), entry.getValue().getValue()); } else { value = new Long(Math.round(entry.getValue().getValue())); rightAligned = true; } putValueCell(sheetEx, rowIndex, cellIndex, value, rightAligned); } } //col width sheetEx.setColumnWidth(0, 256 * 2); sheetEx.autoSizeColumn(1); for (int i = 2; i < 2 + numbOfLeaves; i++) { sheetEx.setColumnWidth(i, 256 * 16); } }