Python3利用openpyxl处理Excel文件

Python3利用openpyxl处理Excel文件

安装：

安装库：
pip install openpyxl

Excel表中有图像，需要安装pillow库

pip install Pillow

创建Excel

Workbook 方法

#将workbook方法导入，该方法来自于openpyxl这个库
from openpyxl import Workbook 

# 使用workbook方法创建一个对象
wb = Workbook()

#创建的对象会默认生成sheet1，通过active方法来获取sheet1
sh = wb.active

#使用title方法对sheet进行重命名
sh.title = '工龄'

sh['A1'] = '名字' #插入数据
sh['B1'] = '工龄'

name_ages = [
    ('李白',10),
    ('上官婉儿',9),
    ('妲己' ,9),
    ('虞姬', 2),
    ('貂蝉', 50),
    ('司马懿', 52),
    ('云中君',43),
    ('公孙离', 23),
]

#对列表进行遍历
row = 2
for name in name_ages:
    name1,age = name
    sh.cell(row,1).value = name1 #对单元格进行赋值
    sh.cell(row,2).value = age
    row +=1
wb.save('工龄1.xlsx')#保存文件

读取Excel数据

load_workbook方法

from openpyxl import load_workbook
#读取文件信息
def readexcel(file,sheetname):
    wb = load_workbook(file)
    #指定sheet
    sheet = wb[sheetname]

    #获取最大行
    row_max = sheet.max_row
    #获取最大列
    col_max = sheet.max_column

    #读取回来的数据
    info = []
    for row in range(2,row_max+1):
        #存放我们取的一行数据
        row_list = []
        for col in range(1,col_max+1):
            row_list.append(sheet.cell(row,col).value)

        info.append(row_list)

    wb.close()

    return info

修改Excel中的数据

load_workbook方法

修改单元格内容

from openpyxl import load_workbook #导入方法

#打开Excel文件
wb = load_workbook('工龄信息.xlsx')

#指定要操作的sheet，读取工作表
sheet = wb['工龄']

#获取当前sheet的最大行数
row = sheet.max_row
#获取当前sheet的最大列数
columns = sheet.max_column

#获取sheet对象，
#这里面有一个sheetnames可以获取当前表格所有sheet存放在一个列表中
sheet = wb['工龄表']

sheet['A1'] = '修改数据'

#指定不同的文件名，可以另存为别的文件，名字和原来相同，会在原文件修改
wb.save('工龄信息.xlsx')

插入行、插入列、删除行、删除列

sheet 对象的 insert_rows 和 insert_cols 方法，分别用来插入行和列

sheet 对象的 delete_rows 和 delete_cols 方法，分别用来删除行和列

from openpyxl import load_workbook

#打开一个已有的文件
wb = load_workbook("工龄信息.xlsx")

#获取指定sheet对象
sheet = wb['工龄表']

#在第二行的位置插入1行
sheet.insert_rows(2)

#在第四行的位置插入5行
sheet.insert_rows(4,5)

#在第二列的位置插入1列
sheet.insert_cols(2)

#在第二列的位置插入3列
sheet.insert_cols(2,3)

#在第四行的位置删除五行
sheet.delete_rows(3,5)

#在第二列的位置删除一列
sheet.delete_cols(2)

#保存文件(文件名相同在原文件修改，文件名不同则另存为)
wb.save('hello.xlsx')

单元格文字格式设置（ 颜色、字体、大小）

from openpyxl import load_workbook
#需要导入Font对象和colors 颜色常量
from openpyxl.styles import Font,colors

#打开Excel文件，获取文件对象信息
wb = load_workbook('工龄信息.xlsx')

#指定sheet
sheet = wb['工龄表']

#设置指定单元格字体颜色
sheet['A1'].font = Font(color=colors.RED, #使用预置的颜色常量
                        size=12,      #设定文字大小
                        bold=True,    #设定为粗体
                        italic=True   #设定为斜体
                        )

#可以根据RGB十六进制数字表示颜色
sheet['B1'].font = Font(color='00CED1')

# 指定整行 字体风格， 这里指定的是第5行
r_font = Font(color="8B0000")
# 第 1 到 50 列
for c in range(1, 51):
    sheet.cell(row=5, cols=c).font = r_font

#指定整列 字体风格 这里指定第一列
c_font = Font(color='006400')
#取第一行到第十行
for r in range(1,11):
    sheet.cell(row=r,cols=1).font = c_font

wb.save('工龄信息.xlsx')

背景色设置（模式填充）

from openpyxl import load_workbook
#导入模式填充
from openpyxl.styles import PatternFill

#加载Excel文件
wb = load_workbook('工龄信息.xlsx')
#指定sheet
sheet = wb['工龄表']

#指定 某个单元格背景色
sheet['A1'].fill = PatternFill('solid', 'BCEE68')

#设置第二列的背景色
for x in range(1,11):
    #第二列10个单元格背景色设置为墨绿色
    sheet.cell(x,2).fill = PatternFill('solid', '2F4F4F')

#设置行背景色
for y in range(1,11):
    #设置第三行10个单元格背景为黄色
    sheet.cell(3,y).fill = PatternFill('solid', 'FFFF00')

wb.save('工龄信息.xlsx')

单元格插入图片

from openpyxl import Workbook, load_workbook
#导入图片操作模块
from openpyxl.drawing.image import Image

#创建Excel表格
wb = Workbook()
#获取自动生成的sheet名
sheet = wb.active

# 设置文字图片单元格的行高列宽
column_width = 12.25
row_height = 80.10

# 修改列D的列宽
sheet.column_dimensions['D'].width = column_width
# 修改行3的行高
sheet.row_dimensions['3'].height = row_height

img = Image('1.jpeg')
newsize = (90, 90)
# 这两个属性分别是对应添加图片的宽高
img.width, img.height = newsize

# 向D3单元格内指定添加图片
sheet.add_image(img, 'D3')

#保存
wb.save('工龄表.xlsx')