Manipulate (Remove/Edit/Extract) Excel Comments in C# and Visual Basic

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.

C#

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”E:\Work\Documents\ExcelFiles\Staff Contact Info.xlsx”);

            Worksheet sheet = workbook.Worksheets[0];

Visual Basic

            Dim workbook As New Workbook()

            workbook.LoadFromFile(“E:\Work\Documents\ExcelFiles\Staff Contact Info.xlsx”)

            Dim sheet As Worksheet = workbook.Worksheets(0)

Remove Comment

Get the first comment of this sheet instance and then invoke ExcelCommentObject.Remove() method to delete the first comment.

C#

            sheet.Comments[0].Remove();

Visual Basic

            sheet.Comments(0).Remove()

Edit 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.

C#

            sheet.Comments[0].Text = “He will come back to company in May”;

Visual Basic

            sheet.Comments(0).Text = “He will come back to company in May”

Extract Comment

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.

C#

            StringBuilder SB = new StringBuilder();

            SB.Append(sheet.Comments[1].Text);

            File.WriteAllText(“ExcelComment.txt”, SB.ToString());

Visual Basic

            Dim SB As New StringBuilder()

            SB.Append(sheet.Comments(1).Text)

            File.WriteAllText(“ExcelComment.txt”, SB.ToString())

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.

C#

            workbook.SaveToFile(“EditComment.xlsx”, ExcelVersion.Version2010);

            System.Diagnostics.Process.Start(“EditComment.xlsx”);

Visual Basic

            workbook.SaveToFile(“EditComment.xlsx”, ExcelVersion.Version2010)

            System.Diagnostics.Process.Start(“EditComment.xlsx”)

Result of Excel file with Removed and Edited Comments

Result of TEXT file with Extracted Comment

Download Spire.XLS for .NET Here

P.S The .NET Office component, Spire.Office for .NET can be used to realize this function as well.

Advertisements

12 thoughts on “Manipulate (Remove/Edit/Extract) Excel Comments in C# and Visual Basic

  1. american heart association says:

    Good day! Do you know if they make any plugins to help with Search Engine Optimization?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good gains.

    If you know of any please share. Many thanks!

  2. buy a car says:

    First of all I want to say great blog! I had a quick
    question which I’d like to ask if you do not mind. I was interested to find out how you center yourself and clear your head before writing. I have had a difficult time clearing my thoughts in getting my thoughts out there. I truly do enjoy writing but it just seems like the first 10 to 15 minutes are generally wasted simply just trying to figure out how to begin. Any suggestions or hints? Kudos!

  3. 500 fast cash payday loan says:

    The other advantage of Quick Instant Payday Loans Online.
    What are Instant Payday Loans Online for Bad Credits?

    With short term 12 month loans that come from the payday loan scene is
    not so developed as in United Kingdom. And by
    using a loan to get by until the next paycheck.

  4. like it says:

    Heya i am for the primary time here. I came across this board and
    I in finding It really useful & it helped me out much.
    I am hoping to provide something again and help others like you aided me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s