python表格模块

Python表格相关模块

xlwt

  xls_write缩写？这是供开发人员用来生成与Microsoft Excel版本95至2003兼容的电子表格文件的库。包本身是纯Python，不依赖于标准Python发行版之外的模块或包。

参考官方文档：https://xlwt.readthedocs.io/en/latest/

1、安装

pip install xlwt

2、基本语法

# 实例一个Workbook对象
wk = xlwt.Workbook()
# 增加一个sheet
sheet = wk.add_sheet('sheet1')
# 在sheet1工作簿的第1行，第1列，写入test
sheet.write(0,0,'test')
# 保存数据
wk.save('test.xls')

3、简单使用

import xlwt
from datetime import datetime as dt

# 可以写成.xlsx文件，但是实际上还是.xls文件
f = 'test.xlsx'
# 实例一个Workbook对象
wk = xlwt.Workbook()
# 增加一个sheet
sheet = wk.add_sheet('sheet1')

user_list=['张三','李四','王五']

# write 参数:行，列，值,样式(可选)。行列从0开始
# style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
#   num_format_str='#,##0.00')
# style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
style = xlwt.easyxf(num_format_str='YYYYMM')
sheet.write(0,0,'用户id')
sheet.write(0,1,'用户名称')
sheet.write(0,2,'创建时间')

for user in range(len(user_list)):
    sheet.write(user+1,0,user+1)
    sheet.write(user+1,1,user_list[user])
    # 约等于 sheet.write(user+1,2,int(dt.now().strftime('%Y%m')))
    sheet.write(user+1,2,dt.now(),style)

wk.save(f)

# 表格数据
# 用户id	用户名称	创建时间
# 1		  张三	   202101
# 2		  李四	   202101
# 3		  王五	   202101

4、总结

  优点：大道至简

  缺点：没有修改功能；没有删除行、列功能；xlwt只能创建一个全新的excel文件, 然后对这个文件进行写入内容以及保存，不能追加写入，xlutils模块提供了方案copy实现追加。

xlrd

  xls_read缩写？xlrd是一个用于以.xls格式从Excel文件读取数据和格式化信息的库。xlwt生成的.xlsx是可以处理的。实际还是只处理.xls格式的

参考官方文档： https://xlrd.readthedocs.io/en/latest/

1、安装

pip install xlrd

2、基本语法

import xlrd

f='test.xlsx'
# 获取文件对象
execl = xlrd.open_workbook(f)
# 获取工作簿对象
sheet1 = execl.sheets()[0]
sheet2 = execl.sheet_by_index(0)
sheet3 = execl.sheet_by_name('sheet1')
print(sheet1 == sheet2 == sheet3)
# 获取最大行数、列数
print(sheet1.nrows)
print(sheet1.ncols)

# 获取第1行，返回list
print(sheet1.row_values(rowx=0))
# rowx可以省略不写
print(type(sheet1.row_values(0)))
# 获取第1行，[start_colx,end_colx)左闭右开，可以理解成合并单元格
print(sheet1.row_values(rowx=0,start_colx=0,end_colx=3))

# 获取第1列，返回list
print(sheet1.col_values(colx=0))
# colx可以省略不写
print(type(sheet1.col_values(0)))
# 获取第1列，[start_rowx,end_rowx)左闭右开，可以理解成合并单元格
print(sheet1.col_values(colx=0, start_rowx=0, end_rowx=3))

# 获取单元格第1行 第1列
print(sheet1.cell(rowx=0, colx=0))
# rowx和colx可以省略不写
print(sheet1.cell_value(0, 0))
# 单元类型type：empty为0，string为1，number为2，date为3，boolean为4，error为5
print(sheet1.cell_type(0, 0))

3、简单使用

# 表格数据使用xlwt生成的数据
# 用户id	用户名称	创建时间
# 1		  张三	   202101
# 2		  李四	   202101
# 3		  王五	   202101
# 遍历表格，获取用户的创建时间
import xlrd

f='test.xlsx'
# 获取文件
execl = xlrd.open_workbook(f)

# 获取工作簿
sheet = execl.sheets()[0]

# 获取最大行列
max_row = sheet.nrows
max_col = sheet.ncols

def getCreateDate(name):
    for i in range(max_row):
        for j in range(max_col):
            if sheet.cell_value(i, j)==name:
                return sheet.cell_value(i, j+1)

if __name__ == '__main__':
    dates =getCreateDate('张三')
    # 打印结果 2021-01-06 17:29:34
    print(xlrd.xldate_as_datetime(dates, 0))

  xlrd提供了两种获取时间的方法xldate_as_datetime、xldate_as_tuple，去看下源码就可以看到参数含义了，使用1900-based默认是当下时间，1904-based会把时间延后4年。这个datemode的设计有点耐人寻味。。。

def xldate_as_datetime(xldate, datemode):
    """
    Convert an Excel date/time number into a :class:`datetime.datetime` object.

    :param xldate: The Excel number
    :param datemode: 0: 1900-based, 1: 1904-based.

    :returns: A :class:`datetime.datetime` object.
    """
def xldate_as_tuple(xldate, datemode):
    """
    Convert an Excel number (presumed to represent a date, a datetime or a time) into
    a tuple suitable for feeding to datetime or mx.DateTime constructors.

    :param xldate: The Excel number
    :param datemode: 0: 1900-based, 1: 1904-based.
    :raises xlrd.xldate.XLDateNegative:
    :raises xlrd.xldate.XLDateAmbiguous:

    :raises xlrd.xldate.XLDateTooLarge:
    :raises xlrd.xldate.XLDateBadDatemode:
    :raises xlrd.xldate.XLDateError:
    :returns: Gregorian ``(year, month, day, hour, minute, nearest_second)``.
    """

4、总结

  优点：大道至简

  缺点：无法处理xlsx格式数据；同时处理openpyxl生成的表格，会报错，也不能处理。

xlutils

  xlutils模块是一个xlwt、xlrd的工具包，是一个拓展模块，增加一些额外的方法，完善表格操作。通常和xlrd、xlwt模块配合使用。

参考官方文档：https://xlutils.readthedocs.io/en/latest/copy.html

1、安装

pip install xlutils

2、基本语法

(1)xlutils.copy

from xlutils.copy import copy
from xlrd import open_workbook
# 先准备一个test.xlsx
# on_demand可以节省内存，formatting_info读取文件格式，包括背景色，字体等信息
rb = open_workbook('test.xlsx', on_demand=True,formatting_info=True)
# 复制一个新的工作表对象
wb = copy(rb)
wb.get_sheet(0).write(0,0,'changed!')
wb.save('output.xlsx')

(2)xlutils.display

  官方介绍：该模块包含quoted_sheet_name()和 cell_display()功能，可轻松安全地显示所返回的信息xlrd。好像没什么实际用处。

import xlutils.display
from xlrd import open_workbook

# on_demand可以节省内存，formatting_info读取文件格式信息，包括背景色，字体等信息
rb = open_workbook('test.xlsx', on_demand=True,formatting_info=True)
print(rb.sheet_by_index(0).cell(0, 0).value)

# 写入的时候要指定编码格式
sheet = rb.sheet_by_index(0)
cell = sheet.cell(0, 0)
print(xlutils.display.cell_display(cell,encoding='utf8'))
print(type(xlutils.display.cell_display(cell,encoding='utf8')))
print(xlutils.display.quoted_sheet_name(sheet))
print(type(xlutils.display.quoted_sheet_name(sheet)))

(3)其他

  xlutils包的很多方法需要继承基础类，重写方法，造好轮子后还要改造轮子有点不符合python设计理念，另一方面其他的表格模块太强大了，因此在实际中运用很少。比如，xlutils.filter：使用许多模块化的读取器，过滤器和写入器来过滤和拆分Excel文件，这些读取器，过滤器和写入器均通过该process()功能捆绑在一起。

3、总结

  优点：xlwt、xlrd、xlutils是属于老牌python模块，为后面的表格模块提供了改进方案。

  缺点：只支持xls格式

openpyxl

  包含读写库，所有的功能比较完善，支持Panda和NumPy。但是与其他库和应用程序相比，内存使用率相当高。官方给出的数据：内存使用情况大约是原始文件大小的50倍，例如50 MB Excel文件为2.5 GB。

参考官方文档：https://openpyxl.readthedocs.io/en/stable/

1、安装

pip install openpyxl

2、基本语法

(1)工作簿：

import openpyxl

# 获取工作表workbook对象
wb = openpyxl.Workbook()
# 默认情况下设置为0。除非修改其值，否则始终将使用此方法获得第一个工作表。
sheet = wb.active
# 新建一个sheet，参数：title，index（default：依次在后面插入，0：新建表在第1个，-1：新建表在倒数第2个）
sheet2 = wb.create_sheet('sheet2'，1)
# copy工作簿
copy_sheet = wb.copy_worksheet(sheet)
# 获取所有工作簿
print(wb.sheetnames)
# 遍历工作表
for sheet in wb:
    print(sheet.title)

(2)数据操作

# 给单元格赋值，参数：行、列、值。行列的下标从1开始
sheet.cell(1,1,'openpyxl')
# 修改单元格
sheet['A1'] = 'openpyxl2'
# 读取单元格的值
c = sheet['A1'].value
print(c)
# 删除行列，参数：要删除的行列
# 删除第2行
sheet.delete_rows(2)
# 删除第1列
sheet.delete_cols(1)

# 以下为测试的表格数据
# id	name		age
# 1		zhangsan	14
# 2		lisi		15

# 获取行列,返回值都是元组<class 'tuple'>
colA = sheet['A']
col_range = sheet['A:C']
row1 = sheet[1]
row_range = sheet[1:3]
cell_range = sheet['A1':'C3']
# 遍历元组，获取值
for i in range(len(colA)):
    print(colA[i].value)


# 通过Worksheet.iter_rows()方法返回值是生成器对象<class 'generator'>
# 可以省略参数名称sheet.iter_rows(1,2,2,3)
for row in sheet.iter_rows(min_row=1,max_row=2, min_col=2,max_col=3):
    for cell in row:
        print(cell.value)
# 结果
name
age
zhangsan
14

# 类似的有Worksheet.iter_cols()
# 出于性能原因，该Worksheet.iter_cols()方法在只读模式下不可用。
for col in sheet.iter_cols(min_row=1,max_row=2, min_col=2,max_col=3):
    for cell in col:
        print(cell.value)
# 结果
name
zhangsan
age
14

# 官方推荐使用Worksheet.rows，Worksheet.columns转化成tuple去遍历
rows = tuple(sheet.rows)
print(rows)
# ((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>), (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>), (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>))
cols = tuple(sheet.columns)
# 遍历取值
for i in range(len(rows)):
    for j in range(len(cols)):
        print(rows[i][j].value)

# 等价于下面这个遍历取值
# 遍历整表，获取value
for row in sheet.values:
    for value in row:
       print(value)
# 结果
id
name
age
1
zhangsan
14
2
lisi
15

(3)数据存储

wb.save('test.xlsx')

(4)数据读取

wb2 = openpyxl.load_workbook('test.xlsx')

3、总结

  优点：读写一体，切片定位单元格方便。

  缺点：内存占用比较高

XlsxWriter

  比较推荐的模块，对比其他模块，XlsxWriter的官方文档比较详细，就不翻译了。

优点：具有丰富的写入功能，

缺点：不能打开/修改已有文件；有且只有写入功能，没有读取表格的功能。

参考官方文档：https://xlsxwriter.readthedocs.io/

win32com

好像没什么文档，不过最后的推荐网站有案例。

xlwings

参考官方文档：https://docs.xlwings.org/en/stable/

推荐一个网站，python模块使用的例子：

https://www.programcreek.com/python/