lib/write_xlsx/workbook.rb in write_xlsx-1.09.4 vs lib/write_xlsx/workbook.rb in write_xlsx-1.09.5

- old
+ new

@@ -1,7 +1,8 @@ # -*- coding: utf-8 -*- # frozen_string_literal: true + require 'write_xlsx/package/xml_writer_simple' require 'write_xlsx/package/packager' require 'write_xlsx/sheets' require 'write_xlsx/worksheet' require 'write_xlsx/chartsheet' @@ -14,15 +15,13 @@ require 'tmpdir' require 'tempfile' require 'digest/md5' module Writexlsx - OFFICE_URL = 'http://schemas.microsoft.com/office/' # :nodoc: class Workbook - include Writexlsx::Utility attr_writer :firstsheet # :nodoc: attr_reader :palette # :nodoc: attr_reader :worksheets, :charts, :drawings # :nodoc: @@ -33,20 +32,22 @@ attr_reader :shared_strings # :nodoc: attr_reader :vba_project # :nodoc: attr_reader :excel2003_style # :nodoc: attr_reader :max_url_length # :nodoc: attr_reader :strings_to_urls # :nodoc: - attr_reader :default_url_format # :nodoc: attr_reader :read_only # :nodoc: def initialize(file, *option_params) options, default_formats = process_workbook_options(*option_params) @writer = Package::XMLWriterSimple.new - @file = file + @file = file @tempdir = options[:tempdir] || - File.join(Dir.tmpdir, Digest::MD5.hexdigest("#{Time.now.to_f.to_s}-#{Process.pid}")) + File.join( + Dir.tmpdir, + Digest::MD5.hexdigest("#{Time.now.to_f}-#{Process.pid}") + ) @date_1904 = options[:date_1904] || false @activesheet = 0 @firstsheet = 0 @selected = 0 @fileclosed = false @@ -71,11 +72,11 @@ @tab_ratio = 600 @excel2003_style = options[:excel2003_style] || false @table_count = 0 @image_types = {} @images = [] - @strings_to_urls = (options[:strings_to_urls].nil? || options[:strings_to_urls]) ? true : false + @strings_to_urls = options[:strings_to_urls].nil? || options[:strings_to_urls] ? true : false @max_url_length = 2079 @has_comments = false @read_only = 0 @has_metadata = false @@ -130,11 +131,11 @@ # def sheets(*args) if args.empty? @worksheets else - args.collect{|i| @worksheets[i] } + args.collect { |i| @worksheets[i] } end end # # Return a worksheet object in the workbook using the sheetname. @@ -146,13 +147,12 @@ # # Set the date system: false = 1900 (the default), true = 1904 # def set_1904(mode = true) - unless sheets.empty? - raise "set_1904() must be called before add_worksheet()" - end + raise "set_1904() must be called before add_worksheet()" unless sheets.empty? + @date_1904 = ptrue?(mode) end # # return date system. false = 1900, true = 1904 @@ -166,35 +166,33 @@ end # # user must not use. it is internal method. # - def set_xml_writer(filename) #:nodoc: + def set_xml_writer(filename) # :nodoc: @writer.set_xml_writer(filename) end # # user must not use. it is internal method. # - def xml_str #:nodoc: + def xml_str # :nodoc: @writer.string end # # user must not use. it is internal method. # - def assemble_xml_file #:nodoc: + def assemble_xml_file # :nodoc: return unless @writer # Prepare format object for passing to Style.rb. prepare_format_properties write_xml_declaration do - # Write the root workbook element. write_workbook do - # Write the XLSX file version. write_file_version # Write the fileSharing element. write_file_sharing @@ -213,20 +211,20 @@ # Write the workbook calculation properties. write_calc_pr # Write the workbook extension storage. - #write_ext_lst + # write_ext_lst end end end # # At least one worksheet should be added to a new workbook. A worksheet is used to write data into cells: # def add_worksheet(name = '') - name = check_sheetname(name) + name = check_sheetname(name) worksheet = Worksheet.new(self, @worksheets.size, name) @worksheets << worksheet worksheet end @@ -255,11 +253,11 @@ chart.set_embedded_config_data else # Check the worksheet name for non-embedded charts. sheetname = check_chart_sheetname(name) chartsheet = Chartsheet.new(self, @worksheets.size, sheetname) - chartsheet.chart = chart + chartsheet.chart = chart @worksheets << chartsheet end @charts << chart ptrue?(embedded) ? chart : chartsheet end @@ -273,13 +271,11 @@ # format1 = workbook.add_format(property_hash) # Set properties at creation # format2 = workbook.add_format # Set properties later # def add_format(property_hash = {}) properties = {} - if @excel2003_style - properties.update(:font => 'Arial', :size => 10, :theme => -1) - end + properties.update(:font => 'Arial', :size => 10, :theme => -1) if @excel2003_style properties.update(property_hash) format = Format.new(@formats, properties) @formats.formats.push(format) # Store format reference @@ -294,11 +290,11 @@ def add_shape(properties = {}) shape = Shape.new(properties) shape.palette = @palette @shapes ||= [] - @shapes << shape #Store shape reference. + @shapes << shape # Store shape reference. shape end # # Create a defined name in Excel. We handle global/workbook level names and @@ -308,67 +304,59 @@ sheet_index = nil sheetname = '' # Local defined names are formatted like "Sheet1!name". if name =~ /^(.*)!(.*)$/ - sheetname = $1 - name = $2 + sheetname = ::Regexp.last_match(1) + name = ::Regexp.last_match(2) sheet_index = @worksheets.index_by_name(sheetname) else sheet_index = -1 # Use -1 to indicate global names. end # Raise if the sheet index wasn't found. - if !sheet_index - raise "Unknown sheet name #{sheetname} in defined_name()" - end + raise "Unknown sheet name #{sheetname} in defined_name()" unless sheet_index # Raise if the name contains invalid chars as defined by Excel help. # Refer to the following to see Excel's syntax rules for defined names: # http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names # - if name =~ /\A[-0-9 !"#\$%&'\(\)\*\+,\.:;<=>\?@\[\]\^`\{\}~]/ || name =~ /.+[- !"#\$%&'\(\)\*\+,\\:;<=>\?@\[\]\^`\{\}~]/ - raise "Invalid characters in name '#{name}' used in defined_name()" - end + raise "Invalid characters in name '#{name}' used in defined_name()" if name =~ /\A[-0-9 !"#$%&'()*+,.:;<=>?@\[\]\^`{}~]/ || name =~ /.+[- !"#$%&'()*+,\\:;<=>?@\[\]\^`{}~]/ # Raise if the name looks like a cell name. - if name =~ %r(^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$) - raise "Invalid name '#{name}' looks like a cell name in defined_name()" - end + raise "Invalid name '#{name}' looks like a cell name in defined_name()" if name =~ /^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$/ # Raise if the name looks like a R1C1 - if name =~ /\A[rcRC]\Z/ || name =~ /\A[rcRC]\d+[rcRC]\d+\Z/ - raise "Invalid name '#{name}' like a RC cell ref in defined_name()" - end + raise "Invalid name '#{name}' like a RC cell ref in defined_name()" if name =~ /\A[rcRC]\Z/ || name =~ /\A[rcRC]\d+[rcRC]\d+\Z/ - @defined_names.push([ name, sheet_index, formula.sub(/^=/, '') ]) + @defined_names.push([name, sheet_index, formula.sub(/^=/, '')]) end # # Set the workbook size. # def set_size(width = nil, height = nil) - if ptrue?(width) - # Convert to twips at 96 dpi. - @window_width = width.to_i * 1440 / 96 - else - @window_width = 16095 - end + @window_width = if ptrue?(width) + # Convert to twips at 96 dpi. + width.to_i * 1440 / 96 + else + 16095 + end - if ptrue?(height) - # Convert to twips at 96 dpi. - @window_height = height.to_i * 1440 / 96 - else - @window_height = 9660 - end + @window_height = if ptrue?(height) + # Convert to twips at 96 dpi. + height.to_i * 1440 / 96 + else + 9660 + end end # # Set the ratio of space for worksheet tabs. # def set_tab_ratio(tab_ratio = nil) - return if !tab_ratio + return unless tab_ratio if tab_ratio < 0 || tab_ratio > 100 raise "Tab ratio outside range: 0 <= zoom <= 100" else @tab_ratio = (tab_ratio * 10).to_i @@ -415,53 +403,46 @@ # # Set a user defined custom document property. # def set_custom_property(name, value, type = nil) - # Valid types. + # Valid types. valid_type = { 'text' => 1, 'date' => 1, 'number' => 1, 'number_int' => 1, - 'bool' => 1, + 'bool' => 1 } - if !name || (type != 'bool' && !value) - raise "The name and value parameters must be defined in set_custom_property()" - end + raise "The name and value parameters must be defined in set_custom_property()" if !name || (type != 'bool' && !value) # Determine the type for strings and numbers if it hasn't been specified. - if !ptrue?(type) - if value =~ /^\d+$/ - type = 'number_int' - elsif value =~ - /^([+-]?)(?=[0-9]|\.[0-9])[0-9]*(\.[0-9]*)?([Ee]([+-]?[0-9]+))?$/ - type = 'number' - else - type = 'text' - end + unless ptrue?(type) + type = if value =~ /^\d+$/ + 'number_int' + elsif value =~ + /^([+-]?)(?=[0-9]|\.[0-9])[0-9]*(\.[0-9]*)?([Ee]([+-]?[0-9]+))?$/ + 'number' + else + 'text' + end end # Check for valid validation types. - if !valid_type[type] - raise "Unknown custom type '$type' in set_custom_property()" - end + raise "Unknown custom type '$type' in set_custom_property()" unless valid_type[type] # Check for strings longer than Excel's limit of 255 chars. - if type == 'text' && value.length > 255 - raise "Length of text custom value '$value' exceeds Excel's limit of 255 in set_custom_property()" - end + raise "Length of text custom value '$value' exceeds Excel's limit of 255 in set_custom_property()" if type == 'text' && value.length > 255 if type == 'bool' value = value ? 1 : 0 end @custom_properties << [name, value, type] end - # # The add_vba_project method can be used to add macros or functions to an # WriteXLSX file using a binary VBA project file that has been extracted # from an existing Excel xlsm file. # @@ -471,15 +452,11 @@ # # Set the VBA name for the workbook. # def set_vba_name(vba_codename = nil) - if vba_codename - @vba_codename = vba_codename - else - @vba_codename = 'ThisWorkbook' - end + @vba_codename = vba_codename || 'ThisWorkbook' end # # Set the Excel "Read-only recommended" save option. # @@ -494,11 +471,11 @@ # def set_calc_mode(mode, calc_id = nil) @calc_mode = mode || 'auto' if mode == 'manual' - @calc_on_load = false + @calc_on_load = false elsif mode == 'auto_except_tables' @calc_mode = 'autoNoTable' end @calc_id = calc_id if calc_id @@ -507,68 +484,60 @@ # # Get the default url format used when a user defined format isn't specified # with write_url(). The format is the hyperlink style defined by Excel for the # default theme. # - def default_url_format - @default_url_format - end + attr_reader :default_url_format alias get_default_url_format default_url_format # # Change the RGB components of the elements in the colour palette. # def set_custom_color(index, red = 0, green = 0, blue = 0) # Match a HTML #xxyyzz style parameter if red.to_s =~ /^#(\w\w)(\w\w)(\w\w)/ - red = $1.hex - green = $2.hex - blue = $3.hex + red = ::Regexp.last_match(1).hex + green = ::Regexp.last_match(2).hex + blue = ::Regexp.last_match(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 + raise "Color index #{index} outside range: 8 <= index <= 64" if index < 8 || index > 64 # 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) + 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 - def activesheet=(worksheet) #:nodoc: - @activesheet = worksheet - end + attr_writer :activesheet - def writer #:nodoc: - @writer - end + attr_reader :writer - def date_1904? #:nodoc: + def date_1904? # :nodoc: @date_1904 ||= false !!@date_1904 end # # Add a string to the shared string table, if it isn't already there, and # return the string index. # EMPTY_HASH = {}.freeze - def shared_string_index(str) #:nodoc: + def shared_string_index(str) # :nodoc: @shared_strings.index(str, EMPTY_HASH) end def str_unique # :nodoc: @shared_strings.unique_count @@ -614,15 +583,15 @@ def non_chartsheets @worksheets.worksheets end - def firstsheet #:nodoc: + def firstsheet # :nodoc: @firstsheet ||= 0 end - def activesheet #:nodoc: + def activesheet # :nodoc: @activesheet ||= 0 end def has_metadata? @has_metadata @@ -638,11 +607,11 @@ def fileobj setup_filename unless @fileobj @fileobj end - def setup_filename #:nodoc: + def setup_filename # :nodoc: if @file.respond_to?(:to_str) && @file != '' @filename = @file @fileobj = nil elsif @file.respond_to?(:write) @filename = File.join(tempdir, Digest::MD5.hexdigest(Time.now.to_s) + '.xlsx.tmp') @@ -650,83 +619,81 @@ else raise "'#{@file}' must be valid filename String of IO object." end end - def tempdir - @tempdir - end + attr_reader :tempdir # # Sets the colour palette to the Excel defaults. # - def set_color_palette #:nodoc: + def set_color_palette # :nodoc: @palette = [ - [ 0x00, 0x00, 0x00, 0x00 ], # 8 - [ 0xff, 0xff, 0xff, 0x00 ], # 9 - [ 0xff, 0x00, 0x00, 0x00 ], # 10 - [ 0x00, 0xff, 0x00, 0x00 ], # 11 - [ 0x00, 0x00, 0xff, 0x00 ], # 12 - [ 0xff, 0xff, 0x00, 0x00 ], # 13 - [ 0xff, 0x00, 0xff, 0x00 ], # 14 - [ 0x00, 0xff, 0xff, 0x00 ], # 15 - [ 0x80, 0x00, 0x00, 0x00 ], # 16 - [ 0x00, 0x80, 0x00, 0x00 ], # 17 - [ 0x00, 0x00, 0x80, 0x00 ], # 18 - [ 0x80, 0x80, 0x00, 0x00 ], # 19 - [ 0x80, 0x00, 0x80, 0x00 ], # 20 - [ 0x00, 0x80, 0x80, 0x00 ], # 21 - [ 0xc0, 0xc0, 0xc0, 0x00 ], # 22 - [ 0x80, 0x80, 0x80, 0x00 ], # 23 - [ 0x99, 0x99, 0xff, 0x00 ], # 24 - [ 0x99, 0x33, 0x66, 0x00 ], # 25 - [ 0xff, 0xff, 0xcc, 0x00 ], # 26 - [ 0xcc, 0xff, 0xff, 0x00 ], # 27 - [ 0x66, 0x00, 0x66, 0x00 ], # 28 - [ 0xff, 0x80, 0x80, 0x00 ], # 29 - [ 0x00, 0x66, 0xcc, 0x00 ], # 30 - [ 0xcc, 0xcc, 0xff, 0x00 ], # 31 - [ 0x00, 0x00, 0x80, 0x00 ], # 32 - [ 0xff, 0x00, 0xff, 0x00 ], # 33 - [ 0xff, 0xff, 0x00, 0x00 ], # 34 - [ 0x00, 0xff, 0xff, 0x00 ], # 35 - [ 0x80, 0x00, 0x80, 0x00 ], # 36 - [ 0x80, 0x00, 0x00, 0x00 ], # 37 - [ 0x00, 0x80, 0x80, 0x00 ], # 38 - [ 0x00, 0x00, 0xff, 0x00 ], # 39 - [ 0x00, 0xcc, 0xff, 0x00 ], # 40 - [ 0xcc, 0xff, 0xff, 0x00 ], # 41 - [ 0xcc, 0xff, 0xcc, 0x00 ], # 42 - [ 0xff, 0xff, 0x99, 0x00 ], # 43 - [ 0x99, 0xcc, 0xff, 0x00 ], # 44 - [ 0xff, 0x99, 0xcc, 0x00 ], # 45 - [ 0xcc, 0x99, 0xff, 0x00 ], # 46 - [ 0xff, 0xcc, 0x99, 0x00 ], # 47 - [ 0x33, 0x66, 0xff, 0x00 ], # 48 - [ 0x33, 0xcc, 0xcc, 0x00 ], # 49 - [ 0x99, 0xcc, 0x00, 0x00 ], # 50 - [ 0xff, 0xcc, 0x00, 0x00 ], # 51 - [ 0xff, 0x99, 0x00, 0x00 ], # 52 - [ 0xff, 0x66, 0x00, 0x00 ], # 53 - [ 0x66, 0x66, 0x99, 0x00 ], # 54 - [ 0x96, 0x96, 0x96, 0x00 ], # 55 - [ 0x00, 0x33, 0x66, 0x00 ], # 56 - [ 0x33, 0x99, 0x66, 0x00 ], # 57 - [ 0x00, 0x33, 0x00, 0x00 ], # 58 - [ 0x33, 0x33, 0x00, 0x00 ], # 59 - [ 0x99, 0x33, 0x00, 0x00 ], # 60 - [ 0x99, 0x33, 0x66, 0x00 ], # 61 - [ 0x33, 0x33, 0x99, 0x00 ], # 62 - [ 0x33, 0x33, 0x33, 0x00 ], # 63 + [0x00, 0x00, 0x00, 0x00], # 8 + [0xff, 0xff, 0xff, 0x00], # 9 + [0xff, 0x00, 0x00, 0x00], # 10 + [0x00, 0xff, 0x00, 0x00], # 11 + [0x00, 0x00, 0xff, 0x00], # 12 + [0xff, 0xff, 0x00, 0x00], # 13 + [0xff, 0x00, 0xff, 0x00], # 14 + [0x00, 0xff, 0xff, 0x00], # 15 + [0x80, 0x00, 0x00, 0x00], # 16 + [0x00, 0x80, 0x00, 0x00], # 17 + [0x00, 0x00, 0x80, 0x00], # 18 + [0x80, 0x80, 0x00, 0x00], # 19 + [0x80, 0x00, 0x80, 0x00], # 20 + [0x00, 0x80, 0x80, 0x00], # 21 + [0xc0, 0xc0, 0xc0, 0x00], # 22 + [0x80, 0x80, 0x80, 0x00], # 23 + [0x99, 0x99, 0xff, 0x00], # 24 + [0x99, 0x33, 0x66, 0x00], # 25 + [0xff, 0xff, 0xcc, 0x00], # 26 + [0xcc, 0xff, 0xff, 0x00], # 27 + [0x66, 0x00, 0x66, 0x00], # 28 + [0xff, 0x80, 0x80, 0x00], # 29 + [0x00, 0x66, 0xcc, 0x00], # 30 + [0xcc, 0xcc, 0xff, 0x00], # 31 + [0x00, 0x00, 0x80, 0x00], # 32 + [0xff, 0x00, 0xff, 0x00], # 33 + [0xff, 0xff, 0x00, 0x00], # 34 + [0x00, 0xff, 0xff, 0x00], # 35 + [0x80, 0x00, 0x80, 0x00], # 36 + [0x80, 0x00, 0x00, 0x00], # 37 + [0x00, 0x80, 0x80, 0x00], # 38 + [0x00, 0x00, 0xff, 0x00], # 39 + [0x00, 0xcc, 0xff, 0x00], # 40 + [0xcc, 0xff, 0xff, 0x00], # 41 + [0xcc, 0xff, 0xcc, 0x00], # 42 + [0xff, 0xff, 0x99, 0x00], # 43 + [0x99, 0xcc, 0xff, 0x00], # 44 + [0xff, 0x99, 0xcc, 0x00], # 45 + [0xcc, 0x99, 0xff, 0x00], # 46 + [0xff, 0xcc, 0x99, 0x00], # 47 + [0x33, 0x66, 0xff, 0x00], # 48 + [0x33, 0xcc, 0xcc, 0x00], # 49 + [0x99, 0xcc, 0x00, 0x00], # 50 + [0xff, 0xcc, 0x00, 0x00], # 51 + [0xff, 0x99, 0x00, 0x00], # 52 + [0xff, 0x66, 0x00, 0x00], # 53 + [0x66, 0x66, 0x99, 0x00], # 54 + [0x96, 0x96, 0x96, 0x00], # 55 + [0x00, 0x33, 0x66, 0x00], # 56 + [0x33, 0x99, 0x66, 0x00], # 57 + [0x00, 0x33, 0x00, 0x00], # 58 + [0x33, 0x33, 0x00, 0x00], # 59 + [0x99, 0x33, 0x00, 0x00], # 60 + [0x99, 0x33, 0x66, 0x00], # 61 + [0x33, 0x33, 0x99, 0x00], # 62 + [0x33, 0x33, 0x33, 0x00] # 63 ] end # # Check for valid worksheet names. We check the length, if it contains any # invalid characters and if the name is unique in the workbook. # - def check_sheetname(name) #:nodoc: + def check_sheetname(name) # :nodoc: @worksheets.make_and_check_sheet_chart_name(:sheet, name) end def check_chart_sheetname(name) @worksheets.make_and_check_sheet_chart_name(:chart, name) @@ -734,25 +701,26 @@ # # Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name and cell # range such as ( 'Sheet1', 0, 1, 4, 1 ). # - def get_chart_range(range) #:nodoc: + def get_chart_range(range) # :nodoc: # Split the range formula into sheetname and cells at the last '!'. pos = range.rindex('!') return nil unless pos if pos > 0 sheetname = range[0, pos] - cells = range[pos + 1 .. -1] + cells = range[pos + 1..-1] end # Split the cell range into 2 cells or else use single cell for both. if cells =~ /:/ cell_1, cell_2 = cells.split(/:/) else - cell_1, cell_2 = cells, cells + cell_1 = cells + cell_2 = cells end # Remove leading/trailing apostrophes and convert escaped quotes to single. sheetname.sub!(/^'/, '') sheetname.sub!(/'$/, '') @@ -761,146 +729,138 @@ row_start, col_start = xl_cell_to_rowcol(cell_1) row_end, col_end = xl_cell_to_rowcol(cell_2) # Check that we have a 1D range only. return nil if row_start != row_end && col_start != col_end - return [sheetname, row_start, col_start, row_end, col_end] + + [sheetname, row_start, col_start, row_end, col_end] end - def write_workbook #:nodoc: - schema = 'http://schemas.openxmlformats.org' + def write_workbook(&block) # :nodoc: + schema = 'http://schemas.openxmlformats.org' attributes = [ ['xmlns', schema + '/spreadsheetml/2006/main'], ['xmlns:r', schema + '/officeDocument/2006/relationships'] ] - @writer.tag_elements('workbook', attributes) do - yield - end + @writer.tag_elements('workbook', attributes, &block) end - def write_file_version #:nodoc: + def write_file_version # :nodoc: attributes = [ - ['appName', 'xl'], + %w[appName xl], ['lastEdited', 4], ['lowestEdited', 4], ['rupBuild', 4505] ] - if @vba_project - attributes << [:codeName, '{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}'] - end + attributes << [:codeName, '{37E998C4-C9E5-D4B9-71C8-EB1FF731991C}'] if @vba_project @writer.empty_tag('fileVersion', attributes) end # # Write the <fileSharing> element. # def write_file_sharing - return if !ptrue?(@read_only) + return unless ptrue?(@read_only) attributes = [] attributes << ['readOnlyRecommended', 1] @writer.empty_tag('fileSharing', attributes) end - def write_workbook_pr #:nodoc: + def write_workbook_pr # :nodoc: attributes = [] attributes << ['codeName', @vba_codename] if ptrue?(@vba_codename) attributes << ['date1904', 1] if date_1904? attributes << ['defaultThemeVersion', 124226] @writer.empty_tag('workbookPr', attributes) end - def write_book_views #:nodoc: + def write_book_views # :nodoc: @writer.tag_elements('bookViews') { write_workbook_view } end - def write_workbook_view #:nodoc: + def write_workbook_view # :nodoc: attributes = [ ['xWindow', @x_window], ['yWindow', @y_window], ['windowWidth', @window_width], ['windowHeight', @window_height] ] - if @tab_ratio != 600 - attributes << ['tabRatio', @tab_ratio] - end - if @firstsheet > 0 - attributes << ['firstSheet', @firstsheet + 1] - end - if @activesheet > 0 - attributes << ['activeTab', @activesheet] - end + attributes << ['tabRatio', @tab_ratio] if @tab_ratio != 600 + attributes << ['firstSheet', @firstsheet + 1] if @firstsheet > 0 + attributes << ['activeTab', @activesheet] if @activesheet > 0 @writer.empty_tag('workbookView', attributes) end - def write_calc_pr #:nodoc: - attributes = [ ['calcId', @calc_id] ] + def write_calc_pr # :nodoc: + attributes = [['calcId', @calc_id]] case @calc_mode when 'manual' - attributes << ['calcMode', 'manual'] + attributes << %w[calcMode manual] attributes << ['calcOnSave', 0] when 'autoNoTable' - attributes << ['calcMode', 'autoNoTable'] + attributes << %w[calcMode autoNoTable] end attributes << ['fullCalcOnLoad', 1] if @calc_on_load @writer.empty_tag('calcPr', attributes) end - def write_ext_lst #:nodoc: + def write_ext_lst # :nodoc: @writer.tag_elements('extLst') { write_ext } end - def write_ext #:nodoc: + def write_ext # :nodoc: attributes = [ ['xmlns:mx', "#{OFFICE_URL}mac/excel/2008/main"], ['uri', uri] ] @writer.tag_elements('ext', attributes) { write_mx_arch_id } end - def write_mx_arch_id #:nodoc: + def write_mx_arch_id # :nodoc: @writer.empty_tag('mx:ArchID', ['Flags', 2]) end - def write_defined_names #:nodoc: + def write_defined_names # :nodoc: return unless ptrue?(@defined_names) + @writer.tag_elements('definedNames') do @defined_names.each { |defined_name| write_defined_name(defined_name) } end end - def write_defined_name(defined_name) #:nodoc: + def write_defined_name(defined_name) # :nodoc: name, id, range, hidden = defined_name - attributes = [ ['name', name] ] + attributes = [['name', name]] attributes << ['localSheetId', "#{id}"] unless id == -1 - attributes << ['hidden', '1'] if hidden + attributes << %w[hidden 1] if hidden @writer.data_element('definedName', range, attributes) end - def write_io(str) #:nodoc: + def write_io(str) # :nodoc: @writer << str str end # for test - def defined_names #:nodoc: + def defined_names # :nodoc: @defined_names ||= [] end # # Assemble worksheets into a workbook. # - def store_workbook #:nodoc: + def store_workbook # :nodoc: # Add a default worksheet if non have been added. add_worksheet if @worksheets.empty? # Ensure that at least one worksheet has been selected. @worksheets.visible_first.select if @activesheet == 0 @@ -952,17 +912,17 @@ # # files # def parts - Dir.glob(File.join(tempdir, "**", "*"), File::FNM_DOTMATCH).select {|f| File.file?(f)} + Dir.glob(File.join(tempdir, "**", "*"), File::FNM_DOTMATCH).select { |f| File.file?(f) } end # # Prepare all of the format properties prior to passing them to Styles.rb. # - def prepare_format_properties #:nodoc: + def prepare_format_properties # :nodoc: # Separate format objects into XF and DXF formats. prepare_formats # Set the font index for the format objects. prepare_fonts @@ -979,11 +939,11 @@ # # Iterate through the XF Format objects and separate them into XF and DXF # formats. # - def prepare_formats #:nodoc: + def prepare_formats # :nodoc: @formats.formats.each do |format| xf_index = format.xf_index dxf_index = format.dxf_index @xf_formats[xf_index] = format if xf_index @@ -993,34 +953,32 @@ # # Iterate through the XF Format objects and give them an index to non-default # font elements. # - def prepare_fonts #:nodoc: + def prepare_fonts # :nodoc: fonts = {} @xf_formats.each { |format| format.set_font_info(fonts) } @font_count = fonts.size # For the DXF formats we only need to check if the properties have changed. @dxf_formats.each do |format| # The only font properties that can change for a DXF format are: color, # bold, italic, underline and strikethrough. - if format.color? || format.bold? || format.italic? || format.underline? || format.strikeout? - format.has_dxf_font(true) - end + format.has_dxf_font(true) if format.color? || format.bold? || format.italic? || format.underline? || format.strikeout? end end # # Iterate through the XF Format objects and give them an index to non-default # number format elements. # # User defined records start from index 0xA4. # - def prepare_num_formats #:nodoc: + def prepare_num_formats # :nodoc: num_formats = {} index = 164 num_format_count = 0 (@xf_formats + @dxf_formats).each do |format| @@ -1030,13 +988,11 @@ # Also check for a string of zeros, which is a valid number format # string but would evaluate to zero. # if num_format.to_s =~ /^\d+$/ && num_format.to_s !~ /^0+\d/ # Number format '0' is indexed as 1 in Excel. - if num_format == 0 - num_format = 1 - end + num_format = 1 if num_format == 0 # Index to a built-in number format. format.num_format_index = num_format next elsif num_format.to_s == 'General' # The 'General' format has an number format index of 0. @@ -1063,11 +1019,11 @@ # # Iterate through the XF Format objects and give them an index to non-default # border elements. # - def prepare_borders #:nodoc: + def prepare_borders # :nodoc: borders = {} @xf_formats.each { |format| format.set_border_info(borders) } @border_count = borders.size @@ -1084,25 +1040,25 @@ # fill elements. # # The user defined fill properties start from 2 since there are 2 default # fills: patternType="none" and patternType="gray125". # - def prepare_fills #:nodoc: + def prepare_fills # :nodoc: fills = {} index = 2 # Start from 2. See above. # Add the default fills. fills['0:0:0'] = 0 fills['17:0:0'] = 1 # Store the DXF colors separately since them may be reversed below. @dxf_formats.each do |format| - if format.pattern != 0 || format.bg_color != 0 || format.fg_color != 0 - format.has_dxf_fill(true) - format.dxf_bg_color = format.bg_color - format.dxf_fg_color = format.fg_color - end + next unless format.pattern != 0 || format.bg_color != 0 || format.fg_color != 0 + + format.has_dxf_fill(true) + format.dxf_bg_color = format.bg_color + format.dxf_fg_color = format.fg_color end @xf_formats.each do |format| # The following logical statements jointly take care of special cases # in relation to cell colours and patterns: @@ -1152,11 +1108,11 @@ # # Iterate through the worksheets and store any defined names in addition to # any user defined names. Stores the defined names for the Workbook.xml and # the named ranges for App.xml. # - def prepare_defined_names #:nodoc: + def prepare_defined_names # :nodoc: @worksheets.each do |sheet| # Check for Print Area settings. if sheet.autofilter_area @defined_names << [ '_xlnm._FilterDatabase', @@ -1165,49 +1121,50 @@ 1 ] end # Check for Print Area settings. - if !sheet.print_area.empty? + unless sheet.print_area.empty? @defined_names << [ '_xlnm.Print_Area', sheet.index, sheet.print_area ] end # Check for repeat rows/cols. aka, Print Titles. - if !sheet.print_repeat_cols.empty? || !sheet.print_repeat_rows.empty? - if !sheet.print_repeat_cols.empty? && !sheet.print_repeat_rows.empty? - range = sheet.print_repeat_cols + ',' + sheet.print_repeat_rows - else - range = sheet.print_repeat_cols + sheet.print_repeat_rows - end + next unless !sheet.print_repeat_cols.empty? || !sheet.print_repeat_rows.empty? - # Store the defined names. - @defined_names << ['_xlnm.Print_Titles', sheet.index, range] - end + range = if !sheet.print_repeat_cols.empty? && !sheet.print_repeat_rows.empty? + sheet.print_repeat_cols + ',' + sheet.print_repeat_rows + else + sheet.print_repeat_cols + sheet.print_repeat_rows + end + + # Store the defined names. + @defined_names << ['_xlnm.Print_Titles', sheet.index, range] end - @defined_names = sort_defined_names(@defined_names) + @defined_names = sort_defined_names(@defined_names) @named_ranges = extract_named_ranges(@defined_names) end # # Iterate through the worksheets and set up the VML objects. # - def prepare_vml_objects #:nodoc: + def prepare_vml_objects # :nodoc: comment_id = 0 vml_drawing_id = 0 vml_data_id = 1 vml_header_id = 0 vml_shape_id = 1024 comment_files = 0 has_button = false @worksheets.each do |sheet| next if !sheet.has_vml? && !sheet.has_header_vml? + if sheet.has_vml? if sheet.has_comments? comment_files += 1 comment_id += 1 @has_comments = true @@ -1218,12 +1175,12 @@ vml_data_id, vml_shape_id, vml_drawing_id, comment_id ) # Each VML file should start with a shape id incremented by 1024. - vml_data_id += 1 * ( 1 + sheet.num_comments_block ) - vml_shape_id += 1024 * ( 1 + sheet.num_comments_block ) + vml_data_id += 1 * (1 + sheet.num_comments_block) + vml_shape_id += 1024 * (1 + sheet.num_comments_block) end if sheet.has_header_vml? vml_header_id += 1 vml_drawing_id += 1 @@ -1232,21 +1189,17 @@ # Set the sheet vba_codename if it has a button and the workbook # has a vbaProject binary. unless sheet.buttons_data.empty? has_button = true - if @vba_project && !sheet.vba_codename - sheet.set_vba_name - end + sheet.set_vba_name if @vba_project && !sheet.vba_codename end end # Set the workbook vba_codename if one of the sheets has a button and # the workbook has a vbaProject binary. - if has_button && @vba_project && !@vba_codename - set_vba_name - end + set_vba_name if has_button && @vba_project && !@vba_codename end # # Set the table ids for the worksheet tables. # @@ -1273,20 +1226,20 @@ # # Add "cached" data to charts to provide the numCache and strCache data for # series and title/axis ranges. # - def add_chart_data #:nodoc: + def add_chart_data # :nodoc: worksheets = {} seen_ranges = {} # Map worksheet names to worksheet objects. @worksheets.each { |worksheet| worksheets[worksheet.name] = worksheet } # Build an array of the worksheet charts including any combined charts. - @charts.collect { |chart| [chart, chart.combined] }.flatten.compact. - each do |chart| + @charts.collect { |chart| [chart, chart.combined] }.flatten.compact + .each do |chart| chart.formula_ids.each do |range, id| # Skip if the series has user defined data. if chart.formula_data[id] seen_ranges[range] = chart.formula_data[id] unless seen_ranges[range] next @@ -1310,13 +1263,11 @@ next end # Raise if the name is unknown since it indicates a user error in # a chart series formula. - unless worksheets[sheetname] - raise "Unknown worksheet reference '#{sheetname} in range '#{range}' passed to add_series()\n" - end + raise "Unknown worksheet reference '#{sheetname} in range '#{range}' passed to add_series()\n" unless worksheets[sheetname] # Add the data to the chart. # And store range data locally to avoid lookup if seen agein. chart.formula_data[id] = seen_ranges[range] = chart_data(worksheets[sheetname], cells) @@ -1328,15 +1279,15 @@ # Get the data from the worksheet table. data = worksheet.get_range_data(*cells) # Convert shared string indexes to strings. data.collect do |token| - if token.kind_of?(Hash) + if token.is_a?(Hash) string = @shared_strings.string(token[:sst_id]) # Ignore rich strings for now. Deparse later if necessary. - if string =~ %r!^<r>! && string =~ %r!</r>$! + if string =~ /^<r>/ && string =~ %r{</r>$} '' else string end else @@ -1349,80 +1300,78 @@ # Sort internal and user defined names in the same order as used by Excel. # This may not be strictly necessary but unsorted elements caused a lot of # issues in the the Spreadsheet::WriteExcel binary version. Also makes # comparison testing easier. # - def sort_defined_names(names) #:nodoc: + def sort_defined_names(names) # :nodoc: names.sort do |a, b| name_a = normalise_defined_name(a[0]) name_b = normalise_defined_name(b[0]) sheet_a = normalise_sheet_name(a[2]) sheet_b = normalise_sheet_name(b[2]) # Primary sort based on the defined name. if name_a > name_b 1 elsif name_a < name_b -1 - else # name_a == name_b - # Secondary sort based on the sheet name. - if sheet_a >= sheet_b - 1 - else - -1 - end + elsif sheet_a >= sheet_b # name_a == name_b + # Secondary sort based on the sheet name. + 1 + else + -1 end end end # Used in the above sort routine to normalise the defined names. Removes any # leading '_xmln.' from internal names and lowercases the strings. - def normalise_defined_name(name) #:nodoc: + def normalise_defined_name(name) # :nodoc: name.sub(/^_xlnm./, '').downcase end # Used in the above sort routine to normalise the worksheet names for the # secondary sort. Removes leading quote and lowercases the strings. - def normalise_sheet_name(name) #:nodoc: + def normalise_sheet_name(name) # :nodoc: name.sub(/^'/, '').downcase end # # Extract the named ranges from the sorted list of defined names. These are # used in the App.xml file. # - def extract_named_ranges(defined_names) #:nodoc: + def extract_named_ranges(defined_names) # :nodoc: named_ranges = [] defined_names.each do |defined_name| name, index, range = defined_name # Skip autoFilter ranges. next if name == '_xlnm._FilterDatabase' # We are only interested in defined names with ranges. - if range =~ /^([^!]+)!/ - sheet_name = $1 + next unless range =~ /^([^!]+)!/ - # Match Print_Area and Print_Titles xlnm types. - if name =~ /^_xlnm\.(.*)$/ - xlnm_type = $1 - name = "#{sheet_name}!#{xlnm_type}" - elsif index != -1 - name = "#{sheet_name}!#{name}" - end + sheet_name = ::Regexp.last_match(1) - named_ranges << name + # Match Print_Area and Print_Titles xlnm types. + if name =~ /^_xlnm\.(.*)$/ + xlnm_type = ::Regexp.last_match(1) + name = "#{sheet_name}!#{xlnm_type}" + elsif index != -1 + name = "#{sheet_name}!#{name}" end + + named_ranges << name end named_ranges end # # Iterate through the worksheets and set up any chart or image drawings. # - def prepare_drawings #:nodoc: + def prepare_drawings # :nodoc: chart_ref_id = 0 image_ref_id = 0 drawing_id = 0 ref_id = 0 image_ids = {} @@ -1479,17 +1428,17 @@ name, type, x_dpi, y_dpi, md5 ) end # Prepare the worksheet charts. - sheet.charts.each_with_index do |chart, index| + sheet.charts.each_with_index do |_chart, index| chart_ref_id += 1 sheet.prepare_chart(index, chart_ref_id, drawing_id) end # Prepare the worksheet shapes. - sheet.shapes.each_with_index do |shape, index| + sheet.shapes.each_with_index do |_shape, index| sheet.prepare_shape(index, drawing_id) end # Prepare the header images. header_image_count.times do |index| @@ -1541,12 +1490,12 @@ end end # Sort the workbook charts references into the order that the were # written from the worksheets above. - @charts = @charts.select { |chart| chart.id != -1 }. - sort_by { |chart| chart.id } + @charts = @charts.select { |chart| chart.id != -1 } + .sort_by { |chart| chart.id } @drawing_count = drawing_id end # @@ -1560,23 +1509,23 @@ y_dpi = 96 # Open the image file and import the data. data = File.binread(filename) md5 = Digest::MD5.hexdigest(data) - if data.unpack('x A3')[0] == 'PNG' + if data.unpack1('x A3') == 'PNG' # Test for PNGs. type, width, height, x_dpi, y_dpi = process_png(data) @image_types[:png] = 1 - elsif data.unpack('n')[0] == 0xFFD8 + elsif data.unpack1('n') == 0xFFD8 # Test for JPEG files. type, width, height, x_dpi, y_dpi = process_jpg(data, filename) @image_types[:jpeg] = 1 - elsif data.unpack('A4')[0] == 'GIF8' + elsif data.unpack1('A4') == 'GIF8' # Test for GIFs. type, width, height, x_dpi, y_dpi = process_gif(data, filename) @image_types[:gif] = 1 - elsif data.unpack('A2')[0] == 'BM' + elsif data.unpack1('A2') == 'BM' # Test for BMPs. type, width, height = process_bmp(data, filename) @image_types[:bmp] = 1 else # TODO. Add Image::Size to support other types. @@ -1605,21 +1554,21 @@ # Search through the image data to read the height and width in th the # IHDR element. Also read the DPI in the pHYs element. while offset < data_length - length = data[offset + 0, 4].unpack("N")[0] - png_type = data[offset + 4, 4].unpack("A4")[0] + length = data[offset + 0, 4].unpack1("N") + png_type = data[offset + 4, 4].unpack1("A4") case png_type when "IHDR" - width = data[offset + 8, 4].unpack("N")[0] - height = data[offset + 12, 4].unpack("N")[0] + width = data[offset + 8, 4].unpack1("N") + height = data[offset + 12, 4].unpack1("N") when "pHYs" - x_ppu = data[offset + 8, 4].unpack("N")[0] - y_ppu = data[offset + 12, 4].unpack("N")[0] - units = data[offset + 16, 1].unpack("C")[0] + x_ppu = data[offset + 8, 4].unpack1("N") + y_ppu = data[offset + 12, 4].unpack1("N") + units = data[offset + 16, 1].unpack1("C") if units == 1 x_dpi = x_ppu * 0.0254 y_dpi = y_ppu * 0.0254 end @@ -1642,26 +1591,26 @@ offset = 2 data_length = data.bytesize # Search through the image data to read the JPEG markers. while offset < data_length - marker = data[offset+0, 2].unpack("n")[0] - length = data[offset+2, 2].unpack("n")[0] + marker = data[offset + 0, 2].unpack1("n") + length = data[offset + 2, 2].unpack1("n") # Read the height and width in the 0xFFCn elements # (Except C4, C8 and CC which aren't SOF markers). if (marker & 0xFFF0) == 0xFFC0 && marker != 0xFFC4 && marker != 0xFFCC - height = data[offset+5, 2].unpack("n")[0] - width = data[offset+7, 2].unpack("n")[0] + height = data[offset + 5, 2].unpack1("n") + width = data[offset + 7, 2].unpack1("n") end # Read the DPI in the 0xFFE0 element. if marker == 0xFFE0 - units = data[offset + 11, 1].unpack("C")[0] - x_density = data[offset + 12, 2].unpack("n")[0] - y_density = data[offset + 14, 2].unpack("n")[0] + units = data[offset + 11, 1].unpack1("C") + x_density = data[offset + 12, 2].unpack1("n") + y_density = data[offset + 14, 2].unpack1("n") if units == 1 x_dpi = x_density y_dpi = y_density elsif units == 2 @@ -1673,10 +1622,11 @@ offset += length + 2 break if marker == 0xFFDA end raise "#{filename}: no size data found in jpeg image.\n" unless height + [type, width, height, x_dpi, y_dpi] end # # Extract width and height information from a GIF file. @@ -1684,22 +1634,20 @@ def process_gif(data, filename) type = 'gif' x_dpi = 96 y_dpi = 96 - width = data[6, 2].unpack("v")[0] - height = data[8, 2].unpack("v")[0] + width = data[6, 2].unpack1("v") + height = data[8, 2].unpack1("v") - if height.nil? - raise "#{filename}: no size data found in gif image.\n" - end + raise "#{filename}: no size data found in gif image.\n" if height.nil? [type, width, height, x_dpi, y_dpi] end # Extract width and height information from a BMP file. - def process_bmp(data, filename) #:nodoc: + def process_bmp(data, filename) # :nodoc: type = 'bmp' # Check that the file is big enough to be a bitmap. raise "#{filename} doesn't contain enough data." if data.bytesize <= 0x36 @@ -1712,11 +1660,12 @@ planes, bitcount = data.unpack("x26 v2") raise "#{filename} isn't a 24bit true color bitmap." unless bitcount == 24 raise "#{filename}: only 1 plane supported in bitmap image." unless planes == 1 # Read the bitmap compression. Verify compression. - compression = data.unpack("x30 V")[0] + compression = data.unpack1("x30 V") raise "#{filename}: compression not supported in bitmap image." unless compression == 0 + [type, width, height] end end end