openpyxl操作Excel

一，python操作Excel常用工具

  - openpyxl --支持xlsx 新型格式的读写，读取速度还可以

  - tablib -- 支持多种格式读写 xlsx，xls，CSV，json，yaml，html，pd，读取速度稍微有点慢

  - pandas --功能强大

二，openpyxl操作Excel

1，导入openpyxl

import openpyxl

2，初始化wb对象

wb = openpyxl.Workbook() # 创建excel文件

ws = wb.active  # 激活worksheet 也就是获取默认的表单。

wb = openpyxl.load_workbook(file)  #打开已有的excel文件

3,获取表单的方式

-1 sheetnames = wb.sheetnames   # 拿到的是sheet的name，是字符串格式的列表

-2 sheets = wb.worksheets  #拿到的是sheet对象存放在列表中

-3 sheet = wb.get_sheet_by_name("Sheet1") ,# 通过表单名称获取，但是此方法会有警告，已过时。

-4 sheet = wb["Sheet1"]

注意：Pycharm不支持sheet属性提示

4，读取单元格，行和列

注意：行和列是从1开始，不是python中的从0开始

-1，获取单元格数据

cell = sheet.cell(1,2)  # 获取单元格的对象

cell.value  # 获取单元格的值

-2，获取行数据

rows =sheet[1]  # 获取第一行的所有对象

rows = sheet[1:3]  # 获取第一行到第三行数据对象，第三行是包含的

for row in rows:

　　row.value   # 获取行所有数据的值

-3，获取列数据

columns = sheet["A"]  # 获取A列所有对象

columns = sheet["A:D"]  # 获取A到D列对象

for columnin columns:

　　columnin .value   # 获取A列所有数据的值

-4 ，获取所有的数据

sheet.rows 里面是每一行的数据，没一行又由一个tuple包裹

sheet.columns 里面是每一列的数据，每个tuple是每一列的单元格。

total_data = list(sheet.rows)  

for row in tatal_data:

　　for cell in row:

　　　　print(cell.value)

 

for column in sheet.columns :

　　for cell in column :

　　　　print(cell.value)

-5 写入数据

cell.value = 12

wb.save(file)  # 保存

wb.close  # 关闭

5，其他

获取最大行

sheet.max_row

获取最大列

sheet.max_column

6，删除工作表

# 方式一
wb.remove(sheet)
# 方式二
del wb[sheet]

7，设置单元格的风格

-1 需要导入的类

from openpyxl.styles import Font, colors, Alignment

 

-2 字体

# 指定了等线24号，加粗斜体，字体颜色红色。直接使用cell的font属性，将Font对象赋值给它
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
 
sheet['A1'].font = bold_itatic_24_font

 

-3 对齐方式

# 设置B1中的数据垂直居中和水平居中
#直接使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数。
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

 

-4  设置行高和列宽

# 第2行行高
sheet.row_dimensions[2].height = 40
# C列列宽
sheet.column_dimensions['C'].width = 30

三，封装操作Excel类

""""""
from openpyxl.worksheet.worksheet import Worksheet

"""
打开表单
读取标题
读取所有数据
指定单元格写入数据（使用静态方法，不要使用实例方法）
"""
from openpyxl import load_workbook
class ExcelHandler():
    ''''''
    '''操作Excel'''
    def __init__(self,file):
        """初始化参数"""
        self.file = file

    def open_sheet(self,name) ->Worksheet:
        """打开表单
        在函数或者方法的后面加 -> 类型；表示此函数返回的是一个这样的类型
        函数注解
        """
        wb = load_workbook(self.file)
        sheet = wb[name]
        wb.close()
        return sheet
    def header(self,sheet_name):
        """获取表头"""
        sheet =self.open_sheet(sheet_name)
        headers =[]
        for i in sheet[1]:
            headers.append(i.value)
        return headers

    def read(self,sheet_name):
        """读取所有的数据"""
        sheet = self.open_sheet(sheet_name)
        rows = list(sheet.rows)
        # 获取标题
        headers = self.header(sheet_name)
        data = []
        for row in rows[1:]:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
                # 列表转成字典：要和header去zip
                data_dict = dict(zip(headers,row_data))
            data.append(data_dict)
        return data

    @staticmethod
    def write(file,sheet_name,row,colum,data):
        # 打开文件
        wb = load_workbook(file)
        sheet = wb[sheet_name]
        # 修改单元格
        sheet.cell(row,colum).value = data
        # 保存
        wb.save(file)
        # 关闭（必须关闭）
        wb.close()
if __name__ == '__main__':
    excel = ExcelHandler(r"C:\cases.xlsx")
    sheet = excel.read("Sheet1")

    print(sheet)
    excel.write(r"C:\cases.xlsx","Sheet1",3,1,'new_value')