python读取excel

openpyxl 库既可以读文件、也可以写文件、修改文件

安装该库

pip install openpyxl

参考文档英文

参考文档中文

openpyxl 库不支持老版本 Office2003 的 xls 格式的Excel文档，如果要读写xls格式的文档，

方法一：手动打开xls文件，然后另存为xlsx类型的文件

方法二：xls==>xlsx，使用pywin32模块进行转换，示例代码如下

import os
import win32com.client as win32

filename = r'C:\Users\XH\Desktop\1.xls'
Excelapp = win32.gencache.EnsureDispatch('Excel.Application')
workbook = Excelapp.Workbooks.Open(filename)

# 转xlsx时: FileFormat=51,
workbook.SaveAs(filename.replace('xls', 'xlsx'), FileFormat=51)
# 如果想将xlsx的文件转换为xls的话，则可以使用以下的代码：
# workbook.SaveAs(filename.replace('xlsx', 'xls'), FileFormat=56)
workbook.Close() 
Excelapp.Application.Quit()

# 删除源文件
# os.remove(filename)

 

基础示例

import openpyxl

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()

# 创建时，会自动产生一个sheet，通过active获取
sh = book.active

# 修改当前 sheet 标题为 工资表
sh.title = '工资表'

# 保存文件
book.save('信息.xlsx')

# 增加一个名为 '年龄表' 的sheet，放在最后
sh1 = book.create_sheet('年龄表-最后')

# 增加一个 sheet，放在最前
sh2 = book.create_sheet('年龄表-最前',0)

# 增加一个 sheet，指定为第2个表单
sh3 = book.create_sheet('年龄表2',1)

# 根据名称获取某个sheet对象
sh = book['工资表']
# 获取sheet页方式二
# sh = book.get_sheet_by_name('工资表')
# 获取所有sheet页
# sh = book.sheetnames

# 给第一个单元格写入内容
sh['A1'] = '你好'

# 获取某个单元格内容
print(sh['A1'].value)
# print(book.cell(row=1,column=1).value)

# 根据行号列号， 给第一个单元格写入内容，
# 注意和 xlrd 不同，是从 1 开始
sh.cell(2,2).value = '天才九少'

# 根据行号列号， 获取某个单元格内容
print(sh.cell(1, 1).value)

book.save('信息.xlsx')

新建Excel，写入数据

写入字典数据

import openpyxl

name2Age = {
    '张飞' :  38,
    '赵云' :  27,
    '许褚' :  36,
    '典韦' :  38,
    '关羽' :  39,
    '黄忠' :  49,
    '徐晃' :  43,
    '马超' :  23,
}

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()

# 创建时，会自动产生一个sheet，通过active获取
sh = book.active

sh.title = '年龄表'

# 写标题栏
sh['A1'] =  '姓名'
# sh.cell(row=1, column=1) = '姓名'
sh['B1'] =  '年龄'

# 写入内容
row = 2

for name,age in name2Age.items():
    sh.cell(row, 1).value = name
    sh.cell(row, 2).value = age
    row += 1

# 保存文件
book.save('信息.xlsx')

写入列表/元组数据

import openpyxl

name2Age = [
    ['张飞' ,  38 ] ,
    ['赵云' ,  27 ] ,
    ['许褚' ,  36 ] ,
    ['典韦' ,  38 ] ,
    ['关羽' ,  39 ] ,
    ['黄忠' ,  49 ] ,
    ['徐晃' ,  43 ] ,
    ['马超' ,  23 ]
]

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()
sh = book.active
sh.title = '年龄表'

# 写标题栏
sh['A1'] =  '姓名'
sh['B1'] =  '年龄'

for row in name2Age:　　# 使用append方法在sheet的末尾添加新行
    # 添加到下一行的数据
    sh.append(row)

# 保存文件
book.save('信息.xlsx')

单元格获取

 

# 访问A1至C3范围单元格
cell_range = ws2['A1':'C3']　　# 切片方式

# 访问A列所有存在数据的单元格
colA = ws2['A']

# 访问A列到C列所有存在数据的单元格
col_range = ws2['A:C']

# 访问第1行所有存在数据的单元格
row1 = ws2[1]

# 访问第1行至第5行所有存在数据的单元格
row_range = ws2[1:5]

for each_cell in cell_range:　　# 遍历 元组cell_range,colA
    for each in each_cell:
     print(each.value)

for each_cell in colA:
    print(each_cell.value)

 

 

修改数据

• 修改单元格内容
• 插入行/列
• 删除行/列
• 样式风格
• 背景色
• 插入图片

修改单元格内容

import openpyxl

# 加载 excel 文件
wb = openpyxl.load_workbook('income.xlsx')

# 得到sheet对象
sheet = wb['2017']

sheet['A1'] = '修改一下'

## 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')

插入行/列

sheet 对象的 inset_rows 和 insert_cols 方法，分别用来插入  行 和 列 


import openpyxl

# 加载工作簿
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 在第2行的位置插入1行
sheet.insert_rows(2)

# 在第3行的位置插入3行
sheet.insert_rows(3,3)

# 在第2列的位置插入1列
sheet.insert_cols(2)

# 在第2列的位置插入3列
sheet.insert_cols(2,3)

## 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')

删除行/列

sheet 对象的delete_rows 和 delete_cols方法，分别用来删除 行 和 列


import openpyxl

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 在第2行的位置删除1行
sheet.delete_rows(2)

# 在第3行的位置删除3行
sheet.delete_rows(3,3)

# 在第2列的位置删除1列
sheet.delete_cols(2)

# 在第3列的位置删除3列
sheet.delete_cols(3,3)

## 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')

样式风格

单元格里面的样式风格（包括 颜色、字体、大小、下划线 等） 都是通过 Font对象设定的

import openpyxl
# 导入Font对象 和 colors 颜色常量
from openpyxl.styles import Font,colors

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 指定单元格字体颜色，
sheet['A1'].font = Font(color=colors.RED, #使用预置的颜色常量
                        size=15,    # 设定文字大小
                        bold=True,  # 设定为粗体
                        italic=True # 设定为斜体
                        )

# 也可以使用RGB数字表示的颜色
sheet['B1'].font = Font(color="981818")

# 指定整行 字体风格， 这里指定的是第3行
font = Font(color="981818")
for y in range(1, 100): # 第 1 到 100 列
    sheet.cell(row=3, column=y).font = font

# 指定整列 字体风格， 这里指定的是第2列
font = Font(bold=True)
for x in range(1, 100): # 第 1 到 100 行
    sheet.cell(row=x, column=2).font = font

wb.save('income-1.xlsx')

背景色

import openpyxl
# 导入Font对象 和 colors 颜色常量
from openpyxl.styles import PatternFill

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 指定 某个单元格背景色
sheet['A1'].fill = PatternFill("solid", "E39191")

# 指定 整行 背景色， 这里指定的是第2行
fill = PatternFill("solid", "E39191")
for y in range(1, 100): # 第 1 到 100 列
    sheet.cell(row=2, column=y).fill = fill

wb.save('income-1.xlsx')

插入图片

import openpyxl
from openpyxl.drawing.image import Image

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 在第1行，第4列 的位置插入图片
sheet.add_image(Image('1.png'), 'D1')

## 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')

 

参考学习：

https://www.byhy.net/tut/py/extra/excel/

https://zhuanlan.zhihu.com/p/342422919