Cells

xlsgen > overview > Where to start with cells?

Cells are accessible from multiple places :

the current worksheet. See the worksheet object and the IXlsWorksheet interface.
This is the main and preferred method to access cells content either on a cell-by-cell basis, or whenever large blocks of data need to be put in cells. The programming language you are using (C++, VB, ...) provides the necessary iteration keywords required for that matter (for loops, while loops, ...).
part of merged cells. See the merged cells object and the IXlsMergedCells interface.
part of a richly formatted cell. See the rich formatting object and the IXlsRichLabel interface.
cells can be read or updated.
formulas can be read, created, updated and calculated.
cells data type and metadata can be retrieved. For more information, see here.
cells can be copied across worksheets using regular copy/paste functionalities. Values, formatting and formulas are copied accordingly.
cells can be written using Html markup.
cells can be floating. See the IXlsTextBox interface.

What kind of content can be put in cells?

Cells contain data and are associated several optional additional information including :

this data can be an integer number, a floating-point number, a date/time, or a label
the content of a cell can be retrieved as is, or formatted according to its associated number format. For instance, if you store number 3 in a cell where the number format is "# ###,00", then using the Number property in that cell returns 3, using the Label property in that cell returns "3", using the FormattedLabel property returns "3,00".
a formula that will be automatically calculated whenever the Excel document is open, or on-demand with xlsgen.

Cell E7 contains 410.00 €, which is calculated dynamically at run-time as a result of the formula =SUM(E4:E6). The formula is displayed by bringing the formula bar in.
formatting which includes all the usual cell property tabs : number format, alignment, font, border and pattern.
rich formatting, which is the opportunity to associate multiple font styles within the same cell

How to put content in cells with code?

Make sure to have a current worksheet to work on (wksht). If you don't know how to do this, read the get started section.

In the code below, we put a label and number in columns C9 and C10. Code snippets are provided for each language.

VB code

wksht.Label(9, 3) = "Avril" ' put Avril in C9
wksht.Number(10, 3) = 10 ' put 10 in C10
wksht.Date(4, 3) = "Jan, 01 2006" ' put Date in C4

C# code

wksht.set_Label(9,3, "Avril"); // put Avril in C9
wksht.set_Number(10,3, 10); // put 10 in C10
wksht.set_Date(4, 3, "Jan, 01 2006"); // put Date in C4

C/C++ code

wksht->Label[9][3] = L"Avril"; // put Avril in C9
wksht->Number[10][3] = 10; // put 10 in C10
wksht->Date[4][3] = L"Jan, 01 2006"; // put Date in C4

Regardless the programming language, cell references always start at 1.

The process can be iterated. For instance, to fill 50 rows of content that changes, you can use the following code :

VB code

Dim i
For i = 1 To 50 
  wksht.Number(9 + i, 3) = 10 + i ' put 10 + i in cells
Next

C# code

for (int i = 1; i < 50; i++)
  wksht.set_Number(9 + i, 3, 10 + i); // put 10 + i in cells

C/C++ code

for (int i = 1; i < 50; i++)
  wksht->Number[9 + i][3] = 10 + i; // put 10 + i in cells

How to read and write date/time values

Date/time values play a special role because of their nature. xlsgen uses the user regional settings (Windows control panel) to convert date/time values back and forth into numbers, i.e. which is how date/time values are stored internally. In addition to this, for date/time values to be displayed correctly in Excel, cells must be associated to an appropriate number format. In the example below, we add a date, and then read it back.

VB code

Dim s As IXlsStyle 
Set s = wksht.NewStyle
s.Format = "d mmm yyyy"
s.Apply

wksht.Date(4,3) = "1 Jan 2006"

C# code

IXlsStyle s = wksht.NewStyle();
s.Format = "d mmm yyyy";
s.Apply();

wksht.set_Date(4,3, "1 Jan 2006");

// you can also use the native .NET date type
DateTime d = DateTime.Parse("1 Jan 2006");
wksht.set_Float(5,3, d.ToOADate());

C/C++ code

xlsgen::IXlsStylePtr s = wksht->NewStyle();
s->Format = L"d mmm yyyy";
s->Apply();

wksht->Date[4][3] = L"1 Jan 2006";

The ability to read and re-write cell values opens a lot of scenarios. Using xlsgen, you can read the content of cells of an existing Excel workbook, and you may reassign new values to those cells. You can also do introspection like knowing whether or not the cell stores a formula.

More than cell values, you can extract formulas, styles, as well as existing ranges.

Extract cell values to perform consistent updates of the data

Reusing cell values is useful in a bunch of scenarios. xlsgen allows exactly that, it can extract strings, numbers floats, and date/time values from any cell.

In a typical scenario, the user would extract the number in some cell, do some processing with it, and put the resulting value back in that cell. The code below shows exactly that. In this example, the numbers are multiplied by 2 :

The original file, and the resulting file

VB code

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

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

' extract the values of cell B3, B4 and B5
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)

MsgBox "Content of cell B3 is : " & wksht.Label(3,2)
MsgBox "Content of cell B4 is : " & wksht.Number(4,2)
MsgBox "Content of cell B5 is : " & wksht.Float(5,2)
MsgBox "Content of cell B6 is : " & wksht.Date(6,2)

' do some processing, and put the values back
wksht.Number(4, 2) = wksht.Number(4, 2) * 2
wksht.Float(5, 2) = wksht.Float(5, 2) * 2

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

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

// extract the values of cell B3, B4 and B5
IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);

MessageBox.Show( "Content of cell B3 is : " + wksht.get_Label(3,2) );
MessageBox.Show( "Content of cell B4 is : " + wksht.get_Number(4,2) );
MessageBox.Show( "Content of cell B5 is : " + wksht.get_Float(5,2) );
MessageBox.Show( "Content of cell B6 is : " + wksht.get_Date(6,2) );

// do some processing, and put the values back
wksht.set_Number(4,2, wksht.get_Number(4, 2) * 2 );
wksht.set_Float(5,2, wksht.get_Float(5, 2) * 2 );

wbk.Close();
excel = null;

C/C++ code

#include <atlbase.h>

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

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

  // extract the values of cell B3, B4 and B5
  xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

  BSTR s = wksht->Label[3][2];
  ::MessageBox(NULL, OLE2T(s), "Content of cell B3 is : ", MB_OK);
  ::SysFreeString(s);

  char tmp[32];
  int n = wksht->Number[4][2];
  sprintf(tmp, "%d", n);
  ::MessageBox(NULL, tmp, "Content of cell B4 is : ", MB_OK);

  double d = wksht->Float[5][2];
  sprintf(tmp, "%f", d);
  ::MessageBox(NULL, tmp, "Content of cell B5 is : ", MB_OK);

  BSTR s_date = wksht->Date[6][2];
  ::MessageBox(NULL, tmp, "Content of cell B6 is : ", MB_OK);
  ::SysFreeString(s_date);

  // do some processing, and put the values back
  wksht->Number[4][2] = wksht->Number[4][2] * 2;
  wksht->Float[5][2] = wksht->Float[5][2] * 2;

  wbk->Close();
}

Default cell values are : empty string when requesting the cell value as a string (Label), 0 when requesting the cell value as a number (Number) and 0.0 when requesting the cell value as a float (Float).

Reuse existing ranges

A template file is not only made of worksheets, it can have one or more named ranges. Those named ranges can be reused. One scenario reuses the cell range defined along with each named range. Another scenario updates the definition of that cell range. For a more comprehensive explanation of ranges, see the following showcase. Ranges are a very productive way to apply formatting to cells in a single statement. The following code both reuses an existing range :

The original file

And the resulting file

VB code

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

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

' extract range "myrange"
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)
Dim myrange As IXlsRange
Set myrange = wksht.Range("myrange")
' redefine the boundaries, and change the background color
myrange.Range = L"R7C1:R7C6"
myrange.Style.Pattern.BackgroundColor = &HFFFF00
myrange.Apply()

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

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

// extract range "myrange"
IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);
IXlsRange myrange = wksht.get_Range("myrange");
if (myrange != null)
{
  myrange.Range = L"R7C1:R7C6"; // redefine the range boundaries
  // and change the background color
  myrange.Style.Pattern.BackgroundColor = 0xFFFF00;
  myrange.Apply();
}

wbk.Close();
excel = null;

C/C++ code

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

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

  // extract range "myrange"
  xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];
  xlsgen::IXlsRangePtr myrange = wksht->Range[L"myrange"];
  if (myrange)
  {
    // redefine the range boundaries
    myrange->Range = L"R7C1:R7C6"; 
    // and change the background color
    myrange->Style->Pattern->BackgroundColor = 0xFFFF00;
    myrange->Apply();
  }

  wbk->Close();
}

More range capabilities are explained here.

Promote formatting reuse by extracting a style from an existing worksheet

Since adding styles and formatting to cells can be a daunting and time-consuming task, whether interactively or using code, it's actually much more productive to be able to reuse existing styles as much as possible. Reusing styles programmatically is pretty much like the ApplyStyle toolbar shortcut that allows to apply the current selection formatting onto another selection.

In the context of templates, it's possible to extract an existing style given a cell (row, column) coordinate pair. The code below shows how it works :

The original file, and the resulting file

VB code

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

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

' extract the existing style in cell B3
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)

Dim style As IXlsStyle
Set style = wksht.StyleFromLocation(3, 2) ' cell B3
style.Apply

' add content using this style
wksht.Label(4, 4) = "data1"
wksht.Label(5, 4) = "data2"
wksht.Label(6, 4) = "data3"

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

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

// extract the existing style in cell B3
IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);

IXlsStyle style = wksht.get_StyleFromLocation(3, 2); // cell B3
style.Apply();

// add content using this style
wksht.set_Label(4,4, "data1");
wksht.set_Label(5,4, "data2");
wksht.set_Label(6,4, "data3");

wbk.Close();
excel = null;

C/C++ code

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

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

  // extract the existing style in cell B3
  xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

  xlsgen::IXlsStylePtr style = wksht->StyleFromLocation[3][2]; // cell B3
  style->Apply();

  // add content using this style
  wksht->Label[4][4] = L"data1";
  wksht->Label[5][4] = L"data2";
  wksht->Label[6][4] = L"data3";

  wbk->Close();
}

Copy/Paste cells

There are two ways to Copy and Paste cells using the xlsgen object model. The first one is explicit, and works on a cell by cell basis. The other defers the bulk of the work to xlsgen. This mechanism works across worksheets of the same workbook or not. For instance, using VB :

VB code


' Let's assume we have two worksheets in our workbook

' Dim worksheet1 As IXlsWorksheet
' Dim worksheet2 As IXlsWorksheet

' Copy the content of cell [3][9] elsewhere

worksheet2.Label(3,4) = worksheet1.Label(3,9)

To copy/paste cells without worrying about how many cells to copy/paste, and without worrying what is the actual data being stored, please consider the powerful range-based copy/paste method that not only does all the work for you, but also allows to set paste options such as copying only the values not the formulas.

Introspection

Finding whether a cell contains a formula

If you want to know whether a given cell stores a formula, you can use the following statement, here in VB :

...
  ' Visual Basic code follows 

  If worksheet.ContainsFormula(5,3) = True Then
    MsgBox "This cell contains a formula"
  Else
    MsgBox "This cell does not contain a formula"
  End If
...

See the IXlsWorksheet interface.

Finding whether a cell is part of a merged cell grouping

...
  ' Visual Basic code follows 

  If Not worksheet.MergedCells(5,3) is Nothing Then
    MsgBox "This cell is part of a merged cell grouping"
  Else
    MsgBox "This cell is not part of a merged cell grouping"
  End If
...

See the IXlsWorksheet interface and then IXlsMergedCells interface.

Retrieving the data type of a cell

...
  ' Visual Basic code follows 

  int datatype = worksheet.CellType(5,3)
  ' returns an enumerated type whose value is one of
  '   datatype_notapplicable   = 0,
  '   datatype_number          = 1,
  '   datatype_float           = 2,
  '   datatype_double          = 3,
  '   datatype_date            = 4,
  '   datatype_time            = 5,
  '   datatype_datetime        = 6,
  '   datatype_string          = 7
  '   datatype_boolean         = 8,
  '   datatype_error           = 9

...

See the enumDataType enumeration.

Retrieving the metadata type of a cell

...
  ' Visual Basic code follows 

  int metadatatype = worksheet.CellMetadataType(5,3)
  ' returns an enumerated type whose value is a bit-wise combination of
  '    metadatatype_notapplicable  = 0,
  '    metadatatype_hyperlink      = 1,
  '    metadatatype_formula        = 2
  '    metadatatype_mergedcells    = 4,
  '    metadatatype_datavalidation = 8
...

The metadata types are logically combined, i.e. a cell can be an hyperlink and part of merged cells at the same time. See the enumMetadataType enumeration.