Python读写Excel文件的实例
本文为大家讲解了python操作excel文件的方法,包括读取excel文件数据,向excel写入数据的方法,感兴趣的同学参考下.
本文为大家讲解了python操作excel文件的方法,包括读取excel文件数据,向excel写入数据的方法,感兴趣的同学参考下.
最近由于经常要用到Excel，需要根据Excel表格中的内容对一些apk进行处理，手动处理很麻烦，于是决定写脚本来处理。首先贴出网上找来的读写Excel的脚本。
1.读取Excel(需要安装xlrd)：
#-*- coding: utf8 -*-
import xlrd
fname = &reflect.xls&
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
&&& sh = bk.sheet_by_name(&Sheet1&)
&&& print &no sheet in %s named Sheet1& % fname
nrows = sh.nrows
ncols = sh.ncols
print &nrows %d, ncols %d& % (nrows,ncols)
#获取第一行第一列数据
cell_value = sh.cell_value(1,1)
#print cell_value
row_list = []
#获取各行数据
for i in range(1,nrows):
&&& row_data = sh.row_values(i)
&&& row_list.append(row_data)
2.写入Excel（需安装pyExcelerator）
from pyExcelerator import *
w = Workbook()&&&& #创建一个工作簿
ws = w.add_sheet('Hey, Hades')&&&& #创建一个工作表
ws.write(0,0,'bit')&&& #在1行1列写入bit
ws.write(0,1,'huang')& #在1行2列写入huang
ws.write(1,0,'xuan')&& #在2行1列写入xuan
w.save('mini.xls')&&&& #保存
3.再举个自己写的读写Excel的例子
读取reflect.xls中的某些信息进行处理后写入mini.xls文件中。　
#-*- coding: utf8 -*-
import xlrd
from pyExcelerator import *&
w = Workbook()&
ws = w.add_sheet('Sheet1')&
fname = &reflect.xls&
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
&&& sh = bk.sheet_by_name(&Sheet1&)
&&& print &no sheet in %s named Sheet1& % fname
nrows = sh.nrows
ncols = sh.ncols
print &nrows %d, ncols %d& % (nrows,ncols)
cell_value = sh.cell_value(1,1)
#print cell_value
row_list = []
mydata = []
for i in range(1,nrows):
&&& row_data = sh.row_values(i)
&&& pkgdatas = row_data[3].split(',')
&&& #pkgdatas.split(',')
&&& #获取每个包的前两个字段
&&& for pkgdata in pkgdatas:
&&&&&&& pkgdata = '.'.join((pkgdata.split('.'))[:2])
&&&&&&& mydata.append(pkgdata)
&&& #将列表排序
&&& mydata = list(set(mydata))
&&& print mydata
&&& #将列表转化为字符串
&&& mydata = ','.join(mydata)
&&& #写入数据到每行的第一列
&&& ws.write(i,0,mydata)
&&& mydata = []
&&& row_list.append(row_data[3])
#print row_list
w.save('mini.xls')
4.现在我需要根据Excel文件中满足特定要求的apk的md5值来从服务器获取相应的apk样本，就需要这样做：　
#-*-coding:utf8-*-
import xlrd
import shutil
fname = &./excelname.xls&
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
&&& #打开Sheet1工作表
&&& sh = bk.sheet_by_name(&Sheet1&)
&&& print &no sheet in %s named Sheet1& % fname
nrows = sh.nrows
ncols = sh.ncols
#print &nrows %d, ncols %d& % (nrows,ncols)
#获取第一行第一列数据
cell_value = sh.cell_value(1,1)
#print cell_value
row_list = []
#range(起始行,结束行)
for i in range(1,nrows):
&&& row_data = sh.row_values(i)
&&& if row_data[6] == &HXB&:
&&&&&&& filename = row_data[3]+&.apk&
&&&&&&& #print &%s& %s& %s& %(i,row_data[3],filename)
&&&&&&& filepath = r&./1/&+filename
&&&&&&& print &%s& %s& %s& %(i,row_data[3],filepath)
&&&&&&& if os.path.exists(filepath):
&&&&&&&&&&& shutil.copy(filepath, r&./myapk/&)
好了，python操作Excel就这么！些了，简单吧
Copyright &
All Rights Reserved使用python3调用COM读写excel文件实例代码
阅读：594次&&&时间： 04:14:55&&
1、先下载安装pywin，版本得是3.1的。http://sourceforge.net/projects/pywin32/2、得重启，不然找不到win32库3、帖下代码，具体的属性与方法得参考excel帮助文档#!/usr/bin/env pythonfrom win32com.client import Dispatchimport win32com.clientimport win32apiimport osclass Excel:&&& def __init__(self, filename=None):&&&&&&& self.xlApp = win32com.client.Dispatch(Excel.Application)&&&&&&& if filename:&&&&&&&&&&& self.filename=filename&&&&&&&&&&& if os.path.exists(self.filename):&&&&&&&&&&&&&&& self.xlBook=self.xlApp.Workbooks.Open(filename)&&&&&&&&&&& else:&&&&&&&&&&&&&&& self.xlBook= self.xlApp.Workbooks.Add()&&&&&&& else:&&&&&&&&&&& self.xlBook= self.xlApp.Workbooks.Add()&&&&&&&&&&& self.filename=Untitle&&& def save(self, newfilename=None):&&&&&&& if newfilename:&&&&&&&&&&& self.filename = newfilename&&&&&&& self.xlBook.SaveAs(self.filename)&&& def close(self):&&&&&&& self.xlBook.Close(SaveChanges=0)&&&&&&& del self.xlApp&&& def copySheet(self, before):&&&&&&& "copy sheet"&&&&&&& shts = self.xlBook.Worksheets&&&&&&& shts(1).Copy(None,shts(1))&&& def newSheet(self,newSheetName):&&&&&&& sheet=self.xlBook.Worksheets.Add()&&&&&&& sheet.Name=newSheetName&&&&&&& sheet.Activate()&&& def activateSheet(self,sheetName):&&&&&&& self.xlBook.Worksheets(sheetName).Activate()&&& def activeSheet(self):&&&&&&& return self.xlApp.ActiveS&&& def getCell(self, row, col,sheet=None):&&&&&&& "Get value of one cell"&&&&&&& if sheet:&&&&&&&&&&& sht = self.xlBook.Worksheets(sheet)&&&&&&& else:&&&&&&&&&&& sht=self.xlApp.ActiveSheet&&&&&&& return sht.Cells(row, col).Value&&& def setCell(self, row, col, value,sheet=Sheet1):&&&&&&& "set value of one cell"&&&&&&& if sheet:&&&&&&&&&&&& sht = self.xlBook.Worksheets(sheet)&&&&&&& else:&&&&&&&&&&&& sht=self.xlApp.ActiveSheet&&&&&&& sht.Cells(row, col).Value = value
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
[商业源码]&
Copyright &
All Rights Reservedpython操作excel 使用openpyxl库问题
[问题点数：80分，结帖人moary]
python操作excel 使用openpyxl库问题
[问题点数：80分，结帖人moary]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2006年5月 PHP大版内专家分月排行榜第二2006年4月 PHP大版内专家分月排行榜第二2007年1月 PHP大版内专家分月排行榜第二
2013年10月 其他开发语言大版内专家分月排行榜第三2007年2月 PHP大版内专家分月排行榜第三
匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。[代码全屏查看]-Python对Excel进行读写
[1].[文件]
Python_EXCEL.py&~&7KB&&&&(5)
#coding:utf=8
__author__ = 'ZZ'
import natsort
from operator import itemgetter
from datetime import datetime,timedelta
from xlrd import open_workbook, cellname, xldate_as_tuple,error_text_from_code
from xlwt import easyxf, Workbook
from xlutils.copy import copy
from version import VERSION
_version_ = VERSION
class ExcelDriverLibrary:
def __init__(self):
self.wb = None
self.rb = None
self.Pid = None
self.rowIndex = None
self.sheetNum = None
self.SheetNames = None
self.fileName = None
def open_excel(self,filename):
tempDir = 'D:\\Codes\\PycharmProjects\\ExcelDataDriver\\ResFile\\'
# filename = 'TestFile.xls'
if filename.find(':')==1:
self.rb = open_workbook(filename,formatting_info=True)
print 'Opening file at %s' % filename
#self.wb = open_workbook(os.path.join("/",self.tmpDir,filename),formatting_info=True, on_demand=True)
self.rb = open_workbook(os.path.join(tempDir,filename),formatting_info=True, on_demand=True)
filename = os.path.join(tempDir,filename)
self.fileName = filename
self.SheetNames = self.rb.sheet_names
except Exception,e:
print str(e)
def get_sheet_names(self):
SheetNames = self.rb.sheet_names()
return SheetNames
def get_number_of_sheets(self):
sheetNum = self.rb.nsheets
return sheetNum
def get_column_count(self,SheetName):
sheet = self.rb.sheet_by_name(SheetName)
return sheet.ncols
def get_row_count(self,SheetName):
sheet = self.rb.sheet_by_name(SheetName)
return sheet.nrows
def get_column_values(self,SheetName,column,includeEmptyCells=True):
sheet = self.rb.sheet_by_name(SheetName)
for row_index in range(sheet.nrows):
cell = cellname(row_index,int(column))
value = sheet.cell(row_index,int(column)).value
data[cell] = value
if includeEmptyCells is True:
sortedData = natsort.natsorted(data.items(),key=itemgetter(0))
return sortedData
data = dict([(k,v) for (k,v) in data.items() if v])
OrderedData = natsort.natsorted(data.items(),key=itemgetter(0))
return OrderedData
def get_row_values(self,SheetName,row,includeEmptyCells=True):
sheet = self.rb.sheet_by_name(SheetName)
for col_index in range(sheet.ncols):
cell = cellname(int(row),col_index)
value = sheet.cell(int(row),col_index).value
data[cell] = value
if includeEmptyCells is True:
sortedData = natsort.natsorted(data.items(),key=itemgetter(0))
return sortedData
data = dict([(k,v) for (k,v) in data.items() if v])
OrderedData = natsort.natsorted(data.items(),key=itemgetter(0))
return OrderedData
def get_sheet_values(self,SheetName,includeEmptyCells=True):
sheet = self.rb.sheet_by_name(SheetName)
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
cell = cellname(row_index,col_index)
value = sheet.cell(row_index,col_index).value
data[cell] = value
if includeEmptyCells is True:
sortedData = natsort.natsorted(data.items(),key=itemgetter(0))
return sortedData
data = dict([(k,v) for (k,v) in data.items() if v])
OrderedData = natsort.natsorted(data.items(),key=itemgetter(0))
return OrderedData
def get_workboot_value(self,includeEmptyCells=True):
sheetData = []
workbookData = []
for sheet_name in self.SheetNames:
if includeEmptyCells is True:
sheetData = self.get_sheet_values(sheet_name)
sheetData = self.get_sheet_values(sheet_name,False)
sheetData.insert(0,sheet_name)
workbookData.append(sheetData)
return workbookData
def read_cell_data_by_name(self,SheetName,cell_name):
Uses the cell name to return the data from that cell.
sheet = self.rb.sheet_by_name(SheetName)
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
cell = cellname(row_index,col_index)
if cell_name ==cell:
cellValue = sheet.cell(row_index,col_index).value
return cellValue
def read_cell_data_by_coordinates(self, SheetName, column, row):
Uses the column and row to return the data from that cell.
my_sheet_index = self.SheetNames.index(SheetName)
sheet = self.rb.sheet_by_index(my_sheet_index)
cellValue = sheet.cell(int(row), int(column)).value
return cellValue
def Get_Sheet_Index(self,SheetName):
sheetnum = self.get_number_of_sheets()
for sheet_index in range(sheetnum):
Current_SheetName =
self.rb.sheet_names()[sheet_index]
if SheetName == Current_SheetName.encode('gb2312').decode('gb2312'):
return sheet_index
def Get_Cell_Data_By_PID(self,SheetName,Pid,CellName):
sheet = self.rb.sheet_by_name(SheetName)
for row_index in range(sheet.nrows):
Cell_Value = sheet.cell(int(row_index),0).value
if Pid == Cell_Value:
for col_index in range(sheet.ncols):
Cell_TitValue = sheet.cell(0,int(col_index)).value
if CellName == Cell_TitValue:
cellValue = sheet.cell(row_index, col_index).value
return cellValue
def Modify_index_cell(self,SheetName,Pid,CellName,CellValue):
CellName_index = self._get_CellName_index(CellName,SheetName)
Pid_index = self._get_PID_index(Pid,SheetName)
rb = open_workbook(self.fileName,formatting_info=True)
sheet_index = rb._sheet_names.index(SheetName)
except Exception,e:
print u'请输入正确的Sheet名,如果是中文,记得在变量名前面加u.具体错误消息为：',e
wb =copy(rb)
sheet = wb.get_sheet(sheet_index)
sheet.write(Pid_index,CellName_index,CellValue)
wb.save(self.fileName)