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
Fill cells
Shift cells
Value setting
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.
Restrict the scope to a single worksheet instead of all worksheets (Local property)
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
Erase all data in a range (data + formatting)
AutoFit all data in a range
Calculate formulas within a range
Delete formulas within a range
Transpose a range (turn table)
Auto-filling with data
Select by formula
Auto-adjusting named ranges
Remove duplicate values
Clear contents
Clear formats
Search and replace
Iterate over values
Create a table
Create named ranges from headers in a range
Get the used cell range
Retrieve special cells
Work with protected 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 :

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

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

copy/paste to another workbook. Example : worksheet.NewRange("C1:E3").PasteTo(destworksheet, 2, 1, xlsgen::pasteoption_valuesandformattingandformulas)
copy/paste a specific area of data and formatting to the Windows clipboard. Example : worksheet.NewRange("C1:E3").CopyToClipboard()

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 :

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;

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

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 :

on values
on cell color : background cell color or font color
on associated icon (related to icon-based conditional formatting)

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 :

	A	B	C	D	E
1
2
3		TurnOver	Apples	Pears	Bananas
4		Europe	1000	800	1200
5		Asia	1200	400	750
6		America	1700	1200	1900

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 :

Apples = C4:C6
Pears = D4:D6
Bananas = E4:E6
Europe = C4:E4
Asia = C5:E5
America = C6:E6
TurnOver = C4:E6

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 :

specialcells_type_allformatconditions : cells with conditional formatting(s)
specialcells_type_allvalidation : cells with data validation(s)
specialcells_type_blanks : blank (empty) cells
specialcells_type_comments : cells with comment notes
specialcells_type_constants : cells with non-formula values (*)
specialcells_type_formulas : cells carrying formulas (*)
specialcells_type_lastcell : last cell of the range (bottom right corner)
specialcells_type_sameformatconditions : cells with the same conditional formatting
specialcells_type_samevalidation: cells with the same data validation
specialcells_type_visible : visible cells (neither row nor column are hidden)

(*) : 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 :

specialcells_value_na : not applicable (no filter)
specialcells_value_error : cells with formula errors
specialcells_value_logical : cells with a logical (boolean) value
specialcells_value_number : cells with numbers (integer, float, date)
specialcells_value_text : cells with text

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