Python学习 - openpyxl库 / xlwt库

一 openpyxl

1. 第三方库 openpyxl

openpyxl 官方文档

openpyxl 是一个处理Excel表格的第三方库。openpyxl 库可以处理Excel2010以后的电子表格格式，包括：xlsx/xlsm/xltx/xltm。

2.Pycharm下载openpyxl

1. 菜单栏中的“File”，选择“Settings”。
2. 选择“Project:项目名”下的“Project Interpreter”，右侧的窗口中，点击“+”按钮，搜索并选择“openpyxl”，最后点击安装 “Install Package”

3.openpyxl的基本使用

教程

3.1 新建工作表

from openpyxl import Workbook

1.新建
wb = Workbook()
2.获取活跃的工作薄(sheet)
ws = wb.active
3.保存工作表并命名
wb.save('test.xlsx')
1
2
3
4
5
6
7
8

3.2 工作薄

1. sheet的属性
## 新建工作薄
ws1 = wb.create_sheet("Mysheet") 

## 修改工作薄的名字
ws.title = "New Title"

## 使用 Workbook.sheetname 属性查看工作簿中所有工作表的名称
print(wb.sheetnames) 

## 遍历工作表
for sheet in wb:
    print(sheet.title)
    
## 在**一个工作表**中创建一个工作簿的复制
source = wb.active
target = wb.copy_worksheet(source)

## 读取指定工作表
ws1 = wb.create_sheet("Mysheet1")
ws2 = wb.create_sheet("Mysheet2")
ws3 = wb.create_sheet("Mysheet3")

ws4=wb["Mysheet3"]


2. sheet的样式
## 默认情况下，包含该标题的选项卡的背景颜色为白色。你也可以使用 RRGGBB 颜色来改变 Worksheet.sheet_properties.tabColor 属性
ws.sheet_properties.tabColor = "1072BA" # 渐变蓝

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

3.3 单元格

3.3.1 单个访问单元格

1.访问单元格
c = ws['A4'] = 'test'
print(c) # test

2.单元格赋值
ws['A4'] = 4

3. 行列符号访问单元格
## 行 列 值
d = ws.cell(row=4, column=2, value=10) # B4 赋值 10(value)
print(d) # <Cell 'New Title'.B4>

4. cell(行,列).value
ws.cell(1,2).value="hello,openpyxl"

5. 先获取后赋值
b2 = ws['B2']
b2.value = 'new！'

6. 在最下面新增一行追加一个或多个值
ws.append([1, 2, 3])

举例
##  1.九九乘法表
wb = Workbook()
ws = wb.active
ws.title = "New Title"
for i in range(1, 10):
    for j in range(1, i+1):
        a = str(i)
        b = str(j)
        c = str(i * j)
        d = b + "*" + a + "=" + c
        ws.cell(row=i, column=j, value=d)
wb.save('test.xlsx')

## 2. 使用数字格式
import datetime
from openpyxl import Workbook

ws['A1'] = datetime.datetime(2023, 2, 28, 23, 59, 59) # xlsx A1显示 2023/2/28  23:59:59
time = ws['A1'].number_format
print(time) # yyyy-mm-dd h:mm:ss

## 使用公式
ws["A1"] = "=SUM(1, 1)"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

3.3.2 大量访问单元格

3.3.2.1 范围取值

1. 可以使用切片来访问一系列单元格
cell_range = ws['A1':'C2']
print(cell_range) # ((<Cell 'New Title'.A1>, <Cell 'New Title'.B1>, <Cell 'New Title'.C1>), (<Cell 'New Title'.A2>, <Cell 'New Title'.B2>, <Cell 'New Title'.C2>))

2. 一系列的行和列也可以通过类似的方法获取:
# 用上面的乘法表作为例子

# 选取第2行（下标从1开始）
row_cells = ws[2]  
print(row_cells) ## A2, B2, C2, D2, E2, F2, G2, H2, I2

#  选取B列
row_cells = ws[2]
print(row_cells)# B1, B2, B3, B4, B5, B6, B7, B8, B9

# 选取2 3 4 5 6 行
row_range_cells = ws[2:6]
col_range = ws[B:F']

# 选取B C D 散列
col_range_cells = ws["B:D"] 

# 选取C列
colC = ws['C'] 
print(row10) # C1 ~ C9

# 单读第10行
row10 = ws[10]
print(row10) # A10 ~ I10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

3.3.2.2 迭代取值

1. iter_rows 指定最大最小的行列，下标从1开始
cells = ws.iter_rows(min_row=1, max_row=2, min_col=2, max_col=6)
for cell in cells:
    print(cell) # B1~F1 && B2~F2

2. iter_cols 同理
cells = ws.iter_cols(min_row=1, max_row=3, min_col=2, max_col=5)
for cell in cells:
    print(cell)
3. 其他迭代
for cell in ws.rows:
    print(cell) # A1 ~ I1 …… A9 ~ I9

for cell in ws.columns:
    print(cell)

for row in ws.values:
    for value in row:
        print(value)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.3.3 合并拆分单元格

# 合并单元格
ws.merge_cells('A1:D4')
# 拆分单元格
ws.unmerge_cells('A1:D4')

# 其他写法
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
1
2
3
4
5
6
7
8

3.3.4 插入/删除/移动/隐藏

3.3.4.1 插入和删除

# 插入第一行
ws.insert_rows(1)
# 删除 F:H 列
ws.delete_cols(6, 3)
1
2
3
4

3.3.4.2 移动

在一个工作表内移动范围单元格,已存在的单元格将会被覆盖

# D4:F9 单元格向上移动一行向右移动两列
ws.move_range("D4:F9", rows=-1, cols=2)

#如果单元格包含公式，你可以让 openpyxl 帮你进行translate，但也并非总是你想要的结果，因此默认是禁用的。 同时，只有单元格本身的公式将会被translate。其他单元格对该单元格的引用或defined name将不会被更新
# ws.move_range("G4:H10", rows=1, cols=1, translate=True)
1
2
3
4
5

3.3.4.3 隐藏

# 隐藏A ~ D列
ws.column_dimensions.group('A','D', hidden=True)

# 隐藏1 ～ 10行
ws.row_dimensions.group(1,10, hidden=True)
1
2
3
4
5

3.4 保存到文件

#这个操作将会无警告直接覆盖已有文件
# filename为要保存成为的文件名，如果文件名带有路径，则文件会保存在对应的路径下
语法：wb.save(filename) 
wb = Workbook()
wb.save('balances.xlsx')
1
2
3
4
5

4.样式

4.1 理解和认识

样式可以应用于以下方面:

默认值

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
font = Font(name='Calibri',
                size=11,
                bold=False,
                italic=False,
                vertAlign=None,
                underline='none',
                strike=False,
                color='FF000000')
fill = PatternFill(fill_type=None,
                start_color='FFFFFFFF',
                end_color='FF000000')
border = Border(left=Side(border_style=None,
                          color='FF000000'),
                right=Side(border_style=None,
                           color='FF000000'),
                top=Side(border_style=None,
                         color='FF000000'),
                bottom=Side(border_style=None,
                            color='FF000000'),
                diagonal=Side(border_style=None,
                              color='FF000000'),
                diagonal_direction=0,
                outline=Side(border_style=None,
                             color='FF000000'),
                vertical=Side(border_style=None,
                              color='FF000000'),
                horizontal=Side(border_style=None,
                               color='FF000000')
               )
alignment=Alignment(horizontal='general',
                    vertical='bottom',
                    text_rotation=0,
                    wrap_text=False,
                    shrink_to_fit=False,
                    indent=0)
number_format = 'General'
protection = Protection(locked=True,
                        hidden=False)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

4.2 单元格

4.2.1 单个单元格

from openpyxl.styles import Font

ws['A1'] = "( •̀ ω •́ )✧"
ws['D4'] = "<(￣︶￣)↗[GO!]"
# 添加样式
a1 = ws['A1']
d4 = ws['D4']
ft = Font(size = 14 , color = "FF0000")
a1.font = ft
d4.font = ft
1
2
3
4
5
6
7
8
9
10

4.2.2 合并单元格

合并单元格和其他单元格对象的行为相似，通过左上单元格来定义值和样式。可以改变左上单元格的边框来改变整个合并单元格的边框。 这种格式是出于编辑目的才被生成

from  openpyxl import  Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment

wb = Workbook()
ws = wb.active

# 添加样式
ws.merge_cells('B2:F4')
top_left_cell = ws['B2']
top_left_cell.value = "My Cell"

thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")

top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)
top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
top_left_cell.fill = fill = GradientFill(stop=("000000", "FFFFFF"))
top_left_cell.font  = Font(b=True, color="FF0000")
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
wb.save('test.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

二 xlwt库

1. xlwt库

1. 介绍：

xlwt 库是一个 python 用于操作 excel的第三方库。它的主要功能是用来写入 excel 。通常会与 xlrd 、 xlutils 组合进行使用。
 
注意:这里的操作 excel ，实际上与 excel 无关，不需要下载excel，xlwt库直接操作的是 excel 打开的 xls 文件！注意！xlrd库只能创建和修改 excel，不能打开 excel ！

2. 安装
   在 cmd 中输入​ pip install xlrd ​即可安装 xlrd 库，安装成功后可以使用​ pip list​ 来检查是否正确安装以及查看当前的 xlrd 版本

2. 基本使用

2.1 创建表格并写入

import xlwt

# 创建一个workbook并设置编码
workbook = xlwt.Workbook(encoding='utf-8')
# 添加sheet
worksheet = workbook.add_sheet('Mysheet')
# 写入excel, 参数对应 行, 列, 值
worksheet.write(1, 0, label='今天的天气很晴朗')
# 保存
workbook.save('天气.xls')

--------------
# 获取第一列
first_col = sheet.col(0) 

# 获取第二列
sec_col = sheet.col(1)  

# 获取第一行
first_row = worksheet.row(0)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.2 设置单元格样式

import xlwt

workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('Mysheet')

# 初始化样式
style = xlwt.XFStyle()

# 为样式创建字体
font = xlwt.Font()

# 黑体
font.name = 'Times New Roman'
font.bold = True

# 下划线
font.underline = True

# 斜体字
font.italic = True

# 设定样式
style.font = font

# 带样式的写入
worksheet.write(0, 0, '今天的天气真不错！', style)

# 保存文件
workbook.save('天气.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

2.3 设置单元格宽度/行高

import xlwt

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Mysheet')
worksheet.write(0, 0, "Hello, world!")
# 设置单元格宽度/高度
worksheet.col(0).width = 5000

# 设置行高
tall_style = xlwt.easyxf('font:height 720')

# 获取sheet页的第一行
first_row = worksheet.row(0)

# 给第一行设置行高
first_row.set_style(tall_style)
workbook.save('天气.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.4 设置单元格背景色

import xlwt

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Mysheet')

# 创建模式对象Create the Pattern
pattern = xlwt.Pattern()

# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern = xlwt.Pattern.SOLID_PATTERN

# 设置模式颜色 May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
pattern.pattern_fore_colour = 5

# 创建样式对象Create the Pattern
style = xlwt.XFStyle()

# 将模式加入到样式对象Add Pattern to Style
style.pattern = pattern

# 向单元格写入内容时使用样式对象style
worksheet.write(0, 0, '今天天气你觉得怎么样？', style)

workbook.save('天气.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2.5 超链接和公式

import xlwt

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Mysheet')

worksheet.write(5, 5,
                xlwt.Formula('HYPERLINK("https://www.baidu.com/";"百度一下")'))

# Outputs 5  #第一行第一列5
worksheet.write(0, 0, 5)  
# Outputs 2  #第一行第二列2
worksheet.write(0, 1, 2)  

# (A1[5] * A2[2])  第二行第一列，输出第一行数字乘积
worksheet.write(1, 0, xlwt.Formula('A1*B1'))

# (A1[5] + A2[2])  第二行第二列，输出第一行数字之和
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)'))

workbook.save('天气.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.6 日期

import xlwt
import datetime

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Mysheet')

style = xlwt.XFStyle()

# 其他格式包括: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
style.num_format_str = 'M/D/YY'

worksheet.write(0, 0, datetime.datetime.now(), style)

workbook.save('天气.xls')
1
2
3
4
5
6
7
8
9
10
11
12
13
14