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
|
# frozen_string_literal: true
# <definedNames>
# <definedName name="_xlnm.Print_Titles" localSheetId="0">Sheet1!$1:$1</definedName>
# </definedNames>
# <xsd:complexType name="CT_DefinedName">
# <xsd:simpleContent>
#  <xsd:extension base="ST_Formula">
# <xsd:attribute name="name" type="s:ST_Xstring" use="required"/>
# <xsd:attribute name="comment" type="s:ST_Xstring" use="optional"/>
# <xsd:attribute name="customMenu" type="s:ST_Xstring" use="optional"/>
# <xsd:attribute name="description" type="s:ST_Xstring" use="optional"/>
# <xsd:attribute name="help" type="s:ST_Xstring" use="optional"/>
# <xsd:attribute name="statusBar" type="s:ST_Xstring" use="optional"/>
# <xsd:attribute name="localSheetId" type="xsd:unsignedInt" use="optional"/>
# <xsd:attribute name="hidden" type="xsd:boolean" use="optional" default="false"/>
# <xsd:attribute name="function" type="xsd:boolean" use="optional" default="false"/>
# <xsd:attribute name="vbProcedure" type="xsd:boolean" use="optional" default="false"/>
# <xsd:attribute name="xlm" type="xsd:boolean" use="optional" default="false"/>
# <xsd:attribute name="functionGroupId" type="xsd:unsignedInt" use="optional"/>
# <xsd:attribute name="shortcutKey" type="s:ST_Xstring" use="optional"/>
# <xsd:attribute name="publishToServer" type="xsd:boolean" use="optional" default="false"/>
# <xsd:attribute name="workbookParameter" type="xsd:boolean" use="optional" default="false"/>
# </xsd:extenstion>
# </xsd:simpleContent>
module Axlsx
# This element defines the defined names that are defined within this workbook.
# 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 Sales!C20:C30.
# A defined name in a formula can make it easier to understand the purpose of the formula.
# @example
# The formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(C20:C30
#
# Names are available to any sheet.
# @example
# If the name ProjectedSales refers to the range A20:A30 on the first worksheet in a workbook,
# you can use the name ProjectedSales on any other sheet in the same workbook to refer to range A20:A30 on the first worksheet.
# Names can also be used to represent formulas or values that do not change (constants).
#
# @example
# The name SalesTax can be used to represent the sales tax amount (such as 6.2 percent) applied to sales transactions.
# You can also link to a defined name in another workbook, or define a name that refers to cells in another workbook.
#
# @example
# The formula =SUM(Sales.xls!ProjectedSales) refers to the named range ProjectedSales in the workbook named Sales.
# A compliant producer or consumer considers a defined name in the range A1-XFD1048576 to be an error.
# All other names outside this range can be defined as names and overrides a cell reference if an ambiguity exists.
#
# @example
# For clarification: LOG10 is always a cell reference, LOG10() is always formula, LOGO1000 can be a defined name that overrides a cell reference.
class DefinedName
include Axlsx::SerializedAttributes
include Axlsx::OptionsParser
include Axlsx::Accessors
# creates a new DefinedName.
# @param [String] formula - the formula the defined name references
# @param [Hash] options - A hash of key/value pairs that will be mapped to this instances attributes.
#
# @option [String] name - Specifies the name that appears in the user interface for the defined name.
# This attribute is required.
# The following built-in names are defined in this SpreadsheetML specification:
# Print
# _xlnm.Print_Area: this defined name specifies the workbook's print area.
# _xlnm.Print_Titles: this defined name specifies the row(s) or column(s) to repeat
# the top of each printed page.
# Filter & Advanced Filter
# _xlnm.Criteria: this defined name refers to a range containing the criteria values
# to be used in applying an advanced filter to a range of data.
# _xlnm._FilterDatabase: can be one of the following
# a. this defined name refers to a range to which an advanced filter has been
# applied. This represents the source data range, unfiltered.
# b. This defined name refers to a range to which an AutoFilter has been
# applied.
# _xlnm.Extract: this defined name refers to the range containing the filtered output
# values resulting from applying an advanced filter criteria to a source range.
# Miscellaneous
# _xlnm.Consolidate_Area: the defined name refers to a consolidation area.
# _xlnm.Database: the range specified in the defined name is from a database data source.
# _xlnm.Sheet_Title: the defined name refers to a sheet title.
# @option [String] comment - A comment to optionally associate with the name
# @option [String] custom_menu - The menu text for the defined name
# @option [String] description - An optional description for the defined name
# @option [String] help - The help topic to display for this defined name
# @option [String] status_bar - The text to display on the application status bar when this defined name has focus
# @option [String] local_sheet_id - Specifies the sheet index in this workbook where data from an external reference is displayed
# @option [Boolean] hidden - Specifies a boolean value that indicates whether the defined name is hidden in the user interface.
# @option [Boolean] function - Specifies a boolean value that 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.
# @option [Boolean] vb_proceedure - Specifies a boolean value that indicates whether the defined name is related to an external function, command, or other executable code.
# @option [Boolean] xlm - Specifies a boolean value that indicates whether the defined name is related to an external function, command, or other executable code.
# @option [Integer] function_group_id - 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.
# See Open Office XML Part 1 for more info.
# @option [String] short_cut_key - Specifies the keyboard shortcut for the defined name.
# @option [Boolean] publish_to_server - Specifies a boolean value that indicates whether the defined name is included in the
# version of the workbook that is published to or rendered on a Web or application server.
# @option [Boolean] workbook_parameter - Specifies a boolean value that indicates that the name is used as a workbook parameter on a
# version of the workbook that is published to or rendered on a Web or application server.
def initialize(formula, options = {})
@formula = formula
parse_options options
end
attr_reader :local_sheet_id
# The local sheet index (0-based)
# @param [Integer] value the unsigned integer index of the sheet this defined_name applies to.
def local_sheet_id=(value)
Axlsx.validate_unsigned_int(value)
@local_sheet_id = value
end
string_attr_accessor :short_cut_key, :status_bar, :help, :description, :custom_menu, :comment, :name, :formula
boolean_attr_accessor :workbook_parameter, :publish_to_server, :xlm, :vb_proceedure, :function, :hidden
serializable_attributes :short_cut_key, :status_bar, :help, :description, :custom_menu, :comment,
:workbook_parameter, :publish_to_server, :xlm, :vb_proceedure, :function, :hidden, :local_sheet_id
def to_xml_string(str = +'')
raise ArgumentError, 'you must specify the name for this defined name. Please read the documentation for Axlsx::DefinedName for more details' unless name
str << '<definedName ' << 'name="' << name << '" '
serialized_attributes str
str << '>' << @formula << '</definedName>'
end
end
end
|
