xlrd和xlwt模块进行Excel文件的读取与写入

         xlrd和xlwt模块进行Excel文件的读取与写入

目录

安装

使用介绍

使用中遇到的问题

code

---

安装

pip install xlrd
pip install xlwt

 

使用介绍

1、导入模块 

import xlrd

2、打开Excel文件读取数据 

data = xlrd.open_workbook('excelFile.xls')

3、使用技巧

获取一个工作表 

table = data.sheets()[0] #通过索引顺序获取 
table = data.sheet_by_index(0) #通过索引顺序获取 
table = data.sheet_by_name(u'Sheet1')#通过名称获取

获取整行和整列的值（数组） 

table.row_values(i) 
table.col_values(i)

获取行数和列数 

nrows = table.nrows 
ncols = table.ncols

循环行列表数据 

for i in range(nrows ): 
print table.row_values(i)

单元格 

cell_A1 = table.cell(0,0).value 
cell_C4 = table.cell(2,3).value

使用行列索引

cell_A1 = table.row(0)[0].value 
cell_A2 = table.col(1)[0].value

简单的写入 

row = 0 
col = 0 
# 类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error 
ctype = 1 value = '单元格的值' 
xf = 0 # 扩展的格式化 
table.put_cell(row, col, ctype, value, xf) 
table.cell(0,0)  #单元格的值' 
table.cell(0,0).value #单元格的值'

使用中遇到的问题

在读取excel的时候,会发现执行结果出现报错：

  File "E:\Python\lib\site-packages\xlrd\compdoc.py", line 426, in _locate_stream
    raise CompDocError("%s corruption: seen[%d] == %d" % (qname, s, self.seen[s]))
xlrd.compdoc.CompDocError: Workbook corruption: seen[2] == 4

如果在电脑上直接打开excel，excel是正常的。看报错信息，来自于源文件compdoc.py，打开该文件，找到426行代码，代码如下：

if self.seen[s]:
    print("_locate_stream(%s): seen" % qname, file=self.logfile); dump_list(self.seen, 20, self.logfile)
    raise CompDocError("%s corruption: seen[%d] == %d" % (qname, s, self.seen[s]))

这是主动抛异常，将代码全部注释起来，再执行代码，发现读取正常。

code

#!/usr/local/python3.5/bin/python3
# -*- encoding:utf-8 -*-
# 根据xls文件内容读取充值订单号
# 根据充值号导出每个订单的相关信息：[ '充值订单号', , '充值类型', '商户', '账户']
# 最后将订单信息生成为xls文件
# 使用说明，命令行输入
#python3 export_complain.py --filename=data.xls

import sys
import pymysql
import traceback
import time
import datetime
import json
import xlwt
import xlrd
import argparse

# 获取命令行的参数
parser = argparse.ArgumentParser(description='manual to this script')
parser.add_argument('--filename', type=str, default='data.xls')
args = parser.parse_args()

complain_list = []
result = []
senders = {}

class Complain:
    charge_order_id = ''
    charge_type = ''
    market_type = ''
    id = ''
    def __str__(self):
        return self.charge_order_id

def exportData(cursor, complain):
    charge_sql = "select charge_order_id, charge_type, market_type, id from charge where charge_order_id = \'%s\'"
    full_charge_sql = charge_sql % complain.charge_order_id
    cursor.execute(full_charge_sql)
    charge_datas = cursor.fetchall()
    for row in charge_datas:
        market_type = str(row[4])
        charge_type = handle_charge_type(str(row[3]))
        if market_type in senders:
            market_type = senders[market_type]
        complain.charge_type = charge_type
        complain.market_type = market_type
        complain.id = str(row[5])
        result.append(complain)

def readFile():
    data = xlrd.open_workbook(args.filename)
    table = data.sheets()[0]
    nrows = table.nrows
    print("读取数据行数" + str(nrows))
    for i in range(1, nrows):
        rows_values = table.row_values(i)
        complain = Complain()
        complain.charge_order_id = str(rows_values[6])
        complain_list.append(complain)

def handle_charge_type(charge_type):
    if charge_type.find("WXPAY") != -1:
        return '微信'
    elif charge_type.find("ALIPAY") != -1:
        return '支付宝'
    else:
        return charge_type

def writeResultToFile():
    file = xlwt.Workbook(encoding='utf-8')
    table = file.add_sheet('result', cell_overwrite_ok=True)
    first_col = table.col(0)
    sec_col = table.col(1)
    third_col = table.col(2)
    four_col = table.col(3)
    # 设置宽度，256为衡量单位，20表示20个字符宽度
    first_col.width = 256 * 20
    sec_col.width = 256 * 20
    third_col.width = 256 * 10
    four_col.width = 256 * 10
    table_head = [ '充值订单号', '充值类型', '商户', '账号']
    print("开始写入")
    for i in range(len(table_head)):
        table.write(0, i, table_head[i])
    for row in range(len(complain_list)):
        table.write(row + 1, 0, complain_list[row].charge_order_id)
        table.write(row + 1, 1, complain_list[row].charge_type)
        table.write(row + 1, 2, complain_list[row].market_type)
        table.write(row + 1, 3, complain_list[row].id)
    print("结束写入")
    today = datetime.datetime.now()
    offset = datetime.timedelta(days=-1)
    re_date = (today + offset).strftime('%Y-%m-%d')        
    file.save(re_date + '投诉相关信息.xls')

def initSenders():
    try:
        cursor = core_db.cursor()
        sql = "select sender, sender_name from sender"
        cursor.execute(sql)
        datas = cursor.fetchall()
        for row in datas:
            sender_name_dict = json.loads(row[1]) #处理json字符串串
            senders[row[0]] = sender_name_dict['china']
    except Exception:
        traceback.print_exc()
    finally:
        core_db.close()

if __name__ == "__main__":
    # 数据库
    #db = pymysql.connect(host="127.0.0.1", user="nwj", password="nwj", database="nwj", port=3307, charset='utf8')

    # 核心数据库
    #core_db = pymysql.connect(host="127.0.0.1", user="nwj", password="nwj", database="nwj", port=3306, charset='utf8')

    initSenders()
    try:
        cursor = db.cursor()
        readFile()
        for complain in complain_list:
            exportData(cursor, complain)
        writeResultToFile()
    except Exception:
        traceback.print_exc()
    finally:
        print('close')
        db.close()