xlsgen bug fixes

Generate Excel files. Speed. Flexibility. Depth. Robustness.

 

Subscribe our RSS feed

xlsgen 5.0.0.20 : Functions GROUPBY, PIVOTBY, PERCENTOF


Build 5.0.0.20 of xlsgen adds read and write support for the following functions :


  • GROUPBY : allows you to create a summary of your data via a formula. It supports grouping along one axis and aggregating the associated values. For instance, if you had a table of sales data, you might generate a summary of sales by year. Syntax : =GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])
  • PIVOTBY : allows you to group, aggregate, sort, and filter data based on the row and column fields that you specify. Syntax : =PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array])
  • PERCENTOF : returns the percentage that a subset makes up of a given data set. Syntax : =PERCENTOF(data_subset,data_all)


Functions are available from Microsoft in Excel 365. In beta at the time of writing.

Posted on 26-November-2023 14:40 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.19 : Preserving Python in Excel files


Build 5.0.0.19 of xlsgen ensures that Excel files storing Python functions coming from the recent Python in Excel feature introduced by Microsoft are preserved as is.

Most notably, supporting reading and writing the new PY() function.

xlsgen does not run Python script however. If you want to use xlsgen and Python together, you can load xlsgen from a python environment and continue from there. This has been available forever.

Posted on 29-October-2023 11:07 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.18 : PowerShell scripting


Build 5.0.0.18 of xlsgen adds support for running Windows PowerShell scripts.

Windows PowerShell is part of Windows and is more security-aware than other scripting languages such as VBScript and JScript. Since it can create and run COM object instances, and expose the object model to the client application, it is important to make it available to everyone in the context of xlsgen applications. Windows PowerShell scripts are .ps1 files.

And PowerShell is one of the available programming language targets available in the automatic source code generation tool that comes with the xlsgen install so you can get going in a snap.



Build 5.0.0.18 comes with a code sample (/samples/PowerShell) that creates a simple worksheet. A copy of it is below.

Note that the xlsgen object model has constants (enumerated types) that PowerShell does not expose after reading the xlsgen type-library so we are making available, also in code samples, a file called xlsgen.ps1 that exposes all constants available from the object model.

Below is a code sample using PowerShell :


# Produced by xlsgen code generator (c) ARsT design.
# For more info, see http://xlsgen.arstdesign.com
#
# To use this code in your PowerShell script editor, remember to add the
# xlsgen constant-library with the following statement :
#     .samples/PowerShell/xlsgen.ps1
#
# In order to import the .ps1 script, make sure to run PowerShell as admin and
# set the appropriate execution policy as in :
#     Set-ExecutionPolicy RemoteSigned
# which allows to run the .ps1 script within a console
#

$engine = new-object -ComObject ExcelGenerator.ARsTDesign
$workbook = $engine.New( "C:\tmp\myfile.xlsx" )

$wksht001 = $workbook.AddWorksheet( "Sheet1" )

$wksht001.Cell(1,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">154</font>"
$wksht001.Cell(1,3).HtmlLabel = "<font color=#000000 size=11 name=""Calibri"">this is a test</font>"
$wksht001.Cell(3,3).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">1</font>"
$wksht001.Cell(4,3).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">2</font>"

$workbook.Close()


Posted on 13-October-2023 19:00 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.17 : Advanced data sorting (II)


Build 5.0.0.17 of xlsgen complements build 5.0.0.15 related to sorting data. It comes with the following features :

- support for sorting horizontally

- support for merged cells inside the sort area. Merged cells are preserved if they satisfy a number of logical constraints, otherwise the cells are unmerged.

- support for sorting inside auto-filters. A Sort property is now available in the auto-filter object, and saved as such in the file so you can combine sorting and filtering.

Posted on 08-October-2023 18:41 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.16 : Fix for page breaks


Build 5.0.0.16 of xlsgen fixes a problem related to page breaks in XLSX and XLSB files which appears in Excel's user interface.

Excel can't handle print areas made of adjacent areas, as it breaks print previews in Excel, so a special flag is added to disable the problem altogether.

Posted on 29-September-2023 19:27 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.15 : Advanced data sorting


Build 5.0.0.15 of xlsgen adds advanced data sorting. Before this build, sorting on values was already available. This build adds the following :

  • on values
  • on cell color : background cell color or font color
  • on associated icon (related to icon-based conditional formatting)



This is illustrated as follows :



Sort on values (ascending) by column C




Sort on background cell color (descending) by column D




Sort on cell font color (ascending) by column E




Sort on icons (ascending) by column G


Any number of sort conditions can be added (up to 3 if you target XLS files) so if you'd like to sort on column C then on column D, the corresponding conditions can be added to the tree of conditions, accordingly.

Here is an example for sorting on background cell color :

C++ code


xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"data.xls", L"output.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

xlsgen::IXlsRangePtr r = wksht->NewRange(L"C4:F7");
xlsgen::IXlsSortPtr sort = r->NewSort();

xlsgen::IXlsSortConditionPtr sc = sort->NewCondition();

sc->SortOnWhat = xlsgen::sortcondition_oncellbkgndcolor; // cell background color is used as sort criteria
sc->columnIndex = 2; // 2nd column of the range, i.e. column D
sc->SortColor = 0xFFFF00; // 0xFFFF00 = yellow

sort->Apply();



And another one for sorting on icons (related to existing conditional formatting of type icon) :

C++ code


xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"data.xls", L"output.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

xlsgen::IXlsRangePtr r = wksht->NewRange(L"C4:F7");
xlsgen::IXlsSortPtr sort = r->NewSort();

xlsgen::IXlsSortConditionPtr sc = sort->NewCondition();

sc->SortOnWhat = xlsgen::sortcondition_onicon; // cell icon is used as sort criteria
sc->columnIndex = 4; // 4th column of the range i.e. column F
sc->SortIconset = xlsgen::iconset_3trafficlights_unrimmed; // icon set to match
sc->SortIconsetIndex = 1; // which icon in iconset to match (1-based)

sort->Apply();




Posted on 29-September-2023 16:26 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.14 : Security fix for WebP files


Build 5.0.0.14 of xlsgen has a security fix related to WebP pictures. A 0-day vulnerability in the official libWebp library for reading/writing WebP pictures has been discovered. xlsgen relies on libWebp so it needed to be patched as well.

Posted on 14-September-2023 09:29 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.13 : Advanced data labels (II)


Build 5.0.0.13 of xlsgen introduces additional features to data labels, this time the ability to set a custom shape to the whole series of data points, or only one data point.


Custom data label shapes

xlsgen reads, writes, renders, and exposes in the automatic source code generation tool (xlscodegen.exe) this new feature. It is available for all XLSX/XLSB files. Notably it is not available for XLS files because Excel does not create internal records for this XLS files. This feature was made available by Microsoft first back in Excel 2013, as noted here.

Here is how to set a custom data label shape for an entire series of data points :


chart->SeriesByIndex[1]->SeriesDataLabels->VectorShape = xlsgen::vectorshapetypeEllipse;


And here is how to do it for a single data point :


chart->SeriesByIndex[1]->SeriesDataLabels->DataLabelElements[3]->VectorShape = xlsgen::vectorshapetypeOctagon;


Shape types are to be chosen from the following enumVectorShape enumeration.

Posted on 06-August-2023 10:32 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.12 : Advanced data labels


Build 5.0.0.12 of xlsgen introduces advanced data labels in charts.


Data labels leader lines (in red)


It is made of the following :

- Exposure in the object model, Read, Write, and exposure in the automatic source code generation tool (xlscodegen.exe) of data labels leader lines (see picture above), which are used to draw lines between data labels with custom positions and the actual chart data. This is for all Excel files (XLS, XLSX, XLSB, ...)

With this exposure, leader line formatting (color, width, style) can be accessed with the following type of code :

(C++)


chart002s1->SeriesByIndex[1]->SeriesDataLabels->Show = xlsgen::datalabels_value;
chart002s1->SeriesByIndex[1]->SeriesDataLabels->LeaderLinesOptions->Patterns->Borders->Type = xlsgen::chartbordertype_custom;
chart002s1->SeriesByIndex[1]->SeriesDataLabels->LeaderLinesOptions->Patterns->Borders->Style = xlsgen::chartborderstyle_solid;
chart002s1->SeriesByIndex[1]->SeriesDataLabels->LeaderLinesOptions->Patterns->Borders->Weight = xlsgen::chartborderweight_thick;
chart002s1->SeriesByIndex[1]->SeriesDataLabels->LeaderLinesOptions->Patterns->Borders->Color = 0x00B0F0;


- Exposure of arrow/symbol formatting at the end of leader lines, both ends. Read, Write, and also exposure in the automatic source code generation tool (xlscodegen.exe). This is for all Excel files (XLS, XLSX, XLSB, ...). With this, as pictured above, you can add a symbol such as a triangle. And it works like this with a couple lines of code :

(C++)


chart002s1->SeriesByIndex[1]->SeriesDataLabels->LeaderLinesOptions->Patterns->Borders->ArrowStyleBeginCap->Style = xlsgen::vectorshapearrowstyle_triangle;
chart002s1->SeriesByIndex[1]->SeriesDataLabels->LeaderLinesOptions->Patterns->Borders->ArrowStyleBeginCap->Width = xlsgen::vectorshapearrowwidth_medium;
chart002s1->SeriesByIndex[1]->SeriesDataLabels->LeaderLinesOptions->Patterns->Borders->ArrowStyleBeginCap->Length = xlsgen::vectorshapearrowlength_medium;



- Extension of data label leader lines to chart types other than pie charts. Before Excel 2013, data label leader lines were exposed in Excel but only for pie charts (and their variants such as the doughnut). With Excel 2013, Excel extended the data label leader lines to all chart types, that is, bars, lines, XY charts, ...

What xlsgen does is provide Read and Write support for this as well. Also, what xlsgen does additionally is to render those formatted data label leader lines for all chart types. This comes at no cost for a client application since not even one line of code is needed for that.


Last but not least, it is important, as xlsgen keeps adding a lot of micro details API to the object model, which can be daunting to learn, grasp and handle, it is reminded that in fact the best way to get your job done is by using a particular feature by hand in Excel, saving the file, and then using our automatic source code generation tool (xlscodegen.exe) in order to obtain the corresponding source code, in one of many of the supported programming languages. It cannot be faster this way.

Posted on 02-August-2023 18:55 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.11 : Performance fix pack (III)


Build 5.0.0.11 of xlsgen is a memory and speed performance again. It improves up to x5 the speed of writing XLSX/XLSB files with many relationships, such as files with many hyperlinks (but not only this scenario).

Posted on 26-July-2023 06:51 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.10 : Behavior of worksheet deletion


Build 5.0.0.10 of xlsgen makes a change in behavior when a worksheet is deleted.

Before this build, when a worksheet was deleted, its content would be internally erased, but the worksheet itself would still appear as part of the worksheet collection of the current workbook. And only when closing the workbook would the worksheet be actually not written back in the file, hence the worksheet deletion.

Beginning with this build, a worksheet deleted by a statement such as worksheet.Delete() immediately disappear from the worksheet collection, making it more natural to program worksheets.

Posted on 22-July-2023 19:00 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.9 : Performance fix pack (II)


Build 5.0.0.9 of xlsgen adds up on the previous build and improves both speed and memory performance of xlsgen :

- faster reading of files with many shapes

- better scaling of memory when processing many hyperlinks and shapes

Posted on 14-July-2023 14:32 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.8 : Performance fix pack


Build 5.0.0.8 of xlsgen is a performance fix pack, and also contains a fix for cell metadata :

- better handling of XLSX files with many internal relationships (hyperlinks) to avoid GPF case.

- removed most middle layer API calls to traverse relationships, avoiding bottleneck in files with many internal relationships

- refactored code in tables and pivot tables that were indirect bottlenecks as well


Also a fix for cell metadata to better return hit test for cells containing hyperlinks.


Posted on 28-June-2023 11:56 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.7 : Fix pack for pivot tables


Build 5.0.0.7 of xlsgen is a fix pack for pivot table rendering :

- introduction of Outline and Compact rendering algorithms

- better reading of existing pivot table layout setting from XLSX fles

- cell indent is now 3 space chars instead of 2 space chars, used notably in Compact rendering algorithm


Pivot table rendering has now 3 rendering layout algorithms depending on your choosing : Tabular, Outline and Compact.

Differences is that Outline and Compact don't have subtotals and grandtotals.

Compact has all row fields in a single column (hierarchically), as opposed to one column per row field.

Posted on 25-June-2023 16:12 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.6 : Fix pack


Build 5.0.0.6 of xlsgen is a fix pack.

- Systematic crash during pivot table generation in XLSX files. This is now fixed.

- Unable to read item groupings from existing pivot tables in XLSX files due to creative usage of XML standard in Excel. This is now fixed too.

- Crash when reading complex pivot tables from XLSB files. Fixed.

Posted on 24-May-2023 11:50 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.5 : Preserve color themes


Build 5.0.0.5 of xlsgen ensures that color details are preserved as is during read/write and file conversion (such as XLSX to XLSB for instance). This preserves palette indexes, color themes, rgbs, ... along with color effects such as tint, alpha, shade, luminosity modulation. This is for colors in cells and in shapes.

Posted on 17-May-2023 12:17 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.4 : Print Row and Col headings


Build 5.0.0.4 of xlsgen adds support for rendering row and column headings in scenarios such as Print, export to PDF, XPS, ...


row and column headings in xlsgen

The row and col headings in Excel is in the print settings dialog, sheet tab. And is on a per worksheet basis.

Posted on 26-April-2023 21:30 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.3 : Fix for horizontal bar chart rendering


Build 5.0.0.3 of xlsgen fixes a problem related to rendering charts with horizontal bars that happen to also be stacked and stretched. Rendering was incorrect and some of the bars could be clipped entirely.

Posted on 19-April-2023 18:16 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.2 : Preserve custom sheet views


Build 5.0.0.2 of xlsgen adds support for preserving custom sheet views. This was already supported in XLS files, but is now for XLSX and XLSB files.

Custom sheet views are views that have their own zoom level, hidden rows, frozen panes and so on. Custom sheet views can be created in Excel by going to the View menu and clicking on New Window to create a new custom sheet view. All other views are open simultaneously. And to switch which view goes in the foreground, click on Switch windows.

Posted on 19-April-2023 18:13 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 5.0.0.1 : Strict ISO 29500


Build 5.0.0.1 of xlsgen introduces read and write support for the strict version of XLSX files known as ISO 29500.

This variant is used in corporations out there for compliance purposes. The strict version of XLSX poses less security risk because it does not carry VBA macros. Internally, the XML is also more standard. And there is no pseudo-XML such as VML either.

How to you create such file ? Just like a regular XLSX file, except a property must be toggled before the file is created :


IXlsWorkbook wbk = engine.New("file.xlsx")
wbk.StrictISO29500 = true

IXlsWorksheet wksht = wbk.AddWorksheet("Sheet1)
...

wbk.Close()


Posted on 30-March-2023 21:49 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

 

<-- previous page