Histograms

xlsgen > overview > Histograms

Creating histograms

Histograms are special charts with statistical meaning : they show on the Y axis the frequency at which an event occurs. For instance, one can use histograms to count how many times a given word occurs in a given paragraph. By displaying the result in a meaningful way, the histogram gives a clear and concise idea of the distribution of things.

Histograms, at least a reduced version of it, are available in Excel with the Analysis toolpak add-in. But they are extremely cumbersome to use : they do not work with non-numeric data ; they create temporary worksheets to store intermediate data whose meaning is not necessarily clear ; the chart does not get updated if the data changes ; etc. And it's not nice!

xlsgen fixes all of this. Histograms are simply added to the chart type enumeration, which means all the underlying computations are hidden to the application. And the layout of the resulting histogram, by default, is designed to be easy to read and understand. The screenshot above was generated with xlsgen.

Use case. We have the following data :

Our data

We have a series of non-numeric data, and we would like to know 1) how many times each word occurs 2) how are the occurences distributed. Here is the corresponding source code :

Java code

XlsChart chart001s0 = wksht001.NewChart(xlsgen.charttype_histogram, 
                                                                  3, //row1
                                                                  4, //col1 
                                                                  17, //row2
                                                                  11 //col2
                                                                  );

XlsChartDynamicDataSource datasource001s0 = chart001s0.getDynamicDataSource();

XlsChartDynamicDataSourceSeries serie001s0ss0 = datasource001s0.AddSerie();
serie001s0ss0.putSeriesTitlesFormula("Sheet1!$B$1");
serie001s0ss0.putDataLabelsFormula("Sheet1!$A$2:$A$26");
serie001s0ss0.putSeriesValuesFormula("Sheet1!$B$2:$B$26");

chart001s0.getYAxis(xlsgen.chartaxis_primary).putSorting(xlsgen.chartaxissorting_gaussian);

VB code

Dim chart001s0 As xlsgen.IXlsChart
Set chart001s0 = wksht001.NewChart(xlsgen.enumChartType.charttype_histogram, _
						3, _
						4, _
						17, _
						11)


Dim datasource001s0 As xlsgen.IXlsChartDynamicDataSource
Set datasource001s0 = chart001s0.DynamicDataSource

Dim serie001s0ss0 As xlsgen.IXlsChartDynamicDataSourceSeries
Set serie001s0ss0 = datasource001s0.AddSerie
serie001s0ss0.SeriesTitlesFormula = "Sheet1!$B$1"
serie001s0ss0.DataLabelsFormula = "Sheet1!$A$2:$A$26"
serie001s0ss0.SeriesValuesFormula = "Sheet1!$B$2:$B$26"

chart001s0.YAxis(xlsgen.enumChartAxisType.chartaxis_primary).Sorting = xlsgen.enumChartAxisSorting.chartaxissorting_gaussian

C# code

IXlsChart chart001s0 = wksht001.NewChart(enumChartType.charttype_histogram, 
                                                                  3, //row1
                                                                  4, //col1 
                                                                  17, //row2
                                                                  11 //col2
                                                                  );

IXlsChartDynamicDataSource datasource001s0 = chart001s0.DynamicDataSource;

IXlsChartDynamicDataSourceSeries serie001s0ss0 = datasource001s0.AddSerie();
serie001s0ss0.SeriesTitlesFormula = "Sheet1!$B$1";
serie001s0ss0.DataLabelsFormula = "Sheet1!$A$2:$A$26";
serie001s0ss0.SeriesValuesFormula = "Sheet1!$B$2:$B$26";

chart001s0.get_YAxis(enumChartAxisType.chartaxis_primary).Sorting = enumChartAxisSorting.chartaxissorting_gaussian;

C++ code

xlsgen::IXlsChartPtr chart001s0 = wksht001->NewChart(xlsgen::charttype_histogram, 
						3, //row1
						4, //col1 
						17, //row2
						11 //col2
						);

xlsgen::IXlsChartDynamicDataSourcePtr datasource001s0 = chart001s0->DynamicDataSource;

xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss0 = datasource001s0->AddSerie();
serie001s0ss0->SeriesTitlesFormula = L"Sheet1!$B$1";
serie001s0ss0->DataLabelsFormula = L"Sheet1!$A$2:$A$26";
serie001s0ss0->SeriesValuesFormula = L"Sheet1!$B$2:$B$26";

chart001s0->YAxis[xlsgen::chartaxis_primary]->Sorting = xlsgen::chartaxissorting_gaussian;

It is very standard code, it's like creating a regular chart. In fact the two computation runs are hidden to the user. Perhaps the only noticeable addition is the Sorting property where it is explicitely requested to sort the X axis elements according to a gaussian distribution, so it looks like a bell curve. Here are the options available :

typedef enum
{
	chartaxissorting_none       = 0, // 
	chartaxissorting_ascending  = 1, // 1-2-3-...
	chartaxissorting_descending = 2, // 3-2-1-...
	chartaxissorting_gaussian   = 3  // bell curve
} enumChartAxisSorting;

The corresponding histogram

In case we are interesting in doing the same with numeric data, and with a descending sorting, it isn't much harder :