如何用Python修改Excel数据

在前面的文章中介绍了如何用Python读写Excel数据，今天再介绍一下如何用Python修改Excel数据。需要用到xlutils模块。下载地址为https://pypi.python.org/pypi/xlutils。下载后执行python setup.py install命令进行安装即可。

具体使用代码如下：

 1 #-*-coding:utf-8-*-
 2 from xlutils.copy import copy    # http://pypi.python.org/pypi/xlutils
 3 from xlrd import open_workbook  # http://pypi.python.org/pypi/xlrd
 4 from xlwt import easyxf         # http://pypi.python.org/pypi/xlwt
 5 
 6 import sys
 7 reload(sys)
 8 sys.setdefaultencoding('utf-8')
 9 
10 # 0 based (subtract 1 from excel row number)
11 START_ROW = 404
12 
13 ismal_index = 2
14 #url所在列
15 url_index = 12
16 #domain所在列
17 domain_index = 11
18 #malinfo所在列
19 malinfo_index = 9
20 
21 file_path = "C:\\Users\\***\\Desktop\\20130514.xls"
22 #formatting_info=True保存之前数据的格式
23 rb = open_workbook(file_path,formatting_info=True)
24 r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
25 wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
26 w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
27 
28 malurl = '''http://xbox.ooqqxx.com/res/ext.jar
29             http://xbox.ooqqxx.com/res/stat.jar
30             http://xbox.ooqqxx.com/pages/v.html
31             http://xbox.ooqqxx.com/pages/extv.html
32             http://xbox.ooqqxx.com/pages/r.html'''
33 domain_info = "http://xbox.ooqqxx.com"
34 malinfo = u"获取恶意URL，写入配置文件中，下载恶意可执行程序。"
35 
36 #r_sheet.nrows为总行数
37 for row_index in range(START_ROW, r_sheet.nrows):
38     #xlsvalue = r_sheet.cell(row_index, col_age_november).value
39     w_sheet.write(row_index, ismal_index, u'是')
40     w_sheet.write(row_index, url_index, malurl)
41     w_sheet.write(row_index, domain_index, domain_info)
42     w_sheet.write(row_index, malinfo_index, malinfo)
43 #wb.save(file_path + '.out' + os.path.splitext(file_path)[-1])
44 wb.save("C:\\Users\\***\\Desktop\\2013.xls")

注意：

如果在写入数据（中文）时出现以下错误：

ascii codec can't decode byte 0xe8 in position 0:ordinal not in range(128)

则需加上以下代码：

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

另外，则中文字符串前记得加上u.