Java tutorial
/* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ package org.apache.poi.hssf.usermodel; import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.model.InternalWorkbook; import org.apache.poi.hssf.record.NameCommentRecord; import org.apache.poi.hssf.record.NameRecord; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.util.CellReference; /** * High Level Representation of a 'defined name' which could be a 'built-in' name, * 'named range' or name of a user defined function. */ public final class HSSFName implements Name { private HSSFWorkbook _book; private NameRecord _definedNameRec; private NameCommentRecord _commentRec; /** * Creates new HSSFName - called by HSSFWorkbook to create a name from * scratch. * * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createName() * @param name the Name Record * @param book workbook object associated with the sheet. */ /* package */ HSSFName(HSSFWorkbook book, NameRecord name) { this(book, name, null); } /** * Creates new HSSFName - called by HSSFWorkbook to create a name from * scratch. * * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createName() * @param name the Name Record * @param comment the Name Comment Record, optional. * @param book workbook object associated with the sheet. */ /* package */ HSSFName(final HSSFWorkbook book, final NameRecord name, final NameCommentRecord comment) { _book = book; _definedNameRec = name; _commentRec = comment; } /** Get the sheets name which this named range is referenced to * @return sheet name, which this named range referred to */ public String getSheetName() { int indexToExternSheet = _definedNameRec.getExternSheetNumber(); return _book.getWorkbook().findSheetFirstNameFromExternSheet(indexToExternSheet); } /** * @return text name of this defined name */ public String getNameName() { return _definedNameRec.getNameText(); } /** * Sets the name of the named range * * <p>The following is a list of syntax rules that you need to be aware of when you create and edit names.</p> * <ul> * <li><strong>Valid characters</strong> * The first character of a name must be a letter, an underscore character (_), or a backslash (\). * Remaining characters in the name can be letters, numbers, periods, and underscore characters. * </li> * <li><strong>Cell references disallowed</strong> * Names cannot be the same as a cell reference, such as Z$100 or R1C1.</li> * <li><strong>Spaces are not valid</strong> * Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter. * </li> * <li><strong>Name length</strong> * A name can contain up to 255 characters. * </li> * <li><strong>Case sensitivity</strong> * Names can contain uppercase and lowercase letters. * </li> * </ul> * * <p> * A name must always be unique within its scope. POI prevents you from defining a name that is not unique * within its scope. However you can use the same name in different scopes. Example: * <pre><blockquote> * //by default names are workbook-global * HSSFName name; * name = workbook.createName(); * name.setNameName("sales_08"); * * name = workbook.createName(); * name.setNameName("sales_08"); //will throw an exception: "The workbook already contains this name (case-insensitive)" * * //create sheet-level name * name = workbook.createName(); * name.setSheetIndex(0); //the scope of the name is the first sheet * name.setNameName("sales_08"); //ok * * name = workbook.createName(); * name.setSheetIndex(0); * name.setNameName("sales_08"); //will throw an exception: "The sheet already contains this name (case-insensitive)" * * </blockquote></pre> * </p> * * @param nameName named range name to set * @throws IllegalArgumentException if the name is invalid or the name already exists (case-insensitive) */ public void setNameName(String nameName) { validateName(nameName); InternalWorkbook wb = _book.getWorkbook(); _definedNameRec.setNameText(nameName); int sheetNumber = _definedNameRec.getSheetNumber(); //Check to ensure no other names have the same case-insensitive name final int lastNameIndex = wb.getNumNames() - 1; for (int i = lastNameIndex; i >= 0; i--) { NameRecord rec = wb.getNameRecord(i); if (rec != _definedNameRec) { if (rec.getNameText().equalsIgnoreCase(nameName) && sheetNumber == rec.getSheetNumber()) { String msg = "The " + (sheetNumber == 0 ? "workbook" : "sheet") + " already contains this name: " + nameName; _definedNameRec.setNameText(nameName + "(2)"); throw new IllegalArgumentException(msg); } } } // Update our comment, if there is one if (_commentRec != null) { _commentRec.setNameText(nameName); _book.getWorkbook().updateNameCommentRecordCache(_commentRec); } } /** * https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4D0F13AC-53B7-422E-AFD2-ABD7FF379C64#bmsyntax_rules_for_names * * Valid characters: * First character: { letter | underscore | backslash } * Remaining characters: { letter | number | period | underscore } * * Cell shorthand: cannot be { "C" | "c" | "R" | "r" } * * Cell references disallowed: cannot be a cell reference $A$1 or R1C1 * * Spaces are not valid (follows from valid characters above) * * Name length: (XSSF-specific?) 255 characters maximum * * Case sensitivity: all names are case-insensitive * * Uniqueness: must be unique (for names with the same scope) */ private static void validateName(String name) { if (name.length() == 0) { throw new IllegalArgumentException("Name cannot be blank"); } if (name.length() > 255) { throw new IllegalArgumentException( "Invalid name: '" + name + "': cannot exceed 255 characters in length"); } if (name.equalsIgnoreCase("R") || name.equalsIgnoreCase("C")) { throw new IllegalArgumentException("Invalid name: '" + name + "': cannot be special shorthand R or C"); } // is first character valid? char c = name.charAt(0); String allowedSymbols = "_\\"; boolean characterIsValid = (Character.isLetter(c) || allowedSymbols.indexOf(c) != -1); if (!characterIsValid) { throw new IllegalArgumentException( "Invalid name: '" + name + "': first character must be underscore or a letter"); } // are all other characters valid? allowedSymbols = "_.\\"; //backslashes needed for unicode escape for (final char ch : name.toCharArray()) { characterIsValid = (Character.isLetterOrDigit(ch) || allowedSymbols.indexOf(ch) != -1); if (!characterIsValid) { throw new IllegalArgumentException( "Invalid name: '" + name + "': name must be letter, digit, period, or underscore"); } } // Is the name a valid $A$1 cell reference // Because $, :, and ! are disallowed characters, A1-style references become just a letter-number combination if (name.matches("[A-Za-z]+\\d+")) { String col = name.replaceAll("\\d", ""); String row = name.replaceAll("[A-Za-z]", ""); if (CellReference.cellReferenceIsWithinRange(col, row, SpreadsheetVersion.EXCEL97)) { throw new IllegalArgumentException( "Invalid name: '" + name + "': cannot be $A$1-style cell reference"); } } // Is the name a valid R1C1 cell reference? if (name.matches("[Rr]\\d+[Cc]\\d+")) { throw new IllegalArgumentException("Invalid name: '" + name + "': cannot be R1C1-style cell reference"); } } public void setRefersToFormula(String formulaText) { Ptg[] ptgs = HSSFFormulaParser.parse(formulaText, _book, FormulaType.NAMEDRANGE, getSheetIndex()); _definedNameRec.setNameDefinition(ptgs); } public String getRefersToFormula() { if (_definedNameRec.isFunctionName()) { throw new IllegalStateException("Only applicable to named ranges"); } Ptg[] ptgs = _definedNameRec.getNameDefinition(); if (ptgs.length < 1) { // 'refersToFormula' has not been set yet return null; } return HSSFFormulaParser.toFormulaString(_book, ptgs); } /** * Sets the NameParsedFormula structure that specifies the formula for the * defined name. * * @param ptgs the sequence of {@link Ptg}s for the formula. */ void setNameDefinition(Ptg[] ptgs) { _definedNameRec.setNameDefinition(ptgs); } public boolean isDeleted() { Ptg[] ptgs = _definedNameRec.getNameDefinition(); return Ptg.doesFormulaReferToDeletedCell(ptgs); } /** * Checks if this name is a function name * * @return true if this name is a function name */ public boolean isFunctionName() { return _definedNameRec.isFunctionName(); } public String toString() { return getClass().getName() + " [" + _definedNameRec.getNameText() + "]"; } /** * Specifies if the defined name is a local name, and if so, which sheet it is on. * * @param index if greater than 0, the defined name is a local name and the value MUST be a 0-based index * to the collection of sheets as they appear in the workbook. * @throws IllegalArgumentException if the sheet index is invalid. */ public void setSheetIndex(int index) { int lastSheetIx = _book.getNumberOfSheets() - 1; if (index < -1 || index > lastSheetIx) { throw new IllegalArgumentException("Sheet index (" + index + ") is out of range" + (lastSheetIx == -1 ? "" : (" (0.." + lastSheetIx + ")"))); } _definedNameRec.setSheetNumber(index + 1); } /** * Returns the sheet index this name applies to. * * @return the sheet index this name applies to, -1 if this name applies to the entire workbook */ public int getSheetIndex() { return _definedNameRec.getSheetNumber() - 1; } /** * Returns the comment the user provided when the name was created. * * @return the user comment for this named range */ public String getComment() { if (_commentRec != null) { // Prefer the comment record if it has text in it if (_commentRec.getCommentText() != null && _commentRec.getCommentText().length() > 0) { return _commentRec.getCommentText(); } } return _definedNameRec.getDescriptionText(); } /** * Sets the comment the user provided when the name was created. * * @param comment the user comment for this named range */ public void setComment(String comment) { // Update the main record _definedNameRec.setDescriptionText(comment); // If we have a comment record too, update that as well if (_commentRec != null) { _commentRec.setCommentText(comment); } } /** * Indicates that the defined name refers to a user-defined function. * This attribute is used when there is an add-in or other code project associated with the file. * * @param value <code>true</code> indicates the name refers to a function. */ public void setFunction(boolean value) { _definedNameRec.setFunction(value); } }