Class: Writexlsx::Workbook

Inherits:
Object
  • Object
show all
Includes:
Utility
Defined in:
lib/write_xlsx/workbook.rb

Overview

The WriteXLSX provides an object oriented interface to a new Excel workbook. The following methods are available through a new workbook.

Direct Known Subclasses

WriteXLSX

Constant Summary

Constants included from Utility

Utility::COL_MAX, Utility::ROW_MAX, Utility::SHEETNAME_MAX, Utility::STR_MAX

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Utility

#absolute_char, #check_dimensions, #check_dimensions_and_update_max_min_values, #check_parameter, #convert_date_time, #dash_types, delete_files, #fill_properties, #float_to_str, #layout_properties, #line_fill_properties, #line_properties, #palette_color, #pixels_to_points, #process_workbook_options, #ptrue?, #put_deprecate_message, #quote_sheetname, #r_id_attributes, #row_col_notation, #shape_style_base, #store_col_max_min_values, #store_row_max_min_values, #substitute_cellref, #underline_attributes, #v_shape_attributes_base, #v_shape_style_base, #value_or_raise, #write_anchor, #write_auto_fill, #write_color, #write_comment_path, #write_div, #write_fill, #write_font, #write_stroke, #write_xml_declaration, #xl_cell_to_rowcol, #xl_col_to_name, #xl_range, #xl_range_formula, #xl_rowcol_to_cell

Constructor Details

#initialize(file, *option_params) ⇒ Workbook

A new Excel workbook is created using the new constructor which accepts either a filename or an IO object as a parameter. The following example creates a new Excel file based on a filename:

workbook  = WriteXLSX.new('filename.xlsx')
worksheet = workbook.add_worksheet
worksheet.write(0, 0, 'Hi Excel!')
workbook.close

Here are some other examples of using new with filenames:

workbook1 = WriteXLSX.new(filename)
workbook2 = WriteXLSX.new('/tmp/filename.xlsx')
workbook3 = WriteXLSX.new("c:\\tmp\\filename.xlsx")
workbook4 = WriteXLSX.new('c:\tmp\filename.xlsx')

The last two examples demonstrates how to create a file on DOS or Windows where it is necessary to either escape the directory separator \ or to use single quotes to ensure that it isn't interpolated.

It is recommended that the filename uses the extension .xlsx rather than .xls since the latter causes an Excel warning when used with the XLSX format.

The new constructor returns a WriteXLSX object that you can use to add worksheets and store data.

You can also pass a valid IO object to the new constructor.

xlsx = StringIO.new
workbook = WriteXLSX.new(xlsx)
....
workbook.close
# you can get XLSX binary data as xlsx.string

And you can pass default_formats parameter like this:

formats = { :font => 'Arial', :size => 10.5 }
workbook = WriteXLSX.new('file.xlsx', formats)

91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
# File 'lib/write_xlsx/workbook.rb', line 91

def initialize(file, *option_params)
  options, default_formats = process_workbook_options(*option_params)
  @writer = Package::XMLWriterSimple.new

  @file                = file
  @tempdir = options[:tempdir] ||
    File.join(Dir.tmpdir, Digest::MD5.hexdigest("#{Time.now.to_f.to_s}-#{Process.pid}"))
  @date_1904           = options[:date_1904] || false
  @activesheet         = 0
  @firstsheet          = 0
  @selected            = 0
  @fileclosed          = false
  @worksheets          = Sheets.new
  @charts              = []
  @drawings            = []
  @formats             = Formats.new
  @xf_formats          = []
  @dxf_formats         = []
  @font_count          = 0
  @num_format_count    = 0
  @defined_names       = []
  @named_ranges        = []
  @custom_colors       = []
  @doc_properties      = {}
  @local_time          = Time.now
  @optimization        = options[:optimization] || 0
  @x_window            = 240
  @y_window            = 15
  @window_width        = 16095
  @window_height       = 9660
  @tab_ratio           = 500
  @excel2003_style     = options[:excel2003_style] || false
  @table_count         = 0
  @image_types         = {}
  @images              = []

  # Structures for the shared strings data.
  @shared_strings = Package::SharedStrings.new

  # Formula calculation default settings.
  @calc_id             = 124519
  @calc_mode           = 'auto'
  @calc_on_load        = true

  if @excel2003_style
    add_format(default_formats
                 .merge(:xf_index => 0, :font_family => 0, :font => 'Arial', :size => 10, :theme => -1))
  else
    add_format(default_formats.merge(:xf_index => 0))
  end
  set_color_palette
end

Instance Attribute Details

#chartsObject (readonly)

:nodoc:


43
44
45
# File 'lib/write_xlsx/workbook.rb', line 43

def charts
  @charts
end

#doc_propertiesObject (readonly)

:nodoc:


45
46
47
# File 'lib/write_xlsx/workbook.rb', line 45

def doc_properties
  @doc_properties
end

#drawingsObject (readonly)

:nodoc:


43
44
45
# File 'lib/write_xlsx/workbook.rb', line 43

def drawings
  @drawings
end

#excel2003_styleObject (readonly)

:nodoc:


49
50
51
# File 'lib/write_xlsx/workbook.rb', line 49

def excel2003_style
  @excel2003_style
end

#firstsheet=(value) ⇒ Object

:nodoc:


41
42
43
# File 'lib/write_xlsx/workbook.rb', line 41

def firstsheet=(value)
  @firstsheet = value
end

#image_typesObject (readonly)

:nodoc:


46
47
48
# File 'lib/write_xlsx/workbook.rb', line 46

def image_types
  @image_types
end

#imagesObject (readonly)

:nodoc:


46
47
48
# File 'lib/write_xlsx/workbook.rb', line 46

def images
  @images
end

#named_rangesObject (readonly)

:nodoc:


44
45
46
# File 'lib/write_xlsx/workbook.rb', line 44

def named_ranges
  @named_ranges
end

#paletteObject (readonly)

:nodoc:


42
43
44
# File 'lib/write_xlsx/workbook.rb', line 42

def palette
  @palette
end

#shared_stringsObject (readonly)

:nodoc:


47
48
49
# File 'lib/write_xlsx/workbook.rb', line 47

def shared_strings
  @shared_strings
end

#vba_projectObject (readonly)

:nodoc:


48
49
50
# File 'lib/write_xlsx/workbook.rb', line 48

def vba_project
  @vba_project
end

#worksheetsObject (readonly)

:nodoc:


43
44
45
# File 'lib/write_xlsx/workbook.rb', line 43

def worksheets
  @worksheets
end

Instance Method Details

#activesheet=(worksheet) ⇒ Object

:nodoc:


956
957
958
# File 'lib/write_xlsx/workbook.rb', line 956

def activesheet=(worksheet) #:nodoc:
  @activesheet = worksheet
end

#add_chart(params = {}) ⇒ Object

This method is use to create a new chart either as a standalone worksheet (the default) or as an embeddable object that can be inserted into a worksheet via the Worksheet#insert_chart method.

chart = workbook.add_chart(:type => 'column')

The properties that can be set are:

:type     (required)
:subtype  (optional)
:name     (optional)
:embedded (optional)

:type

This is a required parameter. It defines the type of chart that will be created.

chart = workbook.add_chart(:type => 'line')

The available types are:

area
bar
column
line
pie
scatter
stock

:subtype

Used to define a chart subtype where available.

chart = workbook.add_chart(:type => 'bar', :subtype => 'stacked')

Currently only Bar and Column charts support subtypes (stacked and percent_stacked). See the documentation for those chart types.

:name

Set the name for the chart sheet. The name property is optional and if it isn't supplied will default to Chart1 .. n. The name must be a valid Excel worksheet name. See add_worksheet for more details on valid sheet names. The name property can be omitted for embedded charts.

chart = workbook.add_chart(:type => 'line', :name => 'Results Chart')

:embedded

Specifies that the Chart object will be inserted in a worksheet via the Worksheet#insert_chart method. It is an error to try insert a Chart that doesn't have this flag set.

chart = workbook.add_chart(:type => 'line', :embedded => 1)

# Configure the chart.
...

# Insert the chart into the a worksheet.
worksheet.insert_chart('E2', chart)

See Chart for details on how to configure the chart object once it is created. See also the chart_*.rb programs in the examples directory of the distro.


384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
# File 'lib/write_xlsx/workbook.rb', line 384

def add_chart(params = {})
  # Type must be specified so we can create the required chart instance.
  type     = params[:type]
  embedded = params[:embedded]
  name     = params[:name]
  raise "Must define chart type in add_chart()" unless type

  chart = Chart.factory(type, params[:subtype])
  chart.palette = @palette

  # If the chart isn't embedded let the workbook control it.
  if ptrue?(embedded)
    chart.name = name if name

    # Set index to 0 so that the activate() and set_first_sheet() methods
    # point back to the first worksheet if used for embedded charts.
    chart.index = 0
    chart.set_embedded_config_data
  else
    # Check the worksheet name for non-embedded charts.
    sheetname  = check_chart_sheetname(name)
    chartsheet = Chartsheet.new(self, @worksheets.size, sheetname)
    chartsheet.chart   = chart
    @worksheets << chartsheet
  end
  @charts << chart
  ptrue?(embedded) ? chart : chartsheet
end

#add_format(property_hash = {}) ⇒ Object

The add_format method can be used to create new Format objects which are used to apply formatting to a cell. You can either define the properties at creation time via a hash of property values or later via method calls.

format1 = workbook.add_format(property_hash) # Set properties at creation
format2 = workbook.add_format                # Set properties later

See the Format Class’s rdoc for more details about Format properties and how to set them.


425
426
427
428
429
430
431
432
433
434
435
436
437
# File 'lib/write_xlsx/workbook.rb', line 425

def add_format(property_hash = {})
  properties = {}
  if @excel2003_style
    properties.update(:font => 'Arial', :size => 10, :theme => -1)
  end
  properties.update(property_hash)

  format = Format.new(@formats, properties)

  @formats.formats.push(format)    # Store format reference

  format
end

#add_shape(properties = {}) ⇒ Object

The add_shape method can be used to create new shapes that may be inserted into a worksheet.

You can either define the properties at creation time via a hash of property values or later via method calls.

# Set properties at creation.
plus  = workbook.add_shape(
          :type   => 'plus',
          :id     => 3,
          :width  => pw,
          :height => ph
        )

# Default rectangle shape. Set properties later.
rect  = workbook.add_shape

See also the shape*.rb programs in the examples directory of the distro.

Shape Properties

Any shape property can be queried or modified by [ ] like hash.

ellipse = workbook.add_shape(properties)
ellipse[:type] = 'cross'    # No longer an ellipse !
type = ellipse[:type]       # Find out what it really is.

The properties of a shape object that can be defined via add_shape are shown below.

:name

Defines the name of the shape. This is an optional property and the shape will be given a default name if not supplied. The name is generally only used by Excel Macros to refer to the object.

:type

Defines the type of the object such as :rect, :ellipse OR :triangle.

ellipse = workbook.add_shape(:type => :ellipse)

The default type is :rect.

The full list of available shapes is shown below.

See also the shape_all.rb program in the examples directory of the distro. It creates an example workbook with all supported shapes labelled with their shape names.

Basic Shapes

blockArc              can            chevron       cube          decagon
diamond               dodecagon      donut         ellipse       funnel
gear6                 gear9          heart         heptagon      hexagon
homePlate             lightningBolt  line          lineInv       moon
nonIsoscelesTrapezoid noSmoking      octagon       parallelogram pentagon
pie                   pieWedge       plaque        rect          round1Rect
round2DiagRect        round2SameRect roundRect     rtTriangle    smileyFace
snip1Rect             snip2DiagRect  snip2SameRect snipRoundRect star10
star12                star16         star24        star32        star4
star5                 star6          star7         star8         sun
teardrop              trapezoid      triangle

Arrow Shapes

bentArrow        bentUpArrow       circularArrow     curvedDownArrow
curvedLeftArrow  curvedRightArrow  curvedUpArrow     downArrow
leftArrow        leftCircularArrow leftRightArrow    leftRightCircularArrow
leftRightUpArrow leftUpArrow       notchedRightArrow quadArrow
rightArrow       stripedRightArrow swooshArrow       upArrow
upDownArrow      uturnArrow

Connector Shapes

bentConnector2   bentConnector3   bentConnector4
bentConnector5   curvedConnector2 curvedConnector3
curvedConnector4 curvedConnector5 straightConnector1

Callout Shapes

accentBorderCallout1  accentBorderCallout2  accentBorderCallout3
accentCallout1        accentCallout2        accentCallout3
borderCallout1        borderCallout2        borderCallout3
callout1              callout2              callout3
cloudCallout          downArrowCallout      leftArrowCallout
leftRightArrowCallout quadArrowCallout      rightArrowCallout
upArrowCallout        upDownArrowCallout    wedgeEllipseCallout
wedgeRectCallout      wedgeRoundRectCallout

Flow Chart Shapes

flowChartAlternateProcess  flowChartCollate        flowChartConnector
flowChartDecision          flowChartDelay          flowChartDisplay
flowChartDocument          flowChartExtract        flowChartInputOutput
flowChartInternalStorage   flowChartMagneticDisk   flowChartMagneticDrum
flowChartMagneticTape      flowChartManualInput    flowChartManualOperation
flowChartMerge             flowChartMultidocument  flowChartOfflineStorage
flowChartOffpageConnector  flowChartOnlineStorage  flowChartOr
flowChartPredefinedProcess flowChartPreparation    flowChartProcess
flowChartPunchedCard       flowChartPunchedTape    flowChartSort
flowChartSummingJunction   flowChartTerminator

Action Shapes

actionButtonBackPrevious actionButtonBeginning actionButtonBlank
actionButtonDocument     actionButtonEnd       actionButtonForwardNext
actionButtonHelp         actionButtonHome      actionButtonInformation
actionButtonMovie        actionButtonReturn    actionButtonSound

Chart Shapes

Not to be confused with Excel Charts.

chartPlus chartStar chartX

Math Shapes

mathDivide mathEqual mathMinus mathMultiply mathNotEqual mathPlus

Starts and Banners

arc            bevel          bracePair  bracketPair chord
cloud          corner         diagStripe doubleWave  ellipseRibbon
ellipseRibbon2 foldedCorner   frame      halfFrame   horizontalScroll
irregularSeal1 irregularSeal2 leftBrace  leftBracket leftRightRibbon
plus           ribbon         ribbon2    rightBrace  rightBracket
verticalScroll wave

Tab Shapes

cornerTabs plaqueTabs squareTabs

:text

This property is used to make the shape act like a text box.

rect = workbook.add_shape(:type => 'rect', :text => "Hello \nWorld")

The Text is super-imposed over the shape. The text can be wrapped using the newline character n.

:id

Identification number for internal identification. This number will be auto-assigned, if not assigned, or if it is a duplicate.

:format

Workbook format for decorating the shape horizontally and/or vertically.

:rotation

Shape rotation, in degrees, from 0 to 360

:line, :fill

Shape color for the outline and fill. Colors may be specified as a color index, or in RGB format, i.e. AA00FF.

See COULOURS IN EXCEL in the main documentation for more information.

Line type for shape outline. The default is solid. The list of possible values is:

dash, sysDot, dashDot, lgDash, lgDashDot, lgDashDotDot, solid

:valign, :align

Text alignment within the shape.

Vertical alignment can be:

Setting     Meaning
=======     =======
t           Top
ctr         Centre
b           Bottom

Horizontal alignment can be:

Setting     Meaning
=======     =======
l           Left
r           Right
ctr         Centre
just        Justified

The default is to center both horizontally and vertically.

:scale_x, :scale_y

Scale factor in x and y dimension, for scaling the shape width and height. The default value is 1.

Scaling may be set on the shape object or via insert_shape.

:adjustments

Adjustment of shape vertices. Most shapes do not use this. For some shapes, there is a single adjustment to modify the geometry. For instance, the plus shape has one adjustment to control the width of the spokes.

Connectors can have a number of adjustments to control the shape routing. Typically, a connector will have 3 to 5 handles for routing the shape. The adjustment is in percent of the distance from the starting shape to the ending shape, alternating between the x and y dimension. Adjustments may be negative, to route the shape away from the endpoint.

:stencil

Shapes work in stencil mode by default. That is, once a shape is inserted, its connection is separated from its master. The master shape may be modified after an instance is inserted, and only subsequent insertions will show the modifications.

This is helpful for Org charts, where an employee shape may be created once, and then the text of the shape is modified for each employee.

The insert_shape method returns a reference to the inserted shape (the child).

Stencil mode can be turned off, allowing for shape(s) to be modified after insertion. In this case the insert_shape() method returns a reference to the inserted shape (the master). This is not very useful for inserting multiple shapes, since the x/y coordinates also gets modified.


673
674
675
676
677
678
679
680
# File 'lib/write_xlsx/workbook.rb', line 673

def add_shape(properties = {})
  shape = Shape.new(properties)
  shape.palette = @palette

  @shapes ||= []
  @shapes << shape  #Store shape reference.
  shape
end

#add_vba_project(vba_project) ⇒ Object

The add_vba_project method can be used to add macros or functions to an WriteXLSX file using a binary VBA project file that has been extracted from an existing Excel xlsm file.

workbook  = WriteXLSX.new('file.xlsm')

workbook.add_vba_project('./vbaProject.bin')

The supplied extract_vba utility can be used to extract the required vbaProject.bin file from an existing Excel file:

$ extract_vba file.xlsm
Extracted 'vbaProject.bin' successfully

Macros can be tied to buttons using the worksheet insert_button method (see the “WORKSHEET METHODS” section for details):

worksheet.insert_button('C2', { :macro => 'my_macro' })

Note, Excel uses the file extension xlsm instead of xlsx for files that contain macros. It is advisable to follow the same convention.

See also the macros.rb example file.


836
837
838
# File 'lib/write_xlsx/workbook.rb', line 836

def add_vba_project(vba_project)
  @vba_project = vba_project
end

#add_worksheet(name = '') ⇒ Object

At least one worksheet should be added to a new workbook. A worksheet is used to write data into cells:

worksheet1 = workbook.add_worksheet               # Sheet1
worksheet2 = workbook.add_worksheet('Foglio2')    # Foglio2
worksheet3 = workbook.add_worksheet('Data')       # Data
worksheet4 = workbook.add_worksheet               # Sheet4

If name is not specified the default Excel convention will be followed, i.e. Sheet1, Sheet2, etc.

The worksheet name must be a valid Excel worksheet name, i.e. it cannot contain any of the following characters,

[ ] : * ? / \

and it must be less than 32 characters. In addition, you cannot use the same, case insensitive, sheetname for more than one worksheet.


307
308
309
310
311
312
# File 'lib/write_xlsx/workbook.rb', line 307

def add_worksheet(name = '')
  name  = check_sheetname(name)
  worksheet = Worksheet.new(self, @worksheets.size, name)
  @worksheets << worksheet
  worksheet
end

#assemble_xml_fileObject

user must not use. it is internal method.


255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
# File 'lib/write_xlsx/workbook.rb', line 255

def assemble_xml_file  #:nodoc:
  return unless @writer

  # Prepare format object for passing to Style.rb.
  prepare_format_properties

  write_xml_declaration do

    # Write the root workbook element.
    write_workbook do

      # Write the XLSX file version.
      write_file_version

      # Write the workbook properties.
      write_workbook_pr

      # Write the workbook view properties.
      write_book_views

      # Write the worksheet names and ids.
      @worksheets.write_sheets(@writer)

      # Write the workbook defined names.
      write_defined_names

      # Write the workbook calculation properties.
      write_calc_pr

      # Write the workbook extension storage.
      #write_ext_lst
    end
  end
end

#chartsheet_countObject


985
986
987
# File 'lib/write_xlsx/workbook.rb', line 985

def chartsheet_count
  @worksheets.chartsheet_count
end

#chartsheetsObject


1014
1015
1016
# File 'lib/write_xlsx/workbook.rb', line 1014

def chartsheets
  @worksheets.chartsheets
end

#closeObject

The close method is used to close an Excel file.

An explicit close is required if the file must be closed prior to performing some external action on it such as copying it, reading its size or attaching it to an email.

In general, if you create a file with a size of 0 bytes or you fail to create a file you need to call close.


154
155
156
157
158
159
160
# File 'lib/write_xlsx/workbook.rb', line 154

def close
  # In case close() is called twice.
  return if @fileclosed

  @fileclosed = true
  store_workbook
end

#date_1904?Boolean

:nodoc:

Returns:

  • (Boolean)

964
965
966
967
# File 'lib/write_xlsx/workbook.rb', line 964

def date_1904? #:nodoc:
  @date_1904 ||= false
  !!@date_1904
end

#define_name(name, formula) ⇒ Object

Create a defined name in Excel. We handle global/workbook level names and local/worksheet names.

This method is used to defined a name that can be used to represent a value, a single cell or a range of cells in a workbook.

For example to set a global/workbook name:

# Global/workbook names.
workbook.define_name('Exchange_rate', '=0.96')
workbook.define_name('Sales',         '=Sheet1!$G$1:$H$10')

It is also possible to define a local/worksheet name by prefixing the name with the sheet name using the syntax sheetname!definedname:

# Local/worksheet name.
workbook.define_name('Sheet2!Sales',  '=Sheet2!$G$1:$G$10')

If the sheet name contains spaces or special characters you must enclose it in single quotes like in Excel:

workbook.define_name("'New Data'!Sales",  '=Sheet2!$G$1:$G$10')

See the defined_name.rb program in the examples dir of the distro.

Refer to the following to see Excel's syntax rules for defined names: <office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names>


711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
# File 'lib/write_xlsx/workbook.rb', line 711

def define_name(name, formula)
  sheet_index = nil
  sheetname   = ''

  # Local defined names are formatted like "Sheet1!name".
  if name =~ /^(.*)!(.*)$/
    sheetname   = $1
    name        = $2
    sheet_index = @worksheets.index_by_name(sheetname)
  else
    sheet_index = -1   # Use -1 to indicate global names.
  end

  # Raise if the sheet index wasn't found.
  if !sheet_index
   raise "Unknown sheet name #{sheetname} in defined_name()\n"
  end

  # Raise if the name contains invalid chars as defined by Excel help.
  # Refer to the following to see Excel's syntax rules for defined names:
  # http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names
  #
  if name =~ /\A[-0-9 !"#\$%&'\(\)\*\+,\.:;<=>\[email protected]\[\]\^`\{\}~]/ || name =~ /.+[- !"#\$%&'\(\)\*\+,\\:;<=>\[email protected]\[\]\^`\{\}~]/
    raise "Invalid characters in name '#{name}' used in defined_name()\n"
  end

  # Raise if the name looks like a cell name.
  if name =~ %r(^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$)
    raise "Invalid name '#{name}' looks like a cell name in defined_name()\n"
  end

  # Raise if the name looks like a R1C1
  if name =~ /\A[rcRC]\Z/ || name =~ /\A[rcRC]\d+[rcRC]\d+\Z/
    raise "Invalid name '#{name}' like a RC cell ref in defined_name()\n"
  end

  @defined_names.push([ name, sheet_index, formula.sub(/^=/, '') ])
end

#get_1904Object

return date system. false = 1900, true = 1904


230
231
232
# File 'lib/write_xlsx/workbook.rb', line 230

def get_1904
  @date_1904
end

#non_chartsheet_countObject


989
990
991
# File 'lib/write_xlsx/workbook.rb', line 989

def non_chartsheet_count
  @worksheets.worksheets.count
end

#non_chartsheetsObject


1018
1019
1020
# File 'lib/write_xlsx/workbook.rb', line 1018

def non_chartsheets
  @worksheets.worksheets
end

#num_comment_filesObject


1010
1011
1012
# File 'lib/write_xlsx/workbook.rb', line 1010

def num_comment_files
  @worksheets.select { |sheet| sheet.has_comments? }.count
end

#num_vml_filesObject


1006
1007
1008
# File 'lib/write_xlsx/workbook.rb', line 1006

def num_vml_files
  @worksheets.select { |sheet| sheet.has_vml? || sheet.has_header_vml? }.count
end

#set_1904(mode = true) ⇒ Object

Set the date system: false = 1900 (the default), true = 1904

Excel stores dates as real numbers where the integer part stores the number of days since the epoch and the fractional part stores the percentage of the day. The epoch can be either 1900 or 1904. Excel for Windows uses 1900 and Excel for Macintosh uses 1904. However, Excel on either platform will convert automatically between one system and the other.

WriteXLSX stores dates in the 1900 format by default. If you wish to change this you can call the set_1904 workbook method. You can query the current value by calling the get_1904 workbook method. This returns false for 1900 and true for 1904.

In general you probably won't need to use set_1904.


220
221
222
223
224
225
# File 'lib/write_xlsx/workbook.rb', line 220

def set_1904(mode = true)
  unless sheets.empty?
    raise "set_1904() must be called before add_worksheet()"
  end
  @date_1904 = ptrue?(mode)
end

#set_calc_mode(mode, calc_id = nil) ⇒ Object

set_calc_mode()

Set the Excel caclcuation mode for the workbook.


856
857
858
859
860
861
862
863
864
865
866
# File 'lib/write_xlsx/workbook.rb', line 856

def set_calc_mode(mode, calc_id = nil)
  @calc_mode = mode || 'auto'

  if mode == 'manual'
      @calc_on_load = false
  elsif mode == 'auto_except_tables'
    @calc_mode = 'autoNoTable'
  end

  @calc_id = calc_id if calc_id
end

#set_custom_color(index, red = 0, green = 0, blue = 0) ⇒ Object

Change the RGB components of the elements in the colour palette.

The set_custom_color method can be used to override one of the built-in palette values with a more suitable colour.

The value for index should be in the range 8..63, see “COLOURS IN EXCEL”.

The default named colours use the following indices:

 8   =>   black
 9   =>   white
10   =>   red
11   =>   lime
12   =>   blue
13   =>   yellow
14   =>   magenta
15   =>   cyan
16   =>   brown
17   =>   green
18   =>   navy
20   =>   purple
22   =>   silver
23   =>   gray
33   =>   pink
53   =>   orange

A new colour is set using its RGB (red green blue) components. The red, green and blue values must be in the range 0..255. You can determine the required values in Excel using the Tools->Options->Colors->Modify dialog.

The set_custom_color workbook method can also be used with a HTML style #rrggbb hex value:

workbook.set_custom_color(40, 255,  102,  0   ) # Orange
workbook.set_custom_color(40, 0xFF, 0x66, 0x00) # Same thing
workbook.set_custom_color(40, '#FF6600'       ) # Same thing

font = workbook.add_format(:color => 40)   # Use the modified colour

The return value from set_custom_color() is the index of the colour that was changed:

ferrari = workbook.set_custom_color(40, 216, 12, 12)

format  = workbook.add_format(
                            :bg_color => ferrari,
                            :pattern  => 1,
                            :border   => 1
                       )

Note, In the XLSX format the color palette isn't actually confined to 53 unique colors. The WriteXLSX gem will be extended at a later stage to support the newer, semi-infinite, palette.


925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
# File 'lib/write_xlsx/workbook.rb', line 925

def set_custom_color(index, red = 0, green = 0, blue = 0)
  # Match a HTML #xxyyzz style parameter
  if red =~ /^#(\w\w)(\w\w)(\w\w)/
    red   = $1.hex
    green = $2.hex
    blue  = $3.hex
  end

  # Check that the colour index is the right range
  if index < 8 || index > 64
    raise "Color index #{index} outside range: 8 <= index <= 64"
  end

  # Check that the colour components are in the right range
  if (red   < 0 || red   > 255) ||
     (green < 0 || green > 255) ||
     (blue  < 0 || blue  > 255)
    raise "Color component outside range: 0 <= color <= 255"
  end

  index -=8       # Adjust colour index (wingless dragonfly)

  # Set the RGB value
  @palette[index] = [red, green, blue]

  # Store the custome colors for the style.xml file.
  @custom_colors << sprintf("FF%02X%02X%02X", red, green, blue)

  index + 8
end

#set_properties(params) ⇒ Object

The set_properties method can be used to set the document properties of the Excel file created by WriteXLSX. These properties are visible when you use the Office Button -> Prepare -> Properties option in Excel and are also available to external applications that read or index windows files.

The properties should be passed in hash format as follows:

workbook.set_properties(
  :title    => 'This is an example spreadsheet',
  :author   => 'Hideo NAKAMURA',
  :comments => 'Created with Ruby and WriteXLSX'
)

The properties that can be set are:

:title
:subject
:author
:manager
:company
:category
:keywords
:comments
:status

See also the properties.rb program in the examples directory of the distro.


780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
# File 'lib/write_xlsx/workbook.rb', line 780

def set_properties(params)
  # Ignore if no args were passed.
  return -1 if params.empty?

  # List of valid input parameters.
  valid = {
    :title       => 1,
    :subject     => 1,
    :author      => 1,
    :keywords    => 1,
    :comments    => 1,
    :last_author => 1,
    :created     => 1,
    :category    => 1,
    :manager     => 1,
    :company     => 1,
    :status      => 1
  }

  # Check for valid input parameters.
  params.each_key do |key|
    return -1 unless valid.has_key?(key)
  end

  # Set the creation time unless specified by the user.
  params[:created] = @local_time unless params.has_key?(:created)

  @doc_properties = params.dup
end

#set_tempdir(dir) ⇒ Object


234
235
236
# File 'lib/write_xlsx/workbook.rb', line 234

def set_tempdir(dir)
  @tempdir = dir.dup
end

#set_vba_name(vba_codename = nil) ⇒ Object

Set the VBA name for the workbook.


843
844
845
846
847
848
849
# File 'lib/write_xlsx/workbook.rb', line 843

def set_vba_name(vba_codename = nil)
  if vba_codename
    @vba_codename = vba_codename
  else
    @vba_codename = 'ThisWorkbook'
  end
end

#set_xml_writer(filename) ⇒ Object

user must not use. it is internal method.


241
242
243
# File 'lib/write_xlsx/workbook.rb', line 241

def set_xml_writer(filename)  #:nodoc:
  @writer.set_xml_writer(filename)
end

#shared_string_index(str, params = {}) ⇒ Object

Add a string to the shared string table, if it isn't already there, and return the string index.


973
974
975
# File 'lib/write_xlsx/workbook.rb', line 973

def shared_string_index(str, params = {}) #:nodoc:
  @shared_strings.index(str, params)
end

#shared_strings_empty?Boolean

:nodoc:

Returns:

  • (Boolean)

981
982
983
# File 'lib/write_xlsx/workbook.rb', line 981

def shared_strings_empty?  # :nodoc:
  @shared_strings.empty?
end

#sheets(*args) ⇒ Object

get array of Worksheet objects

:call-seq:

sheets              -> array of all Wordsheet object
sheets(1, 3, 4)     -> array of spcified Worksheet object.

The sheets method returns a array, or a sliced array, of the worksheets in a workbook.

If no arguments are passed the method returns a list of all the worksheets in the workbook. This is useful if you want to repeat an operation on each worksheet:

workbook.sheets.each do |worksheet|
   print worksheet.get_name
end

You can also specify a slice list to return one or more worksheet objects:

worksheet = workbook.sheets(0)
worksheet.write('A1', 'Hello')

you can write the above example as:

workbook.sheets(0).write('A1', 'Hello')

The following example returns the first and last worksheet in a workbook:

workbook.sheets(0, -1).each do |sheet|
   # Do something
end

195
196
197
198
199
200
201
# File 'lib/write_xlsx/workbook.rb', line 195

def sheets(*args)
  if args.empty?
    @worksheets
  else
    args.collect{|i| @worksheets[i] }
  end
end

#str_uniqueObject

:nodoc:


977
978
979
# File 'lib/write_xlsx/workbook.rb', line 977

def str_unique   # :nodoc:
  @shared_strings.unique_count
end

#style_propertiesObject


993
994
995
996
997
998
999
1000
1001
1002
1003
1004
# File 'lib/write_xlsx/workbook.rb', line 993

def style_properties
  [
   @xf_formats,
   @palette,
   @font_count,
   @num_format_count,
   @border_count,
   @fill_count,
   @custom_colors,
   @dxf_formats
  ]
end

#writerObject

:nodoc:


960
961
962
# File 'lib/write_xlsx/workbook.rb', line 960

def writer #:nodoc:
  @writer
end

#xml_strObject

user must not use. it is internal method.


248
249
250
# File 'lib/write_xlsx/workbook.rb', line 248

def xml_str  #:nodoc:
  @writer.string
end