Module: Axlsx

Defined in:
lib/axlsx.rb,
lib/axlsx/version.rb,
lib/axlsx/package.rb,
lib/axlsx/drawing/pic.rb,
lib/axlsx/util/parser.rb,
lib/axlsx/drawing/axes.rb,
lib/axlsx/util/storage.rb,
lib/axlsx/drawing/axis.rb,
lib/axlsx/doc_props/app.rb,
lib/axlsx/stylesheet/xf.rb,
lib/axlsx/drawing/title.rb,
lib/axlsx/drawing/chart.rb,
lib/axlsx/util/accessors.rb,
lib/axlsx/doc_props/core.rb,
lib/axlsx/drawing/series.rb,
lib/axlsx/stylesheet/dxf.rb,
lib/axlsx/util/constants.rb,
lib/axlsx/drawing/d_lbls.rb,
lib/axlsx/drawing/marker.rb,
lib/axlsx/util/validators.rb,
lib/axlsx/stylesheet/font.rb,
lib/axlsx/drawing/drawing.rb,
lib/axlsx/drawing/scaling.rb,
lib/axlsx/drawing/view_3D.rb,
lib/axlsx/drawing/str_val.rb,
lib/axlsx/drawing/num_val.rb,
lib/axlsx/stylesheet/fill.rb,
lib/axlsx/drawing/str_data.rb,
lib/axlsx/drawing/num_data.rb,
lib/axlsx/drawing/ser_axis.rb,
lib/axlsx/drawing/val_axis.rb,
lib/axlsx/stylesheet/color.rb,
lib/axlsx/drawing/cat_axis.rb,
lib/axlsx/workbook/workbook.rb,
lib/axlsx/stylesheet/border.rb,
lib/axlsx/stylesheet/styles.rb,
lib/axlsx/drawing/vml_shape.rb,
lib/axlsx/drawing/hyperlink.rb,
lib/axlsx/rels/relationship.rb,
lib/axlsx/drawing/line_chart.rb,
lib/axlsx/drawing/bar_series.rb,
lib/axlsx/rels/relationships.rb,
lib/axlsx/stylesheet/num_fmt.rb,
lib/axlsx/drawing/pie_series.rb,
lib/axlsx/drawing/vml_drawing.rb,
lib/axlsx/util/options_parser.rb,
lib/axlsx/drawing/line_series.rb,
lib/axlsx/drawing/bar_3D_chart.rb,
lib/axlsx/drawing/pie_3D_chart.rb,
lib/axlsx/drawing/bubble_chart.rb,
lib/axlsx/stylesheet/border_pr.rb,
lib/axlsx/content_type/default.rb,
lib/axlsx/drawing/series_title.rb,
lib/axlsx/content_type/override.rb,
lib/axlsx/drawing/bubble_series.rb,
lib/axlsx/drawing/line_3D_chart.rb,
lib/axlsx/drawing/scatter_chart.rb,
lib/axlsx/drawing/graphic_frame.rb,
lib/axlsx/stylesheet/cell_style.rb,
lib/axlsx/workbook/defined_name.rb,
lib/axlsx/workbook/workbook_view.rb,
lib/axlsx/stylesheet/table_style.rb,
lib/axlsx/workbook/defined_names.rb,
lib/axlsx/workbook/worksheet/row.rb,
lib/axlsx/util/simple_typed_list.rb,
lib/axlsx/drawing/ax_data_source.rb,
lib/axlsx/workbook/worksheet/col.rb,
lib/axlsx/drawing/scatter_series.rb,
lib/axlsx/stylesheet/table_styles.rb,
lib/axlsx/workbook/worksheet/cols.rb,
lib/axlsx/drawing/one_cell_anchor.rb,
lib/axlsx/drawing/picture_locking.rb,
lib/axlsx/workbook/worksheet/cfvo.rb,
lib/axlsx/drawing/num_data_source.rb,
lib/axlsx/workbook/worksheet/cell.rb,
lib/axlsx/workbook/workbook_views.rb,
lib/axlsx/drawing/two_cell_anchor.rb,
lib/axlsx/stylesheet/pattern_fill.rb,
lib/axlsx/workbook/worksheet/pane.rb,
lib/axlsx/stylesheet/gradient_fill.rb,
lib/axlsx/workbook/worksheet/break.rb,
lib/axlsx/workbook/worksheet/table.rb,
lib/axlsx/workbook/worksheet/cfvos.rb,
lib/axlsx/stylesheet/gradient_stop.rb,
lib/axlsx/stylesheet/cell_alignment.rb,
lib/axlsx/workbook/worksheet/tables.rb,
lib/axlsx/content_type/content_type.rb,
lib/axlsx/util/serialized_attributes.rb,
lib/axlsx/workbook/worksheet/comment.rb,
lib/axlsx/stylesheet/cell_protection.rb,
lib/axlsx/workbook/worksheet/data_bar.rb,
lib/axlsx/workbook/worksheet/comments.rb,
lib/axlsx/workbook/worksheet/sheet_pr.rb,
lib/axlsx/workbook/worksheet/icon_set.rb,
lib/axlsx/workbook/worksheet/worksheet.rb,
lib/axlsx/workbook/worksheet/selection.rb,
lib/axlsx/workbook/worksheet/dimension.rb,
lib/axlsx/workbook/worksheet/rich_text.rb,
lib/axlsx/workbook/worksheet/sheet_data.rb,
lib/axlsx/workbook/worksheet/page_setup.rb,
lib/axlsx/workbook/shared_strings_table.rb,
lib/axlsx/workbook/worksheet/sheet_view.rb,
lib/axlsx/workbook/worksheet/col_breaks.rb,
lib/axlsx/workbook/worksheet/row_breaks.rb,
lib/axlsx/stylesheet/table_style_element.rb,
lib/axlsx/workbook/worksheet/pivot_table.rb,
lib/axlsx/workbook/worksheet/color_scale.rb,
lib/axlsx/workbook/worksheet/merged_cells.rb,
lib/axlsx/workbook/worksheet/page_margins.rb,
lib/axlsx/workbook/worksheet/pivot_tables.rb,
lib/axlsx/workbook/worksheet/header_footer.rb,
lib/axlsx/workbook/worksheet/sheet_calc_pr.rb,
lib/axlsx/workbook/worksheet/rich_text_run.rb,
lib/axlsx/workbook/worksheet/print_options.rb,
lib/axlsx/workbook/worksheet/page_set_up_pr.rb,
lib/axlsx/workbook/worksheet/protected_range.rb,
lib/axlsx/content_type/abstract_content_type.rb,
lib/axlsx/workbook/worksheet/data_validation.rb,
lib/axlsx/workbook/worksheet/sheet_format_pr.rb,
lib/axlsx/workbook/worksheet/cell_serializer.rb,
lib/axlsx/workbook/worksheet/table_style_info.rb,
lib/axlsx/workbook/worksheet/data_validations.rb,
lib/axlsx/workbook/worksheet/protected_ranges.rb,
lib/axlsx/workbook/worksheet/sheet_protection.rb,
lib/axlsx/workbook/worksheet/worksheet_drawing.rb,
lib/axlsx/workbook/worksheet/worksheet_comments.rb,
lib/axlsx/workbook/worksheet/date_time_converter.rb,
lib/axlsx/workbook/worksheet/auto_filter/filters.rb,
lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb,
lib/axlsx/workbook/worksheet/worksheet_hyperlinks.rb,
lib/axlsx/workbook/worksheet/conditional_formatting.rb,
lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb,
lib/axlsx/workbook/worksheet/conditional_formattings.rb,
lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb,
lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb,
lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb

Overview

Defined Under Namespace

Modules: Accessors, OptionsParser, Parser, SerializedAttributes Classes: AbstractContentType, App, AutoFilter, AxDataSource, Axes, Axis, Bar3DChart, BarSeries, Border, BorderPr, Break, BubbleChart, BubbleSeries, CatAxis, Cell, CellAlignment, CellProtection, CellSerializer, CellStyle, Cfvo, Cfvos, Chart, Col, ColBreaks, Color, ColorScale, Cols, Comment, Comments, ConditionalFormatting, ConditionalFormattingRule, ConditionalFormattings, ContentType, Core, DLbls, DataBar, DataTypeValidator, DataValidation, DataValidations, DateTimeConverter, Default, DefinedName, DefinedNames, Dimension, Drawing, Dxf, Fill, FilterColumn, Filters, Font, GradientFill, GradientStop, GraphicFrame, HeaderFooter, Hyperlink, IconSet, Line3DChart, LineChart, LineSeries, Marker, MergedCells, NumData, NumDataSource, NumFmt, NumVal, OneCellAnchor, Override, Package, PageMargins, PageSetUpPr, PageSetup, Pane, PatternFill, Pic, PictureLocking, Pie3DChart, PieSeries, PivotTable, PivotTableCacheDefinition, PivotTables, PrintOptions, ProtectedRange, ProtectedRanges, RangeValidator, RegexValidator, Relationship, Relationships, RestrictionValidator, RichText, RichTextRun, Row, RowBreaks, Scaling, ScatterChart, ScatterSeries, Selection, SerAxis, Series, SeriesTitle, SharedStringsTable, SheetCalcPr, SheetData, SheetFormatPr, SheetPr, SheetProtection, SheetView, Storage, StrData, StrVal, Styles, Table, TableStyle, TableStyleElement, TableStyleInfo, TableStyles, Tables, Title, TwoCellAnchor, ValAxis, View3D, VmlDrawing, VmlShape, Workbook, WorkbookView, WorkbookViews, Worksheet, WorksheetComments, WorksheetDrawing, WorksheetHyperlink, WorksheetHyperlinks, Xf

Constant Summary

VERSION =

The current version

"2.0.1"
ENCODING =

XML Encoding

"UTF-8".freeze
XML_NS =

spreadsheetML namespace

"http://schemas.openxmlformats.org/spreadsheetml/2006/main".freeze
XML_NS_T =

content-types namespace

"http://schemas.openxmlformats.org/package/2006/content-types".freeze
APP_NS =

extended-properties namespace

"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties".freeze
APP_NS_VT =

doc props namespace

"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes".freeze
CORE_NS =

core properties namespace

"http://schemas.openxmlformats.org/package/2006/metadata/core-properties".freeze
CORE_NS_DC =

dc elements (core) namespace

"http://purl.org/dc/elements/1.1/".freeze
CORE_NS_DCMIT =

dcmit (core) namespcace

"http://purl.org/dc/dcmitype/".freeze
CORE_NS_DCT =

dc terms namespace

"http://purl.org/dc/terms/".freeze
CORE_NS_XSI =

xml schema namespace

"http://www.w3.org/2001/XMLSchema-instance".freeze
DIGITAL_SIGNATURE_NS =

Digital signature namespace

"http://schemas.openxmlformats.org/package/2006/digital-signature".freeze
XML_NS_XDR =

spreadsheet drawing namespace

"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing".freeze
XML_NS_A =

drawing namespace

"http://schemas.openxmlformats.org/drawingml/2006/main".freeze
XML_NS_C =

chart namespace

"http://schemas.openxmlformats.org/drawingml/2006/chart".freeze
XML_NS_R =

relationships namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships".freeze
RELS_R =

relationships name space

"http://schemas.openxmlformats.org/package/2006/relationships".freeze
TABLE_R =

table rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/table".freeze
PIVOT_TABLE_R =

pivot table rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable".freeze
PIVOT_TABLE_CACHE_DEFINITION_R =

pivot table cache definition namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition".freeze
WORKBOOK_R =

workbook rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument".freeze
WORKSHEET_R =

worksheet rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet".freeze
APP_R =

app rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties".freeze
CORE_R =

core rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/metadata/core-properties".freeze
DIGITAL_SIGNATURE_R =

digital signature rels namespace

"http://schemas.openxmlformats.org/package/2006/relationships/digital- signature/signature".freeze
STYLES_R =

styles rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles".freeze
SHARED_STRINGS_R =

shared strings namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings".freeze
DRAWING_R =

drawing rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing".freeze
CHART_R =

chart rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart".freeze
IMAGE_R =

image rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/image".freeze
"http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink".freeze
COMMENT_R =

comment rels namespace

"http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments".freeze
COMMENT_R_NULL =

comment relation for nil target

"http://purl.oclc.org/ooxml/officeDocument/relationships/comments".freeze
VML_DRAWING_R =

vml drawing relation namespace

'http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing'
VML_DRAWING_CT =

VML Drawing content type

"application/vnd.openxmlformats-officedocument.vmlDrawing".freeze
TABLE_CT =

table content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml".freeze
PIVOT_TABLE_CT =

pivot table content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml".freeze
PIVOT_TABLE_CACHE_DEFINITION_CT =

pivot table cache definition content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml".freeze
WORKBOOK_CT =

workbook content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml".freeze
APP_CT =

app content type

"application/vnd.openxmlformats-officedocument.extended-properties+xml".freeze
RELS_CT =

rels content type

"application/vnd.openxmlformats-package.relationships+xml".freeze
STYLES_CT =

styles content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml".freeze
XML_CT =

xml content type

"application/xml".freeze
WORKSHEET_CT =

worksheet content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml".freeze
SHARED_STRINGS_CT =

shared strings content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml".freeze
CORE_CT =

core content type

"application/vnd.openxmlformats-package.core-properties+xml".freeze
DIGITAL_SIGNATURE_XML_CT =

digital signature xml content type

"application/vnd.openxmlformats-package.digital-signature-xmlsignature+xml".freeze
DIGITAL_SIGNATURE_ORIGIN_CT =

digital signature origin content type

"application/vnd.openxmlformats-package.digital-signature-origin".freeze
DIGITAL_SIGNATURE_CERTIFICATE_CT =

digital signature certificate content type

"application/vnd.openxmlformats-package.digital-signature-certificate".freeze
CHART_CT =

chart content type

"application/vnd.openxmlformats-officedocument.drawingml.chart+xml".freeze
COMMENT_CT =

comments content type

"application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml".freeze
JPEG_CT =

jpeg content type

"image/jpeg".freeze
GIF_CT =

gif content type

"image/gif".freeze
PNG_CT =

png content type

"image/png".freeze
DRAWING_CT =

drawing content type

"application/vnd.openxmlformats-officedocument.drawing+xml".freeze
XML_EX =

xml content type extensions

"xml".freeze
JPEG_EX =

jpeg extension

"jpeg".freeze
GIF_EX =

gif extension

"gif".freeze
PNG_EX =

png extension

"png".freeze
RELS_EX =

rels content type extension

"rels".freeze
WORKBOOK_PN =

workbook part

"xl/workbook.xml".freeze
STYLES_PN =

styles part

"styles.xml".freeze
SHARED_STRINGS_PN =

shared_strings part

"sharedStrings.xml".freeze
APP_PN =

app part

"docProps/app.xml".freeze
CORE_PN =

core part

"docProps/core.xml".freeze
CONTENT_TYPES_PN =

content types part

"[Content_Types].xml".freeze
RELS_PN =

rels part

"_rels/.rels".freeze
WORKBOOK_RELS_PN =

workbook rels part

"xl/_rels/workbook.xml.rels".freeze
WORKSHEET_PN =

worksheet part

"worksheets/sheet%d.xml".freeze
WORKSHEET_RELS_PN =

worksheet rels part

"worksheets/_rels/sheet%d.xml.rels".freeze
DRAWING_PN =

drawing part

"drawings/drawing%d.xml".freeze
DRAWING_RELS_PN =

drawing rels part

"drawings/_rels/drawing%d.xml.rels".freeze
VML_DRAWING_PN =

vml drawing part

"drawings/vmlDrawing%d.vml".freeze
TABLE_PN =

drawing part

"tables/table%d.xml".freeze
PIVOT_TABLE_PN =

pivot table parts

"pivotTables/pivotTable%d.xml".freeze
PIVOT_TABLE_CACHE_DEFINITION_PN =

pivot table cache definition part name

"pivotCache/pivotCacheDefinition%d.xml".freeze
PIVOT_TABLE_RELS_PN =

pivot table rels parts

"pivotTables/_rels/pivotTable%d.xml.rels".freeze
CHART_PN =

chart part

"charts/chart%d.xml".freeze
IMAGE_PN =

chart part

"media/image%d.%s".freeze
COMMENT_PN =

comment part

"comments%d.xml".freeze
SCHEMA_BASE =

location of schema files for validation

(File.dirname(__FILE__)+'/../../schema/').freeze
APP_XSD =

App validation schema

(SCHEMA_BASE + "shared-documentPropertiesExtended.xsd").freeze
CORE_XSD =

core validation schema

(SCHEMA_BASE + "opc-coreProperties.xsd").freeze
CONTENT_TYPES_XSD =

content types validation schema

(SCHEMA_BASE + "opc-contentTypes.xsd").freeze
RELS_XSD =

rels validation schema

(SCHEMA_BASE + "opc-relationships.xsd").freeze
SML_XSD =

spreadsheetML validation schema

(SCHEMA_BASE + "sml.xsd").freeze
DRAWING_XSD =

drawing validation schema

(SCHEMA_BASE + "dml-spreadsheetDrawing.xsd").freeze
NUM_FMT_PERCENT =

number format id for pecentage formatting using the default formatting id.

9
NUM_FMT_YYYYMMDD =

number format id for date format like 2011/11/13

100
NUM_FMT_YYYYMMDDHHMMSS =

number format id for time format the creates 2011/11/13 12:23:10

101
STYLE_THIN_BORDER =

cellXfs id for thin borders around the cell

1
STYLE_DATE =

cellXfs id for default date styling

2
ERR_RESTRICTION =

error messages RestrictionValidor

"Invalid Data: %s. %s must be one of %s.".freeze
ERR_TYPE =

error message DataTypeValidator

"Invalid Data %s for %s. must be %s.".freeze
ERR_REGEX =

error message for RegexValidator

"Invalid Data. %s does not match %s.".freeze
ERR_RANGE =

error message for RangeValidator

"Invalid Data. %s must be between %s and %s, (inclusive:%s) you gave: %s".freeze
ERR_SHEET_NAME_TOO_LONG =

error message for sheets that use a name which is longer than 31 bytes

"Your worksheet name '%s' is too long. Worksheet names must be 31 characters (bytes) or less".freeze
ERR_SHEET_NAME_CHARACTER_FORBIDDEN =

error message for sheets that use a name which include invalid characters

"Your worksheet name '%s' contains a character which is not allowed by MS Excel and will cause repair warnings. Please change the name of your sheet.".freeze
ERR_DUPLICATE_SHEET_NAME =

error message for duplicate sheet names

"There is already a worksheet in this workbook named '%s'. Please use a unique name".freeze
ERR_INVALID_BORDER_OPTIONS =

error message when user does not provide color and or style options for border in Style#add_sytle

"border hash must include both style and color. e.g. :border => { :color => 'FF000000', :style => :thin }. You provided: %s".freeze
ERR_INVALID_BORDER_ID =

error message for invalid border id reference

"The border id you specified (%s) does not exist. Please add a border with Style#add_style before referencing its index.".freeze
ERR_ANGLE =

error message for invalid angles

"Angles must be a value between -90 and 90. You provided: %s".freeze
ERR_INTEGERISH =

error message for non 'integerish' value

"You value must be, or be castable via to_i, an Integer. You provided %s".freeze
CONTROL_CHARS =

The regular expression used to remove control characters from worksheets

pattern.freeze
ISO_8601_REGEX =
/\A(-?(?:[1-9][0-9]*)?[0-9]{4})-(1[0-2]|0[1-9])-(3[0-1]|0[1-9]|[1-2][0-9])T(2[0-3]|[0-1][0-9]):([0-5][0-9]):([0-5][0-9])(\.[0-9]+)?(Z|[+-](?:2[0-3]|[0-1][0-9]):[0-5][0-9])?\Z/.freeze
FLOAT_REGEX =
/\A[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?\Z/.freeze
NUMERIC_REGEX =
/\A[+-]?\d+?\Z/.freeze
UINT_VALIDATOR =
lambda { |arg| arg.respond_to?(:>=) && arg >= 0 }

Class Method Summary (collapse)

Class Method Details

+ (Object) booleanize(value)

If value is boolean return 1 or 0 else return the value



145
146
147
148
149
150
151
# File 'lib/axlsx.rb', line 145

def self.booleanize(value)
  if value == true || value == false
    value ? 1 : 0
  else
    value
  end
end

+ (String) camel(s = "", all_caps = true)

performs the increadible feat of changing snake_case to CamelCase



126
127
128
129
130
# File 'lib/axlsx.rb', line 126

def self.camel(s="", all_caps = true)
  s = s.to_s
  s = s.capitalize if all_caps
  s.gsub(/_(.)/){ $1.upcase }
end

+ (String) cell_r(c_index, r_index)

Returns The alpha(column)numeric(row) reference for this sell.

Examples:

Relative Cell Reference

ws.rows.first.cells.first.r #=> "A1"


105
106
107
# File 'lib/axlsx.rb', line 105

def self.cell_r(c_index, r_index)
  col_ref(c_index) << (r_index+1).to_s
end

+ (Object) cell_range(cells, absolute = true)

determines the cell range for the items provided



52
53
54
55
56
57
58
59
60
61
62
# File 'lib/axlsx.rb', line 52

def self.cell_range(cells, absolute=true)
  return "" unless cells.first.is_a? Cell
  cells = sort_cells(cells)
  reference = "#{cells.first.reference(absolute)}:#{cells.last.reference(absolute)}"
  if absolute
    escaped_name = cells.first.row.worksheet.name.gsub '&apos;', "''"
    "'#{escaped_name}'!#{reference}"
  else
    reference
  end
end

+ (HtmlEntities) coder

global reference html entity encoding



74
75
76
# File 'lib/axlsx.rb', line 74

def self.coder
  @@coder ||= ::HTMLEntities.new
end

+ (String) col_ref(index)

Note:

This follows the standard spreadsheet convention of naming columns A to Z, followed by AA to AZ etc.

converts the column index into alphabetical values.



91
92
93
94
95
96
97
98
99
100
# File 'lib/axlsx.rb', line 91

def self.col_ref(index)
  chars = ''
  while index >= 26 do
    index, char = index.divmod(26)
    chars.prepend((char + 65).chr)
    index -= 1
  end
  chars.prepend((index + 65).chr)
  chars
end

+ (Object) name_to_indices(name)

returns the x, y position of a cell

Raises:

  • (ArgumentError)


79
80
81
82
83
84
85
86
# File 'lib/axlsx.rb', line 79

def self.name_to_indices(name)
  raise ArgumentError, 'invalid cell name' unless name.size > 1
  # capitalization?!?
  v = name[/[A-Z]+/].reverse.chars.reduce({:base=>1, :i=>0}) do  |val, c|
    val[:i] += ((c.bytes.first - 64) * val[:base]); val[:base] *= 26; val
  end
  [v[:i]-1, ((name[/[1-9][0-9]*/]).to_i)-1]
end

+ (Array) range_to_a(range)

Creates an array of individual cell references based on an excel reference range.



112
113
114
115
116
117
118
119
120
121
# File 'lib/axlsx.rb', line 112

def self.range_to_a(range)
  range.match(/^(\w+?\d+)\:(\w+?\d+)$/)
  start_col, start_row = name_to_indices($1)
  end_col,   end_row   = name_to_indices($2)
  (start_row..end_row).to_a.map do |row_num|
    (start_col..end_col).to_a.map do |col_num|
      cell_r(col_num, row_num)
    end
  end
end

+ (String) sanitize(str)

returns the provided string with all invalid control charaters removed.



136
137
138
139
# File 'lib/axlsx.rb', line 136

def self.sanitize(str)
  str.delete!(CONTROL_CHARS)
  str
end

+ (Array) sort_cells(cells)

sorts the array of cells provided to start from the minimum x,y to the maximum x.y#



68
69
70
# File 'lib/axlsx.rb', line 68

def self.sort_cells(cells)
  cells.sort { |x, y| [x.index, x.row.row_index] <=> [y.index, y.row.row_index] }
end

+ (Object) trust_input

Instructs the serializer to not try to escape cell value input. This will give you a huge speed bonus, but if you content has <, > or other xml character data the workbook will be invalid and excel will complain.



156
157
158
# File 'lib/axlsx.rb', line 156

def self.trust_input
  @trust_input ||= false
end

+ (Boolean) trust_input=(trust_me)

@param[Boolean] trust_me A boolean value indicating if the cell value content is to be trusted

See Also:

  • trust_input


163
164
165
# File 'lib/axlsx.rb', line 163

def self.trust_input=(trust_me)
  @trust_input = trust_me
end

+ (Boolean) validate_angle(v)

Requires that the value is between -54000000 and 54000000

Raises:

  • (ArgumentError)

    raised if the value cannot be converted to an integer between the allowed angle values for chart label rotation.



78
79
80
# File 'lib/axlsx/util/validators.rb', line 78

def self.validate_angle(v)
  raise ArgumentError, (ERR_ANGLE % v.inspect) unless (v.to_i >= -5400000 && v.to_i <= 5400000)
end

+ (Object) validate_boolean(v)

Requires that the value is a form that can be evaluated as a boolean in an xml document. The value must be an instance of Fixnum, String, Integer, Symbol, TrueClass or FalseClass and it must be one of 0, 1, "true", "false", :true, :false, true, false, "0", or "1"



110
111
112
# File 'lib/axlsx/util/validators.rb', line 110

def self.validate_boolean(v)
  DataTypeValidator.validate(:boolean, [String, Integer, Symbol, TrueClass, FalseClass], v, lambda { |arg| [0, 1, "true", "false", :true, :false, true, false, "0", "1"].include?(arg) })
end

+ (Object) validate_cell_u(v)

Requires that the value is one of :none, :single, :double, :singleAccounting, :doubleAccounting



147
148
149
# File 'lib/axlsx/util/validators.rb', line 147

def self.validate_cell_u(v)
  RestrictionValidator.validate "cell run style u", [:none, :single, :double, :singleAccounting, :doubleAccounting], v
end

+ (Object) validate_conditional_formatting_operator(v)

Requires that the value is valid conditional formatting operator. valid operators must be one of lessThan, lessThanOrEqual, equal, notEqual, greaterThanOrEqual, greaterThan, between, notBetween, containsText, notContains, beginsWith, endsWith



199
200
201
# File 'lib/axlsx/util/validators.rb', line 199

def self.validate_conditional_formatting_operator(v)
  RestrictionValidator.validate :conditional_formatting_type, [:lessThan, :lessThanOrEqual, :equal, :notEqual, :greaterThanOrEqual, :greaterThan, :between, :notBetween, :containsText, :notContains, :beginsWith, :endsWith], v
end

+ (Object) validate_conditional_formatting_type(v)

Requires that the value is valid conditional formatting type. valid types must be one of expression, cellIs, colorScale, dataBar, iconSet, top10, uniqueValues, duplicateValues, containsText, notContainsText, beginsWith, endsWith, containsBlanks, notContainsBlanks, containsErrors, notContainsErrors, timePeriod, aboveAverage



184
185
186
# File 'lib/axlsx/util/validators.rb', line 184

def self.validate_conditional_formatting_type(v)
  RestrictionValidator.validate :conditional_formatting_type, [:expression, :cellIs, :colorScale, :dataBar, :iconSet, :top10, :uniqueValues, :duplicateValues, :containsText, :notContainsText, :beginsWith, :endsWith, :containsBlanks, :notContainsBlanks, :containsErrors, :notContainsErrors, :timePeriod, :aboveAverage], v
end

+ (Object) validate_conditional_formatting_value_object_type(v)

Requires thatt he value is a valid conditional formatting value object type. valid types must be one of num, percent, max, min, formula and percentile



190
191
192
# File 'lib/axlsx/util/validators.rb', line 190

def self.validate_conditional_formatting_value_object_type(v)
  RestrictionValidator.validate :conditional_formatting_value_object_type, [:num, :percent, :max, :min, :formula, :percentile], v
end

+ (Object) validate_content_type(v)

Requires that the value is a valid content_type TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, DRAWING_CT, COMMENT_CT are allowed



234
235
236
# File 'lib/axlsx/util/validators.rb', line 234

def self.validate_content_type(v)
  RestrictionValidator.validate :content_type, [TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, JPEG_CT, GIF_CT, PNG_CT, DRAWING_CT, COMMENT_CT, VML_DRAWING_CT, PIVOT_TABLE_CT, PIVOT_TABLE_CACHE_DEFINITION_CT], v
end

+ (Object) validate_data_validation_error_style(v)

Requires that the value is a valid data_validation_error_style :information, :stop, :warning



255
256
257
# File 'lib/axlsx/util/validators.rb', line 255

def self.validate_data_validation_error_style(v)
  RestrictionValidator.validate :validate_data_validation_error_style, [:information, :stop, :warning], v
end

+ (Object) validate_data_validation_operator(v)

Requires that the value is valid data validation operator. valid operators must be one of lessThan, lessThanOrEqual, equal, notEqual, greaterThanOrEqual, greaterThan, between, notBetween



263
264
265
# File 'lib/axlsx/util/validators.rb', line 263

def self.validate_data_validation_operator(v)
  RestrictionValidator.validate :data_validation_operator, [:lessThan, :lessThanOrEqual, :equal, :notEqual, :greaterThanOrEqual, :greaterThan, :between, :notBetween], v
end

+ (Object) validate_data_validation_type(v)

Requires that the value is valid data validation type. valid types must be one of custom, data, decimal, list, none, textLength, time, whole



270
271
272
# File 'lib/axlsx/util/validators.rb', line 270

def self.validate_data_validation_type(v)
  RestrictionValidator.validate :data_validation_type, [:custom, :data, :decimal, :list, :none, :textLength, :time, :whole], v
end

+ (Object) validate_display_blanks_as(v)

Requires that the value is a valid "display blanks as" type. valid types must be one of gap, span, zero



298
299
300
# File 'lib/axlsx/util/validators.rb', line 298

def self.validate_display_blanks_as(v)
  RestrictionValidator.validate :display_blanks_as, [:gap, :span, :zero], v
end

+ (Object) validate_family(v)

validates cell style family which must be between 1 and 5



152
153
154
# File 'lib/axlsx/util/validators.rb', line 152

def self.validate_family(v)
  RestrictionValidator.validate "cell run style family", 1..5, v
end

+ (Object) validate_float(v)

Requires that the value is a Float



122
123
124
# File 'lib/axlsx/util/validators.rb', line 122

def self.validate_float(v)
  DataTypeValidator.validate :float, Float, v
end

+ (Object) validate_gradient_type(v)

Requires that the value is a gradient_type. valid types are :linear and :path



206
207
208
# File 'lib/axlsx/util/validators.rb', line 206

def self.validate_gradient_type(v)
  RestrictionValidator.validate :gradient_type, [:linear, :path], v
end

+ (Object) validate_horizontal_alignment(v)

Requires that the value is a valid horizontal_alignment :general, :left, :center, :right, :fill, :justify, :centerContinuous, :distributed are allowed



220
221
222
# File 'lib/axlsx/util/validators.rb', line 220

def self.validate_horizontal_alignment(v)
  RestrictionValidator.validate :horizontal_alignment, [:general, :left, :center, :right, :fill, :justify, :centerContinuous, :distributed], v
end

+ (Object) validate_icon_set(v)

Requires that the value is one of the valid ST_IconSet types Allowed values are: 3Arrows, 3ArrowsGray, 3Flags, 3TrafficLights1, 3TrafficLights2, 3Signs, 3Symbols, 3Symbols2, 4Arrows, 4ArrowsGray, 4RedToBlack, 4Rating, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Rating, 5Quarters



173
174
175
# File 'lib/axlsx/util/validators.rb', line 173

def self.validate_icon_set(v)
  RestrictionValidator.validate :iconSet, ["3Arrows", "3ArrowsGray", "3Flags", "3TrafficLights1", "3TrafficLights2", "3Signs", "3Symbols", "3Symbols2", "4Arrows", "4ArrowsGray", "4RedToBlack", "4Rating", "4TrafficLights", "5Arrows", "5ArrowsGray", "5Rating", "5Quarters"], v
end

+ (Object) validate_int(v)

Requires that the value is a Integer



102
103
104
# File 'lib/axlsx/util/validators.rb', line 102

def self.validate_int(v)
  DataTypeValidator.validate :signed_int, Integer, v
end

+ (Object) validate_integerish(v)

Requires that the value can be converted to an integer @para, [Any] v the value to validate

Raises:

  • (ArgumentError)

    raised if the value cannot be converted to an integer



70
71
72
# File 'lib/axlsx/util/validators.rb', line 70

def self.validate_integerish(v)
  raise ArgumentError, (ERR_INTEGERISH % v.inspect) unless (v.respond_to?(:to_i) && v.to_i.is_a?(Integer))
end

+ (Object) validate_number_with_unit(v)

Requires that the value is a string containing a positive decimal number followed by one of the following units: "mm", "cm", "in", "pt", "pc", "pi"



128
129
130
# File 'lib/axlsx/util/validators.rb', line 128

def self.validate_number_with_unit(v)
  RegexValidator.validate "number_with_unit", /\A[0-9]+(\.[0-9]+)?(mm|cm|in|pt|pc|pi)\Z/, v
end

+ (Object) validate_page_orientation(v)

Requires that the value is one of :default, :landscape, or :portrait.



143
144
145
# File 'lib/axlsx/util/validators.rb', line 143

def self.validate_page_orientation(v)
  RestrictionValidator.validate "page_orientation", [:default, :landscape, :portrait], v
end

+ (Object) validate_pane_type(v)

Requires that the value is a valid active pane type. valid types must be one of bottom_left, bottom_right, top_left, top_right



284
285
286
# File 'lib/axlsx/util/validators.rb', line 284

def self.validate_pane_type(v)
  RestrictionValidator.validate :active_pane_type, [:bottom_left, :bottom_right, :top_left, :top_right], v
end

+ (Object) validate_pattern_type(v)

Requires that the value is valid pattern type. valid pattern types must be one of :none, :solid, :mediumGray, :darkGray, :lightGray, :darkHorizontal, :darkVertical, :darkDown, :darkUp, :darkGrid, :darkTrellis, :lightHorizontal, :lightVertical, :lightDown, :lightUp, :lightGrid, :lightTrellis, :gray125, or :gray0625.



159
160
161
162
# File 'lib/axlsx/util/validators.rb', line 159

def self.validate_pattern_type(v)
  RestrictionValidator.validate :pattern_type, [:none, :solid, :mediumGray, :darkGray, :lightGray, :darkHorizontal, :darkVertical, :darkDown, :darkUp, :darkGrid,
                                                :darkTrellis, :lightHorizontal, :lightVertical, :lightDown, :lightUp, :lightGrid, :lightTrellis, :gray125, :gray0625], v
end

+ (Object) validate_relationship_type(v)

Requires that the value is a valid relationship_type XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R are allowed



241
242
243
# File 'lib/axlsx/util/validators.rb', line 241

def self.validate_relationship_type(v)
  RestrictionValidator.validate :relationship_type, [XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R, COMMENT_R, VML_DRAWING_R, COMMENT_R_NULL, PIVOT_TABLE_R, PIVOT_TABLE_CACHE_DEFINITION_R], v
end

+ (Object) validate_scale_0_10_400(v)

Requires that the value is an integer ranging from 10 to 400 or 0.



138
139
140
# File 'lib/axlsx/util/validators.rb', line 138

def self.validate_scale_0_10_400(v)
  DataTypeValidator.validate "page_scale", Integer, v, lambda { |arg| arg == 0 || (arg >= 10 && arg <= 400) }
end

+ (Object) validate_scale_10_400(v)

Requires that the value is an integer ranging from 10 to 400.



133
134
135
# File 'lib/axlsx/util/validators.rb', line 133

def self.validate_scale_10_400(v)
  DataTypeValidator.validate "page_scale", Integer, v, lambda { |arg| arg >= 10 && arg <= 400 }
end

+ (Object) validate_scatter_style(v)

Requires that the value is a valid scatterStyle must be one of :none | :line | :lineMarker | :marker | :smooth | :smoothMarker must be one of "none" | "line" | "lineMarker" | "marker" | "smooth" | "smoothMarker"



214
215
216
# File 'lib/axlsx/util/validators.rb', line 214

def self.validate_scatter_style(v)
  Axlsx::RestrictionValidator.validate "ScatterChart.scatterStyle", [:none, :line, :lineMarker, :marker, :smooth, :smoothMarker], v.to_sym
end

+ (Object) validate_sheet_view_type(v)

Requires that the value is a valid sheet view type. valid types must be one of normal, page_break_preview, page_layout



277
278
279
# File 'lib/axlsx/util/validators.rb', line 277

def self.validate_sheet_view_type(v)
  RestrictionValidator.validate :sheet_view_type, [:normal, :page_break_preview, :page_layout], v
end

+ (Object) validate_split_state_type(v)

Requires that the value is a valid split state type. valid types must be one of frozen, frozen_split, split



291
292
293
# File 'lib/axlsx/util/validators.rb', line 291

def self.validate_split_state_type(v)
  RestrictionValidator.validate :split_state_type, [:frozen, :frozen_split, :split], v
end

+ (Object) validate_string(v)

Requires that the value is a String



116
117
118
# File 'lib/axlsx/util/validators.rb', line 116

def self.validate_string(v)
  DataTypeValidator.validate :string, String, v
end

+ (Object) validate_table_element_type(v)

Requires that the value is a valid table element type :wholeTable, :headerRow, :totalRow, :firstColumn, :lastColumn, :firstRowStripe, :secondRowStripe, :firstColumnStripe, :secondColumnStripe, :firstHeaderCell, :lastHeaderCell, :firstTotalCell, :lastTotalCell, :firstSubtotalColumn, :secondSubtotalColumn, :thirdSubtotalColumn, :firstSubtotalRow, :secondSubtotalRow, :thirdSubtotalRow, :blankRow, :firstColumnSubheading, :secondColumnSubheading, :thirdColumnSubheading, :firstRowSubheading, :secondRowSubheading, :thirdRowSubheading, :pageFieldLabels, :pageFieldValues are allowed



248
249
250
# File 'lib/axlsx/util/validators.rb', line 248

def self.validate_table_element_type(v)
  RestrictionValidator.validate :table_element_type, [:wholeTable, :headerRow, :totalRow, :firstColumn, :lastColumn, :firstRowStripe, :secondRowStripe, :firstColumnStripe, :secondColumnStripe, :firstHeaderCell, :lastHeaderCell, :firstTotalCell, :lastTotalCell, :firstSubtotalColumn, :secondSubtotalColumn, :thirdSubtotalColumn, :firstSubtotalRow, :secondSubtotalRow, :thirdSubtotalRow, :blankRow, :firstColumnSubheading, :secondColumnSubheading, :thirdColumnSubheading, :firstRowSubheading, :secondRowSubheading, :thirdRowSubheading, :pageFieldLabels, :pageFieldValues], v
end

+ (Object) validate_time_period_type(v)

Requires that the value is one of the ST_TimePeriod types valid time period types are today, yesterday, tomorrow, last7Days, thisMonth, lastMonth, nextMonth, thisWeek, lastWeek, nextWeek



167
168
169
# File 'lib/axlsx/util/validators.rb', line 167

def self.validate_time_period_type(v)
  RestrictionValidator.validate :time_period_type, [:today, :yesterday, :tomorrow, :last7Days, :thisMonth, :lastMonth, :nextMonth, :thisWeek, :lastWeek, :nextWeek], v
end

+ (Boolean) validate_unsigned_int(v)

Requires that the value is a Fixnum or Integer and is greater or equal to 0

Raises:

  • (ArgumentError)

    raised if the value is not a Fixnum or Integer value greater or equal to 0



88
89
90
# File 'lib/axlsx/util/validators.rb', line 88

def self.validate_unsigned_int(v)
  DataTypeValidator.validate(:unsigned_int, Integer, v, UINT_VALIDATOR)
end

+ (Boolean) validate_unsigned_numeric(v)

Requires that the value is a Fixnum Integer or Float and is greater or equal to 0

Raises:

  • (ArgumentError)

    raised if the value is not a Fixnun, Integer, Float value greater or equal to 0



96
97
98
# File 'lib/axlsx/util/validators.rb', line 96

def self.validate_unsigned_numeric(v)
  DataTypeValidator.validate(:unsigned_numeric, Numeric, v, UINT_VALIDATOR)
end

+ (Object) validate_vertical_alignment(v)

Requires that the value is a valid vertical_alignment :top, :center, :bottom, :justify, :distributed are allowed



227
228
229
# File 'lib/axlsx/util/validators.rb', line 227

def self.validate_vertical_alignment(v)
  RestrictionValidator.validate :vertical_alignment, [:top, :center, :bottom, :justify, :distributed], v
end

+ (Object) validate_view_visibility(v)

Requires that the value is one of :visible, :hidden, :very_hidden



303
304
305
# File 'lib/axlsx/util/validators.rb', line 303

def self.validate_view_visibility(v)
  RestrictionValidator.validate :visibility, [:visible, :hidden, :very_hidden], v
end