python3-操作excel文件内容

读Excel文件

有其他库，这里只用openpyxl库搞一个demo

首先导入包： from openpyxl import load_workbook

from openpyxl import load_workbook

def read_xlsx(file_name):
    workbook = load_workbook(file_name)
    # booksheet = workbook.active                #获取当前活跃的sheet,默认是第一个sheet
    sheets = workbook.get_sheet_names()  # 从名称获取sheet
    booksheet = workbook.get_sheet_by_name(sheets[0]) # 取第1个sheets

    # 取全部行
    rows = booksheet.rows
    # 取全部列
    columns = booksheet.columns
    data = []
    # 迭代所有的行
    for row in rows:
        # 获取到每一行的内容
        line = [col.value for col in row]
        data.append(line[2])

    # 如果有表头的话，如果是list，可以用这种办法把第一行的内容去掉，如果是dict，需要考虑下
    return data[1:]

写Excel文件

def write_excel(file_name):
    from openpyxl import Workbook
    workbook = Workbook()
    booksheet = workbook.active  # 获取当前活跃的sheet,默认是第一个sheet
    # 存第一行单元格cell(1,1)
    booksheet.cell(1, 1).value = 6  # 这个方法索引从1开始
    # 存一行数据
    booksheet.append([11, 87])
    workbook.save("test_openpyxl.xlsx")

读CSV文件

def read_csv():
    import csv
    with open('test2.csv')as f:
        f_csv = csv.reader(f)
        for row in f_csv:
            print(row)

写CSV文件

def write_csv():
    import csv

    headers = ['class', 'name', 'sex', 'height', 'year']

    rows = [
        [1, 'xiaoming', 'male', 168, 23],
        [1, 'xiaohong', 'female', 162, 22],
        [2, 'xiaozhang', 'female', 163, 21],
        [2, 'xiaoli', 'male', 158, 21]
    ]

    with open('test2.csv', 'w')as f:
        f_csv = csv.writer(f)
        f_csv.writerow(headers)
        f_csv.writerows(rows)

字典形式的写入：

def write_csv():
    import csv

    headers = ['class', 'name', 'sex', 'height', 'year']

    rows = [
        {'class': 1, 'name': 'xiaoming', 'sex': 'male', 'height': 168, 'year': 23},
        {'class': 1, 'name': 'xiaohong', 'sex': 'female', 'height': 162, 'year': 22},
        {'class': 2, 'name': 'xiaozhang', 'sex': 'female', 'height': 163, 'year': 21},
        {'class': 2, 'name': 'xiaoli', 'sex': 'male', 'height': 158, 'year': 21},
    ]

    with open('test2.csv', 'w', newline='')as f:
        f_csv = csv.DictWriter(f, headers)
        f_csv.writeheader()
        f_csv.writerows(rows)