Python 对 Excel 的常用操作

经常使用 Python 对 Excel 文件进行一些操作，每次都需要写代码，为了省时省事，今天特意把最近写的代码进行汇总，方便以后直接调用。基本功能如下：

1. 读取单元格的值
2. 把值写入单元格
3. 读取行
4. 读取列
5. 插入行
6. 插入列
7. 清除某范围数据
8. 新建sheet表
9. 拷贝sheet表
10. 删除sheet表
11. 获取Excel中某sheet表的行数
12. 获取Excel中某sheet表的列数
13. 获取Excel文件中所有的sheet表名称
14. 获取某一单元格背景颜色
15. 设置某一单元格背景颜色
16. 获取某一单元格字体颜色
17. 设置某一单元格字体颜色
18. 设置某一范围背景颜色
19. 设置某一范围字体颜色
20. 关闭excel文件

代码如下：

# -*- coding: utf-8 -*-
import re
import logging
import win32com
import xlrd
import pandas as pd
import xlwings as xw
from xlwings.utils import rgb_to_int, int_to_rgb

logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s')


def read_cell(path=None, sheet=0, cell="A1"):
    """
    读取单元格的值
    :param path: excel路径
    :param sheet: sheet名称
    :param cell: 单元格名称
    :return:单元格的值
    """
    logging.info(u"ready to execute [readCell]")
    try:
        wb = xlrd.open_workbook(path)
        if type(sheet) == str:
            sht = wb.sheet_by_name(sheet)
        elif type(sheet) == int:
            sht = wb.sheet_by_index(sheet)
        position = get_split_col_row(cell)
        logging.info(u"position: [%s]" % position)
        pos_col = position[0]
        pos_row = position[1]
        pos_col_index = get_excel_col_index(pos_col)
        co = sht.col_values(pos_col_index, start_rowx=int(pos_row) - 1, end_rowx=int(pos_row))
        if len(co) == 0:
            co = [""]
        logging.debug('read_cell result:[' + str(co[0]) + ']')
        return co[0]
    except Exception as e:
        raise e
    finally:
        logging.info(u"end execute[readCell]")


def write_cell(path=None, sheet=0, cell="A1", text=None):
    """
    把值写入单元格
    :param path: excel路径
    :param sheet: sheet名称
    :param cell: 单元格名称
    :param text: 写入excel的值
    :return:
    """
    logging.info(u"ready to execute[writeCell]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        sht.range(cell).options(index=False, header=False).value = text
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.info(u"end execute[writeCell]")


def read_row(path=None, sheet=0, cell="A1"):
    """
    读取sheet表的某行
    :param path: excel路径
    :param sheet: sheet名称
    :param cell: 单元格名称
    :return:行的值列表
    """
    logging.info(u"ready to execute[read_row]")
    try:
        wb = xlrd.open_workbook(path)
        if type(sheet) == str:
            sht = wb.sheet_by_name(sheet)
        elif type(sheet) == int:
            sht = wb.sheet_by_index(sheet)
        position = get_split_col_row(cell)
        logging.info(u"position: [%s]" % position)
        pos_col = position[0]
        pos_row = position[1]
        pos_col_index = get_excel_col_index(pos_col)
        co = sht.row_values(int(pos_row) - 1, start_colx=pos_col_index)
        logging.debug('read_row result:[' + str(co) + ']')
        return co
    except Exception as e:
        raise e
    finally:
        logging.info(u"end execute[read_row]")


def read_col(path=None, sheet=0, cell="A1"):
    """
    读取sheet表的某列
    :param path: excel路径
    :param sheet: sheet名称
    :param cell: 单元格名称
    :return:列的值列表
    """
    logging.info(u"ready to execute[read_col]")
    try:
        wb = xlrd.open_workbook(path)
        if type(sheet) == str:
            sht = wb.sheet_by_name(sheet)
        elif type(sheet) == int:
            sht = wb.sheet_by_index(sheet)
        position = get_split_col_row(cell)
        logging.info(u"position: [%s]" % position)
        pos_col = position[0]
        pos_row = position[1]
        pos_col_index = get_excel_col_index(pos_col)
        co = sht.col_values(pos_col_index, start_rowx=int(pos_row) - 1)
        logging.debug('read_col result:[' + str(co) + ']')
        return co
    except Exception as e:
        raise e
    finally:
        logging.info(u"end execute[read_col]")


def ins_row(path=None, sheet=0, cell="A1", data=None):
    """
    在sheet表中插入行
    :param path: excel路径
    :param sheet: sheet名称
    :param cell: 单元格的值
    :param data: 插入行的值列表
    :return:
    """
    logging.info(u"ready to execute[ins_row]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        sht.range(cell).api.EntireRow.Insert()
        sht.range(cell).options(index=False, header=False).value = data
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.info(u"end execute[ins_row]")


def ins_col(path=None, sheet=0, cell="A1", data=None):
    """
    在sheet表中插入列
    :param path: excel路径
    :param sheet: sheet名称
    :param cell: 单元格的值
    :param data: 插入列的值列表
    :return:
    """
    logging.info(u"ready to execute[ins_col]")
    try:
        data_list = []
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        sht.range(cell).api.EntireColumn.Insert()
        for i in data:
            data_list.append([i])
        sht.range(cell).options(index=False).value = data_list
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.info(u"end execute[ins_col]")


def del_range(path, sheet=0, cell_1="A1", cell_2="A1"):
    """
    清除某范围数据
    del_range(path, sheet=0, cell_1="A1", cell_2="A1")
        function： Clear the format and content of cells within the specified range without affecting other cells
        parameter：
          path:   str           files path
          sheet:  str or int    sheet name
          cell_1:   str         The cell in the upper left corner
          cell_2:   str         The cell in the lower right corner
        instance:
        del_range('C:\\iexcel.xlsx', 1, 'A1', 'C3')
        del_range('C:\\iexcel.xlsx', 'sheet2', 'B1', 'D5')
    """
    logging.debug(u"ready to execute[del_range]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        rng = sht.range(cell_1, cell_2)
        rng.clear()
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[del_range]")


def creat_sheet(path, sheet=None, before=None, after=None):
    """
    新建sheet表
    creat_sheet(path, sheet=None, before=None) ->   new sheet name
        function： creat a sheet
        parameter：
          path:   str           files path
          sheet:  str           sheet name
          before: str or int    the sheet before which the new sheet is added.
        return:
                new sheet name
        instance:
        creat_sheet('C:\\Desktop\\iexcel.xlsx')
        creat_sheet('C:\\Desktop\\iexcel.xlsx','Sheet4', before=2)
    """
    logging.debug(u"ready to execute[creat_sheet]")
    try:
        wb = xw.Book(path)
        if before:
            if isinstance(before, int):
                before = before + 1
            Sheet = xw.sheets.add(name=sheet, before=before)
        else:
            if isinstance(after, int):
                after = after + 1
            Sheet = xw.sheets.add(name=sheet, after=after)
        wb.save()
        return Sheet.name
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[creat_sheet]")


def copy_sheet(path, sheet=0, new_sheet_name=None):
    """
    拷贝sheet表
    copy_sheet(path, sheet=0 ,new_sheet_name=None) -> new sheet name
        function： copy a sheet
        parameter：
          path:   str                 files path
          sheet:  str or int          sheet name
          new_sheet_name: str         new sheet name
        return:
                new sheet name
        instance:
        copy_sheet('C:\\iexcel.xlsx', sheet=0)
        copy_sheet('C:\\iexcel.xlsx', sheet='Sheet1',new_sheet_name='new_sheet')
    """
    logging.debug(u"ready to execute[copy_sheet]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        sht.api.Copy(Before=sht.api)
        wb.save()
        nsht = wb.sheets[sht.index - 2]
        if new_sheet_name != None:
            nsht.name = new_sheet_name

        return nsht.name
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[copy_sheet]")


def del_sheet(path, sheet=0):
    """
    删除sheet表
    del_sheet(path, sheet=0)
        function： delete a sheet
        parameter：
            path:   str                 files path
            sheet:  str or int          sheet name
        instance:
        del_sheet('C:\\iexcel.xlsx', sheet='sheet1')
        del_sheet('C:\\iexcel.xlsx', sheet=1)
    """
    logging.debug(u"ready to execute[del_sheet]")
    try:
        wb = xw.Book(path)
        sheet = wb.sheets[sheet]
        sheet.delete()
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[del_sheet]")


def get_rows_count(path, sheet=0):
    """
    获取Excel中sheet表的行数
    get_rows_count(path, sheet=0) ->  rows_count
        function： Get the number of form rows
        parameter：
          path:   str                  files path
          sheet:  str or int           sheet name
        return:
              the number of rows
        instance:
        get_rows_count('C:\\iexcel.xlsx', sheet=0) ->  10
        get_rows_count('C:\\iexcel.xlsx', sheet='Sheet1') ->  10
    """
    logging.debug(u"ready to execute[get_rows_count]")
    try:
        df = pd.read_excel(path, sheet_name=sheet, header=None)
        rows_count = df.shape[0]
        return rows_count
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[get_rows_count]")


def get_cols_count(path, sheet=0):
    """
    获取Excel中sheet表的列数
    get_cols_count(path, sheet=0)  ->  cols_count
       function： Get the number of form columns
       parameter：
         path:   str                files path
         sheet:  str or int         sheet name
       return:
             the number of columns
       instance:
       get_cols_count('C:\\iexcel.xlsx', sheet=0 ->  10
       get_cols_count('C:\\iexcel.xlsx', sheet='Sheet1') ->  10
   """
    logging.debug(u"ready to execute[get_cols_count]")
    try:
        df = pd.read_excel(path, sheet_name=sheet, header=None)
        cols_count = df.shape[1]
        return cols_count
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[get_cols_count]")


def get_sheet_name_list(path):
    """
    获取Excel文件中所有的sheet表名称
    :param path: Excel文件路径
    :return: 所有的sheet表名称列表
    """
    xl = pd.ExcelFile(path)
    # 所有的sheet名称
    sheet_names = xl.sheet_names
    # 读取Excel中sheet_name的数据
    # sheet_content_list = xl.parse(sheet_name)
    return sheet_names


def get_cell_color(path, sheet=0, cell="A1"):
    """
    获取某一单元格背景颜色
    get_cell_color(path, sheet=0, cell="A1") -> color
        function： Get the background color of the cell
        parameter：
            path:   str           files path
            sheet:  str or int    sheet name
            cell:   str           cell
        return:
                the background color of the cell
                (If you have never set a cell background color,it will return None)
        instance:
            get_cell_color('C:\\iexcel.xlsx', 'sheet1', 'A1')  ->  (255,0,0)
            get_cell_color('C:\\iexcel.xlsx', 0, 'A2')  ->  (255,255,0)

            Red          (255,0,0)
            Yellow       (255,255,0)
            Blue  	     (0,0,255)
            White	     (255,255,255)
            Black	     (0,0,0)
            Green	     (0,255,0)
            Purple	     (128,0,128)
    """
    logging.debug(u"ready to execute[get_cell_color]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        rng = sht[cell]
        color = rng.color
        if color == None:
            logging.debug(u"This cell background color is the default background color")
        return color
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[get_cell_color]")


def set_cell_color(path, sheet=0, cell="A1", color=None):
    """
    设置某一单元格背景颜色
    set_cell_color(path, sheet=0, cell="A1", color=None)
        function： Set the background color of the cell
        parameter：
          path:   str                 files path
          sheet:  str or int          sheet name
          cell:   str                 cell
          color:  str or tuple        color
                Red       '0000FF'    (255,0,0)
                Yellow    '00FFFF'    (255,255,0)
                Blue  	   'FF0000'   (0,0,255)
                White	   'FFFFFF'   (255,255,255)
                Black	   '000000'    (0,0,0)
                Green	   '00FF00'    (0,255,0)
                Purple	   '800080'    (128,0,128)
        instance:
        set_cell_color('C:\\iexcel.xlsx', 0, 'A1', (0,0,255))
        set_cell_color('C:\\iexcel.xlsx', 1, 'B1', (255,255,255))
        set_cell_color('C:\\iexcel.xlsx', 'sheet1', 'C1', 'FFFFFF')
        set_cell_color('C:\\iexcel.xlsx', 'sheet2', 'D1', 'FFFFFF')
    """
    logging.debug(u"ready to execute[set_cell_color]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        rng = sht[cell]
        if isinstance(color, str):
            color = int(color, 16)
        rng.color = color
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[set_cell_color]")


def get_cell_font_color(path, sheet=0, cell='A1'):
    """
    获取某一单元格字体颜色
    get_cell_font_color(path, sheet=0, cell='A1')
        function： Get the font color of the cell
        parameter：
            path:   str           files path
            sheet:  str or int    sheet name
            cell:   str           cell
        return:
                the font color of the cell
                (If you have never set a cell background color,it will return None)
        instance:
            get_cell_font_color('C:\\iexcel.xlsx', 'sheet1', 'A1')  ->  (255,0,0)
            get_cell_font_color('C:\\iexcel.xlsx', 0, 'A2')  ->  (255,255,0)

            Red          (255,0,0)
            Yellow       (255,255,0)
            Blue  	     (0,0,255)
            White	     (255,255,255)
            Black	     (0,0,0)
            Green	     (0,255,0)
            Purple	     (128,0,128)
    """
    logging.debug(u"ready to execute[get_cell_font_color]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        rng = sht[cell]
        color = rng.api.Font.Color
        if not isinstance(color, tuple):
            color = int_to_rgb(color)
        if color == None:
            logging.debug(u"This cell font color is the default background color")
        return color
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[get_cell_font_color]")


def set_cell_font_color(path, sheet=0, cell='A1', color='000000'):
    """
    设置某一单元格字体颜色
    set_cell_font_color(path, sheet=0, cell='A1', color='000000')
        function： Set the font color of the cell
        parameter：
          path:   str               files path
          sheet:  str or int        sheet name
          cell:   str               cell
          color:  str or tuple        color
                Red       '0000FF'    (255,0,0)
                Yellow    '00FFFF'    (255,255,0)
                Blue  	   'FF0000'   (0,0,255)
                White	   'FFFFFF'   (255,255,255)
                Black	   '000000'    (0,0,0)
                Green	   '00FF00'    (0,255,0)
                Purple	   '800080'    (128,0,128)
        instance:
        set_cell_font_color('C:\\iexcel.xlsx', 1, 'A1', '000000')
        set_cell_font_color('C:\\iexcel.xlsx', 'sheet2', 'B2', (0,0,0))
    """
    logging.debug(u"ready to execute[set_cell_font_color]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        if isinstance(color, tuple):  # RGB元组形式
            color = rgb_to_int(color)
        else:  # str 数字 形式
            color = int(color, 16)
        rng = sht[cell]
        rng.api.Font.Color = color
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[set_cell_font_color]")


def set_range_color(path, sheet=0, cell_1="A1", cell_2="A1", color=None):
    """
    设置某一范围背景颜色
    set_range_color(path, sheet=0, cell_1="A1", cell_2="A1", color=None)
        function： Set the background color of the area cell
        parameter：
          path:   str                 files path
          sheet:  str or int          sheet name
          cell_1:   str               The cell in the upper left corner
          cell_2:   str               The cell in the lower right corner
          color:  str or tuple        color
                Red       '0000FF'    (255,0,0)
                Yellow    '00FFFF'    (255,255,0)
                Blue  	   'FF0000'   (0,0,255)
                White	   'FFFFFF'   (255,255,255)
                Black	   '000000'    (0,0,0)
                Green	   '00FF00'    (0,255,0)
                Purple	   '800080'    (128,0,128)
        instance:
        set_range_color('C:\\iexcel.xlsx', 1, 'A1', 'C3','000000')
        set_range_color('C:\\iexcel.xlsx', 'sheet2', 'B1', 'D5','FFFFFF')
    """
    logging.debug(u"ready to execute[set_range_color]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        rng = sht.range(cell_1, cell_2)
        if isinstance(color, str):
            color = int(color, 16)
        rng.color = color
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[set_range_color]")


def set_range_font_color(path, sheet=0, cell_1='A1', cell_2='A1', color='000000'):
    """
    设置某一范围字体颜色
    set_range_font_color(path, sheet=0, cell_1='A1', cell_2='A1',color='000000')
        function： Set the font color of the range
        parameter：
          path:   str               files path
          sheet:  str or int        sheet name
          cell_1:   str         The cell in the upper left corner
          cell_2:   str         The cell in the lower right corner
          color:  str or tuple        color
                Red       '0000FF'    (255,0,0)
                Yellow    '00FFFF'    (255,255,0)
                Blue  	   'FF0000'   (0,0,255)
                White	   'FFFFFF'   (255,255,255)
                Black	   '000000'    (0,0,0)
                Green	   '00FF00'    (0,255,0)
                Purple	   '800080'    (128,0,128)
        instance:
        set_range_font_color('C:\\iexcel.xlsx', 1, 'A1', 'A2', '000000')
        set_range_font_color('C:\\iexcel.xlsx', 'sheet2', 'A1', 'A2', (0,0,0))
    """
    logging.debug(u"ready to execute[set_range_font_color]")
    try:
        wb = xw.Book(path)
        sht = wb.sheets[sheet]
        if isinstance(color, tuple):  # RGB元组形式
            color = rgb_to_int(color)
        else:  # str 数字 形式
            color = int(color, 16)
        rng = sht.range(cell_1, cell_2)
        rng.api.Font.Color = color
        wb.save()
    except Exception as e:
        raise e
    finally:
        logging.debug(u"end execute[set_range_font_color]")


def close_excel(path):
    """
    关闭excel文件
    :return:
    """
    xl = win32com.client.gencache.EnsureDispatch("Excel.Application")  # 若想引用常数的话使用此法调用Excel
    xl.Visible = False  # 是否显示Excel文件
    wb = xl.Workbooks.Open(path)
    wb.Save()
    wb.Close()


def get_split_col_row(string):
    """
    拆分行列
    :param string: 输入  A12
    :return: 返回   ['A','12']
    """
    string = string.upper()
    return re.findall(r'[0-9]+|[A-Z]+', string)


def get_excel_col_index(string):
    """
    根据excel的列名，返回列号
    :param string: 如  'B'
    :return: 则返回  1
    """
    s = 0
    for c in string:
        c = c.upper()
        s = s * 26 + ord(c) - ord('A') + 1
    return s - 1

注：可以把此代码写入 .py 文件，每次使用只需调用即可。

 本人使用的是office2010版，其他版本可能有部分功能不能使用，欢迎评论区进行交流。