xlsgen bug fixes

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

 

Subscribe our RSS feed

xlsgen 3.0 build #16 : Text reading-order


Build 3.0.0.16 of xlsgen adds read/write support for text reading-order in XLS and XLSX files.

Text reading-order is relevant in far east languages.

A new enumeration is created :

typedef enum
{
[helpstring("Text reading order, context")] textreadingorder_context = 0,
[helpstring("Text reading order, left to right")] textreadingorder_left_to_right = 1,
[helpstring("Text reading order, right to left")] textreadingorder_right_to_left = 2
} enumTextReadingOrder;


And the property is exposed at several levels :

- style : Alignment.ReadingOrder property (IXlsAlignment interface)
- chart text elements : Options.Alignment.ReadingOrder property (IXlsChartAlignment interface)

Also the property is exposed in html cells, as in <b rtl=true>data</b>.

Also the automatic source code generator outputs the corresponding source code, when used.

Posted on 03-February-2012 08:59 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #15 : More conditional formattings


Build 3.0.0.15 of xlsgen adds more conditional formattings to XLSX files.

They are as follows :
- top values : for instance highlights the top 5 values
- bottom values
- top values in percent : for instance highlights the top 10% values
- bottom values in percent
- above or below average : with several average variants
- duplicate values : for instance highlights values that are duplicates
- unique values : for instance highlights values that are unique

Trying to create such conditional formattings in XLS files will return error messages. The reason why is that they are new in the XLSX file format.

Here is how you get to highlight unique values in a set :

// create a style
IXlsStyle s = worksheet.NewStyle();
s.Pattern.BackgroundColor = 0xCCFFCC;

// highlights unique values in column B using style s
IXlsConditionalFormatting cf = worksheet.NewRange("B:B").NewConditionalFormatting();
cf.PredefinedCondition.UniqueValues();
cf.Style = s;

Posted on 16-January-2012 09:44 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #14 : Fix for editing hyperlinks


Build 3.0.0.14 of xlsgen has a fix related to editing existing hyperlinks in XLSX files.

Before that build, changes to existing hyperlinks would be ignored when it caming to writing the XLSX files.

No source code change needed.

Posted on 10-January-2012 22:03 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #13 : Fix for chart axis crossing


Build 3.0.0.13 of xlsgen has a fix related to chart axis crossing values in XLSX files (Excel 2007/2010).

xlsgen did not write the value at which an axis could cross the alternate axis in the case of XY charts (scatter, bubbles).

This fix also reads the value from existing charts (all charts) from XLSX files. And also exposes the value in the automatic source code generation tool.

No source code change needed.

Posted on 05-January-2012 10:01 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #12 : Fix for overlapping shared formulas


Build 3.0.0.12 of xlsgen fixes a problem to shared formulas overlapping one onto others.

The shared formulas is a mechanism in Excel meant to optimize the storing of formulas. A use case that was never seen before, considered valid by Excel, is the overlapping of two shared formulas areas. xlsgen could be disturbed by this and corrupt the formulas in the overlap.

This build fixes the problem in question.

No source code change needed.

Posted on 20-December-2011 21:59 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #11 : Fix for wrapped cells in print/preview/PDF


Build 3.0.0.11 of xlsgen has a fix related to the handling of vertical alignment in cells when doing a print/preview/PDF.

The change is related to the ignoring of the vertical alignment setting whenever the cell is both marked as wrapped and left aligned horizontally.

xlsgen does not ignore the vertical alignment anymore.

No source code changes needed.

Posted on 15-December-2011 14:40 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #10 : Table subtotals


Build 3.0.0.10 of xlsgen introduces another business intelligence feature : table subtotals.

Continuing last build, which introduced table breaks, this time xlsgen adds the ability to compute subtotals.

It is a straight-forward feature. The ability to aggregate data along a numeric axis.

The aggregate function lets one customize the process. Among the functions are : sum, min, max, average and count.

And here is how it looks like :


Table subtotals

Internally, xlsgen does build actual Excel formulas, computes them and put their results in cells in the footer of each break.

Here is how to produce the subtotals :


worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("City").Breaks
pb.DistinctValues()

IXlsTableColumn tcSales = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Sales index")

IXlsTableSubtotal sts = pb.Subtotals.Add(tcSales)
sts.BuiltinFunction = xlsgen.enumSubtotalFunction.subtotalfunc_min
sts.Label = "min = "

IXlsTableSubtotal stm = pb.Subtotals.Add(tcSales)
stm.BuiltinFunction = xlsgen.enumSubtotalFunction.subtotalfunc_max
stm.Label = "max = "

pb.Apply()


Posted on 10-December-2011 23:44 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #9 : Table breaks


Build 3.0.0.9 of xlsgen introduces table breaks, a new capability.


xlsgen business intelligence features include the ability to break tables in sections according to criterias.

The most obvious criteria is distinct values, which is straight forward on columns whose data type is a string. This mechanism allows to break on every other value, and create a section accordingly making it easier to understand the actual data.

The other criteria is a generic formula.

The capabilities are exposed in the IXlsTableBreaks interface.


Breaking on distinct values



Here is the raw data we are working with :


Data before applying breaks

The City column has a set of two distinct values : {Rockwell, Marver}. By breaking on distinct values, xlsgen creates the corresponding sections.


worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("City").Breaks
pb.DistinctValues()
pb.Apply()


And the following result is obtained :


Breaking on distinct values


Breaking on a generic formula



Whenever the criteria to break the table is more complex than just distinct values, xlsgen exposes a generic formula mechanism, which can use one or more column names and any Excel formula. An example follows :

In the first example, the data we have is a capture from For(eign)Ex(change) currency data, which is a time series capturing forex data from 3:00 PM to 5:30 PM on a particular day, on a per minute basis.


Data before applying breaks

We may be interested in breaking all transactions per hour, therefore break on the Time column according to a criteria involving the HOUR() function :


worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Time").Breaks
pb.Formula = "=HOUR([Time])"
pb.Apply()


Doing so we obtain 3 breaks, one for the time period [3:00-3:59], one for [4:00-4:59] and one for [5:00-5:59].

Breaking on a generic formula, =HOUR([Time])

In the second example, the generic formula mechanism can be used to enforce predefined value ranges. For instance, we have a Sales index column where the data is between 0 and 100, and we would like to put all values in the [0-80] range in a particular bucket and everything above 80 in another bucket.


Data before applying breaks



worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Sales index").Breaks
pb.Formula = "=IF([Sales index] > 80; 2; 1)"
pb.Apply()



And the following result is obtained :


Breaking on distinct value ranges (inside [0-80], and above 80)

Posted on 01-December-2011 08:29 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #8 : Fix pack


Build 3.0.0.8 of xlsgen brings a fix pack with the following relevant changes :

- sheet names to be enclosed in quotes in XLSX files whenever the names look like cell references, as in 'T1'!A1
- do not write alignment properties in conditional formattings in XLSX files since Excel 2007/2010 complains about it (there is no Alignment tab in Excel for conditional formattings)
- unary -- in formulas (i.e. better handling of case with multiple unary operators)
- table inference internal headers better inferred (cas with date versus time)

No source code change needed.

Posted on 21-November-2011 19:29 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #7 : Fix for IEEE values in formulas


Build 3.0.0.7 of xlsgen has a fix for IEEE values in exponential form in formulas from XLSX files.

IEEE values in exponential form are : +-dddd(.)dddddE+-cccc. An example is : 2.5E-12

No source code change needed.

Posted on 17-November-2011 08:48 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #6 : Fix pack


Build 3.0.0.6 of xlsgen is a fix pack. It contains the following :

- CSV import : better auto-discovery feature of headers
- XLSX writing : handling of custom part names (written by tools other than Excel)
- Additional Page setup properties duplicated with worksheet duplication
- New sample code : "business intelligence"
- Fix for table inference
- Support for secondary Y chart axis in XLSX reading scenarios
- Support for alternate chart types in XLSX reading scenarios
- Print preview scale now create a better PDF (no flatten print area)
- Fix for reading XLS files created by Crystal Reports
- Preserve the scrollbar position per sheet when reading/writing XLSX files
- Fix for nested sorting (business intelligence API)
- Workaround for the bogus DrawText() WIN32 API calls, used for rendering text

Posted on 11-November-2011 10:51 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #5 : Advanced CSV import (II)


Build 3.0.0.5 of xlsgen adds more capabilities and continues what was introduced in build 3.0.0.4

This time the new feature is the conditional import formula.

It's the ability to set up a formula that governs if rows will be imported or not.

For instance, let's say we have the following CSV file :


---------- datasource.csv ----------
ID;DESCRIPTION;DATE;PRICE
45;description1;10/06/2006;10,2
12;description2;11/06/2006;5,25
32;description3;12/06/2006;6,3
86;description4;13/06/2006;9,99
74;description5;14/06/2006;4,45
29;description6;15/06/2006;8,5
31;description7;16/06/2006;7
56;description8;17/06/2006;11,5
52;description9;18/06/2006;11
---------- datasource.csv ----------


Assuming we would like to import only the rows where the ID is greater than 50, then having identified that the ID column is the first column, we can do this (C++ below) :


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

wksht->Import->CSV->Options->ColumnConditionalImportFormulaByIndex[1] = L"=CELL(\"contents\") > 50";

wksht->Import->CSV->ImportFile(L"input\\datasource.csv");



And the resulting sheet contains the following :







IDDESCRIPTIONDATEPRICE
86description4388819,99
74description5388824,45
56description83888511,5
52description93888611



Posted on 02-November-2011 09:42 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #4 : Advanced CSV import


Build 3.0.0.4 of xlsgen adds advanced CSV import capabilities. What is introduced is the ability to specify custom data import mapping in order to ease the import of data. An example speaks best.

Assuming we would like to import the following data in an Excel file using xlsgen (the EUR/USD ticks are taken from a trading tool) :


---- EURUSD_ticks.csv excerpt -----------------------------------------------
2011.10.25,03:00,1.39076,1.39092,1.39060,1.39061,41
2011.10.25,03:01,1.39062,1.39070,1.39029,1.39033,37
2011.10.25,03:02,1.39031,1.39044,1.39015,1.39042,55
2011.10.25,03:03,1.39040,1.39040,1.38997,1.38997,51
2011.10.25,03:04,1.38997,1.39028,1.38997,1.39018,48
2011.10.25,03:05,1.39017,1.39034,1.39009,1.39016,41
2011.10.25,03:06,1.39020,1.39025,1.39001,1.39009,55
2011.10.25,03:07,1.39017,1.39041,1.39014,1.39037,40
2011.10.25,03:08,1.39037,1.39078,1.39037,1.39077,40
2011.10.25,03:09,1.39079,1.39112,1.39079,1.39094,94
2011.10.25,03:10,1.39095,1.39107,1.39086,1.39091,71
2011.10.25,03:11,1.39093,1.39105,1.39087,1.39102,34
2011.10.25,03:12,1.39101,1.39102,1.39075,1.39098,57
2011.10.25,03:13,1.39098,1.39102,1.39077,1.39077,21
2011.10.25,03:14,1.39076,1.39078,1.39055,1.39061,72
---- EURUSD_ticks.csv excerpt -----------------------------------------------


If we import EURUSD_ticks.csv as is, without custom data mapping, the resulting will not read the first column as a date, but as a raw string (notice the left alignment), therefore unable to be play its role in Excel calculations and display. The second column will be read as a time which is correct but not displayed in a human-friendly manner. Here is a screen capture of what it would look like in Excel :


Importing EURUSD_ticks.csv in xlsgen with default mappings results in incorrect data

By applying custom data mapping for both first and second columns, the dates are correctly imported and time is correctly displayed :


Importing EURUSD_ticks.csv in xlsgen with custom mappings results in correct data

And here is the corresponding code (C++) :


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

// we create a regular Excel file, and use the import interfaces
//  data types are custom bound
//  since we know the incoming data uses a particular data format

xlsgen::IXlsWorkbookPtr workbook = engine->New(L"ResultingFile.xls");
xlsgen::IXlsWorksheetPtr wksht = workbook->AddWorksheet(L"Sheet1");

xlsgen::IXlsCSVImportOptionsPtr options = wksht->Import->CSV->Options;
options->ColumnDataMappingFormatByIndex[1] = L"YYYY.MM.DD";
options->ColumnDataMappingFormatByIndex[2] = L"HH:MM";

wksht->Import->CSV->ImportFile(L"EURUSD_ticks.csv");

workbook->Close();


Posted on 25-October-2011 10:02 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #3 : Fix for macro 4.0 sheets


Build 3.0.0.3 of xlsgen fixes a problem in XLSM files embedding Macro 4.0 sheets, particularly International Macro 4.0 sheets. The corresponding markup was not supported so far.

No source code change needed.

Posted on 17-October-2011 09:51 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #2 : Performance improvements (II)


Build 3.0.0.2 of xlsgen continues on 3.0.0.1 and finds more performance improvements to make. No less than 40% faster in speed for reading XLSX files (the larger the files, the greater the speed change). And up to 15% in speed for writing XLS files.

If you are making intense use of xlsgen, and were using an old version (2.9, 2.5, ...), it's an opportunity to move forward.

Posted on 11-October-2011 11:36 | Category: xlsgen, Excel generator

 

xlsgen 3.0 build #1 : Performance improvements


Build 3.0.0.1 makes great strides in performance. 35% less memory used for processing XLSX files. Up to 15% speed up improvement for processing XLSX files.

Posted on 07-October-2011 17:45 | Category: xlsgen, Excel generator

 

xlsgen 3.0 has shipped!


xlsgen 3.0 has shipped!

A wealth of new capabilities in a number of areas. Whether you are interested in generating raw Excel files, analyzing, introspecting, calculating, reporting and now introducing Business intelligence, xlsgen comes with extreme versatility, speed on so many development platforms all at once.

If you are using an older version of xlsgen, such as 2.9, a new license is needed.


What's new in 3.0?


  • Business intelligence
  • Vector shapes (all basic types) and their associated formattings (lines, fills, ...)
  • Data bars, color scales and icon sets. See here for more information
  • Read and extract VBA macros (i.e. plain text)
  • Histograms
  • File encryption
  • Support for 21 new formulas in the calculation engine : ABS, ACOS, ADDRESS, AND, ASIN, ATAN, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CLEAN, CHAR, CHOOSE, CODE, COLUMN, COLUMNS, CONCATENATE, CORREL, COS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEVALUE, DAY, DAYS360, DEGREES, DELTA, DEVSQ, ERROR.TYPE, EVEN, EXACT, EXP, EXPONDIST, FACT, FACTDOUBLE, FALSE, FIND, FIXED, FLOOR, FREQUENCY, GCD, GEOMEAN, HLOOKUP, HOUR, IF, IFERROR, INDEX, INDIRECT, INT, INTERCEPT, INTERSECT, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, KURT, LARGE, LCM, LEFT, LEN, LINEST, LN, LOG, LOG10, LOGEST, LOGNORMDIST, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MOD, MODE, MONTH, MROUND, N, NA, NORMDIST, NORMSDIST, NOT, NOW, ODD, OFFSET, OR, PEARSON, PERCENTILE, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANK, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SLOPE, SMALL, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, T, TAN, TEXT, TIME, TIMEVALUE, TODAY, TRIM, TRIMMEAN, TRUE, TYPE, UNION, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, YEAR
  • Google charts
  • Export as XPS
  • Custom lists
  • Range helper : search and replace
  • A few new code samples (visual_component, vector_shapes, databar, histogram, read_VBA_macros, querySQLite_datasource, custom_lists)
Posted on 07-September-2011 14:56 | Category: xlsgen, Excel generator

 

xlsgen 3.0 feature #9 : Custom lists



With custom lists, xlsgen 3.0 offers a versatile mechanism for processing data in an order that is not predetermined. The first and most explicit example for custom lists is sorting dates, particularly months : january, february, ...If months are to be sorted alphabetically, january will not come first, so the sorting will be seen as flawed. In fact it takes a custom list to define the expected order.

While sorting dates is trivial, sorting other objects is as easy to implement. And there is no reason to limit this to sorting either. For instance, one could use it in the Remove duplicates feature that we've been exposing for some time in order to define a particular relational order between objects. And to help matters, the list has a few important features :

  • it can have a name
  • it can have an arbitrary number of entries (for instance, january, february, ...)
  • each entry can have any number of alternate names (for instance jan, Jan, ...)
  • each entry is selectable, and the selection is passed around


While two uses of custom lists have already been mentioned, Sorting and Remove duplicates, we'll mention a third use, which is Prompting. Prompting comes when the user has to pick one or more choice in a list. The custom list perfectly fits that model. But it can go well beyond with nested prompting which means entries a list have references to other lists, for instance a country has a number of regions and each region has a number of cities. The corresponding custom lists let define and implement applications where the user will be able to zoom in or out the desired level of detail. That is exactly what is available in the business intelligence prompting features.

A code sample using custom lists in different ways is available in the /samples folder, custom_lists.

Creating custom lists




C# code

// build a calendar list meant to allow proper date sorting
IXlsList list = worksheet.NewList();
list.Add("january 2011");
  list.AddAlternate("january");
list.Add("feb 2011");
  list.AddAlternate("february 2011");
  list.AddAlternate("february");
list.Add("march 2011");
list.AddAlternate("march");
...



Using custom lists


Using custom lists for sorting (1)



With the regular sort API, it goes like this :


C# code

// sort (ascending)
IXlsSort sort = worksheet.NewRange("C5:E8").NewSort();
sort.PrimaryCustomKey(0, list, 1);
sort.Apply();



Using custom lists for sorting (2)



With the business intelligence API, it goes like this :


C# code

// same sort (except descending versus ascending) using business intelligence APIs
worksheet.Tables.InferFromWorksheet();
IXlsTableSort s1 = worksheet.Tables.get_Item(1).DataResults.Columns.get_ItemByName("Date").Sorting;
s1.Order = 0;
s1.CustomList = list;
s1.Apply();
worksheet.InsertTableAt(worksheet.Tables.get_Item(1), 4, 7);



Using custom lists for removing duplicates




C# code

IXlsList listIndicators = worksheet.NewList();
listIndicators.Add("L1");
  listIndicators.AddAlternate("L2"); // that is to say : L2 should be seen as equal to L1

// remove duplicates (custom list)
worksheet.NewRange("R11C3:R15C5").RemoveDuplicatesUsingList(listIndicators);




Posted on 07-September-2011 12:27 | Category: xlsgen, Excel generator

 

xlsgen 3.0 feature #8 : 21 additional calc functions, Search/Replace


xlsgen 3.0 adds 21 functions to the calculation engine (in bold below) :

ABS, ACOS, ADDRESS, AND, ASIN, ATAN, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CLEAN, CHAR, CHOOSE, CODE, COLUMN, COLUMNS, CONCATENATE, CORREL, COS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEVALUE, DAY, DAYS360, DEGREES, DELTA, DEVSQ, ERROR.TYPE, EVEN, EXACT, EXP, EXPONDIST, FACT, FACTDOUBLE, FALSE, FIND, FIXED, FLOOR, FREQUENCY, GCD, GEOMEAN, HLOOKUP, HOUR, IF, IFERROR, INDEX, INDIRECT, INT, INTERCEPT, INTERSECT, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, KURT, LARGE, LCM, LEFT, LEN, LINEST, LN, LOG, LOG10, LOGEST, LOGNORMDIST, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MOD, MODE, MONTH, MROUND, N, NA, NORMDIST, NORMSDIST, NOT, NOW, ODD, OFFSET, OR, PEARSON, PERCENTILE, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANK, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SLOPE, SMALL, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, T, TAN, TEXT, TIME, TIMEVALUE, TODAY, TRIM, TRIMMEAN, TRUE, TYPE, UNION, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, YEAR


xlsgen 3.0 also adds search/replace capabilities :



Trivial to use : worksheet.NewRange("C3:I9").SearchAndReplace("word", "ppt");

The method call actually also returns how many replacements have occured making it easy to track the evolution of the contents of the worksheet.

Posted on 06-September-2011 13:43 | Category: xlsgen, Excel generator

 

xlsgen 3.0 feature #7 : Google charts


 
native support for Google charts in xlsgen 3.0

Google charts are fixed representations calculated and rendered over an internet connection (which is therefore required). All Excel chart types are available. A number of formatting properties are made available through xlsgen 3.0.

Sometimes in the future, it is possible Google charts will replace today's Microsoft OWC chart controls used for rendering spreadsheets in xlsgen (print, preview, PDF, XPS, HTML, ...) so that the client application may be able to pick his preferred choice.

xlsgen exposes the Google charts to a client application through an ActiveX control. There are already two ActiveX controls distributed by xlsgen, Vu-meters and Sparklines. Technically speaking all three ActiveX controls are embedded in the same and unique file : Sparklines.ocx, which has all 3 APIs.

Properties are being passed general formulas. They can be as simple as literals, such as in =120, or cell references, such as in =$E$4, or "real" formulas such as in =SUM(B2:B10). It transfers a lot of value to the client application which can indeed implement complex scenarios and various dynamics.

// Chart types supported

typedef enum
{
[helpstring("Chart type, vertical bars")] charttype_barsvertical = 0,
[helpstring("Chart type, vertical bars, stacked")] charttype_barsverticalstacked = 1,
[helpstring("Chart type, horizontal bars")] charttype_barshorizontal = 2,
[helpstring("Chart type, horizontal bars, stacked")] charttype_barshorizontalstacked = 3,
[helpstring("Chart type, lines")] charttype_lines = 4,
[helpstring("Chart type, lines with markers")] charttype_linesmarkers = 5,
[helpstring("Chart type, scatter, two series at least")] charttype_scatter = 6,
[helpstring("Chart type, bubbles, two series at least")] charttype_bubbles = 7,
[helpstring("Chart type, pie")] charttype_pie = 8,
[helpstring("Chart type, pie 3D")] charttype_pie3d = 9,
[helpstring("Chart type, donut")] charttype_donut = 10,
[helpstring("Chart type, radar")] charttype_radar = 11,
[helpstring("Chart type, areas")] charttype_areas = 12,
[helpstring("Chart type, stock, 4 series")] charttype_stockLowOpenCloseHigh = 13
} enumGoogleChartsType;


// Sample code in C# creating a pie 3D in Google charts

IXlsWorkbook wbk = engine.New( "googlecharts_pie.xls" );

IXlsWorksheet wksht = wbk.AddWorksheet("sheet");

IXlsVisualComponent comp = wksht.NewVisualComponent("GoogleCharts.ARsTdesign.1",
1,1, 10,3, 0,0,0,0);

comp.get_ParameterByName("SetType").Formula = "=9"; 9 = pie 3D

comp.get_ParameterByName("AddSeries").Formula = "={12;21;30}";

comp.get_ParameterByName("SetLabels").Formula = "={\"orange\";\"apple\";\"lemon\"}";

comp.get_ParameterByName("SetTitle").Formula = "=\"pie 3D\"";

Posted on 05-September-2011 18:26 | Category: xlsgen, Excel generator

 

 

<-- previous page