openpyxl 学习笔记

1

pip/pip3 install openpyxl

1

2

3

4

5

import openpyxl

from openpyxl import load_workbook  #load_workbook 只能打开已经存在的表格，不能创建新表格

filename = "阿里云天池电商婴儿用户数据.xlsx"

work_book = load_workbook(filename=filename)

print(work_book.sheetnames)  #打印表格中所有的sheet名称

1

2

3

sheet = work_book['(sample)sam_tianchi_mum_baby_tr']   # 方式一  指定sheet名称

sheet = work_book.active                               # 方式二  使用active属性，表示当前活跃的或者唯一的sheet

print(sheet.dimensions)  # 打印sheet尺寸大小  A1:G1002  从A1到G1，共1002行

1

2

3

cell = sheet['A1']                  # 方式一 通过cell坐标，A1表示格子的坐标

cell = sheet.cell(row=1,column=1)   # 方式二 通过指定行和列

print(cell.value, cell.row, cell.column, cell.coordinate) # 打印格子 内容 行数 列数 坐标

1

2

3

4

5

6

cells = sheet['A1:A5']

print(cells)

for cell in cells:

    print(cell[0].value)

    print(cell[0].row)

    print(cell[0].column)

1

2

3

4

cells = sheet['A']      # 获取指定列所有格子，如A列所有格子

cells = sheet['A:C']    # 获取指A列到G列所有格子

cells = sheet[1]        # 获取第一行所有格子

cells = sheet[1:5]     # 获取第一行到第五行所有格子

1

2

3

# 起始行数 2 结束行数 3 起始列 1(A) 结束列 2(B)

for row in sheet.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2):

    print(row) # A2,B2  A3,B3

1

2

3

# 起始行数 2 结束行数 3 起始列 1(A) 结束列 2(B)

for col in sheet.iter_cols(min_row=2,max_row=3,min_col=1,max_col=2):

    print(col) # A2,A3  B2,B3

1

2

for row in sheet.rows:

    print(row)

1

2

for col in sheet.columns:

    print(col)　

1

2

3

4

5

import openpyxl

from openpyxl import load_workbook  #load_workbook 只能打开已经存在的表格，不能创建新表格

filename = "阿里云天池电商婴儿用户数据.xlsx"

work_book = load_workbook(filename=filename)

sheet = work_book.active 

1

2

3

4

5

6

7

8

# 通过sheet实例写入

sheet['A1'] = '这是测试写入'

work_book.save(filename=filename)  #也可以是其他表格

# 通过cell实例写入

cell = sheet['A1']

cell.value = '这是测试写入'

work_book.save(filename=filename)

1

2

3

4

5

6

7

8

9

10

data = [

    ['a',1],

    ['b',2],

    ['c',3],

    ['d',4]

]

for row in data:

    sheet.append(row)

work_book.save(filename=filename)

1

2

3

4

5

from openpyxl.utils import FORMULAE

print(FORMULAE)  # 打印所有支持直接赋值的公式

sheet['F1002'] = '=AVERAGE(F2:F1001)' #对F列进行评价计算

work_book.save(filename=filename)

1

2

3

4

5

6

7

# 插入一列

sheet.insert_cols(idx=2)  #在第2列的左边插入1个空列

work_book.save(filename=filename)

# 插入多列

sheet.insert_cols(idx=2,amount=3)  #在第2列的左边插入3个空列

work_book.save(filename=filename)

1

2

3

4

5

6

7

# 插入一行

sheet.insert_rows(idx=2)  #在第2行的上面插入1行

work_book.save(filename=filename)

# 插入多行

sheet.insert_rows(idx=2,amount=3)  #在第2行的上面插入3行

work_book.save(filename=filename)

1

2

3

4

5

6

7

8

# 删除一列

sheet.delete_cols(idx=2) #删除第2列

work_book.save(filename=filename)

# 删除多列

sheet.delete_cols(idx=2,amount=3) #从第2列开始删除3列 即 2 3 4 列

work_book.save(filename=filename)　

1

2

3

4

5

6

7

# 删除一行

sheet.delete_rows(idx=2) #删除第2行

work_book.save(filename=filename)

# 删除多行

sheet.delete_rows(idx=2,amount=3) #从第2行开始删除3行 即 2 3 4 行

work_book.save(filename=filename)

1

2

3

# 将 C1和D4直接的格子 向下 2行 向左2行   正整数时 row下 col为左，反之 row为上 col为右

sheet.move_range("C1:D4",rows=2,cols=-2)

work_book.save(filename=filename)

1

2

work_book.create_sheet("表格2")

work_book.save(filename=filename)

1

2

work_book.remove("表格2")

work_book.save(filename=filename

1

2

work_book.copy_worksheet(sheet)

work_book.save(filename=filename) #新sheet名称为 原sheet名称+ ' Copy' 注意有空格

1

2

sheet.title = "表格3"

work_book.save(filename=filename)

1

2

sheet.freeze_panes= "G2" #冻结G列第2行  左右移动时 G列之前冻结  上下滚动第1行冻结，不包括第2行

work_book.save(filename=filename)

1

2

sheet.auto_filter.ref = sheet.dimensions  # 对整张表格都筛选，执行结果第一行所有列都有筛选下拉选项

work_book.save(filename=filename)　

1

2

3

4

5

6

# Font(name=字体名称, size=字体大小, bold=是否加粗, italic=是否斜体,color=字体颜色)

from openpyxl.styles import Font

cell = sheet['A1']

font = Font(name = "宋体 Regular",zize=12, bold=True, italic=True,color='FF000000')  

cell.font = font

work_book.save(filename=filename)

1

2

3

4

5

#cell.font.属性

workbook = load_workbook( filename '这是一个表格.xlsx ')sheet = workbook.active

cell = sheet[ 'A2']

font = cell.font

print(font.name，font.size，font.bold, font.italic)

1

2

3

4

5

6

7

8

# Alignment(horizontal=水平对齐模式, vertical=垂直对齐模式, text_rotation=旋转角度, wrap_text=是否自动换行)

# 水平对齐样式  distributed justify  center left fill centerContinuous right general

# 垂直对齐样式  bottom distributed justify  center top

cell.alignment = alignment

from openpyxl.styles import Alignment

cell = sheet['A1']

alignment = Alignment(horizontal='center',vertical='center',text_rotation=45,warp_text=True) 

work_book.save(filename=filename)

1

2

3

4

5

6

7

8

9

from openpyxl.styles import Side,Border

cell = sheet['A1']

side1 = Side(style='thin',color='FF000000')

side2 = Side(style='dotted',color='FFFF0000')

border = Border(left=side1,right=side1,top=side2,bottom=side2)

cell.border = border

work_book.save(filename=filename)

1

2

3

4

5

6

7

8

9

10

11

12

from openpyxl.styles import PatternFill，GradientFill

from openpyxl import load_workbook  

workbook = load_workbook(filename='这是一个表格.xlsx ')

sheet = workbook.active   

cell_a3 = sheet[ 'A3']   

pattern_fill = PatternFill( fill_type='solid', fgColor='99ccff')

cell_a3.fill = pattern_fil1

cell_a4 = sheet[ 'A4']  

gradient_fill = GradientFill(stop=( 'FFFFFF' , '99ccff' , '000000 ')) #渐变色

cell_a4.fill = gradient_fill   

workbook.save( filename='测试.xlsx ')  

1

2

3

4

5

6

7

# .merge_cells(待合并的格子编号)

# .merge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号，end_column=结束列号)   

workbook = load_workbook( filename='测试.xlsx')

sheet = workbook.active   

sheet.merge_cells('C1:D2')

sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=4)

workbook.save( filename='测试.xlsx ')

1

2

3

4

5

6

7

# .unmerge_cells(待合并的格子编号)

# .unmerge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号, end_column=结束列号)  

workbook = load_workbook( filename='测试.xlsx')

sheet = workbook.active

sheet.unmerge_cells ( 'C1:D2')

sheet.unmerge_cells (start_row=7, start_column=1, end_row=8,end_column=4)

workbook.save( filename='测试.xlsx ')

1

2

3

4

5

6

7

8

9

from openpyxl import load_workbook

from openpyxl.drawing.image import Image

workbook = load_workbook(filename="测试.xlsx")

sheet = workbook.active

logo = Image("刘亦菲.jpg")  # 图片路径

logo.height = 100

logo.width = 100

sheet.add_image( logo,"C1")  # 插入位置

workbook.save( filename="测试.xlsx")  

1

2

3

4

5

6

7

8

9

10

11

from openpyxl import load_workbook

from openpyxl.chart import BarChart, Reference

workbook = load_workbook(filename="测试.xlsx")

sheet = workbook.active

chart = BarChart()

data = Reference(worksheet=sheet,min_row=1,max_row=7, min_col=2,max_col=3)  #设定数据引用范围

categories = Reference(sheet,min_col=1, min_row=2, max_row=7) # 分类名称

chart.add_data(data,titles_from_data=True) #titles_from_data 使用数据源中的标题，如果存在的话

chart.set_categories(categories)

sheet.add_chart(chart,"E2")

workbook.save("测试.xlsx")

1

2

3

4

5

6

7

8

9

10

11

from openpyxl import load_workbook

from openpyxl.chart import LineChart,Reference

workbook = load_workbook(filename="测试.xlsx" )

sheet = workbook.active

chart = LineChart()

data = Reference(worksheet=sheet, min_row=20,max_row=21,min_col=1,max_col=13)

categories = Reference(sheet,min_row=19,min_col=2, max_col=13)

chart.add_data(data,from_rows=True,titles_from_data=True)  # from_rows=True表示数据是横向的

chart.set_categories(categories)

sheet.add_chart(chart,"B23")

workbook.save("测试.xlsx")