Class: Workbook

Inherits:
BIFFWriter show all
Defined in:
lib/writeexcel/workbook.rb

Direct Known Subclasses

WriteExcel

Constant Summary collapse

NonAscii = 
/[^!"#\$%&'\(\)\*\+,\-\.\/\:\;<=>\?@0-9A-Za-z_\[\\\]\{\}^` ~\0\n]/
BOF =

:nodoc:

11
EOF =

:nodoc:

4
SheetName =

:nodoc:

"Sheet"

Constants inherited from BIFFWriter

BIFFWriter::BIFF_Version, BIFFWriter::BigEndian

Instance Attribute Summary

Attributes inherited from BIFFWriter

#byte_order, #data, #datasize

Instance Method Summary collapse

Methods inherited from BIFFWriter

#add_continue, #add_mso_generic, #append, #clear_data_for_test, #get_data, #not_using_tmpfile, #prepend, #print_caller_info, #set_byte_order, #store_bof, #store_eof

Methods included from CallerInfo

#caller_info

Methods inherited from WriteFile

#append, #prepend

Constructor Details

#initialize(file, default_formats = {}) ⇒ Workbook

file is a filename (as string) or io object where to out spreadsheet data. you can set default format of workbook using default_formats.

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

workbook  = WriteExcel.new('filename.xls')
worksheet = workbook.add_worksheet
worksheet.write(0, 0, 'Hi Excel!')

Here are some other examples of using new() with filenames:

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

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.

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

If the file cannot be created, due to file permissions or some other reason, new will return undef. Therefore, it is good practice to check the return value of new before proceeding.

workbook  = WriteExcel.new('protected.xls')
die "Problems creating new Excel file:" if workbook.nil?

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



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
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
 
# File 'lib/writeexcel/workbook.rb', line 70

def initialize(file, default_formats = {})
  super()
  @file                  = file
  @default_formats       = default_formats
  @parser                = Writeexcel::Formula.new(@byte_order)
  @tempdir               = nil
  @date_1904             = false
  @sheet                 =

  @selected              = 0
  @xf_index              = 0
  @fileclosed            = false
  @biffsize              = 0
  @sheet_name            = "Sheet"
  @chart_name            = "Chart"
  @sheet_count           = 0
  @chart_count           = 0
  @url_format            = ''
  @codepage              = 0x04E4
  @country               = 1
  @worksheets            = []
  @sheetnames            = []
  @formats               = []
  @palette               = []
  @biff_only             = 0

  @internal_fh           = 0
  @fh_out                = ""

  @sinfo = {
    :activesheet         => 0,
    :firstsheet          => 0,
    :str_total           => 0,
    :str_unique          => 0,
    :str_table           => {}
  }
  @str_array             = []
  @str_block_sizes       = []
  @extsst_offsets        = []  # array of [global_offset, local_offset]
  @extsst_buckets        = 0
  @extsst_bucket_size    = 0

  @ext_refs              = {}

  @mso_clusters          = []
  @mso_size              = 0

  @hideobj               = 0
  @compatibility         = 0

  @add_doc_properties    = 0
  @summary               = ''
  @doc_summary           = ''
  @localtime             = Time.now

  @defined_names         = []

  # Add the in-built style formats and the default cell format.
  add_format(:type => 1)                        #  0 Normal
  add_format(:type => 1)                        #  1 RowLevel 1
  add_format(:type => 1)                        #  2 RowLevel 2
  add_format(:type => 1)                        #  3 RowLevel 3
  add_format(:type => 1)                        #  4 RowLevel 4
  add_format(:type => 1)                        #  5 RowLevel 5
  add_format(:type => 1)                        #  6 RowLevel 6
  add_format(:type => 1)                        #  7 RowLevel 7
  add_format(:type => 1)                        #  8 ColLevel 1
  add_format(:type => 1)                        #  9 ColLevel 2
  add_format(:type => 1)                        # 10 ColLevel 3
  add_format(:type => 1)                        # 11 ColLevel 4
  add_format(:type => 1)                        # 12 ColLevel 5
  add_format(:type => 1)                        # 13 ColLevel 6
  add_format(:type => 1)                        # 14 ColLevel 7
  add_format(default_formats)                   # 15 Cell XF
  add_format(:type => 1, :num_format => 0x2B)   # 16 Comma
  add_format(:type => 1, :num_format => 0x29)   # 17 Comma[0]
  add_format(:type => 1, :num_format => 0x2C)   # 18 Currency
  add_format(:type => 1, :num_format => 0x2A)   # 19 Currency[0]
  add_format(:type => 1, :num_format => 0x09)   # 20 Percent

  # Add the default format for hyperlinks
  @url_format = add_format(:color => 'blue', :underline => 1)

  if file.respond_to?(:to_str) && file != ''
    @fh_out      = open(file, "wb")
    @internal_fh = 1
  else
    @fh_out = file
  end

  # Set colour palette.
  set_palette_xl97

  get_checksum_method
end

Instance Method Details

#add_chart(params) ⇒ Object

add_chart(params)

Create a chart for embedding or as as new sheet.

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 => 'Chart::Column')

The properties that can be set are:

:type     (required)
: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 => 'Chart::Line')

  The available types are:

      'Chart::Column'
      'Chart::Bar'
      'Chart::Line'
      'Chart::Area'
      'Chart::Pie'
      'Chart::Scatter'
      'Chart::Stock'

* :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 => 'Chart::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 => 'Chart::Line', :embedded => 1)

      # Configure the chart.
      ...

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

See WriteExcel::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.



381
382
383
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
412
413
414
415
416
417
418
419
420
421
422
423
424
 
# File 'lib/writeexcel/workbook.rb', line 381

def add_chart(params)
  name = ''
  encoding = 0
  index    = @worksheets.size

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

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

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

  init_data = [
    name,
    index,
    encoding,
    @url_format,
    @parser,
    @tempdir,
    @date_1904 ? 1 : 0,
    @compatibility,
    @palette,
    @sinfo
  ]

  chart = Writeexcel::Chart.factory(type, *init_data)
  # If the chart isn't embedded let the workbook control it.
  if !embedded
    @worksheets[index] = chart          # Store ref for iterator
    @sheetnames[index] = name           # Store EXTERNSHEET names
  else
    # 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
  end
  chart
end

#add_chart_ext(filename, name, encoding = 0) ⇒ Object

add_chart_ext($filename, $name)

Add an externally created chart.

This method is use to include externally generated charts in a WriteExcel file.

chart = workbook.add_chart_ext('chart01.bin', 'Chart1')

This feature is semi-deprecated in favour of the “native” charts created using add_chart(). Read external_charts.txt in the external_charts directory of the distro for a full explanation.



441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
 
# File 'lib/writeexcel/workbook.rb', line 441

def add_chart_ext(filename, name, encoding = 0)
  index    = @worksheets.size
  type = 'extarnal'

  name, encoding = check_sheetname(name, encoding)

  init_data = [
    filename,
    name,
    index,
    encoding,
    @sinfo
  ]

  chart = Writeexcel::Chart.factory(self, type, init_data)
  @worksheets[index] = chart      # Store ref for iterator
  @sheetnames[index] = name           # Store EXTERNSHEET names
  chart
end

#add_format(*args) ⇒ 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(props) # Set properties at creation
format2 = workbook.add_format        # Set properties later

See the “CELL FORMATTING” section for more details about Format properties and how to set them.



586
587
588
589
590
591
592
593
 
# File 'lib/writeexcel/workbook.rb', line 586

def add_format(*args)
  formats = {}
  args.each { |arg| formats = formats.merge(arg) }
  format = Writeexcel::Format.new(@xf_index, @default_formats.merge(formats))
  @xf_index += 1
  @formats.push format # Store format reference
  format
end

#add_worksheet(sheetname = '', encoding = 0) ⇒ Object

Add a new worksheet to the Excel workbook.

if sheetname is UTF-16BE format, pass 1 as encoding.

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 sheetname is not specified the default Excel convention will be followed, i.e. Sheet1, Sheet2, etc. The utf_16_be parameter is optional, see below.

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.

This method will also handle strings in UTF-8 format.

worksheet = workbook.add_worksheet("シート名")

UTF-16BE worksheet names using an additional optional parameter:

name = [0x263a].pack('n')
worksheet = workbook.add_worksheet(name, 1)   # Smiley



286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
 
# File 'lib/writeexcel/workbook.rb', line 286

def add_worksheet(sheetname = '', encoding = 0)
  index = @worksheets.size

  name, encoding = check_sheetname(sheetname, encoding)

  # Porters take note, the following scheme of passing references to Workbook
  # data (in the \$self->{_foo} cases) instead of a reference to the Workbook
  # itself is a workaround to avoid circular references between Workbook and
  # Worksheet objects. Feel free to implement this in any way the suits your
  # language.
  #
  init_data = [
                name,
                index,
                encoding,
                @url_format,
                @parser,
                @tempdir,
                @date_1904,
                @compatibility,
                nil,    # Palette. Not used yet. See add_chart().
                @sinfo,
  ]
  worksheet = Writeexcel::Worksheet.new(*init_data)
  @worksheets[index] = worksheet      # Store ref for iterator
  @sheetnames[index] = name           # Store EXTERNSHEET names
  @parser.set_ext_sheets(name, index) # Store names in Formula.rb
  worksheet
end

#biff_only=(val) ⇒ Object

:nodoc:



1202
1203
1204
 
# File 'lib/writeexcel/workbook.rb', line 1202

def biff_only=(val)  # :nodoc:
  @biff_only = val
end

#calculate_extern_sizes ⇒ Object

_calculate_extern_sizes()

We need to calculate the space required by the SUPBOOK, EXTERNSHEET and NAME records so that it can be added to the BOUNDSHEET offsets.



2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
 
# File 'lib/writeexcel/workbook.rb', line 2470

def calculate_extern_sizes  # :nodoc:
  ext_refs        = @parser.get_ext_sheets
  length          = 0
  index           = 0

  unless @defined_names.empty?
    index   = 0
    key     = "#{index}:#{index}"

    add_ext_refs(ext_refs, key) unless ext_refs.has_key?(key)
  end

  @defined_names.each do |defined_name|
    length += 19 + defined_name[:name].bytesize + defined_name[:formula].bytesize
  end

  @worksheets.each do |worksheet|

    rowmin      = worksheet.title_rowmin
    colmin      = worksheet.title_colmin
    key         = "#{index}:#{index}"
    index += 1

    # Add area NAME records
    #
    if worksheet.print_rowmin
      add_ext_refs(ext_refs, key) unless ext_refs[key]
      length += 31
    end

    # Add title  NAME records
    #
    if rowmin and colmin
      add_ext_refs(ext_refs, key) unless ext_refs[key]
      length += 46
    elsif rowmin or colmin
      add_ext_refs(ext_refs, key) unless ext_refs[key]
      length += 31
    else
      # TODO, may need this later.
    end

    # Add Autofilter  NAME records
    #
    unless worksheet.filter_count == 0
      add_ext_refs(ext_refs, key) unless ext_refs[key]
      length += 31
    end
  end

  # Update the ref counts.
  ext_ref_count = ext_refs.keys.size
  @ext_refs      = ext_refs

  # If there are no external refs then we don't write, SUPBOOK, EXTERNSHEET
  # and NAME. Therefore the length is 0.

  return length = 0 if ext_ref_count == 0

  # The SUPBOOK record is 8 bytes
  length += 8

  # The EXTERNSHEET record is 6 bytes + 6 bytes for each external ref
  length += 6 * (1 + ext_ref_count)

  length
end

#calculate_extsst_size ⇒ Object

_calculate_extsst_size

The number of buckets used in the EXTSST is between 0 and 128. The number of strings per bucket (bucket size) has a minimum value of 8 and a theoretical maximum of 2^16. For “number of strings” < 1024 there is a constant bucket size of 8. The following algorithm generates the same size/bucket ratio as Excel.



2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
 
# File 'lib/writeexcel/workbook.rb', line 2868

def calculate_extsst_size       #:nodoc:
  unique_strings  = @sinfo[:str_unique]

  if unique_strings < 1024
    bucket_size = 8
  else
    bucket_size = 1 + Integer(unique_strings / 128.0)
  end

  buckets = Integer((unique_strings + bucket_size -1)  / Float(bucket_size))

  @extsst_buckets        = buckets
  @extsst_bucket_size    = bucket_size

  6 + 8 * buckets
end

#cleanup ⇒ Object 



3206
3207
3208
3209
 
# File 'lib/writeexcel/workbook.rb', line 3206

def cleanup
  super
  sheets.each { |sheet| sheet.cleanup }
end

#close ⇒ Object

Calls finalization methods and explicitly close the OLEwriter files handle.

In general your Excel file will be closed automatically when your program ends or when the Workbook object goes out of scope, however the close method can be used to explicitly close an Excel file.

workbook.close

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

The return value of close() is the same as that returned by perl when it closes the file created by new(). This allows you to handle error conditions in the usual way:

$workbook.close() or die "Error closing file: $!";



204
205
206
207
208
209
210
 
# File 'lib/writeexcel/workbook.rb', line 204

def close
  return if @fileclosed  # Prevent close() from being called twice.

  @fileclosed = true
  store_workbook
  cleanup
end

#compatibility_mode(mode = 1) ⇒ Object

Set the compatibility mode.

This method is used to improve compatibility with third party applications that read Excel files.

workbook.compatibility_mode

An Excel file is comprised of binary records that describe properties of a spreadsheet. Excel is reasonably liberal about this and, outside of a core subset, it doesn’t require every possible record to be present when it reads a file. This is also true of Gnumeric and OpenOffice.Org Calc.

WriteExcel takes advantage of this fact to omit some records in order to minimise the amount of data stored in memory and to simplify and speed up the writing of files. However, some third party applications that read Excel files often expect certain records to be present. In “compatibility mode” WriteExcel writes these records and tries to be as close to an Excel generated file as possible.

Applications that require compatibility_mode() are Apache POI, Apple Numbers, and Quickoffice on Nokia, Palm and other devices. You should also use compatibility_mode() if your Excel file will be used as an external data source by another Excel file.

If you encounter other situations that require compatibility_mode(), please let me know.

It should be noted that compatibility_mode() requires additional data to be stored in memory and additional processing. This incurs a memory and speed penalty and may not be suitable for very large files (>20MB).

You must call compatibility_mode() before calling add_worksheet().

Excel doesn’t require every possible Biff record to be present in a file. In particular if the indexing records INDEX, ROW and DBCELL aren’t present it just ignores the fact and reads the cells anyway. This is also true of the EXTSST record. Gnumeric and OOo also take this approach. This allows WriteExcel to ignore these records in order to minimise the amount of data stored in memory. However, other third party applications that read Excel files often expect these records to be present. In “compatibility mode” WriteExcel writes these records and tries to be as close to an Excel generated file as possible.

This requires additional data to be stored in memory until the file is about to be written. This incurs a memory and speed penalty and may not be suitable for very large files.



644
645
646
647
648
649
 
# File 'lib/writeexcel/workbook.rb', line 644

def compatibility_mode(mode = 1)
  unless sheets.empty?
    raise "compatibility_mode() must be called before add_worksheet()"
  end
  @compatibility = mode
end

#define_name(name, formula, encoding = 0) ⇒ Object

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.

workbook.define_name('Exchange_rate', '=0.96')
workbook.define_name('Sales',         '=Sheet1!$G$1:$H$10')
workbook.define_name('Sheet2!Sales',  '=Sheet2!$G$1:$G$10')

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

Note: This currently a beta feature. More documentation and examples will be added.



923
924
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
955
956
957
958
959
960
961
962
963
 
# File 'lib/writeexcel/workbook.rb', line 923

def define_name(name, formula, encoding = 0)
  sheet_index = 0
  full_name   = name.downcase

  if name =~ /^(.*)!(.*)$/
    sheetname   = $1
    name        = $2;
    sheet_index = 1 + @parser.get_sheet_index(sheetname)
  end

  # Strip the = sign at the beginning of the formula string
  formula = formula.sub(/^=/, '')

  # Parse the formula using the parser in Formula.pm
  parser  = @parser

  # In order to raise formula errors from the point of view of the calling
  # program we use an eval block and re-raise the error from here.
  #
  tokens = parser.parse_formula(formula)

  # Force 2d ranges to be a reference class.
  tokens.collect! { |t| t.gsub(/_ref3d/, '_ref3dR') }
  tokens.collect! { |t| t.gsub(/_range3d/, '_range3dR') }

  # Parse the tokens into a formula string.
  formula = parser.parse_tokens(tokens)

  @defined_names.push(
     {
       :name        => name,
       :encoding    => encoding,
       :sheet_index => sheet_index,
       :formula     => formula
     }
   )

  index = @defined_names.size

  parser.set_ext_name(name, index)
end

#extsst_bucket_size ⇒ Object

:nodoc:



1198
1199
1200
 
# File 'lib/writeexcel/workbook.rb', line 1198

def extsst_bucket_size  # :nodoc:
  @extsst_bucket_size
end

#extsst_buckets ⇒ Object

:nodoc:



1194
1195
1196
 
# File 'lib/writeexcel/workbook.rb', line 1194

def extsst_buckets  # :nodoc:
  @extsst_buckets
end

#localtime=(val) ⇒ Object

:nodoc:



1210
1211
1212
 
# File 'lib/writeexcel/workbook.rb', line 1210

def localtime=(val)  # :nodoc:
  @localtime = val
end

#process_jpg(data, filename) ⇒ Object

_process_jpg()

Extract width and height information from a JPEG file.



1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
 
# File 'lib/writeexcel/workbook.rb', line 1651

def process_jpg(data, filename) # :nodoc:
  type     = 5  # Excel Blip type (MSOBLIPTYPE).

  offset = 2;
  data_length = data.bytesize

  # Search through the image data to find the 0xFFC0 marker. The height and
  # width are contained in the data for that sub element.
  while offset < data_length
    marker  = data[offset,   2].unpack("n")
    marker = marker[0]
    length  = data[offset+2, 2].unpack("n")
    length = length[0]

    if marker == 0xFFC0 || marker == 0xFFC2
      height = data[offset+5, 2].unpack("n")
      height = height[0]
      width  = data[offset+7, 2].unpack("n")
      width  = width[0]
      break
    end

    offset += length + 2
    break if marker == 0xFFDA
  end

  if height.nil?
    raise "#{filename}: no size data found in jpeg image.\n"
  end

  [type, width, height]
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.

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

See also “DATES AND TIME IN EXCEL” for more information about working with Excel’s date system.

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



671
672
673
674
675
676
 
# File 'lib/writeexcel/workbook.rb', line 671

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

#set_codepage(type = 1) ⇒ Object

The default code page or character set used by WriteExcel is ANSI. This is also the default used by Excel for Windows. Occasionally however it may be necessary to change the code page via the set_codepage() method.

Changing the code page may be required if your are using WriteExcel on the Macintosh and you are using characters outside the ASCII 128 character set:

workbook.set_codepage(1) # ANSI, MS Windows
workbook.set_codepage(2) # Apple Macintosh

The set_codepage() method is rarely required.



891
892
893
894
895
896
897
 
# File 'lib/writeexcel/workbook.rb', line 891

def set_codepage(type = 1)
  if type == 2
    @codepage = 0x8000
  else
    @codepage = 0x04E4
  end
end

#set_country(code = 1) ⇒ Object

store the country code.

Some non-english versions of Excel may need this set to some value other than 1 = “United States”. In general the country code is equal to the international dialling code.



906
907
908
 
# File 'lib/writeexcel/workbook.rb', line 906

def set_country(code = 1)
  @country = code
end

#set_custom_color(index = nil, red = nil, green = nil, blue = nil) ⇒ 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
                       )



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

def set_custom_color(index = nil, red = nil, green = nil, blue = nil)
  # 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, 0]

  index + 8
end

#set_properties(params) ⇒ Object

Set the document properties such as Title, Author etc. These are written to property sets in the OLE container.

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

The properties should be passed as a hash of values as follows:

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

The properties that can be set are:

* title
* subject
* author
* manager
* company
* category
* keywords
* comments

User defined properties are not supported due to effort required.

You can also pass UTF-8 strings as properties.

$workbook->set_properties(
    :subject => "住所録",
);

Usually WriteExcel allows you to use UTF-16. However, document properties don’t support UTF-16 for these type of strings.

In order to promote the usefulness of Ruby and the WriteExcel module consider adding a comment such as the following when using document properties:

workbook.set_properties(
    ...,
    :comments => 'Created with Ruby and WriteExcel',
    ...,
)

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



1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
 
# File 'lib/writeexcel/workbook.rb', line 1016

def set_properties(params)
  # Ignore if no args were passed.
  return -1 if !params.respond_to?(:to_hash) || params.empty?

  params.each do |k, v|
    params[k] = convert_to_ascii_if_ascii(v) if v.respond_to?(:to_str)
  end
  # List of valid input parameters.
  properties = {
    :codepage      => [0x0001, 'VT_I2'      ],
    :title         => [0x0002, 'VT_LPSTR'   ],
    :subject       => [0x0003, 'VT_LPSTR'   ],
    :author        => [0x0004, 'VT_LPSTR'   ],
    :keywords      => [0x0005, 'VT_LPSTR'   ],
    :comments      => [0x0006, 'VT_LPSTR'   ],
    :last_author   => [0x0008, 'VT_LPSTR'   ],
    :created       => [0x000C, 'VT_FILETIME'],
    :category      => [0x0002, 'VT_LPSTR'   ],
    :manager       => [0x000E, 'VT_LPSTR'   ],
    :company       => [0x000F, 'VT_LPSTR'   ],
    :utf8          => 1
  }

  # Check for valid input parameters.
  params.each_key do |k|
    unless properties.has_key?(k)
      raise "Unknown parameter '#{k}' in set_properties()";
    end
  end

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

  #
  # Create the SummaryInformation property set.
  #

  # Get the codepage of the strings in the property set.
  strings = ["title", "subject", "author", "keywords",  "comments", "last_author"]
  params[:codepage] = get_property_set_codepage(params, strings)

  # Create an array of property set values.
  property_sets = []
  strings.unshift("codepage")
  strings.push("created")
  strings.each do |string|
    property = string.to_sym
    property_sets.push(property_set(properties, property, params)) if params[property]
  end

  # Pack the property sets.
  @summary = create_summary_property_set(property_sets)

  #
  # Create the DocSummaryInformation property set.
  #

  # Get the codepage of the strings in the property set.
  strings = ["category", "manager", "company"]
  params[:codepage] = get_property_set_codepage(params, strings)

  # Create an array of property set values.
  property_sets = []

  [:codepage, :category, :manager, :company].each do |property|
    property_sets.push(property_set(properties, property, params)) if params[property]
  end

  # Pack the property sets.
  @doc_summary = create_doc_summary_property_set(property_sets)

  # Set a flag for when the files is written.
  @add_doc_properties = 1
end

#set_tempdir(dir = '') ⇒ Object

Change the default temp directory

For speed and efficiency WriteExcel stores worksheet data in temporary files prior to assembling the final workbook.

If WriteExcel is unable to create these temporary files it will store the required data in memory. This can be slow for large files.

The problem occurs mainly with IIS on Windows although it could feasibly occur on Unix systems as well. The problem generally occurs because the default temp file directory is defined as C:/ or some other directory that IIS doesn’t provide write access to.

To check if this might be a problem on a particular system you can run a simple test program with -w or use warnings. This will generate a warning if the module cannot create the required temporary files:

#!/usr/bin/ruby -w

require 'WriteExcel'

workbook  = WriteExcel.new('test.xls')
worksheet = workbook.add_worksheet
workbook.close

To avoid this problem the set_tempdir() method can be used to specify a directory that is accessible for the creation of temporary files.

Even if the default temporary file directory is accessible you may wish to specify an alternative location for security or maintenance reasons:

workbook.set_tempdir('/tmp/writeexcel')
workbook.set_tempdir('c:\windows\temp\writeexcel')

The directory for the temporary file must exist, set_tempdir() will not create a new directory.

One disadvantage of using the set_tempdir() method is that on some Windows systems it will limit you to approximately 800 concurrent tempfiles. This means that a single program running on one of these systems will be limited to creating a total of 800 workbook and worksheet objects. You can run multiple, non-concurrent programs to work around this if necessary.



871
872
873
874
875
876
 
# File 'lib/writeexcel/workbook.rb', line 871

def set_tempdir(dir = '')
  raise "#{dir} is not a valid directory" if dir != '' && !FileTest.directory?(dir)
  raise "set_tempdir must be called before add_worksheet" unless sheets.empty?

  @tempdir = dir
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



244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/writeexcel/workbook.rb', line 244

def sheets(*args)
  if args.empty?
    @worksheets
  else
    ary = []
    args.each do |i|
      ary << @worksheets[i]
    end
    ary
  end
end

#store_externsheet ⇒ Object

_store_externsheet()

Writes the Excel BIFF EXTERNSHEET record. These references are used by formulas. TODO NAME record is required to define the print area and the repeat rows and columns.



2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
 
# File 'lib/writeexcel/workbook.rb', line 2174

def store_externsheet  # :nodoc:
  record      = 0x0017                   # Record identifier

  # Get the external refs
  ext = @ext_refs.keys.sort

  # Change the external refs from stringified "1:1" to [1, 1]
  ext.map! {|e| e.split(/:/).map! {|v| v.to_i} }

  cxti        = ext.size                 # Number of Excel XTI structures
  rgxti       = ''                       # Array of XTI structures

  # Write the XTI structs
  ext.each do |e|
    rgxti += [0, e[0], e[1]].pack("vvv")
  end

  data        = [cxti].pack("v") + rgxti
  header    = [record, data.bytesize].pack("vv")

  append(header, data)
end

#store_name(name, encoding, sheet_index, formula) ⇒ Object

Store the NAME record used for storing the print area, repeat rows, repeat columns, autofilters and defined names.

TODO. This is a more generic version that will replace _store_name_short()

and _store_name_long().



2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
 
# File 'lib/writeexcel/workbook.rb', line 2204

def store_name(name, encoding, sheet_index, formula)  # :nodoc:
  ruby_19 { formula = convert_to_ascii_if_ascii(formula) }

  record          = 0x0018        # Record identifier

  text_length     = name.bytesize
  formula_length  = formula.bytesize

  # UTF-16 string length is in characters not bytes.
  text_length       /= 2 if encoding != 0

  grbit           = 0x0000        # Option flags
  shortcut        = 0x00          # Keyboard shortcut
  ixals           = 0x0000        # Unused index.
  menu_length     = 0x00          # Length of cust menu text
  desc_length     = 0x00          # Length of description text
  help_length     = 0x00          # Length of help topic text
  status_length   = 0x00          # Length of status bar text

  # Set grbit built-in flag and the hidden flag for autofilters.
  if text_length == 1
    grbit = 0x0020 if name.ord == 0x06  # Print area
    grbit = 0x0020 if name.ord == 0x07  # Print titles
    grbit = 0x0021 if name.ord == 0x0D  # Autofilter
  end

  data  = [grbit].pack("v")
  data += [shortcut].pack("C")
  data += [text_length].pack("C")
  data += [formula_length].pack("v")
  data += [ixals].pack("v")
  data += [sheet_index].pack("v")
  data += [menu_length].pack("C")
  data += [desc_length].pack("C")
  data += [help_length].pack("C")
  data += [status_length].pack("C")
  data += [encoding].pack("C")
  data += name
  data += formula

  header = [record, data.bytesize].pack("vv")

  append(header, data)
end

#store_names ⇒ Object

_store_names()

Write the NAME record to define the print area and the repeat rows and cols.



1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
 
# File 'lib/writeexcel/workbook.rb', line 1865

def store_names  # :nodoc:
  # Create the user defined names.
  @defined_names.each do |defined_name|
    store_name(
      defined_name[:name],
      defined_name[:encoding],
      defined_name[:sheet_index],
      defined_name[:formula]
    )
  end

  # Sort the worksheets into alphabetical order by name. This is a
  # requirement for some non-English language Excel patch levels.
  worksheets = @worksheets.sort_by{ |x| x.name }

  # Create the autofilter NAME records
  worksheets.each do |worksheet|
    index = worksheet.index
    key = "#{index}:#{index}"
    ref = @ext_refs[key]

    # Write a Name record if Autofilter has been defined
    if worksheet.filter_count != 0
      store_name_short(
        worksheet.index,
        0x0D, # NAME type = Filter Database
        ref,
        worksheet.filter_area[0],
        worksheet.filter_area[1],
        worksheet.filter_area[2],
        worksheet.filter_area[3],
        1     # Hidden
      )
    end
  end

  # Create the print area NAME records
  worksheets.each do |worksheet|
    index  = worksheet.index
    key = "#{index}:#{index}"
    ref = @ext_refs[key]

    # Write a Name record if the print area has been defined
    if !worksheet.print_rowmin.nil?
      store_name_short(
        worksheet.index,
        0x06, # NAME type = Print_Area
        ref,
        worksheet.print_rowmin,
        worksheet.print_rowmax,
        worksheet.print_colmin,
        worksheet.print_colmax
      )
    end
  end

  # Create the print title NAME records
  worksheets.each do |worksheet|
    index = worksheet.index
    rowmin = worksheet.title_rowmin
    rowmax = worksheet.title_rowmax
    colmin = worksheet.title_colmin
    colmax = worksheet.title_colmax
    key = "#{index}:#{index}"
    ref = @ext_refs[key]

    # Determine if row + col, row, col or nothing has been defined
    # and write the appropriate record
    #
    if rowmin && colmin
      # Row and column titles have been defined.
      # Row title has been defined.
      store_name_long(
        worksheet.index,
        0x07, # NAME type = Print_Titles
        ref,
        rowmin,
        rowmax,
        colmin,
        colmax
      )
    elsif rowmin
      # Row title has been defined.
      store_name_short(
        worksheet.index,
        0x07, # NAME type = Print_Titles
        ref,
        rowmin,
        rowmax,
        0x00,
        0xff
      )
    elsif colmin
      # Column title has been defined.
      store_name_short(
        worksheet.index,
        0x07, # NAME type = Print_Titles
        ref,
        0x0000,
        0xffff,
        colmin,
        colmax
      )
    else
      # Nothing left to do
    end
  end
end

#str_unique=(val) ⇒ Object

:nodoc:



1190
1191
1192
 
# File 'lib/writeexcel/workbook.rb', line 1190

def str_unique=(val)  # :nodoc:
  @sinfo[:str_unique] = val
end

#summary ⇒ Object

:nodoc:



1206
1207
1208
 
# File 'lib/writeexcel/workbook.rb', line 1206

def summary  # :nodoc:
  @summary
end