Python对Excel的操作封装(导入导出，Json的处理)

import xlwt
import xlrd
import pymysql
import json


def style():
    # 创建格式style
    style = xlwt.XFStyle()
    # 创建font，设置字体
    font = xlwt.Font()
    # 字体格式
    font.name = 'Times New Roman'
    # 将字体font，应用到格式style
    style.font = font
    # 创建alignment，居中
    alignment = xlwt.Alignment()
    # 居中
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    # 应用到格式style
    style.alignment = alignment

    style1 = xlwt.XFStyle()
    font1 = xlwt.Font()
    font1.name = 'Times New Roman'
    # 字体颜色（绿色）
    # font1.colour_index = 3
    # 字体加粗
    font1.bold = True
    style1.font = font1
    style1.alignment = alignment


def export_excel_json(jsonfile,outputfile):
    #将json字符串转换为字典
    with open(jsonfile,encoding='utf-8') as f:
        json_all = json.load(f)
        print(json_all)

    title = ['id','name']
    book = xlwt.Workbook()  # 创建一个excel对象
    sheet = book.add_sheet('Sheet1', cell_overwrite_ok=True)  # 添加一个sheet页
    for i in range(len(title)):  # 循环列
        sheet.write(0, i, title[i])  # 将title数组中的字段写入到0行i列中

    row = 1  # 定义行
    for k in json_all:
        data = json_all[k]  # data保存id和name的list
        ###需要加入的东西
        # data.append(sum(data[1:4]))  # 倒数第二列加入xxx
        # data.append(sum(data[1:4]) / 3.0)  # 最后一列加入xxxx
        data.insert(0, k)  # 第一列加入序号
        for index in range(len(data)):  # 依次写入每一行
            sheet.write(row, index, data[index])
        row += 1
    book.save(outputfile)  # 保存excel





def conn_db():
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='root',
        db='test',
        charset='utf8'
    )
    return conn


###  导出excel
def export_excel(outputfile):
    conn = conn_db()
    #数据库游标
    cur = conn.cursor()
    sql = 'select * from collations'
    count = cur.execute(sql)
    print(count)
    # 重置游标的位置
    cur.scroll(0, mode='absolute')
    result = cur.fetchall()
    print(result)
    fields = cur.description
    print(fields)
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("tp_admin", cell_overwrite_ok=True)

    # 写上字段信息
    for field in range(0, len(fields)):
        sheet.write(0, field, fields[field][0])
    # 获取并写入数据段信息
    row = 1
    col = 0
    for row in range(1, len(result) + 1):
        for col in range(0, len(fields)):
            sheet.write(row, col, u'%s' % result[row - 1][col])

    workbook.save(outputfile)

    cur.close()
    conn.commit()
    conn.close()

###  导入excel
def  import_excel(inputfile):
    data = xlrd.open_workbook(inputfile)
    count = len(data.sheets())
    print("CCCCCCCCCCCCCCC:",count)
    for i in range(0, count - 1):
        sheet = data.sheet_by_index(i)
        # 循环每一行
        for r in range(1, sheet.nrows):
            id = sheet.cell(r, 0).value
            name = sheet.cell(r, 1).value
            # dq_datetime = sheet.cell(r,3).value
            # 处理日期
            # dq_datetime_num = xlrd.xldate_as_tuple(sheet.cell(r, 5).value, 0)
            #dq_datetime = '%s/%s/%s' % (dq_datetime_num[0], dq_datetime_num[1], dq_datetime_num[2])
            values = (id, name)
            # print query,values
            sql = 'INSERT INTO user(id, name) VALUES (%s, %s)'
            conn = conn_db()
            cur = conn.cursor()
            cur.execute(sql, values)

            cur.close()
            conn.commit()
            conn.close()
            print("Success!")

if __name__=='__main__':
    # import_excel(r"C:\Users\Administrator\Desktop\test.xlsx")
    # style()
    # export_excel(r"C:\Users\Administrator\Desktop\zzz.xlsx")
    data = {'name':'lyk','age':23};
    export_excel_json(r'C:\Users\Administrator\Desktop\lyk.json',r"C:\Users\Administrator\Desktop\zzz.xlsx")