摘要：中使用操作非常方和使用調框架相比這就是天堂啊一安裝模塊二簡單使用導入模塊創建對象創建工作表往表中寫內容第一各參數行第二個參數列第三個參數內容保存表為在中使用操作在創建對象的時候就需要寫好表名創建對象完成后這個表就已經存在但是使用需要在最后

python中使用xlwt操作excel非常方,和Java使用調框架apache poi相比這就是天堂啊
一.安裝xlwt模塊

pip3 install xlwt

二.簡單使用xlwt

import xlwt  #導入模塊
workbook = xlwt.Workbook(encoding="utf-8") #創建workbook 對象
worksheet = workbook.add_sheet("sheet1")   #創建工作表sheet
worksheet.write(0, 0, "hello") #往表中寫內容,第一各參數 行,第二個參數列,第三個參數內容
workbook.save("students.xls") #保存表為students.xls
#在Java中使用poi操作excel 在創建workbook對象的時候就需要寫好表名,創建workbook對象完成后
#這個表就已經存在,但是使用xlwt 需要在最后調用保存的時候為表命名,并且只有調入save方法后才會#創建表

三.為內容設置style

workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("sheet1")
#設置字體樣式
font = xlwt.Font()
#字體
font.name = "Time New Roman"
#加粗
font.bold = True
#下劃線
font.underline = True
#斜體
font.italic = True

#創建style
style = xlwt.XFStyle()
style.font = font
#根據樣式創建workbook
worksheet.write(0, 1, "world", style)
workbook.save("students.xls")

四.合并單元格,使用xlwt 合并單元格時不用像poi通過style來設置,直接創建和并單元格就可以,

workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("sheet1")
#通過worksheet調用merge()創建合并單元格
#第一個和第二個參數單表行合并,第三個和第四個參數列合并,

#合并第0列到第2列的單元格
worksheet.write_merge(0, 0, 0, 2, "first merge")

#合并第1行第2行第一列的單元格
worksheet.write_merge(0, 1, 0, 0, "first merge")

workbook.save("students.xls")

如果需要了解具體調合并單元格規則就自己試著合并,查看合并效果.才能清晰明了

五.設置單元格的對齊方式

workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("sheet1")
alignment = xlwt.Alignment()
# 水平居中
alignment.horz = xlwt.Alignment.HORZ_CENTER
# 垂直居中
alignment.vert = xlwt.Alignment.VERT_CENTER
style = xlwt.XFStyle()
style.alignment = alignment
#設置單元格寬度
worksheet.col(0).width = 6666

#設置單元格的高度
worksheet.row(0).height_mismatch = True
worksheet.row(0).height = 1000

worksheet.write(0, 0, "hello world", style)
workbook.save("center.xls")

六.設置單元格調邊框

workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("sheet1")
border = xlwt.Borders()
# DASHED虛線
# NO_LINE沒有
# THIN實線
border.left = xlwt.Borders.THIN
# 設置顏色
border.left_coloure = 0x40b
border.right = xlwt.Borders.THIN

border.right_colour = 0x40b
border.top = xlwt.Borders.THIN

border.top_colour = 0x40b
border.bottom = xlwt.Borders.THIN

border.bottom_colour = 0x40b
style = xlwt.XFStyle()

style.borders = border
worksheet.write(0, 0, "love", style)
workbook.save("dashed.xls")

七.設置單元格邊框

workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("sheet1")

border = xlwt.Borders()
# DASHED虛線
# NO_LINE沒有
# THIN實線
border.left = xlwt.Borders.THIN
border.right = xlwt.Borders.THIN
border.top = xlwt.Borders.THIN
border.bottom = xlwt.Borders.THIN

style = xlwt.XFStyle()
style.borders = border
worksheet.write(1, 1, "love", style)

workbook.save("dashed.xls")

八.設置單元格背景色

workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("sheet1")
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN

# 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, the list goes on...

pattern.pattern_fore_colour = 3
style = xlwt.XFStyle()
style.pattern = pattern
worksheet.write(1, 1, "shit", style)
workbook.save("shit.xls")

九.設置字體顏色

workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("sheet1")

font = xlwt.Font()
# 設置字體為紅色
font.colour_index=xlwt.Style.colour_map["red"]

style = xlwt.XFStyle()

style.font = font

worksheet.write(0, 1, "world", style)
workbook.save("students.xls")

遇到的問題:

1.PermissionError: [Errno 13] Permission denied: "dashed.xls"
原因:測試寫入調時候文件被打開了,無法寫入,關閉文件后再寫入就ok了
2.TypeError: "module" object is not callable
創建style的時候調錯對象將

style = xlwt.Style()

改為

style = xlwt.XFStyle()

各個樣式組合一起完整代碼:

import  xlwt
workbook = xlwt.Workbook(encoding="utf-8") #創建workbook 對象
worksheet = workbook.add_sheet("sheet1")   #創建工作表sheet
#設置字體樣式
font = xlwt.Font()
#字體
font.name = "Time New Roman"
#加粗
font.bold = True
#下劃線
font.underline = True
#斜體
font.italic = True
# 設置字體為紅色
font.colour_index=xlwt.Style.colour_map["red"]

#--------------------------------邊框
border = xlwt.Borders()
# DASHED虛線
# NO_LINE沒有
# THIN實線
border.left = xlwt.Borders.THIN
# 設置顏色
border.left_coloure = 0x40b
border.right = xlwt.Borders.THIN

border.right_colour = 0x40b
border.top = xlwt.Borders.THIN

border.top_colour = 0x40b
border.bottom = xlwt.Borders.THIN

border.bottom_colour = 0x40b
# DASHED虛線
# NO_LINE沒有
# THIN實線
border.left = xlwt.Borders.THIN
border.right = xlwt.Borders.THIN
border.top = xlwt.Borders.THIN
border.bottom = xlwt.Borders.THIN

#--------------------單元格背景色
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN

#-------------------------------居中--------------
alignment = xlwt.Alignment()
# 水平居中
alignment.horz = xlwt.Alignment.HORZ_CENTER
# 垂直居中
alignment.vert = xlwt.Alignment.VERT_CENTER
#創建style
style = xlwt.XFStyle()
style.font = font
style.borders = border
style.pattern = pattern
style.alignment = alignment

#根據樣式創建workbook
worksheet.write(0, 1, "world", style)
workbook.save("students.xls")