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 xlsgenWhile 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";