I will add comment for cells with special data information in Excel. For example, the cell with manager name will be commented with “He/She is department manager” in staff information file. As is known, data will be changed at anytime, so it is necessary to edit comments to be in correspondence with the changed data. Now, this post focuses on presenting solution about how to manipulate existing Excel comments in C# and Visual Basic, containing to remove, edit and extract comment from Excel.
Solution I will introduce is based on a .NET Excel component, Spire.XLS for .NET. Therefore, I have downloaded, installed this component and add its dll file as reference in my project before programming. And the following screenshot presents original Excel file with comments.
Following, let’s code step by step.
Firstly, initialize a Workbook class instance and invoke LoadFromFile(string fileName) method to load the Excel file with comments. Then, initialize a Worksheet class instance.
Workbook workbook = new Workbook();
workbook.LoadFromFile(@”E:\Work\Documents\ExcelFiles\Staff Contact Info.xlsx”);
Worksheet sheet = workbook.Worksheets;
Dim workbook As New Workbook()
workbook.LoadFromFile(“E:\Work\Documents\ExcelFiles\Staff Contact Info.xlsx”)
Dim sheet As Worksheet = workbook.Worksheets(0)
Get the first comment of this sheet instance and then invoke ExcelCommentObject.Remove() method to delete the first comment.
Because the first comment has been moved, the original second becomes the first. Get it and assign string value which the new comment content for ExcelCommentObject.Text property to edit original comment.
sheet.Comments.Text = “He will come back to company in May”;
sheet.Comments(0).Text = “He will come back to company in May”
Initialize a StringBuilder class instance and invoke Append method of StringBuilder class to append a copy of specified string to the end of this instance. The parameter passed to this method is string which is text of the second comment (original third). Finally, invoke WriteAllTextmethod of System.IO.File class with parameters string path and string contents which are are saved in StringBuilder instance to save extracted comments in a TEXT file.
StringBuilder SB = new StringBuilder();
Dim SB As New StringBuilder()
Finally, we need to invoke SaveToFile method of Workbook class with parameters string fileName and ExcelVersion version to save the Excel with changed comments. Then launch it for viewing the result.
Result of Excel file with Removed and Edited Comments
Result of TEXT file with Extracted Comment
P.S The .NET Office component, Spire.Office for .NET can be used to realize this function as well.