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, delete_files, #put_deprecate_message, #substitute_cellref, #underline_attributes, #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 a 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)



69
70
71
72
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
 
# File 'lib/write_xlsx/workbook.rb', line 69

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

#border_count ⇒ Object (readonly)

Returns the value of attribute border_count.



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

def border_count
  @border_count
end

#charts ⇒ Object (readonly)

Returns the value of attribute charts.



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

def charts
  @charts
end

#custom_colors ⇒ Object (readonly)

Returns the value of attribute custom_colors.



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

def custom_colors
  @custom_colors
end

#doc_properties ⇒ Object (readonly)

Returns the value of attribute doc_properties.



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

def doc_properties
  @doc_properties
end

#drawings ⇒ Object (readonly)

Returns the value of attribute drawings.



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

def drawings
  @drawings
end

#fill_count ⇒ Object (readonly)

Returns the value of attribute fill_count.



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

def fill_count
  @fill_count
end

#firstsheet=(value) ⇒ Object

Sets the attribute firstsheet

Parameters:

  • value —

    the value to set the attribute firstsheet to.



18
19
20
 
# File 'lib/write_xlsx/workbook.rb', line 18

def firstsheet=(value)
  @firstsheet = value
end

#font_count ⇒ Object (readonly)

Returns the value of attribute font_count.



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

def font_count
  @font_count
end

#image_types ⇒ Object (readonly)

Returns the value of attribute image_types.



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

def image_types
  @image_types
end

#images ⇒ Object (readonly)

Returns the value of attribute images.



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

def images
  @images
end

#named_ranges ⇒ Object (readonly)

Returns the value of attribute named_ranges.



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

def named_ranges
  @named_ranges
end

#num_comment_files ⇒ Object (readonly)

Returns the value of attribute num_comment_files.



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

def num_comment_files
  @num_comment_files
end

#num_format_count ⇒ Object (readonly)

Returns the value of attribute num_format_count.



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

def num_format_count
  @num_format_count
end

#palette ⇒ Object (readonly)

Returns the value of attribute palette.



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

def palette
  @palette
end

#shared_strings ⇒ Object (readonly)

Returns the value of attribute shared_strings.



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

def shared_strings
  @shared_strings
end

#sheetnames ⇒ Object (readonly)

Returns the value of attribute sheetnames.



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

def sheetnames
  @sheetnames
end

#worksheets ⇒ Object (readonly)

Returns the value of attribute worksheets.



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

def worksheets
  @worksheets
end

Instance Method Details

#activesheet=(worksheet) ⇒ Object

:nodoc:



616
617
618
 
# File 'lib/write_xlsx/workbook.rb', line 616

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 insert_chart() Worksheet 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 insert_chart Worksheet 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.



341
342
343
344
345
346
347
348
349
350
351
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
381
382
 
# File 'lib/write_xlsx/workbook.rb', line 341

def add_chart(params = {})
  name     = ''
  index    = @worksheets.size

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

  # Ensure that the chart defaults to non embedded.
  embedded = params[:embedded] || 0

  # Check the worksheet name for non-embedded charts.
  name = check_sheetname(params[:name], 1) unless embedded

  chart = Chart.factory(type)

  # Get an incremental id to use for axes ids.
  chart.id = @charts.size

  # If the chart isn't embedded let the workbook control it.
  if embedded
    # 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.palette = @palette
    chart.set_embedded_config_data
    @charts << chart

    return chart
  else
    chartsheet = Chartsheet.new(self, name, index)
    chart.palette = @palette
    chartsheet.chart   = chart
    chartsheet.drawing = Drawing.new
    @worksheets.index = chartsheet
    @sheetnames.index = name

    @charts << chart

    return chartsheet
  end
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.



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

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



263
264
265
266
267
268
269
270
271
272
 
# File 'lib/write_xlsx/workbook.rb', line 263

def add_worksheet(name = '')
  index = @worksheets.size
  name  = check_sheetname(name)

  worksheet = Worksheet.new(self, index, name)
  @worksheets[index] = worksheet
  @sheetnames[index] = name

  worksheet
end

#assemble_xml_file ⇒ Object

user must not use. it is internal method.



206
207
208
209
210
211
212
213
214
215
216
217
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
 
# File 'lib/write_xlsx/workbook.rb', line 206

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.



113
114
115
116
117
118
119
 
# File 'lib/write_xlsx/workbook.rb', line 113

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

  @fileclosed = 1
  store_workbook
end

#date_1904? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


624
625
626
627
 
# File 'lib/write_xlsx/workbook.rb', line 624

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.



434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
 
# File 'lib/write_xlsx/workbook.rb', line 434

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"
   return -1
  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"
   return -1
  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"
    return -1
  end

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

#dxf_formats ⇒ Object

:nodoc:



649
650
651
 
# File 'lib/write_xlsx/workbook.rb', line 649

def dxf_formats    # :nodoc:
  @dxf_formats.dup
end

#get_1904 ⇒ Object 



185
186
187
 
# File 'lib/write_xlsx/workbook.rb', line 185

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



178
179
180
181
182
183
 
# File 'lib/write_xlsx/workbook.rb', line 178

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



585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
 
# File 'lib/write_xlsx/workbook.rb', line 585

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.



500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
 
# File 'lib/write_xlsx/workbook.rb', line 500

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.



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

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.



633
634
635
 
# File 'lib/write_xlsx/workbook.rb', line 633

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

#shared_strings_empty? ⇒ Boolean

Returns:

  • (Boolean) 


641
642
643
 
# File 'lib/write_xlsx/workbook.rb', line 641

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



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

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

#str_unique ⇒ Object 



637
638
639
 
# File 'lib/write_xlsx/workbook.rb', line 637

def str_unique
  @shared_strings.unique_count
end

#writer ⇒ Object

:nodoc:



620
621
622
 
# File 'lib/write_xlsx/workbook.rb', line 620

def writer #:nodoc:
  @writer
end

#xf_formats ⇒ Object

:nodoc:



645
646
647
 
# File 'lib/write_xlsx/workbook.rb', line 645

def xf_formats     # :nodoc:
  @xf_formats.dup
end

#xml_str ⇒ Object

user must not use. it is internal method.



199
200
201
 
# File 'lib/write_xlsx/workbook.rb', line 199

def xml_str  #:nodoc:
  @writer.string
end