1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
|
/* ====================================================================
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.xssf.usermodel;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName;
/**
* Represents a defined named range in a SpreadsheetML workbook.
* <p>
* Defined names are descriptive text that is used to represents a cell, range of cells, formula, or constant value.
* Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as {@code Sales!C20:C30}.
* </p>
* Example:
* <pre>{@code
* XSSFWorkbook wb = new XSSFWorkbook();
* XSSFSheet sh = wb.createSheet("Sheet1");
*
* //applies to the entire workbook
* XSSFName name1 = wb.createName();
* name1.setNameName("FMLA");
* name1.setRefersToFormula("Sheet1!$B$3");
*
* //applies to Sheet1
* XSSFName name2 = wb.createName();
* name2.setNameName("SheetLevelName");
* name2.setComment("This name is scoped to Sheet1");
* name2.setLocalSheetId(0);
* name2.setRefersToFormula("Sheet1!$B$3");
*
* }</pre>
*/
public final class XSSFName implements Name {
/**
* A built-in defined name that specifies the workbook's print area
*/
public static final String BUILTIN_PRINT_AREA = "_xlnm.Print_Area";
/**
* A built-in defined name that specifies the row(s) or column(s) to repeat
* at the top of each printed page.
*/
public static final String BUILTIN_PRINT_TITLE = "_xlnm.Print_Titles";
/**
* A built-in defined name that refers to a range containing the criteria values
* to be used in applying an advanced filter to a range of data
*/
public static final String BUILTIN_CRITERIA = "_xlnm.Criteria:";
/**
* this defined name refers to the range containing the filtered
* output values resulting from applying an advanced filter criteria to a source
* range
*/
public static final String BUILTIN_EXTRACT = "_xlnm.Extract:";
/**
* Can be one of the following
* <ul>
* <li> this defined name refers to a range to which an advanced filter has been
* applied. This represents the source data range, unfiltered.
* <li> This defined name refers to a range to which an AutoFilter has been
* applied
* </ul>
*/
public static final String BUILTIN_FILTER_DB = "_xlnm._FilterDatabase";
/**
* A built-in defined name that refers to a consolidation area
*/
public static final String BUILTIN_CONSOLIDATE_AREA = "_xlnm.Consolidate_Area";
/**
* A built-in defined name that specified that the range specified is from a database data source
*/
public static final String BUILTIN_DATABASE = "_xlnm.Database";
/**
* A built-in defined name that refers to a sheet title.
*/
public static final String BUILTIN_SHEET_TITLE = "_xlnm.Sheet_Title";
private final XSSFWorkbook _workbook;
private final CTDefinedName _ctName;
/**
* Creates an XSSFName object - called internally by XSSFWorkbook.
*
* @param name - the xml bean that holds data represenring this defined name.
* @param workbook - the workbook object associated with the name
* @see org.apache.poi.xssf.usermodel.XSSFWorkbook#createName()
*/
protected XSSFName(CTDefinedName name, XSSFWorkbook workbook) {
_workbook = workbook;
_ctName = name;
}
/**
* Returns the underlying named range object
*/
protected CTDefinedName getCTName() {
return _ctName;
}
/**
* Returns the name that will appear in the user interface for the defined name.
*
* @return text name of this defined name
*/
@Override
public String getNameName() {
return _ctName.getName();
}
/**
* Sets the name that will appear in the user interface for the defined name.
* Names must begin with a letter or underscore, not contain spaces and be unique across the workbook.
*
* <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>{@code
* //by default names are workbook-global
* XSSFName 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)"
*
* }</pre>
*
* @param name name of this defined name
* @throws IllegalArgumentException if the name is invalid or the workbook already contains this name (case-insensitive)
*/
@Override
public void setNameName(String name) {
validateName(name);
String oldName = getNameName();
int sheetIndex = getSheetIndex();
//Check to ensure no other names have the same case-insensitive name at the same scope
for (XSSFName foundName : _workbook.getNames(name)) {
if (foundName.getSheetIndex() == sheetIndex && foundName != this) {
String msg = "The "+(sheetIndex == -1 ? "workbook" : "sheet")+" already contains this name: " + name;
throw new IllegalArgumentException(msg);
}
}
_ctName.setName(name);
//Need to update the name -> named ranges map
_workbook.updateName(this, oldName);
}
@Override
public String getRefersToFormula() {
String result = _ctName.getStringValue();
if (result == null || result.length() < 1) {
return null;
}
return result;
}
@Override
public void setRefersToFormula(String formulaText) {
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(_workbook);
//validate through the FormulaParser
FormulaParser.parse(formulaText, fpb, FormulaType.NAMEDRANGE, getSheetIndex(), -1);
_ctName.setStringValue(formulaText);
}
@Override
public boolean isDeleted(){
String formulaText = getRefersToFormula();
if (formulaText == null) {
return false;
}
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(_workbook);
Ptg[] ptgs = FormulaParser.parse(formulaText, fpb, FormulaType.NAMEDRANGE, getSheetIndex(), -1);
return Ptg.doesFormulaReferToDeletedCell(ptgs);
}
/**
* Tell Excel that this name applies to the worksheet with the specified index instead of the entire workbook.
*
* @param index the sheet index this name applies to, -1 unsets this property making the name workbook-global
*/
@Override
public void setSheetIndex(int index) {
int lastSheetIx = _workbook.getNumberOfSheets() - 1;
if (index < -1 || index > lastSheetIx) {
throw new IllegalArgumentException("Sheet index (" + index +") is out of range" +
(lastSheetIx == -1 ? "" : (" (0.." + lastSheetIx + ")")));
}
if(index == -1) {
if(_ctName.isSetLocalSheetId()) _ctName.unsetLocalSheetId();
} else {
_ctName.setLocalSheetId(index);
}
}
/**
* 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
*/
@Override
public int getSheetIndex() {
return _ctName.isSetLocalSheetId() ? (int) _ctName.getLocalSheetId() : -1;
}
/**
* 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} indicates the name refers to a function.
*/
@Override
public void setFunction(boolean value) {
_ctName.setFunction(value);
}
/**
* 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.
*
* @return {@code true} indicates the name refers to a function.
*/
public boolean getFunction() {
return _ctName.getFunction();
}
/**
* Specifies the function group index if the defined name refers to a function. The function
* group defines the general category for the function. This attribute is used when there is
* an add-in or other code project associated with the file.
*
* @param functionGroupId the function group index that defines the general category for the function
*/
public void setFunctionGroupId(int functionGroupId) {
_ctName.setFunctionGroupId(functionGroupId);
}
/**
* Returns the function group index if the defined name refers to a function. The function
* group defines the general category for the function. This attribute is used when there is
* an add-in or other code project associated with the file.
*
* @return the function group index that defines the general category for the function
*/
public int getFunctionGroupId() {
return (int) _ctName.getFunctionGroupId();
}
/**
* Get the sheets name which this named range is referenced to
*
* @return sheet name, which this named range referred to.
* Empty string if the referenced sheet name was not found.
*/
@Override
public String getSheetName() {
if (_ctName.isSetLocalSheetId()) {
// Given as explicit sheet id
int sheetId = (int)_ctName.getLocalSheetId();
return _workbook.getSheetName(sheetId);
}
String ref = getRefersToFormula();
AreaReference areaRef = new AreaReference(ref, SpreadsheetVersion.EXCEL2007);
return areaRef.getFirstCell().getSheetName();
}
/**
* Is the name refers to a user-defined function ?
*
* @return {@code true} if this name refers to a user-defined function
*/
@Override
public boolean isFunctionName() {
return getFunction();
}
/**
* Checks if this name is hidden, eg one of the built-in Excel
* internal names
*
* @return true if this name is a hidden one
*/
@Override
public boolean isHidden() {
return _ctName.getHidden();
}
/**
* Returns the comment the user provided when the name was created.
*
* @return the user comment for this named range
*/
@Override
public String getComment() {
return _ctName.getComment();
}
/**
* Specifies the comment the user provided when the name was created.
*
* @param comment the user comment for this named range
*/
@Override
public void setComment(String comment) {
_ctName.setComment(comment);
}
@Override
public int hashCode() {
return _ctName.toString().hashCode();
}
/**
* Compares this name to the specified object.
* The result is {@code true} if the argument is XSSFName and the
* underlying CTDefinedName bean equals to the CTDefinedName representing this name
*
* @param o the object to compare this {@code XSSFName} against.
* @return {@code true} if the {@code XSSFName }are equal;
* {@code false} otherwise.
*/
@Override
public boolean equals(Object o) {
if(o == this) return true;
if (!(o instanceof XSSFName)) return false;
XSSFName cf = (XSSFName) o;
return _ctName.toString().equals(cf.getCTName().toString());
}
/**
* 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.isEmpty()) {
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]", "");
try {
if (CellReference.cellReferenceIsWithinRange(col, row, SpreadsheetVersion.EXCEL2007)) {
throw new IllegalArgumentException("Invalid name: '"+name+"': cannot be $A$1-style cell reference");
}
} catch (final NumberFormatException e) {
// row was not parseable as an Integer, such as a BigInt
// therefore name passes the not-a-cell-reference criteria
}
}
// 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");
}
}
}
|