xlsgen bug fixes

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

 

Subscribe our RSS feed

xlsgen 4.5 preview #2 : R programming environment




R programming, or as their originators call it a freely available language and environment for statistical computing and graphics which provides a wide variety of statistical and graphical techniques: linear and nonlinear modelling, statistical tests, time series analysis, classification, clustering, etc.

R programming has become popular not just among scientists but among many corporations and hobbyists for one reason : it is an open platform. Anyone can write and publish a R package in order to extend the features and remain free to host the packages on their website, i.e. the authors of R do not have a say on the packages, unlike application stores one can find with Apple, Google and Microsoft. Plus, R itself is an open source project, meaning that anyone can recompile the source code and modify it to meet their needs.

With the R programming environment installed, xlsgen can interface with it by letting any client application evaluate R statements, pass data, that is from an Excel range to a R data frame, and vice versa.

workbook.R_Programming.Eval(L"myVector <- c(1,2,3,4)"); // create a R vector of 4 numbers

Posted on 28-October-2017 09:23 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.5 preview #1 : OpenOffice becomes first-class citizen




xlsgen 4.5 can read OpenOffice files whether they are generated from Apache's OpenOffice, The document foundation's LibreOffice and third parties. By OpenOffice files is meant OpenOffice calc files, which are .ODS files. Of course xlsgen supports the in-memory variants, so it can read a memory buffer storing the contents of an .ODS file.

Support for objects from .ODS files is fairly extensive :

  • cells
  • formatting (number format, alignment, borders, fill, font, other properties)
  • rich strings
  • formulas
  • defined names and external references
  • row and column formatting, merged cells
  • conditional formattings (cell, formula, date-based, text-based, time-based, top/bottom, duplicate, error-based)
  • advanced conditional formattings from LibreOffice (data bars, iconsets and color scales)
  • worksheet properties (selection, hidden, ...)
  • freeze / split, page breaks
  • header and footer
  • hyperlinks
  • pictures
  • charts (all chart types and subtypes, all formatting properties)
  • comments, text boxes, vector shapes, complete with their formatting properties


.ODS files, whether created from The document foundation's LibreOffice or Apache's OpenOffice, are evolving at their own pace. The document foundation's LibreOffice is more advanced than Apache's OpenOffice in a number of ways, most notably conditional formattings, where Apache's OpenOffice has support for only the two conditional formatting types (cell-based and formula-based). It's up to the corresponding open source project contributors to try to put the two on par. xlsgen reads as much as it can from those files.

Another scenario that is supported is the update of existing .ODS files using xlsgen, by reading and writing back to an .ODS file or memory buffer, as in :

VB code
Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

' read and write an existing .ODS file
Dim wbk As IXlsWorkbook
Set wbk = engine.Open "MyFile.ods", "MyFile.ods"

wbk.Close

Set engine = Nothing



xlsgen can also migrate an .ODS file to a .XLSX file for instance and vice versa.

Posted on 27-October-2017 11:30 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

The road to xlsgen 4.5



A new major upgrade to xlsgen is about to ship.

In xlsgen 4.0, support for a number of additional objects was added. Because those objects came often with a few tweek capabilities, this caused a lot of new API to be made public, which you can review in our page over at what's new in 4.0?

In 4.5 a number of objects are added. It happens that many of those objects require a lot less API and it has therefore become the general theme in 4.5. For instance, whenever a new input file format is supported, no new API is exposed and there is nothing new to learn. Ditto for an output format. Ditto for general advances in core xlsgen engines such as the rendering, or whenever we are adding new engines which happen to rely on standard third-parties.

There is hope that xlsgen becomes seen for what it is, that is more than a general purpose Excel file generator. It has been such a thing for a number of years, but it so happens that every new customer has a different need and it is perhaps not always obvious what products can do to meet your needs and how far they can push you.

Posted on 27-October-2017 00:23 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.108 : Fix for phonetic runs


Build 4.0.0.108 of xlsgen fixes a problem related to reading phonetic runs in strings in XLSX files.

Posted on 26-October-2017 09:23 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.107 : Fix for calculating dimensions


Build 4.0.0.107 of xsgen fixes a problem related to calculating sheet dimensions. An uninitialized merged cells structure could return incorrect results.

Posted on 22-September-2017 11:34 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.106 : Fix for reading corrupt objects


Build 4.0.0.106 of xlsgen fixes a problem related to reading corrupt objects in Excel files. Additional protection is added to avoid that the problem within the file is exposed to the client application.

Posted on 21-September-2017 14:17 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.105 : Fix for multi-threading


Build 4.0.0.105 of xlsgen fixes a race condition which in multi-threading scenarios could have caused a crash, when setting passwords to open protected workbooks.

Posted on 03-September-2017 00:23 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.104 : Improved clipboard


Build 4.0.0.104 of xlsgen improves how the copy to clipboard feature works when a range is copied from an XLSX or XLSB file to the Windows clipboard. There was no problem when the range was being copied from an XLS file.

Posted on 31-August-2017 10:40 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.103 : Unicode fix for XLSX files


Build 4.0.0.103 of xlsgen improves the filepath Unicode support in XLSX scenarios. An internal filepath string conversion did not always work well (Chinese).

Posted on 29-August-2017 19:41 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.102 : Improved CSV import (II)


Build 4.0.0.102 of xlsgen makes sure to avoid local on-the-stack memory allocations during the CSV import, precisely because when there is too many of them it may overflow the stack and cause a crash.

Posted on 28-August-2017 14:12 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.101 : Improved CSV import


Build 4.0.0.101 of xlsgen adds support for automatically inferring values correctly when they are expressed in percent. With such column, numbers are divided by 100 and a percent-type number format is applied to it. Because this is done automatically, there is no reason to customize the CSV import for taking benefit of this feature, which means you don't have to know any prior knowledge of the CSV data structure.

Posted on 25-August-2017 00:05 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.100 : Fix pack for pivot tables


Build 4.0.0.100 of xlsgen fixes a number of issues for creating pivot tables in XLS, XLSX, XLSB files and their variants.

- correct support for full columns when defining the pivot table data source
- correct metadata declaration for mixed data in columns, with at least one blank in cells
- correct grand totalling in XLS files

Posted on 18-August-2017 14:28 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.99 : Subtotal option for pivot tables


Build 4.0.0.99 of xlsgen adds a new member to aggregation functions in pivot tables. This member is "aggrpivotfunction_none" and what it allows to do is ask the pivot table engine to avoid displaying subtotals in row/column/page fields.

Here is a sample source code (C++) :


xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk = engine->New( L"output.xlsx" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet( L"Sheet1" );

wksht->Cell[3][2]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">s1</font>";
wksht->Cell[3][3]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">s2</font>";
wksht->Cell[3][4]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">s3</font>";
wksht->Cell[4][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">1</font>";
wksht->Cell[4][3]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">2</font>";
wksht->Cell[4][4]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">3</font>";
wksht->Cell[5][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">5</font>";
wksht->Cell[5][3]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">2</font>";
wksht->Cell[5][4]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">3</font>";
wksht->Cell[6][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">4</font>";
wksht->Cell[6][3]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">2</font>";
wksht->Cell[6][4]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">3</font>";

xlsgen::IXlsPivotTablePtr pt = wksht->NewPivotTable();
pt->DataSource->Range = L"R3C2:R6C4";

xlsgen::IXlsPivotTableFieldPtr pf1 = pt->Rows->AddByName(L"s1");
pf1->AggregateFunction = xlsgen::aggrpivotfunction_none;

pt->Rows->AddByName(L"s2");
pt->Data->AddByName(L"s3");

pt->InsertAt(3,8);

wbk->Close();


Posted on 28-July-2017 19:21 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.98 : Fix for VBA macros


Build 4.0.0.98 of xlsgen fixes a problem related to extracting VBA macros from spreadsheets (exposing them in clear text).

In the case where there are very large VBA macros in a module (512 KB), there was a memory problem. Now fixed.

Posted on 14-July-2017 18:27 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.97 : Bubble clouds


Build 4.0.0.97 of xlsgen adds support for creating bubble cloud charts.


Bubble clouds in xlsgen

Bubble clouds sparks a new form of data analysis by arranging bubbles in ways that reveal links and associations. As such,
  • bubbles are adjacent one another
  • bubbles are partially chained together, i.e. it's not possible to have a bubble standing alone


The source code involved for creating such chart is concise and straight forward.

(C++ code)

xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk = engine->New( L"bubblecloud.pdf" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet( L"Sheet1" );

xlsgen::IXlsVisualComponentPtr comp = wksht->NewVisualComponent(L"BubbleCloud.ARsTdesign.1",
	   1,1, 13,4, 0,0,0,0);

comp->ParameterByName[L"AddValues"]->Formula = L"={10;15;25;10;10;20;10;10;10;10;10;10;10;10;10;10;10;10;10;10;10;10}";

comp->ParameterByName[L"AddLabels"]->Formula = L"={\"2\";\"3\";\"cr4\";\"5\"}";

//comp->ParameterByName[L"SetBorderColors"]->Formula = L"={\"0x00FF00\"}";

comp->ParameterByName[L"SetFillColors"]->Formula = L"={\"0xFF8080\";\"0x80FF80\"}";

comp->ParameterByName[L"SetChartTitle"]->ValueAsLabel = L"bubble cloud";
comp->ParameterByName[L"SetFontName"]->ValueAsLabel   = L"Calibri";

wbk->Close();


Posted on 01-July-2017 01:41 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.96 : Protected ranges


Build 4.0.0.96 of xlsgen adds read and write support for protected ranges in XLS, XLSB and XLSX files.

Protected ranges, as the name implies, was introduced back in Excel 2003 for making it easier to create editable data in ranges, while still maintaining security (password and domain-based account names).

xlsgen exposes this in the object model. A protected range can be created from a regular range with a single method call. Setting a password is optional. Setting security by naming domain-based accounts who are indeed allowed to edit cells inside those ranges, is also optional. Of course, both the password and the domain-based account names provide value for controlling what's going on when Excel files are deployed accross the organization. This is indeed a very corporate-centric feature.

Here is how to create a protected range :

IXlsProtectedRange pr = worksheet.NewRange("B11:D14;F11:H14").ProtectedRange;
pr.Password = "aaa";
pr.AddUserOrGroup("stef"); // domain-based account name


As a bonus, protected ranges also appear in the automatic source code generator.

Posted on 25-June-2017 22:59 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.95 : Pivot table layout


Build 4.0.0.95 of xlsgen adds support for setting the layout of new pivot tables, particularly whether they are compact, in outline or tabular. Choosing between the three alters the display for improving, if used correctly, the reading experience of the data in the pivot table, particularly hierarchically oriented data.

A new property is introduced in pivot table options, namely Layout, which takes an enum as value.

pivotTable.Options.Layout = pivottablelayout_outline;

and that's it.

Posted on 14-June-2017 18:58 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.94 : Pivot charts


Build 4.0.0.94 of xlsgen adds support for pivot charts.

Pivot charts simply associate an arbitrary chart to an existing pivot table so that the rows and columns that make the pivot table are used as data source for the chart. All the common chart formatting remains available.

A PivotTableDataSource property is available in the chart object interface in order to make the association to an existing pivot table.

C/C++ code

// create a pivot table
xlsgen::IXlsWorksheetPtr wkshtNew = workbook->AddWorksheet( L"SheetNew" );

xlsgen::IXlsPivotTablePtr pt = wkshtNew->NewPivotTable();
pt->DataSource->Range = L"Data!A1:E6";

xlsgen::IXlsPivotTableFieldPtr pf_v0 = pt->Data->AddByName(L"Group Name");

xlsgen::IXlsPivotTableFieldPtr pf_v1 = pt->Data->AddByName(L"Value1");
xlsgen::IXlsPivotTableFieldPtr pf_v2 = pt->Data->AddByName(L"Value2");
xlsgen::IXlsPivotTableFieldPtr pf_v3 = pt->Data->AddByName(L"Value3");

xlsgen::IXlsPivotTableFieldPtr pf_d1 = pt->Rows->AddDataFields(); // in rows

pt->InsertAt(5,2);

// create a chart
xlsgen::IXlsWorksheetPtr wkshtChart = workbook->AddWorksheet( L"Chart" );

xlsgen::IXlsChartPtr chart = wkshtChart->NewChart(xlsgen::charttype_bar2D, 
					     2, //row1
					     2, //col1
					     17, //row2
					     9 //col2
					     );

// attach the chart to the pivot table
chart->PivotTableDataSource = pt;


In Excel, pivot charts can be tweaked by slicing and dicing rows and columns, as they reflect the pivot table slicing and dicing. Of course, the same can be done programmatically with xlsgen when the pivot table is being created.

Posted on 07-June-2017 09:25 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.93 : Fix for iterative scenarios


Build 4.0.0.93 of xlsgen fixes a problem related to use cases with iterative writing in cells.

Unlike most scenarios where values/formulas in cells are written only once, sometimes cells are used as counters or indicators hence the values may be written more than once. Cells can in fact be written many times over and over again. And xlsgen had a problem with that in its internal data structure. It would arise by writing more than 128 times a value in a given cell.

This build fixes the problem.

Posted on 27-May-2017 08:21 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

xlsgen 4.0.0.92 : Fix for pivot tables


Build 4.0.0.92 of xlsgen fixes a problem related to pivot tables.

A new method is introduced in order to make it possible to aggregate data in rows instead of columns (which was and still is by default). This is a method and not a simple boolean property because the client application can choose the position of this aggregate data in rows, or in columns, as the display follows the order of pivot fields in each of the 4 quadrants (Rows, Columns, Data, Page).

Here is how to aggregate data in rows :

pivottable.Rows.AddDataFields();

Posted on 17-May-2017 09:19 | Category: xlsgen, Excel generator | Tags: generating Excel spreadsheets

 

 

<-- previous page