Ranges

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

Decouple data and formatting. The principal use of ranges is to apply formatting to blocks of cells. In some scenarios, the first pass of generating Excel consists in adding all the data with a default formatting style, and the second pass consists in applying the formatting on that data.
Productivity gain : in a sample scenario, 84 lines of code have been replaced with 6 lines.
Promote reusability : use ranges logically.
Combine formatting rather than a brutal formatting replacement. Borders, fonts, ... can be combined, that's one of the key uses of ranges.
Combine ranges : supports single or multiple cell areas.
Copy/Paste cells
Use the description form that suits you best. For example, both are accepted : R1C1:R3C5, A1:B5, or use (1,1,3,5) 4-uples.
Enumerate named ranges
Use Built-in named ranges
Reuse existing ranges : get/edit existing ranges.
Create a conditional formatting onto one or more cell areas.
Create merged cells, or outline onto one or more cell areas.
Create auto-filters onto one or more cell areas.
Sort data
Create dynamic named ranges
Delete a named range, or a dynamic named range

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.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();
  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.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();
  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 :

Name	Comment
builtinname_Consolidate_Area
builtinname_Auto_Open
builtinname_Auto_Close
builtinname_Extract
builtinname_Database	for dataset queries
builtinname_Criteria
builtinname_Print_Area	for print areas
builtinname_Print_Titles	for repeat titles
builtinname_Recorder
builtinname_Data_Form
builtinname_Auto_Activate
builtinname_Auto_Deactivate
builtinname_Sheet_Title
builtinname_Filter_Database	for 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 and see it have an effect on Excel objects depending on that range
update the range definition and use it, for instance to apply a style of an existing range of cells somewhere else.

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.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

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.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

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 :

import an entire worksheet from an external workbook. See DuplicateFrom.
import an entire worksheet from the current workbook. See Duplicate.

copy/paste specific areas. The areas can be in the same or another worksheet. Copy/paste options include : values only, values and formatting, values and formulas, values and formatting and formulas.

C/C++ code


// open an existing workbook that has a worksheet called "Sheet1"

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

// create a new worksheet

xlsgen::IXlsWorksheetPtr wksht2;
wksht2 = wbk->AddWorksheet( L"Sheet2" );

// create a range whose definition references "Sheet1"

xlsgen::IXlsRangePtr range;
range = wksht2->NewRange("Sheet1!B3:I7");

// paste the B3:I7 area into an area of "Sheet2" whose top-left corner is row 7, column 4

range->Paste(7, 4, xlsgen::pasteoption_valuesandformattingandformulas);

wbk->Close();

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 :

CellToRangeR1C1([in]int row, [in]int col, [out, retval]BSTR* cellrange);
CellToRangeAX([in]int row, [in]int col, [out, retval]BSTR* cellrange);
AreaToRangeR1C1([in]int row1, [in]int col1, [in]int row2, [in]int col2, [out, retval]BSTR* cellrange);
AreaToRangeAXAX([in]int row1, [in]int col1, [in]int row2, [in]int col2, [out, retval]BSTR* cellrange);

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

Top([out, retval]int* row);
Left([out, retval]int* col);
Right([out, retval]int* col);
Bottom([out, retval]int* row);

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;

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

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.