python 操作excel 表格

#!/usr/bin/env python
# encoding: utf-8
'''
    @author: Star涛
    @E_mail: startao2021@163.com
    @file: text_1.py
    @time: 2021/12/23 17:56
    @desc:
'''
import xlrd
import xlwt
import time
from openpyxl import load_workbook
import panads as pd 
from openpyxl.worksheet import Worksheet


def openpyxl_read_function():
    """
        读
    :return:
    """
    wb = load_workbook(filename='text_3.xlsx', read_only=True)
    mySheet = wb['数据']
    for row_num in range(1, mySheet.max_row + 1):
        a_list = []
        for i in range(1, mySheet.max_column + 1):
            a_list.append(mySheet.cell(row=row_num, column=i).value)
        print(a_list)


def openpyxl_write_function():
    """
            写
        :return:
        """
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    ws.title = "数据"
    excel_list = [
        ["电视剧名称", "上映日期", "类型"],
        ['一起深呼吸', 2021, '电视剧'],
        ['红鲨突击', 2022, '电视剧'],
        ['我只喜欢你', 2023, '电视剧']
    ]
    for item in excel_list:
        ws.append(item)
    wb.save("text_2.xlsx")


def openpyxl_read_write_function():
	    # 加载已存在的Excel文件
    book = load_workbook('your_file.xlsx')
    # 获取或创建名为'Sheet2'的工作表
    try:
        sheet2 = book['Sheet2']
    except KeyError:
        sheet2 = book.create_sheet("Sheet2")
    excel_list = [
        ["电视剧名称", "上映日期", "类型"],
        ['一起深呼吸', 2021, '电视剧'],
        ['红鲨突击', 2022, '电视剧'],
        ['我只喜欢你', 2023, '电视剧']
    ]
    for item in excel_list:
        sheet2.append(item)
    book.save("text_2.xlsx")




def xlwt_write_function():
    """
        xlwt_write_function
    :return:
    """
    writebook = xlwt.Workbook(encoding='utf-8')  # 打开excel
    data_excel = writebook.add_sheet("数据")  # 添加一个名字叫test的sheet
    excel_list = [
        ["电视剧名称", "上映日期", "类型"],
        ['一起深呼吸', 2021, '电视剧'],
        ['红鲨突击', 2022, '电视剧'],
        ['我只喜欢你', 2023, '电视剧']
    ]

    for i, p in enumerate(excel_list):
        for j, q in enumerate(p):
            data_excel.write(i, j, str(q))
    writebook.save(f'text_3.xlsx')


def xlwd_read_function():
    """
        xlwt_write_function
    :return:
    """
    writebook = xlrd.open_workbook("text_3.xlsx")
    table = writebook.sheet_by_name('数据')
    rowNum = table.nrows
    # 获取工作表的有效列数
    colNum = table.ncols
    for i in range(rowNum):
        # 定义一个空字典
        list1 = []
        for j in range(colNum):
            list1.append(table.cell(i, j).value)
        print(list1)


def pandas_read_function():
    df = pd.read_excel('abc.xls')
    for index, row in df.iterrows():
        duanju_name = row.values
        print(duanju_name)

def pandas_write_function():
    excel_list = [
        ['一起深呼吸', 2021, '电视剧'],
        ['红鲨突击', 2022, '电视剧'],
        ['我只喜欢你', 2023, '电视剧']
    ]
    df = pd.DataFrame(excel_list, columns= ["电视剧名称", "上映日期", "类型"])
    df.to_excel('output.xlsx', index=False)


if __name__ == '__main__':
    # xlwt_write_function()
    # openpyxl_read_function()
    # openpyxl_write_function()
    # xlwd_read_function()
    # pandas_write_function()    
    pandas_read_function()