|
|
|
|
公众号矩阵

超强盘点!让Excel效率起飞的5个Python库

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

作者:吊车尾学院一木木老师来源:今日头条|2021-02-22 11:12

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

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

Xlwings

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

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

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

xlrd

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

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

xlwt

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

  1. import xlwt #导入模块 
  2. workbook = xlwt.Workbook(encoding='utf-8') #创建workbook 对象 
  3. worksheet = workbook.add_sheet('sheet1') #创建工作表sheet 
  4. worksheet.write(0, 0, 'hello') #往表中写内容,第一各参数 行,第二个参数列,第三个参数内容 
  5. workbook.save('students.xls') #保存表为students.xls 
  6. # 为内容设置style 
  7. workbook = xlwt.Workbook(encoding='utf-8'
  8. worksheet = workbook.add_sheet('sheet1'
  9. # 设置字体样式 
  10. font = xlwt.Font() 
  11. font.name = 'Time New Roman' # 字体 
  12. font.bold = True # 加粗 
  13. font.underline = True # 下划线 
  14. font.italic = True # 斜体 
  15.  
  16. style = xlwt.XFStyle() 
  17. style.font = font # 创建style 
  18. worksheet.write(0, 1, 'world', style) 
  19. workbook.save('students.xls') # 根据样式创建workbook 
  20. # 合并单元格 
  21. workbook = xlwt.Workbook(encoding='utf-8'
  22. worksheet = workbook.add_sheet('sheet1'
  23. # 通过worksheet调用merge()创建合并单元格 
  24. # 第一个和第二个参数单表行合并,第三个和第四个参数列合并, 
  25.  
  26. # 合并第0列到第2列的单元格 
  27. worksheet.write_merge(0, 0, 0, 2, 'first merge'
  28.  
  29. # 合并第1行第2行第一列的单元格 
  30. worksheet.write_merge(0, 1, 0, 0, 'first merge'
  31.  
  32. workbook.save('students.xls'
  33. # 设置单元格的对齐方式 
  34. workbook = xlwt.Workbook(encoding='utf-8'
  35. worksheet = workbook.add_sheet('sheet1'
  36. alignment = xlwt.Alignment() 
  37. alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平居中 
  38. alignment.vert = xlwt.Alignment.VERT_CENTER # 垂直居中 
  39. style = xlwt.XFStyle() 
  40. style.alignment = alignment 
  41. worksheet.col(0).width = 6666 # 设置单元格宽度 
  42. worksheet.row(0).height_mismatch = True 
  43. worksheet.row(0).height = 1000 # 设置单元格的高度 
  44. worksheet.write(0, 0, 'hello world', style) 
  45. workbook.save('center.xls'
  46. # 设置单元格边框 
  47. workbook = xlwt.Workbook(encoding='utf-8'
  48. worksheet = workbook.add_sheet('sheet1'
  49.  
  50. border = xlwt.Borders() 
  51. # DASHED虚线 
  52. # NO_LINE没有 
  53. # THIN实线 
  54. border.left = xlwt.Borders.THIN 
  55. border.right = xlwt.Borders.THIN 
  56. border.top = xlwt.Borders.THIN 
  57. border.bottom = xlwt.Borders.THIN 
  58.  
  59. style = xlwt.XFStyle() 
  60. style.borders = border 
  61. worksheet.write(1, 1, 'love', style) 
  62.  
  63. workbook.save('dashed.xls'
  64. # 设置单元格背景色 
  65. workbook = xlwt.Workbook(encoding='utf-8'
  66. worksheet = workbook.add_sheet('sheet1'
  67. pattern = xlwt.Pattern() 
  68. pattern.pattern = xlwt.Pattern.SOLID_PATTERN 
  69. pattern.pattern_fore_colour = 3 
  70. style = xlwt.XFStyle() 
  71. style.pattern = pattern 
  72. worksheet.write(1, 1, 'shit', style) 
  73. workbook.save('shit.xls'
  74. # 设置字体颜色 
  75. workbook = xlwt.Workbook(encoding='utf-8'
  76. worksheet = workbook.add_sheet('sheet1'
  77.  
  78. font = xlwt.Font() 
  79. # 设置字体为红色 
  80. font.colour_index=xlwt.Style.colour_map['red'
  81.  
  82. style = xlwt.XFStyle() 
  83.  
  84. style.font = font 
  85.  
  86. worksheet.write(0, 1, 'world', style) 
  87. workbook.save('students.xls'

XlsxWriter

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

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

openpyxl

openpyxl 是比较火的操作excel表格的Python库,只支持03版本之后的 xlsx。

  1. # 创建工作簿 Workbook 
  2. from openpyxl import Workbook 
  3. workbook = Workbook() # 创建一个工作簿对象 
  4. workbook.save('test.xlsx') # 保存这个工作簿,命名为test 
  5. # 打开已有工作簿 
  6. from openpyxl import load_workbook 
  7. workbook = load_workbook('test.xlsx') # #打开当前路径下的test表格 
  8. # 创建表 
  9. # 方法1:插入到最后(default
  10. ws1 = wb.create_sheet("Mysheet"
  11. # 方法2:插入到最开始的位置 
  12. ws2 = wb.create_sheet("Mysheet", 0) 
  13. # 选择现有的表 
  14. from openpyxl import load_workbook 
  15. workbook = load_workbook('test.xlsx') # 打开当前路径下的test表格 
  16. sheet = workbook['first_sheet'] # 选择名字为first_sheet的表格页 
  17. # 删除表 
  18. from openpyxl import load_workbook 
  19. workbook = load_workbook('test.xlsx') # 打开当前路径下的test表格 
  20. sheet = workbook['first_sheet'] # 选择名字为first_sheet的表格页 
  21. workbook.remove(sheet) #删除这张表 
  22. # 访问单元格 
  23. # 方法1 
  24. cell1 = sheet['A1'
  25. # 方法2 
  26. cell2 = sheet.cell(row=1,column=2) 
  27. cell1.value = '123456' # 设置单元格的值 
  28. sheet.merge_cells('A1:A2') #合并A1和A2单元格 

今天就暂时先推荐到这里,有需要的同学赶紧收藏起来~~

【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

6人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

33人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

220人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微