How To Read Excel Comments / Notes - Please Provide A Simple Sample


#1

Aspose/All,

Please can you provide a snippet of VB.NET code that reads the String Value of a Comment/Note on a Cell.

Basically I have a piece of code that steps through cell by cell and I need to do the following.

e.g. Say :-

intZeroBasedExcelColumn is integer and has a value of 5
and
intZeroBasedExcelRow is integer and has a value of 6


I want to :-

1) Check to see if there is a Comment
2) If there is a comment then read in the value to a String
3a) Append something to the string and either delete the comment and add the new comment
…or …
3b) just append a new comment (because of performance I would be interested in both solutions for 3a and 3b)

The sample code enclosed in your help adds a comment and gets the commentIndex

[Visual Basic]

Dim excel as Excel = new Excel()
Dim comments as Comments = excel.Worksheets(0).Comments

'Add comment to cell A1
Dim commentIndex as Integer = comments.Add(0, 0)
Dim comment as Comment = comments(commentIndex)
comment.Note = “First note.”
comment.Font.Name = “Times New Roman”

'Add comment to cell B2
comments.Add(“B2”)
comment = comments(“B2”)
comment.Note = "Second note."


I want to basically say I’m at column Column 5 and Row 6, ask if a comment exists and if it does read in the Comment/Note, append some text and update the cells comment.

Please can you point me in the right direction (I must be missing something simple???)

Regards

Mark Dicken
mark@sabic.com
(Currently Working In Saudi Arabia)


#2

Hi Mark,

Thanks for your consideration.

Do you want to check if a comment is set at column Column 5 and Row 6 in your designer file?
To improve performance, currently Aspose.Excel doesn’t support modifying comments in designer file.

If this feature is important to you, please let me know. I will put it in my future work plan.

Or you just want to see if a comment is set by your code? It’s easier. You can use

Overloads Public Default ReadOnly Property Item( _
ByVal cellName As String _
) As Comment.

If an exception is thrown, then you can see the comment does not exist. I will change it to return null if no comments exists for this cell.


#3

Laurence,

Sorry but I do not quite understand your answer/reply…???

More details for you.

My code gets some configuration settings and with the settings it knows what processing of data it needs to do with data enclosed within a standard .XLS file

I have hacked togther a code snippet of what I am trying to do … Can you please help me fill in the BLANK’s

I start off with some variables :-

strSpreadsheetNameURL = \server\temp\file001.xls
strSpreadsheetSheetName = 'SheetABC’
intExcelZeroBasedColumn = 5
intExcelZeroBasedRow = 6
strTempStringValue = ""
intStartRow = 10
intColumnNumber = 8
i = 0

Code snippet … START

Dim oAsposeExcelWorkBook As New Aspose.Excel.Excel
Dim oAsposeExcelWorkSheets As Aspose.Excel.Worksheets
Dim oAsposeExcelWorkSheet As Aspose.Excel.Worksheet

oAsposeExcelWorkBook.Open(strSpreadsheetNameURL)
oAsposeExcelWorkSheet = oAsposeExcelWorkBook.Worksheets(strSpreadsheetSheetName)
intEndRow = oAsposeExcelWorkSheet.Cells.MaxRow + 2

for i = intStartRow to intEndRow
strTempStringValue = oAsposeExcelWorksheet.Cells(i, intColumnNumber).StringValue
… (I do some data manipulation with the data and then write back )…
oAsposeExcelWorksheet.Cells(i, intColumnNumber).PutValue(strTempStringValue)

'BLANK - START
1) Check to see if there is a Comment
2) If there is a comment then read in the value to a String
3a) Append something to the string and either delete the comment and add the new comment
…or …
3b) just append a new comment (because of performance I would be interested in both solutions for 3a and 3b)

'BLANK - FINISH

next i
oAsposeExcelWorkBook.Save(strSpreadsheetNameURL , Aspose.Excel.FileFormatType.Excel2000)

As you can I’m happy getting the String Values within the Cells, I now simply want to be able to read/write and manipulate Comments

Please can you point me in the right direction to fill in the BLANKs

I have not used any designer facilities that aspose may offer, I simply in code open up an exl document manipulate data and manipulate comments, save the data and then get on with the next .xls file

Many Thanks In Advance…

Currently I used to get 50 Transactions per second and now I’m using ASPOSE.Excel I’m getting 1000+ Per Second, but I do need comments … please provide some sample code for me.

Regards

Mark Dicken


#4

Hi Mark,

Sorry, currently Aspose.Excel doesn’t support the feature you needed. Aspose.Excel can import comments in your xls file and can set comments dynamically using API. But it cannot modify comments in your xls file(designer file) now.

Could you tell me your expected date for this feature? I will investigate it and implement it ASAP.


#5

Laurence

Let me ask this another way :-

Please can you tell me how to Read A Comment dynamically using the API from a standard excel file. (I’m only using the API)

The Code sample in you help (below) simply adds a comment

Please just tell me how to READ a comment

[Visual Basic]

Dim excel as Excel = new Excel()
Dim comments as Comments = excel.Worksheets(0).Comments

'Add comment to cell A1
Dim commentIndex as Integer = comments.Add(0, 0)
Dim comment as Comment = comments(commentIndex)
comment.Note = “First note.”
comment.Font.Name = “Times New Roman”

'Add comment to cell B2
comments.Add(“B2”)
comment = comments(“B2”)
comment.Note = "Second note."

Can I please phone you as I’m sure that a 5 minute conversation will resolve this simple request

Say for example I have a comment in Cell H12 how can I get the comment into a variable called strComment


Regards

Mark Dicken (Atos Origin Middle East)
Data Team - Sabic Fanar Project
Saudi Arabia
mark@sabic.com
Mobile +966 (0)5 483 9724




#6

Hi Mark,

You can try the following code:

Dim excel as Excel = new Excel()
Dim comments as Comments = excel.Worksheets(0).Comments

Dim comment as Comment

Try
comment = comments(“H12”)
'Set comment as your wish
comment.Note = "hello, world"

Catch
'If catch the exceptin, that means there isn’t comment on this cell
Dim commentIndex as integer = comments.Add(“H12”)
comment = comments(commentIndex)
'Set comment as your wish
comment.Note = "hello, world"

Sorry, I haven’t a phone on hand. But you can reach me at hanyu_chen@hotmail.com using MS messenger.