工作遇见统计流量,只能把网页里的复制到excel表格里,看到表格里的数据无比的头疼,只能用万能的Python来解决(写的很简单又错误或者有更好方法可以指点指点):
下载安装xlrd:
tar zxvf xlrd-0.9.4.tar.gz
cd xlrd-0.9.4
python setup.py install
>>> import xlrd#不报错说明安装成功
>>>
Python来操作excel文件:
#!/usr/local/python2.7
import string
import xlrd
import os
def read_excel(arg):
data=xlrd.open_workbook(arg)#打开文件
sheet1_name=data.sheet_names()[0]
sheet1 = data.sheet_by_name("Sheet1")#获取sheet
cols = sheet1.col_values(1)#获取第三列
sum=0
for str in cols:
if "GB" in str:#获取符合的数据,再求和
data=str.split(" ")[0].replace(",","")
sum += string.atof(data)
return sum
if __name__ == "__main__":
while True:
file_name=raw_input("Please Input you FileName:")
if os.path.exists(file_name):
print read_excel(file_name)
else:
print "The File Is Inexistence,Please Input FileName !!"
稍微改进了一下,只需要把excel文件放到已知的目录里,输入目录名,就可以格式化输出了:
#!/usr/local/python2.7
import string
import xlrd
import os
def read_excel(dir):
file_list=os.listdir(dir)
for file in file_list:
file_path=os.path.join(dir,file)
data=xlrd.open_workbook(file_path)
sheet1_name=data.sheet_names()[0]
sheet1 = data.sheet_by_name("Sheet1")
cols = sheet1.col_values(1)
sum=0
for str in cols:
if "GB" in str:
data=str.split(" ")[0].replace(",","")
sum += string.atof(data)
print file,sum
if __name__ == "__main__":
Catalog_name=raw_input("Please Input you CatalogName:")
if os.path.exists(Catalog_name):
read_excel(Catalog_name)
else:
print "The File Is Inexistence,Please Input !!"