Build 4.0.0.11 of xlsgen adds support for creating Excel 2016 histogram charts. As the name implies, these are Excel 2016 charts in that only Excel 2016 will render those charts, no older Excel version will do. xlsgen reads, creates and writes such charts. Note that xlsgen already supports histograms on its own.


Histogram charts in xlsgen

While Excel 2016 introduces native histograms, xlsgen has been supporting histograms for some time already. For the record, histograms were already available in Excel in the past in the Analysis toolpak add-in, unchecked by default, but were cumbersume to use to say the least (special columns had to be created, and so on). Because there are xlsgen histograms, and now Excel 2016 histograms, it was chosen to differentiate them by name. So you would invoke charttype_histogram for xlsgen histograms and charttype_histogram2016 for Excel 2016 histograms.

More details about Excel 2016 histograms. There are two histogram types : those working with numbers and those working with strings. The goal of an histogram is to show the frequency (y-axis) of values in a series. Because values may be close to each other, Excel 2016 creates this concept of bin which is an interval inside of which all values are counted as if they were the same value, thereby allowing a more practical occurence frequency calculation. The object model allows to choose either the bin width (HistogramBinWidth), or the number of bins in the chart (HistogramBinCount). These are options, they do not have to be set for the histogram to work by default. In addition, the extreme values can have their own bin if you set the overflow and underflow values (respectively HistogramOverflowValue and HistogramUnderflowValue in the IXlsChartCustomProperties interface).

Here is how to create histograms (using C#) :

// histogram of numbers

        IXlsWorksheet wksht001 = workbook.AddWorksheet( "Sheet1" );

        wksht001.set_Label(1,1, "Category A");
        wksht001.set_Number(1,2, 20000);

        wksht001.set_Label(2,1, "Category B");
        wksht001.set_Number(2,2, 18000);

        wksht001.set_Label(3,1, "Category A");
        wksht001.set_Number(3,2, 25000);

        wksht001.set_Label(4,1, "Category D");
        wksht001.set_Number(4,2, 4000);

        wksht001.set_Label(5,1, "Category E");
        wksht001.set_Number(5,2, 16000);

        IXlsChart chart1 = wksht001.NewChart(enumChartType.charttype_histogram2016,
                                                     10, //row1
                                                     2, //col1 
                                                     25, //row2
                                                     10 //col2
                                                     );

        IXlsChartDynamicDataSourceSeries s1 = chart1.DynamicDataSource.AddSerie();
        s1.SeriesValuesFormula = "=Sheet1!$B$1:$B$5";
        s1.DataLabelsFormula   = "=Sheet1!$A$1:$A$5";
 
        chart1.CustomProperties.HistogramBinWidth = 5000.0;


// histogram of strings (Category A appears with a frequency of 2)

        IXlsWorksheet wksht002 = workbook.AddWorksheet( "Sheet2" );

        wksht002.set_Label(1,1, "Category A");
        wksht002.set_Number(1,2, 1);

        wksht002.set_Label(2,1, "Category B");
        wksht002.set_Number(2,2, 1);

        wksht002.set_Label(3,1, "Category A");
        wksht002.set_Number(3,2, 1);

        wksht002.set_Label(4,1, "Category D");
        wksht002.set_Number(4,2, 1);

        wksht002.set_Label(5,1, "Category E");
        wksht002.set_Number(5,2, 1);

        IXlsChart chart2 = wksht002.NewChart(enumChartType.charttype_histogram2016,
                                                     10, //row1
                                                     2, //col1 
                                                     25, //row2
                                                     10 //col2
                                                     );

        IXlsChartDynamicDataSourceSeries s2 = chart2.DynamicDataSource.AddSerie();
        s2.SeriesValuesFormula = "=Sheet2!$B$1:$B$5";
        s2.DataLabelsFormula   = "=Sheet2!$A$1:$A$5";