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.

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

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

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.

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.

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.

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).

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.

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.

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

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();

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.

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();

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.

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.

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.

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.

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();

Build 4.0.0.91 of xlsgen adds support for creating named ranges with restricted scopes, by which is meant restricted to a single worksheet instead of all worksheets. Local scope allows to have non-unique names in each worksheet, facilitating their use.

By default any new named range has a workbook scope. Here is how to make one local to a single worksheet (C++) :

xlsgen::IXlsWorkbookPtr workbook = engine->New( L"local_namedranges.xlsx" );

xlsgen::IXlsWorksheetPtr wksht001 = workbook->AddWorksheet( L"Sheet1" );
xlsgen::IXlsWorksheetPtr wksht002 = workbook->AddWorksheet( L"Sheet2" );
xlsgen::IXlsWorksheetPtr wksht003 = workbook->AddWorksheet( L"Sheet3" );

xlsgen::IXlsRangePtr r = wksht002->NewRange(L"Sheet2!A2:A10");
r->Local = TRUE;
r->Name = L"myname_r";

xlsgen::IXlsDynamicRangePtr rr = wksht003->NewDynamicRange(L"myname_rr");
rr->Local = TRUE;
rr->Formula = L"Sheet2!A2:A10";

workbook->Close();

Build 4.0.0.90 of xlsgen brings supports to resistance trendlines, for financial decision making.

Support and resistance trendlines are used in stock trading to delimit peer pressure for, respectively, selling and buying stocks. Any time the stock data crosses the support line or the resistance line, this acts as a trade signal. Support and resistance play an opposite role.

The support line marks the signal for selling stocks, or for shorting stocks (i.e. buying on the prediction that stocks will go lower). The resistance line marks the signal for buying stocks, or for longing stocks (i.e. buying on the prediction that stocks will go higher).


Support and Resistance trend lines in xlsgen

xlsgen completely hides the underlying complexity of calculating support and resistance trendlines. Those lines are calculated based on 10-period smoothed data and the calculation of local minimum and maximum spots which are then interpolated together.

C/C++ code

xlsgen::IXlsWorkbookPtr workbook = engine->Open( L"EURUSD1.csv", L"support_resistance.xlsx" ); xlsgen::IXlsChartPtr chart = workbook->WorksheetByIndex[1]->NewChart(xlsgen::charttype_line2D, 1, //row1 2, //col1 28, //row2 15 //col2 ); xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss0 = chart->DynamicDataSource->AddSerie(); serie001s0ss0->SeriesValuesFormula = L"=R1C3:R100C3"; chart->SeriesByIndex[1]->TrendLines[1]->Show = xlsgen::charttrendline_support; // Support trendline chart->SeriesByIndex[1]->TrendLines[1]->Options->Type = xlsgen::chartbordertype_custom; chart->SeriesByIndex[1]->TrendLines[1]->Options->Style = xlsgen::chartborderstyle_dot; chart->SeriesByIndex[1]->TrendLines[1]->Options->Weight = xlsgen::chartborderweight_single; chart->SeriesByIndex[1]->TrendLines[1]->Options->Color = 0xFF0000; chart->SeriesByIndex[1]->TrendLines[2]->Show = xlsgen::charttrendline_resistance; // Resistance trendline chart->SeriesByIndex[1]->TrendLines[2]->Options->Type = xlsgen::chartbordertype_custom; chart->SeriesByIndex[1]->TrendLines[2]->Options->Style = xlsgen::chartborderstyle_dot; chart->SeriesByIndex[1]->TrendLines[2]->Options->Weight = xlsgen::chartborderweight_single; chart->SeriesByIndex[1]->TrendLines[2]->Options->Color = 0x00FF00; workbook->WorksheetByIndex[1]->Export->ExportAsPDF(L"support_resistance.pdf"); // ask xlsgen to compute and render it workbook->Close();