如何用python批量处理excel数据_python批量处理Excel数据

一、flask代码展示（看代码）

# coding=utf-8

from app.common import RET

from . import admin

from dbConnection.MysqlConn import Mysql

from flask import request

import xlrd

import sys

import os

from app import util

sys.setdefaultencoding('utf8')

# 批量分配客户

@admin.route('/admin/xxxxx', methods=['POST'])

@util.login_required

def xxxxxxx():

data = dict()

data['code'] = RET.PARAMERR

data['msg'] = u'操作失败'

data['info'] = ''

redo_li = []

try:

header_file = request.files['file']

path = upload(header_file)

_data = xlrd.open_workbook(path)

table = _data.sheet_by_index(0) # 取sheet1表中的数据

# 依据下标获取特定sheet表 table.nrows # 获取该sheet表行数 table.ncols获取该sheet表列数

mysql = Mysql()

insert_sql = "insert into family_xxxx(uid, tig, distributor_name, service_name, add_time, service_id, appointment, appointment_service_time, team_id, o_id) VALUES "

insert_sql1 = "insert into family_xxxxx(uid, tig, distributor_name, team_name, add_time, team_id, appointment, appointment_service_time, o_id) VALUES "

# 对数据行进行循环（不包含第一行的字段行） for i in range(table.nrows) 为从第一行开始

for i in range(1, table.nrows):

k = list(table.row_values(i)) # 获取行数的数据

family_distribution = get_family_distribution(int(k[0])) # 判断用户是否已经分配咨询师

family_distribution_team = get_family_distributionteam(int(k[0])) # 判断用户是否已经分配团队

if family_distribution is None:

insert_sql += "('%s',1,'%s','%s', now(),'%s','%s','%s','%s','%s')," % (int(k[0]), k[1], k[4], int(k[5]), k[6], k[7], int(k[3]), int(k[8]))

if family_distribution_team is None:

insert_sql1 += "('%s',1,'%s','%s', now(),'%s','%s','%s','%s')," % (int(k[0]), k[1], k[2], int(k[3]), k[6], k[7], int(k[8]))

if family_distribution_team or family_distribution:

print family_distribution_team['o_id']

li.append(family_distribution_team['o_id'])

data['info'] = li

continue

insert_sql = insert_sql[:-1] # 插入ux数据

insert_sql1 = insert_sql1[:-1] # 插入ux数据

mysql.insertOne(insert_sql, None)

mysql.insertOne(insert_sql1, None)

mysql.dispose()

orders_detail(table)

data['code'] = RET.OK

data['msg'] = u"操作成功"

os.remove(path)

except Exception as e:

print e

data['code'] = RET.DATAERR

return util.to_json(data)

def get_family_xxxx(uid):

sql = "select * from family_distribution where uid= '%s'" % uid

mysql = Mysql()

res = mysql.getOne(sql, None)

mysql.dispose()

return res

def get_family_xxxxx(uid):

sql = "select * from family_distribution_team where uid= '%s'" % uid

mysql = Mysql()

res = mysql.getOne(sql, None)

mysql.dispose()

return res

def orders_detail(table):

mysql = Mysql() # 联系状态后添加客户意向产品类别 客户需求

for i in range(1, table.nrows):

k = list(table.row_values(i)) # 获取行数的数据 沟通情况变为跟进情况

print k

o_id = int(k[8])

print o_id

update_sql1 = " update orders set distribution_status=1,distributionteam_status=1,team_id='%s' where id = '%s'" % (int(k[3]),o_id)

mysql.update(update_sql1, None)

mysql.dispose()

print update_sql1

return 0

def upload(upFile):

pcsystem = sys.platform

# 保存至本地

name = "rate" + "." + str(upFile.filename.split('.')[-1:][0])

if pcsystem.startswith('win'):

base = 'c:/tmp_upload/'

if not os.path.exists(base):

os.makedirs(base)

upFile.save(os.path.join(base, name))

localPath = base + name

else:

base = '/tmp_upload/'

if not os.path.exists(base):

os.makedirs(base)

upFile.save(os.path.join(base, name))

localPath = base + name

return localPath

#

# import xlrd

# def multi_view(request):

# """

# 批量导入

# :param request:

# :return:

# """

# file_obj = request.FILES.get('exfile')

# with open('xxxxxx.xlsx', mode='wb') as f:

# for chunk in file_obj:

# f.write(chunk)

#

# workbook = xlrd.open_workbook('xxxxxx.xlsx')

# sheet = workbook.sheet_by_index(0) # 拿到excel中的第一个文件薄

# maps = {

# 0: 'name',

# 1: 'qq',

# }

# # maps是关于excel表中应该有的字段和字段顺序

# for index in range(1, sheet.nrows): # 对数据行进行循环（不包含第一行的字段行）

# row = sheet.row(index) # 获得行的列对象

# row_dict = {}

# for i in range(len(maps)):

# key = maps[i]

# cell = row[i]

# row_dict[key] = cell.value # cell.value获得列中的内容

# print(row_dict)

# 将数据录入数据库，如：UserInfo.objects.create(**row_dict)

二、Django代码展示_脚本（外部引用）

import xlrd

import sys

import pymysql

# 链接数据库

def mysql_link(de_name):

try:

db = pymysql.connect(host="127.0.0.1", user="xxx",

passwd="xxx",

db=xxx,

charset='utf8')

return db

except:

print("could not connect to mysql server")

'''

读取excel函数

args：excel_file（excel文件，目录在py文件同目录）

returns：book

'''

def open_excel(excel_file):

try:

book = xlrd.open_workbook(excel_file) # 文件名，把文件与py文件放在同一目录下

print(sys.getsizeof(book))

return book

except:

print("open excel file failed!")

'''

执行插入操作

args:db_name（数据库名称）

table_name(表名称）

excel_file（excel文件名，把文件与py文件放在同一目录下）

'''

def store_to(db_name, table_name, excel_file):

db = mysql_link(db_name) # 打开数据库连接

cursor = db.cursor() # 使用 cursor() 方法创建一个游标对象 cursor

list = [] # 定义列表用来存放数据

book = open_excel(excel_file) # 打开excel文件

sheets = book.sheet_names() # 获取所有sheet表名

for sheet in sheets:

sh = book.sheet_by_name(sheet) # 打开每一张表 也可以设置某一张表

row_num = sh.nrows

print(row_num)

num = 0 # 用来控制每次插入的数量

for i in range(1, row_num): # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1

row_data = sh.row_values(i) # 按行获取excel的值

value = (row_data[0], row_data[1], row_data[2], row_data[3], row_data[4], row_data[5], \

row_data[6], row_data[7], row_data[8], row_data[9], row_data[10], row_data[11], row_data[12],

row_data[13], row_data[14])

list.append(value) # 将数据暂存在列表

num += 1

if (num >= 10000): # 每一万条数据执行一次插入

print(sys.getsizeof(list))

sql = "INSERT INTO " + table_name + " (time, xingbie, afdd, xzb, yzb, cfbj, jjlbmc, \

bjlbmc, bjlxmc, bjlxxlmc, gxqymc,gxdwmc, afql, afxqxx, cjdwmc)\

VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

cursor.executemany(sql, list) # 执行sql语句

num = 0 # 计数归零

list.clear() # 清空list

print("worksheets: " + sheet + " has been inserted 10000 datas!") # < br > # 将剩下不足10000的数据执行插入

sql = "INSERT INTO " + table_name + " (time, xingbie, afdd, xzb, yzb, cfbj, jjlbmc, \

bjlbmc, bjlxmc, bjlxxlmc, gxqymc,gxdwmc, afql, afxqxx, cjdwmc)\

VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

cursor.executemany(sql, list) # 执行sql语句

print("worksheets: " + sheet + " has been inserted " + len(list) + " datas!")

list.clear() # 清空list

print("worksheets: " + sheet + " has been inserted " + str(row_num) + " datas!")

db.commit() # 提交

cursor.close() # 关闭连接

db.close()

if __name__ == '__main__':

store_to('demo', 'demo_yangben', 'xxx.xlsx') # excel文件格式必须为xlsx