大数据让Excel效率起飞的5个Python库

作者:吊车尾学院一木木老师更新于： 2021-02-28 21:22:34

超强盘点！让Excel效率起飞的5个Python库。对于“大数据”（Big data）研究机构Gartner给出了这样的定义。“大数据”是需要新处理模式才能具有更强的决策力、洞察发现力和流程优化能力来适应海量、高增长率和多样化的信息资产。

Excel作为Office的数据处理软件，我们几乎每天都在使用。虽然好用，但在大量录入、处理数据的时候，效率未免有点低。因此，很多学了Python的同学，会利用Python的第三方库来批量操作Excel，提升效率。

大数据让Excel效率起飞的5个Python库_大数据_数据分析_数据结构_课课家

Python有很多支持操作Excel的第三方库，今天推荐的5个库，看看它们是如何让Excel效率起飞的!

Xlwings

Xlwings是非常强大的处理Excel的库，无论是Windows还是Mac，Excel还是WPS，都可以使用。

它功能非常齐全，能十分方便地新建、打开、修改、保存Excel，可以和matplotlib、numpy以及pandas无缝连接，支持读写numpy、pandas数据类型，将matplotlib可视化图表导入到excel中。另外，还可以调用Excel文件中VBA写好的程序，也可以让VBA调用Python写的程序。

import xlwings as xw #导入库
app = xw.App(visible=True,add_book=False)
wb = app.books.add() #打开Excel程序
wb = xw.Book('example.xlsx') #打开已有工作簿
wb.save('example.xlsx') #保存工作簿
wb.close() #退出工作簿（可省略）
app.quit() #退出Excel
sht = wb.sheets[0] #引用工作表，括号内是第一个sheet名
rng = sht.range('a1')
#rng = sht['a1'] #引用单元格，第一行的第一列即a1
rng = sht.range('a1:a5') #引用区域
sht.range('a1').value = 'Hello' #单元格A1,写入字符串‘Hello’
sht.range('a1').value = [1,2,3,4] #默认按行插入：A1:D4分别写入1,2,3,4
sht.range('a2').options(transpose=True).value = [5,6,7,8] #按列插入
sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']] #多行输入
print(sht.range('a1:d4').value) #读取A1:D4
rng = sht.range('a1').expand('table')
nrows = rng.rows.count
a = sht.range(f'a1:a{nrows}').value #读取Excel第一列
ncols = rng.columns.count
fst_col = sht[0,:ncols].value #读取Excel第一行
sht.range('A1').column #获取单元格列标
sht.range('A1').row #获取行标
sht.range('A1').column_width #获取列宽
sht.range('A1').row_height #获取行高
print(sht.range('A1').column ,sht.range('A1').row ,sht.range('A1').column_width ,sht.range('A1').row_height )
sht.range('A1').rows.autofit() #行高自适应
sht.range('A1').columns.autofit()#列宽自适应
sht.range('A1').color=(34,156,65) #给单元格A1上背景色
sht.range('A1').color #返回单元格颜色的RGB值
print(sht.range('A1').color)
sht.range('A1').color = None #清楚单元格颜色
print(sht.range('A1').color)
sht.range('A1').formula='=SUM(B6:B7)' #输入公式，相应单元格执行结果
sht.range('A1').formula_array #获取单元格公式
sht.range('A1').value=[['a1','a2','a3'],[1,2,3]] #向指定单元格位置写入批量信息
sht.range('A1').expand().value #使用expand()方法读取表中批量数据
print(sht.range('A1').expand().value)
import numpy as np
np_data = np.array((1,2,3))
sht.range('F1').value = np_data #写入numpy array数据类型
import pandas as pd
df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
sht.range('A5').value = df #将pandas DataFrame数据类型写入excel
sht.range('A5').options(pd.DataFrame,expand='table').value #将数据读取，输出类型为DataFrame
import matplotlib.pyplot as plt
%matplotlib inline
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])
sht.pictures.add(fig, name='MyPlot', update=True) #将matplotlib图表写入到excel表格里

xlrd

xlrd主要是读取Excel，支持xlsx和xls格式的excel表格，可以实现指定表单、指定行列、指定单元格的读取。

import xlrd #导入库
data = xlrd.open_workbook(filename) #文件名以及路径，如果路径或者文件名有中文给前面加一个r拜师原生字符
# 获取book中一个工作表
table = data.sheets()[0] #通过索引顺序获取
table = data.sheet_by_index(sheet_indx)) #通过索引顺序获取
table = data.sheet_by_name(sheet_name) #通过名称获取
names = data.sheet_names() #返回book中所有工作表的名字
data.sheet_loaded(sheet_name or indx) # 检查某个sheet是否导入完毕
nrows = table.nrows #获取该sheet中的有效行数
table.row(rowx) #返回由该行中所有的单元格对象组成的列表
table.row_slice(rowx) #返回由该列中所有的单元格对象组成的列表
table.row_types(rowx, start_colx=0, end_colx=None) #返回由该行中所有单元格的数据类型组成的列表
table.row_values(rowx, start_colx=0, end_colx=None) #返回由该行中所有单元格的数据组成的列表
table.row_len(rowx) #返回该列的有效单元格长度
ncols = table.ncols #获取列表的有效列数
table.col(colx, start_rowx=0, end_rowx=None) #返回由该列中所有的单元格对象组成的列表
table.col_slice(colx, start_rowx=0, end_rowx=None) #返回由该列中所有的单元格对象组成的列表
table.col_types(colx, start_rowx=0, end_rowx=None) #返回由该列中所有单元格的数据类型组成的列表
table.col_values(colx, start_rowx=0, end_rowx=None) #返回由该列中所有单元格的数据组成的列表
table.cell(rowx,colx) #返回单元格对象
table.cell_type(rowx,colx) #返回单元格中的数据类型
table.cell_value(rowx,colx) #返回单元格中的数据

xlwt

xlwt主要是写入Excel，可以实现指定表单、指定单元格的写入，但保存的格式只支持xls格式。

import xlwt #导入模块
workbook = xlwt.Workbook(encoding='utf-8') #创建workbook 对象
worksheet = workbook.add_sheet('sheet1') #创建工作表sheet
worksheet.write(0, 0, 'hello') #往表中写内容,第一各参数行,第二个参数列,第三个参数内容
workbook.save('students.xls') #保存表为students.xls
# 为内容设置style
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
# 设置字体样式
font = xlwt.Font()
font.name = 'Time New Roman' # 字体
font.bold = True # 加粗
font.underline = True # 下划线
font.italic = True # 斜体
style = xlwt.XFStyle()
style.font = font # 创建style
worksheet.write(0, 1, 'world', style)
workbook.save('students.xls') # 根据样式创建workbook
# 合并单元格
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
# 通过worksheet调用merge()创建合并单元格
# 第一个和第二个参数单表行合并,第三个和第四个参数列合并,
# 合并第0列到第2列的单元格
worksheet.write_merge(0, 0, 0, 2, 'first merge')
# 合并第1行第2行第一列的单元格
worksheet.write_merge(0, 1, 0, 0, 'first merge')
workbook.save('students.xls')
# 设置单元格的对齐方式
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平居中
alignment.vert = xlwt.Alignment.VERT_CENTER # 垂直居中
style = xlwt.XFStyle()
style.alignment = alignment
worksheet.col(0).width = 6666 # 设置单元格宽度
worksheet.row(0).height_mismatch = True
worksheet.row(0).height = 1000 # 设置单元格的高度
worksheet.write(0, 0, 'hello world', style)
workbook.save('center.xls')
# 设置单元格边框
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
border = xlwt.Borders()
# DASHED虚线
# NO_LINE没有
# THIN实线
border.left = xlwt.Borders.THIN
border.right = xlwt.Borders.THIN
border.top = xlwt.Borders.THIN
border.bottom = xlwt.Borders.THIN
style = xlwt.XFStyle()
style.borders = border
worksheet.write(1, 1, 'love', style)
workbook.save('dashed.xls')
# 设置单元格背景色
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 3
style = xlwt.XFStyle()
style.pattern = pattern
worksheet.write(1, 1, 'shit', style)
workbook.save('shit.xls')
# 设置字体颜色
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
font = xlwt.Font()
# 设置字体为红色
font.colour_index=xlwt.Style.colour_map['red']
style = xlwt.XFStyle()
style.font = font
worksheet.write(0, 1, 'world', style)
workbook.save('students.xls')

XlsxWriter

XlsxWriter可以用来写文本、数字、公式并支持单元格格式化、图片、图表、文档配置、自动过滤等特性，不过缺点也很明显，不能用来读取和修改Excel文件。

import xlsxwriter # 导入库
work_book = xlsxwriter.Workbook('my first.xlsx') # 创建一个excel文件，文件名为"my first.xlsx"
work_sheet1 = work_book.add_worksheet() # 添加shhet1
work_sheet2 = work_book.add_worksheet('my excel.xlsx') # 添加sheet名字为my excel.xlsx
work_sheet3 = work_book.add_worksheet() # 不加参数，默认添加sheet3
# write_number：写入数字
# write_blank：写入空格
# write_formula：写入公式
# write_datetime：写入时间格式
# write_boolean:写入逻辑数据
# write_url:写入链接地址
work_sheet2.write_string(0, 0, 'this is write string!')
work_sheet2.write_number('A2', 123456)
work_sheet2.write_blank('A3', None)
work_sheet2.write_number('B1', 12)
work_sheet2.write_number('B2', 24)
work_sheet2.write_number('B3', 35)
work_sheet2.write_formula('B7', '=sum(b1:b5)')
work_sheet2.write_datetime(0, 3, datetime.datetime.strptime('2019-04-18', '%Y-%m-%d'),
work_book.add_format({'num_format': 'yyyy-mm-dd'}))
work_sheet1.write_boolean(0, 0, True)
work_sheet1.write_url('A2', 'http://www.toutiao.com')