python导入EXCEL文件编码报错

文章目录

• 背景说明：
• 前置条件：
• 硬件环境：
• 编码过程要点

• 1. 头部引入
• 2. excel读取
• 3. mysql写入
• 4. 整体代码

• 总结
• 参考

背景说明：

基于Jira设计了一套研发的KPI考核的机制，主要采用任务+工时的形式，分为工作量+质量+规范的考核体系。
数据都从Jira导出，经过规则计算后，生成对应的每个人的考核数据和分值。
现在是前期探索，所以可能中间算法或者数据来源都有变更，目前还不适合编码固定算法，所以现在先使用excel的透视表功能。
将基础数据放在A.xlsx，透视表在B.xlsx中，数据源指定到A。这样以后A的基础数据更新后，B直接刷新透视表即可变为最新数据。
现在问题是员工希望了解自己的基础数据，在excel中不太方便查询，所以想要自动导入数据库中，通过BI系统可以让员工自行查看分析基础数据。

前置条件：

Jira和BI都是现有的系统，没有研发人员，基础数据A是由研发助理手动处理的。我设想最简单的方法就是让助理通过web直接上传至服务器，服务器上写一个脚本解析excel并且插入对应的数据表中，BI就可以直接连接数据库提供查询界面了。
shell脚本解析和操作数据库比较困难，所以就把目光放在了python身上。

硬件环境：

• 操作系统：CentOS 6.5
• 数据库：Mysql 5.6.41
• 编程语言：Python 2.7.3 Python 开发者年度调查报告,Python3 采用率超 84% 推荐使用Python3
• 编码工具：Vim (只是一段很小的脚本，就直接在服务器上用vim编写了，但是linux默认的颜色渲染还是很好的，如图)

编码过程要点

1. 头部引入

import MySQLdb as mdb # mysql
import os
import sys
import re
import xlrd # 读取 excel

引用要产生效果，需要安装对应的包
python提供了很多的外部包调用来解决问题，我们本次的重点是excel 和myslq 所以需要引用 MySQLdb 和 xlrd，在系统命令行执行以下命令即可：

pip install MySQL-python
pip install xlrd

这样就安装了mysql和excel的支持

2. excel读取

wb = xlrd.open_workbook('%s%s' %(filepath,excel.split(',')[0]))
s01 = wb.sheet_by_name('S01-本月workpoint原始数据'.decode('utf-8'))
s02 = wb.sheet_by_name('S02-上月跨本月工时原始数据'.decode('utf-8'))
s03 = wb.sheet_by_name('S03-本月跨下月工时原始数据'.decode('utf-8'))
s04 = wb.sheet_by_name('S04-在线问题工时原始数据'.decode('utf-8'))

一行代码 xlrd.open_workbook 就直接打开了excel ，wb.sheet_by_name就直接将行列数据读取为数组，完全没有废话。

3. mysql写入

mysql的操作比较常规，主要分为两大块，连接mdb.connect()，游标connect.cursor()
connect负责建立连接通道，cursor负责具体操作执行

# 建立连接
conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='dbname', charset='utf8')

# 使用cursor()方法获取操作游标
cursor = conn.cursor()

# 执行插入语句
sSql = 'insert into z_kpi_s01 (k_month,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10,col_11,col_12) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.executemany(sSql,values)

4. 整体代码

整体代码中还使用到了：

• for循环
• if判断
• 正则判断
• 打印带参数文字

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import os
import sys
import re
import xlrd

conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='dbname', charset='utf8')

# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。

try:
    # 读取Excel文件，并且确认是否需要导入
    needImportXlsx = []

    filepath = '/data/'
    pathDir = os.listdir(filepath)
    print 'list size is : %s' % len(pathDir)
    for allDir in pathDir:
        fnCount = cursor.execute('select filename from fileattachment where id=%s' % allDir)
        filename = cursor.fetchone()[0]

        canMatch = re.match(r'\d{6}', filename)
        if not (canMatch):
            continue

        tmpCount = cursor.execute('select * from z_kpi_s01 where k_month=%s' % filename)
        print '%s tmp count is : %s ' % (filename, tmpCount)
        if tmpCount == 0:
            needImportXlsx.append(allDir + ',' + filename)

    if len(needImportXlsx) == 0:
        print '!!!no file need import!!!'
        sys.exit()

    print 'all need import xlsx are : %s ' % needImportXlsx

    for excel in needImportXlsx:
        wb = xlrd.open_workbook('%s%s' % (filepath, excel.split(',')[0]))
        s01 = wb.sheet_by_name('S01-本月workpoint原始数据'.decode('utf-8'))
        s02 = wb.sheet_by_name('S02-上月跨本月工时原始数据'.decode('utf-8'))

        print 's01 has col %d row %d \n s02 has col %d row %d ' % (
            s01.ncols, s01.nrows, s02.ncols, s02.nrows)

        # s01 insert mysql
        values = []

        for i in range(s01.nrows):
            if i == 0:
                continue
            value = s01.row_values(i)[:12]
            value.insert(0, excel.split(',')[1])
            values.append(value)

        sSql = 'insert into z_kpi_s01 (k_month,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10,col_11,col_12) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        cursor.executemany(sSql, values)

        # s02 insert mysql
        values = []

        for i in range(s02.nrows):
            if i == 0:
                continue
            value = s02.row_values(i)[:31]
            value.insert(0, excel.split(',')[1])
            values.append(value)

        sSql = 'insert into z_kpi_s02 (k_month,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10,col_11,col_12,col_13,col_14,col_15,col_16,col_17,col_18,col_19,col_20,col_21,col_22,col_23,col_24,col_25,col_26,col_27,col_28,col_29,col_30,col_31) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        cursor.executemany(sSql, values)

    # 如果没有设置自动提交事务，则这里需要手动提交一次
    conn.commit()
except:
    import traceback

    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

总结

在这个之前还从来没有写过Python，写整体代码大概花了2个小时左右，其他时间花在服务器上面的安装和一些权限上。整体感觉Python的优势很明显，作为一种粘合代码，执行一些Shell脚本比较难实现的功能，是非常方便的。语法也很简单，但是对于我这样的Java开发者有时候习惯性的 {} 和 ; 反而是错误。