List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column, boolean useMergedCells)
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.
You can specify whether the content of merged cells should be considered or ignored.
From source file:org.meveocrm.admin.action.reporting.MeasurementBean.java
License:Open Source License
public void generateExcelReport(Object document) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException { FacesContext facesContext = FacesContext.getCurrentInstance(); String messageBundleName = facesContext.getApplication().getMessageBundle(); ResourceBundle messageBundle = ResourceBundle.getBundle(messageBundleName); HSSFWorkbook workbook = (HSSFWorkbook) document; HSSFSheet sheet = workbook.getSheetAt(0); Row row = sheet.createRow(1);/*from ww w. j a v a2 s . com*/ Cell dateCell = row.createCell(0); dateCell.setCellValue("Date"); dateCell.setCellStyle(getCellStyle(workbook)); int j = 1; while (hasDimension(j) && j < 4) { if (j > 1 && getDimension(j).size() > 0) { row = sheet.createRow(j); } log.info(getDimension(j).toString()); Integer dimCounter = 1; Integer colspan = 0; for (String dimension : getDimension(j)) { Integer colFrom = dimCounter + colspan; Cell cell = row.createCell(dimCounter + colspan); cell.setCellStyle(getCellStyle(workbook)); if (hasSubDimension(dimension, j)) { colspan += getColspan(dimension, j); Integer colTo = colspan; sheet.addMergedRegion(new CellRangeAddress(j, j, colFrom, colTo)); for (int i = dimCounter + 1; i <= colspan; i++) { Cell blankCell = row.createCell(i); blankCell.setCellStyle(getCellStyle(workbook)); } } else { dimCounter++; } if (dimension1Filter != null && !dimension1Filter.isEmpty()) { if (dimension1Filter.equals(dimension) && j <= 1) { cell.setCellValue(dimension); } else if (j > 1) { cell.setCellValue(dimension); } } else { cell.setCellValue(dimension); } } j++; } // for (List<MeasuredValue> mv : mainMVModel) { row = sheet.createRow(j); int mvCounter = 0; for (MeasuredValue subMV : mainMVModel) { Cell cell = row.createCell(mvCounter); if (mvCounter == 0) { cell.setCellValue(sdf1.format(subMV.getDate())); } else { if (subMV.getValue() != null && subMV.getMeasurementPeriod() == period) { cell.setCellValue(subMV.getValue() == null ? 0 : subMV.getValue().doubleValue()); } } cell.setCellStyle(getCellStyle(workbook)); sheet.autoSizeColumn(mvCounter, true); mvCounter++; } j++; // } HSSFRow reportTitleRow = sheet.getRow(0); HSSFCell reportTitleCell = reportTitleRow.createCell(0); reportTitleCell.setCellValue(messageBundle.getString("menu.measuredValues") + " " + new SimpleDateFormat("MMMM").format(selectedDate) + "," + new SimpleDateFormat("yyyy").format(selectedDate) + " " + messageBundle.getString("entity.measuredvalue.measurementPeriod") + " : " + messageBundle.getString("enum.measurementperiod." + measuredPeriod)); sheet.autoSizeColumn(0); }
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 w ww .j a va 2 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:org.openelis.bean.WorksheetExcelHelperBean.java
License:Open Source License
private HSSFWorkbook buildTemplate(DictionaryDO formatDO) throws Exception { int c;// w w w . j a v a2 s .c o m ArrayList<DictionaryDO> formatColumns; Cell hCell, tCell; FileInputStream in; HSSFSheet resultSheet; HSSFWorkbook wb; Name cellName; Row hRow, tRow; in = new FileInputStream(getWorksheetTemplateFileName("Base")); wb = new HSSFWorkbook(in, true); createStyles(wb); try { formatColumns = categoryCache.getBySystemName(formatDO.getSystemName()).getDictionaryList(); } catch (Exception anyE) { throw new Exception("Error retrieving worksheet format: " + anyE.getMessage()); } c = 9; resultSheet = wb.getSheet("Worksheet"); hRow = resultSheet.getRow(0); tRow = resultSheet.getRow(1); for (DictionaryDO columnDO : formatColumns) { hCell = hRow.createCell(c); hCell.setCellStyle(styles.get("header")); hCell.setCellValue(columnDO.getEntry()); tCell = tRow.createCell(c); tCell.setCellStyle(styles.get("row_edit")); cellName = wb.createName(); cellName.setNameName(columnDO.getSystemName().substring(formatDO.getSystemName().length())); cellName.setRefersToFormula( "Worksheet!$" + CellReference.convertNumToColString(c) + "$" + (tRow.getRowNum() + 1)); resultSheet.autoSizeColumn(c, true); c++; } return wb; }