Openpyxl库对excel的常规处理详解

from openpyxl import load_work

#openpyxl仅支持xlsx，如果表格文件不在脚本所在目录下，则需填ecel文件的完整路径
wb = load_work("xxx.xlsx")
ws = wb.active#激活工作表，得到第一张工作表，至少有一个工作表
ws = wb.get_sheet_by_name("xxx")#通过工作表名字得到
ws1 = wb.create_sheet() #默认在最后插入
ws1.title = "xxx" #命名工作表
ws2 = wb.create_sheet(title='Data')#创建一个分页，标题为Data
--------------------------------------------------------------------
#循环得到工作薄中的所有工作表
for sheet in wb:
    print(sheet.title)
--------------------------------------------------------------------
#保存文件
wb.save("xxx.xlsx")

#one_cell为cell对象，例子为第二行第二列的单元格
one_cell = ws.cell(row=2,column=2) 
#单元格的值
print(one_cell.value)
#将数字转换成excel列的字母形式get_column_letter(col)
>>>get_column_letter(3)
>>>C
-----------------------------------------------------------------------
#定位excel表格的最后一个单元格的位置(有限制：一共26个字母，超过26则报错)
import string
zimus = string.ascii_uppercase #26个大写字母
#将最大列数转换成对应的大写字母
#enumerate()函数用于一个可遍历的数据对象组合(列表、字符串)组合为一个索引序列，同时列出
#数据和数据下标，一般用在for循环中。
#eg:>>>list(enumerate(zimus,start=1))
#[(1,'A'),(2,'B'),(3,'C')....]
dictvar = {index:elem for index, elem in enumerate(zimus, start=1)}
max_row = ws.max_row #获取excel的最大行数
max_col = ws.max_col #获取excel的最大列数
alpha = dictvar[max_col]
position = alpha + str(max_row)
---------------------------------------------------------------------------------
#获取指定行的最后一个单元格的位置
#比如第二行的最后一个单元格
import string
zimus = string.ascii_uppercase
dictvar = {index:elem for index, elem in enumerate(zimus,start=1)}
max_col = ws.max_col
alpha = dictvar[max_col]
position = alpha + '2'#只有此处不同
---------------------------------------------------------------------------------
#访问多个单元格
cell_range = ws['A1':'C2']#方法1:第1个单元格到最后1个单元格
#方法二：最小-最大行；最小列-最大列
cells = ws.iter_rows(min_row=4, min_col=1, max_row=20, max_col=21)

from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Font, Alignment
wb = load_workbook("xxx.xlsx")
ws = wb.active
-------------------------------------------------------------------------
#Border 边框Side边线
#eg:将A1:D5范围的边框显示
thin = Side(border_style="thin", color="000000")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in ws[A1:D5]:
    for cell in row:
        cell.border = border
--------------------------------------------------------------------------
#Alignment 单元格对齐方式
#horizontal:代表水平方向，left:左对齐;center:居中对齐;right:右对齐;distributed:分散对齐;centerContinuous:跨列居中;justify:两端对齐
#vertical
#vertical:代表垂直方向，center:居中;top:靠上;bottom:靠下;justify:两端对齐;distributed:分散对齐
#wrap_text或者wrapText:自动换行
#eg:将单元格A3设置为水平右对齐，垂直居中，自动换行
align = Alignment(horizontal='right', vertical='center', wrap_text=True)
ws['A3'].alignment = align
------------------------------------------------------------------------------
#Font 字体设置
#name:字体名字
#size:字体大小
#color:字体颜色
#bold:加粗 True or False
#italic:斜体 True or False
#strike:删除线 True or False
#vertAlign = None 垂直对齐
#纵向对齐方式有3种：baseline superscript subscript
#underline = 'none':下划线
#eg
font = Font(size=11, bold=True, name='微软雅黑', color="FF0000")
WS['A3'].font = font
------------------------------------------------------------------------------
#PatternFill 填充
#fill_type:填充类型，若没有特别指定类型，则后续的参数都无效
#soild:纯色填充、其余类型：'none'/'darkDown'/'darkGray'/'darkGrid'/'darkHorizontal'等
#start_color:前景色 end_color:背景色
#eg

```python
fill = PatternFill(patternType="solid",start_color="33CCFF")
ws['A3'].fill = fill
-------------------------------------------------------------------------------

#合并单元格
ws.merge_cells(range_string='A1:B3') #方法1
ws.merge_cells(start_row=5, start_column=4, end_row=8, end_column=8)#方法2
-----------------------------------------------------------------------
#取消单元格合并
ws.unmerge_cells(range_string='A1:B3')
ws.unmerge_cells(start_row=5, start_column=4, end_row=8, end_column=8)

**注意：**openpyxl的单元格合并会把区域左上角单元格的值赋给合并之后的单元格，如果这个单元格没有值，即使区域内有其他单元格有值，合并后的单元格要是空白。
下面这个函数：合并单元格并且将合并区域最上边最左边有值的单元格赋值给合并后的单元格。

from openpyxl import Workbook
from openpyxl.utols import get_column_letter

def merge_cells_value(sheet=None, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
	if start_row > end_row:
		fmt = "{end_row} must be greater than {start_row}"
		raise ValueError(fmt.format(start_row=start_row, end_row=end_row))
	if start_column > end_column:
		fmt = "{end_column} must be greater than {start_column}"
		raise ValueError(fmt.format(start_colum=start_column, end_column=end_column)
	if range_string is None:
		fmt = '{start_column}{start_row}:{end_column}{end_row}'
		range_string = fmt.format(start_row=start_row, start_column=get_column_letter(start_column), end_row=end_row, end_column=get_column_letter(end_column))
	
	v=None
	for cells in sheet[range_string]:
		for cell in cells:
			if cell.value is not None:
				v = cell.value
				break
		if v is not None:
			break
	print(range_string)
	sheet.merge_cells(range_string=range_string)
#使用方式
wb = Workbook()
ws = wb.active
merge_cells_value(sheet=ws, start_row=5, start_column=4, end_row=8, end_column=8)

	

#改变行高和列宽
import openpyxl
wb = openpyxl.Workbook()
ws = wb.active
ws.row_dimensions[2].height = 69 #增加第2行行高
ws.column_dimensions['C'].width = 23 #增加C列列宽
-----------------------------------------------------------------------------
#改变整行整列的格式，前提是单元格已创建
#eg：改变A列的格式
font = Font(size=11, bold=True, name='微软雅黑', color="FF0000")
col = ws.column_dimensions['A']
col.font = font
#eg:改变第1行的格式
row = ws.row_dimensions[1]
row.font = font

import openpyxl import Workbook
import datetime
wb = Workbook()
ws = wb.active()
ws['A1'] = datetime.datetime.today()
ws['A1'].number_format = 'yyyy-mm--dd hh:mm:ss'
ws['A2'] = datetime.datetime.fromisoformat('2020-12-12 12:22:22')
ws['A2'].number_format = 'yyyy年mm月dd hh时mm分ss秒'

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = 1111111
ws['A1'].number_format = '#,##0'
ws['A2'] = 111111.55
ws['A2'].number_format = '"￥"#,##0.00;-"￥"#,##0.00'