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, #ptrue?, #put_deprecate_message, #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, default_formats = {}) ⇒ 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)



90
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
 
# File 'lib/write_xlsx/workbook.rb', line 90

def initialize(file, default_formats = {})
  @writer = Package::XMLWriterSimple.new

  @tempdir  = File.join(Dir.tmpdir, Digest::MD5.hexdigest(Time.now.to_s))
  setup_filename(file)
  @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        = 0
  @x_window            = 240
  @y_window            = 15
  @window_width        = 16095
  @window_height       = 9660
  @tab_ratio           = 500
  @table_count         = 0
  @image_types         = {}
  @images              = []

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

  add_format(default_formats.merge(:xf_index => 0))
  set_color_palette
end

Instance Attribute Details

#charts ⇒ Object (readonly)

:nodoc:



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

def charts
  @charts
end

#doc_properties ⇒ Object (readonly)

:nodoc:



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

def doc_properties
  @doc_properties
end

#drawings ⇒ Object (readonly)

:nodoc:



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

def drawings
  @drawings
end

#firstsheet=(value) ⇒ Object

:nodoc:



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

def firstsheet=(value)
  @firstsheet = value
end

#image_types ⇒ Object (readonly)

:nodoc:



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

def image_types
  @image_types
end

#images ⇒ Object (readonly)

:nodoc:



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

def images
  @images
end

#named_ranges ⇒ Object (readonly)

:nodoc:



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

def named_ranges
  @named_ranges
end

#palette ⇒ Object (readonly)

:nodoc:



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

def palette
  @palette
end

#shared_strings ⇒ Object (readonly)

:nodoc:



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

def shared_strings
  @shared_strings
end

#vba_project ⇒ Object (readonly)

:nodoc:



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

def vba_project
  @vba_project
end

#worksheets ⇒ Object (readonly)

:nodoc:



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

def worksheets
  @worksheets
end

Instance Method Details

#activesheet=(worksheet) ⇒ Object

:nodoc:



904
905
906
 
# File 'lib/write_xlsx/workbook.rb', line 904

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.



366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
 
# File 'lib/write_xlsx/workbook.rb', line 366

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(properties = {}) ⇒ 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.



407
408
409
410
411
412
413
 
# File 'lib/write_xlsx/workbook.rb', line 407

def add_format(properties = {})
  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.



649
650
651
652
653
654
655
656
 
# File 'lib/write_xlsx/workbook.rb', line 649

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.



812
813
814
 
# File 'lib/write_xlsx/workbook.rb', line 812

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.



289
290
291
292
293
294
 
# File 'lib/write_xlsx/workbook.rb', line 289

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

#assemble_xml_file ⇒ Object

user must not use. it is internal method.



237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
 
# File 'lib/write_xlsx/workbook.rb', line 237

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_count ⇒ Object 



933
934
935
 
# File 'lib/write_xlsx/workbook.rb', line 933

def chartsheet_count
  @worksheets.chartsheet_count
end

#close ⇒ Object

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.



140
141
142
143
144
145
146
 
# File 'lib/write_xlsx/workbook.rb', line 140

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

  @fileclosed = true
  store_workbook
end

#date_1904? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


912
913
914
915
 
# File 'lib/write_xlsx/workbook.rb', line 912

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>



687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
 
# File 'lib/write_xlsx/workbook.rb', line 687

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 !"#\$%&'\(\)\*\+,\.:;<=>\?@\[\]\^`\{\}~]/ || name =~ /.+[- !"#\$%&'\(\)\*\+,\\:;<=>\?@\[\]\^`\{\}~]/
    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_1904 ⇒ Object

return date system. false = 1900, true = 1904



216
217
218
 
# File 'lib/write_xlsx/workbook.rb', line 216

def get_1904
  @date_1904
end

#num_comment_files ⇒ Object 



954
955
956
 
# File 'lib/write_xlsx/workbook.rb', line 954

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

#num_vml_files ⇒ Object 



950
951
952
 
# File 'lib/write_xlsx/workbook.rb', line 950

def num_vml_files
  @worksheets.select { |sheet| sheet.has_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.



206
207
208
209
210
211
 
# File 'lib/write_xlsx/workbook.rb', line 206

def set_1904(mode = true)
  unless sheets.empty?
    raise "set_1904() must be called before add_worksheet()"
  end
  @date_1904 = ptrue?(mode)
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.



873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
 
# File 'lib/write_xlsx/workbook.rb', line 873

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.



756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
 
# File 'lib/write_xlsx/workbook.rb', line 756

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_xml_writer(filename) ⇒ Object

user must not use. it is internal method.



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

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.



921
922
923
 
# File 'lib/write_xlsx/workbook.rb', line 921

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

#shared_strings_empty? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


929
930
931
 
# File 'lib/write_xlsx/workbook.rb', line 929

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



181
182
183
184
185
186
187
 
# File 'lib/write_xlsx/workbook.rb', line 181

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

#str_unique ⇒ Object

:nodoc:



925
926
927
 
# File 'lib/write_xlsx/workbook.rb', line 925

def str_unique   # :nodoc:
  @shared_strings.unique_count
end

#style_properties ⇒ Object 



937
938
939
940
941
942
943
944
945
946
947
948
 
# File 'lib/write_xlsx/workbook.rb', line 937

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

#writer ⇒ Object

:nodoc:



908
909
910
 
# File 'lib/write_xlsx/workbook.rb', line 908

def writer #:nodoc:
  @writer
end

#xml_str ⇒ Object

user must not use. it is internal method.



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

def xml_str  #:nodoc:
  @writer.string
end