Display empty cells in chart

[Norbert Wołowiec]

Hi, again!

Is in the library option to chenge how chart display empty cells?

In excel are 3 options.
https://support.office.com/en-us/article/display-empty-cells-null-n-a-values-and-hidden-worksheet-data-in-a-chart-a1ee6f0c-192f-4248-abeb-9ca49cb92274?ui=en-US&rs=en-US&ad=US

[Norbert Wołowiec]

I found line in xml wich change this:

<c:dispblanksas val="gap">
<c:dispblanksas val="span">
<c:dispblanksas val="zero"></c:dispblanksas></c:dispblanksas></c:dispblanksas>

Line:
<c:plotvisonly val="1">
is in xml when we dont want display hidden cells.
If we want display hidden values we must delete this line.</c:plotvisonly>

[Norbert Wołowiec]

My suggestion for the next version:

Add new Enum:

/// @brief The enumeration determines empty cell display method
            enum EEmptyCellsDisplayMethod
            {
                EMPTY_CELLS_DISP_GAPS =0,
                EMPTY_CELLS_DISP_ZERO,
                EMPTY_CELLS_DISP_CONNECT
            };

Change struct Diagramm:

struct Diagramm
            {
                UniString name;             ///< name diagramm name (that will be depicted above the chart)
                uint32_t nameSize;          ///< nameSize font size
                EPosition legend_pos;       ///< legend_pos legend position
                ETableData tableData;       ///< tableData table data state
                EChartTypes typeMain;       ///< typeMain main chart type
                EChartTypes typeAdditional; ///< typeAdditional additional chart type (optional)

                EBarDirection barDir;
                EBarGrouping barGroup;
                EScatterStyle scatterStyle;
                EEmptyCellsDisplayMethod emptyCellsDisplayMethod;

                Diagramm()
                {
                    nameSize = 18;
                    legend_pos = POS_RIGHT;
                    tableData = TBL_DATA_NONE;
                    typeMain = CHART_LINEAR;
                    typeAdditional = CHART_NONE;

                    barDir = BAR_DIR_VERTICAL;
                    barGroup = BAR_GROUP_CLUSTERED;
                    scatterStyle = SCATTER_POINT;
                    emptyCellsDisplayMethod = EMPTY_CELLS_DISP_GAPS;
                }
            };

Add new method:

inline void SetEmptyCellsDisplayingMethod(EEmptyCellsDisplayMethod emptyCellsDisplayMethod) { m_diagramm.emptyCellsDisplayMethod = emptyCellsDisplayMethod; }

Add switch in CChart::Save() instead line:xmlw.TagL( "c:dispBlanksAs" ).Attr( "val", "gap" ).EndL();

switch (m_diagramm.emptyCellsDisplayMethod)
        {
        case EMPTY_CELLS_DISP_GAPS:
          xmlw.TagL("c:dispBlanksAs").Attr("val", "gap").EndL();
          break;
        case EMPTY_CELLS_DISP_ZERO:
          xmlw.TagL("c:dispBlanksAs").Attr("val", "zero").EndL();
          break;
        case EMPTY_CELLS_DISP_CONNECT:
          xmlw.TagL("c:dispBlanksAs").Attr("val", "span").EndL();
          break;
        default:
          break;
        }

I hope it will be helpful.
These are small changes and add some functionality.

[Norbert Wołowiec]

Other question.

Why we can't change bar chart fill color?

[Alexandr Belyak]

Hi,
Thank you for useful improvement! And I am sorry for the long silence, I did not have any free time.
I added your improvements to the project. Also I added CChart::SetShowDataFromHiddenCells method for display data from hidden rows and columns.

To change bar chart fill color, see in CChart::Series new specific for bar chart fields: barFillStyle (need BAR_FILL_SOLID), barInvertIfNegative and barInvertedColor.

I will be glad to hear your remarks and suggestions.

[Norbert Wołowiec]

I am glad that I can help and You do not need to apologize.

[Norbert Wołowiec]

I have other very small change

Now, if i want to add custom marker, I have to specify the color of the fill and the line. When I specify only one, nothing will happen

I change this:

xmlw.Tag( "c:marker" ).TagL( "c:symbol" ).Attr( "val", markerID ).EndL().TagL( "c:size" ).Attr( "val", it->Marker.Size ).EndL();
            if( it->Marker.FillColor.size() == 6 && it->Marker.LineColor.size() == 6 ) // check formal RGB record format
            {
                xmlw.Tag( "c:spPr" );
                xmlw.Tag( "a:solidFill" ).TagL( "a:srgbClr" ).Attr( "val", it->Marker.FillColor ).EndL().End( "a:solidFill" ); // marker fill
                xmlw.Tag( "a:ln" ).Attr( "w", floor( it->Marker.LineWidth * 12700 ) ).Tag( "a:solidFill" ).TagL( "a:srgbClr" ).Attr( "val", it->Marker.LineColor ).EndL().End( "a:solidFill" ).End( "a:ln" ); // marker line
                xmlw.End( "c:spPr" );
            };
            xmlw.End( "c:marker" );

For that:

xmlw.Tag( "c:marker" ).TagL( "c:symbol" ).Attr( "val", markerID ).EndL().TagL( "c:size" ).Attr( "val", it->Marker.Size ).EndL();
            if (it->Marker.FillColor.size() == 6 || it->Marker.LineColor.size() == 6) // check formal RGB record format
            {
              xmlw.Tag("c:spPr");
              if (it->Marker.FillColor.size() == 6)
              {
                xmlw.Tag("a:solidFill").TagL("a:srgbClr").Attr("val", it->Marker.FillColor).EndL().End("a:solidFill");
              }// marker fill

              if (it->Marker.LineColor.size() == 6)
              {
                xmlw.Tag("a:ln").Attr("w", floor(it->Marker.LineWidth * 12700)).Tag("a:solidFill").TagL("a:srgbClr").Attr("val", it->Marker.LineColor).EndL().End("a:solidFill").End("a:ln");
              }// marker line
              xmlw.End("c:spPr");
            };
            xmlw.End( "c:marker" );

Now we have more flexibility.

[Alexandr Belyak]

Thanks for the improvement, I will add it to the project.

[Norbert Wołowiec]

I have another problem.

When I have a linear chart and add an additional bar chart, the second y axis is generated.
What if I want both to use one y axis? It is possible in Excel.

[Alexandr Belyak]

It seems I did not fully understand the problem.
Can you attach a xlsx-file with the chart that you want to generate?

[Norbert Wołowiec]

Ok

[Alexandr Belyak]

Thanks for your files, they helped me.
Try new method CChart::SetDrawAdditionalAxes.

[Alexandr Belyak]

The new method was superfluous. Please see the message at this address:
https://sourceforge.net/p/simplexlsx/discussion/general/thread/9e13fc27/#249b