python3：openpyxl解析和生成excel的常用方法

向右看齐 2024-03-27 08:54 21阅读 0赞

## 解析 ##

### 打开已经存在的工作簿 ###

**openpyxl.load\_workbook()方法**

from openpyxl import load_workbook
    
    wb2 = load_workbook('test.xlsx')
    print wb2.get_sheet_names()
    
    '''
    ['Sheet2', 'New Title', 'Sheet1']
    '''

## 生成 ##

### 创建一个工作簿 ###

from openpyxl import Workbook
    
    # 一个工作簿(workbook)在创建的时候同时至少也新建了一张工作表(worksheet)
    wb = Workbook()
    # openpyxl.workbook.Workbook.active()调用得到正在运行的工作表
    ws = wb.active

### 新建一张表 ###

# 默认插在工作簿末尾
     ws1 = wb.create_sheet() 
    # 插入在工作簿的第一个位置
    ws2 = wb.create_sheet(0)

在创建工作表的时候系统自动命名。他们按照序列依次命名 (Sheet, Sheet1, Sheet2, …)

**修改工作表的名称**

ws.title = "New Title"
    # 标签栏的背景色默认为白色，改变标签栏的字体颜色
    ws.sheet_properties.tabColor = "1072BA"

### 获取工作表 ###

# 获取一个工作表
    ws3 = wb["New Title"]
    # 得到工作簿的所有工作表
    wb4 = wb.get_sheet_names()
    # 循环得到所有的工作表
    for sheet in wb:
       print(sheet.title)

### 操作数据 ###

#### 通过索引获取 ####

# 通过索引得到单元格数据，将返回在A4处的单元格，如果不存在将在A4新建一个
    c = ws['A4']
    # 为单元格的值赋值
    ws['A4'] = 4

#### 通过cell获取 ####

c = ws.cell('A4')
    # 根据行列值获取单元格
    d = ws.cell(row = 4, column = 2)

#### 特点 ####

上面的两种方法，如果遍历了单元格而非想要使用它们也将会在内存当中创建

for i in range(1,101):
       for j in range(1,101):
            ws.cell(row = i, column = j)

**代码将会在内存中创建100\*100个单元格**

#### 获取多个单元格 ####

##### 方式1：使用切片 #####

cell_range = ws['A1':'C2']

##### 方式2 #####

tuple(ws.iter_rows('A1:C2'))
    '''
    ((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>),
     (<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>))
    '''
    for row in ws.iter_rows('A1:C2'):
                  for cell in row:
                        print cell
    '''
    <Cell Sheet1.A1>
    <Cell Sheet1.B1>
    <Cell Sheet1.C1>
    <Cell Sheet1.A2>
    <Cell Sheet1.B2>
    <Cell Sheet1.C2>
    '''

##### 方式3：迭代文件中所有的行或者列 #####

**openpyxl.worksheet.Worksheet.xxx方法**

###### rows() ######

ws = wb.active
    ws['C9'] = 'hello world'
    ws.rows
    '''
    ((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
    (<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
    (<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
    (<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
    (<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
    (<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
    (<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
    (<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
    (<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
    '''

###### columns() ######

ws.columns
    '''
    ((<Cell Sheet.A1>,
    <Cell Sheet.A2>,
    <Cell Sheet.A3>,
    <Cell Sheet.A4>,
    <Cell Sheet.A5>,
    <Cell Sheet.A6>,
    ...
    <Cell Sheet.B7>,
    <Cell Sheet.B8>,
    <Cell Sheet.B9>),
    (<Cell Sheet.C1>,
    <Cell Sheet.C2>,
    <Cell Sheet.C3>,
    <Cell Sheet.C4>,
    <Cell Sheet.C5>,
    <Cell Sheet.C6>,
    <Cell Sheet.C7>,
    <Cell Sheet.C8>,
    <Cell Sheet.C9>))
    '''

### 数据存储（为单元格赋值） ###

#### 通过cell ####

c = ws.cell('A4')
    # 根据行列值获取单元格
    d = ws.cell(row = 4, column = 2)
    
    c.value = 'hello, world'
    print(c.value)
    '''
    hello, world
    '''
    d.value = 3.14
    print(d.value)
    '''
    3.14
    '''

也可以保存python中的其他数据格式

### 保存到文件 ###

#### 直接保存 ####

**openpyxl.workbook.Workbook.save()方法**

wb = Workbook()
    wb.save('test.xlsx')

`这个操作将会在没有认识提示的情况下用现在写的内容，覆盖掉原文件中的所有内容`

#### 保存为一个模板 ####

wb = load_workbook('test.xlsx')
     wb.save('test.xltx', as_template=True)

`如果as_template=False(默认)，则将文件或模板保存为文件`

wb = load_workbook('test.xltx')
    wb.save('test.xlsx', as_template=False)

### 清理不需要的单元格 ###

### 合并单元格：merge\_cells ###

预期效果  
![在这里插入图片描述][801a7014de8c4e50a0cf51b469c009e6.png]  
merge\_cells 的使用方法有两种，一种是指定range\_string参数，一种是指定具体的行与列

#### 方法1：指定range\_string参数 ####

import openpyxl
    
    def merge_cell():
        wb = openpyxl.Workbook()        # 创建一个excel文件
        sheet = wb.active               # 获得一个的工作表
        sheet.title = "省市信息"
    	# A1:A3 ,一个是合并区域的左上角，一个是合并区域的右下角单元格，第二种方法是
        sheet.merge_cells("A1:A3")
        sheet.cell(1, 1, '吉林省')
        citys = ["长春", "吉林", "四平"]
        for index, city in enumerate(citys):
            sheet.cell(index+1, 2, city)
    
        wb.save("./data/省市信息.xlsx")
    
    
    if __name__ == '__main__':
        merge_cell()

#### 方法2：指定具体的行与列 ####

**指定合并区域的开始和结束行列号**

sheet.merge_cells(start_row=1, end_row=3, start_column=1, end_column=1)

### 读取合并后的单元格 ###

`合并后，单元格里的值存储在合并区域的左上角单元格里，因此要读取左上角单元格。`

def read_merge_cell():
        wb = openpyxl.load_workbook("./data/省市信息.xlsx")
        sheet = wb.get_sheet_by_name("省市信息")
        print(sheet.cell(1, 1).value)       # 吉林省
        print(sheet.cell(2, 1).value)       # None
        print(sheet.cell(3, 1).value)       # None
    
    if __name__ == '__main__':
        read_merge_cell()

### 设置自动列宽 ###

思路：

1.  通过遍历，计算最大列宽，同时注意中文宽度；
2.  调整列宽。

from openpyxl import load_workbook, workbook
    from openpyxl.utils import get_column_letter
    
    # 第一步：计算每列最大宽度
    def getOpenpyxlColWidth(ws):
        colWidths = []
        for i in range(1, ws.max_column + 1):  # 每列循环
            colWidth = 1  # 定义初始列宽，并在每个行循环完成后重置
            for j in range(1, ws.max_row + 1):  # 每行循环
                sz = ws.cell(row=j, column=i).value  # 每个单元格内容
                if isinstance(sz, str):  # 中文占用多个字节，需要分开处理
                    lk = len(sz.encode('gbk'))  # gbk解码一个中文两字节，utf-8一个中文三字节，gbk合适
                else:
                    lk = len(str(sz))
                if colWidth < lk:
                    colWidth = lk  # 借助每行循环将最大值存入lk中
            colWidths.append(colWidth)  # 将每列最大宽度加入列表。
        return colWidths
    
    
    wb = load_workbook('自动列宽.xlsx')
    ws = wb.active
     
    
    if __name__ == '__main__':
    	wb = load_workbook('自动列宽.xlsx')
    	ws = wb.active
    	colWidths=getOpenpyxlColWidth(sheet)
    	# 第二步：设置列宽
    	for i in range(1, ws.max_column +1):
    	    k = get_column_letter(i) #将数字转化为列名,26个字母以内也可以用[chr(i).upper() for i in range(97, 123)]，不用导入模块
    	    ws.column_dimensions[k].width = lks[i-1]+2 #设置列宽，一般加两个字节宽度，可以根据实际情况灵活调整
    	wb.close()
    	wb.save('自动列宽.xlsx')

参考：https://blog.csdn.net/crammy/article/details/120469646

### 设置行高 ###

# 设置行高 第一行 40
    sheet1.row_dimensions[1].height = 40
    # 设置第二行行高25
    sheet1.row_dimensions[2].height = 25

### 设置列宽 ###

# 设置列宽 A20 F10 E10
    sheet1.column_dimensions['A'].width = 20
    sheet1.column_dimensions['F'].width = 10
    sheet1.column_dimensions['E'].width = 10

## 将数据保存到excel中 ##

### openpyxl结合pandas ###

# openpyxl 结合 pandas
    # 本例演示了 sheet 和 DataFrame 之间如何互相转换
    
    from openpyxl import *
    from openpyxl.utils.dataframe import dataframe_to_rows
    from itertools import islice
    import pandas as pd
    import sys
    
    path = sys.path[0] + "\demo3.xlsx"
    wb = Workbook()
    sheet1 = wb.active
    sheet1.title = "sheet1"
    
    
    data1 = {
        
        "name": ['zhao', 'qian', 'sun', 'li', 'zhou'],
        "age": [40, 25, 22, 28, 28],
        "gender": ['M', 'F', 'M', 'M', 'F'],
        "city": ['beijing', 'beijing', 'shanghai', 'beijing', 'shanghai']
    }
    df1 = pd.DataFrame(data=data1)
    # DataFrame 数据写入 sheet
    #    index 用于指定是否需要写入索引列的数据
    #    header 用于指定是否需要写入标题数据
    for row in dataframe_to_rows(df1, index=False, header=True):
        sheet1.append(row)
    '''
    保存到 sheet 后的数据是这样的
    name	age	gender	city
    zhao	40	M	beijing
    qian	25	F	beijing
    sun	22	M	shanghai
    li	28	M	beijing
    zhou	28	F	shanghai
    
    '''
    
    
    # sheet 转 DataFrame
    #   firstColumnIsIndexColumn 用于指定第一列是否是索引列
    #     True 把第一列作为 DataFrame 的索引列
    #     False 自动生成 DataFrame 的索引列
    # 注：本函数会将 sheet 的第一行转换为 DataFrame 的列名
    def sheetToDataFrame(sheet, firstColumnIsIndexColumn):
        data = sheet.values
        if (firstColumnIsIndexColumn):
            cols = next(data)[1:] 
            data = list(data)
            idx = [r[0] for r in data]
            data = (islice(r, 1, None) for r in data)
        else:
            cols = next(data)[:] 
            data = list(data)
            idx = None
            data = (islice(r, 0, None) for r in data)
        return pd.DataFrame(data, index=idx, columns=cols)
    
    df2 = sheetToDataFrame(sheet1, True)
    df3 = sheetToDataFrame(sheet1, False)
    
    print(df2)
    '''      age gender      city
    zhao   40      M   beijing
    qian   25      F   beijing
    sun    22      M  shanghai
    li     28      M   beijing
    zhou   28      F  shanghai'''
    print(df3)
    '''
       name  age gender      city
    0  zhao   40      M   beijing
    1  qian   25      F   beijing
    2   sun   22      M  shanghai
    3    li   28      M   beijing
    4  zhou   28      F  shanghai
    '''
    
    
    # 保存 excel
    wb.save(path)

[801a7014de8c4e50a0cf51b469c009e6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/bc025091703447c9b52476c90b8774f3.png