lib/write_xlsx/worksheet.rb in write_xlsx-1.10.2 vs lib/write_xlsx/worksheet.rb in write_xlsx-1.11.0

- old
+ new

@@ -29,34 +29,43 @@ attr_reader :header_images, :footer_images, :background_image # :nodoc: attr_reader :vml_drawing_links # :nodoc: attr_reader :vml_data_id # :nodoc: attr_reader :vml_header_id # :nodoc: attr_reader :autofilter_area # :nodoc: - attr_reader :writer, :set_rows, :col_formats # :nodoc: + attr_reader :writer, :set_rows, :col_info # :nodoc: attr_reader :vml_shape_id # :nodoc: attr_reader :comments, :comments_author # :nodoc: attr_accessor :data_bars_2010, :dxf_priority # :nodoc: attr_reader :vba_codename # :nodoc: - attr_writer :excel_version + attr_writer :excel_version # :nodoc: + attr_reader :filter_cells # :nodoc: def initialize(workbook, index, name) # :nodoc: + rowmax = 1_048_576 + colmax = 16_384 + strmax = 32_767 + @writer = Package::XMLWriterSimple.new @workbook = workbook @index = index @name = name - @colinfo = {} + @col_info = {} @cell_data_table = [] @excel_version = 2007 @palette = workbook.palette @default_url_format = workbook.default_url_format @max_url_length = workbook.max_url_length @page_setup = PageSetup.new @screen_gridlines = true @show_zeros = true + + @xls_rowmax = rowmax + @xls_colmax = colmax + @xls_strmax = strmax @dim_rowmin = nil @dim_rowmax = nil @dim_colmin = nil @dim_colmax = nil @selections = [] @@ -75,25 +84,24 @@ @autofilter_area = nil @filter_on = false @filter_range = [] @filter_cols = {} + @filter_cells = {} @filter_type = {} - @col_sizes = {} @row_sizes = {} - @col_formats = {} @last_shape_id = 1 @rel_count = 0 @hlink_count = 0 @external_hyper_links = [] @external_drawing_links = [] @external_comment_links = [] @external_vml_links = [] - @external_table_links = [] @external_background_links = [] + @external_table_links = [] @drawing_links = [] @vml_drawing_links = [] @charts = [] @images = [] @tables = [] @@ -119,10 +127,11 @@ @default_row_height = 15 @default_row_pixels = 20 @default_col_width = 8.43 @default_col_pixels = 64 @default_row_rezoed = 0 + @default_date_pixels = 68 @merge = [] @has_vml = false @has_header_vml = false @@ -303,11 +312,12 @@ # column or a range of columns. All parameters apart from +first_col+ # and +last_col+ are optional. # def set_column(*args) # Check for a cell reference in A1 notation and substitute row and column - if args[0].to_s =~ /^\D/ + # ruby 3.2 no longer handles =~ for various types + if args[0].respond_to?(:=~) && args[0].to_s =~ /^\D/ _row1, firstcol, _row2, lastcol, *data = substitute_cellref(*args) else firstcol, lastcol, *data = args end @@ -319,10 +329,11 @@ # Ensure 2nd col is larger than first. Also for KB918419 bug. firstcol, lastcol = lastcol, firstcol if firstcol > lastcol width, format, hidden, level, collapsed = data + autofit = 0 # Check that cols are valid and store max and min values with default row. # NOTE: The check shouldn't modify the row dimensions and should only modify # the column dimensions in certain cases. ignore_row = 1 @@ -336,26 +347,24 @@ # Set the limits for the outline levels (0 <= x <= 7). level ||= 0 level = 0 if level < 0 level = 7 if level > 7 + # Excel has a maximum column width of 255 characters. + width = 255.0 if width && width > 255.0 + @outline_col_level = level if level > @outline_col_level # Store the column data based on the first column. Padded for sorting. - @colinfo[sprintf("%05d", firstcol)] = [firstcol, lastcol, width, format, hidden, level, collapsed] + (firstcol..lastcol).each do |col| + @col_info[col] = + Struct.new('ColInfo', :width, :format, :hidden, :level, :collapsed, :autofit) + .new(width, format, hidden, level, collapsed, autofit) + end # Store the column change to allow optimisations. @col_size_changed = 1 - - # Store the col sizes for use when calculating image vertices taking - # hidden columns into account. Also store the column formats. - width ||= @default_col_width - - (firstcol..lastcol).each do |col| - @col_sizes[col] = [width, hidden] - @col_formats[col] = format if format - end end # # Set the width (and properties) of a single column or a range of columns in # pixels rather than character units. @@ -386,10 +395,117 @@ set_column(first_col, last_col, width, format, hidden, level) end # + # autofit() + # + # Simulate autofit based on the data, and datatypes in each column. We do this + # by estimating a pixel width for each cell data. + # + def autofit + col_width = {} + + # Iterate through all the data in the worksheet. + (@dim_rowmin..@dim_rowmax).each do |row_num| + # Skip row if it doesn't contain cell data. + next unless @cell_data_table[row_num] + + (@dim_colmin..@dim_colmax).each do |col_num| + length = 0 + case (cell_data = @cell_data_table[row_num][col_num]) + when StringCellData, RichStringCellData + # Handle strings and rich strings. + # + # For standard shared strings we do a reverse lookup + # from the shared string id to the actual string. For + # rich strings we use the unformatted string. We also + # split multiline strings and handle each part + # separately. + string = cell_data.raw_string + + if string =~ /\n/ + # Handle multiline strings. + length = max = string.split("\n").collect do |str| + xl_string_pixel_width(str) + end.max + else + length = xl_string_pixel_width(string) + end + when DateTimeCellData + + # Handle dates. + # + # The following uses the default width for mm/dd/yyyy + # dates. It isn't feasible to parse the number format + # to get the actual string width for all format types. + length = @default_date_pixels + when NumberCellData + + # Handle numbers. + # + # We use a workaround/optimization for numbers since + # digits all have a pixel width of 7. This gives a + # slightly greater width for the decimal place and + # minus sign but only by a few pixels and + # over-estimation is okay. + length = 7 * cell_data.token.to_s.length + when BooleanCellData + + # Handle boolean values. + # + # Use the Excel standard widths for TRUE and FALSE. + if ptrue?(cell_data.token) + length = 31 + else + length = 36 + end + when FormulaCellData, FormulaArrayCellData, DynamicFormulaArrayCellData + # Handle formulas. + # + # We only try to autofit a formula if it has a + # non-zero value. + if ptrue?(cell_data.data) + length = xl_string_pixel_width(cell_data.data) + end + end + + # If the cell is in an autofilter header we add an + # additional 16 pixels for the dropdown arrow. + if length > 0 && + @filter_cells["#{row_num}:#{col_num}"] + length += 16 + end + + # Add the string lenght to the lookup hash. + max = col_width[col_num] || 0 + col_width[col_num] = length if length > max + end + end + + # Apply the width to the column. + col_width.each do |col_num, pixel_width| + # Convert the string pixel width to a character width using an + # additional padding of 7 pixels, like Excel. + width = pixels_to_width(pixel_width + 7) + + # The max column character width in Excel is 255. + width = 255.0 if width > 255.0 + + # Add the width to an existing col info structure or add a new one. + if @col_info[col_num] + @col_info[col_num].width = width + @col_info[col_num].autofit = 1 + else + @col_info[col_num] = + Struct.new('ColInfo', :width, :format, :hidden, :level, :collapsed, :autofit) + .new(width, nil, 0, 0, 0, 1) + end + end + end + + # # :call-seq: # set_selection(cell_or_cell_range) # # Set which cell or cells are selected in a worksheet. # @@ -934,35 +1050,39 @@ # Match an array ref. elsif _token.respond_to?(:to_ary) write_row(_row, _col, _token, _format, _value1, _value2) elsif _token.respond_to?(:coerce) # Numeric write_number(_row, _col, _token, _format) - # Match integer with leading zero(s) - elsif @leading_zeros && _token =~ /^0\d*$/ - write_string(_row, _col, _token, _format) - elsif _token =~ /\A([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?\Z/ - write_number(_row, _col, _token, _format) - # Match formula - elsif _token =~ /^=/ - write_formula(_row, _col, _token, _format, _value1) - # Match array formula - elsif _token =~ /^\{=.*\}$/ - write_formula(_row, _col, _token, _format, _value1) - # Match blank - elsif _token == '' - # row_col_args.delete_at(2) # remove the empty string from the parameter list - write_blank(_row, _col, _format) - elsif @workbook.strings_to_urls - # Match http, https or ftp URL - if _token =~ %r{\A[fh]tt?ps?://} - write_url(_row, _col, _token, _format, _value1, _value2) - # Match mailto: - elsif _token =~ /\Amailto:/ - write_url(_row, _col, _token, _format, _value1, _value2) - # Match internal or external sheet link - elsif _token =~ /\A(?:in|ex)ternal:/ - write_url(_row, _col, _token, _format, _value1, _value2) + elsif _token.respond_to?(:=~) # String + # Match integer with leading zero(s) + if @leading_zeros && _token =~ /^0\d*$/ + write_string(_row, _col, _token, _format) + elsif _token =~ /\A([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?\Z/ + write_number(_row, _col, _token, _format) + # Match formula + elsif _token =~ /^=/ + write_formula(_row, _col, _token, _format, _value1) + # Match array formula + elsif _token =~ /^\{=.*\}$/ + write_formula(_row, _col, _token, _format, _value1) + # Match blank + elsif _token == '' + # row_col_args.delete_at(2) # remove the empty string from the parameter list + write_blank(_row, _col, _format) + elsif @workbook.strings_to_urls + # Match http, https or ftp URL + if _token =~ %r{\A[fh]tt?ps?://} + write_url(_row, _col, _token, _format, _value1, _value2) + # Match mailto: + elsif _token =~ /\Amailto:/ + write_url(_row, _col, _token, _format, _value1, _value2) + # Match internal or external sheet link + elsif _token =~ /\A(?:in|ex)ternal:/ + write_url(_row, _col, _token, _format, _value1, _value2) + else + write_string(_row, _col, _token, _format) + end else write_string(_row, _col, _token, _format) end else write_string(_row, _col, _token, _format) @@ -1112,11 +1232,11 @@ check_dimensions(_row, _col) store_row_col_max_min_values(_row, _col) index = shared_string_index(_string.length > STR_MAX ? _string[0, STR_MAX] : _string) - store_data_to_table(StringCellData.new(index, _format), _row, _col) + store_data_to_table(StringCellData.new(index, _format, _string), _row, _col) end # # :call-seq: # write_rich_string(row, column, (string | format, string)+, [,cell_format]) @@ -1141,17 +1261,20 @@ # Check that row and col are valid and store max and min values check_dimensions(_row, _col) store_row_col_max_min_values(_row, _col) - _fragments, _length = rich_strings_fragments(_rich_strings) + _fragments, _raw_string = rich_strings_fragments(_rich_strings) # can't allow 2 formats in a row return -4 unless _fragments + # Check that the string si < 32767 chars. + return 3 if _raw_string.size > @xls_strmax + index = shared_string_index(xml_str_of_rich_string(_fragments)) - store_data_to_table(StringCellData.new(index, _xf), _row, _col) + store_data_to_table(RichStringCellData.new(index, _xf, _raw_string), _row, _col) end # # :call-seq: # write_blank(row, col, format) @@ -1677,11 +1800,11 @@ store_row_col_max_min_values(_row, _col) date_time = convert_date_time(_str) if date_time - store_data_to_table(NumberCellData.new(date_time, _format), _row, _col) + store_data_to_table(DateTimeCellData.new(date_time, _format), _row, _col) else # If the date isn't valid then write it as a string. write_string(_row, _col, _str, _format) end end @@ -2162,10 +2285,15 @@ _col1, _col2 = _col2, _col1 if _col2 < _col1 @autofilter_area = convert_name_area(_row1, _col1, _row2, _col2) @autofilter_ref = xl_range(_row1, _row2, _col1, _col2) @filter_range = [_col1, _col2] + + # Store the filter cell positions for use in the autofit calculation. + (_col1.._col2).each do |col| + @filter_cells["#{_row1}:#{col}"] = 1 + end end # # Set the column filter criteria. # @@ -2504,12 +2632,12 @@ def external_links [ @external_hyper_links, @external_drawing_links, @external_vml_links, - @external_table_links, @external_background_links, + @external_table_links, @external_comment_links ].reject { |a| a.empty? } end def drawing_links @@ -2633,10 +2761,37 @@ end private # + # Compare adjacent column information structures. + # + def compare_col_info(col_options, previous_options) + if !col_options.width.nil? != !previous_options.width.nil? + return nil + end + if col_options.width && previous_options.width && + col_options.width != previous_options.width + return nil + end + + if !col_options.format.nil? != !previous_options.format.nil? + return nil + end + if col_options.format && previous_options.format && + col_options.format != previous_options.format + return nil + end + + return nil if col_options.hidden != previous_options.hidden + return nil if col_options.level != previous_options.level + return nil if col_options.collapsed != previous_options.collapsed + + true + end + + # # Get the index used to address a drawing rel link. # def drawing_rel_index(target = nil) if !target # Undefined values for drawings like charts will always be unique. @@ -2683,13 +2838,13 @@ # def rich_strings_fragments(rich_strings) # :nodoc: # Create a temp format with the default font for unformatted fragments. default = Format.new(0) - length = 0 # String length. last = 'format' pos = 0 + raw_string = '' fragments = [] rich_strings.each do |token| if token.respond_to?(:xf_index) # Can't allow 2 formats in a row @@ -2706,16 +2861,16 @@ else # If previous token wasn't a format add one before the string. fragments << default << token end - length += token.size # Keep track of actual string length. + raw_string += token # Keep track of actual string length. last = 'string' end pos += 1 end - [fragments, length] + [fragments, raw_string] end def xml_str_of_rich_string(fragments) # Create a temp XML::Writer object and use it to write the rich string # XML to a string. @@ -2942,12 +3097,13 @@ # we use the default value. A hidden column is treated as having a width of # zero unless it has the special "object_position" of 4 (size with cells). # def size_col(col, anchor = 0) # :nodoc: # Look up the cell value to see if it has been changed. - if @col_sizes[col] - width, hidden = @col_sizes[col] + if @col_info[col] + width = @col_info[col].width || @default_col_width + hidden = @col_info[col].hidden # Convert to pixels. pixels = if hidden == 1 && anchor != 4 0 elsif width < 1 @@ -3274,32 +3430,26 @@ button end # - # Based on the algorithm provided by Daniel Rentz of OpenOffice. - # + # Hash a worksheet password. Based on the algorithm in ECMA-376-4:2016, + # Office Open XML File Foemats -- Transitional Migration Features, + # Additional attributes for workbookProtection element (Part 1, ยง18.2.29). # def encode_password(password) # :nodoc: - i = 0 - chars = password.split(//) - count = chars.size + hash = 0 - chars.collect! do |char| - i += 1 - char = char.ord << i - low_15 = char & 0x7fff - high_15 = char & (0x7fff << 15) - high_15 = high_15 >> 15 - char = low_15 | high_15 + password.reverse.split(//).each do |char| + hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff) + hash ^= char.ord end - encoded_password = 0x0000 - chars.each { |c| encoded_password ^= c } - encoded_password ^= count - encoded_password ^= 0xCE4B + hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff) + hash ^= password.length + hash ^= 0xCE4B - sprintf("%X", encoded_password) + sprintf("%X", hash) end # # Write the <worksheet> element. This is the root element of Worksheet. # @@ -3473,14 +3623,46 @@ # # Write the <cols> element and <col> sub elements. # def write_cols # :nodoc: # Exit unless some column have been formatted. - return if @colinfo.empty? + return if @col_info.empty? @writer.tag_elements('cols') do - @colinfo.keys.sort.each { |col| write_col_info(@colinfo[col]) } + # Use the first element of the column informatin structure to set + # the initial/previous properties. + first_col = @col_info.keys.min + last_col = first_col + previous_options = @col_info[first_col] + deleted_col = first_col + deleted_col_options = previous_options + + @col_info.delete(first_col) + + @col_info.keys.sort.each do |col| + col_options = @col_info[col] + + # Check if the column number is contiguous with the previous + # column and if the properties are the same. + if (col == last_col + 1) && + compare_col_info(col_options, previous_options) + last_col = col + else + # If not contiguous/equal then we write out the current range + # of columns and start again. + write_col_info([first_col, last_col, previous_options]) + first_col = col + last_col = first_col + previous_options = col_options + end + end + + # We will exit the previous loop with one unhandled column range. + write_col_info([first_col, last_col, previous_options]) + + # Put back the deleted first column information structure: + @col_info[deleted_col] = deleted_col_options end end # # Write the <col> element. @@ -3488,17 +3670,18 @@ def write_col_info(args) # :nodoc: @writer.empty_tag('col', col_info_attributes(args)) end def col_info_attributes(args) - min = args[0] || 0 # First formatted column. - max = args[1] || 0 # Last formatted column. - width = args[2] # Col width in user units. - format = args[3] # Format index. - hidden = args[4] || 0 # Hidden flag. - level = args[5] || 0 # Outline level. - collapsed = args[6] || 0 # Outline level. + min = args[0] || 0 # First formatted column. + max = args[1] || 0 # Last formatted column. + width = args[2].width # Col width in user units. + format = args[2].format # Format index. + hidden = args[2].hidden || 0 # Hidden flag. + level = args[2].level || 0 # Outline level. + collapsed = args[2].collapsed || 0 # Outline Collapsed + autofit = args[2].autofit || 0 # Best fit for autofit numbers. xf_index = format ? format.get_xf_index : 0 custom_width = true custom_width = false if width.nil? && hidden == 0 custom_width = false if width == 8.43 @@ -3517,13 +3700,14 @@ ['min', min + 1], ['max', max + 1], ['width', width] ] - attributes << ['style', xf_index] if xf_index != 0 - attributes << ['hidden', 1] if hidden != 0 + attributes << ['style', xf_index] if xf_index != 0 + attributes << ['hidden', 1] if hidden != 0 + attributes << ['bestFit', 1] if autofit != 0 attributes << ['customWidth', 1] if custom_width - attributes << ['outlineLevel', level] if level != 0 + attributes << ['outlineLevel', level] if level != 0 attributes << ['collapsed', 1] if collapsed != 0 attributes end #