excel是我們在日常工作中經常會用到的表格工具，我們在也已認證python來實作excel檔案中寫入和追加内容。
話不多說，上幹貨。
import xlwt
import xlrd
from xlutils.copy import copy
def write_to_excel(words, filename, sheet_name='sheet1'):
'''
将item存儲到excel中。
:param words: 儲存item的list [{},{}]格式
:return:
'''
try:
# 1、建立工作薄
work_book = xlwt.Workbook(encoding='utf-8')
# 2、建立sheet表單
sheet = work_book.add_sheet(sheet_name)
# 3、寫表頭
# head = ['英文','中文']
head = []
for k in words[0].keys():
head.append(k)
for i in range(len(head)):
sheet.write(0, i, head[i])
# 4、添加内容
# 行号
i = 1
for item in words:
for j in range(len(head)):
sheet.write(i, j, item[head[j]])
# 寫完一行，将行号+1
i += 1
# 儲存
work_book.save(filename)
print('寫入excel成功！')
except Exception as e:
print('寫入excel失敗！', e)
def append_to_excel(words, filename):
'''
追加資料到excel
:param words: 【item】 [{},{}]格式
:param filename: 檔案名
:return:
'''
try:
# 打開excel
word_book = xlrd.open_workbook(filename)
# 擷取所有的sheet表單。
sheets = word_book.sheet_names()
# 擷取第一個表單
work_sheet = word_book.sheet_by_name(sheets[0])
# 擷取已經寫入的行數
old_rows = work_sheet.nrows
# 擷取表頭資訊
heads = work_sheet.row_values(0)
# 将xlrd對象變成xlwt
new_work_book = copy(word_book)
# 添加内容
new_sheet = new_work_book.get_sheet(0)
i = old_rows
for item in words:
for j in range(len(heads)):
new_sheet.write(i, j, item[heads[j]])
i += 1
new_work_book.save(filename)
print('追加成功！')
except Exception as e:
print('追加失敗！', e)
if __name__ == '__main__':
# 樣例
words1 = [
{'name': 'aki', 'age': 18, 'gender': '女'},
{'name': 'zed', 'age': 20, 'gender': '男'}
]
words2 = [
{'name': 'leblance', 'age': 19, 'gender': '女'},
{'name': 'yasuo', 'age': 20, 'gender': '男'}
]
# 寫入内容
write_to_excel(words=words1, filename='demo.xls', )
# 追加内容
append_to_excel(words=words2, filename='demo.xls')
執行結果如下：
生成的excel表格内容結果如下：
