I'm a python programmer and I have to insert notes en-mass into an excel spreadsheet. Does anybody know some way that's easy/practical using a python script? (could be a java script, if it had examples).
It can be on a .xls, .xlsx or .ods, and can be in any OS (although I'm a linux user, so if I could have my solution portable to linux, it would be better).
I've tried xlwt module for python and searched for someway to do this using csv, but no success.
Here's some python code that uses the pywin32 package on Windows to start Excel, open a spreadsheet and create a comment in cell A1:
>>> import win32com.client
>>> xl = win32com.client.Dispatch("Excel.Application")
>>> xl.Visible = 1
>>> wb = xl.Workbooks.Open(r'<full path of excel spreadsheet>')
>>> sheet = wb.ActiveSheet
>>> sheet.Range("A1").AddComment()
<COMObject AddComment>
>>> sheet.Range("A1").Comment.Visible = True
>>> sheet.Range("A1").Comment.Text("Hello World")
u'Hello World'
>>> wb.SaveAs(r'<full path of modified spreadsheet>')
>>> wb.Close()
>>> xl.Quit()
I just ran this code using python 2.7.2 on Windows 7 with Excel 2007 and it worked for me.
If you need to export a lot of data to Excel with formulas (and also including comments), you can use library called portable-spreadsheet (pip install portable-spreadsheet). See the documentation with use-cases. It follows the logic:
import portable_spreadsheet as ps
sheet = ps.Spreadsheet.create_new_sheet(5, 5)
# Set values
sheet.iloc[0, 0] = 25 # Set A1
sheet.iloc[1, 0] = sheet.iloc[0, 0] # reference to A1
# Export to Excel
sheet.to_excel('output/sample.xlsx')
It works in a similar way as Pandas Dataframe.
You can use openpyxl for this.
from openpyxl import Workbook
from openpyxl.comments import Comment
wb = Workbook()
ws = wb.active
comment = Comment("This is a comment!", "Author")
# comment.width =
ws['A1'].comment = comment
wb.save('CommentTest.xlsx')
wb.close()
