python3处理excel【三方库—openpyxl】

openpyxl为三方库，需要 pip install openpyxl安装。

一、标准使用方式：

1. 创建excel表格

# -*- coding:utf-8 -*-

import os
import openpyxl
from ProcessorFile import config #定义全局变量，注意模块导入指导：https://www.jb51.net/article/138206.htm

student_info = [{"name":"Jack", "gender":"man"}, {"name":"Mary", "gender":"woman"}]
excel_file = os.path.join(config.output_path, 'student.xlsx')

book = openpyxl.Workbook() #创建一个新 excel工作簿
sheet = book.create_sheet('base_info') #创建一个新 sheet页

for i in range(len(student_info)):
    # sheet.cell(row=i, column=0).value = student_info[i]['name']
    '''如上语句报错：ValueError: Row or column values must be at least 1； 
行和列至少都要从1开始；openpyxl，首行首列都从1开始'''
    sheet.cell(row=i+1, column=1).value = student_info[i]['name']
    sheet.cell(row=i+1, column=2).value = student_info[i]['gender']

book.save(excel_file) #这句一定要执行，这是数据插入之后的保存动作
print("create excel successful!")

效果图：

2. 更新excel表格

# -*- coding:utf-8 -*-

import os
import  openpyxl
from ProcessorFile import config #定义全局变量，注意模块导入指导：https://www.jb51.net/article/138206.htm

excel_file = os.path.join(config.output_path, 'student.xlsx')
book = openpyxl.load_workbook(excel_file) #加载已经创建好的excel表格
sheet = book['base_info'] #获取sheet页面

rows = sheet.max_row #获取当前sheet页最大行数
columns = sheet.max_column #获取当前sheet页最大列数
print(rows, columns)

for i in range(rows):
    for j in range(columns):
        sheet.cell(row=i+1, column=j+1).value = "null"

book.save(os.path.join(config.output_path, 'student_update.xlsx')) #这句一定要执行，这是数据插入之后的保存动作
print("update excel successful!")

效果图：

3. 获取excel表格内容方法：三个对象：工作薄(workbook)、工作表(worksheet)、 单元格(cell)

import os
import openpyxl
from openpyxl.styles import Alignment, Border, Side, PatternFill, Font, colors

def getCell():
    #表格三个对象： 工作薄(workbook)、工作表(worksheet)、 单元格(cell)
    ##该方法目的获取表格数据各种方法
    book = openpyxl.load_workbook(excel_path)
    sheet = book['base_info']
    ##第一类：基本方法,：获取具体单元格
    A1 = sheet.cell(1,1) #return <Cell 'base_info'.A1>
    A1 = sheet.cell(row=1, column=1) #<Cell 'base_info'.A1>
    A1_value = A1.value #return value
    print(A1_value)

    ##第二类：基本方法：获取范围（域）
    area = sheet['A1:H6']#return ((<Cell>,<Cell>),(<Cell>,<Cell>))
    print(area)
    for r in area:
        for c in r:
            print(c) #return <Cell>

    ##第二种方法：根据最大行、最大列村换
    rows = sheet.max_row  # int
    columns = sheet.max_column  #int
    for row in range(1, rows+1): #Row or column values must be at least 1
        for col in range(1, columns+1):
            cell = sheet.cell(row,col) #<Cell 'base_info'.A1>
            value = cell.value #<class 'str'>/<class 'int'>返回值
    
    ##第三种方法：使用 item迭代
    for row in sheet.iter_rows():
        print(row) # return (cell<A1>, cell<B1>, cell<C1>)
        for co in row:
            print(co) #return cell
            pass

    for col in sheet.iter_cols():
        print(col) # return (cell<A1>, cell<A2>, cell<A3>)
        for ro in col:
            print(ro) #return cell    

    print("get cell successful!")

if __name__ == '__main__':
    # create_table()
    getCell()

4. excel表格样式简单设置

import os
import openpyxl
from openpyxl.styles import Alignment, Border, Side, PatternFill, Font, colors
from openpyxl.utils import get_column_letter, column_index_from_string

def setStyle():
    '''
    该方法简单设置表格样式
    '''
    book = openpyxl.load_workbook(excel_path)
    sheet = book['base_info']
    rows = sheet.max_row
    columns = sheet.max_column

    ###########合并单元格
    sheet.merge_cells(start_row=1, start_column=1,end_row=1, end_column=4)
    sheet.cell(1,1).value = '男儿何不带吴钩，收取关山五十州。请君暂上凌烟阁，若个书生万户侯。'
    sheet.merge_cells(start_row=4, start_column=1,  end_row=6,end_column=1)
    sheet.cell(4,1).value = '英雄'
    
    ########设置行高、列宽
    sheet.row_dimensions[1].height = 25
    sheet.column_dimensions['A'].width = 50
    print(get_column_letter(1)) # return A，列编号整数转变为字母
    print(column_index_from_string('B')) # return 2，列编号字母转变为整数

    #######设置单元格样式
    align = Alignment(horizontal='center', vertical='center')
    font = Font(name='微软雅黑',size=10, bold=True, color=colors.RED)
    border = Border(left=Side(border_style='medium',color=colors.BLACK),right=Side(border_style='medium',color=colors.BLACK),top=Side(border_style='medium',color=colors.BLACK),bottom=Side(border_style='medium',color=colors.BLACK))
    fill = PatternFill(fill_type='solid', fgColor=colors.GREEN)

    #Cell对象属性：font(字体)/fill(填充)/border(边框)/alignment(位置)/style/protection/number_format/quotePrefix/pivotButton
    sheet.cell(1,1).alignment = align
    sheet.cell(1,1).font = font
    sheet.cell(row=1, column=1).fill = fill

    sheet_area = sheet['A1:H6']
    for row in sheet_area:
        for col in row:
            col.border = border

    book.save(excel_path)
    print("update table successful!")

if __name__ == '__main__':
    # create_table()
    setStyle()

二、高级用法，参见openpyxl官方文档：https://openpyxl.readthedocs.io/en/stable/