openpyxl 模块范例

openpyxl
https://www.cnblogs.com/zeke-python-road/p/8986318.html

1.创建和复制sheet，操作行和列

# coding=utf-8
from openpyxl import Workbook
from datetime import datetime
import time

# 概述：创建和复制sheet，操作行和列

# 创建Excel对象
wb = Workbook()

# 一, 获取当前激活的sheet(默认激活的Sheet为第一个)
ws = wb.active

# 1.1 简单设置
# 设置内容
ws['A1'] = '张三'
ws['B1'] = 20
ws.append([1,2,3])
ws['A3'] = datetime.now()   # 返回的是时间元组
ws['B3'] = time.strftime('%Y%m%d%H%M%S', time.localtime())

# 操作单元格,获取单元格的值
cell3_3 = ws.cell(row=3, column=3, value=10)  # 第3行，第3列，值为10
print('d.value: %s'%cell3_3.value)
print('A1 value is %s'%ws['A1'].value)

# 1.2 批量操作
# 操作列
a_cells = [cell.value for cell in ws['A']]    # 操作单列
print('a_cells: %s'%a_cells)
a_c_cell_list = [[cell.value for cell in column] for column in ws['A:C']]   # 操作多列，二维列表
print('a_c_cell_list: %s'%a_c_cell_list)
a_c_cells = [cell.value for column in ws['A:C'] for cell in column] # 操作多列，一维列表
print('a_c_cells: %s'%a_c_cells)

all_columns_cells = [cell.value for col in ws.columns for cell in col] # 获取所有列 ws.columns
print('all_columns_cells: %s'%all_columns_cells)
all_columns_cells_2 = [cell.value for col in ws.iter_cols() for cell in col] # 获取所有列 ws.iter_cols()
print('all_columns_cells_2: %s'%all_columns_cells_2)

# 操作行
one_three_cells = [cell.value for row in ws['1:3'] for cell in row] # 操作多行
print('one_three_cells :%s'%one_three_cells)
all_rows_cells = [cell.value for row in ws.rows for cell in row]    # 操作所有行 ws.rows
print('all_rows_cells :%s'%all_rows_cells)
all_rows_cells_2 = [cell.value for row in ws.iter_rows() for cell in row]    # 操作所有行 ws.iter_rows()
print('all_rows_cells_2 :%s'%all_rows_cells_2)

# 操作指定行范围，指定列范围
row_col_cells = []
for row in ws.iter_rows(min_row=1, max_row=2, min_col=1, max_col=2):
    for cell in row:
        row_col_cells.append(cell.value)
print('row_col_cells :%s'%row_col_cells)

print('*'*50)
# 二, 创建一个新的sheet, 并且更新名称
ws2 = wb.create_sheet("Mysheet", 0)     #设定sheet的插入位置(为第一个)， 默认插在后面
ws2.title = u"ws2"         #更新sheet的名字 必须是Unicode
ws2.sheet_properties.tabColor = 'FF3300'    #设置sheet的tab标签背景颜色(FF3300 红色)

# 根据sheet名称获取sheet对象
ws2_new = wb[u'ws2']
ws2_new.title = u'ws2_new'
ws2_new['A1'] = datetime.now()

# copy一个sheet (会把对应的格式也copy过来)
ws3 = wb.copy_worksheet(ws2_new)
ws3.title = u'ws3'

# 打印所有sheet的名称
sheet_name_list = [sheet_name for sheet_name in wb.sheetnames]
print('sheet_name_list: %s'%sheet_name_list)
sheet_title_list = [sheet.title for sheet in wb]
print('sheet_title_list: %s'%sheet_title_list)

# 保存
wb.save(r'D:\pyxl_01.xlsx')
print('success')

2.设置单元格格式(字体，大小，颜色)，合并单元格，使用公式

# coding=utf-8
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill, colors, Alignment, Protection
from openpyxl.worksheet.table import Table, TableStyleInfo
from copy import copy
from datetime import datetime

## 概述：设置单元格格式(字体，大小，颜色)，合并单元格，使用公式

wb = load_workbook(r'd:\pyxl_01.xlsx')
ws = wb[u'ws3']

# 单元格格式，合并单元格
ws['B1'] = datetime(2021, 10, 1)
print(ws['B1'].number_format)   # yyyy-mm-dd h:mm:ss

ws['B2'] = '12%'
print(ws['B2'].number_format)   # General   常规

ws['B3'] = 3.14
print(ws['B3'].number_format)   # General

ws['B4'] = 'hello'
print(ws['B4'].number_format)   # General

# 合并单元格
ws.merge_cells('C2:D2')
ws.merge_cells(start_row=3, start_column=3, end_row=4, end_column=4)
# 拆分单元格
ws.unmerge_cells('C2:D2')
ws.unmerge_cells(start_row=3, start_column=3, end_row=4, end_column=4)
# 隐藏单元格(隐藏D到F的列)
# ws.column_dimensions.group('D', 'F', hidden=True)

# 设置字体，大小和颜色
ft1 = Font(color=colors.BLUE, name=u'宋体', size=14)    # 字体对象(颜色，字体，大小)
ws['C1'] = '你好'
ws['C1'].font = ft1

ft2 = copy(ft1)     # copy字体样式
ft2.name='Tahoma'   # 字体
ft2.color='FF00CC'  # 红色
ft2.italic=True     # 斜体
ws['C2'] = '世界'
ws['C2'].font=ft2

# 设置行和列的字体
b_col = ws.column_dimensions['B']
b_col.font = Font(bold=True)
first_row = ws.row_dimensions[1]
first_row.font = Font(underline='single')

# 设定单元格的边框、字体、颜色、大小和边框背景色
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20,color= "ff0100")
highlight.fill = PatternFill("solid", fgColor="DDDDDD")#背景填充
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)

ws["A1"].style =highlight

# 设定一个表格区域，并设定表格的格式
ws = wb.create_sheet('new')
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws.append(row)

tab=Table(displayName='Tablel1', ref='A1:E5')
style=TableStyleInfo(name='TableStyleMedium1', showFirstColumn=True, showLastColumn=True, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo=style
ws.add_table(tab)


# 使用公式
ws['A1'] = 1    # bug(之前单元格的格式是时间格式，无法自动转换单元格格式)
ws['A2'] = 2
ws['A3'] = 3
ws['A4'] = '=SUM(A1:A3)'    # 6
ws['A5'] = '=SUM(1,7)'      # 计算1+7

# 保存
wb.save(r'd:\pyxl_01.xlsx')