List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet
@Override
public HSSFSheet getSheet(String name)
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;//ww w. ja v a2 s. co m Row row; 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;// www.j a va 2s.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
@TransactionTimeout(600) public WorksheetManager1 importFromExcel(WorksheetManager1 manager) throws Exception { boolean editLocked; int a, i, c, r, rowIndex; ArrayList<String> userNames; ArrayList<SystemUserVO> validUserVOs; ArrayList<WorksheetAnalysisViewDO> waList; ArrayList<WorksheetResultViewDO> wrList; ArrayList<WorksheetQcResultViewDO> wqrList; File file;/*from w w w. java 2 s. co m*/ FileInputStream in; HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>> waMap; HashMap<Integer, ArrayList<WorksheetResultViewDO>> wrMap; HashMap<Integer, ArrayList<WorksheetQcResultViewDO>> wqrMap; HashMap<Integer, WorksheetAnalysisViewDO> waLinkMap; HSSFWorkbook wb; Object value; SectionPermission perm; SimpleDateFormat format; String validUsers; ValidationErrorsList errorList; manager = worksheetManager.fetchForUpdate(manager.getWorksheet().getId()); format = new SimpleDateFormat("yyyy-MM-dd HH:mm"); errorList = new ValidationErrorsList(); try { file = new File(getWorksheetOutputFileName(manager.getWorksheet().getId(), manager.getWorksheet().getSystemUserId())); in = new FileInputStream(file); wb = new HSSFWorkbook(in); } catch (Exception anyE) { worksheetManager.unlock(manager.getWorksheet().getId()); throw anyE; } loadStatuses(); i = 0; r = 0; rowIndex = 1; 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); 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) { // // increment rowIndex if there were no result records for the // previous analysis if (r == 0 && a != 0) rowIndex++; if (waVDO.getAnalysisId() != null) { perm = userCache.getPermission().getSection(waVDO.getSectionName()); editLocked = (waVDO.getFromOtherId() != null || Constants.dictionary().ANALYSIS_INPREP.equals(waVDO.getStatusId()) || Constants.dictionary().ANALYSIS_RELEASED.equals(waVDO.getStatusId()) || Constants.dictionary().ANALYSIS_CANCELLED.equals(waVDO.getStatusId()) || perm == null || !perm.hasCompletePermission()); wrList = wrMap.get(waVDO.getId()); if (!editLocked) { value = getValueFromCellByName(wb.getSheet("Overrides"), "analysis_users." + i + "." + a); validUsers = ""; if (value != null) { userNames = new ArrayList<String>(Arrays.asList(((String) value).split(","))); try { validUserVOs = userCache.validateSystemUsers(userNames); for (SystemUserVO userVO : validUserVOs) { if (userNames.contains(userVO.getLoginName())) { if (validUsers.length() > 0) validUsers += ","; validUsers += userVO.getLoginName(); userNames.remove(userVO.getLoginName()); } } if (userNames.size() > 0) errorList.add(new FormErrorException( Messages.get().worksheet_illegalWorksheetUserFormException( userNames.toString(), String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } catch (Exception anyE) { errorList.add(new FormErrorException( Messages.get().worksheet_illegalWorksheetUserFormException( userNames.toString(), String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } } waVDO.setSystemUsers(validUsers); value = getValueFromCellByName(wb.getSheet("Overrides"), "analysis_started." + i + "." + a); if (value != null) { if (value instanceof Datetime) { waVDO.setStartedDate((Datetime) value); } else if (value instanceof String) { try { waVDO.setStartedDate(new Datetime(Datetime.YEAR, Datetime.MINUTE, format.parse((String) value))); } catch (ParseException parE) { errorList.add(new FormErrorException( Messages.get().worksheet_unparseableStartedDate( String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } } } else { waVDO.setStartedDate(null); } value = getValueFromCellByName(wb.getSheet("Overrides"), "analysis_completed." + i + "." + a); if (value != null) { if (value instanceof Datetime) { waVDO.setCompletedDate((Datetime) value); } else if (value instanceof String) { try { waVDO.setCompletedDate(new Datetime(Datetime.YEAR, Datetime.MINUTE, format.parse((String) value))); } catch (ParseException anyE) { errorList.add(new FormErrorException( Messages.get().worksheet_unparseableCompletedDate( String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } } } else { waVDO.setCompletedDate(null); } r = 0; if (wrList != null && wrList.size() > 0) { for (WorksheetResultViewDO wrVDO : wrList) { for (c = 0; c < 30; c++) { value = getValueFromCellByCoords(wb.getSheet("Worksheet"), rowIndex, 9 + c); if (value != null && !value.equals(wrVDO.getValueAt(c))) wrVDO.setValueAt(c, value.toString()); else if (value == null && wrVDO.getValueAt(c) != null) wrVDO.setValueAt(c, null); } r++; rowIndex++; } } } else { // // increment rowIndex and r since we skipped running // through the result records due to permissions or // status // r = wrList.size(); rowIndex += r; } } else if (waVDO.getQcLotId() != null) { wqrList = wqrMap.get(waVDO.getId()); r = 0; if (wqrList != null && wqrList.size() > 0) { for (WorksheetQcResultViewDO wqrVDO : wqrList) { for (c = 0; c < 30; c++) { value = getValueFromCellByCoords(wb.getSheet("Worksheet"), rowIndex, 9 + c); if (value != null && !value.equals(wqrVDO.getValueAt(c))) wqrVDO.setValueAt(c, value.toString()); else if (value == null && wqrVDO.getValueAt(c) != null) wqrVDO.setValueAt(c, null); } r++; rowIndex++; } } value = getValueFromCellByName(wb.getSheet("Overrides"), "analysis_users." + i + "." + a); validUsers = ""; if (value != null) { userNames = new ArrayList<String>(Arrays.asList(((String) value).split(","))); try { validUserVOs = userCache.validateSystemUsers(userNames); for (SystemUserVO userVO : validUserVOs) { if (userNames.contains(userVO.getLoginName())) { if (validUsers.length() > 0) validUsers += ","; validUsers += userVO.getLoginName(); userNames.remove(userVO.getLoginName()); } } if (userNames.size() > 0) errorList.add(new FormErrorException( Messages.get().worksheet_illegalWorksheetUserFormException( userNames.toString(), String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } catch (Exception anyE) { errorList.add(new FormErrorException(Messages.get() .worksheet_illegalWorksheetUserFormException(userNames.toString(), String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } } waVDO.setSystemUsers(validUsers); // TODO: Need to move the following code to commit. // } else if (waVDO.getSystemUsers() == null) { // try { // userVO = userCache.getSystemUser(); // waVDO.setSystemUsers(userVO.getLoginName()); // } catch (Exception anyE) { // errorList.add(new FormErrorException(Messages.get().defaultWorksheetQcUserFormException( // String.valueOf(wiDO.getPosition()), // String.valueOf(a + 1)))); // } // } value = getValueFromCellByName(wb.getSheet("Overrides"), "analysis_started." + i + "." + a); if (value != null) { if (value instanceof Datetime) { waVDO.setStartedDate((Datetime) value); } else if (value instanceof String) { try { waVDO.setStartedDate(new Datetime(Datetime.YEAR, Datetime.MINUTE, format.parse((String) value))); } catch (ParseException anyE) { errorList.add(new FormErrorException( Messages.get().worksheet_unparseableStartedDate( String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } } } else { waVDO.setStartedDate(null); } value = getValueFromCellByName(wb.getSheet("Overrides"), "analysis_completed." + i + "." + a); if (value != null) { if (value instanceof Datetime) { waVDO.setCompletedDate((Datetime) value); } else if (value instanceof String) { try { waVDO.setCompletedDate(new Datetime(Datetime.YEAR, Datetime.MINUTE, format.parse((String) value))); } catch (ParseException anyE) { errorList.add(new FormErrorException( Messages.get().worksheet_unparseableCompletedDate( String.valueOf(wiDO.getPosition()), String.valueOf(a + 1)))); } } } else { waVDO.setCompletedDate(null); } } } a++; } // // increment rowIndex if there were no result records for the // last analysis or there were no analyses for this item if (r == 0) rowIndex++; i++; } } if (errorList.getErrorList().size() > 0) { try { worksheetManager.unlock(manager.getWorksheet().getId()); } catch (Exception anyE) { errorList.add(anyE); } throw errorList; } else { manager = worksheetManager.update(manager, null); } file.delete(); return manager; }
From source file:org.openelis.bean.WorksheetExcelHelperBean.java
License:Open Source License
private HSSFWorkbook buildTemplate(DictionaryDO formatDO) throws Exception { int c;/*from w w w. jav a 2 s .co 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; }
From source file:org.openhealthtools.mdht.cda.generate.C32Generator.java
License:Open Source License
public static void main(String[] args) { String fileName = args[0];/* ww w . ja v a2s. co m*/ final HSSFWorkbook wb; try { wb = C32Generator.readFile(fileName); /* * Define and override various builders */ C32DocumentBuilder exampleHITSPC32 = new C32DocumentBuilder() { // note a single Random object is reused here Random randomGenerator = new Random(); HSSFSheet patients = wb.getSheet("Patients"); HSSFSheet immunizationsSheet = wb.getSheet("Immunizations"); @Override public Patient buildPatient() { // int rows = patients.getPhysicalNumberOfRows() - 1; int randomRow = randomGenerator.nextInt(rows) + 1; return new ExcelBuildPatient(patients.getRow(randomRow)).construct(); } @Override public ImmunizationsSectionDirector getImmunizationsSectionDirector() { return new ImmunizationsSectionDirector() { @Override public List<SubstanceAdministration> buildSubstanceAdministrations() { int rows = immunizationsSheet.getPhysicalNumberOfRows() - 1; ArrayList<SubstanceAdministration> immunizations = new ArrayList<SubstanceAdministration>(); for (int i = 0; i < 5; i++) { int randomRow = randomGenerator.nextInt(rows) + 1; HSSFRow row = immunizationsSheet.getRow(randomRow); Immunization immunization = HITSPFactory.eINSTANCE.createImmunization().init(); immunization.setNegationInd(false); immunization.getIds().add(DatatypesFactory.eINSTANCE .createII(java.util.UUID.randomUUID().toString())); immunization.setStatusCode(DatatypesFactory.eINSTANCE.createCS("completed")); SXCM_TS effectiveTime = DatatypesFactory.eINSTANCE.createSXCM_TS(); effectiveTime.setNullFlavor(NullFlavor.UNK); immunization.getEffectiveTimes().add(effectiveTime); immunization.setCode(DatatypesFactory.eINSTANCE.createCD("IMMUNIZ", "2.16.840.1.113883.5.4", "ActCode", "")); Consumable consumable = CDAFactory.eINSTANCE.createConsumable(); ProductEntry product = IHEFactory.eINSTANCE.createProductEntry().init(); Material material = CDAFactory.eINSTANCE.createMaterial(); CE ce = DatatypesFactory.eINSTANCE.createCE(); ce.setCode(getCellValueAsString(row.getCell(0))); ce.setCodeSystem("2.16.840.1.113883.6.59"); ce.setDisplayName(getCellValueAsString(row.getCell(1))); ce.setOriginalText( DatatypesFactory.eINSTANCE.createED(getCellValueAsString(row.getCell(2)))); material.setCode(ce); product.setManufacturedMaterial(material); consumable.setManufacturedProduct(product); immunization.setConsumable(consumable); immunizations.add(immunization); } return immunizations; } }; } }; try { System.out.println("Start C32 Document Build Example"); for (int i = 0; i < 5; i++) { ClinicalDocument clinicalDocument = exampleHITSPC32.buildDocument(); CDAUtil.save(clinicalDocument, System.out); boolean valid = CDAUtil.validate(clinicalDocument, new BasicValidationHandler() { @Override public void handleError(Diagnostic diagnostic) { System.out.println("ERROR: " + diagnostic.getMessage()); } }); } System.out.println("Completed C32 Document Build Example"); } catch (Exception e) { e.printStackTrace(); } } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Uploads an Excel file in the correct directory. * @exception ServiceException if an error occurs *//*from w ww . j av a 2 s .com*/ public void parseFileForDataImport() throws ServiceException { // Get the uploaded file File file = getUploadedExcelFile(getUploadedFileName()); // set it up as an Excel workbook POIFSFileSystem fs = null; HSSFWorkbook wb = null; try { // this will auto close the FileInputStream when the constructor completes fs = new POIFSFileSystem(new FileInputStream(file)); wb = new HSSFWorkbook(fs); } catch (IOException e) { throw new ServiceException("Unable to read or create workbook from file [" + getUploadedFileName() + "] " + e.getMessage()); } // loop through the tabs and import them one by one try { // a collection of all the records from all the excel spreadsheet tabs FastList<EntityInterface> entitiesToCreate = FastList.newInstance(); for (String excelTab : EXCEL_TABS) { HSSFSheet sheet = wb.getSheet(excelTab); if (sheet == null) { Debug.logWarning("Did not find a sheet named " + excelTab + " in " + file.getName() + ". Will not be importing anything.", MODULE); } else { if (EXCEL_PRODUCT_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportProducts(sheet)); } else if (EXCEL_SUPPLIERS_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportSuppliers(sheet)); } else if (EXCEL_CUSTOMERS_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportCustomers(sheet)); } else if (EXCEL_INVENTORY_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportInventory(sheet)); } else if (EXCEL_GL_ACCOUNTS_TAB.equals(excelTab)) { entitiesToCreate.addAll(createDataImportGlAccounts(sheet)); } // etc ... } } // create and store values from all the sheets in the workbook in database using the PartyRepositoryInterface // note we're just using the most basic repository method, so any repository could do here PartyRepositoryInterface partyRepo = this.getDomainsDirectory().getPartyDomain().getPartyRepository(); partyRepo.createOrUpdate(entitiesToCreate); } catch (RepositoryException e) { throw new ServiceException(e); } // remove the uploaded file now if (!file.delete()) { Debug.logWarning("Could not delete the file : " + file.getName(), MODULE); } }
From source file:org.pathwayeditor.metabolic.excelexport.ExcelGenerator.java
License:Apache License
public boolean isTemplateValid() { HSSFWorkbook loadedTemplate; try {/*from w w w . ja v a2s. com*/ loadedTemplate = loadTemplateFromPath(); } catch (IOException e) { return false; } if (loadedTemplate == null) return false; if (loadedTemplate.getSheet("Model") == null) return false; if (loadedTemplate.getSheet("Compounds") == null) return false; if (loadedTemplate.getSheet("Reactions") == null) return false; if (loadedTemplate.getSheet("Macromolecules") == null) return false; return true; }
From source file:org.riotfamily.dbmsgsrc.riot.ImportMessageEntriesCommand.java
License:Apache License
private void updateMessages(byte[] data, boolean addNewMessages) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(data)); HSSFSheet sheet = wb.getSheet("Translations"); if (isValid(sheet)) { for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { HSSFRow row = sheet.getRow(i); if (row.getCell(1) != null && row.getCell(2) != null) { String code = row.getCell(1).getRichStringCellValue().getString(); String defaultMessage = row.getCell(2).getRichStringCellValue().getString(); String comment = null; if (row.getCell(3) != null) { comment = row.getCell(3).getRichStringCellValue().getString(); }//from ww w.j av a 2 s . co m if (StringUtils.hasText(defaultMessage) || StringUtils.hasText(comment)) { MessageBundleEntry entry = MessageBundleEntry.loadByBundleAndCode(bundle, code); if (entry != null) { entry.getDefaultMessage().setText(defaultMessage); entry.setComment(comment); entry.save(); } else if (addNewMessages) { entry = new MessageBundleEntry(bundle, code, defaultMessage); entry.setComment(comment); entry.save(); } else { log.info("Message Code does not exist and creation not allowed - " + code); } } } else { log.info("Skipping invalid row {}", i); } } } }
From source file:org.riotfamily.dbmsgsrc.riot.ImportMessagesCommand.java
License:Apache License
private void updateMessages(byte[] data, Site site) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(data)); HSSFSheet sheet = wb.getSheet("Translations"); if (isValid(sheet)) { for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { HSSFRow row = sheet.getRow(i); if (row.getCell(1) != null && row.getCell(3) != null) { String code = row.getCell(1).getRichStringCellValue().getString(); String translation = row.getCell(3).getRichStringCellValue().getString(); if (StringUtils.hasText(translation)) { MessageBundleEntry entry = MessageBundleEntry.loadByBundleAndCode(bundle, code); if (entry != null) { entry.addTranslation(site.getLocale(), translation); entry.save();// w ww .ja v a 2 s . com } else { log.info("Message Code does not exist - " + code); } } } else { log.info("Skipping invalid row {}", i); } } } }
From source file:org.sakaiproject.assignment.impl.BaseAssignmentService.java
License:Educational Community License
protected void zipGroupSubmissions(String assignmentReference, String assignmentTitle, String gradeTypeString, int typeOfSubmission, Iterator submissions, OutputStream outputStream, StringBuilder exceptionMessage, boolean withStudentSubmissionText, boolean withStudentSubmissionAttachment, boolean withGradeFile, boolean withFeedbackText, boolean withFeedbackComment, boolean withFeedbackAttachment, String gradeFileFormat) { ZipOutputStream out = null;//from w ww .j ava 2 s . c om //Excel generation HSSFWorkbook gradesWorkbook = null; HSSFSheet dataSheet = null; try { out = new ZipOutputStream(outputStream); // create the folder structure - named after the assignment's title String root = escapeInvalidCharsEntry(Validator.escapeZipEntry(assignmentTitle)) + Entity.SEPARATOR; //Create excel datasheet if ("excel".equals(gradeFileFormat)) { String sheetTitle = escapeInvalidCharsEntry(Validator.escapeZipEntry(assignmentTitle)); gradesWorkbook = createGradesWorkbook(sheetTitle, true); dataSheet = gradesWorkbook.getSheet(sheetTitle); } String submittedText = ""; if (!submissions.hasNext()) { exceptionMessage.append("There is no submission yet. "); } // the buffer used to store grade information StringBuilder gradesBuffer = new StringBuilder(assignmentTitle + "," + gradeTypeString + "\n\n"); gradesBuffer.append("Group" + "," + rb.getString("grades.eid") + "," + rb.getString("grades.members") + "," + rb.getString("grades.grade") + "\n"); // allow add assignment members List allowAddSubmissionUsers = allowAddSubmissionUsers(assignmentReference); // Create the ZIP file String submittersName = ""; int count = 1; int xlsRowCount = 1; String caughtException = null; while (submissions.hasNext()) { GroupSubmission gs = (GroupSubmission) submissions.next(); AssignmentSubmission s = gs.getSubmission(); M_log.debug(this + " ZIPGROUP " + (s == null ? "null" : s.getId())); if (s.getSubmitted()) { try { count = 1; submittersName = root; User[] submitters = s.getSubmitters(); String submitterString = gs.getGroup().getTitle() + " (" + gs.getGroup().getId() + ")"; String submittersString = ""; String submitters2String = ""; for (int i = 0; i < submitters.length; i++) { if (i > 0) { submittersString = submittersString.concat("; "); submitters2String = submitters2String.concat("; "); } String fullName = submitters[i].getSortName(); // in case the user doesn't have first name or last name if (fullName.indexOf(",") == -1) { fullName = fullName.concat(","); } submittersString = submittersString.concat(fullName); submitters2String = submitters2String.concat(submitters[i].getDisplayName()); // add the eid to the end of it to guarantee folder name uniqness submittersString = submittersString + "(" + submitters[i].getEid() + ")"; } //Adding the row to the csv file if ("csv".equals(gradeFileFormat)) { gradesBuffer.append(gs.getGroup().getTitle() + "," + gs.getGroup().getId() + "," + submitters2String + "," + s.getGradeDisplay() + "\n"); } //Adding the row to the excel file if ("excel".equals(gradeFileFormat)) { addExcelRowInfo(dataSheet, xlsRowCount, true, gs.getGroup().getTitle(), gs.getGroup().getId(), submitters2String, null, s.getGradeDisplay()); xlsRowCount++; } if (StringUtil.trimToNull(submitterString) != null) { submittersName = submittersName.concat(StringUtil.trimToNull(submitterString)); submittedText = s.getSubmittedText(); submittersName = submittersName.concat("/"); // record submission timestamp if (s.getSubmitted() && s.getTimeSubmitted() != null) { ZipEntry textEntry = new ZipEntry(submittersName + "timestamp.txt"); out.putNextEntry(textEntry); byte[] b = (s.getTimeSubmitted().toString()).getBytes(); out.write(b); textEntry.setSize(b.length); out.closeEntry(); } // create the folder structure - named after the submitter's name if (typeOfSubmission != Assignment.ATTACHMENT_ONLY_ASSIGNMENT_SUBMISSION && typeOfSubmission != Assignment.NON_ELECTRONIC_ASSIGNMENT_SUBMISSION) { // include student submission text if (withStudentSubmissionText) { // create the text file only when a text submission is allowed ZipEntry textEntry = new ZipEntry(submittersName + submitterString + "_submissionText" + ZIP_SUBMITTED_TEXT_FILE_TYPE); out.putNextEntry(textEntry); byte[] text = submittedText.getBytes(); out.write(text); textEntry.setSize(text.length); out.closeEntry(); } // include student submission feedback text if (withFeedbackText) { // create a feedbackText file into zip ZipEntry fTextEntry = new ZipEntry(submittersName + "feedbackText.html"); out.putNextEntry(fTextEntry); byte[] fText = s.getFeedbackText().getBytes(); out.write(fText); fTextEntry.setSize(fText.length); out.closeEntry(); } } if (typeOfSubmission != Assignment.TEXT_ONLY_ASSIGNMENT_SUBMISSION && typeOfSubmission != Assignment.NON_ELECTRONIC_ASSIGNMENT_SUBMISSION) { // include student submission attachment if (withStudentSubmissionAttachment) { // create a attachment folder for the submission attachments String sSubAttachmentFolder = submittersName + rb.getString("stuviewsubm.submissatt") + "/"; ZipEntry sSubAttachmentFolderEntry = new ZipEntry(sSubAttachmentFolder); out.putNextEntry(sSubAttachmentFolderEntry); // add all submission attachment into the submission attachment folder zipAttachments(out, submittersName, sSubAttachmentFolder, s.getSubmittedAttachments()); out.closeEntry(); } } if (withFeedbackComment) { // the comments.txt file to show instructor's comments ZipEntry textEntry = new ZipEntry( submittersName + "comments" + ZIP_COMMENT_FILE_TYPE); out.putNextEntry(textEntry); byte[] b = FormattedText.encodeUnicode(s.getFeedbackComment()).getBytes(); out.write(b); textEntry.setSize(b.length); out.closeEntry(); } if (withFeedbackAttachment) { // create an attachment folder for the feedback attachments String feedbackSubAttachmentFolder = submittersName + rb.getString("download.feedback.attachment") + "/"; ZipEntry feedbackSubAttachmentFolderEntry = new ZipEntry( feedbackSubAttachmentFolder); out.putNextEntry(feedbackSubAttachmentFolderEntry); // add all feedback attachment folder zipAttachments(out, submittersName, feedbackSubAttachmentFolder, s.getFeedbackAttachments()); out.closeEntry(); } if (submittersString.trim().length() > 0) { // the comments.txt file to show instructor's comments ZipEntry textEntry = new ZipEntry( submittersName + "members" + ZIP_COMMENT_FILE_TYPE); out.putNextEntry(textEntry); byte[] b = FormattedText.encodeUnicode(submittersString).getBytes(); out.write(b); textEntry.setSize(b.length); out.closeEntry(); } } // if } catch (Exception e) { caughtException = e.toString(); break; } } // if the user is still in site } // while -- there is submission if (caughtException == null) { // continue if (withGradeFile) { if ("csv".equals(gradeFileFormat)) { // create a grades.csv file into zip ZipEntry gradesCSVEntry = new ZipEntry(root + "grades.csv"); out.putNextEntry(gradesCSVEntry); byte[] grades = gradesBuffer.toString().getBytes(); out.write(grades); gradesCSVEntry.setSize(grades.length); out.closeEntry(); } if ("excel".equals(gradeFileFormat)) { // create a grades.xls file into zip ZipEntry gradesEXCELEntry = new ZipEntry(root + "grades.xls"); out.putNextEntry(gradesEXCELEntry); gradesWorkbook.write(out); out.closeEntry(); } } } else { // log the error exceptionMessage.append(" Exception " + caughtException + " for creating submission zip file for assignment " + "\"" + assignmentTitle + "\"\n"); } } catch (IOException e) { exceptionMessage.append("IOException for creating submission zip file for assignment " + "\"" + assignmentTitle + "\" exception: " + e + "\n"); } finally { // Complete the ZIP file if (out != null) { try { out.finish(); out.flush(); } catch (IOException e) { // tried } try { out.close(); } catch (IOException e) { // tried } } } }