Python生成excel表格並設置樣式
- 2020 年 2 月 14 日
- 筆記
python在做爬蟲時會涉及到數據存儲問題,下面說一下將數據存儲在excel表格中,主要使用擴展類xlwt,下面詳細說一下,主要涉及到了數據列,背景,名稱等參數。
說明:python3.7、window環境
一、類庫安裝
pip install xlwt
二、示例程式碼
# -*- coding: utf-8 -*- import xlwt import time # 生成表格文件 def create_file(content): # 初始化樣式 style_head = xlwt.XFStyle() # 初始化字體相關 font = xlwt.Font() font.name = "微軟雅黑" font.bold = True # 必須是數字索引 font.colour_index = 1 # 初始背景圖案 bg = xlwt.Pattern() # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 bg.pattern = xlwt.Pattern.SOLID_PATTERN # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray bg.pattern_fore_colour = 4 # 設置字體 style_head.font = font # 設置背景 style_head.pattern = bg # 創建一個excel excel = xlwt.Workbook(encoding='utf-8') # 添加工作區 sheet = excel.add_sheet("演示表格") # xlwt中是行和列都是從0開始計算的 first_col_1 = sheet.col(1) first_col_3 = sheet.col(3) # 設置創建時間寬度 first_col_1.width = 256 * 15 # 設置存儲路徑列寬度 first_col_3.width = 256 * 100 # 標題資訊 head = ["序號", "插入時間", "標題", "英文名稱"] for index, value in enumerate(head): sheet.write(0, index, value, style_head) # 循環寫入 for index, value_list in enumerate(content, 1): for i, value in enumerate(value_list): sheet.write(index, i, value) # 保存excel file_name = time.time() excel.save("./%s.xls" % file_name) return file_name if __name__ == '__main__': data_list = [ (0, 1580719141, '影片|有你,才有勝利!', 'You are the winner!'), (1, 1580719142, '中央軍委批准!軍隊承擔武漢生活物資配送供應任務', "Approved by the Central Military Commission! The military undertakes the task of distribution and supply of living materials in Wuhan") ] data = create_file(data_list) print("success") print(data)
三、導出效果
四、注意事項
1、開發過程中遇到了生成文件名後綴為xlsx格式,無法打開,故設置為xls格式。
2、設置列寬時需要注意,xlwt是從0開始計數的。