干货！教你使用 openpyxl 处理表格数据~

[root@localhost ~]# yum -y install python3-openpyxl

[root@localhost data]# python3

# 导入openpyxl库
>>> import openpyxl
# path变量指定表格文件的位置
>>> path = "/data/wb.xlsx"
# 加载表格文件
>>> wb_obj = openpyxl.load_workbook(path)
# 激活默认的工作表
>>> sheet_obj = wb_obj.active
# 选择第一行第一列的单元格，也就是A1单元格
>>> cell_obj = sheet_obj.cell(row = 1, column = 1)
# 打印单元格所属位置
>>> print(cell_obj)
<cell 'Sheet1'.A1>
# 打印A1单元格的值
>>> print(cell_obj.value)
Name

# 使用max_row获取工作表有多少行的数据
>>> row = sheet_obj.max_row
# 使用max_column获取工作表有多少列的数据
>>> column = sheet_obj.max_column
# 打印行数
>>> print("Total Rows: ", row)
Total Rows:  6
# 打印列数
>>> print("Total Cols: ", column)
Total Cols:  4

# 使用for循环打印第一列数据
>>> for i in range(1, row + 1):
...   cell_obj = sheet_obj.cell(row = i, column = 1)
...   print(cell_obj.value)
... 
Name
Ankit
Rahul
Priya
Nikhil
Nisha

# 使用for循环打印第二条数据：
>>> for i in range(1, column + 1):
...   cell_obj = sheet_obj.cell(row = 3, column = i)
...   print(cell_obj.value, end = " ")
... 
Rahul M.Tech CSE 2

# 选中A1到B6的单元格
>>> cell_obj = sheet_obj['A1':'B6']
>>> 
# for玄幻打印两列的数据
>>> for cell1, cell2 in cell_obj:
...   print(cell1.value, cell2.value)
... 
Name Course
Ankit B.Tech
Rahul M.Tech
Priya MBA
Nikhil B.Tech
Nisha B.Tech

[root@localhost data]# python3

# 导入openpyxl库里面的Workbook
>>> from openpyxl import Workbook
>>> 
# 创建空的工作簿
>>> workbook = Workbook()
>>> 
# 保存文件
>>> workbook.save(filename="/data/sample.xlsx")
>>>

# 获取激活的工作表
>>> sheet = workbook.active
>>> 
# 为A1添加Hello值
>>> c1 = sheet.cell(row = 1, column = 1, value = 'Hello')
# 为B1添加World值
>>> C2 = sheet.cell(row = 1, column = 2, value = 'World')
>>> 
# 为A2添加Welcome值
>>> C3  = sheet['A2'].value = 'Welcome'
# 为B2添加Everyone值
>>> C4 = sheet['B2'].value = 'Everyone'
>>> 
# 保存到文件
>>> workbook.save("/data/sample.xlsx")

[root@localhost data]# python3

>>> import openpyxl
>>> 
# 使用load_workbook加载文件
>>> wb = openpyxl.load_workbook("/data/sample.xlsx")
>>> 
>>> sheet = wb.active
>>> 
>>> c = sheet['A3']
>>> 
>>> c.value = 'New Data'
>>> 
>>> wb.save("/data/sample.xlsx")

# 定义数据
>>> data = (
... (1,2,3),
... (4,5,6)
... )
>>> 
# 使用for循环将内容附加到工作表中
>>> for row in data:
...   sheet.append(row)
... 
>>> wb.save('/data/sample.xlsx')

[root@localhost data]# python3

>>> import openpyxl
>>> 
# 创建新工作簿
>>> wb = openpyxl.Workbook()
>>> 
>>> sheet = wb.active
>>> 
# 在单元格中写入数据
>>> sheet['A1'] = 200
>>> sheet['A2'] = 300
>>> sheet['A3'] = 400
>>> sheet['A4'] = 500
>>> sheet['A5'] = 619

# 在A7单元格求和
>>> sheet['A7'] = '=SUM(A1:A5)'
>>> 
# 保存文件名称为sum.xlsx
>>> wb.save('/data/sum.xlsx')

[root@localhost data]# python3

# 导入模块
>>> import openpyxl
>>> 
# 创建新工作簿
>>> wb = openpyxl.Workbook()
>>> 
>>> sheet = wb.active
>>> 
# 向A1单元格写入数据
>>> sheet.cell(row = 1, column = 1, value = ' Hello ')
<cell 'Sheet'.A1>
# 向B2单元格写入数据
>>> sheet['B2'].value = ' Everyone '
>>> 
# 设置第一行单元格高度为45
>>> sheet.row_dimensions[1].height = 45
>>> 
# 设置B列单元格宽度为70
>>> sheet.column_dimensions['B'].width = 70
>>> 
>>> wb.save('/data/h_and_w.xlsx')

[root@localhost data]# python3

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active

# 合并A2到D4单元格，并添加数据
>>> sheet.merge_cells('A2:D4')
>>> sheet['A2'].value = 'Twelve cells join together.'
>>> 
# 合并C6到D6单元格，并添加数据
>>> sheet.merge_cells('C6:D6')
>>> sheet['C6'].value = 'Two merge cells.'
>>> 
>>> wb.save('/data/merge_cells.xlsx')

# 取消合并A2到D4单元格
>>> sheet.unmerge_cells('A2:D4')
>>> 
# 取消合并C6到D6单元格
>>> sheet.unmerge_cells('C6:D6')
>>> 
>>> wb.save('/data/merge_cells.xlsx')

[root@localhost data]# python3

>>> import openpyxl
# 从openpyxl.styles导入Font函数
>>> from openpyxl.styles import Font
>>> 
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> 
# 为A1单元格添加内容，并设置字体大小为24
>>> sheet['A1'].value = 'Linuxprobe.com'
>>> sheet['A1'].font = Font(size = 24)

# B2单元格添加内容，设置字体大小24，斜体
>>> sheet.cell(row = 2, column = 2, value = "Linuxprobe.com").font = Font(size = 24, italic = True)

# C3单元格添加内容，设置字体大小24，粗体
>>> sheet.cell(row = 3, column = 3, value ="Linuxprobe.com").font = Font(size = 24, bold = True)

# D4单元格添加内容，设置字体大小24，设置字体样式
>>> sheet.cell(row = 4, column = 4, value = "Linuxprobe.com").font = Font(size = 24, name = 'Times New Roman')
>>> wb.save('/data/font_styles.xlsx')

[root@localhost data]# python3

>>> import openpyxl
# 导入图表库
>>> from openpyxl.chart import BarChart, Reference
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> 
# 使用循环为工作表附加内容
>>> for i in range(10):
...   sheet.append([i])
... 
# 为图表创建数据
>>> values = Reference(sheet, min_col=1,min_row=1,max_col=1,max_row=10)
#创建图表实例
>>> chart = BarChart()
# 向条形图添加数据
>>> chart.add_data(values)
# 添加图表标题
>>> chart.title = " BAR-CHART "
# 添加X坐标标题
>>> chart.x_axis.title = " X_AXIS "
# 添加Y坐标标题
>>> chart.y_axis.title = " Y_AXIS "
>>> 
# 工作表的E2单元格添加图表
>>> sheet.add_chart(chart, 'E2')
>>> 
>>> wb.save("/data/charts.xlsx")

[root@localhost data]# python3

>>> import openpyxl
# 导入图表库
>>> from openpyxl.chart import LineChart, Reference
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> 
# 使用循环为工作表附加内容
>>> for i in range(10):
...   sheet.append([i])
... 
# 为图表创建数据
>>> values = Reference(sheet, min_col=1,min_row=1,max_col=1,max_row=10)
#创建图表实例
>>> chart = LineChart()
# 向折现图添加数据
>>> chart.add_data(values)
# 添加图表标题
>>> chart.title = " LINE-CHART "
# 添加X坐标标题
>>> chart.x_axis.title = " X_AXIS "
# 添加Y坐标标题
>>> chart.y_axis.title = " Y_AXIS "
>>> 
# 工作表的E2单元格添加图表
>>> sheet.add_chart(chart, 'E2')
>>> 
>>> wb.save("/data/charts.xlsx")

[root@localhost data]# yum -y install python3-pillow
[root@localhost data]# python3

>>> import openpyxl
# 导入Image函数
>>> from openpyxl.drawing.image import Image
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>>
# 向工作表第一行添加内容
>>> sheet.append(["abc",10,"def",20])
# 添加图片
>>> img = Image("/data/banner1.jpg")
>>> 
>>> sheet.add_image(img, 'A2')
>>> 
>>> wb.save("/data/images.xlsx")

官方站点：www.linuxprobe.com

Linux命令大全：www.linuxcool.com

刘遄老师QQ：5604215

Linux技术交流群：3861509

（新群，火热加群中……）

想要学习Linux系统的读者可以点击"阅读原文"按钮来了解书籍《Linux就该这么学》，同时也非常适合专业的运维人员阅读，成为辅助您工作的高价值工具书！