之前操作excel使用的都是xlrd,xlwt,不是很方便,近期尝试用xlwings实现了excel文件的操作,实例代码如下。
开源地址:https://github.com/guliangce/MiniPrograms
import xlwings as xw
#init
import os
filepath = r'../files/execlnew.xls'
try:
os.remove(filepath)
except Exception as e:
print(e)
import time
time.sleep(3)
#1、打开Excel程序,默认设置:程序可见,只打开不新建工作薄,屏幕更新关闭,添加数据
app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False
filepath = r'../files/execlnew.xls'
wb = app.books.add()
wb.sheets['sheet1'].range('A1').value=['类型','模块']
wb.sheets['sheet1'].range('A2').expand('right').value=[['官方','os'],['官方','sys'],['第三方','pytest']]
wb.save(filepath)
wb.close()
app.quit()
#2、打开已经有的Excel表单,添加数据
app = xw.App(visible=False, add_book=False)
filepath = r'../files/execlnew.xls'
wb = app.books.open(filepath)
lastrow = wb.sheets['sheet1'].range('A1').expand('right').current_region.last_cell.row
wb.sheets['sheet1'].range('A'+str(lastrow+1)).expand('right').value=[['第三方','requests'],['第三方','xlwings'],['第三方','pytest']]
wb.save(filepath)
wb.close()
app.quit()
#3、读取Execl表单中的数据,并结构化
app = xw.App(visible=False, add_book=False)
filepath = r'../files/execlnew.xls'
wb = app.books.open(filepath)
lis = wb.sheets['sheet1'].range('A1:B7').value
print("获取的数据:" ,lis)
wb.close()
app.quit()