Python处理Excel-openpyxl模块

本文介绍使用Python的openpyxl模块处理Excel文件。

一、前言

日常工作中，难免会遇到处理excel表格的工作，如果简单功能我们可以直接使用excel自身的能力去处理，但是如果遇到一些复杂的逻辑运算，批量加工处理时就很麻烦，而且很容易出现错误。本文介绍如何使用Python的openpyxl模块对日常工作中的excel进行处理，以及Python可以帮助我们做哪些基础工作。

二、模块简介

1、openpyxl功能介绍

  • 读取、写入数据；
  • 使用公式、样式；
  • 插入图表。
    详情参考 官方文档 。

2、openpyxl安装

模块不是Python内置的模块，需要我们自己安装。
命令行执行进行安装：pip3 install openpyxl

三、Excel操作示例

几个概念介绍：

  • 单元格（cell） 最小引用单位
  • 行（row）
  • 列（column）
  • 表（sheet）
  • 工作簿（excel文件）

（一）、读取Excel

读取Excel的步骤一般为：获取工作簿对象->获取工作表对象->读取对应工作表中内容。

1、获取工作簿

from openpyxl import load_workbook

workbook = load_workbook(filename='example.xlsx')

2、获取工作表

（1）读取工作簿中所有sheet页

基于上一步，获取sheet名称列表，返回是所有sheet的名称。

print(workbook.sheetnames)

（2）通过sheet名获取sheet

sheet = workbook['Sheet1']

（3）获取当前活跃的sheet

sheet = workbook.active

（4）sheet中表格的尺寸

sheet = workbook['Sheet2']
print(sheet.dimensions)

返回示例：A1:B3

3、读取单元格

（1）获取单元格(cell)的内容,行，列，坐标

根据坐标获取单元格内容：

cell = sheet['A1']
print(cell.value)
print(cell.row)
print(cell.column)
print(cell.coordinate)

根据行数和列数获取单元格内容：

cell = sheet.cell(row=1,column=1)
print(cell.value)
print(cell.row)
print(cell.column)
print(cell.coordinate)

####（2）获取多个单元格

获取指定区域单元格：

cells = sheet['A2:A3']
print(cells)

返回多个cell对象，以元组的形式存储。 获取单行或单列单元格：

col_cells = sheet['A']
print(col_cells)
row_cells = sheet[5]
print(row_cells)

获取多行或多列单元格：

col_cells = sheet['A:C']
print(col_cells)
row_cells = sheet[5:6]
print(row_cells)

上面是按照单个cell给我们返回，如果项按照行或者列输出则如下：
按行分组获取单元格：

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

按列分组获取单元格：

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

返回所有的行的单元格：

for row in sheet.rows:
    print(row)

返回所有的列的单元格：

for col in sheet.columns:
    print(col)

（二）、写入Excel

写excel的步骤为：获取或创建工作簿对象->获取工作表对象->单元格中写入内容或修改样式->保存工作簿

1、获取或创建工作簿对象

已有excel文件的获取：

from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')

创建新的工作簿对象：

from openpyxl import Workbook

workbook = Workbook()

workbook.save("new_test.xlsx")

2、获取工作表

（1）通过sheet名获取sheet

sheet = workbook['Sheet1']

（2）获取当前活跃的sheet

sheet = workbook.active

（3）删除sheet

print(workbook.sheetnames)
oldSheet = workbook['Sheet2']
workbook.remove(oldSheet)
print(workbook.sheetnames)
workbook.save("test.xlsx")

（4）复制sheet

print(workbook.sheetnames)
srcSheet = workbook['srcSheetName']
workbook.copy_worksheet(srcSheet)
print(workbook.sheetnames)

（5）修改sheet名称

sheet.title = 'newSheetName'

3、修改工作表

（1）单个单元格中插入数据

sheet['A1'] = '物联网161班花名册'

另一种实现：

cell = sheet['A1']
cell.value = '物联网161班花名册'

（2）多条数据插入到单元格

data = [
    ['李四',10111],
    ['王五',10112],
    ['赵六',10113]
]
for row in data:
    sheet.append(row)

（3）插入公式到单元格

sheet['F10'] = '=AVERAGE(F2:F99)'

查看openpyxl支持的公式：

from openpyxl.utils import FORMULAE
print(FORMULAE)

frozenset({‘VLOOKUP’, ‘POWER’, ‘PMT’, ‘TRUE ADDRESS’, ‘GAMMALN’, ‘ODD’, ‘ODDLPRICE’, ‘XIRR’, ‘COVAR’, ‘FORECAST’, ‘LOG’, ‘MMULT’, ‘IMPRODUCT’, ‘IMSUB’, ‘BETADIST’, ‘COUPNCD’, ‘FISHERINV’, ‘PERCENTRANK’, ‘ROUNDDOWN’, ‘DURATION’, ‘TRUNC’, ‘ISNA’, ‘FIXED’, ‘IMABS’, ‘AMORLINC’, ‘SUM’, ‘MODE’, ‘VARPA’, ‘TIME’, ‘CRITBINOM’, ‘TANH’, ‘COUNTA’, ‘CUMIPMT’, ‘COUPDAYBS’, ‘N’, ‘DATEDIF’, ‘PRICEMAT’, ‘BIN2DEC’, ‘ECMA.CEILING’, ‘YIELDDISC’, ‘LARGE’, ‘TYPE’, ‘OCT2DEC’, ‘ODDLYIELD’, ‘PI’, ‘NOW’, ‘ODDFYIELD’, ‘COUPNUM’, ‘RTD’, ‘EOMONTH’, ‘LEN’, ‘ISREF’, ‘RECEIVED’, ‘DEVSQ’, ‘DSTDEV’, ‘SEARCHB’, ‘RIGHTB’, ‘TDIST’, ‘BESSELJ’, ‘MOD’, ‘HARMEAN’, ‘IMDIV’, ‘MAXA’, ‘INDIRECT’, ‘DSTDEVP’, ‘AMORDEGRC’, ‘INFO’, ‘PEARSON’, ‘QUOTIENT’, ‘GEOMEAN’, ‘PRICEDISC’, ‘CELL’, ‘ATAN’, ‘WORKDAY.INTL’, ‘GROWTH’, ‘PERMUT’, ‘FREQUENCY’, ‘ROUNDUP’, ‘DEC2OCT’, ‘LOG10’, ‘IMLOG2’, ‘TIMEVALUE’, ‘NA’, ‘IMCOS’, ‘NORMDIST’, ‘MROUND’, ‘YEAR’, ‘LOGEST’, ‘DB’, ‘ROW’, ‘CHAR’, ‘PERCENTILE’, ‘SQRTPI’, ‘STDEVPA STEYX’, ‘AVERAGE’, ‘PRODUCT’, ‘MIRR’, ‘INDEX’, ‘BETAINV’, ‘IMSIN’, ‘TEXT’, ‘IFERROR’, ‘ROWS’, ‘ISNONTEXT’, ‘AVERAGEIF’, ‘LCM’, ‘DSUM’, ‘IMSUM’, ‘ASINH’, ‘EXACT’, ‘IMEXP’, ‘TBILLPRICE’, ‘CUBEKPIMEMBER’, ‘CUBESET’, ‘SERIESSUM’, ‘IMCONJUGATE’, ‘REPT’, ‘INTERCEPT’, ‘BIN2OCT’, ‘COUPDAYS’, ‘IRR’, ‘SLOPE’, ‘ACOS’, ‘AVERAGEIFS’, ‘DATEVALUE’, ‘IMREAL’, ‘AVERAGEA’, ‘CODE’, ‘YEARFRAC’, ‘FINV’, ‘DEC2HEX’, ‘BINOMDIST’, ‘STDEVP’, ‘COUPDAYSNC’, ‘MONTH’, ‘CONFIDENCE’, ‘MIN’, ‘DAVERAGE’, ‘TODAY’, ‘MDURATION’, ‘COMBIN’, ‘STDEV STDEVA’, ‘RANK’, ‘OCT2BIN’, ‘LOGNORMDIST’, ‘DAY’, ‘VDB’, ‘TTEST’, ‘INT’, ‘DISC’, ‘DVAR’, ‘NORMSDIST’, ‘NEGBINOMDIST’, ‘NOMINAL’, ‘SQRT’, ‘GETPIVOTDATA’, ‘JIS’, ‘FIND’, ‘FINDB’, ‘TAN’, ‘SIN’, ‘ZTEST’, ‘SKEW’, ‘VARA’, ‘MINA’, ‘GAMMAINV’, ‘ISERR’, ‘ACCRINTM’, ‘OR’, ‘COLUMN’, ‘VARP’, ‘ISPMT’, ‘ROMAN’, ‘BAHTTEXT’, ‘DEC2BIN’, ‘POISSON’, ‘NPER’, ‘TINV’, ‘PRICE’, ‘NETWORKDAYS’, ‘EXPONDIST’, ‘BESSELK’, ‘ERROR.TYPE’, ‘ISTEXT’, ‘IMLOG10’, ‘DCOUNT’, ‘SUMX2MY2’, ‘PROB’, ‘AREAS’, ‘MEDIAN’, ‘BESSELY’, ‘CLEAN’, ‘LN’, ‘EXP’, ‘PPMT’, ‘IMAGINARY’, ‘KURT’, ‘ATANH’, ‘NETWORKDAYS.INTL’, ‘HYPERLINK’, ‘ISNUMBER’, ‘MIDB’, ‘VAR’, ‘QUARTILE’, ‘LEFT’, ‘EFFECT’, ‘IF’, ‘NORMSINV’, ‘HEX2DEC’, ‘TRANSPOSE’, ‘SMALL’, ‘LINEST’, ‘FTEST’, ‘MID’, ‘LOWER’, ‘FALSE’, ‘IMLN’, ‘PROPER’, ‘OCT2HEX’, ‘COS’, ‘GAMMADIST’, ‘COUNTIF’, ‘GESTEP’, ‘CUBERANKEDMEMBER’, ‘CEILING’, ‘DOLLAR’, ‘FVSCHEDULE’, ‘COUNTIFS’, ‘MATCH’, ‘BIN2HEX’, ‘COUNTBLANK’, ‘DGET’, ‘IMARGUMENT’, ‘UPPER’, ‘REPLACE’, ‘EVEN’, ‘PHONETIC’, ‘ISO.CEILING’, ‘HEX2OCT’, ‘RAND’, ‘LOGINV’, ‘CORREL’, ‘RSQ’, ‘COSH’, ‘FV’, ‘RANDBETWEEN’, ‘DOLLARDE’, ‘RIGHT’, ‘PV’, ‘ISERROR’, ‘DDB’, ‘DATE’, ‘DMIN’, ‘ATAN2’, ‘TBILLEQ’, ‘FISHER’, ‘LOOKUP’, ‘MAX’, ‘SUMIF’, ‘NPV’, ‘SUBSTITUTE’, ‘XNPV’, ‘COUPPCD’, ‘ISODD’, ‘REPLACEB’, ‘DELTA’, ‘CUBESETCOUNT’, ‘LEFTB’, ‘ROUND’, ‘DMAX’, ‘WEIBULL’, ‘HLOOKUP’, ‘T’, ‘ACCRINT’, ‘YIELD’, ‘CHOOSE’, ‘SYD’, ‘FACTDOUBLE’, ‘CHITEST’, ‘IMPOWER’, ‘SEARCH’, ‘DEGREES’, ‘DVARP’, ‘MDETERM’, ‘YIELDMAT’, ‘AND’, ‘CUBEMEMBERPROPERTY’, ‘DCOUNTA’, ‘SUMXMY2’, ‘VALUE’, ‘SUMPRODUCT’, ‘CHIINV’, ‘TBILLYIELD’, ‘MULTINOMIAL’, ‘FDIST’, ‘SUMIFS’, ‘COUNT’, ‘ERF’, ‘STANDARDIZE’, ‘SUMX2PY2’, ‘CUBEVALUE’, ‘AVEDEV’, ‘WEEKNUM’, ‘DOLLARFR’, ‘RADIANS’, ‘HOUR’, ‘FLOOR’, ‘INTRATE’, ‘CHIDIST’, ‘SUBTOTAL’, ‘DAYS360’, ‘ODDFPRICE’, ‘ACOSH’, ‘SLN’, ‘ABS’, ‘CONCATENATE’, ‘ERFC’, ‘ISLOGICAL’, ‘ASIN’, ‘DPRODUCT’, ‘SECOND’, ‘MINVERSE’, ‘NORMINV’, ‘IMSQRT’, ‘IPMT’, ‘SINH’, ‘OFFSET’, ‘WORKDAY ‘, ‘CUMPRINC’, ‘ISEVEN’, ‘TRIMMEAN’, ‘COMPLEX’, ‘SIGN’, ‘MINUTE’, ‘ISBLANK’, ‘TRIM’, ‘SUMSQ’, ‘ASC’, ‘HYPGEOMDIST’, ‘WEEKDAY’, ‘CONVERT’, ‘EDATE’, ‘CUBEMEMBER’, ‘BESSELI’, ‘COLUMNS’, ‘RATE’, ‘LENB’, ‘HEX2BIN’, ‘GCD’, ‘TREND’, ‘FACT’, ‘NOT’})

（4）表格新增行和列

sheet.insert_cols(idx=2, amount=3)
sheet.insert_cols(idx=2) 2列左边插入一列。 sheet.insert_cols(idx=2, amount=3) 2列左边插入三列。 
sheet.insert_rows(idx=2, amount=3)
sheet.insert_rows(idx=2) 2行上边插入一行。 sheet.insert_cols(idx=2, amount=3) 2行上边插入三行。

（5）删除行或列

delete_cols(idx=数字编号,amount=要删除的列数) 从idx这一列开始，包括idx这一列。
sheet.delete_cols(idx=2)
delete_rows(idx=数字编号,amount=要删除的行数) 从idx这一行开始，包括idx这一行。
sheet.delete_rows(idx=2)
执行后上面的恢复。

（6）移动单元格

move_range(‘C1:D4’,rows=2,cols=-2) 正整数为向下或者向右、负整数表示向左或者向上。
sheet.move_range(‘C1:D4’,rows=2,cols=-2)

（7）冻结窗格

sheet.freeze_panes = ‘B2’

（8）添加筛选功能

# 首行添加筛选标签
sheet.auto_filter.ref = sheet.dimensions
# 制定区域添加筛选标签
sheet.auto_filter.ref = "A2:B2"

（9）调整字体样式

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

from openpyxl.styles import Font
from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = Font(name='思源黑体 Regular', size=12, bold=True, italic=True, color='FF0000')
cell.font = font
workbook.save(filename='test.xlsx')

获取单元格中字体的样式：

font = cell.font
print(font.name, font.size, font.bold, font.italic)

（10）调整对齐样式

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

from openpyxl.styles import Alignment
from openpyxl import load_workbook

sheet = workbook.active
cell = sheet['A2']
alignment = Alignment(horizontal='center', vertical='center', text_rotation=45)
cell.alignment = alignment

workbook.save(filename='test.xlsx')

（11）调整边框样式

Side(style=边线样式,color=边线颜色) Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)

from openpyxl.styles import Side, Border
from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')
sheet = workbook.active
cell = sheet['A4']
side = Side(style='thin', color='FF000000')
border = Border(left=side, right=side, top=side, bottom=side)
cell.border = border
workbook.save(filename='test.xlsx')

（12）调整填充样式

PatternFill(file_type=填充样式,fgColor=填充颜色) GradientFill(stop=(渐变颜色1,渐变颜色2,……))

from openpyxl.styles import PatternFill, GradientFill
from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')
sheet = workbook.active
cell_a1 = sheet['A1']
pattern_fill = PatternFill(fill_type='solid', fgColor='99ccff')
cell_a1.fill = pattern_fill
cell_a2 = sheet['A2']
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff', '000000'))
cell_a2.fill = gradient_fill
workbook.save(filename='test.xlsx')

（13）设置行高和列宽

from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')
sheet = workbook.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['B'].width = 20
workbook.save(filename='test.xlsx')

（14）合并单元格

from openpyxl import load_workbook

workbook = load_workbook(filename='test.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='test.xlsx')

（15）取消合并单元格

from openpyxl import load_workbook

workbook = load_workbook(filename='test.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='test.xlsx')

（16）插入图片

from openpyxl.drawing.image import Image
from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')
sheet = workbook.active

my_picture = Image('图片.png')
my_picture.height = 100
my_picture.width = 100

sheet.add_image(my_picture, 'C1')
workbook.save(filename='test.xlsx')

（17）插入柱状图

from openpyxl.chart import BarChart, Reference
from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')
sheet = workbook.active
# 新建一个柱状图
chart = BarChart()
# 设定数据引用范围
data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)
# 设定x轴项目的名称，min_col标识x轴项目所在列
categories = Reference(sheet, min_col=1, min_row=2, max_row=7)
# 给柱状图添加数据，titles_from_data为True说明数据源有标题
chart.add_data(data, titles_from_data=True)
# 设定x轴类目
chart.set_categories(categories)
sheet.add_chart(chart, 'G5')

workbook.save(filename='test.xlsx')

（18）插入折线图

from openpyxl.chart import LineChart, Reference
from openpyxl import load_workbook

workbook = load_workbook(filename='test.xlsx')
sheet = workbook.active

chart = LineChart()
data = Reference(worksheet=sheet, min_row=20, max_row=21, min_col=1, max_col=13)
# 设定x轴项目的名称，min_row标识x轴项目所在行
categories = Reference(sheet, min_row=19, min_col=2, max_col=13)
# 横向
chart.add_data(data, from_rows=True, titles_from_data=True)
chart.set_categories(categories)
sheet.add_chart(chart, 'B25')

workbook.save(filename='test.xlsx')
赏

谢谢你请我吃糖果

微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

👩‍💻本人程序员一枚！<br> 🏢国企待过，侥幸参与国标制定✨<br> 💼私企混过，荣获“反内卷达人”称号<br> 😴目前对打工的日常失去了兴趣😴<br> 🔍作为编程路上的探索者<br> 📚日常中分享技术干货📚<br> 🤝与志同道合的你共同进步🤝<br> 🙏感谢你的支持与关注！💖<br>