python xlrd pandas_【python操作Excel的常见方法汇总】 xlrd pandas xlwings

用python处理Excel数据，实现Excel的功能：分列、透视等功能

1. Excel 解压文件

#解压tar_path中的压缩文件到uzipPath

defunzip_archive(tar_path):print('#######解压文件夹，',tar_path)for i inos.listdir(tar_path):if i.endswith('zip'):print(i)

shutil.unpack_archive(tar_path+ "/" + i,unzipPath)

2. Excel的基本打开读写、获取列

常见的Excel操作包括xlrd、pandas、xlwings，基本操作包括打开、读取、获取单元格值、写入等

xlrd

data = xlrd.open_workbook(i) #打开表

sheet = data.sheet_by_index(0) #按照index获取工作簿

sheet = data.sheet_by_name('sheet1') #按照工作簿名获取工作簿

nrows = sheet.nrows #获取行数

ncols = sheet.ncol # 获取列数

row_value = sheet.row_values(i)

col_value = sheet.col_values(j)

用xlsxwriter写入到Excel

划重点：write_row(row,col,data) 用了enumerate自动匹配索引和迭代的值

#将alist写入tarfile的名为name的工作簿中

def insert_file(alist,tarfile,name='sheet1'):print("####将透视表写入到",tarfile)

wh=xlsxwriter.Workbook(tarfile)

wadd=wh.add_worksheet(name)if len(alist) >0 :for row_num,row_data inenumerate(alist):

wadd.write_row(row_num,0,row_data)

补充：enumerate的用法

enumerate() 函数用于将一个可遍历的数据对象(如列表、元组或字符串)组合为一个索引序列，同时列出数据和数据下标

代码是示意结果

alist = [['1','1-1'],['2','2-2'],['3','3-3']]for row_num, row_data inenumerate(alist):print(row_num,row_data)

View Code

pandas

xlwings

xlwings的好处在于可以直接获得某列的指定位置的数据，缺点是 默认自动打开读取的文件，甚至关了运行程序后台还是显示这个文件是打开的……

设置visible之后 只是前段看不见，但是默认的还是会打开……

但是在获取数据上还是很方便的

importxlwings as xw

app= xw.App(visible=True, add_book=False)

wb= app.books.open(file) #打开file

sht =wb.sheets[0]

dateLong= sht.range(f'H3:H{nrows}').value

3.数据透视表

#根绝tarfile生成数据透视表

df =pd.read_excel(tarfile)

pd.pivot_table(df,index=[u'日期',u'首次访问'],values=[u'手机',u'会话效果'],aggfunc=[np.count_nonzero,np.count_nonzero])

但是这种方法写出的透视表数据不美观，后续需要调整，所以自己写了个计数算法

算法思想是：空为0非空为1，计算

lista = [[0 if temp=='' else 1 for temp in list] for list ina]for i inrange(len(a)):

kl= a[i][:2]if type(kl[0]) == float or type(kl[0]) ==int:

kl[0]= getdate(kl[0]) #从数字型日期转为字符串型日期

key= (kl[0], kl[1]) #前两列作为做透视的key

val = lista[i][2:] #h后两列是值

if key not indicta.keys():

dicta[key]=valelse:

raw=dicta[key]

dicta[key]= [raw[i] + val[i] for i inrange(len(raw))]

result= [list(key) + dicta[key] for key in dicta.keys()]

4. 获取文件内的所有文件

defget_filename(tar_path):for root,dirs,files inos.walk(tar_path):for i infiles:if i.endswith('xls') or i.endswith('xlsx'):print(i)

dir.append(tar_path+ "/" +i)print('#####解压后的Excel文件',dir)return dir

这里深入了解了os的几个常用函数，包括获取指定目录下的文件、

#获取指定目录下的文件夹或文件的名字列表

a =os.listdir(path)print(a)#打开文件

f = os.open("",os.O_RDWR|os.O_CREAT)

os.write(f,"this is test")#重命名

os.rename(src,dst)

os.renames(old,new)#递归地更名#删除

os.remove(path)

os.removedirs(path)#删除递归目录#遍历目录

os.walk()

View Code