【Python】Excel文件读写操作

Excel操作库-OpenPyXL

Python的Excel操作库

Python的Excel处理库主流的有xlrd、xlwt，支持.xls格式文件读写，由于目前用.xlsx格式的Excel多，还是选择OpenPyXL库进行处理，当然还有很多其他库，比如xlsxwriter、pyxlsb、pylightxl, 日后再研究一下其他库的操作。

OpenPyXL简介

OpenPyXL是一个读写Excel2010包括xlsx/xlsm/xltx/xltm格式文件的Python库，可以参考 官方文档。

• 基本概念
  • workbook：工作簿，也就是一个Excel文件
  • sheet：表单，每个Excel中至少有一个表单
  • rows：表单中的行，可以进行按行读取Excel数据
  • column：表单中的列
  • cell：单元格

OpenPyXL安装

使用pip进行安装：
pip install openpyxl

创建Excel文件

OpenPyXL的workbook就是一个Excel文件，所以创建一个workbook对象进行保存，就得到一个新的Excel文件啦，perfect！

import os
import openpyxl

# 文件路径
folder_path = "E:\\coding"
file_name = "test.xlsx"
file_path = os.path.join(folder_path, file_name)

# 创建workbook对象
workbook = openpyxl.Workbook()
workbook.save(file_path)

创建sheet表单

• 使用openpyxl.Workbook()创建的工作簿会有一个默认的sheet，用workbook.active属性就可以获取到这个sheet页。
• 默认生成的sheet页名字为“sheet”，可以对workbook.active.title属性赋值修改名字。
• 创建新的sheet页可以用workbook.create_sheet(sheetname)函数

# 获取workbook当前活跃sheet页
sheet = workbook.active

# 修改sheet页名字
sheet.title = "newName"

# 创建新的sheet页
new_sheet = workbook.create_sheet("newSheet") # 默认插入到工作簿的最后一个sheet页后
new_sheet2 = workbook.create_sheet("newSheet2", 1) # 插入到第1个工作簿后面 

# 获取当前工作簿所有的sheet页名称
print(workbook.sheetnames)

# 通过sheet页名字获取工作簿的其他sheet页
sheet_1 = workbook["newSheet"] 
	

读取Excel文件

读取已有的Excel文件，一行搞定！

workbook = openpyxl.load_workbook(file_path )
sheet = workbook.active

处理文件数据

当然，读取文件后，要把Excel中的单元格数据读出来，这才是我们想要哒，只有一个文件是没用的(狗头

对sheet页中的数据进行处理，按照我们对Excel的了解，现在每个sheet页里面就是单元格，也就是cell，用Excel的单元格方式就可以直接读取到单元格的内容：

# 读取A4单元格数据，cell类型，<class 'openpyxl.cell.cell.Cell'>
cell1 = sheet['A4'] 
# 对单元格赋值
cell1.value = 1
# 或者
cell2 = sheet.cell(row=1, column=4)

Excel里面的单元格还可以批量获取，同理可得：

# 读取A列，元组类型，<class 'tuple'>
column_A = sheet['A'] 
# 读取A列和B列，元组类型，<class 'tuple'>
column_AB = sheet['A:B']
# 读取全部列，生成器类型，<class 'generator'>
column = sheet.columns

# 读取第1行，元组类型，<class 'tuple'>
row_1 = sheet[1]
# 读取第1行和第2行，元组类型，<class 'tuple'>
row_12 = sheet[1:2]
# 读取全部行，生成器类型，<class 'generator'>
row = sheet.rows

这些都可以获取到Excel的单元格，但是通常的需求是获取单元格的值，对值进行操作；

• sheet.values属性将sheet页中的值按行迭代

data_list = []
for row in sheet.values:
	data = []
	for value in row:
		print(value) # 每个单元格的值
		data.append(value)
		data_list.append(data) # 将sheet数据转为list

Excel处理类

日常工作中对Excel的操作主要有创建Excel文件/sheet页、打开Excel文件/sheet页、读取数据为List、写入List数据到Excel文件，因此写一个Excel类，在不同的代码中引用可以提高效率：

import os
import openpyxl
# 复制表格行元组为list
def row_data_type_change(row):
    data = []
    for i in row:
        data.append(i.value)
    return data

class Excel(object):
    rootpath = ""
    filepath = ""
    workbook = ""
    sheet = ""

    @classmethod
    def create_file(cls, rootpath, filename):
        '''create an excel file'''
        try:
            cls.rootpath = rootpath
            cls.filepath = os.path.join(cls.rootpath, filename)
            cls.workbook = openpyxl.Workbook()
            cls.workbook.save(cls.filepath)
        except IOError as e:
            print("when create this file:" + cls.filepath + "\\n" + str(e))

    @classmethod
    def openfile(cls, rootpath, filename):
        '''open an excel file'''
        try:
            cls.rootpath = rootpath
            cls.filepath = os.path.join(cls.rootpath, filename)
            cls.workbook = openpyxl.load_workbook(cls.filepath)
        except IOError as e:
            print("when open this file:" + cls.filepath + "\\n" + str(e))

    @classmethod
    def opensheet(cls, sheetname):
        '''open a sheet'''
        try:
            cls.sheet = cls.workbook[sheetname]
        except Exception as e:
            print("when open this sheet:" + sheetname + "\n" + str(e))

    @classmethod
    def createsheet(cls, sheetname):
        '''create a new sheet'''
        try:
            cls.sheet = cls.workbook.create_sheet(sheetname)
        except Exception as e:
            print("when create this sheet:" + sheetname + "\n" + str(e))

    @classmethod
    def getdatalist(cls):
        '''read excel data into list'''
        data_list = []
        for row in cls.sheet.rows:
            data_list.append(row_data_type_change(row))
        return data_list

    @classmethod
    def writesheet(cls, data_list):
        '''write the list into an excel file'''
        i = 1
        for data in data_list:
            if (isinstance(data, list)):
                column_num = 1
                for tmp in data:
                    cls.sheet.cell(row=i, column=column_num).value = tmp
                    column_num = column_num + 1
            i = i + 1
        cls.workbook.save(cls.filepath)


if __name__ == '__main__':
    # 文件夹路径
    fold_path = "E:\\coding"

    # 打开文件
    excel = Excel()
    excel.rootpath = fold_path
    excel_file_name = "images.xlsx"
    excel.openfile(fold_path, excel_file_name)

    # 打开sheet
    attr_sheet_name = "对象API参数"
    excel.opensheet(attr_sheet_name)

    # 读取sheet页的数据
    api_list = excel.getdatalist()

    # 创建Excel文件
    new_excel_file_name = "new_images.xlsx"
    excel.create_file(fold_path, new_excel_file_name)

    # 写入Excel文件
    sheet_name = "api"
    if sheet_name in excel.workbook.sheetnames:
        excel.opensheet(sheet_name)
    else:
        excel.createsheet(sheet_name)
    excel.writesheet(api_list)