Python excel文件操作及封装

在Python对Excel文件进行处理常用模块有xlrd、xlwt、openpyxl，其中xlrd主要用于读取Excel文件，xlwt用于向Excel中写入数据，openpyxl既能读取Excel文件也能向其中写入数据。openpyxl是一个用于读取/写入Excel 2010 xlsx、xlsm、xltm、xltx文件的库。

1、openpyxl中的常用方法

openpyxl中有几个基本概念，其中workbook表示整个Excel工作表对象，Worksheet表示Excel表格中的一张表对象，Cell表示单元格对象。

（1）加载Excel文件，获取workbook对象

import openpyxl

workbook = openpyxl.load_workbook('./test.xlsx')
print(workbook)

# <openpyxl.workbook.workbook.Workbook object at 0x000001768C808978>

（2）获取worksheet对象

# 读取工作表中所有的sheet, 返回一个list
sheets = wb.sheetnames
print(sheets)
# ['Sheet1', 'Sheet2', 'Sheet3']

# 获取某个sheet, 返回Worksheet对象
sheet = wb[sheets[0]] 
print(sheet)
# <Worksheet "Sheet1">

（3）访问Cell对象

"""
    访问单个cell:
        Worksheet.cell(row, column), 如Worksheet.cell(1, 1)
        Worksheet[position], 获取指定位置的cell, 如Worksheet['A3']

    访问多个cell，使用切片：
        Worksheet[num], 获取第num行的cell, 如Worksheet[1]
        Worksheet[num1:num2], 获取第num1到num2行的所有cell, Worksheet[1:3]
        Worksheet['pos1':'pos2'], 获取从pos1到pos2之间的所有cell, Worksheet['A1': 'D4']

    获取cell的值：
        获取指定位置单元格的值, Worksheet['position'].value
        获取指定位置单元格的值, Worksheet.cell(row, column, value).value
            row: 指定行
            colum: 指定列
            value: 默认为None, 单元值为空时的返回值
        获取sheet所有的值：
            sheet.values
"""
# 获取单个cell
cell = sheet.cell(3, 4)
print("cell:", cell)
# cell: <Cell 'Sheet1'.D3>

cell = sheet['d4']
print("cell", cell)
# cell: <Cell 'Sheet1'.D4>

# 获取多个cell A1-D4 区间内所有cell, 返回值是一个元组
cell = sheet['A1': 'D4']
print("cell:", cell)


# 获取B1 B2 B3 B4 四个单元格的cell, 返回值也是元组
cell = sheet['B1:B4']
print("cell:", cell)

# 获取1-3行的cell, 返回值也是元组
cell = sheet[1:3]
print("cell:", cell)

# 获取指定行的cell, 返回值也是元组
cell = sheet[1]
print("cell:", cell)

# 以行方式访问指定行到指定列之间所有的cell
for col in sheet.iter_rows(min_row=1, min_col=2, max_col=3, max_row=3):
    for cell in col:
        print(cell)
            
# 以列方式访问指定行到指定列之间所有的cell      
for col in sheet.iter_cols(min_row=1, min_col=2, max_col=3, max_row=3):
    for cell in col:
        print(cell)

# 获取所有行的cell，Worksheet.rows, 它是一个可迭代对象, 需要转换为tuple再进行访问
rows =  sheet.rows
print(tuple(rows))

# 以列方式获取所有cell, Worksheet.columns, 它是一个可迭代对象, 需要转换为tuple再进行访问
cols =  sheet.columns
print(tuple(rows))

# 获取指定位置单元格的值
data = sheet['A4'].value
print('data:', data)
# data: assd


# 获取所有值, 返回值是一个元组
data = tuple(sheet.values)
print("sheet的所有值为:", data)

（4）获取sheet的最大/小行数与列数

# sheet.max_row, 最大行数
data = sheet.max_row
print('当前sheet的最大行数为:{}'.format(data))

# sheet.min_row，最小行数
data = sheet.min_row
print('当前sheet的最小行数为:{}'.format(data))

# sheet.max_column，最大列数
data = sheet.max_column
print('当前sheet的最大列数为:{}'.format(data))

# sheet.min_column，最小列数
data = sheet.min_column
print('当前sheet的最小列数为:{}'.format(data))

（5）向Excel文件中写入数据

# 获取当前正在活动的sheet
sheet = workbook.active

# 向指定的cell赋值
sheet.cell(row, column, value)

# 保存excel文件到指定路径，不传值则默认覆盖原文件
workbook.save(filename=path)

注意：在向Excel文件写数据时，应该关闭Excel文件以避免报错

2、基于常用方法的封装(封装一个工具类处理Excel文件)

# -*- coding: utf-8 -*-
# description: 封装对excel文件的操作

import openpyxl

class ExcelTool:
    """
    封装一个处理excel文件的工具类
    """
    def __init__(self, path):
        """
        加载文件, 创建一个workbook对象
        :param path, excel文件路径
        """
        self.workbook = openpyxl.load_workbook(path)
        self.worksheet = None

    def get_sheet(self, var):
        """
        通过索引或者名字获取sheet
        :param var: 索引or名字
        :return: sheet
        """
        if isinstance(var, int):
            self.worksheet = self.workbook[self.workbook.sheetnames[var]]
            return self.worksheet

        if isinstance(var, str):
            self.worksheet = self.workbook[var]
            return self.worksheet

    def get_cell_value(self, row, col):
        """
        获取cell的值
        :param row: cell所在行
        :param col: cell所在列
        :return: cell的值
        """
        try:
            return self.worksheet.cell(row=row, column=col).value
        except BaseException as e:
            return None

    def get_max_row(self):
        """
        获取最大行数
        :return: 最大行数
        """
        return self.worksheet.max_row

    def get_min_row(self):
        """
        获取最小行数
        :return: 最小行数
        """
        return self.worksheet.min_row

    def get_max_col(self):
        """
        获取最大列数
        :return: 最大列数
        """
        return self.worksheet.max_column

    def get_min_col(self):
        """
        获取最小列数
        :return: 最小列数
        """
        return self.worksheet.min_column

    def get_all_value(self):
        """
        获取所有数据
        :return:
        """
        return tuple(self.worksheet.values)

    def get_row_data(self, row):
        """
        获取指定行的所有数据
        :param row: 指定行
        :return: 所有行数据组成的列表
        """
        return list(self.get_all_value()[row])

    def get_col_data(self, col):
        """
        获取指定列的所有数据
        :param col: 指定列, 如'A'
        :return: 所有列数据组成的列表
        """
        col_data_list = []
        for i in self.worksheet[col]:
            col_data_list.append(i.value)
        return col_data_list

    def write_data(self, row, col, value, path):
        """
        写入数据
        :param row: 指定cell所在行
        :param col: 指定cell所在列
        :param value: cell的值
        :param path: 保存文件路径
        """
        try:
            self.worksheet = self.workbook.active
            self.worksheet.cell(column=col, row=row, value=value)
            self.workbook.save(path)
        except BaseException as e:
            print(e)
            return None

if __name__ == '__main__':
    path = './test_case.xlsx'
    workbook = ExcelTool(path)

    workbook.get_sheet(1)
    print(workbook.get_sheet(1))
    # >>> <Worksheet "Sheet2">

    # 获取指定sheet
    workbook.get_sheet('Sheet1')
    print(workbook.get_sheet('Sheet1'))
    # >>> <Worksheet "Sheet1">

    # 获取指定cell的值
    data = workbook.get_cell_value(1, 3)
    print("data:", data)

    # 获取当前sheet某一行、列的数据
    data = workbook.get_row_data(2)
    print('第2行的数据为:', data)

    data = workbook.get_col_data('C')
    print('第c列的数据为:', data)

    # 写入数据
    workbook.write_data(11, 2, 'excel dispose', path)