Python基于xlrd&xlwt读写Excel

# !/usr/bin/env python 
# -*- coding: utf-8 -*- 
# @Time : 2021/4/15 9:12 
# @Author : XXX
"""
资源链接： https://download.csdn.net/download/qq_37754459/16731250
实现对 xls 格式的 excel 表格的 增改查
实现对 xls 单元格样式和宽度的设置
实现自由切换sheet读写数据
弊端:
    1. 所有数据的增改只能在下一次实例化才会生效
    2. 只能操作已存在的excel
依赖:
    1. xlrd
    2. xlwt
    3. xlutils
全局变量:
    1. 文件名称
    2 工作薄对象
    3. sheet对象
    4. copy的工作薄对象
    5. copy的sheet对象
    6. 单元格样式对象
    7. 当前 sheet 有效行数
    8. 当前 sheet 有效列数
基本:
    1. 创建新sheet
    2. 切换已存在sheet
    3. 获取所有的 sheet 名称列表
    4. 设置单元格样式
    5. 批量设置列宽度
增:
    1. 有效行下增加一行
    2. 有效行下批量增加多行
查:
    1. 获取指定行有效长度
    2. 获取整行数据
    3. 获取整列数据
    4. 获取切片后的行数据
    5. 获取切片后的列数据
    6. 获取指定单元格数据
    7. 获取整个sheet的数据
    8. 获取指定值在sheet中的坐标
改:
    1. 替换指定单元格数据
    2. 插入指定单元格数据(保留原数据)
    3. 替换整行数据
    4. 批量行替换(向下替换)
"""
import xlrd
import xlwt
from xlutils.copy import copy


class Excel(object):

    def __init__(self, filename, sheet):
        self.filename = filename
        self.book = xlrd.open_workbook(self.filename)
        self.book_copy = copy(self.book)
        self.style = self.set_cell_style()
        # 获取sheet
        try:
            self.sheet = self.book.sheet_by_index(sheet) if isinstance(sheet, int) else self.book.sheet_by_name(sheet)
            self.sheet_copy = self.book_copy.get_sheet(sheet) if isinstance(sheet, int) else self.book_copy.get_sheet(
                sheet)
        except Exception:
            print("sheet {} not is exist".format(sheet))
        self.row_number = self.sheet.nrows
        self.col_number = self.sheet.ncols

    def set_cell_style(self):
        """
        设置单元格样式
        """
        style = {
            "font": {
                "name": "Calibri",  # 字体
                "colour": 0,  # 颜色
                "size": 200,  # 大小
                # "bold": True,
                # "italic": True,
                # "underline": True,
                # "struck_out": True
            },
            "alignment": {
                "horz": 0,  # 设置水平位置，0是左对齐，1是居中，2是右对齐
                "wrap": 1  # 设置自动换行 0 不换, 1 换
            },
            "borders": {
                # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
                # 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
                # color 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,
                # 7 = Cyan, 16 = Maroon, 17 = Dark Green,
                # 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal,
                # 22 = Light Gray, 23 = Dark Gray
                "left": 1,
                "left_color": 0,
                "right": 1,
                "right_color": 0,
                "top": 1,
                "top_color": 0,
                "bottom": 1,
                "bottom_color": 0
            }
        }
        style_object = xlwt.XFStyle()
        if style.get("borders"):
            borders = xlwt.Borders()
            left = style.get("borders").get("left")
            if left:
                borders.left = left
            left_color = style.get("borders").get("left_color")
            if left_color:
                borders.left_colour = left_color
            right = style.get("borders").get("right")
            if right:
                borders.right = right
            right_color = style.get("borders").get("right_color")
            if right_color:
                borders.right_colour = right_color
            top = style.get("borders").get("top")
            if top:
                borders.top = top
            top_color = style.get("borders").get("top_color")
            if top_color:
                borders.top_colour = top_color
            bottom = style.get("borders").get("bottom")
            if bottom:
                borders.bottom = bottom
            bottom_color = style.get("borders").get("bottom_color")
            if bottom_color:
                borders.bottom_colour = bottom_color
            style_object.borders = borders
        if style.get("font"):
            font = xlwt.Font()
            name = style.get("font").get("name")
            if name:
                font.name = name
            colour = style.get("font").get("colour")
            if colour:
                font.colour_index = colour
            size = style.get("font").get("size")
            if size:
                font.height = size
            bold = style.get("font").get("bold")
            if bold:
                font.bold = bold
            italic = style.get("font").get("italic")
            if italic:
                font.italic = italic
            underline = style.get("font").get("underline")
            if underline:
                font.underline = underline
            struck_out = style.get("font").get("struck_out")
            if struck_out:
                font.struck_out = struck_out
            style_object.font = font
        if style.get("alignment"):
            alignment = xlwt.Alignment()
            horz = style.get("alignment").get("horz")
            if horz:
                alignment.horz = horz
            wrap = style.get("alignment").get("wrap")
            if wrap:
                alignment.wrap = wrap
            style_object.alignment = alignment
        return style_object

    def batch_set_col_width(self, width_list):
        """
        width : [第一列,第二列,"",....]
        不设置col宽度则填写空字符串
        设置列宽度,高度有字体大小和自动换行控制
        """
        for item in range(len(width_list)):
            if width_list[item] == "":
                continue
            self.sheet_copy.col(item).width = width_list[item]
        self.book_copy.save(self.filename)

    def create_sheet(self, name):
        """
        创建一个新的 sheet
        """
        self.book_copy.add_sheet(name)
        self.book_copy.save(self.filename)

    def get_all_sheet_name(self):
        """
        返回所有的sheet名称列表
        ['Sheet1']
        """
        return self.book.sheet_names()

    def switch_sheet(self, sheet):
        """
        切换 sheet
        """
        try:
            self.sheet = self.book.sheet_by_index(sheet) if isinstance(sheet, int) else self.book.sheet_by_name(sheet)
            self.sheet_copy = self.book_copy.get_sheet(sheet) if isinstance(sheet, int) else self.book_copy.get_sheet(
                sheet)
        except Exception:
            print("sheet {} not is exist".format(sheet))

    def get_valid_row_len(self, row):
        """
        返回行有效长度
        int 1
        """
        return self.sheet.row_len(row)

    def get_row_data(self, row):
        """
        返回整行的数据
        list ['s', 's', 's', 's', '', 's']
        """
        return self.sheet.row_values(row)

    def get_col_data(self, col):
        """
        返回整列的数据
        list ['s', 's', 's', 's', '', 's']
        """
        return self.sheet.col_values(col)

    def get_sheet_data(self, mode=True):
        """
        mode true  跳过首行
        mode false 不跳过首行
        获取整个sheet的数据
        list
        """
        sheet_data = []
        for item in range(self.row_number):
            sheet_data.append(self.sheet.row_values(item))
        return sheet_data[1::] if mode else sheet_data

    def get_row_data_slice(self, row, start, end=None):
        """
        返回切片后的行数据
        list ['s', 's', 's', '', 's']
        """
        row_data = []
        row_object_data = self.sheet.row_slice(row, start, end) if end else self.sheet.row_slice(row, start)
        for item in row_object_data:
            row_data.append(item.value)
        return row_data

    def get_col_data_slice(self, col, start, end=None):
        """
        返回切片后的列数据
        list ['s', 's', 's', '', 's']
        """
        row_data = []
        row_object_data = self.sheet.col_slice(col, start, end) if end else self.sheet.col_slice(col, start)
        for item in row_object_data:
            row_data.append(item.value)
        return row_data

    def get_cell_data(self, row, col):
        """
        返回指定单元格的数据
        str
        """
        return self.sheet.cell_value(row, col)

    def find_data(self, data):
        """
        data : str
        返回指定数据在sheet中的坐标
        [(0, 1, 's'), (0, 2, 's'), (0, 3, 's'), (0, 5, 's'), (1, 0, 's')]
        """
        temp_index = []
        for r in range(self.row_number):
            if data in self.sheet.row_values(r):
                for c in range(len(self.sheet.row_values(r))):
                    if data == self.sheet.row_values(r)[c]:
                        temp_index.append((r, c, data))
        return temp_index

    def replace_cell_data(self, row, col, data, mode=True):
        """
        data : str
        mode true row 包含首行
        mode false row 不包含首行
        替换指定单元格的数据
        返回修改后的数据
        """
        row = row - 1 if mode else row
        self.sheet_copy.write(row, col, data, self.style) if self.style else self.sheet_copy.write(row, col, data)
        self.book_copy.save(self.filename)
        return data

    def install_cell_data(self, row, col, data, mode=True):
        """
        data : str
        mode True  在单元格内数据前插入
        mode False 在单元格内数据后插入
        返回修改后的数据
        """
        insert_data = data + self.get_cell_data(row, col) if mode else self.get_cell_data(row, col) + data
        self.sheet_copy.write(row, col, insert_data, self.style) if self.style else self.sheet_copy.write(
            row, col, insert_data)
        self.book_copy.save(self.filename)
        return insert_data

    def replace_row_data(self, row, data, mode=True):
        """
        data : ['s', 's', 's', '', 's']
        mode true row 包含首行
        mode false row 不包含首行
        替换整行数据
        """
        row = row - 1 if mode else row
        for item in range(len(data)):
            self.sheet_copy.write(row, item, data[item], self.style) if self.style else self.sheet_copy.write(
                row, item, data[item])
        self.book_copy.save(self.filename)
        return data

    def batch_replace_row_data(self, row, data, mode=True):
        """
        data : [['s', 's', 's', '', 's'], ['s', 's', 's', '', 's'], ['s', 's', 's', '', 's']]
        mode true row 包含首行
        mode false row 不包含首行
        从 row开始 批量 往下 替换整行数据
        """
        row = row - 1 if mode else row
        for r in range(len(data)):
            for c in range(len(data[r])):
                self.sheet_copy.write(row, c, data[r][c], self.style) if self.style else self.sheet_copy.write(
                    row, c, data[r][c])
            row += 1
        self.book_copy.save(self.filename)
        return data

    def add_row_data(self, data):
        """
        data : [1,2,3,4,5,6,7]
        最下面增加一行
        """
        row_number = self.row_number
        for col in range(len(data)):
            self.sheet_copy.write(row_number, col, data[col], self.style) if self.style else self.sheet_copy.write(
                row_number, col, data[col])
        self.book_copy.save(self.filename)

    def batch_add_row_data(self, data):
        """
        data : [[1,2,3,4,5,6,7],[1,2,3,4,5,6,7],[1,2,3,4,5,6,7],[1,2,3,4,5,6,7]]
        批量在最下面增加一行
        """
        row_number = self.row_number
        for a in range(len(data)):
            for col in range(len(data[a])):
                self.sheet_copy.write(row_number, col, data[a][col],
                                      self.style) if self.style else self.sheet_copy.write(
                    row_number, col, data[a][col])
            row_number += 1
        self.book_copy.save(self.filename)


if __name__ == '__main__':
    e = Excel("test123.xls", "a")

    e.batch_add_row_data([[1, 2, 3], [1, 2, 3]])

    e.switch_sheet("b")

    e.batch_add_row_data([[3, 2, 1], [3, 2, 1]])