Borders

xlsgen > overview > Borders

Borders are responsible for individual cell borders, including the 4 main borders, plus the two diagonals. Border options are accessed by right-clicking on a cell and choosing Format Cell and then clicking on the Borders tab. The most common border options are also provided in the formatting toolbar.

The borders object manages all individual borders. Borders are part of the current cell style. The IXlsBorders programming interface provides access to individual borders. See IXlsBorder for more information.

Borders include the following individual borders :

top, left, right, and bottom
upper and lower diagonal

Alternatively, each individual border can be assigned a style and a color. See enumBorderStyle for more information.

Because of their nature, borders between two adjacent cells are shared. This can have a side effect on how the programmer expected borders to behave, and how they eventually show in Excel. For instance, there is no way to assign an outlining border to a range of cells at the same time. The reason for that is that an outlining border can be broken down in as much as 3 or 9 distinct borders, and each of them must be individually created and set. The user interface in Excel uses tricks to make users believe they can select a range of cells and apply a border without much work. Actually, Excel creates all underlying individual borders without letting the user know about it.

In order to produce the result above (please note the borders on the left column are all the same, while borders on the right column are distinct), the following code is required :

VB code


Dim style As IXlsStyle
Set style = wksht.NewStyle

'
' column on the left
'

' create a style : bold + border
style.Font.Color = colorBlack
style.Font.Bold = True
style.Borders.Top.style = border_medium
style.Borders.Top.Color = colorMagenta
style.Borders.Left.style = border_medium
style.Borders.Left.Color = colorMagenta
style.Borders.Right.style = border_medium
style.Borders.Right.Color = colorMagenta
style.Borders.Bottom.style = border_medium
style.Borders.Bottom.Color = colorMagenta
style.Apply

wksht.Label(4, 3) = "First Name"

' another style, change only the bold property
Dim style2 As IXlsStyle
Set style2 = style.Duplicate
style2.Font.Bold = False
style2.Apply

wksht.Label(5, 3) = "First Name1"
wksht.Label(6, 3) = "First Name2"
wksht.Label(7, 3) = "First Name3"
wksht.ColWidth(3) = 12


'
' column on the right
'

' another style : bold + border
Dim style3 As IXlsStyle
Set style3 = wksht.NewStyle
style3.Font.Color = colorBlack
style3.Font.Bold = True
style3.Borders.Top.style = border_doubles
style3.Borders.Top.Color = colorRed
style3.Borders.Left.style = border_doubles
style3.Borders.Left.Color = colorRed
style3.Borders.Right.style = border_doubles
style3.Borders.Right.Color = colorRed
style3.Borders.Bottom.style = border_doubles
style3.Borders.Bottom.Color = colorRed
style3.Apply

wksht.Label(4, 5) = "Last Name"

' another style : change bold, and remove one border
Dim style4 As IXlsStyle
Set style4 = style3.Duplicate
style4.Font.Bold = False
style4.Borders.Bottom.style = border_none
style4.Apply

wksht.Label(5, 5) = "Last Name1"

' another style : remove one border
Dim style5 As IXlsStyle
Set style5 = style4.Duplicate
style5.Borders.Top.style = border_none
style5.Apply

wksht.Label(6, 5) = "Last Name2"

' another style : remove bold, remove one border
Dim style6 As IXlsStyle
Set style6 = style3.Duplicate
style6.Font.Bold = False
style6.Borders.Top.style = border_none
style6.Apply

wksht.Label(7, 5) = "Last Name3"
wksht.ColWidth(5) = 12

C# code


IXlsStyle style = wksht.NewStyle();

//
// column on the left
//

// create a style : bold + border
style.Font.Color = (int) xlsgen.enumColorPalette.colorBlack;
style.Font.Bold = 1;
style.Borders.Top.Style = xlsgen.enumBorderStyle.border_medium;
style.Borders.Top.Color = (int) xlsgen.enumColorPalette.colorMagenta;
style.Borders.Left.Style = xlsgen.enumBorderStyle.border_medium;
style.Borders.Left.Color = (int) xlsgen.enumColorPalette.colorMagenta;
style.Borders.Right.Style = xlsgen.enumBorderStyle.border_medium;
style.Borders.Right.Color = (int) xlsgen.enumColorPalette.colorMagenta;
style.Borders.Bottom.Style = xlsgen.enumBorderStyle.border_medium;
style.Borders.Bottom.Color = (int) xlsgen.enumColorPalette.colorMagenta;
style.Apply();

wksht.set_Label(4, 3, "First Name");

// another style, change only the bold property
IXlsStyle style2 = style.Duplicate();
style2.Font.Bold = 0;
style2.Apply();

wksht.set_Label(5, 3, "First Name1");
wksht.set_Label(6, 3, "First Name2");
wksht.set_Label(7, 3, "First Name3");
wksht.set_ColWidth(3, 12);


//
// column on the right
//

// another style : bold + border
IXlsStyle style3 = wksht.NewStyle();

style3.Font.Color = (int) xlsgen.enumColorPalette.colorBlack;
style3.Font.Bold = 1;
style3.Borders.Top.Style = xlsgen.enumBorderStyle.border_doubles;
style3.Borders.Top.Color = (int) xlsgen.enumColorPalette.colorRed;
style3.Borders.Left.Style = xlsgen.enumBorderStyle.border_doubles;
style3.Borders.Left.Color = (int) xlsgen.enumColorPalette.colorRed;
style3.Borders.Right.Style = xlsgen.enumBorderStyle.border_doubles;
style3.Borders.Right.Color = (int) xlsgen.enumColorPalette.colorRed;
style3.Borders.Bottom.Style = xlsgen.enumBorderStyle.border_doubles;
style3.Borders.Bottom.Color = (int) xlsgen.enumColorPalette.colorRed;
style3.Apply();

wksht.set_Label(4, 5, "Last Name");

// another style : change bold, and remove one border
IXlsStyle style4 = style3.Duplicate();

style4.Font.Bold = 0;
style4.Borders.Bottom.Style = xlsgen.enumBorderStyle.border_none;
style4.Apply();

wksht.set_Label(5, 5, "Last Name1");

// another style : remove one border
IXlsStyle style5 = style4.Duplicate();

style5.Borders.Top.Style = xlsgen.enumBorderStyle.border_none;
style5.Apply();

wksht.set_Label(6, 5, "Last Name2");

// another style : remove bold, remove one border
IXlsStyle style6 = style3.Duplicate();

style6.Font.Bold = 0;
style6.Borders.Top.Style = xlsgen.enumBorderStyle.border_none;
style6.Apply();

wksht.set_Label(7, 5, "Last Name3");
wksht.set_ColWidth(5, 12);

C/C++ code


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

//
// column on the left
//

// create a style : bold + border
style->Font->Color = xlsgen::colorBlack;
style->Font->Bold = TRUE;
style->Borders->Top->Style = xlsgen::border_medium;
style->Borders->Top->Color = xlsgen::colorMagenta;
style->Borders->Left->Style = xlsgen::border_medium;
style->Borders->Left->Color = xlsgen::colorMagenta;
style->Borders->Right->Style = xlsgen::border_medium;
style->Borders->Right->Color = xlsgen::colorMagenta;
style->Borders->Bottom->Style = xlsgen::border_medium;
style->Borders->Bottom->Color = xlsgen::colorMagenta;
style->Apply();

wksht->Label[4][3] = "First Name";

// another style, change only the bold property
xlsgen::IXlsStylePtr style2 = style->Duplicate();
style2->Font->Bold = FALSE;
style2->Apply();

wksht->Label[5][3] = "First Name1";
wksht->Label[6][3] = "First Name2";
wksht->Label[7][3] = "First Name3";
wksht->ColWidth[3] = 12;


//
// column on the right
//

// another style : bold + border
xlsgen::IXlsStylePtr style3 = wksht->NewStyle();

style3->Font->Color = xlsgen::colorBlack;
style3->Font->Bold = TRUE;
style3->Borders->Top->Style = xlsgen::border_doubles;
style3->Borders->Top->Color = xlsgen::colorRed;
style3->Borders->Left->Style = xlsgen::border_doubles;
style3->Borders->Left->Color = xlsgen::colorRed;
style3->Borders->Right->Style = xlsgen::border_doubles;
style3->Borders->Right->Color = xlsgen::colorRed;
style3->Borders->Bottom->Style = xlsgen::border_doubles;
style3->Borders->Bottom->Color = xlsgen::colorRed;
style3->Apply();

wksht->Label[4][5] = "Last Name";

// another style : change bold, and remove one border
xlsgen::IXlsStylePtr style4 = style3->Duplicate();

style4->Font->Bold = FALSE;
style4->Borders->Bottom->Style = xlsgen::border_none;
style4->Apply();

wksht->Label[5][5] = "Last Name1";

// another style : remove one border
xlsgen::IXlsStylePtr style5 = style4->Duplicate();

style5->Borders->Top->Style = xlsgen::border_none;
style5->Apply();

wksht->Label[6][5] = "Last Name2";

// another style : remove bold, remove one border
xlsgen::IXlsStylePtr style6 = style3->Duplicate();

style6->Font->Bold = FALSE;
style6->Borders->Top->Style = xlsgen::border_none;
style6->Apply();

wksht->Label[7][5] = "Last Name3";
wksht->ColWidth[5] = 12;