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/bar_chart.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/area_chart.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/area_series.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

Defined Under Namespace

Modules: Accessors, MimeTypeUtils, OptionsParser, Parser, SerializedAttributes Classes: AbstractContentType, App, AreaChart, AreaSeries, AutoFilter, AxDataSource, Axes, Axis, Bar3DChart, BarChart, 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

VERSION =

The current version

"3.0.0.pre"
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 =

ISO 8601 date recognition

/\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 =

FLOAT recognition

/\A[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?\Z/.freeze
NUMERIC_REGEX =

Numeric recognition

/\A[+-]?\d+?\Z/.freeze
UINT_VALIDATOR =

Validates an unsigned intger

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)


150
151
152
153
154
155
156
# File 'lib/axlsx.rb', line 150

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:

  • (String)


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

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.



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

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



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

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

.coderHtmlEntities

global reference html entity encoding

Returns:

  • (HtmlEntities)


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

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:

  • (String)


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

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)


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

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)


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

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:

  • (String)


137
138
139
140
141
142
143
144
# File 'lib/axlsx.rb', line 137

def self.sanitize(str)
  if str.frozen?
    str.delete(CONTROL_CHARS)
  else
    str.delete!(CONTROL_CHARS)
    str
  end
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)


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

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

.trust_inputObject

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.



161
162
163
# File 'lib/axlsx.rb', line 161

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


168
169
170
# File 'lib/axlsx.rb', line 168

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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"



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

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.



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

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



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

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



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

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



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

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.



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

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.



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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



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

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