Class: Writexlsx::Workbook

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

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, delete_files, #ptrue?, #put_deprecate_message, #row_col_notation, #store_col_max_min_values, #store_row_max_min_values, #substitute_cellref, #underline_attributes, #write_color, #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)



73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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
 
# File 'lib/write_xlsx/workbook.rb', line 73

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
  @sheet_name          = 'Sheet'
  @chart_name          = 'Chart'
  @sheetname_count     = 0
  @chartname_count     = 0
  @worksheets          = []
  @charts              = []
  @drawings            = []
  @sheetnames          = []
  @formats             = []
  @xf_formats          = []
  @xf_format_indices   = {}
  @dxf_formats         = []
  @dxf_format_indices  = {}
  @font_count          = 0
  @num_format_count    = 0
  @defined_names       = []
  @named_ranges        = []
  @custom_colors       = []
  @doc_properties      = {}
  @local_time          = Time.now
  @num_comment_files   = 0
  @optimization        = 0
  @x_window            = 240
  @y_window            = 15
  @window_width        = 16095
  @window_height       = 9660
  @tab_ratio           = 500
  @table_count         = 0
  @image_types         = {}
  @images              = []
  @images_seen         = {}

  # 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

#border_count ⇒ Object (readonly)

Returns the value of attribute border_count.



22
23
24
 
# File 'lib/write_xlsx/workbook.rb', line 22

def border_count
  @border_count
end

#charts ⇒ Object (readonly)

Returns the value of attribute charts.



23
24
25
 
# File 'lib/write_xlsx/workbook.rb', line 23

def charts
  @charts
end

#custom_colors ⇒ Object (readonly)

Returns the value of attribute custom_colors.



22
23
24
 
# File 'lib/write_xlsx/workbook.rb', line 22

def custom_colors
  @custom_colors
end

#doc_properties ⇒ Object (readonly)

Returns the value of attribute doc_properties.



24
25
26
 
# File 'lib/write_xlsx/workbook.rb', line 24

def doc_properties
  @doc_properties
end

#drawings ⇒ Object (readonly)

Returns the value of attribute drawings.



23
24
25
 
# File 'lib/write_xlsx/workbook.rb', line 23

def drawings
  @drawings
end

#fill_count ⇒ Object (readonly)

Returns the value of attribute fill_count.



22
23
24
 
# File 'lib/write_xlsx/workbook.rb', line 22

def fill_count
  @fill_count
end

#firstsheet=(value) ⇒ Object

Sets the attribute firstsheet

Parameters:

  • value —

    the value to set the attribute firstsheet to.



20
21
22
 
# File 'lib/write_xlsx/workbook.rb', line 20

def firstsheet=(value)
  @firstsheet = value
end

#font_count ⇒ Object (readonly)

Returns the value of attribute font_count.



22
23
24
 
# File 'lib/write_xlsx/workbook.rb', line 22

def font_count
  @font_count
end

#image_types ⇒ Object (readonly)

Returns the value of attribute image_types.



25
26
27
 
# File 'lib/write_xlsx/workbook.rb', line 25

def image_types
  @image_types
end

#images ⇒ Object (readonly)

Returns the value of attribute images.



25
26
27
 
# File 'lib/write_xlsx/workbook.rb', line 25

def images
  @images
end

#named_ranges ⇒ Object (readonly)

Returns the value of attribute named_ranges.



23
24
25
 
# File 'lib/write_xlsx/workbook.rb', line 23

def named_ranges
  @named_ranges
end

#num_comment_files ⇒ Object (readonly)

Returns the value of attribute num_comment_files.



23
24
25
 
# File 'lib/write_xlsx/workbook.rb', line 23

def num_comment_files
  @num_comment_files
end

#num_format_count ⇒ Object (readonly)

Returns the value of attribute num_format_count.



22
23
24
 
# File 'lib/write_xlsx/workbook.rb', line 22

def num_format_count
  @num_format_count
end

#palette ⇒ Object (readonly)

Returns the value of attribute palette.



21
22
23
 
# File 'lib/write_xlsx/workbook.rb', line 21

def palette
  @palette
end

#shared_strings ⇒ Object (readonly)

Returns the value of attribute shared_strings.



26
27
28
 
# File 'lib/write_xlsx/workbook.rb', line 26

def shared_strings
  @shared_strings
end

#sheetnames ⇒ Object (readonly)

Returns the value of attribute sheetnames.



23
24
25
 
# File 'lib/write_xlsx/workbook.rb', line 23

def sheetnames
  @sheetnames
end

#table_count ⇒ Object

Returns the value of attribute table_count.



27
28
29
 
# File 'lib/write_xlsx/workbook.rb', line 27

def table_count
  @table_count
end

#vba_project ⇒ Object (readonly)

Returns the value of attribute vba_project.



28
29
30
 
# File 'lib/write_xlsx/workbook.rb', line 28

def vba_project
  @vba_project
end

#worksheets ⇒ Object (readonly)

Returns the value of attribute worksheets.



23
24
25
 
# File 'lib/write_xlsx/workbook.rb', line 23

def worksheets
  @worksheets
end

Instance Method Details

#activesheet=(worksheet) ⇒ Object

:nodoc:



854
855
856
 
# File 'lib/write_xlsx/workbook.rb', line 854

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_*.pl programs in the examples directory of the distro.



352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
 
# File 'lib/write_xlsx/workbook.rb', line 352

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
    @sheetnames << sheetname
  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.



393
394
395
396
397
398
399
400
401
402
403
404
405
 
# File 'lib/write_xlsx/workbook.rb', line 393

def add_format(properties = {})
  init_data = [
    @xf_format_indices,
    @dxf_format_indices,
    properties
  ]

  format = Format.new(*init_data)

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

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    def add_shape(properties)

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.



630
631
632
633
634
635
636
637
 
# File 'lib/write_xlsx/workbook.rb', line 630

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

Add a vbaProject binary to the XLSX file.



763
764
765
 
# File 'lib/write_xlsx/workbook.rb', line 763

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.



275
276
277
278
279
280
281
 
# File 'lib/write_xlsx/workbook.rb', line 275

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

#assemble_xml_file ⇒ Object

user must not use. it is internal method.



218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
 
# File 'lib/write_xlsx/workbook.rb', line 218

def assemble_xml_file  #:nodoc:
  return unless @writer

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

  write_xml_declaration

  # Write the root workbook element.
  write_workbook

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

  # Write the workbook defined names.
  write_defined_names

  # Write the workbook calculation properties.
  write_calc_pr

  # Write the workbook extension storage.
  #write_ext_lst

  # Close the workbook tag.
  write_workbook_end

  # Close the XML writer object and filehandle.
  @writer.crlf
  @writer.close
end

#close ⇒ Object

write XLSX data to file or IO object.



125
126
127
128
129
130
131
 
# File 'lib/write_xlsx/workbook.rb', line 125

def close
  # In case close() is called twice, by user and by DESTROY.
  return if @fileclosed

  @fileclosed = true
  store_workbook
end

#date_1904? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


862
863
864
865
 
# File 'lib/write_xlsx/workbook.rb', line 862

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.



665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
 
# File 'lib/write_xlsx/workbook.rb', line 665

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

  # Remove the = sign from the formula if it exists.
  formula.sub!(/^=/, '')

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

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

  # Warn if the sheet name contains invalid chars as defined by Excel help.
  if name !~ %r!^[a-zA-Z_\\][a-zA-Z_.]+!
   raise "Invalid characters in name '#{name}' used in defined_name()\n"
  end

  # Warn if the sheet 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

  @defined_names.push([ name, sheet_index, formula])
end

#dxf_formats ⇒ Object

:nodoc:



887
888
889
 
# File 'lib/write_xlsx/workbook.rb', line 887

def dxf_formats    # :nodoc:
  @dxf_formats.dup
end

#get_1904 ⇒ Object 



197
198
199
 
# File 'lib/write_xlsx/workbook.rb', line 197

def get_1904
  @date_1904
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 0 for 1900 and 1 for 1904.

In general you probably won’t need to use set_1904().



190
191
192
193
194
195
 
# File 'lib/write_xlsx/workbook.rb', line 190

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.



823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
 
# File 'lib/write_xlsx/workbook.rb', line 823

def set_custom_color(index, red = 0, green = 0, blue = 0)
  # Match a HTML #xxyyzz style parameter
  if !red.nil? && 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.



730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
 
# File 'lib/write_xlsx/workbook.rb', line 730

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.



204
205
206
 
# File 'lib/write_xlsx/workbook.rb', line 204

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

#shared_string_index(str) ⇒ Object

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



871
872
873
 
# File 'lib/write_xlsx/workbook.rb', line 871

def shared_string_index(str) #:nodoc:
  @shared_strings.index(str)
end

#shared_strings_empty? ⇒ Boolean

Returns:

  • (Boolean) 


879
880
881
 
# File 'lib/write_xlsx/workbook.rb', line 879

def shared_strings_empty?
  @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



165
166
167
168
169
170
171
 
# File 'lib/write_xlsx/workbook.rb', line 165

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

#str_unique ⇒ Object 



875
876
877
 
# File 'lib/write_xlsx/workbook.rb', line 875

def str_unique
  @shared_strings.unique_count
end

#writer ⇒ Object

:nodoc:



858
859
860
 
# File 'lib/write_xlsx/workbook.rb', line 858

def writer #:nodoc:
  @writer
end

#xf_formats ⇒ Object

:nodoc:



883
884
885
 
# File 'lib/write_xlsx/workbook.rb', line 883

def xf_formats     # :nodoc:
  @xf_formats.dup
end

#xml_str ⇒ Object

user must not use. it is internal method.



211
212
213
 
# File 'lib/write_xlsx/workbook.rb', line 211

def xml_str  #:nodoc:
  @writer.string
end