Module: Axlsx

Defined in:
lib/axlsx.rb,
lib/axlsx/package.rb,
lib/axlsx/version.rb,
lib/axlsx/util/cbf.rb,
lib/axlsx/drawing/pic.rb,
lib/axlsx/util/parser.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/marker.rb,
lib/axlsx/drawing/series.rb,
lib/axlsx/util/constants.rb,
lib/axlsx/drawing/drawing.rb,
lib/axlsx/drawing/scaling.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/ser_axis.rb,
lib/axlsx/drawing/val_axis.rb,
lib/axlsx/stylesheet/color.rb,
lib/axlsx/drawing/hyperlink.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/pie_series.rb,
lib/axlsx/rels/relationships.rb,
lib/axlsx/stylesheet/num_fmt.rb,
lib/axlsx/util/ms_off_crypto.rb,
lib/axlsx/drawing/line_series.rb,
lib/axlsx/content_type/default.rb,
lib/axlsx/drawing/bar_3D_chart.rb,
lib/axlsx/drawing/pie_3D_chart.rb,
lib/axlsx/drawing/series_title.rb,
lib/axlsx/stylesheet/border_pr.rb,
lib/axlsx/content_type/override.rb,
lib/axlsx/drawing/cat_axis_data.rb,
lib/axlsx/drawing/graphic_frame.rb,
lib/axlsx/drawing/line_3D_chart.rb,
lib/axlsx/drawing/scatter_chart.rb,
lib/axlsx/drawing/val_axis_data.rb,
lib/axlsx/stylesheet/cell_style.rb,
lib/axlsx/drawing/scatter_series.rb,
lib/axlsx/stylesheet/table_style.rb,
lib/axlsx/util/simple_typed_list.rb,
lib/axlsx/workbook/worksheet/col.rb,
lib/axlsx/workbook/worksheet/row.rb,
lib/axlsx/drawing/named_axis_data.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/worksheet/cell.rb,
lib/axlsx/stylesheet/gradient_fill.rb,
lib/axlsx/stylesheet/gradient_stop.rb,
lib/axlsx/workbook/worksheet/table.rb,
lib/axlsx/content_type/content_type.rb,
lib/axlsx/stylesheet/cell_alignment.rb,
lib/axlsx/stylesheet/cell_protection.rb,
lib/axlsx/workbook/worksheet/worksheet.rb,
lib/axlsx/workbook/shared_strings_table.rb,
lib/axlsx/stylesheet/table_style_element.rb,
lib/axlsx/workbook/worksheet/page_margins.rb,
lib/axlsx/workbook/worksheet/date_time_converter.rb

Overview

TODO: review cat, val and named access data to extend this and reduce replicated code.

Defined Under Namespace

Modules: Parser Classes: App, Axis, Bar3DChart, BarSeries, Border, BorderPr, CatAxis, CatAxisData, Cbf, Cell, CellAlignment, CellProtection, CellStyle, Chart, Col, Color, ContentType, Core, DataTypeValidator, DateTimeConverter, Default, Drawing, Fill, Font, GradientFill, GradientStop, GraphicFrame, Hyperlink, Line3DChart, LineSeries, Marker, MsOffCrypto, NamedAxisData, NumFmt, OneCellAnchor, Override, Package, PageMargins, PatternFill, Pic, PictureLocking, Pie3DChart, PieSeries, RegexValidator, Relationship, Relationships, RestrictionValidator, Row, Scaling, ScatterChart, ScatterSeries, SerAxis, Series, SeriesTitle, SharedStringsTable, Storage, Styles, Table, TableStyle, TableStyleElement, TableStyles, Title, TwoCellAnchor, ValAxis, ValAxisData, View3D, Workbook, Worksheet, Xf

Constant Summary collapse

VERSION =

The version of the gem When using bunle exec rake and referencing the gem on github or locally it will use the gemspec, which preloads this constant for the gem’s version. We check to make sure that it has not already been loaded

"1.1.0"
ENCODING =

XML Encoding

"UTF-8"
XML_NS =

spreadsheetML namespace

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

content-types namespace

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

extended-properties namespace

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

doc props namespace

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

core properties namespace

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

dc elements (core) namespace

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

dcmit (core) namespcace

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

dc terms namespace

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

xml schema namespace

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

spreadsheet drawing namespace

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

drawing namespace

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

chart namespace

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

relationships namespace

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

relationships name space

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

table rels namespace

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

workbook rels namespace

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

worksheet rels namespace

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

app rels namespace

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

core rels namespace

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

styles rels namespace

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

shared strings namespace

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

drawing rels namespace

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

chart rels namespace

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

image rels namespace

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

table content type

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

workbook content type

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

app content type

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

rels content type

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

styles content type

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

xml content type

"application/xml"
WORKSHEET_CT =

worksheet content type

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

shared strings content type

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

core content type

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

chart content type

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

jpeg content type

"image/jpeg"
GIF_CT =

gif content type

"image/gif"
PNG_CT =

png content type

"image/png"
DRAWING_CT =

drawing content type

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

xml content type extensions

"xml"
JPEG_EX =

jpeg extension

"jpeg"
GIF_EX =

gif extension

"gif"
PNG_EX =

png extension

"png"
RELS_EX =

rels content type extension

"rels"
WORKBOOK_PN =

workbook part

"xl/workbook.xml"
STYLES_PN =

styles part

"styles.xml"
SHARED_STRINGS_PN =

shared_strings part

"sharedStrings.xml"
APP_PN =

app part

"docProps/app.xml"
CORE_PN =

core part

"docProps/core.xml"
CONTENT_TYPES_PN =

content types part

"[Content_Types].xml"
RELS_PN =

rels part

"_rels/.rels"
WORKBOOK_RELS_PN =

workbook rels part

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

worksheet part

"worksheets/sheet%d.xml"
WORKSHEET_RELS_PN =

worksheet rels part

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

drawing part

"drawings/drawing%d.xml"
DRAWING_RELS_PN =

drawing rels part

"drawings/_rels/drawing%d.xml.rels"
TABLE_PN =

drawing part

"tables/table%d.xml"
CHART_PN =

chart part

"charts/chart%d.xml"
IMAGE_PN =

chart part

"media/image%d.%s"
SCHEMA_BASE =

location of schema files for validation

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

App validation schema

SCHEMA_BASE + "shared-documentPropertiesExtended.xsd"
CORE_XSD =

core validation schema

SCHEMA_BASE + "opc-coreProperties.xsd"
CONTENT_TYPES_XSD =

content types validation schema

SCHEMA_BASE + "opc-contentTypes.xsd"
RELS_XSD =

rels validation schema

SCHEMA_BASE + "opc-relationships.xsd"
SML_XSD =

spreadsheetML validation schema

SCHEMA_BASE + "sml.xsd"
DRAWING_XSD =

drawing validation schema

SCHEMA_BASE + "dml-spreadsheetDrawing.xsd"
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."
ERR_TYPE =

error message DataTypeValidator

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

error message for RegexValidator

"Invalid Data. %s does not match %s."
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"
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"

Class Method Summary collapse

Class Method Details

.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.



83
84
85
 
# File 'lib/axlsx.rb', line 83

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

.cell_range(items) ⇒ Object

determines the cell range for the items provided



49
50
51
52
53
54
55
 
# File 'lib/axlsx.rb', line 49

def self.cell_range(items)
  return "" unless items.first.is_a? Cell
  ref = "'#{items.first.row.worksheet.name}'!" +
    "#{items.first.r_abs}"
  ref += ":#{items.last.r_abs}" if items.size > 1
  ref
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) 


70
71
72
73
74
75
76
77
78
 
# File 'lib/axlsx.rb', line 70

def self.col_ref(index)
  chars = []
  while index >= 26 do
    chars << ((index % 26) + 65).chr
    index = index / 26 - 1
  end
  chars << (index + 65).chr
  chars.reverse.join
end

.name_to_indices(name) ⇒ Object

Raises:

  • (ArgumentError) 


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

def self.name_to_indices(name)
  raise ArgumentError, 'invalid cell name' unless name.size > 1
  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

.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 Fixnum, 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



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

def self.validate_boolean(v)
  DataTypeValidator.validate(:boolean, [Fixnum, String, Integer, Symbol, TrueClass, FalseClass], v, lambda { |arg| [0, 1, "true", "false", :true, :false, true, false, "0", "1"].include?(arg) })
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 are allowed

Parameters:

  • v (Any) —

    The value validated



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

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], v
end

.validate_float(v) ⇒ Object

Requires that the value is a Float

Parameters:

  • v (Any) —

    The value validated



86
87
88
 
# File 'lib/axlsx/util/validators.rb', line 86

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



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

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



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

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

.validate_int(v) ⇒ Object

Requires that the value is a Fixnum or Integer

Parameters:

  • v (Any) —

    The value validated



66
67
68
 
# File 'lib/axlsx/util/validators.rb', line 66

def self.validate_int(v)
  DataTypeValidator.validate :unsigned_int, [Fixnum, Integer], 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



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

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



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

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], v
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:

  • the (Symbol|String) —

    value to validate



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

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

.validate_string(v) ⇒ Object

Requires that the value is a String

Parameters:

  • v (Any) —

    The value validated



80
81
82
 
# File 'lib/axlsx/util/validators.rb', line 80

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



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

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_unsigned_int(v) ⇒ Boolean

Requires that the value is a Fixnum or 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 Fixnum or Integer value greater or equal to 0



52
53
54
 
# File 'lib/axlsx/util/validators.rb', line 52

def self.validate_unsigned_int(v)
  DataTypeValidator.validate(:unsigned_int, [Fixnum, Integer], v, lambda { |arg| arg.respond_to?(:>=) && arg >= 0 })
end

.validate_unsigned_numeric(v) ⇒ Boolean

Requires that the value is a Fixnum 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



60
61
62
 
# File 'lib/axlsx/util/validators.rb', line 60

def self.validate_unsigned_numeric(v)
  DataTypeValidator.validate("Invalid column width", [Fixnum, Integer, Float], v, lambda { |arg| arg.respond_to?(:>=) && arg >= 0 })
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



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

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