import xlrd3
import xlsxwriter
def read_excel_data(filename, data_type=None):
""" :param filename: 文件名 :param data_type: 返回數據類型，默認是list,如果data_type是True返回的是dict的類型的數據 :return: """
wb = xlrd3.open_workbook(filename)
sheet_01 = wb.sheet_by_index(0) # 選第一個sheet
res_list = []
if data_type:
start_index = 0
title = [ele.value for ele in sheet_01.row(start_index)]
for index in range(1, sheet_01.nrows): # 從第一行開始讀取，標題不讀
row_list = sheet_01.row(index)
if data_type:
row_data = {
title[k_index]: ele.value for k_index, ele in enumerate(row_list)}
else:
row_data = [ele.value for ele in row_list]
res_list.append(row_data)
return res_list
寫excle_
代碼如下
def write_to_excel_data(data_list, file_name, title=None, sheet_name="sheet1"):
""" :param data_list: 寫入數據[{}] 或者 [[]] :param title: 標題 :param file_name: 文件名 :param sheet_name: 工作薄 :return: """
workfile = xlsxwriter.Workbook(file_name)
worksheet = workfile.add_worksheet(sheet_name)
if not isinstance(title, (tuple, list)):
return "標題數據格式不對"
row_index, col_index = 0, 0
start_index = 0
# 設置背景色
if not sheet_name:
format = workfile.add_format()
format.set_bg_color("green")
if title:
worksheet.write_row(row_index, col_index, title)
start_index = 1
# dict 數據寫入
if isinstance(data_list[0], dict):
for index, item in enumerate(data_list, start_index):
ele = [item.get(key) for key in title]
worksheet.write_row(index, col_index, ele)
pass
# list 數據寫入
else:
for index, item in enumerate(data_list, start_index):
worksheet.write_row(index, col_index, item)
workfile.close()
