文件读写操作通过open()函数进行操作，使用with关键字读写文件就不用再惦记着要何时关闭文件。

先来看一个简单常规的文件读写操作；

f = open('test.txt', mode='w+', encoding='utf-8')
f.write(','.join([t for t in range(1000)]))
f.close()

f = open('test.txt', mode='r', encoding='utf-8')
print(f.read())
f.close()

再看一个省心的文件读写操作

def write_csv_file(file_path, data, data_type="list"):
    """爬虫输入写入Excel文件"""
    head = ["标题", "小区", "房厅", "面积", "朝向", "楼层", "年份",
            "位置", "总价(万)", "单价(元/平方米)"]
    keys = ["title", "house", "bedroom", "area", "direction",
            "floor", "year", "location", "total_price", "unit_price"]
    try:
        with open(file_path, 'w', newline='', encoding='utf_8_sig') as csv_file:
            writer = csv.writer(csv_file, dialect='excel')
            if head is not None:
                writer.writerow(head)
            if data_type == "list":
                for item in data:
                    row_data = []
                    for k in keys:
                        row_data.append(item[k])
                    writer.writerow(row_data)
            else:
                # 如果队列不为空，写入每行数据
                while not data.empty():
                    item = data.get()
                    if item:
                        row_data = []
                        for k in keys:
                            row_data.append(item[k])
                        writer.writerow(row_data)
            print("Write a CSV file to path %s Successful." % file_path)
    except Exception as e:
        print("Fail to write CSV to path: %s, Case: %s" % (file_path, e))

def read_file():
    with open("home.csv", mode='r', encoding='utf-8') as f:
        for i in f.readlines():
            print(i)

Excel操作

Excel文件操作常用的库有xlrd,xlwt, openpyxl, xlswriter已经pandas等；

• pandas：数据处理最常用的分析库之一，可以读取各种各样格式的数据文件，一般输出dataframe格式，功能强大；
• openpyxl：主要针对xlsx格式的excel进行读取和编辑；
• xlrd库：从excel中读取数据，支持xls；
• xlwt库：对excel进行修改操作，不支持对xlsx格式的修改；
• xlutils库：在xlwt和xlrd中，对一个已存在的文件进行修改
• xlwings：对xlsx、xls、xlsm格式文件进行读写、格式修改等操作
• xlsxwriter：用来生成excel表格，插入数据、插入图标等表格操作，不支持读取

1. xlwt库创建Excel

# 设置表格样式
def set_style(name, height, bold=False):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height
    style.font = font
    return style

def write_excel(file_name: str, info: List, 
                table_head: Optional[List], sheet: str = "data",
  							save_path: str = "",
                *args, **kwargs) -> str:
    """ xlwt库创建Excel    """
    try:
        file_name += ".xls"
        save_file = os.path.join(save_path, file_name) if save_path else file_name  # Excel文件保存路径
        workbook = xlwt.Workbook()  # 创建Excel
        sheet1 = workbook.add_sheet(sheet, cell_overwrite_ok=True)  # 增加sheet页

        for i in range(0, len(table_head)):  # 写表头数据
            sheet1.write(0, i, table_head[i], set_style('Times New Roman', 220, True))

        for i in range(len(info)):  # 从第二行开始写数据
            item = info[i]
            for j in range(len(table_head)):
                field = table_head[j]
                sheet1.write(i + 1, j, item[field], set_style('Times New Roman', 220, True))
        workbook.save(save_file)
        logger.info(f"Excel文件: {file_name}, 保存成功")
        return save_file
    except Exception as e:
        logger.exception(f"Excel文件处理异常: {str(e)}")
        return ""

2. xlrd包读取Excel文件

def read_excel(file_name: str, sheet_name: str = "", 
               sheet_index: int = 0, data_type: int = 0) -> List:
    """ xlrd包读取Excel文件 只支持xls
    :param file_name: 文件路径
    :param sheet_name: 要读取的sheet名称
    :param sheet_index: 要读取的sheet下标
    :param data_type: 数据类型 0: list, 1: dict
    :return:
    """
    wb = xlrd.open_workbook(filename=file_name)  # 打开文件
    sheet1 = wb.sheet_by_name(sheet_name) if sheet_name else wb.sheet_by_index(sheet_index)  # 通过sheet名称或索引获取表格
    rows = sheet1.nrows  # sheet页行数
    colums = sheet1.ncols  # sheet页列数
    table_head = sheet1.row_values(0)  # 获取表头
    logger.info(f"table_head: {table_head}")
    li = [table_head]

    for i in range(1, rows):  # sheet页数据添加到li中
        if data_type == 0:
            li.append(sheet1.row_values(i))  # 数据形式为列表
        else:
            di = {}
            for idx, t in enumerate(table_head):
                di[t] = sheet1.row_values(i)[idx]
            li.append(di)
    return li

3. xlsxwriter库进行创建Excel

def write_excel_xlsx(file_name: str, info: List, 
                     table_head: Optional[List], sheet: str = "data",
                     save_path: str = ""):
    """ 数据写入Excel，导出Excel文件, 支持xlsx类型  使用xlsxwriter库
    :param file_name: excel文件名称
    :param info: 写入的数据
    :param table_head: 数据表头
    :param sheet: sheet页名称
    :param save_path: 保存路径
    :return:
    """
    try:
        down_path = os.path.expanduser("~")  # 用户目录
        file_name += '.xlsx'
        down_path_file = os.path.join(save_path, file_name) if save_path else \
          		os.path.join(down_path, file_name)  # excel文件地址

        workbook = xlsxwriter.Workbook(down_path_file)  # 创建Excel文件
        worksheet = workbook.add_worksheet(sheet)  # 创建sheet，名为data
       
        format1 = workbook.add_format(
            {'bold': True, 'font_color': 'black', 'font_size': 13, 'align': 'left', 'font_name': u'宋体'})  # 表头格式
        format2 = workbook.add_format({'font_color': 'black', 'font_size': 11, 'align': 'left', 'font_name': u'宋体'}) # 表头外格式
        worksheet.set_column("A:A", 10)  # A列列宽设置能更好的显示
        for i in range(0, len(table_head)):  # 插入第一行表头标题
            field = table_head[i]
            worksheet.write(0, i, field, format1)

        # 从第二行开始插入数据
        for i in range(len(info)):
            item = info[i]  # 行数据
            for j in range(len(table_head)):
                field = table_head[j]
                worksheet.write(i + 1, j, item[field], format2)
        workbook.close()
        alert_text = '导出成功,导出地址:{}!'.format(down_path_file)
        return down_path_file
    except Exception as e:
        logger.exception("导出Excel异常: {}".format(e))
        return ''

4. openpyxl库进行写Excel

def openpyxl_write_excel(file_name: str, info: List, 
                         table_head: Optional[List], sheet: str = "data",
                         save_path: str = ""):
    """ 数据写入Excel，导出Excel文件, 支持xlsx类型  使用openpyxl库
    raise ValueError("Cannot convert {0!r} to Excel".format(value)) 数据需要为字符串
    """
    down_path = os.path.expanduser("~")  # 用户目录
    file_name += '.xlsx'
    down_path_file = os.path.join(save_path, file_name) if save_path else \
      	os.path.join(down_path,   file_name)  # excel文件地址
    wb = Workbook()  # 创建一个新的Excel表, 只写模式write_only=True， 默认读写模式
    sheet1 = wb.active  # 激活当前sheet页
    # sheet1 = wb.create_sheet(sheet)  # sheet页面末尾新建一个sheet页   create_sheet(sheet, index=0) 在第0位置新建
    sheet1.append(table_head)

    for t in info:
        if isinstance(t, list):
            t = [str(s) for s in t]
            sheet1.append(t)
        elif isinstance(t, dict):
            fields = [str(t[s]) for s in table_head]
            sheet1.append(fields)
    wb.save(down_path_file)
    return down_path_file

5. openpyxl库进行读Excel

def openpyxl_read_excel(file_name: str, sheet_name: str = "", sheet_index: int = 0, data_type: int = 0) -> List:
    """ 读取Excel文件  使用openpyxl库  param data_type: 列表中数据类型 0:list， 1: dict    """
    workbook = load_workbook(file_name)  # 加载Excel文件
    # booksheet = workbook.active  # 获取当前活跃的sheet,默认是第一个sheet
    # 如果想获取别的sheet页采取下面这种方式，先获取所有sheet页名，在通过指定那一页。
    sheets = workbook.sheetnames  # workbook.get_sheet_names()  # 从名称获取sheet
    # booksheet = workbook.get_sheet_by_name(sheet_name)
    try:
        booksheet = workbook[sheet_name] if sheet_name else workbook[sheets[sheet_index]]
    except Exception as e:
        booksheet = workbook[sheets[0]]
    rows = booksheet.rows  # 获取sheet页的行数据
    columns = booksheet.columns  # 获取sheet页的列数据
    li = []
    table_head = []
    for idx, row in enumerate(rows):  # 迭代所有的行
        line = [col.value for col in row]
        if idx == 0:
            table_head.extend(line)  # 列表数据追加到列表
            continue
        if data_type == 0:
            li.append(line)  # 数据形式为列表
        else:
            di = dict(zip(table_head, line))  # 数据转换为字典
            li.append(di)
    return li

6. pandas操作Excel

def pd_excel_write_info(path, file, data, abs_path, **kwargs):
    info = {}
    for i in data:
        info = i
        info['path'] = abs_path
    f = os.path.join(path, file)
    df = pd.DataFrame(info)
    df.to_excel(f)

def pd_excel_info(file, sheetname=0, **kwargs):
    converters = kwargs.get('converters', {})  # 列转码
    try:
        data = pd.read_excel(file, sheet_name=sheetname, converters=converters)
        li = []
        for ix, row in data.iterrows():
            try:
                li.append(dict(row))  # 按行添加数据 以dict/list形式添加
            except Exception as e:
                logger.error(e)
        return li
    except Exception as e:
        return None

根据以上库的使用，可以看出openpyxl和pandas的功能更加健全和强大，尤其是pandas，对于数据操作那简直方便的飞起！

以下是各个库是性能对比，该图是从知乎网上得来的，如有侵权边删！

#头条创作挑战赛##python#