python读写Excel

import xlrd, openpyxl
import traceback
import os


def read_excel(file_path, sheel_name="sheel1"):
    """
    读取表格，返回json
    :param file_path:
    :param sheel_name:
    :return:
    """
    try:
        res = {}
        word_book = xlrd.open_workbook(file_path)
        sheel_names = word_book.sheet_names()
        for sheel in sheel_names:
            res[sheel] = []
            data = word_book.sheet_by_name(sheel)
            row = 1
            while row < data.nrows:
                tem_li = data.row_values(row)
                res[sheel].append(tem_li)
                row += 1
        return res
    except Exception:
        traceback.print_exc()
        return []


def writeExcel(head, data, file_name, title):
    """

    :param head: 列的描述 ["时间", "销量"]
    :param data: 列的信息 [["2020-02-04", 1], ["2020-02-03", 2],["2020-02-05", 1],["2020-02-06", 2],["2020-02-08", 12]]
    :param file_name: 文件保存路径
    :param title: sheet  的名称
    :return:
    """
    outwb = openpyxl.Workbook()  # 打开一个将写的文件
    outws = outwb.create_sheet(title=title, index=0)  # 在将写的文件创建sheet
    for row in range(1,len(data) + 2):
        for col in range(1,len(head) + 1):
            if row == 1:
                outws.cell(row, col).value = head[col-1]  # 写文件
            else:
                outws.cell(row, col).value = data[row - 2][col - 1]
    outwb.save(file_name)  # 一定要记得保存
    return file_name


def write_Excel_multi_sheets(file_name, excel_prepare_datas):
    """
    数据写入Excel表格，支持一个Excel文件写入多个sheet
    :param file_name: 文件名称
    :param excel_prepare_datas:
           example：
           excel_prepare_datas = [
        {"head": ["时间", "销量"], "data": [["2020-02-04", 1], ["2020-02-03", 2]], "title": "饼图"},
        {"head": ["彩虹", "彩虹桥"], "data": [["是", 1], ["否", 2]], "title": "折线图"}]
        "head":表头, "data":数据, "title": sheet_name
    :return:
    """
    outwb = openpyxl.Workbook()  # 打开一个将写的文件
    index = 0
    for origin_data in excel_prepare_datas:
        title = origin_data.get('title')
        data = origin_data.get('data')
        head = origin_data.get('head')

        outws = outwb.create_sheet(title=title, index=index)  # 在将写的文件创建sheet
        for row in range(1, len(data) + 2):
            for col in range(1, len(head) + 1):
                if row == 1:
                    outws.cell(row, col).value = head[col-1]  # 写文件
                else:
                    outws.cell(row, col).value = data[row - 2][col - 1]
        index += 1
    outwb.save(file_name)  # 一定要记得保存
    return file_name


if __name__ == '__main__':
    head = ["时间", "销量"]
    data = [["2020-02-04", 1], ["2020-02-03", 2],["2020-02-05", 1],["2020-02-06", 2],["2020-02-08", 12]]
    # file_name = "1.xls"
    # title = "测试"
    # writeExcel(head, data, file_name, title)

    excel_prepare_datas = [
        # {"head": ["彩虹桥", "彩虹"], "data": [["2020-02-04", 1], ["2020-02-03", 2]], "title": "饼图"},
        {"head": ["彩虹", "彩虹桥"], "data": [["是", 1], ["否", 2]], "title": "折线图"},

    ]
    file_name = write_Excel_multi_sheets("multi_sheet_test02.xls", excel_prepare_datas)