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/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/val_axis_data.rb,
lib/axlsx/stylesheet/cell_style.rb,
lib/axlsx/stylesheet/table_style.rb,
lib/axlsx/util/simple_typed_list.rb,
lib/axlsx/workbook/worksheet/row.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/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/stylesheet/table_style_element.rb

Overview

xlsx generation with charts, images, automated column width, customizable styles and full schema validation. Axlsx excels at helping you generate beautiful Office Open XML Spreadsheet documents without having to understand the entire ECMA specification. Check out the README for some examples of how easy it is. Best of all, you can validate your xlsx file before serialization so you know for sure that anything generated is going to load on your client’s machine.

Defined Under Namespace

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

Constant Summary collapse

VERSION =

version 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.0.15"
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"
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"
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"
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"
CHART_PN =

chart part

"charts/chart%d.xml"
IMAGE_PN =

chart part

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

App validation schema

"lib/schema/shared-documentPropertiesExtended.xsd"
CORE_XSD =

core validation schema

"lib/schema/opc-coreProperties.xsd"
CONTENT_TYPES_XSD =

content types validation schema

"lib/schema/opc-contentTypes.xsd"
RELS_XSD =

rels validation schema

"lib/schema/opc-relationships.xsd"
SML_XSD =

spreadsheetML validation schema

"lib/schema/sml.xsd"
DRAWING_XSD =

drawing validation schema

"lib/schema/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_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_range(items) ⇒ Object

determines the cell range for the items provided



43
44
45
46
47
48
49
 
# File 'lib/axlsx.rb', line 43

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

.name_to_indices(name) ⇒ Object

Raises:

  • (ArgumentError) 


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

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  |v, c|        
    v[:i] += ((c.bytes.first - 65) + v[:base]); v[:base] *= 26; v 
  end

  [v[:i]-1, ((name[/[1-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



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

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



114
115
116
 
# File 'lib/axlsx/util/validators.rb', line 114

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



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

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



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

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



100
101
102
 
# File 'lib/axlsx/util/validators.rb', line 100

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



57
58
59
 
# File 'lib/axlsx/util/validators.rb', line 57

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



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

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 are allowed

Parameters:

  • v (Any) —

    The value validated



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

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

.validate_string(v) ⇒ Object

Requires that the value is a String

Parameters:

  • v (Any) —

    The value validated



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

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



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

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



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

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



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

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