python 对excel操作
本帖最后由 ALEXSUNRISE 于 2012-12-07 11:32:40 编辑 需求:一个test.xls文件,有一列为“成绩”(0-100分),现在想将成绩小于60的成绩修改成60分。
我想用python实现这个对xls操作的脚本,其中涉及到对xls的读,修改单元格中的值并写回。
之前尝试过xlrd和xlwt,似乎不是很好用,感谢大牛给出操作的源码。
如果使用win32com.client这个类库,如何实现合并单元格呢?
[解决办法]
自己用到的代码,供参考,Excel 2003测试没有问题。
#!/usr/bin/env python
#-*- coding:utf-8 -*-
import win32com.client
import os.path
class easyExcel(object):
'''A utility to make it easier to get at Excel.'''
def __init__(self, filename=None, UpdateLinks=0, ReadOnly=False):
self.xlsApp = win32com.client.Dispatch('Excel.Application')
self.Visible = False
if filename:
if not os.path.exists(filename):
raise '%s not found!' %(filename)
self.filename = filename
self.xlsBook = self.xlsApp.Workbooks.Open(filename, UpdateLinks, ReadOnly)
else:
self.filename = ''
self.xlsBook = self.xlsApp.Workbooks.Add()
def save(self, newfilename=None):
if newfilename:
self.filename = newfilename
self.xlsBook.SaveAs(newfilename)
else:
self.xlsBook.Save()
def close(self):
'''Rembering to save before Close'''
self.xlsBook.Close(SaveChanges = 0)
del self.xlsApp
def getCell(self, sheet, row, col):
'''Get Value of one Cell'''
sht = self.xlsBook.Worksheets(sheet)
return sht.Cells(row, col).Value
def setCell(self, sheet, row, col, value):
'''Set Value of one Cell'''
sht = self.xlsBook.Worksheets(sheet)
sht.Cells(row, col).Value = value
def getRowsCount(self, sheet):
sht = self.xlsBook.Worksheets(sheet)
return sht.UsedRange.Rows.Count
def mergeCells(self, sheet, row1, col1, row2, col2):
sht = self.xlsBook.Worksheets(sheet)
sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Merge()
if __name__ == '__main__':
xls = easyExcel(r'D:\test.xls')
for row in range(1, 8):
val = xls.getCell('Sheet1', row, 1)
print(type(val), val)
xls.mergeCells('Sheet1', 2, 2, 6, 6)
xls.save()
xls.close()