When a bar chart has multiple categories, can't set color for series as a whole.

[idoa01]

see code example:

wb.add_worksheet(name: "Category Test") do |sheet|
  sheet.add_row ['',  'First', 'Second']
  sheet.add_row ['A', '10',    '20']
  sheet.add_row ['B', '20',    '10']
  sheet.add_row ['C', '30',    '40']
  sheet.add_row ['D', '40',    '30']
  sheet.add_chart(Axlsx::Bar3DChart, :start_at => "E2", :end_at => "K15", :title => "Test") do |chart|
    chart.add_series :data => sheet["B2:B5"], :labels => sheet["A2:A5"], :title => sheet["B1"], :colors => ['88F700'], :color => "000000"
    chart.add_series :data => sheet["C2:C5"], :labels => sheet["A2:A5"], :title => sheet["C1"], :colors => ['279CAC'], :color => "000000"
    chart.valAxis.label_rotation = -45
    chart.catAxis.label_rotation = 45
  end   
end

The above code produces this chart (on LibreOffice), which is missing most of the bars:

if we change the add_series to multiply the colors:

    chart.add_series :data => sheet["B2:B5"], :labels => sheet["A2:A5"], :title => sheet["B1"], :colors => ['88F700']*4, :color => "000000"
    chart.add_series :data => sheet["C2:C5"], :labels => sheet["A2:A5"], :title => sheet["C1"], :colors => ['279CAC']*4, :color => "000000"

we get the following, which is better, but still missing the legend colors

proposed fix:

fix the code at Axlsx::BarSeries (https://github.com/randym/axlsx/blob/f16b22913ab21f6187598216510bc68c0f662cc1/lib/axlsx/drawing/bar_series.rb#L54-L70) to be:

    def to_xml_string(str = '')
      super(str) do |str_inner|

        if colors.length == 1
          str_inner << '<c:spPr><a:solidFill>'
          str_inner << '<a:srgbClr val="' << colors[0] << '"/>'
          str_inner << '</a:solidFill></c:spPr>'
        else
          colors.each_with_index do |c, index|
            str_inner << '<c:dPt>'
            str_inner << '<c:idx val="' << index.to_s << '"/>'
            str_inner << '<c:spPr><a:solidFill>'
            str_inner << '<a:srgbClr val="' << c << '"/>'
            str_inner << '</a:solidFill></c:spPr></c:dPt>'
          end 
        end

        @labels.to_xml_string(str_inner) unless @labels.nil?
        @data.to_xml_string(str_inner) unless @data.nil?
        # this is actually only required for shapes other than box 
        str_inner << '<c:shape val="' << shape.to_s << '"></c:shape>'
      end
    end

which will produce the following nice chart:

(haven't checked this fix on other charts/excel)

[randym]

@idoa01

If you are generating charts for libra office you need to explicitly specify the colors you want to use. Supporting LibreOffice is actually how colors ended up being part of the code. Excel (as per usual) just does 'whatever' and magically gives you colors....

still, I really like the direction you are taking here. Why should I, the developer, have to repeat the same color over and over just to get my bar chart funkified? (legend issues still need to be sorted though)

As an enhancement to allow you to only set one color definition for all series, or for that matter any number of colors where the color count is less than the data series size, I think we should repeat the original colors elements until we have as many colors as we have data series.

Just out of curiosity, do the specs, specifically schema validation pass with this change? I would wonder if spPr is required to be a child node of dPt.

[idoa01]

sorry, didn't run the specs. IIRC, I found that you can extract spPr from dPt by looking at the saved output of LibreOffice.

you can also see it in the ISO (ISO/IEC 29500-1:2012), relevant parts extracted here (under EG_SerShared and CT_DPt)

tl;dr you can specify a spPr tag under the bar-series (ser) tag, and you don't have to specify a spPr tag under the dPt tag.

<xsd:group name="EG_BarChartShared">
    <xsd:sequence>
        <xsd:element name="barDir" type="CT_BarDir" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="grouping" type="CT_BarGrouping" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="varyColors" type="CT_Boolean" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ser" type="CT_BarSer" minOccurs="0" maxOccurs="unbounded"/>
        <xsd:element name="dLbls" type="CT_DLbls" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
</xsd:group>
<xsd:complexType name="CT_BarChart">
    <xsd:sequence>
        <xsd:group ref="EG_BarChartShared" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="gapWidth" type="CT_GapAmount" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="overlap" type="CT_Overlap" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="serLines" type="CT_ChartLines" minOccurs="0" maxOccurs="unbounded"/>
        <xsd:element name="axId" type="CT_UnsignedInt" minOccurs="2" maxOccurs="2"/>
        <xsd:element name="extLst" type="CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="CT_Bar3DChart">
    <xsd:sequence>
        <xsd:group ref="EG_BarChartShared" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="gapWidth" type="CT_GapAmount" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="gapDepth" type="CT_GapAmount" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="shape" type="CT_Shape" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="axId" type="CT_UnsignedInt" minOccurs="2" maxOccurs="3"/>
        <xsd:element name="extLst" type="CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
</xsd:complexType>
<xsd:group name="EG_SerShared">
    <xsd:sequence>
        <xsd:element name="idx" type="CT_UnsignedInt" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="order" type="CT_UnsignedInt" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="tx" type="CT_SerTx" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="spPr" type="a:CT_ShapeProperties" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
</xsd:group>
<xsd:complexType name="CT_BarSer">
    <xsd:sequence>
        <xsd:group ref="EG_SerShared" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="invertIfNegative" type="CT_Boolean" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="pictureOptions" type="CT_PictureOptions" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="dPt" type="CT_DPt" minOccurs="0" maxOccurs="unbounded"/>
        <xsd:element name="dLbls" type="CT_DLbls" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="trendline" type="CT_Trendline" minOccurs="0" maxOccurs="unbounded"/>
        <xsd:element name="errBars" type="CT_ErrBars" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="cat" type="CT_AxDataSource" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="val" type="CT_NumDataSource" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="shape" type="CT_Shape" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="extLst" type="CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="CT_DPt">
    <xsd:sequence>
        <xsd:element name="idx" type="CT_UnsignedInt" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="invertIfNegative" type="CT_Boolean" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="marker" type="CT_Marker" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="bubble3D" type="CT_Boolean" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="explosion" type="CT_UnsignedInt" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="spPr" type="a:CT_ShapeProperties" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="pictureOptions" type="CT_PictureOptions" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="extLst" type="CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
</xsd:complexType>

[randym]

This look like what you are expecting?

[idoa01]

yes, thanks :+1: although, it is kind of confusing having multiple color schemes and a legend that doesn't show them. is the legend optional in this case?

[randym]

yeah, you can set show_legend to false on the chart. But I'd much rather have the legend colors updated!

[jughead]

@randym So what's about merge? It'd be good to have it fixed.

[vkatz-ninthdecimal]

+1 for this fix!

[TomK32]

I ran into the same issue as @idoa01 did and what worked for me was a slight adaptation to his:

module Axlsx
  class BarSeries < Series
    def to_xml_string(str = '')
      super(str) do

        str << '<c:spPr><a:solidFill>'
        str << '<a:srgbClr val="' << colors[0] << '"/>'
        str << '</a:solidFill></c:spPr>'

        colors.each_with_index do |c, index|
          str << '<c:dPt>'
          str << '<c:idx val="' << index.to_s << '"/>'
          str << '<c:spPr><a:solidFill>'
          str << '<a:srgbClr val="' << c << '"/>'
          str << '</a:solidFill></c:spPr></c:dPt>'
        end

        @labels.to_xml_string(str) unless @labels.nil?
        @data.to_xml_string(str) unless @data.nil?
        # this is actually only required for shapes other than box 
        str << '<c:shape val="' << shape.to_s << '"></c:shape>'
      end
    end
  end
end