Build 3.5.0.98 of xlsgen improves the speed of reading of XLSX files (and their variants, XLSM, XLST, ...).

What we have done in this build is pre-tokenize XML nodes so that the parsing phase is faster for loading the parts of the file which can be arbitrarily big.

This is an ongoing work. We won't be done until reading XLSX files is as fast as reading XLS files (even though there is more work due to the unencoded nature of data and formulas in XLSX files).

Build 3.5.0.97 of xlsgen fixes the rendering of custom data labels in charts.

Whenever series would have a single item of data, custom data labels of type value would ignore the "linked to source" number format, wherever applicable. This is now fixed.

Build 3.5.0.96 of xlsgen fixes the rendering of charts of type Area.

What is changed is how the bottom portion of the area surface gets rendered. Before this build, the plot area bottom would be the area bottom. Now this x axis (category axis) is the bottom, which means the area surface correctly crosses (if there is any negative data) or rests on the x axis instead of the bottom of the plot area.

This is mostly anecdotal change but it mimcs Excel.

Build 3.5.0.95 of xlsgen adds support for the wellknown UsedRange helper.

Traditional VBA programmers often rely on UsedRange in order to find the extent of cells in a given sheet, and use the range object capabilities to act on it, for instance deleting formulas in that range.

This is exactly what xlsgen supports. For instance,

worksheet.UsedRange.DeleteFormulas()

is a very handy mechanism for deleting formulas in the used range of the worksheet without having to build the cell range yourself.

The range object supports no less than 42 methods which means this is an effective mechanism. Just wish this was part of xlsgen long time ago already.

Build 3.5.0.94 of xlsgen improves the memory performance for XLSX files. Specifically, the writing phase is made much more scalable in memory by zipping the important parts (worksheets and shared strings) as chunks arrive rather than waiting for the whole parts to be ready for zipping.

The consequence is scalability in memory for very large XLSX files.

Build 3.5.0.93 of xlsgen adds better support for Excel files generated on Mac computers.

There is an internal encoding in cells that wasn't supported and as a result could cause data loss.

In previous builds were introduced 40 Office themes and a new default theme, but there has also been work around the scenarios in which they apply :

- generation of new XLS files : unaffected since there is no such thing as a theme in the XLS file format

- generation of new XLSX files : theme settings stored, rendered and applied

- reading of XLS files : xlsgen chart renderer enforces the classic theme (i.e. Excel 2003 theme) unless told otherwise

- reading of XLSX files : xlsgen chart renderer enforces the stored theme unless told otherwise

- reading of XLSB files : xlsgen extracts the theme, renders and stores it unless told otherwise

- custom theme : round-tripping support, rendering support

Build 3.5.0.91 of xlsgen changes the default theme. It affects both new XLSX files (and their variants) as well as the rendering of charts by xlsgen.

The default theme is now Elemental, which is one of the 40 Office themes available in xlsgen.

To be more accurate, it uses Elemental for fonts and effects, but it uses the Adjacency theme colors. Indeed a theme is made of 3 independent sections : colors, fonts and effects, and a client application may choose a different theme for each section, among the 40 themes. The Elemental colors were shades of blue, and we believe it would be more appropriate to use a more diverse set of colors. That is why we choose Adjacency colors.

It is equivalent to writing the following in code, except you don't have to :

workbook.ThemeManager.Theme = workbooktheme_elemental;
workbook.ThemeManager.CustomColors = workbooktheme_adjacency;

It can be noted that this is just being the default and at any point in time in your client application, the theme can be modified by either choosing an entire theme (just one line of code), or a different theme for just colors, fonts or effects. For this there is this ThemeManager property available at the workbook level.

Most notably, if you would like to keep using the theme prior this change, all you have to do is add this line of code in your program :

workbook.ThemeManager.Theme = workbooktheme_modern;

The chart style itself for any new chart defaults on moderate effect with auto colors, which provides great looking shadow and soft edge effects. You can also go back to what it was before :

chart.Theme.EffectSpan = chartthemeeffectspan_none;
chart.Theme.ColorSpan = chartthemecolorspan_auto;


Below is a few charts rendered with the new default theme :

Build 3.5.0.90 of xlsgen adds 40 Office themes to the object model for rendering charts.

So far xlsgen had 4 themes (classic, modern, olive and marine). This build introduces all 20 Office themes that are part of Excel 2007, in addition to all 20 Office themes that are part of Excel 2010.

In Excel, themes can be changed by clicking on the Page layout ribbon then on the Theme drop down. Picking one immediately affects all charts and tables.

In xlsgen, theme can be set by using the ThemeManager object at the workbook level.

The 40 Office themes from Excel 2007 and Excel 2010 are :
[helpstring("Workbook Theme, office (Excel 2007)")] workbooktheme_office = 4,
[helpstring("Workbook Theme, adjacency")] workbooktheme_adjacency = 5,
[helpstring("Workbook Theme, angles")] workbooktheme_angles = 6,
[helpstring("Workbook Theme, apex")] workbooktheme_apex = 7,
[helpstring("Workbook Theme, apothecary")] workbooktheme_apothecary = 8,
[helpstring("Workbook Theme, aspect")] workbooktheme_aspect = 9,
[helpstring("Workbook Theme, austin")] workbooktheme_austin = 10,
[helpstring("Workbook Theme, black tie")] workbooktheme_blacktie = 11,
[helpstring("Workbook Theme, civic")] workbooktheme_civic = 12,
[helpstring("Workbook Theme, clarity")] workbooktheme_clarity = 13,
[helpstring("Workbook Theme, composite")] workbooktheme_composite = 14,
[helpstring("Workbook Theme, concourse")] workbooktheme_concourse = 15,
[helpstring("Workbook Theme, couture")] workbooktheme_coutoure = 16,
[helpstring("Workbook Theme, elemental")] workbooktheme_elemental = 17,
[helpstring("Workbook Theme, equity")] workbooktheme_equity = 18,
[helpstring("Workbook Theme, essential")] workbooktheme_essential = 19,
[helpstring("Workbook Theme, executive")] workbooktheme_executive = 20,
[helpstring("Workbook Theme, flow")] workbooktheme_flow = 21,
[helpstring("Workbook Theme, foundry")] workbooktheme_foundry = 22,
[helpstring("Workbook Theme, grid")] workbooktheme_grid = 23,
[helpstring("Workbook Theme, hardcover")] workbooktheme_hardcover = 24,
[helpstring("Workbook Theme, horizon")] workbooktheme_horizon = 25,
[helpstring("Workbook Theme, median")] workbooktheme_median = 26,
[helpstring("Workbook Theme, metro")] workbooktheme_metro = 27,
[helpstring("Workbook Theme, module")] workbooktheme_module = 28,
[helpstring("Workbook Theme, newsprint")] workbooktheme_newsprint = 29,
[helpstring("Workbook Theme, opulent")] workbooktheme_opulent = 30,
[helpstring("Workbook Theme, oriel")] workbooktheme_oriel = 31,
[helpstring("Workbook Theme, origin")] workbooktheme_origin = 32,
[helpstring("Workbook Theme, paper")] workbooktheme_paper = 33,
[helpstring("Workbook Theme, perspective")] workbooktheme_perspective = 34,
[helpstring("Workbook Theme, pushpin")] workbooktheme_pushpin = 35,
[helpstring("Workbook Theme, slipstream")] workbooktheme_slipstream = 36,
[helpstring("Workbook Theme, solstice")] workbooktheme_solstice = 37,
[helpstring("Workbook Theme, technic")] workbooktheme_technic = 38,
[helpstring("Workbook Theme, thatch")] workbooktheme_thatch = 39,
[helpstring("Workbook Theme, trek")] workbooktheme_trek = 40,
[helpstring("Workbook Theme, urban")] workbooktheme_urban = 41,
[helpstring("Workbook Theme, verve")] workbooktheme_verve = 42,
[helpstring("Workbook Theme, waveform")] workbooktheme_waveform = 43

As a consequence of a number of those themes bringing along with them picture parts used for textures, xlsgen.dll is now 400 KB bigger in size. Both the 32-bit and the 64-bit versions.

Build 3.5.0.89 of xlsgen refines a feature meant to create great looking charts. This is the first build and it will be followed by more of it.

In build 3.5.0.61, xlsgen added support for 48 chart styles for use when creating/updating XLSX files (and their variants). These are wonderful chart styles one can pick among 48 for any chart type, but as it turns out, it is harder than usual to pick which one would be good to use because this is just a list of unspecified 48 chart styles.

At least, according to the way Excel stores this information inside files. xlsgen tries to make things more palatable for users out there. This is why we are refining this by sorting out the 48 chart styles according to what they can be used for.

Here are the 48 chart styles,



There are 6 rows of 8 styles, thus 48 styles.

The 48 styles are spread around two axis, vertically the effect span, horizontally the color span. Each row thus has 8 color variants : the grayscale, the automatic colors, and then 6 primary theme color selections. There are 6 rows accounting for 4 gradual effect spans : no effect, subtle effect, moderate effect, intense effect, and then two more rows for the classic Excel 2003 mode and the intense dark mode (dark background which may be white depending on your system background and foreground colors).

How it works in xlsgen is quite simple. Here is a piece of code in C++ for a creating a pie chart with, as in the example above, the moderate effect, and auto colors :

chart001s0->Theme->EffectSpan = xlsgen::chartthemeeffectspan_moderate;
chart001s0->Theme->ColorSpan = xlsgen::chartthemecolorspan_auto;


Here are two examples of such chart styles :


A pie chart made with moderate effect and auto colors



A bar chart made with moderate effect and auto colors

Charts look professional.

Build 3.5.0.88 of xlsgen adds read, write and calc support for reduced same-sheet references.

Reduced same-sheet references are formulas of the form =!$C$5, that is a cell or area reference without a sheet name before the exclamation point. Excel allows the use of such formulas in defined names.

What they allow is to create indirections with less constraint than fully qualified cell or area references.

xlsgen brings full support for it.

Build 3.5.0.87 adds more features from XLSB files. The features provided in this build are in bold below.

Imported features from XLSB files are :

• worksheets and their properties (name, hidden or not, ...)
  
• row formatting, column formatting
  
• values in cells (numbers, strings, dates, booleans, errors)
  
• formatting in cells (font, number format, alignment, border, fill, lock)
  
• rich-formatted strings
  
• formulas
  
• merged cells
  
• defined names
  
• page setup and header/footer
  
• conditional formattings
  
• VBA macros
  
• charts
  
• text boxes, pictures and vector shapes
  
• hyperlinks
  
• comments
  
• document metadata
  
• data validation objects
  
• sheet selection, protection, sheet level properties
  
• page breaks
  
• tables
  
• freeze/split panes
  
• outline
  
• auto-filters
  
• custom filter expressions
  
• advanced custom filters (color filters, icon filters, date filters, top filters, ...)
  

Build 3.5.0.86 adds support for more features from XLSB files and is a continuation from what was introduced in build 3.5.0.84. The features are in bold below.

Imported features from XLSB files are :

• worksheets and their properties (name, hidden or not, ...)
  
• row formatting, column formatting
  
• values in cells (numbers, strings, dates, booleans, errors)
  
• formatting in cells (font, number format, alignment, border, fill, lock)
  
• rich-formatted strings
  
• formulas
  
• merged cells
  
• defined names
  
• page setup and header/footer
  
• conditional formattings
  
• VBA macros
  
• charts
  
• text boxes, pictures and vector shapes
  
• hyperlinks
  
• comments
  
• document metadata
  
• data validation objects
  
• sheet selection, protection, sheet level properties
  
• page breaks
  
• tables
  
• freeze/split panes
  

Last but not least, the automatic source code generator that is part of the xlsgen install can also open XLSB files.

Build 3.5.0.85 of xlsgen makes it possible to define alternate filename associations for reading or writing files using xlsgen.

It works like this. If you have a file ending with .aaa and you know this file is an Excel 2007 file, you can use the following in order to make sure that xlsgen will open it correctly :

engine.Workbooks.AddFilenameAssociation("aaa", xlsgen::excelversion_2007);

and then,

workbook = engine.Open("inputfile.aaa", "outputfile.xlsx");


This works with New(), Open() and other methods where an Excel filepath is passed.

Build 3.5.0.84 adds support for importing Excel XLSB files.

XLSB files were introduced along with XLSX/XLSM/XLST/XLSA files a few years ago. XLSB files are binary files and have nothing to do with XLS files.

xlsgen imports many of the features in XLSX files. Among them :

- cells
- formatting
- formulas
- charts
- text boxes and vector shapes
- pictures
- defined names
- conditional formattings
- page setup
- VBA macros


To use this feature, just pass a XLSB file (or XLSB memory buffer) to the Open() method.

Build 3.5.0.83 of xlsgen continues the work begun with build 3.5.0.82 in which we added support for automatically decrypting XLSX files (and their variants) without a password. This was useful for cases where Excel would encrypt a file depending on what you are doing with it.

xlsgen adds support for XLS files (and their variants) in such scenario.

So when you are to open such file, you do not have to pass a password.

Build 3.5.0.82 of xlsgen makes it possible that some auto-encrypted XLSX files can be auto-opened with xlsgen without the hassles of passing a password.

Auto-encryption occurs in Excel depending on the version of Excel being used, and obviously the action triggering this encryption.

The action in question is the "Protect workbook" feature that you can find in the Review ribbon tab. When this button is clicked, Excel will provide the ability to check/uncheck "Structures" and "Windows", thereby giving you the ability to ask Excel to lock the ability to add/move/delete sheet tabs and the ability to move windows hosting the sheet tabs, respectively. Usually this is enforced by passing a password.

As it turns out, if you are using Excel 2003 (or past versions) or Excel 2007, the file will be encrypted after you used this "Protect workbook" feature. It won't if you are using Excel 2010 or Excel 2013 however, but it does not matter to you.

And, as it turns out too, this encryption uses a hardcoded password, not the password mentioned above. This hardcoded password can be found over on the internet by just using the correct keywords in Google.

Without knowledge of this password, xlsgen could not in the past open such files. Beginning with this build xlsgen automatically tries to open a file (or an input buffer in memory) as an encrypted file with said password. If the file isn't encrypted, xlsgen knows it soon in the code execution so there is no performance loss compared to what we did before. The benefits is that xlsgen can now automatically open those files.

This behavior does not prevent the regular password file encryption/decryption from working correctly.

Build 3.5.0.81 of xlsgen makes it possible to use more complex passwords when securing spreadsheets.

As it turns out, with XLSX files and their variants Excel 2007 upgraded the password length limit from 15 characters to 255. Beginning with this build, xlsgen reflects that.

So for any XLS, XLA, XLT, XLM file, the password length limit is still 15 characters for encrypting or decrypting a spreadsheet.

And for any XLSX, XLSA, XLST, XLSM file, the password length limit is 255 characters.

Build 3.5.0.80 of xlsgen improves the speed at which merged cells in bulk can be processed.

There are two improvements. During the insertion in sheets, the hit test is made much faster (hit test must occur to avoid creating overlapping merged cells otherwise Excel thinks the file is corrupt). And during the writing phase in XLSX files, merged cells are written much faster. Think an order of magnitude in both.

Build 3.5.0.79 of xlsgen includes the interop .NET assembly (Interop.xlsgen.dll) strong-named from now on. You can find it in the samples folder.

What is strong-named for? What this allows is install this .NET assembly in the global assembly cache should the need arise. For instance we have been using it lately for use in Sql Server Integration Services scenarios. Custom SSIS packages make use of custom .NET assemblies provided they can be found arbitrarily on the system, and that's when the global assembly cache comes into play.

Just for reference, if you would like to install Interop.xlsgen.dll in the global assembly cache, all you have to do is bring a command line window and type :

gacutil.exe /i <xlsgen install path>\Interop.xlsgen.dll

The interop assembly is the same whether you are using the 32-bit or the 64-bit version of xlsgen.dll. The reason is that an interop assembly, by definition, is just MSIL, not target code.