lib/write_xlsx/worksheet.rb in write_xlsx-1.11.1 vs lib/write_xlsx/worksheet.rb in write_xlsx-1.11.2

- old
+ new

@@ -226,21 +226,32 @@ end # # Hide this worksheet. # - def hide - @hidden = true + def hide(hidden = :hidden) + @hidden = hidden @selected = false @workbook.activesheet = 0 if @workbook.activesheet == @index @workbook.firstsheet = 0 if @workbook.firstsheet == @index end + # + # Hide this worksheet. This can only be unhidden from VBA. + # + def very_hidden + hide(:very_hidden) + end + def hidden? # :nodoc: - @hidden + @hidden == :hidden end + def very_hidden? # :nodoc: + @hidden == :very_hidden + end + # # Set this worksheet as the first visible sheet. This is necessary # when there are a large number of worksheets and the activated # worksheet is not visible on the screen. # @@ -269,11 +280,11 @@ # def unprotect_range(range, range_name = nil, password = nil) if range.nil? raise "The range must be defined in unprotect_range())\n" else - range = range.gsub(/\$/, "") + range = range.gsub("$", "") range = range.sub(/^=/, "") @num_protected_ranges += 1 end range_name ||= "Range#{@num_protected_ranges}" @@ -610,15 +621,24 @@ end # # This method is used to display the worksheet in "Page View/Layout" mode. # - def set_page_view(flag = true) - @page_view = !!flag + def set_page_view(flag = 1) + @page_view = flag end # + # set_pagebreak_view + # + # Set the page view mode. + # + def set_pagebreak_view + @page_view = 2 + end + + # # Set the colour of the worksheet tab. # def tab_color=(color) @tab_color = Colors.new.color(color) end @@ -646,11 +666,11 @@ # def set_header(string = '', margin = 0.3, options = {}) raise 'Header string must be less than 255 characters' if string.length > 255 # Replace the Excel placeholder &[Picture] with the internal &G. - @page_setup.header = string.gsub(/&\[Picture\]/, '&G') + @page_setup.header = string.gsub("&[Picture]", '&G') @page_setup.header_footer_aligns = options[:align_with_margins] if options[:align_with_margins] @page_setup.header_footer_scales = options[:scale_with_doc] if options[:scale_with_doc] @@ -662,11 +682,11 @@ ].each do |p| @header_images << [options[p.first], p.last] if options[p.first] end # placeholeder /&G/ の数 - placeholder_count = @page_setup.header.scan(/&G/).count + placeholder_count = @page_setup.header.scan("&G").count image_count = @header_images.count raise "Number of header image (#{image_count}) doesn't match placeholder count (#{placeholder_count}) in string: #{@page_setup.header}" if image_count != placeholder_count @@ -683,11 +703,11 @@ raise 'Footer string must be less than 255 characters' if string.length > 255 @page_setup.footer = string.dup # Replace the Excel placeholder &[Picture] with the internal &G. - @page_setup.footer = string.gsub(/&\[Picture\]/, '&G') + @page_setup.footer = string.gsub("&[Picture]", '&G') @page_setup.header_footer_aligns = options[:align_with_margins] if options[:align_with_margins] @page_setup.header_footer_scales = options[:scale_with_doc] if options[:scale_with_doc] @@ -699,11 +719,11 @@ ].each do |p| @footer_images << [options[p.first], p.last] if options[p.first] end # placeholeder /&G/ の数 - placeholder_count = @page_setup.footer.scan(/&G/).count + placeholder_count = @page_setup.footer.scan("&G").count image_count = @footer_images.count raise "Number of footer image (#{image_count}) doesn't match placeholder count (#{placeholder_count}) in string: #{@page_setup.footer}" if image_count != placeholder_count @@ -1318,41 +1338,61 @@ # # Utility method to strip equal sign and array braces from a formula # and also expand out future and dynamic array formulas. # - def prepare_formula(given_formula) + def prepare_formula(given_formula, expand_future_functions = nil) # Ignore empty/null formulas. return given_formula unless ptrue?(given_formula) # Remove array formula braces and the leading =. formula = given_formula.sub(/^\{(.*)\}$/, '\1').sub(/^=/, '') # # Don't expand formulas that the user has already expanded. return formula if formula =~ /_xlfn\./ # Expand dynamic array formulas. - formula = expand_formula(formula, 'LET\(') + formula = expand_formula(formula, 'ANCHORARRAY\(') + formula = expand_formula(formula, 'BYCOL\(') + formula = expand_formula(formula, 'BYROW\(') + formula = expand_formula(formula, 'CHOOSECOLS\(') + formula = expand_formula(formula, 'CHOOSEROWS\(') + formula = expand_formula(formula, 'DROP\(') + formula = expand_formula(formula, 'EXPAND\(') + formula = expand_formula(formula, 'FILTER\(', '._xlws') + formula = expand_formula(formula, 'HSTACK\(') formula = expand_formula(formula, 'LAMBDA\(') + formula = expand_formula(formula, 'MAKEARRAY\(') + formula = expand_formula(formula, 'MAP\(') + formula = expand_formula(formula, 'RANDARRAY\(') + formula = expand_formula(formula, 'REDUCE\(') + formula = expand_formula(formula, 'SCAN\(') + formula = expand_formula(formula, 'SEQUENCE\(') formula = expand_formula(formula, 'SINGLE\(') + formula = expand_formula(formula, 'SORT\(', '._xlws') formula = expand_formula(formula, 'SORTBY\(') + formula = expand_formula(formula, 'SWITCH\(') + formula = expand_formula(formula, 'TAKE\(') + formula = expand_formula(formula, 'TEXTSPLIT\(') + formula = expand_formula(formula, 'TOCOL\(') + formula = expand_formula(formula, 'TOROW\(') formula = expand_formula(formula, 'UNIQUE\(') - formula = expand_formula(formula, 'XMATCH\(') + formula = expand_formula(formula, 'VSTACK\(') + formula = expand_formula(formula, 'WRAPCOLS\(') + formula = expand_formula(formula, 'WRAPROWS\(') formula = expand_formula(formula, 'XLOOKUP\(') - formula = expand_formula(formula, 'SEQUENCE\(') - formula = expand_formula(formula, 'RANDARRAY\(') - formula = expand_formula(formula, 'SORT\(', '._xlws') - formula = expand_formula(formula, 'ANCHORARRAY\(') - formula = expand_formula(formula, 'FILTER\(', '._xlws') - return formula unless @use_future_functions + if !@use_future_functions && !ptrue?(expand_future_functions) + return formula + end # Future functions. formula = expand_formula(formula, 'ACOTH\(') formula = expand_formula(formula, 'ACOT\(') formula = expand_formula(formula, 'AGGREGATE\(') formula = expand_formula(formula, 'ARABIC\(') + formula = expand_formula(formula, 'ARRAYTOTEXT\(') formula = expand_formula(formula, 'BASE\(') formula = expand_formula(formula, 'BETA.DIST\(') formula = expand_formula(formula, 'BETA.INV\(') formula = expand_formula(formula, 'BINOM.DIST.RANGE\(') formula = expand_formula(formula, 'BINOM.DIST\(') @@ -1413,11 +1453,13 @@ formula = expand_formula(formula, 'IMSECH\(') formula = expand_formula(formula, 'IMSEC\(') formula = expand_formula(formula, 'IMSINH\(') formula = expand_formula(formula, 'IMTAN\(') formula = expand_formula(formula, 'ISFORMULA\(') + formula = expand_formula(formula, 'ISOMITTED\(') formula = expand_formula(formula, 'ISOWEEKNUM\(') + formula = expand_formula(formula, 'LET\(') formula = expand_formula(formula, 'LOGNORM.DIST\(') formula = expand_formula(formula, 'LOGNORM.INV\(') formula = expand_formula(formula, 'MAXIFS\(') formula = expand_formula(formula, 'MINIFS\(') formula = expand_formula(formula, 'MODE.MULT\(') @@ -1448,28 +1490,30 @@ formula = expand_formula(formula, 'SHEETS\(') formula = expand_formula(formula, 'SHEET\(') formula = expand_formula(formula, 'SKEW.P\(') formula = expand_formula(formula, 'STDEV.P\(') formula = expand_formula(formula, 'STDEV.S\(') - formula = expand_formula(formula, 'SWITCH\(') formula = expand_formula(formula, 'T.DIST.2T\(') formula = expand_formula(formula, 'T.DIST.RT\(') formula = expand_formula(formula, 'T.DIST\(') formula = expand_formula(formula, 'T.INV.2T\(') formula = expand_formula(formula, 'T.INV\(') formula = expand_formula(formula, 'T.TEST\(') + formula = expand_formula(formula, 'TEXTAFTER\(') + formula = expand_formula(formula, 'TEXTBEFORE\(') formula = expand_formula(formula, 'TEXTJOIN\(') formula = expand_formula(formula, 'UNICHAR\(') formula = expand_formula(formula, 'UNICODE\(') + formula = expand_formula(formula, 'VALUETOTEXT\(') formula = expand_formula(formula, 'VAR.P\(') formula = expand_formula(formula, 'VAR.S\(') formula = expand_formula(formula, 'WEBSERVICE\(') formula = expand_formula(formula, 'WEIBULL.DIST\(') + formula = expand_formula(formula, 'XMATCH\(') formula = expand_formula(formula, 'XOR\(') expand_formula(formula, 'Z.TEST\(') end - private :prepare_formula # # :call-seq: # write_formula(row, column, formula [ , format [ , value ] ]) # @@ -1549,11 +1593,11 @@ else "#{xl_rowcol_to_cell(row1, col1)}:#{xl_rowcol_to_cell(row2, col2)}" end # Modify the formula string, as needed. - formula = prepare_formula(formula) + formula = prepare_formula(formula, 1) store_data_to_table( if type == 'a' FormulaArrayCellData.new(formula, xf, range, value) elsif type == 'd' @@ -2924,11 +2968,11 @@ # Remove leading and trailing quotes and unescape other quotes tokens.map! do |token| token.sub!(/^"/, '') token.sub!(/"$/, '') - token.gsub!(/""/, '"') + token.gsub!('""', '"') # if token is number, convert to numeric. if token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ token.to_f == token.to_i ? token.to_i : token.to_f else @@ -3206,17 +3250,17 @@ target = escape_url(url) if url =~ %r{^[fh]tt?ps?://} || url =~ /^mailto:/ if url =~ /^external:/ target = escape_url(url.sub(/^external:/, '')) # Additional escape not required in worksheet hyperlinks - target = target.gsub(/#/, '%23') + target = target.gsub("#", '%23') # Prefix absolute paths (not relative) with file:/// target = if target =~ /^\w:/ || target =~ /^\\\\/ "file:///#{target}" else - target.gsub(/\\/, '/') + target.gsub("\\", '/') end end if url =~ /^internal:/ target = url.sub(/^internal:/, '#') @@ -3435,11 +3479,11 @@ # Office Open XML File Foemats -- Transitional Migration Features, # Additional attributes for workbookProtection element (Part 1, §18.2.29). # def encode_password(password) # :nodoc: hash = 0 - password.reverse.split(//).each do |char| + password.reverse.split("").each do |char| hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff) hash ^= char.ord end hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff) @@ -3557,20 +3601,31 @@ # Turn outlines off. Also required in the outlinePr element. attributes << ["showOutlineSymbols", 0] if @outline_on # Set the page view/layout mode if required. - # TODO. Add pageBreakPreview mode when requested. - attributes << %w[view pageLayout] if page_view? + case @page_view + when 1 + attributes << %w[view pageLayout] + when 2 + attributes << %w[view pageBreakPreview] + end # Set the first visible cell. attributes << ['topLeftCell', @top_left_cell] if ptrue?(@top_left_cell) # Set the zoom level. if @zoom != 100 - attributes << ['zoomScale', @zoom] unless page_view? - attributes << ['zoomScaleNormal', @zoom] if zoom_scale_normal? + attributes << ['zoomScale', @zoom] + + if @page_view == 1 + attributes << ['zoomScalePageLayoutView', @zoom] + elsif @page_view == 2 + attributes << ['zoomScaleSheetLayoutView', @zoom] + elsif ptrue?(@zoom_scale_normal) + attributes << ['zoomScaleNormal', @zoom] + end end attributes << ['workbookViewId', 0] if @panes.empty? && @selections.empty? @@ -3752,13 +3807,14 @@ !@set_rows[row_num] && !@cell_data_table[row_num] && !@comments.has_comment_in_row?(row_num) end def write_cell_column_dimension(row_num) # :nodoc: row = @cell_data_table[row_num] + row_name = (row_num + 1).to_s (@dim_colmin..@dim_colmax).each do |col_num| if (cell = row[col_num]) - cell.write_cell(self, row_num, col_num) + cell.write_cell(self, row_num, row_name, col_num) end end end # @@ -4751,13 +4807,9 @@ ptrue?(@vba_codename) end def zoom_scale_normal? # :nodoc: ptrue?(@zoom_scale_normal) - end - - def page_view? # :nodoc: - !!@page_view end def right_to_left? # :nodoc: !!@right_to_left end