I wish to position comments on top of the target cell but they are always placed at the same (default) position, with the respect of the target cell. I have tried increasing the shape.Top
and shape.Left
every other comment but nothing. Any suggestion?
Here’s the function:
private static void AddComments(Worksheet worksheet, Range sourceRange, Range targetRange)
{
int lastRow = GetLastRowInColumn(worksheet, targetRange.Column);
int sourceColumn = sourceRange.Column;
int targetColumn = targetRange.Column;
int[] missingCommentRows = new int[0];
for (int i = targetRange.Row + 1; i <= lastRow; i++)
{
targetRange = worksheet.Cells[i, targetColumn];
sourceRange = worksheet.Cells[i, sourceColumn];
// Clear comment, if any
targetRange.ClearComments();
string sourceValue = sourceRange.Value2.ToString().Trim();
if (!string.IsNullOrEmpty(sourceValue))
{
sourceValue = char.ToUpper(sourceValue[0]) + sourceValue.Substring(1);
if (sourceValue.EndsWith("."))
{
_ = sourceValue.Substring(0, sourceValue.Length - 1);
}
Comment comment = targetRange.AddComment();
Shape shape = comment.Shape;
// Formatting
shape.TextFrame.Characters().Font.Size = 9;
shape.TextFrame.Characters().Font.Name = "Arial";
shape.TextFrame.Characters().Font.Bold = false;
shape.TextFrame.Characters().Font.Italic = true;
shape.TextFrame.Characters().Font.Color = Color.Red;
shape.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// Sizing
shape.TextFrame.AutoSize = true;
// Positioning
float topDistance = shape.Top;
float leftDistance = shape.Left;
if (i % 2 == 0)
{
topDistance += 20;
leftDistance += 20;
}
shape.Top = topDistance;
shape.Left = leftDistance;
shape.Top = (int)targetRange.Top + 100;
shape.Left = (int)targetRange.Left + 100;
// Name
shape.Name = "cmnt_" + targetRange.Address;
comment.Text(sourceValue);
comment.Visible = false;
}
else
{
// Create a new array with increased size to hold row number where the comment is missing
int[] newArray = new int[missingCommentRows.Length + 1];
// Copy the existing elements to the new array
Array.Copy(missingCommentRows, newArray, missingCommentRows.Length);
// Add row number to the new array
newArray[newArray.Length - 1] = i;
// Update the reference to the array
missingCommentRows = newArray;
}
}
// Delete the entire column
sourceRange.EntireColumn.Delete();
}
Many thanks!