python3操作Excel

一些使用模块

由于设计目的不同，每个模块通常着重于某一方面功能，各有所长。

xlwings

可结合 VBA 实现对 Excel 编程，强大的数据输入分析能力，同时拥有丰富的接口，结合 pandas/numpy/matplotlib 轻松应对 Excel 数据处理工作。同时可以对现有的表格进行修改，对表格的内容及格式无任何影响。

1 import xlwings as xw
2 workbook = xw.Book("../report/Performance_Test_Report.xlsx")
3 data_range = workbook.sheets('FPS_detail').range("A22")
4 data_range.value = [1,2,3]
5 workbook.save()

 

openpyxl

简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有，图表功能是其一大亮点，缺点是对 VBA 支持的不够好。不支持对现有文件进行修改

1 import openpyxl
2 workbook = openpyxl.Workbook("../report/Performance_Test_Report.xlsx")
3 print (workbook.sheetnames)
4 workbook.create_sheet("test")
5 workbook.save("../report/Performance_Test_Report.xls")

 

pandas

数据处理是 pandas 的立身之本，Excel 作为 pandas 输入/输出数据的容器。

win32com

从命名上就可以看出，这是一个处理 windows 应用的扩展，Excel 只是该库能实现的一小部分功能。该库还支持 office 的众多操作。需要注意的是，该库不单独存在，可通过安装 pypiwin32 或者 pywin32 获取。

xlsxwriter

拥有丰富的特性，支持图片/表格/图表/筛选/格式/公式等，功能与openpyxl相似，优点是相比 openpyxl 还支持 VBA 文件导入，迷你图等功能，缺点是不能打开/修改已有文件，意味着使用 xlsxwriter 需要从零开始。

DataNitro

作为插件内嵌到 Excel 中，可完全替代 VBA，在 Excel 中使用 python 脚本。既然被称为 Excel 中的 python，协同其他 python 库亦是小事一桩。然而，这是付费插件...

xlutils

基于 xlrd/xlwt，老牌 python 包，算是该领域的先驱，功能特点中规中矩，比较大的缺点是仅支持 xls 文件。同时不支持对现有文件进行修改，只可以copy过来生成一个新表，但copy过来时原表格的格式、公式这些全部被破坏了。

1 from xlutils.copy import copy
2 workbook = xlrd.open_workbook("../report/Performance_Test_Report.xlsx")
3 workbooknew = copy(workbook)
4 ws = workbooknew.get_sheet(0)
5 ws.write(22,1,"changed")
6 workbooknew.save("../report/Performance_Test_Report1.xls")

(1). 安装模块openpyxl   

cmd ==>    pip  install openpyxl

(2). 创建打开保存Excel

(2.1) 创建Excel

import openpyxl
wb = openpyxl.Workbook()

(2.2) 打开现有Excel

import openpyxl

#加载demo.xlsx表格
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")

#获取表格每个表的名称[包括隐藏起来的表格]
li = wb.sheetnames

(2.3) 保存Excel

#xxx 为excel名称
wb.save("xxx.xlsx")  

(3). Excel内的表操作

(3.1) 通过现有表的名称获取Excel里的表test1

#test1为表的名称
#弃用wb.get_sheet_by_name("test1")
ws = wb["test1"]

(3.2) 创建Excel里新的表

#test1 表名称
#0  表的索引
wb.create_sheet("test1", 0)

(3.3) 删除Excel里已存在的表

import openpyxl
#创建Execl
wb = openpyxl.workbook.Workbook()
#创建表test1
wb.create_sheet("test1", 0)
#获取表test1
ws = wb["test1"]  

#删除存在表的三种方式
wb.remove_sheet(ws)  #已弃用
wb.remove(ws)
del wb["test1"]

(3.4) Excel表的tab颜色设置

import openpyxl
wb = openpyxl.workbook.Workbook()
#创建了4个表
ws1 = wb.create_sheet("test1", 0)
ws2 = wb.create_sheet("test2", 1)
ws3 = wb.create_sheet("test3", 2)
ws4 = wb.create_sheet("test4", 3)

#分别设置tab颜色属性
ws1.sheet_properties.tabColor = "FF0000"
ws2.sheet_properties.tabColor = "00FF00"
ws3.sheet_properties.tabColor = "0000FF"
ws4.sheet_properties.tabColor = "FF00FF"

#删除默认的一张表
del wb["Sheet"]
#保存
wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(3.5) 获取表中所有行的内容 

import openpyxl
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")
ws = wb["test_sheet1"]
#遍历test_sheet1表中的所有行
for row in ws.rows:
    #取第三列内容
    print(row[3].value)

wb.close()

(3.6) 表增加一行数据

import openpyxl
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")
#使用test1表
ws = wb["test1"]
#增加数据
ws.append(["1", "增加第一行", "第1行第3列"])
ws.append(["2", "增加第二行", "第2行第3列"])
ws.append(["3", "增加第三行", "第3行第3列"])

wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(3.7) 设置表的指定行高和指定列宽

import openpyxl
wb = openpyxl.workbook.Workbook()
#创建了1个表
ws1 = wb.create_sheet("test1", 0)
# 设置第2行的高度
ws1.row_dimensions[2].height = 100
#设置第C列的宽度
ws1.column_dimensions["C"].width = 50
#删除默认的一张表
del wb["Sheet"]
#保存
wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(3.8) 冻结表中的窗口

import openpyxl
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")

ws = wb["test1"]
#冻结窗口 
# C8中C必须大写
ws.freeze_panes = "C8"

#解冻
#ws.freeze_panes = None
wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(3.9) 表中文本对齐

openpyxl.styles.Alignment

import openpyxl
from openpyxl.styles import Alignment
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")
#test1表
ws = wb["test1"]
ws["A3"] = "增加文本对齐"
#合并单元格
ws.merge_cells("A3:C4")

#垂直水平都居中
ws["A3"].alignment = Alignment(horizontal="center",vertical="center")

wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(3.10) 表的命名样式

   命名样式可以给表中单元进行批量赋值

import openpyxl
from openpyxl.styles import NamedStyle,Font,Alignment
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")

#使用test1表
ws = wb["test1"]
#创建样式
myStyle = NamedStyle(name= "StyleMe",
                     font= Font(color="00FF00", bold=True, size = 16),
                     alignment= Alignment(horizontal="center", vertical="center"))

#把样式加入工作簿中
wb.add_named_style(myStyle)

#给指定单元格赋上面创建的样式
ws["A3"] = "A3"
ws["A3"].style = myStyle
ws["A6"] = "A6"
ws["A6"].style = myStyle

wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

 

(4). Excel内的表的单元格操作

(4.1) 基本操作

import openpyxl
#加载Excel
wb = openpyxl.load_workbook("demo.xlsx")
#通过表名称获取Excel里的表
ws = wb["test1"]
c = ws["A2"]
#获取c的类型
print(type(c))
#获取单元格行  2
print(c.row)
#获取单元格列  1
print(c.column)
#获取单元格定位
print(c.coordinate)
#获取单元格的值  None(空)
print(ws['A2'].value)
print(c.value)

#定位偏移位置
c_other = c.offset(2, 1)  #偏移2行 1列
print(c_other.coordinate) #B4
wb.close()

(4.2) 定位单元格范围

import openpyxl
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")
ws = wb["test_sheet1"]
#获取C4:D8每行内容并打印
for row in ws["C4":"D8"]:
    for clo in row:
        print(clo.value, end="  ")
    print("\n")
wb.close()

(4.4) 指定获取最小和最大的单元格

import openpyxl
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")
ws = wb["test_sheet1"]
#遍历test_sheet1表范围内的内容
#左上角4行4列 右下角10行10列的区域范围
for row in ws.iter_rows(min_row=4, min_col=4, max_row=10, max_col=10):
    #取第三列内容
    print(row[3].value)

wb.close()

(4.5) 合并和拆分单元格

import openpyxl
wb = openpyxl.workbook.Workbook()
#创建了1个表
ws1 = wb.create_sheet("test1", 0)
ws1["A1"] = "hello world!"
#合并单元格[A1:C3]
ws1.merge_cells("A1:C3")

#拆分必须和合并的单元格一致
#ws1.unmerge_cells("A1:C3")

#删除默认的一张表
del wb["Sheet"]
#保存
wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(4.6) 设置单元格字体

import openpyxl
from openpyxl.styles import Font
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")

ws = wb["test1"]
ws["A3"] = "字体设置"
#设置字体
ws["A3"].font = Font(bold=True,size = 14, color="FF0000")
wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(4.7) 填充单元格

单色填充openpyxl.styles.PatternFill

import openpyxl
from openpyxl.styles import PatternFill
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")

ws = wb["test1"]
ws["A3"] = "单元格填充"
#设置填充
ws["A3"].fill = PatternFill(fill_type="solid", fgColor="00FF00")
wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

渐变填充openpyxl.styles.PatternFill

import openpyxl
from openpyxl.styles import GradientFill
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")

ws = wb["test1"]
ws["A5"] = "单元格渐变填充"
#设置填充
ws["A5"].fill = GradientFill(type="linear", stop=("FF0000","0000FF"))
wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()

(4.8) 设置单元格边框

import openpyxl
from openpyxl.styles import Border,Side
#加载demo excel表
wb = openpyxl.load_workbook(r"C:\Users\Administrator\Desktop\demo.xlsx")

ws = wb["test1"]
ws["A6"] = "边框设置1"
ws["A8"] = "边框设置2"
#设置边框
thin_side = Side(color="000000", border_style="thin")
double_side = Side(color="000000", border_style="double")
ws["A6"].border = Border(diagonal=thin_side, diagonalUp=True, diagonalDown=True)
ws["A8"].border = Border(left=double_side, top=double_side, right=double_side, bottom=double_side)

wb.save(r"C:\Users\Administrator\Desktop\demo.xlsx")
wb.close()