Module: Axlsx

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

xsd:sequence /xsd:sequence /xsd:complexType

Defined Under Namespace

Modules: Accessors, MimeTypeUtils, 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, OutlinePr, 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 collapse

VERSION =

The current version

"2.2.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/package/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

.booleanize(value) ⇒ Object

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

Parameters:

  • value (Object) —

    The value to process

Returns:

  • (Object) 


147
148
149
150
151
152
153
 
# File 'lib/axlsx.rb', line 147

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

.camel(s = "", all_caps = true) ⇒ String

performs the increadible feat of changing snake_case to CamelCase

Parameters:

  • s (String) (defaults to: "") —

    The snake case string to camelize

Returns:



128
129
130
131
132
 
# File 'lib/axlsx.rb', line 128

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

.cell_r(c_index, r_index) ⇒ String

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

Examples:

Relative Cell Reference

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

Returns:

  • (String) —

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



107
108
109
 
# File 'lib/axlsx.rb', line 107

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

.cell_range(cells, absolute = true) ⇒ Object

determines the cell range for the items provided



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

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

.coder ⇒ HtmlEntities

global reference html entity encoding

Returns:

  • (HtmlEntities) 


76
77
78
 
# File 'lib/axlsx.rb', line 76

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

.col_ref(index) ⇒ String

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.

Returns:



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

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

.name_to_indices(name) ⇒ Object

returns the x, y position of a cell

Raises:

  • (ArgumentError) 


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

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

.range_to_a(range) ⇒ Array

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

Parameters:

  • range (String) —

    A cell range, for example A1:D5

Returns:

  • (Array) 


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

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

.sanitize(str) ⇒ String

returns the provided string with all invalid control charaters removed.

Parameters:

  • str (String) —

    The string to process

Returns:



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

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

.sort_cells(cells) ⇒ Array

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

Parameters:

  • cells (Array)

Returns:

  • (Array) 


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

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

.trust_input ⇒ Object

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.



158
159
160
 
# File 'lib/axlsx.rb', line 158

def self.trust_input
  @trust_input ||= false
end

.trust_input=(trust_me) ⇒ Boolean

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

Returns:

  • (Boolean)

See Also:

  • trust_input


165
166
167
 
# File 'lib/axlsx.rb', line 165

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

.validate_angle(v) ⇒ Boolean

Requires that the value is between -54000000 and 54000000

Parameters:

  • v (Any) —

    The value validated

Returns:

  • (Boolean) —

    true if the data is valid

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

.validate_boolean(v) ⇒ Object

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 String, Integer, Symbol, TrueClass or FalseClass and it must be one of 0, 1, "true", "false", :true, :false, true, false, "0", or "1"

Parameters:

  • v (Any) —

    The value validated



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

.validate_cell_u(v) ⇒ Object

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

.validate_conditional_formatting_operator(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_conditional_formatting_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_conditional_formatting_value_object_type(v) ⇒ Object

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

.validate_content_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_data_validation_error_style(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_data_validation_operator(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_data_validation_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_display_blanks_as(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_family(v) ⇒ Object

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

.validate_float(v) ⇒ Object

Requires that the value is a Float

Parameters:

  • v (Any) —

    The value validated



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

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

.validate_gradient_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_horizontal_alignment(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_icon_set(v) ⇒ Object

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

.validate_int(v) ⇒ Object

Requires that the value is a Integer

Parameters:

  • v (Any) —

    The value validated



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

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

.validate_integerish(v) ⇒ Object

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

.validate_marker_symbol(v) ⇒ Object

Requires that the value is one of :default, :circle, :dash, :diamond, :dot, :picture, :plus, :square, :star, :triangle, :x



308
309
310
 
# File 'lib/axlsx/util/validators.rb', line 308

def self.validate_marker_symbol(v)
  RestrictionValidator.validate :marker_symbol, [:default, :circle, :dash, :diamond, :dot, :picture, :plus, :square, :star, :triangle, :x], v
end

.validate_number_with_unit(v) ⇒ Object

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

.validate_page_orientation(v) ⇒ Object

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

.validate_pane_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_pattern_type(v) ⇒ Object

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.

Parameters:

  • v (Any) —

    The value validated



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

.validate_relationship_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_scale_0_10_400(v) ⇒ Object

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

.validate_scale_10_400(v) ⇒ Object

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

.validate_scatter_style(v) ⇒ Object

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"

Parameters:

  • v (Symbol|String) —

    the value to validate



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

.validate_sheet_view_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_split_state_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_string(v) ⇒ Object

Requires that the value is a String

Parameters:

  • v (Any) —

    The value validated



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

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

.validate_table_element_type(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_time_period_type(v) ⇒ Object

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

.validate_unsigned_int(v) ⇒ Boolean

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

Parameters:

  • v (Any) —

    The value validated

Returns:

  • (Boolean) —

    true if the data is valid

Raises:

  • (ArgumentError) —

    raised if the value is not a 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

.validate_unsigned_numeric(v) ⇒ Boolean

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

Parameters:

  • v (Any) —

    The value validated

Returns:

  • (Boolean) —

    true if the data is valid

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

.validate_vertical_alignment(v) ⇒ Object

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

Parameters:

  • v (Any) —

    The value validated



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

.validate_view_visibility(v) ⇒ Object

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