Question

如何用python读取excel

Answer 1

用python对excel的读写操作，要用到两个库：xlrd和xlwt，首先下载安装这两个库。

1、#读取Excel
import xlrd
data = xlrd.open_workbook(excelFile)
table = data.sheets()[0]
nrows = table.nrows #行数
ncols = table.ncols #列数
for i in xrange(0,nrows):
rowValues= table.row_values(i) #某一行数据
for item in rowValues:
print item

2、写Excel文件
'''往EXCEl单元格写内容，每次写一行sheet:页签名称；row：行内容列表；rowIndex：行索引;
isBold:true:粗字段，false:普通字体'''
def WriteSheetRow(sheet,rowValueList,rowIndex,isBold):
i = 0
style = xlwt.easyxf('font: bold 1')
#style = xlwt.easyxf('font: bold 0, color red;')#红色字体
#style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;') # 设置Excel单元格的背景色为黄色，字体为粗体
for svalue in rowValueList:
strValue = unicode(str(svalue),'utf-8')
if isBold:
sheet.write(rowIndex,i,strValue,style)
else:
sheet.write(rowIndex,i,strValue)
i = i + 1

'''写excel文件'''
def save_Excel(strFile):
excelFile = unicode(strFile, "utf8")
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('sheet1',cell_overwrite_ok=True)
headList = ['标题1','标题2','标题3','标题4','总计']
rowIndex = 0
WriteSheetRow(sheet,headList,rowIndex,True)
for i in xrange(1,11):
rowIndex = rowIndex + 1
valueList = []
for j in xrange(1,5):
valueList.append(j*i)
WriteSheetRow(sheet,valueList,rowIndex,False)
wbk.save(excelFile)

style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;')