简介

在实际项目中,我们有时候会需要对Excel文件进行处理,python中有很多模块都可以对Excel文件进行相应处理,本文将对python的相关库做一下比较,如下是处理Excel文件的模块对比
avatar

xlrd

是python的常用的读取excel的模块,如果只有读取excel数据时,推荐使用,速度较快,另外,excel只兼容xls。

安装

pip install xlrd

常用的表格中数据类型

empty(空的)
string(text)
number
date
boolean
error
blank(空白表格)

测试的表格数据

avatar

常用的xlrd中的方法及属性

注意所有以索引获取的索引都是从0开始。

import os

excel_path = os.path.join(os.path.dirname(__file__), '**.xlsx')
# xlrd
import xlrd
from xlrd import Book

data:Book = xlrd.open_workbook(excel_path)
# 对于工作表的操作
table = data.sheets()[0]
print('使用sheets获取第一个工作表对象:', table)
table = data.sheet_by_index(1)
print('根据索引获取工作表对象:', table)
table = data.sheet_by_name('范例1')
print('根据工作表名称获取工作表对象:', table)
table_name = data.sheet_names()
print('工作表名称列表:', table_name)
# 根据index判断
print('第一个工作表是否导入完成:', data.sheet_loaded(0))
# 根据表名称判断
print('第二个工作表是否导入完成:', data.sheet_loaded('范例2'))
# 对于行的操作, 此处以第一个范例为示例
print('工作表的行数:', table.nrows)
print('工作表第一行数据为(包含表格中数据类型):',table.row(0))
print('工作表中所有行有效数据中的最大长度(不管选择的是哪个):', table.row_len(0))
print('工作表第一行数据(直接显示对应数据):', table.row_values(0))
print('工作表中对应的数据类型,empty为0,其余不为0', table.row_types(0))
print('工作表中第一行数据,第一列到第二列的数据:', table.row_slice(0, start_colx=0, end_colx=2))
# 对于列的操作,此处以第一个范例为示例
print('工作表的列数:',table.ncols)
print('工作表的第一列数据(包含类型):',table.col(0))
print('工作表中第一列数据(不包含类型):', table.col_values(0))
print('工作表中第一列数据的类型,empty为0,其余不为0:', table.col_types(0))
print('工作表中第一列数据,第一行到第二行的数据:',table.col_slice(0, start_rowx=0, end_rowx=2))
# 对于单元格的操作
print('工作表第二行第二列的单元格对象:', table.cell(1,1))
print('工作表第二行第二列的单元格数据类型:', table.cell_type(1,1))
print('工作表第二行第二列的单元格数据:', table.cell_value(1,1))
使用sheets获取第一个工作表对象: <xlrd.sheet.Sheet object at 0x7f2826d2a278>
根据索引获取工作表对象: <xlrd.sheet.Sheet object at 0x7f2826d2a2b0>
根据工作表名称获取工作表对象: <xlrd.sheet.Sheet object at 0x7f2826d2a278>
工作表名称列表: ['范例1', '范例2']
第一个工作表是否导入完成: True
第二个工作表是否导入完成: True
工作表的行数: 28
工作表第一行数据为(包含表格中数据类型): [number:1.0, number:2.0, number:3.0, number:4.0, number:5.0, number:6.0, number:7.0, number:8.0, number:9.0, number:10.0]
工作表中所有行有效数据中的最大长度(不管选择的是哪个): 10
工作表第一行数据(直接显示对应数据): [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0]
工作表中对应的数据类型,empty为0,其余不为0 array('B', [2, 2, 2, 2, 2, 2, 2, 2, 2, 2])
工作表中第一行数据,第一列到第二列的数据: [number:1.0, number:2.0]
工作表的列数: 10
工作表的第一列数据(包含类型): [number:1.0, number:2.0, number:3.0, number:4.0, number:5.0, number:6.0, number:7.0, number:8.0, number:9.0, number:10.0, number:11.0, number:12.0, number:13.0, number:14.0, number:15.0, number:16.0, number:17.0, number:18.0, number:19.0, number:20.0, number:21.0, number:22.0, number:23.0, number:24.0, number:25.0, number:26.0, number:27.0, number:28.0]
工作表中第一列数据(不包含类型): [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0, 22.0, 23.0, 24.0, 25.0, 26.0, 27.0, 28.0]
工作表中第一列数据的类型,empty为0,其余不为0: [2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2]
工作表中第一列数据,第一行到第二行的数据: [number:1.0, number:2.0]
工作表第二行第二列的单元格对象: number:3.0
工作表第二行第二列的单元格数据类型: 2
工作表第二行第二列的单元格数据: 3.0

官方文档:xlrd官方文档

xlwt

xlwt用于新增表格及原有表格数据的修改,速度较快,如果涉及新增或者修改,推荐使用

安装

pip install xlwt

常用方法及属性

import xlwt, os
from xlwt import Worksheet

# excel_path = os.path.join(os.path.dirname(__file__), 't.xlsx')
excel_path = os.path.join(os.path.dirname(__file__), 't.xls')

workBook = xlwt.Workbook()
# 新建工作表1
workSheet:Worksheet = workBook.add_sheet('工作表1')

# 设置样式, 可以添加到对应单元格中
style = xlwt.XFStyle()

# 设置字体样式
font = xlwt.Font()
# 设置字体加粗
font.bold = True
# 设置字体下划线
font.underline = True
# 设置字体斜体
font.italic = True
# 设置字体类型
font.name = 'Times New Roman'

# 设置边框样式
# DASHED:虚线
# NO_LINE 没有线
# THIN:实线
borders = xlwt.Borders() 
# 设置边框样式
borders.left = xlwt.Borders.DASHED
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
# 设置边框颜色
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40

# 设置单元格样式
pattern = xlwt.Pattern()
# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow,
# 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow ,
# almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
pattern.pattern_fore_colour = 5

# 设置单元格对齐
alignment = xlwt.Alignment()
# VERT_TOP = 0x00       上端对齐
# VERT_CENTER = 0x01    居中对齐(垂直方向上)
# VERT_BOTTOM = 0x02    低端对齐
# HORZ_LEFT = 0x01      左端对齐
# HORZ_CENTER = 0x02    居中对齐(水平方向上)
# HORZ_RIGHT = 0x03     右端对齐
alignment.horz = 0x02  # 设置水平居中
alignment.vert = 0x01  # 设置垂直居中

style.font = font
style.borders = borders
style.pattern = pattern
style.alignment = alignment

# 在工作表的第一行第一列写入test
workSheet.write(0, 0, 'test')

# 设置列宽,20个字符
"""
xlwt中列宽的值表示方法:默认字体0的1/256为衡量单位。
xlwt创建时使用的默认宽度为2960,既11个字符0的宽度
所以我们在设置列宽时可以用如下方法:
width = 256 * 20 256为衡量单位,20表示20个字符宽度
"""
workSheet.col(0).width =  256 * 20

# 设置行高
"""
在xlwt中设置行高,只能通过设置单元格的样式实现
"""
height_style = xlwt.easyxf('font:height 360;')  # 18pt,类型小初的字号
row = workSheet.row(0)
row.set_style(height_style)

# 在工作表的第一行第二列写入test2
workSheet.write(0, 1, 'test2', style)

# 在工作表中合并单元格和行,参数依次为开始行数,结束行数,开始列数,结束列数,此处为第二行到第三行,第二列到第三列
workSheet.write_merge(1,2,1,2,'merge_test')



# 保存excel
workBook.save(excel_path)

官方文档:xlwt官方文档

xlutils

与xlwt类似,都是用于excel表格文件内容的新增与修改

安装

pip install xlutils

常用方法与属性

略,由于xlutils仍需要xlrd配合,不推荐使用
官方文档:xlutils官方文档

xlwings

注意事项:

传统上,xlwings需要安装Excel,因此只能在Windows和macOS上运行。请注意,macOS当前不支持UDF。

兼顾了xlrd和xlwt的读写excel数据的功能,并且支持的更为全面,有如下特点:

  • xlwings能够非常方便的读写Excel文件中的数据,并且能够进行单元格格式的修改
  • 可以和matplotlib以及pandas无缝连接,支持读写numpy、pandas数据类型,将matplotlib可视化图表导入到excel中。
  • 可以调用Excel文件中VBA写好的程序,也可以让VBA调用用Python写的程序。
  • 开源免费,一直在更新
    avatar

安装

pip install xlwings

由于目前电脑为Linux且Windows没有excel,暂时不做介绍
官网地址:https://www.xlwings.org/
官方文档:https://docs.xlwings.org/en/stable/api.html

openpyxl

在openpyxl中,主要用到三个概念:Workbooks,Sheets,Cells。

Workbook就是一个excel工作表;
Sheet是工作表中的一张表页;
Cell就是简单的一个格。
openpyxl就是围绕着这三个概念进行的,不管读写都是“三板斧”:打开Workbook,定位Sheet,操作Cell。

安装

pip install openpyxl

注意事项

支持流行的lxml库,如果安装了该库,将使用该库。这在创建大型文件时特别有用。
为了能够将图像(jpeg、png、bmp…)包含到openpyxl文件中,您还需要“枕头”库,该库可以安装:
pip install pillow

新建文档及常用方法及属性

from openpyxl import Workbook
import datetime, os


new_excel_path = os.path.join(os.path.dirname(__file__), 'new_test.xlsx')

# 新建文件
wb = Workbook()
# 激活工作表
ws = wb.active

# 创建sheet
## 插入sheet到最后
ws_sheet = wb.create_sheet('sheet1')
## 插入sheet到最开始的位置, 可以指定插入到第几个位置
wb.create_sheet('sheet2', 0)

# 删除sheet:Use wb.remove(worksheet) or del wb[sheetname]
## 由于创建表格时会有一个默认的Sheet,因此可以先手动删除再做操作,默认的wb对象指向Sheet
del wb['Sheet']

# 写入数据
## 数据可以直接分配到单元格,并且支持输入公式
ws_sheet['A1'] = 42
## 数据依次填写表格中数据,一次为一行
ws_sheet.append([1,2,3])
## 支持python类型自动转换
ws_sheet['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")

# 设置单元格的风格
from openpyxl.styles import Font, colors, Alignment
# 设置字体, 此处为字体为等线、大小24、斜体、加粗、颜色为蓝色
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.BLUE, bold=True)
ws_sheet['A1'].font = bold_itatic_24_font
# 设置对齐方式,设置垂直水平居中,详细参数
"""
horizontal_alignments = (
    "general", "left", "center", "right", "fill", "justify", "centerContinuous",
    "distributed", )
vertical_aligments = (
    "top", "center", "bottom", "justify", "distributed",
)
"""
ws_sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')
# 设置行高、列宽
## 设置第二行行高为40
ws_sheet.row_dimensions[2].height = 40
## 设置第A列列宽
ws_sheet.column_dimensions['A'].width = 40

# 合并单元格,与excel软件一至,合并单元格如果存在数据只会保留左上角的数据
ws_sheet.merge_cells('A3:C3')
ws_sheet.merge_cells('D2:F4')

# 拆分单元格
ws_sheet.unmerge_cells('D2:F4')

# 保存数据
wb.save(new_excel_path)

读取原有文档及常用方法及属性

import os
from openpyxl import load_workbook
excel_path = os.path.join(os.path.dirname(__file__), 'test.xlsx')
# 打开已有文件
wb = load_workbook(excel_path)

# 选择表
ws2 = wb['范例1']
ws3 = wb.get_sheet_by_name('范例1')
print(ws2 is ws3)

print('-------------------------------')

# 访问单元格/获取单元格,默认都是以cell返回
# 单个单元格
## 直接通过索引的方式
print(ws2['A1'])
## 通过cell的行列获取单元格数据, 此方法也可以修改数据
d = ws2.cell(row=1,column=1,value=10)
print(d.value)
## 访问当前不存在的单元格,会自动创建对应的单元格对象
for i in range(1,100):
    for j in range(1,100):
        ws2.cell(row=i, column=j)
# 多个单元格
## 通过切片, 默认从左上角到右下角
data = ws2['A1':'C3']
print(data)
print(type(data))
for item in data:
    for i in item:
        print(i.value, end=',')
print()
## 通过行(列),一般获取的是整行整列数据
### 按照行,获取的是整行数据
print('获取C行数据:', ws2['C'])
print('获取C到D行数据:', ws2['C:D'])
### 按照列,获取的是整列数据
print('获取第10列的数据:', ws2[10])
print('获取6-10列数据:', ws2[6:10])
# 指定范围行列
## iter_rows与iter_cols功能类似,只不过遍历的顺序相反而已
## 从第一行、第三行到第一列、第三列,优先遍历列,再遍历行 
for item in ws2.iter_rows(1,3,1,3):
    for cell in item:
        print(cell, end='')
print()
## 从第一行、第三行到第一列、第三列,优先遍历行,再遍历列
for item in ws2.iter_cols(1,3,1,3):
    for cell in item:
        print(cell, end='')
print()
# 遍历所有行或者列
print(tuple(ws2.rows))
print(tuple(ws2.columns))


print('-------------------------------')

# 修改单元格数据
## 访问第一行第一列的数据并修改为10
d = ws2.cell(row=1,column=1,value=10)
print(d.value)

print('-------------------------------')
# 修改sheet按钮的颜色
ws2.sheet_properties.tabColor = '1072BA'

print('-------------------------------')

# 获取表格的所有sheet名称
print(wb.sheetnames)
# 遍历获取表格的所有sheet名称
for item in wb:
    print(item.title)

print('-------------------------------')

# 获取表格最大行和最大列
print('最大行为:', ws2.max_row)
print('最大列为:', ws2.max_column)

print('-------------------------------')

# 保存数据
wb.save(excel_path)

其他常用方法

from openpyxl.utils import get_column_letter, column_index_from_string

# 根据列的数字获得列对应的字母
print(get_column_letter(4))
# 根据列的字母获取对应的数字
print(column_index_from_string('E'))

生成2d图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)
ws = wb.create_sheet()

rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]

for row in rows:
    ws.append(row)

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

from copy import deepcopy

chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")

chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")

chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "G27")

wb.save("bar.xlsx")

avatar

3D图表

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    Series,
    BarChart3D,
)

wb = Workbook()
ws = wb.active

rows = [
    (None, 2013, 2014),
    ("Apples", 5, 4),
    ("Oranges", 6, 2),
    ("Pears", 8, 3)
]

for row in rows:
    ws.append(row)

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)

ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")

avatar
官方文档:https://openpyxl.readthedocs.io/en/stable/

xlswriter

安装

pip install XlsxWriter

持续更新中

win32com

pandas

参考

excel表格的python处理

版权声明:本文为xy-bot原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/xy-bot/p/16320973.html