digitalMoto.net

(Another snippet I originally posted to CodeKeep.)

Code:

// Required libs
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel.Extensions;
using Tools = Microsoft.Office.Tools;

Range range = <get the target range to apply the label over>
string controlName = <unique name for the control to avoid collisions on other worksheets>

// Add a control
Tools.Excel.Controls.Label lbl = worksheet.Controls.AddLabel(targetRange, controlName);

// Add meaning to the label
lbl.Text = Title;
lbl.TextAlign = ContentAlignment.MiddleCenter;
lbl.BorderStyle = BorderStyle.FixedSingle;
lbl.ForeColor = Color.Black;
lbl.BackColor = Color.Green;

// Delete a control
worksheet.Controls.Remove(controlName);

Usage:

//Add
lbl = worksheet.Controls.AddLabel(targetRange, controlName);

// Delete
worksheet.Controls.Remove(controlName);

Notes:

If your range extends beyond a single cell, clear it before merging and applying the control.  EG: range.Value2 = String.Empty; range.MergeCells = true; range.Value2 = Title;

Setting the cell background color and border, works around the label border artifacting.  I was getting a strange green 1px line when I tried to define the borders in the label.  I dropped the explicit settings and applied the border settings to the underlying cell and the green lines disappeared.  It is probably something related to the Interop / Tool interaction.

If you are hitting a large number of cells, toggle Application.ScreenUpdating (false/true) to speed up the update and eliminate the screen flutter.

(Another snippet I originally posted to CodeKeep.)

Code:

 

Range rng = <your range>;

if (rng.get_End(XlDirection.xlToLeft) != rng.get_End(XlDirection.xlToLRight)
{
  rng.Value2 = string.Empty;
  rng.MergeCells = true;
}
rng.Value2 = Title;
rng.HorizontalAlignment = XlHAlign.xlHAlignCenter;
rng.VerticalAlignment = XlVAlign.xlVAlignCenter;

rng.Font.Size = 10;
rng.Font.Bold = true;
rng.Borders.Weight = XlBorderWeight.xlMedium

 

Notes:

The range must be large than a single cell for the ‘MergeCells = true’ to work. 

I’ve had problems with get_End.  In some cases, I parsed the address range manually as when I needed the address range for the first column in a listObject.DataBodyRange.  In other cases, using get_End worked just fine. 

Ever tried to debug a loop inside a listObject.Range? Use get_Address(). It makes the process a lot easier.

Code: Read more…

Description:
Set a cell’s solid background color using a single user-defined color.

Code: Read more…

Description:
Set a cell’s background color to a vertical gradient using two user-defined colors.

Code: Read more…

Description:
WinForm controls placed in a worksheet while it is not at 100% will be positioned incorrectly. Here’s a simple workaround to change the zoom to 100% then revert to the previous setting.

Code: Read more…