python 解析excel模板_python 解析Excel

python 解析Excel

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

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

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

翠花，上代码：

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

# coding=utf-8

from openpyxl.workbook import Workbook

from openpyxl.writer.excel import ExcelWriter

from openpyxl.styles import Color, Fill

from openpyxl.cell import Cell

import datetime

from pymongo import MongoClient

import pymongo

import smtplib

from email.MIMEText import MIMEText

from email.MIMEMultipart import MIMEMultipart

from email.MIMEBase import MIMEBase

from email import Encoders

import time

mongoDB = 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 True

def 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 True

def 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 False

def getEmail(row):＃这也是

try:

if row["descriptions"]["contactEmail"] != "" and row["descriptions"]["contactEmail"] != None:

return True

else:

return False

except Exception as e:

return False

def getPhone(row):＃这也是

try:

if row["descriptions"]["contactPhoneNumber"] != "" and row["descriptions"]["contactPhoneNumber"] != None:

return True

else:

return False

except Exception as e:

return False

now = datetime.datetime.now()

#新建一个workbook

wb = Workbook()

#第一个sheet是ws

ws = 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.0

ws.column_dimensions["B"].width =25.0

ws.column_dimensions["C"].width =10.0

ws.column_dimensions["D"].width =10.0

ws.column_dimensions["E"].width =15.0

ws.column_dimensions["F"].width =20.0

ws.column_dimensions["G"].width =15.0

ws.column_dimensions["H"].width =25.0

ws.column_dimensions["I"].width =35.0

ws.column_dimensions["J"].width =35.0

ws.column_dimensions["K"].width =15.0

ws.column_dimensions["L"].width =15.0

ws.column_dimensions["M"].width =10.0

#文件Name

file_name = str(now.strftime("%Y%m%d")) + "(2).xlsx"

#文件存放地址

file_dir = '/usr/src/Python'

##保存生成xlsx

wb.save(filename = str(file_name))

ew = ExcelWriter(workbook = wb)

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

python读取excel文件代码：

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# 读取excel数据

# 小罗的需求，取第二行以下的数据，然后取每行前13列的数据

import xlrd

data = xlrd.open_workbook('test.xls') # 打开xls文件

table = data.sheets()[0] # 打开第一张表

nrows = table.nrows # 获取表的行数

for i in range(nrows): # 循环逐行打印

if i == 0: # 跳过第一行

continue

print 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

#打开一个workbook

workbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')

#抓取所有sheet页的名称

worksheets = workbook.sheet_names()

print('worksheets is %s' %worksheets)

#定位到sheet1

worksheet1 = 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中所有行row

num_rows = worksheet1.nrows

for curr_row in range(num_rows):

row = worksheet1.row_values(curr_row)

print('row%s is %s' %(curr_row,row))

#遍历sheet1中所有列col

num_cols = worksheet1.ncols

for curr_col in range(num_cols):

col = worksheet1.col_values(curr_col)

print('col%s is %s' %(curr_col,col))

#遍历sheet1中所有单元格cell

for rown in range(num_rows):

for coln in range(num_cols):

cell = worksheet1.cell_value(rown,coln)

print cell

"""

#其他写法：

cell = worksheet1.cell(rown,coln).value

print cell

#或

cell = worksheet1.row(rown)[coln].value

print cell

#或

cell = worksheet1.col(coln)[rown].value

print cell

#获取单元格中值的类型，类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

cell_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 xlrd

import xlutils.copy

#打开一个workbook

rb = 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 = True

mystyle = pyExcelerator.XFStyle()

mystyle.font = myfont

#写入数据，使用样式

ws.write(0,0,u'ni hao 帕索！',mystyle)

#保存该excel文件,有同名文件时直接覆盖

wb.save('E:\\Code\\Python\\mini.xls')

print '创建excel文件完成！'