python 解析Excel

　　公司背景：好吧LZ太懒了.略...

　　原由起因：公司老板发话要导出公司数据库中符合条件的数据，源数据有400万，符合条件的大概有70万左右吧.

　　最终目的：符合条件的数据并生成Excel

　　翠花，上代码：

　　由于LZ python的底子并不是很好只会写一些简单的脚本，全当是记录学习里程了。此次采用的是openpyxl，因为查到它支持Excel2010

　　

# coding=utf-8from openpyxl.workbook import Workbookfrom openpyxl.writer.excel import ExcelWriterfrom openpyxl.styles import Color, Fillfrom openpyxl.cell import Cellimport datetimefrom pymongo import MongoClientimport pymongoimport smtplibfrom email.MIMEText import MIMETextfrom email.MIMEMultipart import MIMEMultipartfrom email.MIMEBase import MIMEBasefrom email import Encodersimport timemongoDB = MongoClient('beta-mongo01')＃公司数据库采用的是mongodb　别问我为什么LZ也不知道为毛不用关系型数据库db_name = 'core'db = mongoDB[db_name]rows = db.customerProfiles.find()filters = ["理财","金融","证劵","咨询","银行","财务","信托","基金","期货","租赁","投资","保险","会计","审计","投行","券商","股权","风险","财务","财富","资产"]#这是筛选条件　自己猜我们是干啥的吧...def getfiltersByexperiences(experiences):＃这是数据筛选	for i in filters:		if i.decode('utf-8') in experiences:			return Truedef getExperieces(row):＃筛选工作经历	try:		experiences = ""		i = 0		while i < len(row["workExperiences"]):			experiences = experiences + row["workExperiences"][i]["position"] + row["workExperiences"][i]["organization"]			i =i +1			continue		return getfiltersByexperiences(experiences)	except Exception as e:		experiences = ""		return getfiltersByexperiences(experiences)def getfiltersByexpect(expect):＃另一个筛选	for i in filters:		if i.decode('utf-8') in expect:			return Truedef getExpect(row):＃这也是	try:		expect = ""		i = 0		while i < len(row["expect"]["expectIndustry"]):			expect = expect + row["expect"]["expectIndustry"][i]			i =i +1			continue		return getfiltersByexpect(expect)	except Exception as e:		expect = ""		return getfiltersByexpect(expect)def getfullName(row):＃这也是	try:		if row["fullName"] != "" and row["fullName"] != None:			return True		else:			return False	except Exception as e:		return Falsedef getEmail(row):＃这也是	try:		if row["descriptions"]["contactEmail"] != "" and row["descriptions"]["contactEmail"] != None:			return True		else:			return False	except Exception as e:		return Falsedef getPhone(row):＃这也是	try:		if row["descriptions"]["contactPhoneNumber"] != "" and row["descriptions"]["contactPhoneNumber"] != None:			return True		else:			return False	except Exception as e:		return Falsenow = datetime.datetime.now()#新建一个workbookwb = Workbook()#第一个sheet是wsws = wb.worksheets[0]#设置ws的名称ws.title = u"简历数据"#给A1赋值ws.cell('A1').value = '%s'%("编号")ws.cell('B1').value = '%s'%("CustomerId")ws.cell('C1').value = '%s'%("姓名")ws.cell('D1').value = '%s'%("性别")ws.cell('E1').value = '%s'%("所在地")ws.cell('F1').value = '%s'%("邮箱")ws.cell('G1').value = '%s'%("电话")ws.cell('H1').value = '%s'%("曾经任职职位")ws.cell('I1').value = '%s'%("曾经任职公司")ws.cell('J1').value = '%s'%("期望行业")ws.cell('K1').value = '%s'%("工作年份")ws.cell('L1').value = '%s'%("简历更新时间")ws.cell('M1').value = '%s'%("简历来源")count = 2 for row in rows:＃循环取数据	if getfullName(row) == True:		if getEmail(row) == True or getPhone(row) == True:			if getExperieces(row) == True or getExpect(row) == True:				count = count +1				if count >200002:					try:						position = ""						if len(row["workExperiences"]) == 0:							pass						else:							i = 0 							while i < len(row["workExperiences"]):								position = position + row["workExperiences"][i]["position"] + "/"								i =i +1								continue					except Exception as e:						position = ""					try:						organization = ""						if len(row["workExperiences"]) == 0:							pass						else:							i = 0 							while i < len(row["workExperiences"]):								organization = organization + row["workExperiences"][i]["organization"] + "/"								i =i +1								continue									except Exception as e:						organization = ""					try:						expectedIndustry = ""						if len(row["expect"]["expectedIndustry"]) == 0:							pass						else:							i = 0 							while i < len(row["expect"]["expectIndustry"]):								expectedIndustry = expectedIndustry + row["expect"]["expectIndustry"][i]								i =i +1								continue					except Exception as e:						expectedIndustry = ""					try:＃开始写ｅｘｃｅｌ						ws.cell(str('A'+str(count))).value = '%s'%(str(count-1))						ws.cell(str('B'+str(count))).value = '%s'%(str(row.get("_id","")))						ws.cell(str('C'+str(count))).value = '%s'%(row.get("fullName",""))						ws.cell(str('D'+str(count))).value = '%s'%(row.get("gender",""))						ws.cell(str('E'+str(count))).value = '%s'%(row.get("descriptions","").get("city",""))						ws.cell(str('F'+str(count))).value = '%s'%(row.get("descriptions","").get("contactEmail",""))						ws.cell(str('G'+str(count))).value = '%s'%(row.get("descriptions","").get("contactPhoneNumber",""))						ws.cell(str('H'+str(count))).value = '%s'%(position)						ws.cell(str('I'+str(count))).value = '%s'%(organization)						ws.cell(str('J'+str(count))).value = '%s'%(expectedIndustry)						ws.cell(str('K'+str(count))).value = '%s'%(str(row.get("descriptions","").get("workLife","")) + "年")						ws.cell(str('L'+str(count))).value = '%s'%(str(row.get("updateTime","2015-05-05 00:00:00"))[0:11])						ws.cell(str('M'+str(count))).value = '%s'%(str(row.get("source","")))					except Exception as e:						count = count +1						continue					print(count)					if count == 500002:						break				else:					continue			else:				continue				else:			continue	else:		continue		##修改某一列宽度ws.column_dimensions["A"].width =10.0ws.column_dimensions["B"].width =25.0ws.column_dimensions["C"].width =10.0ws.column_dimensions["D"].width =10.0ws.column_dimensions["E"].width =15.0ws.column_dimensions["F"].width =20.0ws.column_dimensions["G"].width =15.0ws.column_dimensions["H"].width =25.0ws.column_dimensions["I"].width =35.0ws.column_dimensions["J"].width =35.0ws.column_dimensions["K"].width =15.0ws.column_dimensions["L"].width =15.0ws.column_dimensions["M"].width =10.0#文件Namefile_name = str(now.strftime("%Y%m%d")) + "(2).xlsx"#文件存放地址file_dir = '/usr/src/Python'##保存生成xlsxwb.save(filename = str(file_name))ew = ExcelWriter(workbook = wb)

还有一些从网上找的记录下来　各种方法...

python读取excel文件代码：

#!/usr/bin/env python# -*- coding: utf-8 -*-# 读取excel数据# 小罗的需求，取第二行以下的数据，然后取每行前13列的数据import xlrddata = xlrd.open_workbook('test.xls') # 打开xls文件table = data.sheets()[0] # 打开第一张表nrows = table.nrows # 获取表的行数for i in range(nrows): # 循环逐行打印if i == 0: # 跳过第一行continueprint table.row_values(i)[:13] # 取前十三列

使用xlrd读取文件，使用xlwt生成Excel文件（可以控制Excel中单元格的格式）。但是用xlrd读取excel是不能对其进行操作的；而 xlwt生成excel文件是不能在已有的excel文件基础上进行修改的，如需要修改文件就要使用xluntils模块。pyExcelerator模 块与xlwt类似，也可以用来生成excel文件。

#coding=utf-8########################################################filename:test_xlrd.py#author:defias#date:xxxx-xx-xx#function：读excel文件中的数据#######################################################import xlrd#打开一个workbookworkbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')#抓取所有sheet页的名称worksheets = workbook.sheet_names()print('worksheets is %s' %worksheets)#定位到sheet1worksheet1 = workbook.sheet_by_name(u'Sheet1')"""#通过索引顺序获取worksheet1 = workbook.sheets()[0]#或worksheet1 = workbook.sheet_by_index(0)""""""#遍历所有sheet对象for worksheet_name in worksheets:worksheet = workbook.sheet_by_name(worksheet_name)"""#遍历sheet1中所有行rownum_rows = worksheet1.nrowsfor curr_row in range(num_rows):row = worksheet1.row_values(curr_row)print('row%s is %s' %(curr_row,row))#遍历sheet1中所有列colnum_cols = worksheet1.ncolsfor curr_col in range(num_cols):col = worksheet1.col_values(curr_col)print('col%s is %s' %(curr_col,col))#遍历sheet1中所有单元格cellfor rown in range(num_rows):for coln in range(num_cols):cell = worksheet1.cell_value(rown,coln)print cell"""#其他写法：cell = worksheet1.cell(rown,coln).valueprint cell#或cell = worksheet1.row(rown)[coln].valueprint cell#或cell = worksheet1.col(coln)[rown].valueprint cell#获取单元格中值的类型，类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorcell_type = worksheet1.cell_type(rown,coln)print cell_type"""

#coding=utf-8########################################################filename:test_xlwt.py#author:defias#date:xxxx-xx-xx#function：新建excel文件并写入数据#######################################################import xlwt#创建workbook和sheet对象workbook = xlwt.Workbook() #注意Workbook的开头W要大写sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)sheet2 = workbook.add_sheet('sheet2',cell_overwrite_ok=True)#向sheet页中写入数据sheet1.write(0,0,'this should overwrite1')sheet1.write(0,1,'aaaaaaaaaaaa')sheet2.write(0,0,'this should overwrite2')sheet2.write(1,2,'bbbbbbbbbbbbb')"""#-----------使用样式-----------------------------------#初始化样式style = xlwt.XFStyle() #为样式创建字体font = xlwt.Font()font.name = 'Times New Roman'font.bold = True#设置样式的字体style.font = font#使用样式sheet.write(0,1,'some bold Times text',style)"""#保存该excel文件,有同名文件时直接覆盖workbook.save('E:\\Code\\Python\\test2.xls')print '创建excel文件完成！'

#coding=utf-8########################################################filename:test_xlutils.py#author:defias#date:xxxx-xx-xx#function：向excel文件中写入数据#######################################################import xlrdimport xlutils.copy#打开一个workbookrb = xlrd.open_workbook('E:\\Code\\Python\\test1.xls') wb = xlutils.copy.copy(rb)#获取sheet对象，通过sheet_by_index()获取的sheet对象没有write()方法ws = wb.get_sheet(0)#写入数据ws.write(1, 1, 'changed!')#添加sheet页wb.add_sheet('sheetnnn2',cell_overwrite_ok=True)#利用保存时同名覆盖达到修改excel文件的目的,注意未被修改的内容保持不变wb.save('E:\\Code\\Python\\test1.xls')

#coding=utf-8########################################################filename:test_pyExcelerator_read.py#author:defias#date:xxxx-xx-xx#function：读excel文件中的数据#######################################################import pyExcelerator#parse_xls返回一个列表，每项都是一个sheet页的数据。#每项是一个二元组(表名,单元格数据)。其中单元格数据为一个字典，键值就是单元格的索引(i,j)。如果某个单元格无数据，那么就不存在这个值sheets = pyExcelerator.parse_xls('E:\\Code\\Python\\testdata.xls')print sheets

#coding=utf-8########################################################filename:test_pyExcelerator.py#author:defias#date:xxxx-xx-xx#function：新建excel文件并写入数据#######################################################import pyExcelerator#创建workbook和sheet对象wb = pyExcelerator.Workbook()ws = wb.add_sheet(u'第一页')#设置样式myfont = pyExcelerator.Font()myfont.name = u'Times New Roman'myfont.bold = Truemystyle = pyExcelerator.XFStyle()mystyle.font = myfont#写入数据，使用样式ws.write(0,0,u'ni hao 帕索！',mystyle)#保存该excel文件,有同名文件时直接覆盖wb.save('E:\\Code\\Python\\mini.xls')print '创建excel文件完成！'