CODEWITHSUNDEEP
pythonexcelcomcomments
stolen_rosary
stolen_rosary

Reputation: 23

How to check if there is a comment or not

I've got an .xlsx file. Some cells in it have comments which content will be used thereafter. How to check, iterating through every cell, if it has a comment or not? This code (in which I tried to iterate the third column and nothing else) returns an error:

import win32com.client, win32gui, re
xl = win32com.client.Dispatch("Excel.Application")
xl.Visible = 1
TempExchFilePath = win32gui.GetOpenFileNameW()[0]
wb = xl.Workbooks.Open(TempExchFilePath)
sh = wb.Sheets("Sheet1")
comments = None
for i in range (0,201,1):
   if sh.Cells(2,i).Comment.Text() != None:
      comment = sh.Cells(2,i).Comment.Text()
      comments += comment
print(comments)
input()

I am very new to Python and sorry for my English. Thanks! :3

Upvotes: 2

Views: 1559

Answers (3)

wnnmaw
wnnmaw

Reputation: 5534

Here is what I think is the best way, using the Python Excel modules, specifically xlrd

Suppose you have a workbook which has a cell A1 with a comment written by Joe Schmo which says "Hi!", here's how you'd get at that.

>>> from xlrd import *
>>> wb = open_workbook("test.xls")
>>> sheet = wb.sheet_by_index(0)
>>> notes = sheet.cell_note_map
>>> print notes
{(0, 0): <xlrd.sheet.Note object at 0x00000000033FE9E8>}
>>> notes[0,0].text
u'Schmo, Joe:\nHi!'

A Quick Explanation of What's Going On

So the xlrd module is a pretty handy thing, once you figure it out (full documentation here). The first two lines import the module and create a workbook object called wb. Next, we create a sheet object of the first sheet (index 0) and call that sheet (I'm feeling creative today). Then we create a dicitonary of note objects called notes with the cell_note_map attribute of our sheet object. This dictionary has the (row,col) index of the comment as the key, and then a note object as the value. We can then extract the text of that note using the text attribute of the note object.

For multiple notes, you can iterate through your dictionary to get at all the text as show below:

>>> comments = []
>>> for key in notes.keys():
...     comments.append(notes[key].text)
...
>>> print comments
[u"Schmo, Joe:\nHere's another\n", u'Schmo, Joe:\nhi!']

Some Things to Note

  • This will only work with .xls files, not .xlsx, but you can save any .xlsx as an .xls so there's no problem
  • The author of the comment will always be listed first, but can be accessed separately by using the author attribute instead of text. There will also always be a \n inbetween the author and text.
  • Cells which do not have comments will not be mapped by cell_note_map. So a full sheet without any comments will yield an empty dictionary

Upvotes: 5

Corley Brigman
Corley Brigman

Reputation: 12401

I think this should work. However, you have

comments = None

and then

comments += comment

I don't think you can do None + anything. Most likely, you either want to do

comments = ''
comments += comment

or

comments = []
comments.append(comment)

Another thing you probably need to fix:

if sh.Cells(2,i).Comment.Text() != None:

The (2,i) syntax doesn't appear to work in python. Change to Cells[2][i]. Also, if Comment doesn't exist, then it will be None , and won't have a Text() function. i.e.:

if sh.Cells[2][i].Comment != None:
    comment = sh.Cells[2][i].Comment.Text()

Upvotes: 0

cptPH
cptPH

Reputation: 2401

I think defining comments as None and then trying to add Stuff (i guess a string) won't work.

Try comments = "" instead of comments = None

Other then that, it would deffinitly help to see the error.

Upvotes: 0

Related Questions