lib/write_xlsx/workbook.rb in write_xlsx-0.0.4 vs lib/write_xlsx/workbook.rb in write_xlsx-0.51.0

- old
+ new

@@ -1,10 +1,12 @@ # -*- coding: utf-8 -*- require 'write_xlsx/package/xml_writer_simple' require 'write_xlsx/package/packager' require 'write_xlsx/worksheet' +require 'write_xlsx/chartsheet' require 'write_xlsx/format' +require 'write_xlsx/shape' require 'write_xlsx/utility' require 'write_xlsx/chart' require 'write_xlsx/zip_file_utils' require 'tmpdir' require 'tempfile' @@ -20,14 +22,14 @@ attr_reader :font_count, :num_format_count, :border_count, :fill_count, :custom_colors attr_reader :worksheets, :sheetnames, :charts, :drawings, :num_comment_files, :named_ranges attr_reader :doc_properties attr_reader :image_types, :images attr_reader :shared_strings - + attr_accessor :table_count # - # A new Excel workbook is created using the new() constructor which accepts either a filename - # or a IO object as a parameter. + # A new Excel workbook is created using the new() constructor + # which accepts either a filename or an IO object as a parameter. # The following example creates a new Excel file based on a filename: # # workbook = WriteXLSX.new('filename.xlsx') # worksheet = workbook.add_worksheet # worksheet.write(0, 0, 'Hi Excel!') @@ -38,19 +40,20 @@ # workbook1 = WriteXLSX.new(filename) # workbook2 = WriteXLSX.new('/tmp/filename.xlsx') # workbook3 = WriteXLSX.new("c:\\tmp\\filename.xlsx") # workbook4 = WriteXLSX.new('c:\tmp\filename.xlsx') # - # The last two examples demonstrates how to create a file on DOS or Windows where it is - # necessary to either escape the directory separator \ or to use single quotes to ensure - # that it isn't interpolated. + # The last two examples demonstrates how to create a file on DOS or Windows + # where it is necessary to either escape the directory separator \ + # or to use single quotes to ensure that it isn't interpolated. # - # It is recommended that the filename uses the extension .xlsx rather than .xls since - # the latter causes an Excel warning when used with the XLSX format. + # It is recommended that the filename uses the extension .xlsx + # rather than .xls since the latter causes an Excel warning + # when used with the XLSX format. # - # The new() constructor returns a WriteXLSX object that you can use to add worksheets and - # store data. + # The new() constructor returns a WriteXLSX object that you can use to + # add worksheets and store data. # # You can also pass a valid IO object to the new() constructor. # # xlsx = StringIO.new # workbook = WriteXLSX.new(xlsx) @@ -95,12 +98,20 @@ @named_ranges = [] @custom_colors = [] @doc_properties = {} @local_time = Time.now @num_comment_files = 0 + @optimization = 0 + @x_window = 240 + @y_window = 15 + @window_width = 16095 + @window_height = 9660 + @tab_ratio = 500 + @table_count = 0 @image_types = {} @images = [] + @images_seen = {} # Structures for the shared strings data. @shared_strings = Package::SharedStrings.new add_format(default_formats.merge(:xf_index => 0)) @@ -112,11 +123,11 @@ # def close # In case close() is called twice, by user and by DESTROY. return if @fileclosed - @fileclosed = 1 + @fileclosed = true store_workbook end # get array of Worksheet objects # @@ -177,11 +188,11 @@ # def set_1904(mode = true) unless sheets.empty? raise "set_1904() must be called before add_worksheet()" end - @date_1904 = (!mode || mode == 0) ? false : true + @date_1904 = ptrue?(mode) end def get_1904 @date_1904 end @@ -259,24 +270,21 @@ # and it must be less than 32 characters. # In addition, you cannot use the same, case insensitive, # sheetname for more than one worksheet. # def add_worksheet(name = '') - index = @worksheets.size name = check_sheetname(name) - - worksheet = Worksheet.new(self, index, name) - @worksheets[index] = worksheet - @sheetnames[index] = name - + worksheet = Worksheet.new(self, @worksheets.size, name) + @worksheets << worksheet + @sheetnames << name worksheet end # # This method is use to create a new chart either as a standalone worksheet # (the default) or as an embeddable object that can be inserted into - # a worksheet via the insert_chart() Worksheet method. + # a worksheet via the Worksheet#insert_chart method. # # chart = workbook.add_chart(:type => 'column') # # The properties that can be set are: # @@ -285,11 +293,12 @@ # :name (optional) # :embedded (optional) # # :type # - # This is a required parameter. It defines the type of chart that will be created. + # This is a required parameter. + # It defines the type of chart that will be created. # # chart = workbook.add_chart(:type => 'line') # # The available types are: # @@ -313,19 +322,20 @@ # # :name # # Set the name for the chart sheet. The name property is optional and # if it isn't supplied will default to Chart1 .. n. The name must be - # a valid Excel worksheet name. See add_worksheet for more details on - # valid sheet names. The name property can be omitted for embedded charts. + # a valid Excel worksheet name. See add_worksheet + # for more details on valid sheet names. The name property can be + # omitted for embedded charts. # # chart = workbook.add_chart(:type => 'line', :name => 'Results Chart') # # :embedded # # Specifies that the Chart object will be inserted in a worksheet - # via the insert_chart Worksheet method. It is an error to try insert + # via the Worksheet#insert_chart method. It is an error to try insert # a Chart that doesn't have this flag set. # # chart = workbook.add_chart(:type => 'line', :embedded => 1) # # # Configure the chart. @@ -337,50 +347,40 @@ # See Chart for details on how to configure the chart object # once it is created. See also the chart_*.pl programs in the examples # directory of the distro. # def add_chart(params = {}) - name = '' - index = @worksheets.size - # Type must be specified so we can create the required chart instance. - type = params[:type] + type = params[:type] + embedded = params[:embedded] + name = params[:name] raise "Must define chart type in add_chart()" unless type - # Ensure that the chart defaults to non embedded. - embedded = params[:embedded] || 0 + chart = Chart.factory(type, params[:subtype]) + chart.palette = @palette - # Check the worksheet name for non-embedded charts. - name = check_sheetname(params[:name], 1) unless embedded - - chart = Chart.factory(type) - # Get an incremental id to use for axes ids. chart.id = @charts.size # If the chart isn't embedded let the workbook control it. - if embedded + if ptrue?(embedded) + chart.name = name if name + # Set index to 0 so that the activate() and set_first_sheet() methods # point back to the first worksheet if used for embedded charts. chart.index = 0 - chart.palette = @palette chart.set_embedded_config_data - @charts << chart - - return chart else - chartsheet = Chartsheet.new(self, name, index) - chart.palette = @palette + # Check the worksheet name for non-embedded charts. + sheetname = check_chart_sheetname(name) + chartsheet = Chartsheet.new(self, @worksheets.size, sheetname) chartsheet.chart = chart - chartsheet.drawing = Drawing.new - @worksheets.index = chartsheet - @sheetnames.index = name - - @charts << chart - - return chartsheet + @worksheets << chartsheet + @sheetnames << sheetname end + @charts << chart + ptrue?(embedded) ? chart : chartsheet end # # The add_format method can be used to create new Format objects # which are used to apply formatting to a cell. You can either define @@ -405,28 +405,261 @@ format end # + # The add_shape() method can be used to create new shapes that may be + # inserted into a worksheet. + # + # You can either define the properties at creation time via a hash of + # property values or later via method calls. + # + # # Set properties at creation. + # plus = workbook.add_shape( + # :type => 'plus', + # :id => 3, + # :width => pw, + # :height => ph + # ) + # + # # Default rectangle shape. Set properties later. + # rect = workbook.add_shape + # + # See also the shape*.rb programs in the examples directory of the distro. + # + # === Shape Properties + # + # Any shape property can be queried or modified by [] like hash. + # + # ellipse = workbook.add_shape(properties) + # ellipse[:type] = 'cross' # No longer an ellipse ! + # type = ellipse[:type] # Find out what it really is. + # + # The properties of a shape object that can be defined via add_shape are + # shown below. + # + # ====:name + # + # Defines the name of the shape. This is an optional property and the shape + # will be given a default name if not supplied. The name is generally only + # used by Excel Macros to refer to the object. + # + # ====:type + # + # Defines the type of the object such as :rect, :ellipse OR :triangle. + # + # ellipse = workbook.add_shape(:type => :ellipse) + # + # The default type is :rect. + # + # The full list of available shapes is shown below. + # + # See also the shape_all.rb program in the examples directory of the distro. + # It creates an example workbook with all supported shapes labelled with + # their shape names. + # + # ===== Basic Shapes + # + # blockArc can chevron cube decagon + # diamond dodecagon donut ellipse funnel + # gear6 gear9 heart heptagon hexagon + # homePlate lightningBolt line lineInv moon + # nonIsoscelesTrapezoid noSmoking octagon parallelogram pentagon + # pie pieWedge plaque rect round1Rect + # round2DiagRect round2SameRect roundRect rtTriangle smileyFace + # snip1Rect snip2DiagRect snip2SameRect snipRoundRect star10 + # star12 star16 star24 star32 star4 + # star5 star6 star7 star8 sun + # teardrop trapezoid triangle + # + # ===== Arrow Shapes + # + # bentArrow bentUpArrow circularArrow curvedDownArrow + # curvedLeftArrow curvedRightArrow curvedUpArrow downArrow + # leftArrow leftCircularArrow leftRightArrow leftRightCircularArrow + # leftRightUpArrow leftUpArrow notchedRightArrow quadArrow + # rightArrow stripedRightArrow swooshArrow upArrow + # upDownArrow uturnArrow + # + # ===== Connector Shapes + # + # bentConnector2 bentConnector3 bentConnector4 + # bentConnector5 curvedConnector2 curvedConnector3 + # curvedConnector4 curvedConnector5 straightConnector1 + # + # ===== Callout Shapes + # + # accentBorderCallout1 accentBorderCallout2 accentBorderCallout3 + # accentCallout1 accentCallout2 accentCallout3 + # borderCallout1 borderCallout2 borderCallout3 + # callout1 callout2 callout3 + # cloudCallout downArrowCallout leftArrowCallout + # leftRightArrowCallout quadArrowCallout rightArrowCallout + # upArrowCallout upDownArrowCallout wedgeEllipseCallout + # wedgeRectCallout wedgeRoundRectCallout + # + # ===== Flow Chart Shapes + # + # flowChartAlternateProcess flowChartCollate flowChartConnector + # flowChartDecision flowChartDelay flowChartDisplay + # flowChartDocument flowChartExtract flowChartInputOutput + # flowChartInternalStorage flowChartMagneticDisk flowChartMagneticDrum + # flowChartMagneticTape flowChartManualInput flowChartManualOperation + # flowChartMerge flowChartMultidocument flowChartOfflineStorage + # flowChartOffpageConnector flowChartOnlineStorage flowChartOr + # flowChartPredefinedProcess flowChartPreparation flowChartProcess + # flowChartPunchedCard flowChartPunchedTape flowChartSort + # flowChartSummingJunction flowChartTerminator + # + # ===== Action Shapes + # + # actionButtonBackPrevious actionButtonBeginning actionButtonBlank + # actionButtonDocument actionButtonEnd actionButtonForwardNext + # actionButtonHelp actionButtonHome actionButtonInformation + # actionButtonMovie actionButtonReturn actionButtonSound + # + # ===== Chart Shapes + # + # Not to be confused with Excel Charts. + # + # chartPlus chartStar chartX + # + # ===== Math Shapes + # + # mathDivide mathEqual mathMinus mathMultiply mathNotEqual mathPlus + # + # ===== Starts and Banners + # + # arc bevel bracePair bracketPair chord + # cloud corner diagStripe doubleWave ellipseRibbon + # ellipseRibbon2 foldedCorner frame halfFrame horizontalScroll + # irregularSeal1 irregularSeal2 leftBrace leftBracket leftRightRibbon + # plus ribbon ribbon2 rightBrace rightBracket + # verticalScroll wave + # + # ===== Tab Shapes + # + # cornerTabs plaqueTabs squareTabs + # + # ==== :text + # + # This property is used to make the shape act like a text box. + # + # rect = workbook.add_shape(:type => 'rect', :text => "Hello \nWorld") + # + # The Text is super-imposed over the shape. The text can be wrapped using + # the newline character \n. + # + # ==== :id + # + # Identification number for internal identification. This number will be + # auto-assigned, if not assigned, or if it is a duplicate. + # + # ==== :format + # + # Workbook format for decorating the shape horizontally and/or vertically. + # + # ==== :rotation + # + # Shape rotation, in degrees, from 0 to 360 + # + # ==== :line, :fill + # + # Shape color for the outline and fill. + # Colors may be specified as a color index, or in RGB format, i.e. AA00FF. + # + # ==== :link_type + # + # Line type for shape outline. The default is solid. + # The list of possible values is: + # + # dash, sysDot, dashDot, lgDash, lgDashDot, lgDashDotDot, solid + # + # ==== :valign, :align + # + # Text alignment within the shape. + # + # Vertical alignment can be: + # + # Setting Meaning + # ======= ======= + # t Top + # ctr Centre + # b Bottom def add_shape(properties) + # + # The default is to center both horizontally and vertically. + # + # ==== :scale_x, :scale_y + # + # + # Scale factor in x and y dimension, for scaling the shape width and height. The default value is 1. + # + # Scaling may be set on the shape object or via insert_shape(). + # + # ==== :adjustments + # + # Adjustment of shape vertices. Most shapes do not use this. For some + # shapes, there is a single adjustment to modify the geometry. + # For instance, the plus shape has one adjustment to control the width + # of the spokes. + # + # Connectors can have a number of adjustments to control the shape + # routing. Typically, a connector will have 3 to 5 handles for routing + # the shape. The adjustment is in percent of the distance from the + # starting shape to the ending shape, alternating between the x and y + # dimension. Adjustments may be negative, to route the shape away + # from the endpoint. + # + # ==== :stencil + # + # Shapes work in stencil mode by default. That is, once a shape is + # inserted, its connection is separated from its master. + # The master shape may be modified after an instance is inserted, + # and only subsequent insertions will show the modifications. + # + # This is helpful for Org charts, where an employee shape may be + # created once, and then the text of the shape is modified for each + # employee. + # + # The insert_shape() method returns a reference to the inserted + # shape (the child). + # + # Stencil mode can be turned off, allowing for shape(s) to be + # modified after insertion. In this case the insert_shape() method + # returns a reference to the inserted shape (the master). + # This is not very useful for inserting multiple shapes, + # since the x/y coordinates also gets modified. + # + def add_shape(properties) + shape = Shape.new(properties) + shape[:palette] = @palette + + @shapes ||= [] + @shapes << shape #Store shape reference. + shape + end + + # # Create a defined name in Excel. We handle global/workbook level names and # local/worksheet names. # - # This method is used to defined a name that can be used to represent a value, - # a single cell or a range of cells in a workbook. + # This method is used to defined a name that can be used to represent + # a value, a single cell or a range of cells in a workbook. # - # For example to set a global/workbook name: + # For example to set a global/workbook name: # # # Global/workbook names. # workbook.define_name('Exchange_rate', '=0.96') # workbook.define_name('Sales', '=Sheet1!$G$1:$H$10') # # It is also possible to define a local/worksheet name by prefixing the name # with the sheet name using the syntax sheetname!definedname: # # # Local/worksheet name. # workbook.define_name('Sheet2!Sales', '=Sheet2!$G$1:$G$10') - # If the sheet name contains spaces or special characters + # + # If the sheet name contains spaces or special characters # you must enclose it in single quotes like in Excel: # # workbook.define_name("'New Data'!Sales", '=Sheet2!$G$1:$G$10') # # See the defined_name.rb program in the examples dir of the distro. @@ -443,39 +676,37 @@ if name =~ /^(.*)!(.*)$/ sheetname = $1 name = $2 sheet_index = get_sheet_index(sheetname) else - sheet_index =-1 # Use -1 to indicate global names. + sheet_index = -1 # Use -1 to indicate global names. end # Warn if the sheet index wasn't found. if !sheet_index raise "Unknown sheet name #{sheetname} in defined_name()\n" - return -1 end # Warn if the sheet name contains invalid chars as defined by Excel help. if name !~ %r!^[a-zA-Z_\\][a-zA-Z_.]+! raise "Invalid characters in name '#{name}' used in defined_name()\n" - return -1 end # Warn if the sheet 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()\n" - return -1 end @defined_names.push([ name, sheet_index, formula]) end # # The set_properties method can be used to set the document properties # of the Excel file created by WriteXLSX. These properties are visible # when you use the Office Button -> Prepare -> Properties option in Excel - # and are also available to external applications that read or index windows files. + # and are also available to external applications that read or index windows + # files. # # The properties should be passed in hash format as follows: # # workbook.set_properties( # :title => 'This is an example spreadsheet', @@ -493,11 +724,12 @@ # :category # :keywords # :comments # :status # - # See also the properties.rb program in the examples directory of the distro. + # See also the properties.rb program in the examples directory + # of the distro. # def set_properties(params) # Ignore if no args were passed. return -1 if params.empty? @@ -531,11 +763,12 @@ # Change the RGB components of the elements in the colour palette. # # The set_custom_color() method can be used to override one of the built-in # palette values with a more suitable colour. # - # The value for _index_ should be in the range 8..63, see "COLOURS IN EXCEL". + # The value for _index_ should be in the range 8..63, + # see "COLOURS IN EXCEL". # # The default named colours use the following indices: # # 8 => black # 9 => white @@ -730,50 +963,53 @@ # # 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, chart = nil) #:nodoc: - name ||= '' - invalid_char = /[\[\]:*?\/\\]/ - + def check_sheetname(name) #:nodoc: # Increment the Sheet/Chart number used for default sheet names below. - if chart - @chartname_count += 1 - else - @sheetname_count += 1 - end + @sheetname_count += 1 # Supply default Sheet/Chart name if none has been defined. - if name == '' - if chart - name = "#{@chart_name}#{@chartname_count}" - else - name = "#{@sheet_name}#{@sheetname_count}" - end + if name.nil? || name == '' + name = "#{@sheet_name}#{@sheetname_count}" end + check_valid_sheetname(name) + name + end + def check_chart_sheetname(name) + @chartname_count += 1 + if name.nil? || name == '' + name = "#{@chart_name}#{@chartname_count}" + end + check_valid_sheetname(name) + name + end + + def check_valid_sheetname(name) # Check that sheet name is <= 31. Excel limit. raise "Sheetname #{name} must be <= #{SHEETNAME_MAX} chars" if name.length > SHEETNAME_MAX # Check that sheetname doesn't contain any invalid characters + invalid_char = /[\[\]:*?\/\\]/ if name =~ invalid_char raise 'Invalid character []:*?/\\ in worksheet name: ' + name end # Check that the worksheet name doesn't already exist since this is a fatal # error in Excel 97. The check must also exclude case insensitive matches. - @worksheets.each do |worksheet| - name_a = name - name_b = worksheet.name - - if name_a.downcase == name_b.downcase - raise "Worksheet name '#{name}', with case ignored, is already used." - end + unless is_sheetname_uniq?(name) + raise "Worksheet name '#{name}', with case ignored, is already used." end + end - name + def is_sheetname_uniq?(name) + @worksheets.each do |worksheet| + return false if name.downcase == worksheet.name.downcase + end + true end # # 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 ). @@ -847,15 +1083,18 @@ @writer.tag_elements('bookViews') { write_workbook_view } end def write_workbook_view #:nodoc: attributes = [ - 'xWindow', 240, - 'yWindow', 15, - 'windowWidth', 16095, - 'windowHeight', 9660 - ] + 'xWindow', @x_window, + 'yWindow', @y_window, + 'windowWidth', @window_width, + 'windowHeight', @window_height + ] + if @tab_ratio != 500 + attributes << 'tabRatio' << @tab_ratio + end if @firstsheet > 0 attributes << 'firstSheet' << @firstsheet end if @activesheet > 0 attributes << 'activeTab' << @activesheet @@ -881,11 +1120,11 @@ if hidden attributes << 'state' << 'hidden' end attributes << 'r:id' << "rId#{sheet_id}" - @writer.empty_tag('sheet', attributes) + @writer.empty_tag_encoded('sheet', attributes) end def write_calc_pr #:nodoc: attributes = ['calcId', 124519] @writer.empty_tag('calcPr', attributes) @@ -954,11 +1193,11 @@ # Ensure that at least one worksheet has been selected. @worksheets.first.select if @activesheet == 0 # Set the active sheet. - @worksheets.each { |sheet| sheet.activate if sheet.index == @activesheet } + @worksheets[@activesheet].activate prepare_comments # Prepare the worksheet cell comments. prepare_defined_names # Set the defined names for the worksheets such as Print Titles. prepare_drawings # Prepare the drawings, charts and images. add_chart_data # Add cached data to charts. @@ -1016,24 +1255,20 @@ # Iterate through the XF Format objects and give them an index to non-default # font elements. # def prepare_fonts #:nodoc: fonts = {} - @xf_formats.each do |format| - key = format.get_font_key - format.has_font(!fonts[key]) - format.font_index = fonts[key] || fonts.size - fonts[key] ||= fonts.size - end + @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? + if format.color? || format.bold? || format.italic? || format.underline? || format.strikeout? format.has_dxf_font(true) end end end @@ -1069,11 +1304,11 @@ num_formats[num_format] = index format.num_format_index = index index += 1 # Only increase font count for XF formats (not for DXF formats). - num_format_count += 1 unless format.xf_index == 0 + num_format_count += 1 if ptrue?(format.xf_index) end end @num_format_count = num_format_count end @@ -1082,17 +1317,13 @@ # Iterate through the XF Format objects and give them an index to non-default # border elements. # def prepare_borders #:nodoc: borders = {} - @xf_formats.each do |format| - key = format.get_border_key - format.has_border(!borders[key]) - format.border_index = borders[key] || borders.size - borders[key] ||= borders.size - end + @xf_formats.each { |format| format.set_border_info(borders) } + @border_count = borders.size # For the DXF formats we only need to check if the properties have changed. @dxf_formats.each do |format| key = format.get_border_key @@ -1113,26 +1344,35 @@ # 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 + end + @xf_formats.each do |format| # The following logical statements jointly take care of special cases # in relation to cell colours and patterns: # 1. For a solid fill (_pattern == 1) Excel reverses the role of # foreground and background colours, and # 2. If the user specifies a foreground or background colour without # a pattern they probably wanted a solid fill, so we fill in the # defaults. # - if format.pattern <= 1 && format.bg_color != 0 && format.fg_color == 0 + if format.pattern == 1 && ne_0?(format.bg_color) && ne_0?(format.fg_color) + format.fg_color, format.bg_color = format.bg_color, format.fg_color + elsif format.pattern <= 1 && ne_0?(format.bg_color) && eq_0?(format.fg_color) format.fg_color = format.bg_color format.bg_color = 0 format.pattern = 1 - end - - if format.pattern <= 1 && format.bg_color == 0 && format.fg_color != 0 + elsif format.pattern <= 1 && eq_0?(format.bg_color) && ne_0?(format.fg_color) format.bg_color = 0 format.pattern = 1 end key = format.get_fill_key @@ -1149,17 +1389,20 @@ index += 1 end end @fill_count = index + end - # For the DXF formats we only need to check if the properties have changed. - @dxf_formats.each do |format| - format.has_dxf_fill(true) if format.pattern || format.bg_color || format.fg_color - end + def eq_0?(val) + ptrue?(val) ? false : true end + def ne_0?(val) + !eq_0?(val) + end + # # 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. # @@ -1214,10 +1457,11 @@ @worksheets.each do |sheet| next unless sheet.has_comments? comment_id += 1 + sheet.set_external_vml_links(comment_id) sheet.set_external_comment_links(comment_id) sheet.set_vml_data_id(vml_data_id) sheet.vml_shape_id = vml_shape_id # Each VML file should start with a shape id incremented by 1024. @@ -1257,57 +1501,59 @@ @charts.each do |chart| chart.formula_ids.each do |range, id| # Skip if the series has user defined data. if chart.formula_data[id] - if !seen_ranges.has_key?(range) || seen_ranges[range] - data = chart.formula_data[id] - seen_ranges[range] = data - end + seen_ranges[range] = chart.formula_data[id] unless seen_ranges[range] next - end - # Check to see if the data is already cached locally. - if seen_ranges.has_key?(range) + elsif seen_ranges[range] chart.formula_data[id] = seen_ranges[range] next end # Convert the range formula to a sheet name and cell range. sheetname, *cells = get_chart_range(range) # Skip if we couldn't parse the formula. next unless sheetname - # Skip if the name is unknown. Probably should throw exception. - next unless worksheets[sheetname] + # 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 - # Find the worksheet object based on the sheet name. - worksheet = 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) + end + end + end - # Get the data from the worksheet table. - data = worksheet.get_range_data(*cells) + def chart_data(worksheet, cells) + # 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) - token = @shared_strings.string(token[:sst_id]) + # Convert shared string indexes to strings. + data.collect do |token| + if token.kind_of?(Hash) + string = @shared_strings.string(token[:sst_id]) - # Ignore rich strings for now. Deparse later if necessary. - token = '' if token =~ %r!^<r>! && token =~ %r!</r>$! - end - token + # Ignore rich strings for now. Deparse later if necessary. + if string =~ %r!^<r>! && string =~ %r!</r>$! + '' + else + string end - - # Add the data to the chart. - chart.formula_data[id] = data - - # Store range data locally to avoid lookup if seen again. - seen_ranges[range] = data + else + token end end end + private :chart_data # # 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 @@ -1365,13 +1611,13 @@ sheet_name = $1 # Match Print_Area and Print_Titles xlnm types. if name =~ /^_xlnm\.(.*)$/ xlnm_type = $1 - name = sheet_name + '!' + xlnm_type + name = "#{sheet_name}!#{xlnm_type}" elsif index != -1 - name = sheet_name + '!' + name + name = "#{sheet_name}!#{name}" end named_ranges << name end end @@ -1387,12 +1633,15 @@ image_ref_id = 0 drawing_id = 0 @worksheets.each do |sheet| chart_count = sheet.charts.size image_count = sheet.images.size - next if chart_count + image_count == 0 + shape_count = sheet.shapes.size + next if chart_count + image_count + shape_count == 0 + sheet.sort_charts + drawing_id += 1 (0 .. chart_count - 1).each do |index| chart_ref_id += 1 sheet.prepare_chart(index, chart_ref_id, drawing_id) @@ -1406,10 +1655,14 @@ image_ref_id += 1 sheet.prepare_image(index, image_ref_id, drawing_id, width, height, name, type) end + (0..shape_count - 1).each do |index| + sheet.prepare_shape(index, drawing_id) + end + drawing = sheet.drawing @drawings << drawing end @drawing_count = drawing_id @@ -1428,8 +1681,113 @@ ( 0 .. sheet_count - 1 ).each do |i| sheet_index = i if sheetname == @sheetnames[i] end sheet_index + end + + # + # Extract information from the image file such as dimension, type, filename, + # and extension. Also keep track of previously seen images to optimise out + # any duplicates. + # + def get_image_properties(filename) + previous_images = [] + image_id = 1; + if @images_seen[filename] + # We've processed this file already. + index = images_seen[filename] - 1 + + # Increase image reference count. + image_data[index][0] += 1 + else + # Open the image file and import the data. + data = File.binread(filename) + if data.unpack('x A3')[0] == 'PNG' + # Test for PNGs. + type, width, height = process_png(data) + image_types[:png] = 1 + elsif data.unpack('n')[0] == 0xFFD8 && + (data.unpack('x6 A4')[0] == 'JFIF' || data.unpack('x6 A4')[0] == 'Exif') + # Test for JFIF and Exif JPEGs. + type, width, height = process_jpg(data, filename) + @image_types[:jpeg] = 1 + elsif data.unpack('A2')[0] == 'BM' + # Test for BMPs. + type, width, height = process_bmp(data, filename) + @image_types[:bmp] = 1 + else + # TODO. Add Image::Size to support other types. + raise "Unsupported image format for file: #{filename}\n" + end + + @images << [ filename, type] + + # Also store new data for use in duplicate images. + previous_images << [image_id, type, width, height] + @images_seen[filename] = image_id + image_id += 1 + end + + [image_id, type, width, height, File.basename(filename)] + end + + # + # Extract width and height information from a PNG file. + # + def process_png(data) + type = 'png' + width = data[16, 4].unpack("N")[0] + height = data[20, 4].unpack("N")[0] + + [type, width, height] + end + + def process_jpg(data, filename) + type = 'jpeg' + offset = 2 + data_length = data.bytesize + + # Search through the image data to find the 0xFFC0 marker. The height and + # width are contained in the data for that sub element. + while offset < data_length + marker = data[offset, 2].unpack("n")[0] + length = data[offset+2, 2].unpack("n")[0] + + if marker == 0xFFC0 || marker == 0xFFC2 + height = data[offset+5, 2].unpack("n")[0] + width = data[offset+7, 2].unpack("n")[0] + break + end + + offset += length + 2 + break if marker == 0xFFDA + end + + raise "#{filename}: no size data found in jpeg image.\n" unless height + [type, width, height] + end + + # Extract width and height information from a BMP file. + 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 + + # Read the bitmap width and height. Verify the sizes. + width, height = data.unpack("x18 V2") + raise "#{filename}: largest image width #{width} supported is 65k." if width > 0xFFFF + raise "#{filename}: largest image height supported is 65k." if height > 0xFFFF + + # Read the bitmap planes and bpp data. Verify them. + 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] + raise "#{filename}: compression not supported in bitmap image." unless compression == 0 + [type, width, height] end end end