lib/writeexcel.rb in writeexcel-0.1.0 vs lib/writeexcel.rb in writeexcel-0.3.0

- old
+ new

@@ -1,18 +1,1134 @@ -############################################################################### -# -# WriteExcel. -# -# Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file. -# -# Copyright 2000-2008, John McNamara, jmcnamara@cpan.org -# -# original written in Perl by John McNamara -# converted to Ruby by Hideo Nakamura, cxn03651@msj.biglobe.ne.jp -# -require "writeexcel/workbook" - -class Spreadsheet - class WriteExcel < Workbook - VERSION = "0.1.0" - end -end +############################################################################### +# +# WriteExcel. +# +# WriteExcel - Write to a cross-platform Excel binary file. +# +# Copyright 2000-2010, John McNamara, jmcnamara@cpan.org +# +# original written in Perl by John McNamara +# converted to Ruby by Hideo Nakamura, cxn03651@msj.biglobe.ne.jp +# +require "writeexcel/workbook" +# +# = WriteExcel - Write to a cross-platform Excel binary file. +# +# == Contents +# SYSNOPSYS +# DESCRIPTION +# QUICK START +# WORKBOOK METHODS +# WORKSHEET METHODS +# PAGE SET-UP METHODS +# CELL FORMATTING +# FORMAT METHODS +# COLOURS IN EXCEL +# DATE AND TIME IN EXCEL +# OUTLINES AND GROUPING IN EXCEL +# DATA VALIDATION IN EXCEL +# FORMULAS AND FUNCTIONS IN EXCEL +# CHART +# +# == Synopsis +# +# To write a string, a formatted string, a number and a formula to the first +# worksheet in an Excel workbook called ruby.xls: +# +# require 'WriteExcel' +# +# # Create a new Excel workbook +# workbook = WriteExcel.new('ruby.xls') +# +# # Add a worksheet +# worksheet = workbook.add_worksheet +# +# # Add and define a format +# format = workbook.add_format # Add a format +# format.set_bold() +# format.set_color('red') +# format.set_align('center') +# +# # Write a formatted and unformatted string, row and column notation. +# col = row = 0 +# worksheet.write(row, col, 'Hi Excel!', format) +# worksheet.write(1, col, 'Hi Excel!') +# +# # Write a number and a formula using A1 notation +# worksheet.write('A3', 1.2345) +# worksheet.write('A4', '=SIN(PI()/4)') +# +# == Description +# +# WriteExcel can be used to create a cross-platform Excel binary file. +# Multiple worksheets can be added to a workbook and formatting can be applied +# to cells. Text, numbers, formulas, hyperlinks and images can be written to +# the cells. +# +# The Excel file produced by this gem is compatible with 97, 2000, 2002, 2003 +# and 2007. +# +# WriteExcel will work on the majority of Windows, UNIX and Mac platforms. +# Generated files are also compatible with the Linux/UNIX spreadsheet +# applications Gnumeric and OpenOffice.org. +# +# This module cannot be used to write to an existing Excel file +# +# This library is converted from Spreadsheet::WriteExcel module of Perl. +# http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.37/ +# +# == Quick Start +# +# WriteExcel tries to provide an interface to as many of Excel's features as +# possible. As a result there is a lot of documentation to accompany the +# interface and it can be difficult at first glance to see what it important +# and what is not. So for those of you who prefer to assemble Ikea furniture +# first and then read the instructions, here are three easy steps: +# +# 1. Create a new Excel workbook (i.e. file) using new(). +# +# 2. Add a worksheet to the new workbook using add_worksheet(). +# +# 3. Write to the worksheet using write(). +# +# Like this: +# +# require 'WriteExcel' # Step 0 +# +# workbook = WriteExcel.new('ruby.xls') # Step 1 +# worksheet = workbook.add_worksheet # Step 2 +# worksheet.write('A1', 'Hi Excel!') # Step 3 +# +# This will create an Excel file called ruby.xls with a single worksheet and the +# text 'Hi Excel!' in the relevant cell. And that's it. Okay, so there is +# actually a zeroth step as well, but use WriteExcel goes without saying. There +# are also many examples that come with the distribution and which you can +# use to get you started. See EXAMPLES. +# +# = Workbook methods +# +# The WriteExcel module provides an object oriented interface +# to a new Excel workbook. The following methods are available through +# a new workbook. +# +# new() +# add_worksheet() +# add_format() +# add_chart() +# add_chart_ext() +# close() +# compatibility_mode() +# set_properties() +# define_name() +# set_tempdir() +# set_custom_color() +# sheets() +# set_1904() +# set_codepage() +# +# = Worksheet methods +# +# A new worksheet is created by calling the add_worksheet() method from +# a workbook object: +# +# worksheet1 = workbook.add_worksheet +# worksheet2 = workbook.add_worksheet +# +# The following methods are available through a new worksheet: +# +# write() +# write_number() +# write_string() +# write_utf16be_string() +# write_utf16le_string() +# keep_leading_zeros() +# write_blank() +# write_row() +# write_col() +# write_date_time() +# write_url() +# write_url_range() +# write_formula() +# store_formula() +# repeat_formula() +# write_comment() +# show_comments() +# add_write_handler() (* not implemented yet) +# insert_image() +# insert_chart() +# data_validation() +# get_name() +# activate() +# select() +# hide() +# set_first_sheet() +# protect() +# set_selection() +# set_row() +# set_column() +# outline_settings() +# freeze_panes() +# split_panes() +# merge_range() +# set_zoom() +# right_to_left() +# hide_zero() +# set_tab_color() +# autofilter() +# +# == Cell notation +# WriteExcel supports two forms of notation to designate the position of cells: +# Row-column notation and A1 notation. +# +# Row-column notation uses a zero based index for both row and column while A1 +# notation uses the standard Excel alphanumeric sequence of column letter and +# 1-based row. For example: +# +# (0, 0) # The top left cell in row-column notation. +# ('A1') # The top left cell in A1 notation. +# +# (1999, 29) # Row-column notation. +# ('AD2000') # The same cell in A1 notation. +# +# Row-column notation is useful if you are referring to cells +# programmatically: +# +# (0 .. 10).each do |i| +# worksheet.write(i, 0, 'Hello') # Cells A1 to A10 +# end +# +# A1 notation is useful for setting up a worksheet manually and for working +# with formulas: +# +# worksheet.write('H1', 200) +# worksheet.write('H2', '=H1+1') +# +# In formulas and applicable methods you can also use the A:A column notation: +# +# worksheet.write('A1', '=SUM(B:B)') +# +# For simplicity, the parameter lists for the worksheet method calls in the +# following sections are given in terms of row-column notation. In all cases +# it is also possible to use A1 notation. +# +# Note: in Excel it is also possible to use a R1C1 notation. This is not +# supported by WriteExcel. +# +# ==PAGE SET-UP METHODS +# +# Page set-up methods affect the way that a worksheet looks when it is printed. +# They control features such as page headers and footers and margins. These +# methods are really just standard worksheet methods. They are documented +# here in a separate section for the sake of clarity. +# +# The following methods are available for page set-up: +# +# set_landscape() +# set_portrait() +# set_page_view() +# set_paper() +# center_horizontally() +# center_vertically() +# set_margins() +# set_header() +# set_footer() +# repeat_rows() +# repeat_columns() +# hide_gridlines() +# print_row_col_headers() +# print_area() +# print_across() +# fit_to_pages() +# set_start_page() +# set_print_scale() +# set_h_pagebreaks() +# set_v_pagebreaks() +# +# A common requirement when working with WriteExcel is to apply the same page +# set-up features to all of the worksheets in a workbook. To do this you can use +# the sheets() method of the workbook class to access the array of worksheets +# in a workbook: +# +# workbook.sheets.each do |worksheet| +# worksheet.set_landscape +# end +# +# ==CELL FORMATTING +# +# This section describes the methods and properties that are available for +# formatting cells in Excel. The properties of a cell that can be formatted +# include: fonts, colours, patterns, borders, alignment and number formatting. +# +# ===Creating and using a Format object +# +# Cell formatting is defined through a Format object. Format objects are +# created by calling the workbook add_format() method as follows: +# +# format1 = workbook.add_format # Set properties later +# format2 = workbook.add_format(property hash..) # Set at creation +# +# The format object holds all the formatting properties that can be applied +# to a cell, a row or a column. The process of setting these properties is +# discussed in the next section. +# +# Once a Format object has been constructed and it properties have been set +# it can be passed as an argument to the worksheet write methods as follows: +# +# worksheet.write(0, 0, 'One', format) +# worksheet.write_string(1, 0, 'Two', format) +# worksheet.write_number(2, 0, 3, format) +# worksheet.write_blank(3, 0, format) +# +# Formats can also be passed to the worksheet set_row() and set_column() +# methods to define the default property for a row or column. +# +# worksheet.set_row(0, 15, format) +# worksheet.set_column(0, 0, 15, format) +# +# ===Format methods and Format properties +# +# The following table shows the Excel format categories, the formatting +# properties that can be applied and the equivalent object method: +# +# Category Description Property Method Name +# -------- ----------- -------- ----------- +# Font Font type font set_font() +# Font size size set_size() +# Font color color set_color() +# Bold bold set_bold() +# Italic italic set_italic() +# Underline underline set_underline() +# Strikeout font_strikeout set_font_strikeout() +# Super/Subscript font_script set_font_script() +# Outline font_outline set_font_outline() +# Shadow font_shadow set_font_shadow() +# +# Number Numeric format num_format set_num_format() +# +# Protection Lock cells locked set_locked() +# Hide formulas hidden set_hidden() +# +# Alignment Horizontal align align set_align() +# Vertical align valign set_align() +# Rotation rotation set_rotation() +# Text wrap text_wrap set_text_wrap() +# Justify last text_justlast set_text_justlast() +# Center across center_across set_center_across() +# Indentation indent set_indent() +# Shrink to fit shrink set_shrink() +# +# Pattern Cell pattern pattern set_pattern() +# Background color bg_color set_bg_color() +# Foreground color fg_color set_fg_color() +# +# Border Cell border border set_border() +# Bottom border bottom set_bottom() +# Top border top set_top() +# Left border left set_left() +# Right border right set_right() +# Border color border_color set_border_color() +# Bottom color bottom_color set_bottom_color() +# Top color top_color set_top_color() +# Left color left_color set_left_color() +# Right color right_color set_right_color() +# +# There are two ways of setting Format properties: by using the object method +# interface or by setting the property directly. For example, a typical use of +# the method interface would be as follows: +# +# format = workbook.add_format +# format.set_bold +# format.set_color('red') +# +# By comparison the properties can be set directly by passing a hash of +# properties to the Format constructor: +# +# format = workbook.add_format(:bold => 1, :color => 'red') +# +# or after the Format has been constructed by means of the +# set_format_properties() method as follows: +# +# format = workbook.add_format +# format.set_format_properties(:bold => 1, :color => 'red') +# +# You can also store the properties in one or more named hashes and pass them +# to the required method: +# +# font = { +# :font => 'Arial', +# :size => 12, +# :color => 'blue', +# :bold => 1 +# } +# +# shading = { +# :bg_color => 'green', +# :pattern => 1 +# } +# +# format1 = workbook.add_format(font) # Font only +# format2 = workbook.add_format(font, shading) # Font and shading +# +# The provision of two ways of setting properties might lead you to wonder +# which is the best way. The method mechanism may be better is you prefer +# setting properties via method calls (which the author did when they were +# code was first written) otherwise passing properties to the constructor has +# proved to be a little more flexible and self documenting in practice. An +# additional advantage of working with property hashes is that it allows you to +# share formatting between workbook objects as shown in the example above. +# +#-- +# +# did not converted ??? +# +# The Perl/Tk style of adding properties is also supported: +# +# %font = ( +# -font => 'Arial', +# -size => 12, +# -color => 'blue', +# -bold => 1, +# ) +#++ +# +# ===Working with formats +# +# The default format is Arial 10 with all other properties off. +# +# Each unique format in WriteExcel must have a corresponding +# Format object. It isn't possible to use a Format with a write() method and +# then redefine the Format for use at a later stage. This is because a Format +# is applied to a cell not in its current state but in its final state. +# Consider the following example: +# +# format = workbook.add_format +# format.set_bold +# format.set_color('red') +# worksheet.write('A1', 'Cell A1', format) +# format.set_color('green') +# worksheet.write('B1', 'Cell B1', format) +# +# Cell A1 is assigned the Format _format_ which is initially set to the colour +# red. However, the colour is subsequently set to green. When Excel displays +# Cell A1 it will display the final state of the Format which in this case +# will be the colour green. +# +# In general a method call without an argument will turn a property on, +# for example: +# +# format1 = workbook.add_format +# format1.set_bold # Turns bold on +# format1.set_bold(1) # Also turns bold on +# format1.set_bold(0) # Turns bold off +# +# ==FORMAT METHODS +# +# The Format object methods are described in more detail in the following +# sections. In addition, there is a Ruby program called formats.rb in the +# examples directory of the WriteExcel distribution. This program creates an +# Excel workbook called formats.xls which contains examples of almost all +# the format types. +# +# The following Format methods are available: +# +# set_font() +# set_size() +# set_color() +# set_bold() +# set_italic() +# set_underline() +# set_font_strikeout() +# set_font_script() +# set_font_outline() +# set_font_shadow() +# set_num_format() +# set_locked() +# set_hidden() +# set_align() +# set_rotation() +# set_text_wrap() +# set_text_justlast() +# set_center_across() +# set_indent() +# set_shrink() +# set_pattern() +# set_bg_color() +# set_fg_color() +# set_border() +# set_bottom() +# set_top() +# set_left() +# set_right() +# set_border_color() +# set_bottom_color() +# set_top_color() +# set_left_color() +# set_right_color() +# +# The above methods can also be applied directly as properties. For example +# format.set_bold is equivalent to workbook.add_format(:bold => 1). +# +# ==COLOURS IN EXCEL +# +# Excel provides a colour palette of 56 colours. In WriteExcel these colours +# are accessed via their palette index in the range 8..63. This index is used +# to set the colour of fonts, cell patterns and cell borders. For example: +# +# format = workbook.add_format( +# :color => 12, # index for blue +# :font => 'Arial', +# :size => 12, +# :bold => 1 +# ) +# +# The most commonly used colours can also be accessed by name. The name acts +# as a simple alias for the colour index: +# +# black => 8 +# blue => 12 +# brown => 16 +# cyan => 15 +# gray => 23 +# green => 17 +# lime => 11 +# magenta => 14 +# navy => 18 +# orange => 53 +# pink => 33 +# purple => 20 +# red => 10 +# silver => 22 +# white => 9 +# yellow => 13 +# +# For example: +# +# font = workbook.add_format(:color => 'red') +# +# Users of VBA in Excel should note that the equivalent colour indices are in +# the range 1..56 instead of 8..63. +# +# If the default palette does not provide a required colour you can override +# one of the built-in values. This is achieved by using the set_custom_color() +# workbook method to adjust the RGB (red green blue) components of the colour: +# +# ferrari = workbook.set_custom_color(40, 216, 12, 12) +# +# format = workbook.add_format( +# :bg_color => ferrari, +# :pattern => 1, +# :border => 1 +# ) +# +# worksheet.write_blank('A1', format) +# +# You may also find the following links helpful: +# +# A detailed look at Excel's colour palette: +# http://www.mvps.org/dmcritchie/excel/colors.htm +# +# A decimal RGB chart: http://www.hypersolutions.org/pages/rgbdec.html +# +# A hex RGB chart: : http://www.hypersolutions.org/pages/rgbhex.html +# +# ==DATES AND TIME IN EXCEL +# +# There are two important things to understand about dates and times in Excel: +# +# 1. A date/time in Excel is a real number plus an Excel number format. +# +# 2. WriteExcel doesn't automatically convert date/time strings in write() to +# an Excel date/time. +# +# These two points are explained in more detail below along with some +# suggestions on how to convert times and dates to the required format. +# +# ===An Excel date/time is a number plus a format +# +# If you write a date string with write() then all you will get is a string: +# +# worksheet.write('A1', '02/03/04') # !! Writes a string not a date. !! +# +# Dates and times in Excel are represented by real numbers, for example +# "Jan 1 2001 12:30 AM" is represented by the number 36892.521. +# +# The integer part of the number stores the number of days since the epoch +# and the fractional part stores the percentage of the day. +# +# A date or time in Excel is just like any other number. To have the number +# display as a date you must apply an Excel number format to it. Here are +# some examples. +# +# #!/usr/bin/ruby -w +# +# require 'writeexcel' +# +# workbook = WriteExcel.new('date_examples.xls') +# worksheet = workbook.add_worksheet +# +# worksheet.set_column('A:A', 30) # For extra visibility. +# +# number = 39506.5 +# +# worksheet.write('A1', number) # 39506.5 +# +# format2 = workbook.add_format(:num_format => 'dd/mm/yy') +# worksheet.write('A2', number , format2); # 28/02/08 +# +# format3 = workbook.add_format(:num_format => 'mm/dd/yy') +# worksheet.write('A3', number , format3); # 02/28/08 +# +# format4 = workbook.add_format(:num_format => 'd-m-yyyy') +# worksheet.write('A4', .number , format4) # 28-2-2008 +# +# format5 = workbook.add_format(:num_format => 'dd/mm/yy hh:mm') +# worksheet.write('A5', number , format5) # 28/02/08 12:00 +# +# format6 = workbook.add_format(:num_format => 'd mmm yyyy') +# worksheet.write('A6', number , format6) # 28 Feb 2008 +# +# format7 = workbook.add_format(:num_format => 'mmm d yyyy hh:mm AM/PM') +# worksheet.write('A7', number , format7) # Feb 28 2008 12:00 PM +# +# ===WriteExcel doesn't automatically convert date/time strings +# +# WriteExcel doesn't automatically convert input date strings into Excel's +# formatted date numbers due to the large number of possible date formats +# and also due to the possibility of misinterpretation. +# +# For example, does 02/03/04 mean March 2 2004, February 3 2004 or even March +# 4 2002. +# +# Therefore, in order to handle dates you will have to convert them to numbers +# and apply an Excel format. Some methods for converting dates are listed in +# the next section. +# +# The most direct way is to convert your dates to the ISO8601 +# yyyy-mm-ddThh:mm:ss.sss date format and use the write_date_time() worksheet +# method: +# +# worksheet.write_date_time('A2', '2001-01-01T12:20', format) +# +# See the write_date_time() section of the documentation for more details. +# +# A general methodology for handling date strings with write_date_time() is: +# +# 1. Identify incoming date/time strings with a regex. +# 2. Extract the component parts of the date/time using the same regex. +# 3. Convert the date/time to the ISO8601 format. +# 4. Write the date/time using write_date_time() and a number format. +# +# Here is an example: +# +# #!/usr/bin/ruby -w +# +# require 'writeexcel' +# +# workbook = WriteExcel.new('example.xls') +# worksheet = workbook.add_worksheet +# +# # Set the default format for dates. +# date_format = workbook.add_format(:num_format => 'mmm d yyyy') +# +# # Increase column width to improve visibility of data. +# worksheet.set_column('A:C', 20) +# +# data = [ +# %w(Item Cost Date), +# %w(Book 10 1/9/2007), +# %w(Beer 4 12/9/2007), +# %w(Bed 500 5/10/2007) +# ] +# +# # Simulate reading from a data source. +# row = 0 +# +# data.each do |row_data| +# col = 0 +# row_data.each do |item| +# +# # Match dates in the following formats: d/m/yy, d/m/yyyy +# if item =~ %r[^(\d{1,2})/(\d{1,2})/(\d{4})$] +# # Change to the date format required by write_date_time(). +# date = sprintf "%4d-%02d-%02dT", $3, $2, $1 +# worksheet.write_date_time(row, col, date, date_format) +# else +# # Just plain data +# worksheet.write(row, col, item) +# end +# col += 1 +# end +# row += 1 +# end +# +#-- +# For a slightly more advanced solution you can modify the write() method to +# handle date formats of your choice via the add_write_handler() method. See +# the add_write_handler() section of the docs and the write_handler3.rb and +# write_handler4.rb programs in the examples directory of the distro. +#++ +# +# ==OUTLINES AND GROUPING IN EXCEL +# +# Excel allows you to group rows or columns so that they can be hidden or +# displayed with a single mouse click. This feature is referred to as outlines. +# +# Outlines can reduce complex data down to a few salient sub-totals or +# summaries. +# +# This feature is best viewed in Excel but the following is an ASCII +# representation of what a worksheet with three outlines might look like. Rows +# 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at level 1. +# The lines at the left hand side are called outline level bars. +# +# ------------------------------------------ +# 1 2 3 | | A | B | C | D | ... +# ------------------------------------------ +# _ | 1 | A | | | | ... +# | _ | 2 | B | | | | ... +# | | | 3 | (C) | | | | ... +# | | | 4 | (D) | | | | ... +# | - | 5 | E | | | | ... +# | _ | 6 | F | | | | ... +# | | | 7 | (G) | | | | ... +# | | | 8 | (H) | | | | ... +# | - | 9 | I | | | | ... +# - | . | ... | ... | ... | ... | ... +# +# Clicking the minus sign on each of the level 2 outlines will collapse and +# hide the data as shown in the next figure. The minus sign changes to a plus +# sign to indicate that the data in the outline is hidden. +# +# ------------------------------------------ +# 1 2 3 | | A | B | C | D | ... +# ------------------------------------------ +# _ | 1 | A | | | | ... +# | | 2 | B | | | | ... +# | + | 5 | E | | | | ... +# | | 6 | F | | | | ... +# | + | 9 | I | | | | ... +# - | . | ... | ... | ... | ... | ... +# +# Clicking on the minus sign on the level 1 outline will collapse the +# remaining rows as follows: +# +# ------------------------------------------ +# 1 2 3 | | A | B | C | D | ... +# ------------------------------------------ +# | 1 | A | | | | ... +# + | . | ... | ... | ... | ... | ... +# +# Grouping in WriteExcel is achieved by setting the outline level via the +# set_row() and set_column() worksheet methods: +# +# set_row(row, height, format, hidden, level, collapsed) +# set_column(first_col, last_col, width, format, hidden, level, collapsed) +# +# The following example sets an outline level of 1 for rows 1 and 2 +# (zero-indexed) and columns B to G. The parameters _height_ and _XF_ are +# assigned default values since they are undefined: +# +# worksheet.set_row(1, nil, nil, 0, 1) +# worksheet.set_row(2, nil, nil, 0, 1) +# worksheet.set_column('B:G', nil, nil, 0, 1) +# +# Excel allows up to 7 outline levels. Therefore the _level_ parameter should +# be in the range 0 <= _level_ <= 7. +# +# Rows and columns can be collapsed by setting the _hidden_ flag for the hidden +# rows/columns and setting the _collapsed_ flag for the row/column that has +# the collapsed + symbol: +# +# worksheet.set_row(1, nil, nil, 1, 1) +# worksheet.set_row(2, nil, nil, 1, 1) +# worksheet.set_row(3, nil, nil, 0, 0, 1) # Collapsed flag. +# +# worksheet.set_column('B:G', nil, nil, 1, 1) +# worksheet.set_column('H:H', nil, nil, 0, 0, 1) # Collapsed flag. +# +# Note: Setting the _collapsed_ flag is particularly important for +# compatibility with OpenOffice.org and Gnumeric. +# +# For a more complete example see the outline.rb +#-- +# and outline_collapsed.rb +#++ +# programs in the examples directory of the distro. +# +# Some additional outline properties can be set via the outline_settings() +# worksheet method, see above. +# +# ==DATA VALIDATION IN EXCEL +# +# Data validation is a feature of Excel which allows you to restrict the data +# that a users enters in a cell and to display help and warning messages. It +# also allows you to restrict input to values in a drop down list. +# +# A typical use case might be to restrict data in a cell to integer values in +# a certain range, to provide a help message to indicate the required value and +# to issue a warning if the input data doesn't meet the stated criteria. +# In WriteExcel we could do that as follows: +# +# worksheet.data_validation('B3', +# { +# :validate => 'integer', +# :criteria => 'between', +# :minimum => 1, +# :maximum => 100, +# :input_title => 'Input an integer:', +# :input_message => 'Between 1 and 100', +# :error_message => 'Sorry, try again.' +# }) +# +# The above example would look like this in Excel: +# http://homepage.eircom.net/~jmcnamara/perl/data_validation.jpg. +# +# For more information on data validation see the following Microsoft +# support article "Description and examples of data validation in Excel": +# http://support.microsoft.com/kb/211485. +# +# ==FORMULAS AND FUNCTIONS IN EXCEL +# +# ===Caveats +# +# The first thing to note is that there are still some outstanding issues +# with the implementation of formulas and functions: +# +# 1. Writing a formula is much slower than writing the equivalent string. +# 2. You cannot use array constants, i.e. {1;2;3}, in functions. +# 3. Unary minus isn't supported. +# 4. Whitespace is not preserved around operators. +# 5. Named ranges are not supported. +# 6. Array formulas are not supported. +# +# However, these constraints will be removed in future versions. They are +# here because of a trade-off between features and time. Also, it is possible +# to work around issue 1 using the store_formula() and repeat_formula() +# methods as described later in this section. +# +# ===Introduction +# +# The following is a brief introduction to formulas and functions in Excel +# and WriteExcel. +# +# A formula is a string that begins with an equals sign: +# +# '=A1+B1' +# '=AVERAGE(1, 2, 3)' +# +# The formula can contain numbers, strings, boolean values, cell references, +# cell ranges and functions. Named ranges are not supported. Formulas should +# be written as they appear in Excel, that is cells and functions must be +# in uppercase. +# +# Cells in Excel are referenced using the A1 notation system where the +# column is designated by a letter and the row by a number. Columns +# range from A to IV i.e. 0 to 255, rows range from 1 to 65536. +#-- +# The Spreadsheet::WriteExcel::Utility module that is included in the distro +# contains helper functions for dealing with A1 notation, for example: +# +# use Spreadsheet::WriteExcel::Utility; +# +# ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2) +# $str = xl_rowcol_to_cell(1, 2); # C2 +#++ +# +# The Excel $ notation in cell references is also supported. This allows you +# to specify whether a row or column is relative or absolute. This only has +# an effect if the cell is copied. The following examples show relative and +# absolute values. +# +# '=A1' # Column and row are relative +# '=$A1' # Column is absolute and row is relative +# '=A$1' # Column is relative and row is absolute +# '=$A$1' # Column and row are absolute +# +# Formulas can also refer to cells in other worksheets of the current +# workbook. For example: +# +# '=Sheet2!A1' +# '=Sheet2!A1:A5' +# '=Sheet2:Sheet3!A1' +# '=Sheet2:Sheet3!A1:A5' +# q{='Test Data'!A1} +# q{='Test Data1:Test Data2'!A1} +# +# The sheet reference and the cell reference are separated by ! the exclamation +# mark symbol. If worksheet names contain spaces, commas o parentheses then Excel +# requires that the name is enclosed in single quotes as shown in the last two +# examples above. In order to avoid using a lot of escape characters you can +# use the quote operator %q{} to protect the quotes. Only valid sheet names that +# have been added using the add_worksheet() method can be used in formulas. +# You cannot reference external workbooks. +# +# The following table lists the operators that are available in Excel's formulas. +# The majority of the operators are the same as Perl's, differences are indicated: +# +# Arithmetic operators: +# ===================== +# Operator Meaning Example +# + Addition 1+2 +# - Subtraction 2-1 +# * Multiplication 2*3 +# / Division 1/4 +# ^ Exponentiation 2^3 # Equivalent to ** +# - Unary minus -(1+2) # Not yet supported +# % Percent (Not modulus) 13% # Not supported, [1] +# +# Comparison operators: +# ===================== +# Operator Meaning Example +# = Equal to A1 = B1 # Equivalent to == +# <> Not equal to A1 <> B1 # Equivalent to != +# > Greater than A1 > B1 +# < Less than A1 < B1 +# >= Greater than or equal to A1 >= B1 +# <= Less than or equal to A1 <= B1 +# +# String operator: +# ================ +# Operator Meaning Example +# & Concatenation "Hello " & "World!" # [2] +# +# Reference operators: +# ==================== +# Operator Meaning Example +# : Range operator A1:A4 # [3] +# , Union operator SUM(1, 2+2, B3) # [4] +# +# Notes: +# [1]: You can get a percentage with formatting and modulus with MOD(). +# [2]: Equivalent to ("Hello " . "World!") in Perl. +# [3]: This range is equivalent to cells A1, A2, A3 and A4. +# [4]: The comma behaves like the list separator in Perl. +# +# The range and comma operators can have different symbols in non-English +# versions of Excel. These will be supported in a later version of WriteExcel. +# European users of Excel take note: +# +# worksheet.write('A1', '=SUM(1; 2; 3)') # Wrong!! +# worksheet.write('A1', '=SUM(1, 2, 3)') # Okay +# +# The following table lists all of the core functions supported by +# Excel 5 and WriteExcel. Any additional functions that are available through +# the "Analysis ToolPak" or other add-ins are not supported. These functions +# have all been tested to verify that they work. +# +# ABS DB INDIRECT NORMINV SLN +# ACOS DCOUNT INFO NORMSDIST SLOPE +# ACOSH DCOUNTA INT NORMSINV SMALL +# ADDRESS DDB INTERCEPT NOT SQRT +# AND DEGREES IPMT NOW STANDARDIZE +# AREAS DEVSQ IRR NPER STDEV +# ASIN DGET ISBLANK NPV STDEVP +# ASINH DMAX ISERR ODD STEYX +# ATAN DMIN ISERROR OFFSET SUBSTITUTE +# ATAN2 DOLLAR ISLOGICAL OR SUBTOTAL +# ATANH DPRODUCT ISNA PEARSON SUM +# AVEDEV DSTDEV ISNONTEXT PERCENTILE SUMIF +# AVERAGE DSTDEVP ISNUMBER PERCENTRANK SUMPRODUCT +# BETADIST DSUM ISREF PERMUT SUMSQ +# BETAINV DVAR ISTEXT PI SUMX2MY2 +# BINOMDIST DVARP KURT PMT SUMX2PY2 +# CALL ERROR.TYPE LARGE POISSON SUMXMY2 +# CEILING EVEN LEFT POWER SYD +# CELL EXACT LEN PPMT T +# CHAR EXP LINEST PROB TAN +# CHIDIST EXPONDIST LN PRODUCT TANH +# CHIINV FACT LOG PROPER TDIST +# CHITEST FALSE LOG10 PV TEXT +# CHOOSE FDIST LOGEST QUARTILE TIME +# CLEAN FIND LOGINV RADIANS TIMEVALUE +# CODE FINV LOGNORMDIST RAND TINV +# COLUMN FISHER LOOKUP RANK TODAY +# COLUMNS FISHERINV LOWER RATE TRANSPOSE +# COMBIN FIXED MATCH REGISTER.ID TREND +# CONCATENATE FLOOR MAX REPLACE TRIM +# CONFIDENCE FORECAST MDETERM REPT TRIMMEAN +# CORREL FREQUENCY MEDIAN RIGHT TRUE +# COS FTEST MID ROMAN TRUNC +# COSH FV MIN ROUND TTEST +# COUNT GAMMADIST MINUTE ROUNDDOWN TYPE +# COUNTA GAMMAINV MINVERSE ROUNDUP UPPER +# COUNTBLANK GAMMALN MIRR ROW VALUE +# COUNTIF GEOMEAN MMULT ROWS VAR +# COVAR GROWTH MOD RSQ VARP +# CRITBINOM HARMEAN MODE SEARCH VDB +# DATE HLOOKUP MONTH SECOND VLOOKUP +# DATEVALUE HOUR N SIGN WEEKDAY +# DAVERAGE HYPGEOMDIST NA SIN WEIBULL +# DAY IF NEGBINOMDIST SINH YEAR +# DAYS360 INDEX NORMDIST SKEW ZTEST +# +#-- +# You can also modify the module to support function names in the following +# languages: German, French, Spanish, Portuguese, Dutch, Finnish, Italian and +# Swedish. See the function_locale.pl program in the examples directory of the distro. +#++ +# +# For a general introduction to Excel's formulas and an explanation of the +# syntax of the function refer to the Excel help files or the following: +# http://office.microsoft.com/en-us/assistance/CH062528031033.aspx. +# +# If your formula doesn't work in Spreadsheet::WriteExcel try the following: +# +# 1. Verify that the formula works in Excel (or Gnumeric or OpenOffice.org). +# 2. Ensure that it isn't on the Caveats list shown above. +# 3. Ensure that cell references and formula names are in uppercase. +# 4. Ensure that you are using ':' as the range operator, A1:A4. +# 5. Ensure that you are using ',' as the union operator, SUM(1,2,3). +# 6. Ensure that the function is in the above table. +# +# If you go through steps 1-6 and you still have a problem, mail me. +# Improving performance when working with formulas +# +# Writing a large number of formulas with Spreadsheet::WriteExcel can be slow. +# This is due to the fact that each formula has to be parsed and with the +# current implementation this is computationally expensive. +# +# However, in a lot of cases the formulas that you write will be quite +# similar, for example: +# +# worksheet.write_formula('B1', '=A1 * 3 + 50', format) +# worksheet.write_formula('B2', '=A2 * 3 + 50', format) +# ... +# ... +# worksheet.write_formula('B99', '=A999 * 3 + 50', format) +# worksheet.write_formula('B1000', '=A1000 * 3 + 50', format) +# +# In this example the cell reference changes in iterations from A1 to A1000. +# The parser treats this variable as a token and arranges it according to +# predefined rules. However, since the parser is oblivious to the value of +# the token, it is essentially performing the same calculation 1000 times. +# This is inefficient. +# +# The way to avoid this inefficiency and thereby speed up the writing of +# formulas is to parse the formula once and then repeatedly substitute +# similar tokens. +# +# A formula can be parsed and stored via the store_formula() worksheet method. +# You can then use the repeat_formula() method to substitute _pattern_, +# _replace_ pairs in the stored formula: +# +# formula = worksheet.store_formula('=A1 * 3 + 50') +# +# (0...1000).each do |row| +# worksheet.repeat_formula(row, 1, formula, format, 'A1', 'A' + (row +1).to_s) +# end +# +# On an arbitrary test machine this method was 10 times faster than the +# brute force method shown above. +# +# It should be noted however that the overall speed of direct formula parsing +# will be improved in a future version. +# +# ==Chart +# +# ===Synopsis(Chart) +# +# To create a simple Excel file with a chart using WriteExcel: +# +# #!/usr/bin/ruby -w +# +# require 'writeexcel' +# +# workbook = WriteExcel.new('chart.xls') +# worksheet = workbook.add_worksheet +# +# chart = workbook.add_chart(:type => Chart::Column) +# +# # Configure the chart. +# chart.add_series( +# :categories => '=Sheet1!$A$2:$A$7', +# :values => '=Sheet1!$B$2:$B$7' +# ) +# +# # Add the data to the worksheet the chart refers to. +# data = [ +# [ 'Category', 2, 3, 4, 5, 6, 7 ], +# [ 'Value', 1, 4, 5, 2, 1, 5 ] +# ] +# +# worksheet.write('A1', data) +# +# workbook.close +# +# ===DESCRIPTION(Chart) +# +# The Chart module is an abstract base class for modules that implement charts +# in WriteExcel. The information below is applicable to all of the available +# subclasses. +# +# The Chart module isn't used directly, a chart object is created via the +# Workbook add_chart() method where the chart type is specified: +# +# chart = workbook.add_chart(:type => Chart::Column) +# +# Currently the supported chart types are: +# +# * Chart::Column: Creates a column style (histogram) chart. See Column. +# * Chart::Bar: Creates a Bar style (transposed histogram) chart. See Bar. +# * Chart::Line: Creates a Line style chart. See Line. +# * Chart::Area: Creates an Area (filled line) style chart. See Area. +# * Chart::Scatter: Creates an Scatter style chart. See Scatter. +# * Chart::Stock: Creates an Stock style chart. See Stock. +# +# More chart types will be supported in time. See the "TODO" section. +# +# === Chart names and links +# +# The add_series()), set_x_axis(), set_y_axis() and set_title() methods all +# support a name property. In general these names can be either a static +# string or a link to a worksheet cell. If you choose to use the name_formula +# property to specify a link then you should also the name property. +# This isn't strictly required by Excel but some third party applications +# expect it to be present. +# +# chartl.set_title( +# :name => 'Year End Results', +# :name_formula => '=Sheet1!$C$1' +# ) +# +# These links should be used sparingly since they aren't commonly +# used in Excel charts. +# +# === Chart names and Unicode +# +# The add_series()), set_x_axis(), set_y_axis() and set_title() methods all +# support a name property. These names can be UTF8 strings. +# +# This methodology is explained in the "UNICODE IN EXCEL" section of WriteExcel +# but is semi-deprecated. If you are using Unicode the easiest option is to +# just use UTF8. +# +# === TODO(Chart) +# +# Charts in WriteExcel are a work in progress. More chart types and +# features will be added in time. Please be patient. Even a small feature +# can take a week or more to implement, test and document. +# +# Features that are on the TODO list and will be added are: +# +# * Additional chart types. Stock, Pie and Scatter charts are next in line. +# Send an email if you are interested in other types and they will be +# added to the queue. +# * Colours and formatting options. For now you will have to make do +# with the default Excel colours and formats. +# * Axis controls, gridlines. +# * Embedded data in charts for third party application support. +# +# == KNOWN ISSUES(Chart) +# +# * Currently charts don't contain embedded data from which the charts +# can be rendered. Excel and most other third party applications ignore +# this and read the data via the links that have been specified. However, +# some applications may complain or not render charts correctly. The +# preview option in Mac OS X is an known example. This will be fixed +# in a later release. +# * When there are several charts with titles set in a workbook some of +# the titles may display at a font size of 10 instead of the default +# 12 until another chart with the title set is viewed. +# +class WriteExcel < Workbook + VERSION = "0.3.0" +end