1. Johngo学长首页
  2. 技术点详细复盘专题
  3. 人工智能

python 对 excel的基本操作（1）

• 人工智能 • 阅读 72 
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")


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")


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")

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中。一般情况下，表头是保留的，而索引列根据实际需要来决定是否保留。如果索引列中含有关键信息，则需要保留；如果索引列只是单纯的一列数，则没必要保留。

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")


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"])


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")

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")

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")

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")

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")

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")

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")

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.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.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")

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")

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")

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")

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")

for 循环遍历
for c in ['A','B','C]:
 ws.column_dimensions[c].width = 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, 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")

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")

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)

24、

25、

26、

27、

28、

29、

30、

31、

32、

33、

34、

35、

36、

37、

38、

39、

40、

41、

42、

43、

44、

45、

46、

47、

48、

50、

51、

52、

53、

54、

55、

56、

57、

58、

59、

60、

61、

62、

63、

64、

64、

65、

66、

67、

68、

69、

70、

71、

72、

73、

74、

75、

76、

77、

78、

79、

80、

81、

82、

83、

84、

85、

86、

87、

88、

89、

90、

91、

92、

93、

94、

95、

96、

97、

98、

99、

100

Original: https://blog.csdn.net/weixin_54217632/article/details/122317460
Author: Jack_2085
Title: python 对 excel的基本操作（1）

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/679108/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球