Build 4.0.0.14 of xlsgen improves how password-protected XLSX files are reported as such when a client application tries to open them without the appropriate password.

Before the build, E_FAIL (i.e. 80040005) would be returned. In fact since mid 2015 it has been like this as a result of the handling of auto-encrypted files.

Beginning with this build, HRESULT_ERROR_PASSWORDPROTECTED is returned (i.e. 0x80072001)

Build 4.0.0.13 of xlsgen is a fix pack for the calculation engine.

A number of issues for fixing how to retrieve values in cells :

- better handling of "always calc" flag in cells
- fix for volatile functions
- fix for calculating external references when the corresponding file cannot be opened
- querying for a formula in a cell returns an empty string instead of NULL when there is no formula, avoiding needless exceptions
- better handling of shared formulas internally when a cell value is being updated
- ensure that DeleteFormulas() preserves hidden rows

Build 4.0.0.12 of xlsgen adds support for creating Excel 2016 pareto 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.


Pareto charts in xlsgen

Pareto charts is a type of chart that contains both bars and a line graph, where individual values are represented in descending order by bars, and the cumulative total is represented by the line. The purpose of the Pareto chart is to highlight the most important among a (typically large) set of factors.

Here is how to create a Pareto chart (using Java) :

XlsChart chart = worksheet.NewChart(xlsgen.charttype_pareto,
					     10, //row1
					     2, //col1 
					     25, //row2
					     10 //col2
					     );

XlsChartDynamicDataSourceSeries s1 = chart.getDynamicDataSource().AddSerie();
s1.putSeriesValuesFormula("=Sheet1!$B$1:$B$5");
s1.putDataLabelsFormula("=Sheet1!$A$1:$A$5");

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

Build 4.0.0.10 of xlsgen adds support for creating Excel 2016 sunburst 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.


Sunburst charts in xlsgen

Sunburst chart is another chart type for showing data which is hierarchical in nature. It is made of multiple rings which reflects the hierarchy, and as such is different from a doughnut chart or exploded pie chart, which xlsgen also supports. A segment of the inner circle bears a hierarchical relationship to those segments of the outer circle which lie within the angular sweep of the parent segment. The multiple rings are created from a single series of values associated to multiple columns of categories, reflecting the hierarchies, for instance a quarter/month/week hierarchy as in the example above.

And here is how you do it (in VB) :

Dim chart As IXlsChart 
Set chart = worksheet.NewChart(enumChartType.charttype_sunburst, _
					     10, _ 'row1
					     2,  _ 'col1 
					     25, _ 'row2
					     10  _ 'col2
					     )

Dim s1 As IXlsChartDynamicDataSourceSeries
Set s1 = chart.DynamicDataSource.AddSerie
s1.SeriesValuesFormula = "=Sheet1!$D$2:$D$16"
s1.DataLabelsFormula   = "=Sheet1!$A$2:$C$16"

Build 4.0.0.9 of xlsgen adds support for creating Excel 2016 treemap 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.


Treemap charts in xlsgen

Treemap charts can be used to display values which are hierarchical in nature, using nested rectangles.

In the example above, we have a single series of values, which is the price column, and we would like to show how breakfasts are sorted out by price, and how lunches are sorted out by price. The display builds two separate collections from the same series, based on names from column A and column C, sorts them, according to column D, then displays the nested rectangles.

And here is how to create such chart (c#) :

IXlsChart chart = worksheet.NewChart(enumChartType.charttype_treemap,
					     10, //row1
					     2, //col1 
					     25, //row2
					     10 //col2
					     );

IXlsChartDynamicDataSourceSeries s1 = chart.DynamicDataSource.AddSerie();
s1.SeriesValuesFormula = "=Sheet1!$D$2:$D$13";
s1.DataLabelsFormula   = "=Sheet1!$A$2:$C$13";

Build 4.0.0.8 of xlsgen adds support for creating Excel 2016 box & whisker 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.


Box & whisker charts in xlsgen

Box and whisker charts display the dispersion of values in one or more series. Boxes represent main quartiles, lines represent the dispersion of values outside the main quartiles (the larger the lines, the most varying the values), X signs show the mean and dots represent outliers (i.e. values outside the most meaningful quartiles). Box and whisker charts can be put to great use in statistical and financial charts, notably, in addition or in contrast of histogram charts.

IXlsChart chart = worksheet.NewChart(enumChartType.charttype_boxwhisker,
					     10, //row1
					     2, //col1 
					     25, //row2
					     10 //col2
					     );

IXlsChartDynamicDataSourceSeries s1 = chart.DynamicDataSource.AddSerie();
s1.SeriesValuesFormula = "=Sheet1!$B$2:$B$16";
s1.DataLabelsFormula   = "=Sheet1!$A$2:$A$16";

IXlsChartDynamicDataSourceSeries s2 = chart.DynamicDataSource.AddSerie();
s2.SeriesValuesFormula = "=Sheet1!$C$2:$C$16";
s2.DataLabelsFormula   = "=Sheet1!$A$2:$A$16";

IXlsChartDynamicDataSourceSeries s3 = chart.DynamicDataSource.AddSerie();
s3.SeriesValuesFormula = "=Sheet1!$D$2:$D$16";
s3.DataLabelsFormula   = "=Sheet1!$A$2:$A$16";

chart.MainTitle.Label = "Box and Whisker chart type";

Build 4.0.0.7 of xlsgen adds support for creating Excel 2016 waterfall 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.


Waterfall charts in xlsgen

Waterfall charts can be used for showing differences between values. When the current value is higher than the previous value, a positive color is used (first color in the current theme). And when the current value is lower than the previous value, a negative color is used. By showing those color-coded differences over the entire series, it is possible to visualize how the value is evolving over time. Stock tick charts are often waterfall charts.

Special values can be used and formatted accordingly to rebase the differences, often for showing a total in the values. For this to work, a boolean property called WaterfallRebase in the IXlsChartDataElement interface is available, i.e. the i-th data point of a given series of values.

And they are very simple to create :

IXlsChart chart = worksheet.NewChart(enumChartType.charttype_waterfall,
                                             10, //row1
                                             2, //col1 
                                             25, //row2
                                             10 //col2
                                             );

IXlsChartDynamicDataSourceSeries s1 = chart.DynamicDataSource.AddSerie();
s1.SeriesValuesFormula = "=Sheet1!$A$1:$A$9";

Build 4.0.0.6 of xlsgen adds support for Excel 2016 charts in the automatic source code generator, which is the tool we have for generating the source code from any spreadsheet, in the programming language of your choice.

Excel 2016 introduces 6 new chart types, namely Waterfall, Box and whisker, Treemap, Sunburst, Histogram and Pareto.

Build 4.0.0.5 of xlsgen improves how styles are managed internally, to avoid creating useless styles with operation such as Range.Apply() which is a mechanism used for copying or manipulating style and content in sheets. This is particularly useful if you are creating any substantial spreadsheet because of the limitation of Excel, which are 4000 for a XLS file and 50000 for a XLSX file. Note that when the limit is passed, Excel thinks the file is corrupt next time it opens it. Of course it isn't corrupt, it only has more styles that Excel decides it should have. With this build of xlsgen at least, we are doing our best avoiding the creation of temporary styles that end up being written in files.

Build 4.0.0.4 of xlsgen adds compatibility with Excel 2016. Any file created by xlsgen can be opened with Excel 2016 and vice versa.

In addition to this, build 4.0.0.4 adds support for the 5 new functions of Excel 2016, namely : FORECAST.LINEAR (linear trend algorithm), FORECAST.ETS (exponential trend algorithm), FORECAST.ETS.SEASONALITY, FORECAST.ETS.CONFINT and FORECAST.ETS.STAT

Build 4.0.0.3 of xlsgen makes a change to the default theme in new spreadsheets. It's been almost a year since xlsgen has been defaulting to a much more 3D-like and colorful theme, notably for charts.

Unfortunately, this is perhaps too much. And while this is trivial to use a different theme, it has to be said that the default theme plays a major role, particularly for clients upgrading the xlsgen version they are using.

So instead of internally defaulting to the Elemental theme (which is one of the 40+ available themes) :

workbook.Theme = workbooktheme_elemental;

we know default to the Office theme :

workbook.Theme = workbooktheme_office;


The change can be seen as pictured below :


On the right, the new default theme

Build 4.0.0.2 of xlsgen adds fixes for XLSX charts, particularly chart formatting fidelity in duplicate scenarios.

- make sure to read series overlap from XLSX charts.
- font sizes in XLSX charts should be written if they are custom only in order to take advantage of themes
- better preservation of custom number formats (international).

Build 4.0.0.1 of xlsgen fixes three problems for writing charts in XLS files. Those problems were also present in older xlsgen releases.

Fixes are :
- correct writing of no-markers in charts
- proper writing of line versus line with markers charts
- proper ptg token in formulas used for chart series

xlsgen 4.0 ships with a ton of features :

- pivot tables (XLS and XLSX files)
- import HTML streams
- import JSON streams
- PDF encryption
- Chart anti-aliasing
- Save As XLSB
- Effects (glow, shadow, reflection, ...)
- More beautiful charts (rounded up scale, ...)

More information here.

4 Effects applied to an ellipse in xlsgen (glow, shadow, ...)

xlsgen 4.0 introduces effects to drawings (text boxes, vector shapes, pictures and chart elements).

The effects include :

• glow
• blur
• shadow
• reflection
• soft edges
• material and bevel
• camera

Each effect has its own programming interface. Usually the default values will be enough which means an effect can be applied with a single line of code. And of course effects can be combined.

Here is how to apply blur to a picture :

picture.Effect.Blur.Strength = 10;

xlsgen 4.0 adds support for creating XLSB files.

XLSB (B is for binary) files are a variant of XLSX files, in that it uses binary records internally instead of XML, for the most part.

Support for reading XLSB files was added in xlsgen 3.5. It is the writing that is new in xlsgen 4.0

Of course, this is a comprehensive feature which includes cells, formatting, formulas, charts and so on.

On the right, chart with layout algorithm for rounded scale on axis

xlsgen 4.0 makes rendered charts more beautiful by maximizing the rounding of scales on axis, and quicker to analyze.

Anti-aliasing in xlsgen 4.0

xlsgen 4.0 introduces anti-aliasing for rendering charts. Charts are arguably much nicer.

There are 3 anti-aliasing levels to choose from. xlsgen 4.0 defaults to anti-aliasing level 1. Older xlsgen versions did not have anti-aliasing at all.

workbook.AntiAliasing = xlsgen::antialiasing_level_1;

xlsgen 4.0 makes it possible to encrypt PDF files so that a user has to enter a password in order to open said PDF file.

Very simple, it is actually the same exposure in the object model than the encryption for XLS and XLSX files. In order to do so, the EncryptionPassword property at the workbook level can be used.

Internal PDF buffers are encrypted and the user of an PDF display application has to enter a password in order to view the file.