xlsgen 4.9.0.5 : Fix for relative cells Build 4.9.0.5 of xlsgen fixes a problem related to manipulating rows/columns with formulas using partially relative rows or columns, such as R$170 (R is relative as a column, 170 is an absolute reference due to the presence of the $ prefix). Inserting, deleting rows or columns would corrupt such formulas. There was no problem with cell references either not relative at all (for instance $A$5), or both relative for rows and columns (for instance A5).
|  |  | Posted on 24-March-2021 18:22 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9.0.4 : Style optimisation xlsgen 4.9.0.4 adds a new property to the xlsgen object model, to be used before loading files. This Style optimisation property allows to disable the style optimisation algorithm done by xlsgen on behalf of the client application, trying to remove useless styles. This algorithm is on by default and has been going on for years, it is nothing new. By setting this property as false, the algorithm is disabled, in order to meet scenarios where style optimization is not expected to occur (i.e. pure template scenarios).
engine.Workbooks.StyleOptimisation = False
engine.Open("mytemplatefile.xlsx", "")
...
|  |  | Posted on 24-February-2021 12:48 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9.0.3 : Elapsed timeBuild 4.9.0.3 of xlsgen adds direct support for elapsed time, i.e. time used with either hour, minute or second enclosed in square brackets, as in [hh]:mm:ss in order to display out of boundaries numbers. Using elapsed time, you can display with 30:00:00 the fact that you've had an event lasting 30 hours, notably bypassing the implicit limit of 24 hours. All it takes is to pass 30:00:00 as a date and apply a number format of the form [h]:mm:ss. It works like this : worksheet->Cell[2][1]->HtmlDate = L"<div format=\"[h]:mm:ss\">30:00:00</div>"; In Excel this is effectively stored as an elapsed duration of 30 hours, not a display artefact. Which means that formulas that depend on it can do their calculations properly. Square brackets can apply to minutes or seconds as well, to represent an arbitrary duration in minutes or seconds. This feature has been in Excel just about forever, but it was not added to xlsgen until now. |  |  | Posted on 26-January-2021 18:06 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9.0.2 : Fix pack for memory leaks Build 4.9.0.2 of xlsgen removes half a dozen memory leaks, in all parts of the library. This makes sustained scenarios better work.
|  |  | Posted on 25-January-2021 15:30 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9.0.1 : Retargeting name scope Build 4.9.0.1 of xlsgen makes it possible to retarget the scope of a defined name. Particularly make it local to a sheet. To that end, the Local property of existing named ranges can be set accordingly as in :
// let's say we have two sheets, Sheet1 and Sheet2
// also let's say range2 = Sheet2!R2C2:R20C5, and of global scope
workbook->WorksheetByName[L"Sheet1"]->DynamicRange[L"range2"]->Local = TRUE; // tell range2 to be local to Sheet2
|  |  | Posted on 11-December-2020 09:02 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 ships ! xlsgen 4.9 developer friendly edition ships !
Features, most notably :
- multithreaded calc engine - PDF Unicode and font embedding - SVG charts - HTML markup for rich labels - Rendering treemaps and sunbursts - Advanced OpenOffice - Performance improvements
|  |  | Posted on 26-November-2020 06:27 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 preview #7 : Improved LibreOffice/OpenOfficexlsgen 4.9 adds the following support to LibreOffice/OpenOffice files : - read existing pivot tables
- write pivot charts (introduced in libre office 5.4.1)
- read cell data validations
- read auto filter and custom filters
- improved writing of custom filters
|  |  | Posted on 25-November-2020 11:38 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 preview #6 : Performance improvementsxlsgen 4.9 includes performance improvements. Those are particularly noticeable in server scenarios where xlsgen runs for long periods of time. - for reading and creating XLS files, memory consumption much lowered by defaulting to 4KB blocks instead of 64KB blocks
- for reading chart formatting details from any Excel file, memory leak removal
|  |  | Posted on 25-November-2020 11:36 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 preview #5 : Chart renderingsxlsgen 4.9 improves chart renderings in multiple ways : - Rendering of treemaps and sunbursts. (outputs : PNG, PDF, SVG)
- Avoid chart element overlap wherever possible, for instance make sure the legend never overlaps with the plot area
- Additional chart element formatting that were not supported in past xlsgen versions
xlsgen 4.9 renders treemaps and sunbursts, charts which were introduced in Excel 2016 and were already supported in read and write. Those two charts are particularly suited when the data being charted is hierarchical in nature. For instance if you have multiple level of time groups in a timeline, or multiple level of particular regions in a geographical division. The multiple levels are taken as chart categories. Categories usually are a single row or a single column. With multiple levels of depth, it's more than one row or more than one column. Rendering treemaps in xlsgenAnd here is how you can create this : xlsgen::IXlsChartPtr chartTreemap = worksheet->NewChart(xlsgen::charttype_treemap, 9, //row1 2, //col1 25, //row2 9 //col2 );
chartTreemap->MainTitle->Label = L"treemap"; xlsgen::IXlsChartDynamicDataSourceSeriesPtr s1 = chartTreemap->DynamicDataSource->AddSerie(); s1->SeriesValuesFormula = L"=Sheet1!$D$2:$D$6"; s1->DataLabelsFormula = L"=Sheet1!$A$2:$C$6"; // Categories with 3 levels of depth
chartTreemap->ExtractAsPNG(L"treemap.png");
Rendering sunbursts in xlsgen
xlsgen::IXlsChartPtr chartSunburst = worksheet->NewChart(xlsgen::charttype_sunburst, 27, //row1 2, //col1 43, //row2 9 //col2 );
chartSunburst->MainTitle->Label = L"sunburst"; xlsgen::IXlsChartDynamicDataSourceSeriesPtr s2 = chartSunburst->DynamicDataSource->AddSerie(); s2->SeriesValuesFormula = L"=Sheet1!$D$2:$D$6"; s2->DataLabelsFormula = L"=Sheet1!$B$2:$C$6"; // Categories with 2 levels of depth
chartSunburst->ExtractAsSVG(L"sunburst.svg");
As for when to choose sunbursts over treemaps, sunbursts show the entire depth of categories, as in rings, whereas treemaps show a less detailed depth of categories. |  |  | Posted on 25-November-2020 11:26 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 preview #4 : Advanced HTML markupxlsgen 4.9 adds HTML markup to all rich label formattings, not only in cells. Rich label formatting is in many other places, notably text boxes, comments, vector shapes, chart elements (titles, data labels, ...), and the only way so far to put more than one formatting run in rich labels was to create multiple styles and pass them. In other word, there wasn't for the rich label object model, the same mechanism that was added years ago for cells, which is the ability to either pass styles (one style for each formatting run) or use HTML markup. So what xlsgen 4.9 introduces is the ability to use HTML markup as well in general purpose rich labels. Of course, you can also read existing formatting runs and obtain the HTML markup. Last but not least, the automatic source code generator tool exposes the HTML markup in rich labels. So if you open Excel, insert a text box, write this This is blue text in it, save the file, then open the automatic source code generator, you'll get the following source code fragment in the programming language of your choice, here C/C++ : xlsgen::IXlsTextBoxPtr textbox001s0 = wksht001->NewTextBox(4, 3, 9, 7, 38, 208, 128, 896); xlsgen::IXlsRichLabelPtr richLabel001s000c0 = wksht001->NewRichLabel(); richLabel001s000c0->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\"><b>This is </b></font><font color=#00B0F0 size=11 name=\"Calibri\"><b>blue </b></font>" \ L"<font color=#000000 size=11 name=\"Calibri\"><b>text</b></font>"; textbox001s0->RichLabel = richLabel001s000c0;
Before this, here is what you had to write : xlsgen::IXlsTextBoxPtr textbox001s0 = wksht001->NewTextBox(4, 3, 9, 7, 38, 208, 128, 896);
xlsgen::IXlsStylePtr styleDefault = wksht001->NewStyle(); styleDefault->Font->Bold = TRUE; styleDefault->Apply();
xlsgen::IXlsStylePtr styleBlue = styleDefault->Duplicate(); styleBlue->Font->Color = 0x0000FF; styleBlue->Apply();
xlsgen::IXlsRichLabelPtr richLabel001s000c0 = wksht001->NewRichLabel(); richLabel001s000c0->Label(L"This is ", styleDefault); richLabel001s000c0->Label(L"blue ", styleBlue); richLabel001s000c0->Label(L"text", styleDefault);
textbox001s0->RichLabel = richLabel001s000c0;
|  |  | Posted on 24-November-2020 11:18 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 preview #3 : SVG chartsxlsgen 4.9 introduces charts using the SVG file format. It is now possible to export one or more charts as SVG, or Scalable Vector Graphics. This W3C norm is now very popular on the web thanks to the nature of SVG, i.e. vector-based drawing along with its effectiveness when specifying complex combinations (clipping, gradients, ...). It is also more efficient in size. Here is a sample chart in PNG, PNG chartand then the same in SVG, SVG chartTo get this, just write : currentWorksheet.Charts.Item(2).ExtractAsSVG("c:\\tmp\\export.svg"); Of course, all chart types and their formatting details can be exported as SVG, including the new charts we are introducing in xlsgen 4.9 When SVG charts are part of an HTML page, the beauty of it is that instead of having to worry about retrieving secondary parts (images are binary elements that are loaded after the HTML page itself is loaded), SVG charts can now be part of the HTML page itself, right within the HTML markup, and so it's much simpler to handle if you are serving HTML pages on your server. By default, HTML export in xlsgen uses bitmap charts (PNG files), but all it takes is the following line of code to replace PNG charts with SVG charts during HTML export : workbook.ChartEngine = enumChartEngine.chartengine_svg; |  |  | Posted on 24-November-2020 10:21 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 preview #2 : PDF font embeddingxlsgen 4.9 improves PDF output in the following ways : - Unicode fonts : prior to this, only ANSI characters in Excel files would be encoded correctly in PDF files. That would leave much of eastern languages unsupported. With Unicode fonts, PDF files use whatever character from whatever encoding.
- Font embedding : the embedding of the subset of fonts actually used in the Excel file ensures that PDF readers can accurately display characters for viewing and printing purposes. This is activated by default, meaning that such PDF files are bigger in size from those not embedding the fonts. For this reason, font embedding can be disabled by setting the boolean value to the corresponding export option :
C/C++ code | worksheet->Export->Options->FontEmbedding = FALSE;
|
- improved character spacing : even though PDF is character-based, fonts are not. Accurate spacing depends on actual glyph bounding boxes and this is taken care of to improve the reading of words in PDF files.
|  |  | Posted on 23-November-2020 07:59 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 preview #1 : multithreaded calculation enginexlsgen 4.9 introduces a multithreaded calculation engine. xlsgen multithreaded calculation engine can be taken advantage of to accelerate calculations or just avoid having many CPU cores staying idle. Here is a snapshot of a calculation scenario using single thread calculations :  Single thread calculations (notice the 24% overall usage and the bottom right chart) |
And here is the same calculation scenario with all of the 6 CPU cores of that i5-9400 taken to handle the calculations :  Mlutithread calculations (notice the 100% overall usage and all charts) |
The amount of worker threads is up to the client application. Of course, if you are running a CPU with n cores, it makes sense to create no more than n worker threads otherwise threads will be waiting one another, not doing work in parallel. If you don't know how many cores the CPU where xlsgen runs has, it can be queried very easily : C/C++ code |
int nbCores = workbook->CalculationOptions->MultithreadCores;
|
And then, creating n worker threads for next calculations is just as simple : C/C++ code |
workbook->CalculationOptions->MultithreadCores = n;
workbook->CalculateFormulas();
|
|  |  | Posted on 22-November-2020 10:01 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.9 developer friendly editionxlsgen 4.9 is coming and the theme for this next major release is developer friendliness. More than half of the features are actually meant to make existing tasks easier to write and use, such as PDF engine improvements, chart rendering improvements, HTML markup improvements, and so on. Over the next few days, we'll be previewing details. And also new features such as the introduction of multithreaded calculations, SVG chart export and so on.  |  |  | Posted on 22-November-2020 09:57 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.5.0.99 : Fix for HTML in cells (III) Build 4.5.0.99 of xlsgen fixes a problem related to content with multiple formatting runs in cells, particularly for the calculation of the height of autofit.
|  |  | Posted on 20-November-2020 13:22 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.5.0.98 : Fix for rendering Build 4.5.0.98 of xlsgen has two fixes related to rendering :
- some merged cells incorrectly (by Excel!) storing a different style (particularly background color) across the cells, still have to be rendered using only the style referenced in the top left corner of the merged cells area
- in XLSX files, some HTML encoded characters were not parsed correctly to Unicode
|  |  | Posted on 14-October-2020 09:49 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.5.0.97 : Fix for HTML in cells (II) Build 4.5.0.97 of xlsgen does not ignore multiple EOL characters when it occurs, especially for the calculation of row autofit.
|  |  | Posted on 22-September-2020 13:43 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.5.0.96 : Fix for HTML in cells Build 4.5.0.96 of xlsgen is a fix related to HTML in cells. It improves how non-font HTML tags relate to font HTML tags.
|  |  | Posted on 17-September-2020 15:18 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.5.0.95 : Fix for the INDEX() function Build 4.5.0.95 of xlsgen fixes a problem related to calculating the INDEX() function. Under certain circumstances, the function would be unable to calculate at all.
|  |  | Posted on 26-May-2020 15:02 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets xlsgen 4.5.0.94 : Fix pack Build 4.5.0.94 of xlsgen is a fix pack. It has the following :
- fixed "fit to page" print scenarios in real and virtual printing
- fixed rendering of merged cells at edges of pages
- improved row height computation (autofit) in presence of merged cells
- improved row height computation (autofit) scenarios
|  |  | Posted on 06-April-2020 20:36 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets <-- previous page
|
|