openpyxl 操作Excel笔记

一、常用库引入

from openpyxl import Workbook,load_workbook
from openpyxl.styles import *

import warnings
warnings.filterwarnings('ignore')

二、基本操作

1.工作簿操作

1.创建新的工作簿
wb1 = Workbook()

2.加载已存在的工作簿
wb = load_workbook('./000.xlsx')
# openpyxl只能处理 .xlsx 合适的表格

3.保存工作簿
wb.save()
wb.save("name.xlsx")  # 另存为

4.关闭工作簿
wb.close()

2.工作表操作

1.建新的工作表
ws1 = wb.create_sheet('111')

2.当前工作表
ws2 = wb.active

3.查看已存在的全部工作表
wb.sheetnames
wb.sheetnames[0]	# 查看第一个工作表名称

4.指定工作表
ws = wb['sheet_name']
ws = wb[wb.sheetnames[0]]
ws = wb.get_sheet_by_name("sheet_name")

5.表的属性
ws.max_col		最大列数
ws.max_row		最大列数
ws.values		获取的内容是从 “A1” 到 “最大行最大列”
ws.rows			按行
ws.iter_rows()
ws.columns		按列
ws.iter_col() 
rows 和 iter_row()的区别在于，iter_row()可以指定区域，rows是全部单元格

6.操作
ws.delete_cols(1)		删除第一列
ws.delete_rows(3)		删除第三行
ws.merge_cells("A1:B1")		合并单元格
ws.merge_cells(start_column=3,end_column=6,start_row=2,end_row=3)
ws.merged_cells	   已存在的合并单元格（合并后的单元格，只会保留最上角的值，其他单元格的值全部为空(None)）
ws.auto_filter.ref = "A:B"	过滤和排序
ws.auto_filter.add_filter_column(0, ['ASC','DWS'])	# 给指定列添加过滤条件
ws.auto_filter.add_sort_condition("B2:B15")


7.引入pandas
import pandas as pd
df = pd.DataFrame(ws.values)

3.单元格操作

1.选择单个单元格
ws['A1']
ws.cell(1,1)    # 先行后列，都是索引下标

2.单元格属性
cell = ws['A1']
cell.col_idx	索引idx(列)
cell.column  	列索引
cell.row		行索引
cell.column_letter	单元格列名
cell.coordinate		单元格的坐标
cell.data_type		日期时间
cell.encoding		单元格编码格式，默认 utf-8
cell.has_style		是否有样式（默认样式是 Normal，如果是默认样式，返回False）
cell.style			单元格样式
cell.style_id		单元格样式id
cell.font			单元格的样式属性
cell.alignment
cell.border
cell.fill
cell.number_format
cell.hyperlink


3.单元格值
ws['A1'].value	单个单元格值
ws.cell(1,1).value
ws['A']			一列 字符串
ws[1]			一行，数字
ws['A:B']		多行
ws[5:10]
ws['A3:B9']		指定区域单元格

for i in ws[1]:      打印一行所有值
    print(i.value)

4.单元格赋值
ws['A1'] = 20
ws.cell(2,2).value = 30
ws.append([1,2,3])		增加一行

5.单元格遍历
方法一：
for i in ws.values:
    print(i)
方法二：  
for i in ws.rows:
    for j in i:
        print(j.value)
方法三：
for i in ws.iter_rows(min_col=1,max_col=3,min_row=1,max_row=10):
    print(i)

三、样式

1.颜色

Color(index=0) # 根据索引进行填充
# 
Color(rgb='00000000') # 根据rgb值进行填充
# index 
COLOR_INDEX = (
    '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4
    '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9
    '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14
    '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19
    '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24
    '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29
    '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34
    '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39
    '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44
    '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49
    '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54
    '00969696', '00003366', '00339966', '00003300', '00333300', #55-59
    '00993300', '00993366', '00333399', '00333333',  #60-63
)
BLACK = COLOR_INDEX[0]
WHITE = COLOR_INDEX[1]
RED = COLOR_INDEX[2]
DARKRED = COLOR_INDEX[8]
BLUE = COLOR_INDEX[4]
DARKBLUE = COLOR_INDEX[12]
GREEN = COLOR_INDEX[3]
DARKGREEN = COLOR_INDEX[9]
YELLOW = COLOR_INDEX[5]
DARKYELLOW = COLOR_INDEX[19]

2.字体

ws.cell(5,3).value='哈哈哈'
ws.cell(5,3).font = Font(name='仿宋',size=12,color=Color(index=0),b=True,i=True)

# size   sz  字体大小
# b bold  是否粗体
# i italic  是否斜体
# name family  字体样式

3.边框

Side(style='thin',color=Color(index=0))

# style可选项
style = ('dashDot','dashDotDot', 'dashed','dotted',
'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
'mediumDashed', 'slantDashDot', 'thick', 'thin')
#  'medium' 中粗
#  'thin'  细
#  'thick'  粗
#  'dashed'  虚线
#  'dotted'  点线

Border(left=Side(),
      right=Side(),
      top=Side(),
      bottom=Side())

<openpyxl.styles.borders.Border object>
Parameters:
outline=True, diagonalUp=False, diagonalDown=False, start=None, end=None, left=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, right=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, top=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, bottom=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, diagonal=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, vertical=None, horizontal=None

4.填充

PatternFill(patternType='solid',fgColor=Color(), bgColor=Color())
# fgColor   前景色
# bgColor   后景色
# 参数可选项
patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', 
               'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', 
               'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', 
               'lightHorizontal', 'darkTrellis', 'darkVertical'}

ws.cell(3,3).fill = PatternFill()

5.对齐

Alignment(horizontal='fill',vertical='center')

# 参数可选项
horizontal = {'fill', 'distributed', 'centerContinuous', 'right',
              'justify', 'center', 'left', 'general'}

vertical = {'distributed', 'justify', 'center', 'bottom', 'top'}

ws.cell(3,3).alignment= Alignment()

6.数字显示样式

wb.guess_types = True		设置工作薄自动识别单元格样式

7.链接

1.Excel的链接公式
ws['C5'].value = '=HYPERLINK("#Sheet!B2","名称")'

2.hyperlink参数
from openpyxl.worksheet.hyperlink import Hyperlink
ws['C6'].hyperlink = Hyperlink(ref='',location='Sheet!H5',target='')
ws['C6'].value = '这是链接'
# 参数说明
	target : 目标文件
	location ：目标单元格 工作表名 + ！ + 单元格名

3.如果需要显示蓝色字体和下划线，需要设置字体
link = NamedStyle(name='link',font=Font(color=colors.BLUE,underline='single'))
ws['C6'].style = link

8.行高 列宽

row =ws.row_dimensions[1]
row.height = 15

col = ws.column_dimensions['E']
col.width = 10