[大数据]excel读取和写入

excel文件的导入导出
不过这个依赖包读取大一点的文件非常慢

# workbook相关
from openpyxl.workbook import Workbook
# ExcelWriter，封装了很强大的excel写的功能
from openpyxl.writer.excel import ExcelWriter

from openpyxl.reader.excel import load_workbook

from zipfile import ZipFile, ZIP_DEFLATED


def write_to_excel(records, head_row, excel_name="save.xlsx"):
    """
      function：
       写入内容到excel文件
      Param:
       records: **records(二维数组)，存储的文件内容
       head_row: *head_row，表头，标题
       excel_name: 保存的Excel文件名
      Return:
     """

    # 新建一个workbook
    wb = Workbook()
    archive = ZipFile(excel_name, 'w', ZIP_DEFLATED)
    # 新建一个excelWriter
    ew = ExcelWriter(workbook=wb, archive=archive)

    # 第一个sheet是ws
    ws = wb.worksheets[0]
    # 设置ws的名称
    ws.title = "range_name"
    # 写第一行，标题行（第一列为空），标题的数组都往后挪动一列
    for h_x in range(1, len(head_row) + 1):
        # 现在处于哪一列
        ws.cell(1, h_x, head_row[h_x-1])

    # 写第二行及其以后的那些行
    i = 2
    for record in records:
        for x in range(1, len(record) + 1):
            ws.cell(i, x, record[x-1])
        i += 1
    # 写文件
    ew.save()

    return


def read_excel(file):
    """
    :param file: 文件路径
    :return: **data_list(二维数组)
    """

    wb = load_workbook(file)

    # 取第一张表
    sheet_name = wb.get_sheet_names()
    ws = wb.get_sheet_by_name(sheet_name[0])

    # 建立存储数据的字典
    data_list = []

    for row in ws.rows:
        temp_list = []
        for cell in row:
            temp_list.append(cell.value)
        data_list.append(temp_list)
    return data_list