xlsgen > overview > Ranges

Ranges are an essential part of Excel, and now of xlsgen. The use of ranges for the creation of Excel worksheets is a time saver and helps articulate a clean separation between data and formatting. Simple ranges are continuous blocks of cells.


Creating a range in Excel

Ranges have a strong support in Excel through numerous options. Select a block of cells, then enter a name for this range thanks to the edit box in the top-left angle of the Excel worksheet. Drop down the combo-box to select an existing range. You can also define ranges more accurately thanks to the Insert / Name / Define menu option. Once a range is selected, you can right-click and apply some formatting, or create a graph from it, etc.


Ranges in Excel

Ranges are continuous blocks of cells out of which properties like style can be associated. More complex ranges can combine simple ranges to make unions. There are also built-in ranges, for instance whenever you use the File / Print Area / Adjust menu option, a built-in range is created and appears in the range combo-box of the current worksheet. xlsgen supports all this, programmatically.

 

Benefits of using ranges

 


Simple and complex ranges

One of the great strengths of xlsgen is the ability to reuse existing ranges from Excel templates. This lets not only work with ranges rather than cells, which means a lot less code to write, but also the ability to change the definition of ranges to accomodate change over time. The remainder of this page explains how to use ranges.

 

Creating ranges

Assume we want to achieve the following effect in an Excel worksheet (please note this is not a merged cells even though it may look like it is) :


How much code do you need to achieve this if you create the effect by hand, then use reverse engineering to produce the code for it ? 84 lines, here is the code for it :

C++ code

//
// the following piece of code does not use ranges and indeed
// shows the benefits of using ranges.
//


// declaration of styles

xlsgen::IXlsStylePtr style0000 = wksht001->NewStyle();
style0000->Borders->Top->Style = xlsgen::border_dashed;
style0000->Borders->Top->Color = 0xFF0000;
style0000->Borders->Left->Style = xlsgen::border_dashed;
style0000->Borders->Left->Color = 0xFF0000;
style0000->Pattern->Pattern = xlsgen::pattern_solid;
style0000->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0001 = wksht001->NewStyle();
style0001->Borders->Top->Style = xlsgen::border_dashed;
style0001->Borders->Top->Color = 0xFF0000;
style0001->Pattern->Pattern = xlsgen::pattern_solid;
style0001->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0002 = wksht001->NewStyle();
style0002->Borders->Top->Style = xlsgen::border_dashed;
style0002->Borders->Top->Color = 0xFF0000;
style0002->Borders->Right->Style = xlsgen::border_dashed;
style0002->Borders->Right->Color = 0xFF0000;
style0002->Pattern->Pattern = xlsgen::pattern_solid;
style0002->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0003 = wksht001->NewStyle();
style0003->Borders->Left->Style = xlsgen::border_dashed;
style0003->Borders->Left->Color = 0xFF0000;
style0003->Pattern->Pattern = xlsgen::pattern_solid;
style0003->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0004 = wksht001->NewStyle();
style0004->Pattern->Pattern = xlsgen::pattern_solid;
style0004->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0005 = wksht001->NewStyle();
style0005->Borders->Right->Style = xlsgen::border_dashed;
style0005->Borders->Right->Color = 0xFF0000;
style0005->Pattern->Pattern = xlsgen::pattern_solid;
style0005->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0006 = wksht001->NewStyle();
style0006->Borders->Bottom->Style = xlsgen::border_dashed;
style0006->Borders->Bottom->Color = 0xFF0000;
style0006->Borders->Left->Style = xlsgen::border_dashed;
style0006->Borders->Left->Color = 0xFF0000;
style0006->Pattern->Pattern = xlsgen::pattern_solid;
style0006->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0007 = wksht001->NewStyle();
style0007->Borders->Bottom->Style = xlsgen::border_dashed;
style0007->Borders->Bottom->Color = 0xFF0000;
style0007->Pattern->Pattern = xlsgen::pattern_solid;
style0007->Pattern->BackgroundColor = 0xFFDDAA;

xlsgen::IXlsStylePtr style0008 = wksht001->NewStyle();
style0008->Borders->Bottom->Style = xlsgen::border_dashed;
style0008->Borders->Bottom->Color = 0xFF0000;
style0008->Borders->Right->Style = xlsgen::border_dashed;
style0008->Borders->Right->Color = 0xFF0000;
style0008->Pattern->Pattern = xlsgen::pattern_solid;
style0008->Pattern->BackgroundColor = 0xFFDDAA;

// actual formatting

style0000->Apply();
wksht001->Label[3][3] = L"";
style0001->Apply();
wksht001->Label[3][4] = L"";
style0001->Apply();
wksht001->Label[3][5] = L"";
style0002->Apply();
wksht001->Label[3][6] = L"";
style0003->Apply();
wksht001->Label[4][3] = L"";
style0004->Apply();
wksht001->Label[4][4] = L"";
style0004->Apply();
wksht001->Label[4][5] = L"";
style0005->Apply();
wksht001->Label[4][6] = L"";
style0006->Apply();
wksht001->Label[5][3] = L"";
style0007->Apply();
wksht001->Label[5][4] = L"";
style0007->Apply();
wksht001->Label[5][5] = L"";
style0008->Apply();
wksht001->Label[5][6] = L"";

Now let's use a range object instead. Here is the equivalent code :

C++ code

//
// using ranges
//

xlsgen::IXlsRangePtr range = wksht->NewRange("R2C2:R5C5");
range->Style->Pattern->Pattern = xlsgen::pattern_solid;
range->Style->Pattern->BackgroundColor = 0xFFDDAA;
range->Style->Borders->Bottom->Color = xlsgen::colorRed;
range->Style->Borders->Bottom->Style = xlsgen::border_dashed;
range->Style->Borders->Top->Color = xlsgen::colorRed;
range->Style->Borders->Top->Style = xlsgen::border_dashed;
range->Style->Borders->Right->Color = xlsgen::colorRed;
range->Style->Borders->Right->Style = xlsgen::border_dashed;
range->Style->Borders->Left->Color = xlsgen::colorRed;
range->Style->Borders->Left->Style = xlsgen::border_dashed;
range->Apply();

This is only 12 lines instead of 84, to achieve exactly the same effect. In fact, whenever the style to be applied on borders is on the same on the 4 corners (this excludes the diagonals), then it is possible to apply the style to the Borders property itself, leaving the code snippet to only 6 lines :

C++ code

//
// using ranges
//

xlsgen::IXlsRangePtr range = wksht->NewRange("R2C2:R5C5");
range->Style->Pattern->Pattern = xlsgen::pattern_solid;
range->Style->Pattern->BackgroundColor = 0xFFDDAA;
range->Style->Borders->Color = xlsgen::colorRed;
range->Style->Borders->Style = xlsgen::border_dashed;
range->Apply();

You might ask, how do I set the borders inside ? Easy answer, just use the new MidHorizontal and MidVertical borders, as in :


The code for setting middle borders is as follows :

C++ code
xlsgen::IXlsRangePtr range = wksht->NewRange("R2C2:R5C5");
range->Style->Pattern->Pattern = xlsgen::pattern_solid;
range->Style->Pattern->BackgroundColor = 0xFFDDAA;
range->Style->Borders->Color = xlsgen::colorRed;
range->Style->Borders->Style = xlsgen::border_dashed;
range->Style->Borders->MidHorizontal->Color = xlsgen::colorBlue;
range->Style->Borders->MidHorizontal->Style = xlsgen::border_dashed;
range->Apply();

Defining the boundaries of the form RxxCxxx:RyyCyyy can be tedious especially if those boundaries need to be built dynamically. There is an alternative may to assign boundaries, for instance when you combine more than one contiguous block of cells into a single range (what is called a union) :

C++ code
xlsgen::IXlsRangePtr range = wksht->NewRange("R2C2:R5C8");
range->AddSubRange("R8C2:R10C6"); // combine with this one
range->AddCellsRange(13 /*row*/,3 /*col*/,13 /*row*/,6 /*col*/); // combine with this one

which can be used to obtain the following :


The rectangular area of a range needs need be defined by the cartesian form RxxxCxxx:RyyyCyyy. You can use the natural Excel form, example A2:B5.

 

Creating named ranges

So far, the ranges that have been created were only used as a mean to an end, namely apply formatting properties to blocks of cells. But there is more to it, it's possible to use named ranges to define logical blocks of cells, and reuse named ranges. When you create a named range, do not use special characters like spaces. Using named ranges automatically promote the ranges to Excel ranges, including a regular entry in the combo-box drop down in the worksheet in which each range is defined when the resulting workbook is opened in Excel. How it works is really simple, assign a name to the range, as in :

C++ code
xlsgen::IXlsRangePtr myrange = wksht->NewRange("R2C2:R5C8");
myrange->Name = L"myrange";
myrange->Apply();

To reuse an existing range, the worksheet exposes a range-related method :

C++ code
xlsgen::IXlsRangePtr myrange = wksht->Range[L"myrange"];
if (myrange)
{
  myrange->Style->Pattern->BackgroundColor = 0xFF0000;
  myrange->Apply();
}

When an existing range object is extracted, it is possible to redefine his boundaries, as in :

C++ code
xlsgen::IXlsRangePtr myrange = wksht->Range[L"myrange"];
if (myrange)
{
  myrange->Range = L"R3C6:R10C9"; // redefine the range boundaries
  myrange->Style->Pattern->BackgroundColor = 0xFF0000;
  myrange->Apply();
}

Reusing or redefining ranges makes particular sense with existing Excel files. In one scenario, the developer can change the definition of a particular range and still keep the same related code dealing with that range (for instance formatting code). Doing so, the range becomes a logical range and can apply properties to blocks of cells without the developer knowing about them, or without being limited to a fix blocks of cells.

 

A code sample

We are trying to achieve the following :


Above, the original data ; Below the resulting data and formatting

 

VB code
'
' Excel workbook creation
'

Sub generate()

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

  Dim wbk As IXlsWorkbook
  Set wbk = engine.New("myfile.xls")

  Dim wksht001 As IXlsWorksheet
  Set wksht001 = wbk.AddWorksheet("Feuil1")

  '
  ' Worksheet "Feuil1"
  '

  wksht001.DefaultStyle = 16
  wksht001.Number(4,3) = 2003
  wksht001.Number(4,4) = 2004

  wksht001.Label(5,2) = "January"
  wksht001.Number(5,3) = 100
  wksht001.Number(5,4) = 105

  wksht001.Label(6,2) = "February"
  wksht001.Number(6,3) = 80
  wksht001.Number(6,4) = 120

  wksht001.Label(7,2) = "March"
  wksht001.Number(7,3) = 120
  wksht001.Number(7,4) = 140

  wksht001.Label(8,2) = "Total"
  wksht001.Formula(8,3) = "=SUM(R5C3:R7C3)"
  wksht001.Formula(8,4) = "=SUM(R5C4:R7C4)"

  '
  ' formatting
  '

  Dim range as IXlsRange
  Set range = wksht001.NewRange("R3C1:R7C3")
  range.Style.Borders.Color = colorBlue
  range.Style.Borders.Style = border_doubles
  range.Style.Borders.MidHorizontal.Color = RGB(170,0,0)
  range.Style.Borders.MidHorizontal.Style = border_dashed
  range.Style.Borders.MidVertical.Color = RGB(170,0,0)
  range.Style.Borders.MidVertical.Style = border_dashed
  range.Apply

  Dim range2 as IXlsRange
  Set range2 = wksht001.NewRange("R7C1:R7C3")
  range2.Style.Font.Bold = True
  range2.Style.Borders.Color = colorBlue
  range2.Style.Borders.Style = border_doubles
  range2.Apply


  '
  ' Excel workbook epilogue
  '

  wbk.Close

  Set engine = Nothing

End Sub

Function RGB(r, g, b) As Long
  RGB = r * 65536 + g * 256 + b
End Function
C# code
  //
  // Excel workbook creation
  //

  xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();
  IXlsWorkbook wbk = excel.New( @"myfile.xls" );
  IXlsWorksheet wksht001 = wbk.AddWorksheet( "Feuil1" );


  //
  // Worksheet "Feuil1"
  //

  wksht001.DefaultStyle = 16;
  wksht001.set_Number(4,3, 2003);
  wksht001.set_Number(4,4, 2004);

  wksht001.set_Label(5,2, "January");
  wksht001.set_Number(5,3, 100);
  wksht001.set_Number(5,4, 105);

  wksht001.set_Label(6,2, "February");
  wksht001.set_Number(6,3, 80);
  wksht001.set_Number(6,4, 120);

  wksht001.set_Label(7,2, "March");
  wksht001.set_Number(7,3, 120);
  wksht001.set_Number(7,4, 140);

  wksht001.set_Label(8,2, "Total");
  wksht001.set_Formula(8,3, "=SUM(R5C3:R7C3)");
  wksht001.set_Formula(8,4, "=SUM(R5C4:R7C4)");

  //
  // formatting
  //

  IXlsRange range = wksht001.NewRange("R3C1:R7C3");
  range.Style.Borders.Color = (int) xlsgen.enumColorPalette.colorBlue;
  range.Style.Borders.Style = xlsgen.enumBorderStyle.border_doubles;
  range.Style.Borders.MidHorizontal.Color = 0xAA0000;
  range.Style.Borders.MidHorizontal.Style = xlsgen.enumBorderStyle.border_dashed;
  range.Style.Borders.MidVertical.Color = 0xAA0000;
  range.Style.Borders.MidVertical.Style = xlsgen.enumBorderStyle.border_dashed;
  range.Apply();

  IXlsRange range2 = wksht001.NewRange("R7C1:R7C3");
  range2.Style.Font.Bold = 1;
  range2.Style.Borders.Color = (int) xlsgen.enumColorPalette.colorBlue;
  range2.Style.Borders.Style = xlsgen.enumBorderStyle.border_doubles;
  range2.Apply();

  //
  // Excel workbook epilogue
  //

  wbk.Close();
C++ code
{
  //
  // Excel workbook creation
  //

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

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->New( L"myfile.xls" );

  xlsgen::IXlsWorksheetPtr wksht001;
  wksht001 = wbk->AddWorksheet( L"Feuil1" );

  //
  // Worksheet "Feuil1"
  //

  wksht001->DefaultStyle = 16;
  wksht001->Number[4][3] = 2003;
  wksht001->Number[4][4] = 2004;

  wksht001->Label[5][2] = L"January";
  wksht001->Number[5][3] = 100;
  wksht001->Number[5][4] = 105;

  wksht001->Label[6][2] = L"February";
  wksht001->Number[6][3] = 80;
  wksht001->Number[6][4] = 120;

  wksht001->Label[7][2] = L"March";
  wksht001->Number[7][3] = 120;
  wksht001->Number[7][4] = 140;

  wksht001->Label[8][2] = L"Total";
  wksht001->Formula[8][3] = L"=SUM(R5C3:R7C3)";
  wksht001->Formula[8][4] = L"=SUM(R5C4:R7C4)";

  //
  // formatting
  //

  xlsgen::IXlsRangePtr range = wksht001->NewRange("R3C1:R7C3");
  range->Style->Borders->Color = xlsgen::colorBlue;
  range->Style->Borders->Style = xlsgen::border_doubles;
  range->Style->Borders->MidHorizontal->Color = 0x00AA0000;
  range->Style->Borders->MidHorizontal->Style = xlsgen::border_dashed;
  range->Style->Borders->MidVertical->Color = 0x00AA0000;
  range->Style->Borders->MidVertical->Style = xlsgen::border_dashed;
  range->Apply();

  xlsgen::IXlsRangePtr range2 = wksht001->NewRange("R7C1:R7C3");
  range2->Style->Font->Bold = TRUE;
  range2->Style->Borders->Color = xlsgen::colorBlue;
  range2->Style->Borders->Style = xlsgen::border_doubles;
  range2->Apply();

  //
  // Excel workbook epilogue
  //

  wbk->Close();

}

 

Enumerate named ranges

You can easily enumerate existing named ranges either for introspection purposes, or as a first step to make changes.

The workbook object exposes a NamedRanges property which in turn lets you navigate the collection of named ranges by index. You can retrieve any named range as a IXlsDynamicRange object, hence reuse it, or change its definition (a dynamic range has a name and an associated formula).

Here is how to enumerate ranges, here in C# :

C# code

  xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();
  IXlsWorkbook wbk = excel.Open( @"myfile.xls", "" );

  IXlsRanges ranges = wbk.NamedRanges;
  int nbNamedRanges = ranges.Count;
  for (int i = 1 ; i <= nbNamedRanges; i++)
  {
    IXlsDynamicRange r = ranges.get_DynamicRange(i);

    //...
  }

  wbk.Close();

 

Use built-in named ranges

The following built-in named ranges are available :

NameComment
builtinname_Consolidate_Area 
builtinname_Auto_Open 
builtinname_Auto_Close 
builtinname_Extract 
builtinname_Databasefor dataset queries
builtinname_Criteria 
builtinname_Print_Areafor print areas
builtinname_Print_Titlesfor repeat titles
builtinname_Recorder 
builtinname_Data_Form 
builtinname_Auto_Activate 
builtinname_Auto_Deactivate 
builtinname_Sheet_Title 
builtinname_Filter_Databasefor auto-filters

An example code using built-in named ranges is how to set repeat titles.

VB code
' repeat lines
Dim r As IXlsDynamicRange
r = wksht.NewDynamicRange("Print_Titles")
r.Formula = "R2C1:R4C256"
r.BuiltInNamedRange = xlsgen.builtinname_Print_Titles

' repeat columns
Dim r2 As IXlsDynamicRange
r2 = wksht.NewDynamicRange("Print_Columns")
r2.Formula = "R1C2:R65536C4"
r2.BuiltInNamedRange = xlsgen.builtinname_Print_Titles

' repeat lines and columns
Dim r3 As IXlsDynamicRange
r3 = wksht.NewDynamicRange("Print_Titles")
r3.Formula = "R2C1:R4C256;R1C2:R65536C4"
r3.BuiltInNamedRange = xlsgen.builtinname_Print_Titles
C/C++ code
// repeat lines
xlsgen::IXlsDynamicRangePtr r = wksht->NewDynamicRange(L"Print_Titles");
r->Formula = L"R2C1:R4C256";
r->BuiltInNamedRange = xlsgen::builtinname_Print_Titles;

// repeat columns
xlsgen::IXlsDynamicRangePtr r2 = wksht->NewDynamicRange(L"Print_Columns");
r2->Formula = L"R1C2:R65536C4";
r2->BuiltInNamedRange = xlsgen::builtinname_Print_Titles;

// repeat lines and columns
xlsgen::IXlsDynamicRangePtr r3 = wksht->NewDynamicRange(L"Print_Titles");
r3->Formula = L"R2C1:R4C256;R1C2:R65536C4";
r3->BuiltInNamedRange = xlsgen::builtinname_Print_Titles;

 

Reusing ranges

In the following, we'll discuss how ranges can be reused over and over again.

Updating the definition of a range is as possible as querying for the range, then assigning new range area values to it. That is enough to be used immediately, or to be committed to the workbook when xlsgen performs the actual save phase. There are two scenarios you may be interested in :

Update the range definition

In the following code, we simply update an existing range.

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

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("input\MyExistingFile.xls", "output\ResultingFile.xls")

' get the first worksheet
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)

' retrieve an existing range
Dim r As IXlsRange
Set r = wksht.Range("existingrange")

' update it
r.Range = "A1:B4"

' optionally, we could add more cell areas to this range
'   r.AddSubRange ("A6:B9")

' or we could have passed the areas in just one call
'   r.Range = "A1:B4;A6:B9"

wbk.Close

Set engine = Nothing

C# code
xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"input\MyExistingFile.xls", @"output\ResultingFile.xls" );

// get the first worksheet
IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);

// retrieve an existing range
IXlsRange r = wksht.get_Range("existingrange");

// update it
r.Range = "A1:B4";

// optionally, we could add more cell areas to this range
//   r.AddSubRange ("A6:B9");

// or we could have passed the areas in just one call
//   r.Range = "A1:B4;A6:B9";

wbk.Close();
excel = null;

C/C++ code

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

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"input\\MyExistingFile.xls", L"output\\ResultingFile.xls" );

  // get the first worksheet
  xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

  // retrieve an existing range
  xlsgen::IXlsRangePtr r = wksht->Range[L"existingrange"];

  // update it
  r->Range = L"A1:B4";

  // optionally, we could add more cell areas to this range
  //   r->AddSubRange (L"A6:B9");

  // or we could have passed the areas in just one call
  //   r->Range = L"A1:B4;A6:B9";

  wbk->Close();
}

 

Reuse a range to apply styles elsewhere

Reusing ranges is a convenient way to apply styles in areas without having to define them in the first place. Let's assume the following scenario, wehave the existing table style on the left, and we'd like to clone the styles to the right using the same exact table width and height. Then we'd like to clone the styles to another table whose width is twice as large. Here is what we want to accomplish :


What we want to accomplish

 


What we can accomplish by reusing existing ranges

 

And here is the code to accomplish just that :

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

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("input\MyExistingFile.xls", "output\ResultingFile.xls")

' get the first worksheet
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)

' update the range with an equivalent range
Dim range0001 As IXlsRange
Set range0001 = wksht.Range("complex")
range0001.Range = "F4:H8;F10:H11"
range0001.Apply

' update the range with a different range
'   both in terms of size, and amount of range areas
Dim range0002 As IXlsRange
Set range0002 = wksht.Range("complex")
range0002.Range = "B13:H20"
range0002.Apply

wbk.Close

Set engine = Nothing

C# code
xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"input\MyExistingFile.xls", @"output\ResultingFile.xls" );

// get the first worksheet
IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);

// update the range with an equivalent range
IXlsRange range0001 = wksht.get_Range("complex");
range0001.Range = "F4:H8;F10:H11";
range0001.Apply();

// update the range with a different range
//   both in terms of size, and amount of range areas
IXlsRange range0002 = wksht.get_Range("complex");
range0002.Range = "B13:H20";
range0002.Apply();

wbk.Close();
excel = null;

C/C++ code

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

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"input\\MyExistingFile.xls", L"output\\ResultingFile.xls" );

  // get the first worksheet
  xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

  // update the range with an equivalent range
  xlsgen::IXlsRangePtr range0001 = wksht->Range["complex"];
  range0001->Range = L"F4:H8;F10:H11";
  range0001->Apply();

  // update the range with a different range
  //   both in terms of size, and amount of range areas
  xlsgen::IXlsRangePtr range0002 = wksht->Range["complex"];
  range0002->Range = L"B13:H20";
  range0002->Apply();

  wbk->Close();
}

You can find this sample code in the samples directory.

 

Copy/Paste cells

It is possible to copy and paste cells the following ways :

 

Fill cells

Filling cells is an extension of paste where a source area gets duplicated to a larger area multiple times both horizontally and vertically, hence the fill.


Creating a range fill

As an example, you could have content at row 3 that according to some scenario needs to be duplicated in rows 6 to 8. If the paste/pasteTo function is used, this needs iteration over rows 6 to 8. But using fill, a single statement will do :

C/C++ code

worksheet->Rows[L"3:3"]->Range->FillTo(worksheet, worksheet->Rows[L"6:8"]->Range, xlsgen::pasteoption_valuesandformattingandformulas);

Another example is range C3:F4 that gets used in order to fill range B5:Z8 of another worksheet :

C/C++ code

worksheet->NewRange(L"C3:F4")->FillTo(worksheet2, worksheet2->NewRange(L"B5:Z8"), xlsgen::pasteoption_valuesandformattingandformulas);

 

Manipulating and converting range coordinates

There are conversion helpers in the programming interface meant to ease the conversion from the 3 ways to define rectangular areas : 4-uple (row1, col1) - (row2, col2) ; AXAX notation ; R1C1 notation.

Those converters are available at the worksheet level and at the range level.

At the worksheet level are the properties converting 2-uples (row, col) and 4-uples (row1, col1) - (row2, col2) to the other notation :

At the range level are the converters going in the other direction :

In the following sample code, we create a range using the AXAX notation from a 4-uple, and then retrieve the 4-uple coordinates from the range.

VB code

' convert an area to the AXAX notation
'   row1 = 1
'   col1 = 2
'   row2 = 15
'   col2 = 5
Dim s As String
s = wksht.AreaToRangeAXAX(1, 2, 15, 5)

' create a range using the AXAX notation
Dim r As IXlsRange
Set r = wksht.NewRange(s)

' retrieve the 4-uple coordinates
Dim top
Dim left
Dim right
Dim bottom
top = r.Top
left = r.Left
right = r.Right
bottom = r.Bottom

C# code

// convert an area to the AXAX notation
//   row1 = 1
//   col1 = 2
//   row2 = 15
//   col2 = 5
String s = wksht.get_AreaToRangeAXAX(1, 2, 15, 5);

// create a range using the AXAX notation
IXlsRange r = wksht.NewRange(s);

// retrieve the 4-uple coordinates
int top = r.Top;
int left = r.Left;
int right = r.Right;
int bottom = r.Bottom;

C++ code

// convert an area to the AXAX notation
//   row1 = 1
//   col1 = 2
//   row2 = 15
//   col2 = 5
BSTR s = wksht->AreaToRangeAXAX[1][2][15][5];

// create a range using the AXAX notation
xlsgen::IXlsRangePtr r = wksht->NewRange(s);

// retrieve the 4-uple coordinates
int top = r.Top;
int left = r.Left;
int right = r.Right;
int bottom = r.Bottom;

 

Shift cells


Shift cells (before on the left, after on the right)

xlsgen shifts cells if a client application deletes entire rows or entire columns. But the client application may need such shift for an arbitrary cell range. When this occurs, the client application must choose to ask xlsgen to fill the blank by shifting cells to the left, or shifting cells up.

Here is how it works :

worksheet.NewRange("D5:E7").ShiftCellsLeft();
or
worksheet.NewRange("D5:E7").ShiftCellsUp();

 

Computing the extent of ranges

What is the extent of ranges ? it's a way to name the idea that starting from a given cell in a worksheet, you can choose one of the directions (up, down, left, right) and ask yourself whether this new cell is empty or not, and then iterate through the process until you reach an empty cell. That's right, computing the extent of ranges is meant to compute the dimensions of a fictitious range of cells which share a common attribute : non-emptiness.

This can be useful when you are manipulating ranges, that's why this is explained here. But actually the method exposure is elsewhere, in the row and column objects. After all, the row and column objects help map the idea of horizontal extent and vertical extent. For more information, see working with rows and columns.

 

Conditional formattings

Conditional formattings can be triggered on one or more cell areas, which provides a convenient way to get alerts based on any of the cells. When creating a range-based conditional formatting, the Apply() statement used for range styling is not required. For more information and a code sample, see conditional formatting.

 

Merged Cells

Merged Cells can be set from one or more cell areas. For more information, see merged cells.

 

Outline

Outline can be set from one or more cell areas. For more information, see outline.

 

Auto-filters

Auto-filters can be set from one or more cell areas. For more information, see auto-filters.

 

Sorting data

Sorting data is an important tool in the analysis arsenal. Here is how any range can be sorted, in C/C++ :

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

// 3 = column C
// TRUE = ascending
sort->PrimaryKey(3, TRUE);

sort->Apply();

Sorting can be performed by xlsgen a number of ways :

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

 

Dynamic named ranges

Dynamic named ranges are the most general form of named ranges. By definition, a dynamic named range is a formula associated with a name. This name can in turn be used in other formulas or in many places where Excel expects ranges such as a chart data source. Regular named ranges are a special case of dynamic named ranges where the cell areas are known and static. Regular named ranges appear in the Excel user interface in the top-left dropdown. Dynamic named ranges, however, appear in the Insert / Name / Define dialog box, and only also appear in the regular Excel dropdown if they happen to store static cell areas. Dynamic named ranges are a very convenient way to keep a changing logic behind a name, and use this name in other Excel objects : this insulation layer allows for example to update/refine a chart data source without editing the chart properties.


IXlsDynamicRange dynrange = worksheet.NewDynamicRange("r2")
dynrange.Formula = "=OFFSET($E$4:$G$6;1;0;1;1)"

In the installed samples, sample called charts_dynamic_datasource show how to use a dynamic named range with a chart.

Anecdotically, dynamic named ranges, because of their nature, can also be used to store formula portions, either to make a big formula easier to read by chunking part of it elsewhere, or to allow to overcome an hardcoded limit in Excel : formulas cannot hold more than 1024 characters. By storing chunks of a given formula in other places, the formula can be much more concise.

Excel recognizes built-in named ranges, for instance the print area of a worksheet. If you go in Excel, and select File / Print Area / Set Print Area, then you'll see a new entry in the names drop down called "Print_Area". There are a number of other built-in named ranges. Those are available in xlsgen using the special enumBuiltInNamedRange enumeration. And here is an example of a built-in "Database" named range that can be used in order to set up an Excel file so that it can be queried as an ODBC data source :


IXlsDynamicRange dynrange = worksheet.NewDynamicRange("DATABASE")
dynrange.Formula = "=$A$1:$AT$380"
dynrange.BuiltInNamedRange = xlsgen.builtinname_Database

 

Delete a named range or a dynamic named range

You can delete a named range or a dynamic named range at any point in time. xlsgen will update the associated formula references accordingly, essentially by flagging them with a named range error.

 

Transpose a range


Transpose a range (turn table)

Sometimes, data arranged as a cross table may be more useful with rows and columns turn upside down. Transpose is the mathematical name for such transform, and it is available in xlsgen. It transposes the data, formatting, formulas, merged cells and more. An optional parameter lets you choose to transpose values only, value and formatting, or everything.

The method also returns the transposed areas, for your convenience. For instance, if you transpose range D2:I10, a range 6-column wide and 9-row tall, the transposed area is D2:L7, 9-column wide and 6-row tall.

Transposing a range is a conservative function, therefore transposing a range twice returns to the initial situation.

C++ code

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

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

wksht->NewRange(L"C2:H10")->Transpose(xlsgen::pasteoption_valuesandformattingandformulas);

wbk->Close();

 

Auto-filling a range with data


Auto-filling a range with data (two examples, one with formulas, another with numbers)

Auto-filling a range in xlsgen is the programmatic way by which it is possible to drag a selection in Excel vertically or horizontally and see the extension filled with data relevant to the data in the selection.

Formulas are replicated (the cell references are updated accordingly). Strings are replicated as is. Numbers are replicated by taking into account the difference between numbers in the data selection. The formatting is replicated.

The selection is defined by its depth. The depth means the height of the selection in case the fill is vertical. And the depth means the width in case the fill is horizontal. It is therefore possible to choose to auto-fill vertically or horizontally.

In the examples above, auto-fill is used for replicating formulas vertically, and numbers vertically. Since there is only one formula in the selection, the depth is set to 1. In the other example, numbers are replicated. The selection has three numbers, which implies the depth is set to 3. If the depth was set to 1 or 2, the result would be entirely different. Indeed the selection depth is used to infer the differences between numbers. The algorithm is always linear.

C++ code

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

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

// auto-fill vertically our formula in the selection (see example above)
wksht->NewRange(L"C3:C11")->AutoFill(1);

wbk->Close();

 

Formula-based selection

Sometimes the range where a particular action has to be applied is complex and ruled by the content of cells. xlsgen has a special capability known as formula-based selection, which allows to build a range based on a formula. For instance, a range can be built for all cells whose value is greater than 20. The code sample below does exactly that.


Formula-based selection, here values > 20
Java code

// build a range of cells governed by value > 20
// notice the use of relative cell references in : F2 > 20
XlsRange r = wksht.NewRange("F2:F14").SelectByFormula("F2 > 20");

// returns s = R2C6:R3C6;R9C6:R11C6
String s = r.getRange();

// apply color to those cells
r.getStyle().getPattern().putPattern(xlsgen.pattern_solid);
r.getStyle().getPattern().putBackgroundColor(0xFF0000);

r.Apply();

VB code

' build a range of cells governed by value > 20
' notice the use of relative cell references in : F2 > 20
Dim r As IXlsRange 
Set r = wksht.NewRange("F2:F14").SelectByFormula("F2 > 20")

' returns s = R2C6:R3C6;R9C6:R11C6
Dim s As String
Set s = r.Range

' apply color to those cells
r.Style.Pattern.Pattern = enumPattern.pattern_solid
r.Style.Pattern.BackgroundColor = &HFF0000

r.Apply

C# code

// build a range of cells governed by value > 20
// notice the use of relative cell references in : F2 > 20
IXlsRange r = wksht.NewRange("F2:F14").SelectByFormula("F2 > 20");

// returns s = R2C6:R3C6;R9C6:R11C6
String s = r.Range;

// apply color to those cells
r.Style.Pattern.Pattern = enumPattern.pattern_solid;
r.Style.Pattern.BackgroundColor = 0xFF0000;

r.Apply();

C++ code

// build a range of cells governed by value > 20
// notice the use of relative cell references in : F2 > 20
xlsgen::IXlsRangePtr r = wksht->NewRange(L"F2:F14")->SelectByFormula(L"F2 > 20");

// returns s = R2C6:R3C6;R9C6:R11C6
_bstr_t s = r->Range;

// apply color to those cells
r->Style->Pattern->Pattern = xlsgen::pattern_solid;
r->Style->Pattern->BackgroundColor = 0xFF0000;

r->Apply();

 

Auto-adjusting named ranges

A common frustration with named ranges is that they don't get automatically redefined whenever data is refreshed. That is, if a named range encloses 10 rows, and the data in those rows gets refreshed, the spreadsheet may end up with say 15 rows, with the named range still enclosing the 10 rows. Hence the gap. What xlsgen allows to do is to automatically update the definition of named ranges according to how the data grows.

In the example below, on the left side of the capture, we initially have a bunch of rows and a named range myrange. Then when the data is updated, a new row is added (Product13). Thanks to xlsgen's built-in auto-adjusting named ranges enabled, the named range myrange gets automatically updated, on the right side of the capture below.


An example of auto-adjusting named ranges

Here is how to do it :

Java code

XlsWorksheet wksht = wbk.getWorksheetByIndex(1);

// will return areaBeforeUpdate = R2C1:R14C5
String areaBeforeUpdate = wksht.getRange("myrange").getRange();

// enable auto-adjusting of named ranges
wbk.putAutoAdjustDefinedNames(true);

// insert a new row of data
wksht.putLabel(15, 1, "Product13");

// will return areaAfterUpdate = R2C1:R15C5, notice the change
String areaAfterUpdate = wksht.getRange("myrange").getRange();

VB code

Dim wksht As IXlsWorksheet 
Set wksht = wbk.WorksheetByIndex(1)

' will return areaBeforeUpdate = R2C1:R14C5
Dim areaBeforeUpdate As Str
areaBeforeUpdate = wksht.Range("myrange").Range

' enable auto-adjusting of named ranges
wbk.AutoAdjustDefinedNames = True

' insert a new row of data
wksht.Label(15, 1) = "Product13"

' will return areaAfterUpdate = R2C1:R15C5, notice the change
Dim areaAfterUpdate As Str
areaAfterUpdate = wksht.Range("myrange").Range

C# code

IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);

// will return areaBeforeUpdate = R2C1:R14C5
String areaBeforeUpdate = wksht.Range("myrange").Range;

// enable auto-adjusting of named ranges
wbk.AutoAdjustDefinedNames = true;

// insert a new row of data
wksht.set_Label(15, 1, "Product13");

// will return areaAfterUpdate = R2C1:R15C5, notice the change
String areaAfterUpdate = wksht.Range("myrange").Range;

C++ code

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

// will return areaBeforeUpdate = R2C1:R14C5
_bstr_t areaBeforeUpdate = wksht->Range[L"myrange"]->Range;

// enable auto-adjusting of named ranges
wbk->AutoAdjustDefinedNames = TRUE;

// insert a new row of data
wksht->Label[15][1] = L"Product13";

// will return areaAfterUpdate = R2C1:R15C5, notice the change
_bstr_t areaAfterUpdate = wksht->Range[L"myrange"]->Range;

 

Remove duplicate values


Remove duplicate values

Remove duplicate values is a feature in xlsgen which makes it possible to clean up data in a list, which is often a step before further analysis (sort, filter, ...). Duplicate values are replaced by blanks. Blanks are written at the end of the range.

Here is how to do it :

Java code
workbook.getWorksheetByIndex(1).NewRange("B2:B12").RemoveDuplicates();
VB code
workbook.WorksheetByIndex(1).NewRange("B2:B12").RemoveDuplicates
C# code
workbook.get_WorksheetByIndex(1).NewRange("B2:B12").RemoveDuplicates();
C++ code
workbook->WorksheetByIndex[1]->NewRange(L"B2:B12")->RemoveDuplicates();

 

A big use case is obviously when multiple columns are involved, as in the example below :


Remove duplicate values : multiple columns

 

Java code
workbook.getWorksheetByIndex(1).NewRange("F19:K23").RemoveDuplicates();
VB code
workbook.WorksheetByIndex(1).NewRange("F19:K23").RemoveDuplicates
C# code
workbook.get_WorksheetByIndex(1).NewRange("F19:K23").RemoveDuplicates();
C++ code
workbook->WorksheetByIndex[1]->NewRange(L"F19:K23")->RemoveDuplicates();

 

Clear contents

Clear contents removes all data and formulas while leaving all the formatting intact.


Clear contents removes data and formulas, not the formatting

 

Java code
workbook.getWorksheetByIndex(1).NewRange("B2:G10").ClearContents();
VB code
workbook.WorksheetByIndex(1).NewRange("B2:G10").ClearContents
C# code
workbook.get_WorksheetByIndex(1).NewRange("B2:G10").ClearContents();
C++ code
workbook->WorksheetByIndex[1]->NewRange(L"B2:G10")->ClearContents();

 

Clear formats

Clear formats removes all formatting, while leaving data and formulas intact. It's the opposite of Clear contents.


Clear formats removes the formatting, not data and formulas

 

Java code
workbook.getWorksheetByIndex(1).NewRange("F4:J9").ClearFormats();
VB code
workbook.WorksheetByIndex(1).NewRange("F4:J9").ClearFormats
C# code
workbook.get_WorksheetByIndex(1).NewRange("F4:J9").ClearFormats();
C++ code
workbook->WorksheetByIndex[1]->NewRange(L"F4:J9")->ClearFormats();

 

Range value iterator

Iterating over values in a range is a powerful mechanism. xlsgen has an interface, IXlsDynamicRangeValueIterator, to do just that.

Iterating over values works regardless the complexity of the range, i.e. an arbitrary formula, a union of cell areas, ...

It works like this :

C++ code

  xlsgen::IXlsDynamicRangePtr r = ranges->DynamicRange[i];

  xlsgen::IXlsDynamicRangeValueIteratorPtr iter = r->ValueIterator;

  while (!iter->IsLast)
  {
    int worksheetId = iter->ValueWorksheet;
    int row = iter->ValueRow;
    int col = iter->ValueColumn;

    _bstr_t s = workbook->WorksheetByIndex[worksheetId]->FormattedLabel[row][col];

    iter->Next();
  }

 

Creating named ranges from headers in a range

Let's consider the following data range :

 ABCDE
1     
2     
3 TurnOverApplesPearsBananas
4 Europe10008001200
5 Asia1200400750
6 America170012001900

By doing NewRange("B3:E6").CreateNames(true, true, false, false), xlsgen automatically creates the following named ranges based on the data range headers and columns :

 

Get the used cell range

Java code
workbook.getWorksheetByIndex(1).getUsedRange().ClearFormats();
VB code
workbook.WorksheetByIndex(1).UsedRange.ClearFormats
C# code
workbook.get_WorksheetByIndex(1).UsedRange.ClearFormats();
C++ code
workbook->WorksheetByIndex[1]->UsedRange->ClearFormats();

 

Special cells range


Special cells in Excel (Ctrl+G and then Special)

The application interface for special cells in xlsgen is the programmatic equivalent of the special cells user interface dialog in Excel, raised from Ctrl+G and then the Special button.

Special cells is a range of cells, single area or a union of areas, that meets a certain criteria. One example, is cells carrying a formula. Another is cells with number literals. The conditions are one of these :

(*) : If the chosen condition is either constants or formulas, then the application may filter according to the literal data type, one or more of the following :

Special cells is exposed as a range property, so an existing range must be specified to limit the bounding area where the special cells filtering applies. If the client application knows no such area, it can pass either A1 as range or use the UsedRange which is the area of the current worksheet that encompasses all content and formulas. Here is an example :

Java code
// find cells with a formula, turn them red
XlsRange r = workbook.getWorksheetByIndex(1).getUsedRange().getSpecialCells(xlsgen.specialcells_type_formulas, xlsgen.specialcells_value_na);
r.getStyle().getPattern().putBackgroundColor(0xFF0000);
r.Apply();

VB code
' find cells with a formula, turn them red
Dim r As IXlsRange
Set r = workbook.WorksheetByIndex(1).UsedRange.SpecialCells(specialcells_type_formulas, specialcells_value_na)
r.Style.Pattern.BackgroundColor = &HFF0000
r.Apply
C# code
// find cells with a formula, turn them red
IXlsRange r = workbook.get_WorksheetByIndex(1).UsedRange.get_SpecialCells(enumSpecialCellsType.specialcells_type_formulas, enumSpecialCellsValue.specialcells_value_na);
r.Style.Pattern.BackgroundColor = 0xFF0000;
r.Apply();
C++ code
// find cells with a formula, turn them red
xlsgen::IXlsRangePtr r = workbook->WorksheetByIndex[1]->UsedRange->SpecialCells[xlsgen::specialcells_type_formulas][xlsgen::specialcells_value_na];
r->Style->Pattern->BackgroundColor = 0xFF0000;
r->Apply();

 

xlsgen documentation. © ARsT Design all rights reserved.