List of usage examples for org.apache.poi.ss.usermodel Name getNameName
String getNameName();
From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java
License:Apache License
/** * For given {@link Workbook} does convert everything to new {@link DataModel} structure. * Does copy all supported fields (for supported fields see {@link DataModel} class. *///from w ww . j a v a2s .co m static IDataModel toDataModel(final Workbook workbook) { if (workbook == null) { return null; } //add custom functions information workbook.addToolPack(getUdfFinder()); Sheet s = workbook.getSheetAt(0); //TODO: only one sheet is supported if (s == null) { return null; } IDataModel dm = new DataModel(s.getSheetName()); for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) { Row r = s.getRow(i); if (r == null) { continue; } DmRow row = new DmRow(i); dm.setRow(i, row); for (int j = r.getFirstCellNum(); j < r.getLastCellNum(); j++) { Cell c = r.getCell(j); if (c == null) { continue; } DmCell cell = new DmCell(); row.setCell(j, cell); cell.setAddress(new CellAddress(dm.getDataModelId(), A1Address.fromRowColumn(i, j))); cell.setContent(ConverterUtils.resolveCellValue(c)); } } EvaluationWorkbook evaluationWbook = ConverterUtils.newEvaluationWorkbook(workbook); for (int nIdx = 0; nIdx < workbook.getNumberOfNames(); nIdx++) { Name name = workbook.getNameAt(nIdx); String reference = name.getRefersToFormula(); if (reference == null) { continue; } if (A1Address.isAddress(removeSheetFromNameRef(reference))) { dm.setNamedAddress(name.getNameName(), A1Address.fromA1Address(removeSheetFromNameRef(reference))); } else if (isFormula(reference, evaluationWbook)) { dm.setNamedValue(name.getNameName(), new CellValue(FORMULA_PREFIX + reference)); } else { dm.setNamedValue(name.getNameName(), CellValue.from(reference)); } } return dm; }
From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java
License:Apache License
private Map<Integer, PoiProxyName> makeNames(Workbook wb, EvaluationWorkbook ewb) { Map<Integer, PoiProxyName> names = new HashMap<>(); for (int nIdx = 0; nIdx < wb.getNumberOfNames(); nIdx++) { Name wbName = wb.getNameAt(nIdx); Ptg[] ptgs;/*from www . j a va2 s .co m*/ String refersToFormula; if (!wbName.isFunctionName() && wbName.getRefersToFormula() != null) { //NOPMD refersToFormula = wbName.getRefersToFormula(); ptgs = FormulaParser.parse(refersToFormula, (FormulaParsingWorkbook) ewb, FormulaType.NAMEDRANGE, 0 /*TODO: sheet index*/); } else { ptgs = null; refersToFormula = null; } names.put(nIdx, new PoiProxyName(wbName.getNameName(), wbName.isFunctionName(), refersToFormula != null, ptgs, wbName.isFunctionName(), nIdx)); } return names; }
From source file:com.dataart.spreadsheetanalytics.engine.SpreadsheetAuditor.java
License:Apache License
public static Map<String, String> getWorkbookNames(Workbook workbook) { Map<String, String> result = new HashMap<>(workbook.getNumberOfNames()); for (int i = 0; i < workbook.getNumberOfNames(); i++) { Name name = workbook.getNameAt(i); result.put(name.getNameName(), name.getRefersToFormula()); }// ww w . j a v a2s. c o m return result; }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ??/*from w w w . j a v a2 s . c o m*/ * @param config * @param sheet * @param columnIndex * @param valueSet */ protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex, Set<String> valueSet) { Workbook workbook = sheet.getWorkbook(); Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME); if (codeSheet == null) { log.debug("?Sheet?Sheet"); codeSheet = workbook.createSheet(DICCODE_SHEET_NAME); } int codeIndex = config.columnName() - 'A'; log.debug("codeIndex{}", codeIndex); if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) { log.debug("????"); int i = 0; for (String dic : valueSet) { Row row = codeSheet.getRow(i); if (row == null) row = codeSheet.createRow(i); Cell cell = row.createCell(codeIndex); cell.setCellValue(dic); i++; } } else { log.debug("????"); } Name name = workbook.getName(config.columnName() + ""); if (name == null || name.isDeleted()) { log.debug("?Name?Name"); name = workbook.createName(); name.setNameName(config.columnName() + ""); } name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$" + valueSet.size()); DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName()); CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN); setValidationTip(validation, config); sheet.addValidationData(validation); log.debug("??"); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public String[] getDefinedNames(boolean validOnly) { int count = workbook.getNumberOfNames(); // String[] nameNames = new String[count]; ArrayList<String> nameNames = new ArrayList<String>(); for (int i = 0; i < count; i++) { Name namedRegion = workbook.getNameAt(i); // if valid only, check corresponding reference formula validity if (validOnly && !isValidNamedRegion(namedRegion)) continue; nameNames.add(namedRegion.getNameName()); }// ww w .java2 s.c om return nameNames.toArray(new String[nameNames.size()]); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
private void checkName(Name name) { if (!isValidReference(name.getRefersToFormula())) throw new IllegalArgumentException("Name '" + name.getNameName() + "' has invalid reference!"); else if (!existsSheet(name.getSheetName())) { // The reference as such is valid but it doesn't point to a (existing) sheet ... throw new IllegalArgumentException( "Name '" + name.getNameName() + "' does not refer to a valid sheet!"); }/*from w w w .j a v a2 s . c om*/ }
From source file:de.jlo.talendcomp.excel.SpreadsheetNamedCellInput.java
License:Apache License
public boolean readNextNamedCell() { if (workbook == null) { throw new IllegalStateException("workbook is not initialized"); }//from w ww. j av a 2s . c o m if (namedCellCount == 0) { return false; } else { if (currentNamedCellIndex < namedCellCount) { Name name = workbook.getNameAt(currentNamedCellIndex); cellName = name.getNameName(); currentNamedCell = getNamedCell(name); currentNamedCellIndex++; return true; } else { currentNamedCellIndex++; return false; } } }
From source file:org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java
License:Open Source License
private void loadNames(Workbook _xlsWorkbook, BaseSpreadsheet _spreadsheet) { final int numberOfNames = _xlsWorkbook.getNumberOfNames(); for (int nameIndex = 0; nameIndex < numberOfNames; nameIndex++) { final Name name = _xlsWorkbook.getNameAt(nameIndex); if (name.isFunctionName()) continue; final String cellRangeAddress = name.getRefersToFormula(); final String rangeName = name.getNameName(); final ExpressionParser parser = new SpreadsheetExpressionParserA1OOXML(cellRangeAddress, _spreadsheet); try {/*from w w w .j av a2 s . com*/ final CellRange cellRange = (CellRange) parser.rangeOrCellRefA1(); _spreadsheet.defineModelRangeName(rangeName, cellRange); } catch (ParseException e) { // Ignore all non 'named range' names } } }
From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;//from w ww .j a v a2s . co m //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }
From source file:org.jreserve.gui.excel.template.dataimport.createwizard.ExcelNameReader.java
License:Open Source License
@Override public void run() { synchronized (lock) { try {// w ww . j a va 2s. c o m names = new ArrayList<String>(); Workbook wb = WorkbookFactory.create(file); for (Name name : ExcelUtil.getReferenceNames(wb)) names.add(name.getNameName()); } catch (Exception ex2) { this.ex = ex2; } } }