前言
Python可以操作Excel的模块不止一种，我习惯使用的写入模块是xlwt（一般都是读写模块分开的）
python中使用xlwt操作excel非常方,和Java使用调框架apache poi相比这就是天堂啊，下面话不多说了，来一起看看详细的介绍吧
一.安装xlwt模块
|
1
|
pip3 install xlwt |
二.简单使用xlwt
|
1
2
3
4
5
6
7
|
import xlwt #导入模块workbook = xlwt.Workbook(encoding='utf-8') #创建workbook 对象worksheet = workbook.add_sheet('sheet1') #创建工作表sheetworksheet.write(0, 0, 'hello') #往表中写内容,第一各参数 行,第二个参数列,第三个参数内容workbook.save('students.xls') #保存表为students.xls#在Java中使用poi操作excel 在创建workbook对象的时候就需要写好表名,创建workbook对象完成后#这个表就已经存在,但是使用xlwt 需要在最后调用保存的时候为表命名,并且只有调入save方法后才会#创建表 |
三.为内容设置style
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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#创建stylestyle = xlwt.XFStyle()style.font = font#根据样式创建workbookworksheet.write(0, 1, 'world', style)workbook.save('students.xls') |
四.合并单元格
使用xlwt 合并单元格时不用像poi通过style来设置,直接创建和并单元格就可以,
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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') |
如果需要了解具体调合并单元格规则就自己试着合并,查看合并效果.才能清晰明了
五.设置单元格的对齐方式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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_CENTERstyle = xlwt.XFStyle()style.alignment = alignment#设置单元格宽度worksheet.col(0).width = 6666#设置单元格的高度worksheet.row(0).height_mismatch = Trueworksheet.row(0).height = 1000worksheet.write(0, 0, 'hello world', style)workbook.save('center.xls') |
六.设置单元格调边框
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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 = 0x40bborder.right = xlwt.Borders.THINborder.right_colour = 0x40bborder.top = xlwt.Borders.THINborder.top_colour = 0x40bborder.bottom = xlwt.Borders.THINborder.bottom_colour = 0x40bstyle = xlwt.XFStyle()style.borders = borderworksheet.write(0, 0, 'love', style)workbook.save('dashed.xls') |
七.设置单元格边框
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
workbook = xlwt.Workbook(encoding='utf-8')worksheet = workbook.add_sheet('sheet1')border = xlwt.Borders()# DASHED虚线# NO_LINE没有# THIN实线border.left = xlwt.Borders.THINborder.right = xlwt.Borders.THINborder.top = xlwt.Borders.THINborder.bottom = xlwt.Borders.THINstyle = xlwt.XFStyle()style.borders = borderworksheet.write(1, 1, 'love', style)workbook.save('dashed.xls') |
八.设置单元格背景色
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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 = 3style = xlwt.XFStyle()style.pattern = patternworksheet.write(1, 1, 'shit', style)workbook.save('shit.xls') |
九.设置字体颜色
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 = fontworksheet.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的时候调错对象将
|
1
|
style = xlwt.Style() |
改为
|
1
|
style = xlwt.XFStyle() |
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。
原文链接：https://segmentfault.com/a/1190000017866070
