List of usage examples for org.apache.poi.hssf.usermodel DVConstraint createExplicitListConstraint
public static DVConstraint createExplicitListConstraint(String[] explicitListValues)
From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java
License:Apache License
private void setExplicitListConstraint(HSSFSheet sheet, String[] list, int firstRow, int lastRow, int firstCol, int lastCol) { //????/* w ww .j a v a 2 s . com*/ CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); DVConstraint constraint = DVConstraint.createExplicitListConstraint(list); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); validation.setEmptyCellAllowed(true); validation.setSuppressDropDownArrow(false); sheet.addValidationData(validation); }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ???// ww w . j a v a 2 s. c o m * @param dataBean * @param sheet * @param columnIndex * @param filedName */ public void DicCodeValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) { DicValidateConfig config = dataBean.getValidateConfig(filedName); String dicCode = config.dicCode(); Set<String> set = dicCodePool.getDicValueSet().get(dicCode); if (ObjectHelper.isEmpty(set)) { log.error("?{}", dicCode); return; } if (config.columnName() != 0) {//? createDicCodeSheet(config, sheet, columnIndex, set); } else { String[] strings = set.toArray(new String[] {}); DVConstraint constraint = DVConstraint.createExplicitListConstraint(strings); // ??,???? CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); // ? HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint); setValidationTip(data_validation_list, config); sheet.addValidationData(data_validation_list); } }
From source file:com.sccl.attech.common.utils.excel.ExportExcel.java
License:Open Source License
/** * ?//from w w w. j ava 2 s . co m * @param row * @param column ? * @param val * @param align ??1?23?? * @return ? */ public Cell addCellStyle(Row row, int column, Object val, int align, Class<?> fieldType) { Cell cell = row.createCell(column); CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : "")); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setWrapText(true); try { if (val == null) { cell.setCellValue(""); } else if (val instanceof String) { cell.setCellValue((String) val); } else if (val instanceof Integer) { cell.setCellValue((Integer) val); } else if (val instanceof Long) { cell.setCellValue((Long) val); } else if (val instanceof Double) { cell.setCellValue((Double) val); } else if (val instanceof Float) { cell.setCellValue((Float) val); } else if (val instanceof Date) { DataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("yyyy-MM-dd")); cell.setCellValue((Date) val); } else { if (fieldType != Class.class) { cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val)); } else { cell.setCellValue((String) Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class).invoke(null, val)); } } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } if (column == 8) { CellRangeAddressList regions = new CellRangeAddressList(25, 25, 8, 8); // ? DVConstraint constraint = DVConstraint .createExplicitListConstraint(new String[] { "2", "3", "" }); // HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint); // sheet data_validation.createErrorBox("Error", "Error"); data_validation.createPromptBox("", null); sheet.addValidationData(data_validation); } cell.setCellStyle(style); return cell; }
From source file:edu.casetools.rcase.extensions.excel.control.Exporter.java
License:Open Source License
/** * Creates the cell style.//from w ww . j av a2 s . c om * * @param sheet * the sheet where to create the style. * @param rowNumber * the row number where to create the style. * @param columnNumber * the column number where to create the style. * @param cell * the cell where to create the style. * @param cellTable * the cell table where to create the style. * @return the column number */ protected int createCellStyle(Sheet sheet, int rowNumber, int columnNumber, Cell cell, TableCell cellTable) { int auxiliarColumnNumber = columnNumber; cell.setCellStyle(this.contentCellStyle); cell.setCellValue(cellTable.getValue()); String[] possibleValues = cellTable.getPossibleValues(); if (0 != possibleValues.length) { DataValidation validation; DataValidationConstraint constraint; CellRangeAddressList addressList = new CellRangeAddressList(rowNumber, rowNumber, auxiliarColumnNumber, auxiliarColumnNumber); if (this.version == SpreadsheetVersion.EXCEL2007) { validation = createExcel2007CellStyle(sheet, possibleValues, addressList); } else { constraint = DVConstraint.createExplicitListConstraint(possibleValues); validation = new HSSFDataValidation(addressList, constraint); } if (validation != null) { sheet.addValidationData(validation); } } auxiliarColumnNumber++; return auxiliarColumnNumber; }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** *///from w w w . j a v a 2s. c o m public static void setOneLineHeader(HSSFSheet sheet, int iRowNum, ArrayList<String> al) { LogMaker.makelog("Creating Header in Excel"); try { HSSFRow row = sheet.createRow(iRowNum); row.setHeight((short) 600); int i = 0; for (String str : al) { row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(str); if (i == 1) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else if (i == 2) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else if (i == 3) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else { try { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); } catch (NullPointerException e) { e.printStackTrace(); } } sheet.autoSizeColumn((short) i, true); i++; } } catch (Exception e) { e.printStackTrace(); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
@SuppressWarnings("unchecked") public static void srcPr(HSSFSheet srcSheet) { LogMaker.makelog("prepare to Print ID File info to Excel sheet"); //category // w w w . j av a 2 s .c o m String[] strCategory = new String[FileVals.category.size()]; FileVals.category.toArray(strCategory); String[] strFPath = new String[FileVals.fPath.size()]; FileVals.fPath.toArray(strFPath); String[] strDType = new String[FileVals.dtype.size()]; FileVals.dtype.toArray(strDType); String[] strFileFolder = new String[FileVals.file.size()]; FileVals.file.toArray(strFileFolder); String[] strComponent = new String[FileVals.component.size()]; FileVals.component.toArray(strComponent); String[] strLicense = new String[FileVals.license.size()]; FileVals.license.toArray(strLicense); String[] strVersion = new String[FileVals.version.size()]; FileVals.version.toArray(strVersion); //matched files String[] strOSSFname = new String[FileVals.OSSFname.size()]; FileVals.OSSFname.toArray(strOSSFname); String[] strPercent = new String[FileVals.matchedRatio.size()]; FileVals.matchedRatio.toArray(strPercent); //OSS matched file line String[] strOSSFLine = new String[FileVals.OSSFLine.size()]; FileVals.OSSFLine.toArray(strOSSFLine); //Dev Matched First Line String[] strFirstLine = new String[FileVals.firstLine.size()]; FileVals.firstLine.toArray(strFirstLine); //matched total line String[] strTotalLine = new String[FileVals.tline.size()]; FileVals.tline.toArray(strTotalLine); //identified date String[] strIdentifiedDate = new String[FileVals.identifiedDate.size()]; FileVals.identifiedDate.toArray(strIdentifiedDate); //identifier String[] strIdentifier = new String[FileVals.identifier.size()]; FileVals.identifier.toArray(strIdentifier); String[] strComment = new String[FileVals.comment.size()]; FileVals.comment.toArray(strComment); int partialCount = Count.partialCount; //int partialCount=0; LogMaker.makelog("Printing File Info to excel"); for (int i = 0; i < strDType.length; i++) { //HSSFRow tempRow = srcSheet.createRow(i+2+iCount-(lineCount-1));//??? HSSFRow tempRow = srcSheet.createRow(i + 2 + Count.partialCount);//??? partialCount++; tempRow.setHeight((short) 500); String a = (strFileFolder[i].substring(1)); // category print int c = 0; try { tempRow.createCell(c).setCellValue(a.substring(0, a.indexOf("/"))); //if (strCate.equals(a.substring(0,a.indexOf("/")))){} } catch (Exception e) { tempRow.createCell(c).setCellValue(a); } tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; tempRow.createCell(c).setCellValue(a); tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //Identification type print tempRow.createCell(c).setCellValue(strDType[i]); tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; tempRow.createCell(c).setCellValue(strComponent[i]); tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //License Print tempRow.createCell(c).setCellValue(strLicense[i]); tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //version Print tempRow.createCell(c).setCellValue(strVersion[i]); tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //OSS matched Files;OSS matched Filename; print try { tempRow.createCell(c).setCellValue(strOSSFname[i]); } catch (Exception e) { tempRow.createCell(c).setCellValue(""); } tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //matched percent print; if (strPercent[i] == "" || strPercent[i] == null) { tempRow.createCell(c).setCellValue("100%"); } else { tempRow.createCell(c).setCellValue(strPercent[i] + "%"); } tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //OSS file line print try { tempRow.createCell(c).setCellValue(strOSSFLine[i]); } catch (Exception e) { tempRow.createCell(c).setCellValue(""); } tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //Dev Matched First Line print try { tempRow.createCell(c).setCellValue(strFirstLine[i]); } catch (Exception e) { tempRow.createCell(c).setCellValue(""); } tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //Dev Matched Total Line print try { tempRow.createCell(c).setCellValue(strTotalLine[i]); } catch (Exception e) { tempRow.createCell(c).setCellValue(""); } tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //Identified Date; identifieddate; try { tempRow.createCell(c).setCellValue(strIdentifiedDate[i]); } catch (Exception e) { tempRow.createCell(c).setCellValue(""); } //tempRow.createCell(c).setCellValue(strIdentifiedDate[i]); tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //Identifier print try { tempRow.createCell(c).setCellValue(strIdentifier[i]); } catch (Exception e) { tempRow.createCell(c).setCellValue(""); } tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //Comment print tempRow.createCell(c).setCellValue(strComment[i]); tempRow.getCell(c).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) c, true); c++; //other empty cell for (int temp = c; temp < 21; temp++) { tempRow.createCell(temp).setCellValue(""); tempRow.getCell(temp).setCellStyle(Style.componentCellStyle); //srcSheet.autoSizeColumn((short) c, true); if (temp == 16) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i + 2, i + 2, temp, temp); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); srcSheet.addValidationData(dataValidation); } if (temp == 15) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i + 2, i + 2, temp, temp); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); srcSheet.addValidationData(dataValidation); } //c++; } //tempRow.createCell(3).setCellValue(strComponent[i]+" (+"+(compCount-1)+")"); tempRow.createCell(3).setCellValue(strComponent[i] + " (+0)"); tempRow.getCell(3).setCellStyle(Style.componentCellStyle); srcSheet.autoSizeColumn((short) 3, true); FileVals.clearAllArrayList(); } Count.partialCount = partialCount; }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "Build Image Analysis" //from www .jav a 2s . c o m * * */ public static void BIA(HSSFWorkbook wb) { LogMaker.makelog("Making BIA sheet"); HSSFSheet sheetBIA = wb.createSheet("Build Image Analysis");// ??????? ArrayList<String> Header = new ArrayList<String>(); Header.add("Analysis Description"); Header.add("1st Reviewer (Development Team)"); Header.add("Final Revewer"); lineHeader(sheetBIA, 0, Header);// 0?? ???? ??????? sheetBIA.addMergedRegion(new Region(0, (short) 0, 0, (short) 5)); sheetBIA.addMergedRegion(new Region(0, (short) 6, 0, (short) 8)); sheetBIA.addMergedRegion(new Region(0, (short) 9, 0, (short) 10)); Header.clear(); HSSFRow row = sheetBIA.createRow(1); //row ???? row.setHeight((short) 500); Header.add("Binary File Path"); Header.add(" "); Header.add("Component"); Header.add("License");//drop down Header.add("Analyzer"); Header.add("Analyzer's Comments"); src2ndrow(sheetBIA, Header, 0, row, 6, 9); Header.clear(); sheetBIA.addMergedRegion(new Region(1, (short) 0, 1, (short) 1)); Header.add("Person In Charge"); Header.add("A or B");//drop down Header.add("Reviewer's Ccomments"); src2ndrow(sheetBIA, Header, 6, row, 6, 9);// 0?? ???? ??????? sheetBIA.autoSizeColumn((short) 6, true); sheetBIA.autoSizeColumn((short) 8, true); Header.clear(); Header.add("Review Date"); Header.add("Reviewer's Comments"); src2ndrow(sheetBIA, Header, 9, row, 6, 9);// 0?? ???? ??????? Header.clear(); for (int i = 2; i < 200; i++) { HSSFRow row2 = sheetBIA.createRow(i); row2.setHeight((short) 500); for (int j = 0; j < 11; j++) { row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue(" "); row2.getCell(j).setCellStyle(Style.componentCellStyle); if (j == 3) { CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheetBIA.addValidationData(dataValidation); } if (j == 7) { CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheetBIA.addValidationData(dataValidation); } } } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "Final List" /*from w w w .j a v a 2s . co m*/ * */ public static void FL(HSSFWorkbook wb) { LogMaker.makelog("Making Final sheet"); HSSFSheet sheet = wb.createSheet("Final List");// ??????? ArrayList<String> Header = new ArrayList<String>(); Header.add(" Component Name "); Header.add(" A or B "); //Drop down Header.add(" License "); //Drop Down Header.add(" Linkage Type "); //Drop down Header.add(" License Copy "); Header.add(" Remark "); // Macro ? ??? setOneLineHeader(sheet, 0, Header);// 0?? ???? ??????? Header.clear(); //?? ???? for (int i = 1; i < 200; i++) { HSSFRow row2 = sheet.createRow(i); row2.setHeight((short) 500); for (int j = 0; j < 6; j++) { row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row2.getCell(j).setCellStyle(Style.componentCellStyle); if (j == 1) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } if (j == 2) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } if (j == 3) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } } } }
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 av a 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:output.ExcelM3Upgrad.java
private void writeMigration() { Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Migration"); sheet.setDisplayGridlines(false);//ww w.j a v a2 s . c o m sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); styles = createStyles(workbook); int rownum = beginROW; int cellnum = beginCOL; Row row = sheet.createRow(rownum++); for (int k = 0; k < model.getListColumn().length; k++) { Cell cell = row.createCell(cellnum++); cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng())); cell.setCellStyle(styles.get("header")); sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden()); sheet.autoSizeColumn(k); dialStatus(); } ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); } String[] listLevel = i18n.Language.traduce(Ressource.listLevel) .toArray(new String[Ressource.listLevel.length]); data = model.getData(); for (int i = 0; i < data.length; i++) { busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length); row = sheet.createRow(rownum++); Object[] objArr = data[i]; cellnum = beginCOL; boolean first = true; int j = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { if (first) { first = false; if ((Boolean) obj) { cell.setCellValue("Oui"); } else { cell.setCellValue("Non"); } } else { if ((Boolean) obj) { cell.setCellValue("OK"); } else { cell.setCellValue("KO"); } } } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } if (listHeader.indexOf(218) == j) { try { int n = Integer.parseInt(obj.toString().trim()); if (n == -1) { cell.setCellValue("ERROR"); } else { cell.setCellValue(listLevel[n]); } } catch (NumberFormatException ex) { cell.setCellValue(""); } } if (j < objArr.length - 3) { cell.setCellStyle(styles.get("cell_b_centered_locked")); } else { cell.setCellStyle(styles.get("cell_b_centered")); } j++; dialStatus(); } dialStatus(); } dialStatus(); busyDial.setText("Formatage du document"); CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 1, beginCOL + data[0].length - 1); DataValidationConstraint userConstraint; DataValidation userValidation; if (type == 0) { userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel() .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = new HSSFDataValidation(userList, userConstraint); } else { XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); userConstraint = (XSSFDataValidationConstraint) userHelper .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect() .toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList); } sheet.addValidationData(userValidation); CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 2, beginCOL + data[0].length - 2); DataValidationConstraint migConstraint; DataValidation migValidation; if (type == 0) { migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = new HSSFDataValidation(migList, migConstraint); } else { XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); migConstraint = (XSSFDataValidationConstraint) migHelper .createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList); } sheet.addValidationData(migValidation); CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 3, beginCOL + data[0].length - 3); DataValidationConstraint levelConstraint; DataValidation levelValidation; ArrayList<String> listNameLevel = new ArrayList<>(); listNameLevel.add("ERROR"); listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length]) if (type == 0) { levelConstraint = DVConstraint .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()])); levelValidation = new HSSFDataValidation(levelList, levelConstraint); } else { XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint( i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length])); levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList); } sheet.addValidationData(levelValidation); int irow = beginROW; int icol = beginCOL + model.getListColumn().length + 2; row = sheet.getRow(irow); Cell cell = row.createCell(icol); sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1)); cell.setCellValue("Estimation de la charge"); cell.setCellStyle(styles.get("header")); irow++; row = sheet.getRow(irow); int cpt = 0; ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel); for (String s : listStringLevel) { cell = row.createCell(icol); cell.setCellValue(s); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL); cell.setCellFormula( "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]); cell.setCellStyle(styles.get("cell_b_centered_locked")); irow++; row = sheet.getRow(irow); cpt++; } row = sheet.getRow(irow); cell = row.createCell(icol); cell.setCellValue("Total des charges"); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1)); cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")"); cell.setCellStyle(styles.get("cell_b_centered_locked")); for (int k = 0; k < model.getListColumn().length + 3; k++) { sheet.autoSizeColumn(k); } sheet.protectSheet("3kles2014"); }