xlsgen > overview > Merged cells

Merged cells are a special formatting applied to cells, in the sense that they are the equivalent of a single cell that would be spread horizontally along an arbitrary amount of columns, or vertically along an arbitrary amount of rows, or both.

Because of the nature of merged cells, additional alignment options are often used to place that content somewhere inside.

Using xlsgen's object model, you can :

 


 

Merging cells

To produce the content from the screen above, you need the following code :

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

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

Dim wksht As IXlsWorksheet
Set wksht = wbk.AddWorksheet("sheet1")

' create a style (borders, alignment)

Dim style As IXlsStyle
Set style = wksht.NewStyle

style.Alignment.Vertical = valign_bottom
style.Alignment.Horizontal = halign_center
style.Borders.Color = colorBlack
style.Borders.Style = border_thin

style.Apply

' write first merged cells

wksht.Number(3,2) = 5

Dim range1 As IXlsRange
Set range1 = wksht.NewRange("R3C2:R5C2")
Dim mc1 As IXlsMergedCells
Set mc1 = range1.NewMergedCells


' write second merged cells

Dim style2 As IXlsStyle
Set style2 = style.Duplicate

style2.Alignment.Horizontal = halign_right

style2.Apply

wksht.Number(3,4) = 5

Dim range2 As IXlsRange
Set range2 = wksht.NewRange("R3C4:R3C6")
Dim mc2 As IXlsMergedCells
Set mc2 = range2.NewMergedCells


' write third merged cells

Dim style3 As IXlsStyle
Set style3 = style.Duplicate

style3.Alignment.Horizontal = halign_center
style3.Alignment.Vertical = valign_bottom

style3.Apply

wksht.Number(5,4) = 5

Dim range3 As IXlsRange
Set range3 = wksht.NewRange("R5C4:R8C6")
Dim mc3 As IXlsMergedCells
Set mc3 = range3.NewMergedCells


wbk.Close

Set engine = Nothing
C# code

// create a running instance of the generator
xlsgen.CoXlsEngine engine = new xlsgen.CoXlsEngine();

// create a new Excel file, and retrieve a workbook to work with
IXlsWorkbook wbk = engine.New( @"c:\myfile.xls" );

// create a new worksheet
IXlsWorksheet wksht = wbk.AddWorksheet( "sheet1" );

IXlsStyle style = wksht.NewStyle();

style.Alignment.Vertical = xlsgen.enumVerticalAlignment.valign_bottom;
style.Alignment.Horizontal = xlsgen.enumHorizontalAlignment.halign_center;
style.Borders.Color = (int) xlsgen.enumColorPalette.colorBlack;
style.Borders.Style = xlsgen.enumBorderStyle.border_thin;

style.Apply();

// write first merged cells

wksht.set_Number(3,2, 5);

xlsgen.IXlsRange range1 = wksht.NewRange("R3C2:R5C2");
xlsgen.IXlsMergedCells mc1 = range1.NewMergedCells();


// write second merged cells

xlsgen.IXlsStyle style2 = style.Duplicate();

style2.Alignment.Horizontal = xlsgen.enumHorizontalAlignment.halign_right;

style2.Apply();

wksht.set_Number(3,4, 5);

xlsgen.IXlsRange range2 = wksht.NewRange("R3C4:R3C6");
xlsgen.IXlsMergedCells mc2 = range2.NewMergedCells();


// write third merged cells

xlsgen.IXlsStyle style3 = style.Duplicate();

style3.Alignment.Horizontal = xlsgen.enumHorizontalAlignment.halign_center;
style3.Alignment.Vertical = xlsgen.enumVerticalAlignment.valign_bottom;

style3.Apply();

wksht.set_Number(5,4, 5);

xlsgen.IXlsRange range3 = wksht.NewRange("R5C4:R8C6");
xlsgen.IXlsMergedCells mc3 = range3.NewMergedCells();


// commit all work, and make sure the .xls file is ready to use
wbk.Close();

// free the memory from the running instance
System.Runtime.InteropServices.Marshal.ReleaseComObject(engine);
C/C++ code

// initialize the COM library
::CoInitialize(NULL);

{

  // create a running instance of the generator
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  // create a new Excel file, and retrieve a workbook to work with
  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->New( L"c:\\myfile.xls" );

  // create a new worksheet
  xlsgen::IXlsWorksheetPtr wksht;
  wksht = wbk->AddWorksheet( L"sheet1" );

  xlsgen::IXlsStylePtr style = wksht->NewStyle();

  style->Alignment->Vertical = xlsgen::valign_bottom;
  style->Alignment->Horizontal = xlsgen::halign_center;
  style->Borders->Color = xlsgen::colorBlack;
  style->Borders->Style = xlsgen::border_thin;

  style->Apply();

  // write first merged cells

  style->Apply();
  wksht->Number[3][2] = 5;

  xlsgen::IXlsRangePtr range1 = wksht->NewRange(L"R3C2:R5C2");
  xlsgen::IXlsMergedCellsPtr mc1 = range1->NewMergedCells();


  // write second merged cells

  xlsgen::IXlsStylePtr style2 = style->Duplicate();

  style2->Alignment->Horizontal = xlsgen::halign_right;

  style2->Apply();

  wksht->Number[3][4] = 5;

  xlsgen::IXlsRangePtr range2 = wksht->NewRange(L"R3C4:R3C6");
  xlsgen::IXlsMergedCellsPtr mc2 = range2->NewMergedCells();


  // write third merged cells

  xlsgen::IXlsStylePtr style3 = style->Duplicate();

  style3->Alignment->Horizontal = xlsgen::halign_center;
  style3->Alignment->Vertical = xlsgen::valign_bottom;

  style3->Apply();

  wksht->Number[5][4] = 5;

  xlsgen::IXlsRangePtr range3 = wksht->NewRange(L"R5C4:R8C6");
  xlsgen::IXlsMergedCellsPtr mc3 = range3->NewMergedCells();


  // commit all work, and make sure the .xls file is ready to use
  wbk->Close();

}

// frees the COM library
::CoUninitialize();

 

Unmerging cells

It is also possible to unmerge cells, using the UnmergeCell method from the IXlsWorksheet interface.

VB code

' unmerge cells in "B3"
wksht.UnmergeCell(3,2)

 

Finding merged cells

Depending on whether you are only interested in knowing whether a given cell represented by its (row, column) pair is part of a merged cells group, or you want to know exactly what is the definition of this merged cells group, there are two properties exposed at the worksheet level for that matter :

 

xlsgen documentation. © ARsT Design all rights reserved.