1、load_workbook()和read_excel()都可以对文件进行读取，那两者有什么区别呢？
import pandas as pd
from openpyxl import load_workbook
from openpyxl import Workbook
# 通过openpyxl 拿取数据，load_workbook()
# 会把整个工作簿中的所有内容都导入进来，具体实现代码如下。
# ex2 = load_workbook("C:/Users/Administrator/Desktop/456999/2022/3z/dataset.xlsx")
# print(ex2)
# 通过pandas 拿取数据，read_excel读取的excel可以显示出来。
# ex1 = pd.read_excel("C:/Users/Administrator/Desktop/456999/2022/3z/dataset.xlsx",
# sheet_name="杭州")
# print(ex1)
wb = Workbook()
ws = wb.active
ws["A1"] = "张三"
ws["A2"] = "李四"
ws["A3"] = "王五"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/20220105.xlsx")2、创建工作簿，并插入数据
from openpyxl import Workbook
# 创建工作簿，并插入数据
wb = Workbook()
ws = wb.active
ws["A1"] = "张三"
ws["A2"] = "李四"
ws["A3"] = "王五"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/20220105.xlsx")
# 方法2 对单元格赋值
wbt = Workbook()
wst = wbt.active
wst.cell(row=1, column=1).value = 91
wst.cell(row=2, column=1).value = 92
wst.cell(row=3, column=1).value = 93
wbt.save("C:/Users/Administrator/Desktop/456999/2022/3z/20220105ceil_num.xlsx")3、插入一行数据或循环插入多行数据
from openpyxl import Workbook
# 给某一行单元格赋值
wb = Workbook()
ws = wb.active
ws.append(["张三", "李四", "王五", 100, 99])
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201051106.xlsx")
# 循环插入多行数据
wbt = Workbook()
wst = wbt.active
data = [["jack", 3, 2, 1], ["lucy", 4, 5, 1], ["mark", 8, 3, 9]]
for i in data:
wst.append(i)
wbt.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201051152.xlsx")4、我们先利用Pandas导入一个pandas文件，然后新建一个openpyxl格式的空工作簿，再利用append()方法将Pandas中的数据插入空工作簿中，最后保存这个工作簿，代码如下。
from openpyxl import Workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
# 先读取数据
df = pd.read_excel("C:/Users/Administrator/Desktop/456999/2022/3z/202201051152.xlsx")
# 创建工作簿
wb = Workbook()
# 激活工作簿
wb_act = wb.active
# 循环解析excel，并插入工作簿
for r in dataframe_to_rows(df, index=False, header=True):
wb_act.append(r)
# 保存工作簿
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201051436.xlsx")总结：在上述代码中有一个关键的方法dataframe_to_rows()，这个方法是将pandas格式的数据转化为一行一行的数据，其后面括号中的index表示在转化过程中是否需要将DataFrame表的索引列也插入Excel中，True表示需要，False表示不需要；header表示是否需要将DataFrame表的表头（列名）也插入Excel中。一般情况下，表头是保留的，而索引列根据实际需要来决定是否保留。如果索引列中含有关键信息，则需要保留；如果索引列只是单纯的一列数，则没必要保留。
5、创建sheet ，拷贝一个sheet，修改sheet（包含修改颜色），删除sheet等
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
# 创建sheet ，拷贝一个sheet，修改sheet（包含修改颜色），删除sheet等
wb = Workbook()
ws = wb.active
# 创建一个空sheet
wb.create_sheet()
# 创建二个命名的sheet
wb.create_sheet("上海")
# 也可以指定sheet的位置和命名
bj_sheet = wb.create_sheet("北京", 0)
# 默认的sheet，都是放在最后面
wb.create_sheet("深圳")
# 拷贝一个sheet
wb.copy_worksheet(bj_sheet)
# 只会修改第一个sheet的名字
ws.title = "9999"
# 更改sheet的颜色(默认修改第一个)，改成红色。要把excel先关闭。
ws.sheet_properties.tabColor = "FFEE0000"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201051527.xlsx")
# 查询有哪些sheet(会放在一个list中)
lw = load_workbook("C:/Users/Administrator/Desktop/456999/2022/3z/202201051527.xlsx")
print(lw.sheetnames)
# 删除sheet的动作函数（删除北京）
wb.remove(bj_sheet)
# 可以生成一个新的excel，也可以在原来的基础上删除（北京这个sheet）
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201051528.xlsx")6、用Python选择单元格，给单元格赋值。
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
#
wb = Workbook()
ws = wb.active
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 给指定的sheet赋值
bj_sheet["A1"] = "张三"
bj_sheet["A2"] = "李四"
bj_sheet["A3"] = "王五"
# 给默认的sheet插入数据
ws["A1"] = "张三ws"
ws["A2"] = "李四ws"
ws["A3"] = "王五ws"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201051529.xlsx")
# 具体指明单元格
print(bj_sheet["A1"])
# 指定取出某单元格中的值
wc_ll = bj_sheet.cell(row=1, column=1)
print(wc_ll.value)
# 取出默认的sheet中的值
print(ws.cell(row=1, column=1).value)
# 通过切片获取单元格,或其他方法获取单元格
print(ws[1: 3])
print(ws["C:E"])
print(ws["A1": "C6"])7、设置单元格的字体(对单元格的font属性= Font()函数，并在Font()函数中指明具体的设置参数)
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.styles import Font
from openpyxl.utils.dataframe import dataframe_to_rows
#
wb = Workbook()
ws = wb.active
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 给指定的sheet赋值
bj_sheet["A1"] = "张三"
bj_sheet["A2"] = "李四"
bj_sheet["A3"] = "王五"
# 设置单元格的字体
bj_sheet["A1"].font = Font(name="新宋体", size=20, bold=True, italic=True,
vertAlign="baseline", underline="singleAccounting",
strike=True, color="FFEE0000")
# 给默认的sheet插入数据
ws["A1"] = "张三ws"
ws["A2"] = "李四ws"
ws["A3"] = "王五ws"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201051529.xlsx")8、创建sheet（自己命名和指定位置），给指定的sheet赋值，设置单元格的字体，单元格填充(背景颜色，格式)，边框线设置，设置对角线，给默认的sheet插入数据
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.styles import Font, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
#
wb = Workbook()
ws = wb.active
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 给指定的sheet赋值
bj_sheet["A1"] = "张三"
bj_sheet["A2"] = "李四"
bj_sheet["A3"] = "王五"
bj_sheet["B1"] = "BBB张三"
bj_sheet["B2"] = "BBB李四"
bj_sheet["B3"] = "BBB王五"
# 设置单元格的字体
bj_sheet["A1"].font = Font(name="新宋体", size=20, bold=True, italic=True,
vertAlign="baseline", underline="singleAccounting",
strike=True, color="FFEE0000")
# 单元格填充(背景颜色，格式)
bj_sheet["A2"].fill = PatternFill(fill_type="gray125", end_color="FFFFFF00")
# 边框线设置
bj_sheet["A3"].border = Border(left=Side(border_style="hair", color="FFEE0000"),
right=Side(border_style="thick", color="FFEE0000"),
top=Side(border_style="thick", color="FF00A8FF"),
bottom=Side(border_style="thick", color="FF11FF00"))
# 设置对角线
bj_sheet["B1"].border = Border(diagonal=Side(border_style="thick", color="FFEE0000"),
diagonalDown=True)
bj_sheet["B2"].border = Border(diagonal=Side(border_style="thick", color="FFEE0000"),
diagonalUp=True)
bj_sheet["B3"].border = Border(diagonal=Side(border_style="thick", color="FFEE0000"),
diagonalDown=True, diagonalUp=True)
# 给默认的sheet插入数据
ws["A1"] = "张三ws"
ws["A2"] = "李四ws"
ws["A3"] = "王五ws"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201060920.xlsx")9、
9.1、遍历每一行，遍历每一行中的每一列
9.2、指定区域中的每一行，遍历每一行中的每一列
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.styles import Font, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
#
wb = Workbook()
ws = wb.active
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 给指定的sheet赋值
bj_sheet["A1"] = "张三"
bj_sheet["A2"] = "李四"
bj_sheet["A3"] = "王五"
bj_sheet["B1"] = "BBB张三"
bj_sheet["B2"] = "BBB李四"
bj_sheet["B3"] = "BBB王五"
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
# 遍历每一行
# for row in bj_sheet[1: 3]:
# # 遍历每一行中的每一列
# for c in row:
# c.font = Font(name="新宋体", size=20, bold=True, italic=True,
# vertAlign="baseline", underline="singleAccounting",
# strike=True, color="FFEE0000")
# c.fill = PatternFill(fill_type="gray125", end_color="FFFFFF00")
# c.border = Border(left=Side(border_style="hair", color="FFEE0000"),
# right=Side(border_style="thick", color="FFEE0000"),
# top=Side(border_style="thick", color="FF00A8FF"),
# bottom=Side(border_style="thick", color="FF11FF00"))
# 方法2、指定区域中的每一行
for row in bj_sheet["A1": "C3"]:
# 遍历每一行中的每一列
for c in row:
c.font = Font(name="新宋体", size=20, bold=True, italic=True,
vertAlign="baseline", underline="singleAccounting",
strike=True, color="FFEE0000")
c.fill = PatternFill(fill_type="gray125", end_color="FFFFFF00")
c.border = Border(left=Side(border_style="hair", color="FFEE0000"),
right=Side(border_style="thick", color="FFEE0000"),
top=Side(border_style="thick", color="FF00A8FF"),
bottom=Side(border_style="thick", color="FF11FF00"))
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201061028.xlsx")10、设置对齐方式
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.styles import Alignment
from openpyxl.utils.dataframe import dataframe_to_rows
# 设置对齐方式（下面内容总结）
wb = Workbook()
ws = wb.active
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 给指定的sheet赋值
bj_sheet["A1"] = "分散对齐"
bj_sheet["A2"] = "李四"
bj_sheet["A3"] = "王五"
bj_sheet["A4"] = "赵六"
bj_sheet["B1"] = "BBB张三"
bj_sheet["B2"] = "自动换行自动换行自动换行自动换行自动换行自动换行自动换行"
bj_sheet["B3"] = "自动调整大小自动调整大小自动调整大小"
bj_sheet["B4"] = "自动缩进空格"
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
# 分散对齐
bj_sheet["A1"].alignment = Alignment(horizontal="distributed")
# 底部对齐
bj_sheet["A2"].alignment = Alignment(vertical="bottom")
# 居中对齐
bj_sheet["A3"].alignment = Alignment(vertical="center")
# 上部对齐
bj_sheet["A4"].alignment = Alignment(vertical="top")
# 旋转30度
bj_sheet["B1"].alignment = Alignment(text_rotation=30)
# 自动换行
bj_sheet["B2"].alignment = Alignment(wrap_text=True)
# 自动调整大小
bj_sheet["B3"].alignment = Alignment(shrink_to_fit=True)
# 自动缩进2个字符
bj_sheet["B4"].alignment = Alignment(indent=2)
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201061530.xlsx")11、合并单元格，解除单元格，设置合并单元格样式，批量设置单元格对齐方式
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows
# 设置对齐方式（下面内容总结）
wb = Workbook()
ws = wb.active
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 给指定的sheet赋值
bj_sheet["A1"] = "张三"
bj_sheet["A2"] = "李四"
bj_sheet["A3"] = "王五"
bj_sheet["A4"] = "赵六"
bj_sheet["B1"] = "BBB张三"
bj_sheet["B2"] = "BBB李四"
bj_sheet["B3"] = "BBB王五"
bj_sheet["B4"] = "BBB赵六"
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
# 合并单元格
bj_sheet.merge_cells("A2:C2")
# 解除单元格
bj_sheet.unmerge_cells("A2:C2")
# 设置合并单元格样式
bj_sheet.merge_cells("A6:F9")
top_left_cell = bj_sheet["A6"]
top_left_cell.value = "My Cell"
top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
top_left_cell.font = Font(bold=True, color="FF0000")
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
# 批量设置单元格对齐方式
for row in bj_sheet["A1": "C3"]:
# 遍历每一行中的每一列
for c in row:
c.fill = PatternFill("solid", fgColor="DDDDDD")
c.font = Font(bold=True, color="FF0000")
c.alignment = Alignment(horizontal="center", vertical="center")
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201061649.xlsx")12、设置数字格式（时间，百分比，小数点）等等。
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows
# 设置对齐方式（下面内容总结）
wb = Workbook()
ws = wb.active
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 给指定的sheet赋值
bj_sheet["A1"] = "123"
bj_sheet["A2"] = "223"
bj_sheet["A3"] = "323"
bj_sheet["A4"] = "423"
bj_sheet["A5"] = "523"
bj_sheet["A6"] = "623"
bj_sheet["A7"] = "723"
bj_sheet["A8"] = "823"
bj_sheet["A9"] = "923"
bj_sheet["B1"] = "BBB张三"
bj_sheet["B2"] = "BBB李四"
bj_sheet["B3"] = "BBB王五"
bj_sheet["B4"] = "BBB赵六"
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
# 设置数字格式
bj_sheet["A1"].number_format = "General"
bj_sheet["A2"].number_format = "0.00"
bj_sheet["A3"].number_format = "0%"
bj_sheet["A4"].number_format = "0.00%"
bj_sheet["A5"].number_format = "#，##0"
bj_sheet["A6"].number_format = "0.00E+00"
bj_sheet["A7"] = datetime.datetime(2022, 1, 6, 18, 1, 50)
bj_sheet["A8"] = datetime.datetime(2022, 1, 6, 18, 2, 52)
bj_sheet["A8"].number_format = "mm-dd-yy"
bj_sheet["A9"] = datetime.datetime(2022, 1, 6, 18, 3, 22)
bj_sheet["A9"].number_format = "h:mm:ss"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201061804.xlsx")13、批量给单元格赋值，筛选符合条件的单元格，并标注颜色。
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows
# 设置对齐方式（下面内容总结）
wb = Workbook()
ws = wb.active
data = [[13, 45],
[24, 56],
[31, 53],
[34, 51],
[65, 89],
[81, 50],
[41, 98],
[91, 65],
[76, 80]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 筛选符合条件的单元格，并标注颜色。
rule1 = CellIsRule(operator="greaterThan", formula=[50],
fill=PatternFill(end_color="FFFF2100"))
# operator="between", formula=[50, 80] 是包含50和80
rule2 = CellIsRule(operator="between", formula=[50, 80],
fill=PatternFill(end_color="FFFF2100"))
bj_sheet.conditional_formatting.add("A1:A10", rule1)
bj_sheet.conditional_formatting.add("B1:B10", rule2)
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201062027.xlsx")14、筛选符合条件的单元格，添加数据条，并标注颜色。
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.formatting.rule import CellIsRule, DataBarRule
from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows
# 数据条
wb = Workbook()
ws = wb.active
data = [[13, 45],
[24, 56],
[31, 53],
[34, 51],
[65, 89],
[81, 50],
[41, 98],
[91, 65],
[76, 80]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 筛选符合条件的单元格，添加数据条，并标注颜色。
rule1 = DataBarRule(start_type="min", end_type="max", color="FF638EC6",
showValue=True)
rule2 = DataBarRule(start_type="min", end_type="max", color="FF638EC6",
showValue=True)
bj_sheet.conditional_formatting.add("A1:A10", rule1)
bj_sheet.conditional_formatting.add("B1:B10", rule2)
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201071044.xlsx")
15、
15.1、筛选符合条件的单元格，添加数据条，并标注颜色。
15.2、我们将start_type与end_type最小值和最大值类型都设为'num'，
15.2、那start_value与end_value必须填写数字
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.formatting.rule import CellIsRule, DataBarRule
from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows
# 数据条
wb = Workbook()
ws = wb.active
data = [[13, 45],
[24, 56],
[31, 53],
[34, 51],
[65, 89],
[81, 50],
[41, 98],
[91, 65],
[76, 80]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 筛选符合条件的单元格，添加数据条，并标注颜色。
# 我们将start_type与end_type最小值和最大值类型都设为'num'，
# 那start_value与end_value必须填写数字
rule1 = DataBarRule(start_type="num", start_value=1, end_type="num", end_value=100,
color="FF638EC6", showValue=True)
bj_sheet.conditional_formatting.add("A1:B10", rule1)
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201071101.xlsx")16、
16.1、筛选符合条件的单元格，添加数据条，并标注颜色(双色)
16.2、筛选符合条件的单元格，添加数据条，并标注颜色(三色)
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.formatting.rule import CellIsRule, DataBarRule, ColorScaleRule
from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows
# 数据条
wb = Workbook()
ws = wb.active
data = [[13, 45],
[24, 56],
[31, 53],
[34, 51],
[65, 89],
[81, 50],
[41, 98],
[91, 65],
[76, 80]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 筛选符合条件的单元格，添加数据条，并标注颜色(双色)。
rule1 = ColorScaleRule(start_type="min", start_color="FFFFF0F5",
end_type="max", end_color="FFFF7F00")
# 筛选符合条件的单元格，添加数据条，并标注颜色(三色)。
rule2 = ColorScaleRule(start_type="percentile", start_value=10, start_color="FFFFE4E1",
mid_type="percentile", mid_value=50, mid_color="FFFFC1C1",
end_type="percentile", end_value=90, end_color="FFFF3030")
bj_sheet.conditional_formatting.add("A1:A10", rule1)
bj_sheet.conditional_formatting.add("B1:B10", rule2)
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201071351.xlsx")17、选择单元格的图标集，三色旗，三向箭头（彩色）
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
from openpyxl.formatting.rule import CellIsRule, DataBarRule, ColorScaleRule, IconSetRule
from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows
# 图标集
wb = Workbook()
ws = wb.active
data = [[13, 45],
[24, 56],
[31, 53],
[34, 51],
[65, 89],
[81, 50],
[41, 98],
[91, 65],
[76, 80]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 选择单元格的图标集，三色旗。
rule1 = IconSetRule("3Flags", "percent", [0, 30, 50], showValue=True, reverse=False)
# 选择单元格的图标集，三向箭头（彩色）。
rule2 = IconSetRule("3Arrows", "percent", [0, 30, 50], showValue=True, reverse=False)
bj_sheet.conditional_formatting.add("A1:A10", rule1)
bj_sheet.conditional_formatting.add("B1:B10", rule2)
bj_sheet["C1"] = "CCC张三"
bj_sheet["C2"] = "CCC李四"
bj_sheet["C3"] = "CCC王五"
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201071611.xlsx")18、插入行和列
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
# 插入行和列
wb = Workbook()
ws = wb.active
data = [[13, 45, 76, 80],
[24, 56, 91, 65],
[31, 53, 41, 98],
[34, 51, 81, 50]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 拷贝一份，插入的数据
ws1 = wb.copy_worksheet(bj_sheet)
# 在第3行插入2行
ws1.insert_rows(3, 2)
# 在第2列插入1列
ws1.insert_cols(2, 1)
print("***********************************")
# 上海sheet插入数据
for r in data:
sh_sheet.append(r)
# 上海sheet，在第3行插入2行
sh_sheet.insert_rows(3, 2)
# 上海sheet，在第2列插入1列
sh_sheet.insert_cols(2, 1)
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201071718.xlsx")19、删除行和列
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
# 删除行和列
wb = Workbook()
ws = wb.active
data = [[13, 45, 76, 80],
[24, 56, 91, 65],
[31, 53, 41, 98],
[34, 51, 81, 50]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 拷贝一份，插入的数据
ws1 = wb.copy_worksheet(bj_sheet)
# 删除从第3行开始的2行
ws1.delete_rows(3, 2)
# 删除从第2列开始的1列
ws1.delete_cols(2, 1)
print("***********************************")
# 上海sheet插入数据
for r in data:
sh_sheet.append(r)
# 上海sheet，删除从第3行开始的2行
sh_sheet.delete_rows(3, 2)
# 上海sheet，删除从第2列开始的1列
sh_sheet.delete_cols(2, 1)
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201071817.xlsx")
20、调整设置 行和列
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
# 调整设置行和列
wb = Workbook()
ws = wb.active
data = [[13, 45, 76, 80],
[24, 56, 91, 65],
[31, 53, 41, 98],
[34, 51, 81, 50]
]
# 创建sheet（自己命名和指定位置）
bj_sheet = wb.create_sheet("北京", 0)
sh_sheet = wb.create_sheet("上海", 1)
sz_sheet = wb.create_sheet("深圳", 2)
# 批量给单元格赋值
for r in data:
bj_sheet.append(r)
# 调整列的宽度
bj_sheet.column_dimensions["A"].width = 20
# 调整行的高度
bj_sheet.row_dimensions[1].height = 40
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201071837.xlsx")20.2、如果设置全部的行和列，只能通过遍历
for 循环遍历
for c in ['A','B','C]:
ws.column_dimensions[c].width = 2021、隐藏行和列
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
# 隐藏行和列
wb = Workbook()
ws = wb.active
data = [[13, 45, 76, 80, 23, 29],
[24, 56, 91, 65, 31, 36],
[31, 53, 41, 98, 45, 49],
[34, 51, 81, 50, 54, 56],
[124, 156, 191, 165, 131, 136],
[731, 653, 541, 498, 345, 249]
]
# 批量给单元格赋值
for r in data:
ws.append(r)
# 隐藏的sheet效果(拷贝的一份数据)
ws1 = wb.copy_worksheet(ws)
# 隐藏列b 到 d
ws1.column_dimensions.group("b", "d", hidden=True)
# 隐藏行2 到 3
ws1.row_dimensions.group(2, 3, hidden=True)
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201072057.xlsx")22、批量设置行高和列宽
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
# 批量设置行高和列宽
wb = Workbook()
ws = wb.active
data = [[13, 45, 76, 80, 23, 29],
[24, 56, 91, 65, 31, 36],
[31, 53, 41, 98, 45, 49],
[34, 51, 81, 50, 54, 56],
[124, 156, 191, 165, 131, 136],
[731, 653, 541, 498, 345, 249]
]
# 批量给单元格赋值
for r in data:
ws.append(r)
# 批量调整列宽
for col in ["A", "B", "C"]:
ws.column_dimensions[col].width = 20
# 批量调整行高
for row in range(5):
ws.row_dimensions[row + 1].height = 40
wb.save("C:/Users/Administrator/Desktop/456999/2022/3z/202201072157.xlsx")
23、excel的排序和筛选，用pandas科学数据库
import pandas as pd
# excel的排序和筛选，只要用pandas科学数据库
# 读取excel
re_ex = pd.read_excel("C:/Users/Administrator/Desktop/456999/2022/3z/test666.xlsx")
pd_re_ex = pd.DataFrame(re_ex)
# print(re_ex)
# 排序功能
print("**************************,对单列倒序")
str_sort = pd_re_ex.sort_values(by="col1", ascending=False)
print(str_sort)
print("**************************,对多列倒序，升序")
str_sort_more = pd_re_ex.sort_values(by=["col1", "col2"], ascending=[False, True])
print(str_sort_more)
# 保存在excel中
str_sort_more.to_excel("C:/Users/Administrator/Desktop/456999/2022/3z/202201081545.xlsx")
# 筛选功能
pd_re_ex_ed = pd.DataFrame(re_ex)
# 筛选大于2的行
value_sx = pd_re_ex_ed[pd_re_ex_ed["col1"] > 2]
print(value_sx)
# 筛选等于b的行
value_sx_db = pd_re_ex_ed[pd_re_ex_ed["col2"] == "b"]
print(value_sx_db)本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
