Python中操作Excel文件的方法有很多,xlwt(对Excel文件的写操作)、xlrd(对Excel文件的读操作)、原生的csv模块等,在日常的数据处理工作中,经常需要对Excel文件进行一些不可描述的操作。今天开始我的数据分析--数据处理--xlwt方法的第一篇博客。欢迎大家阅读、issue、转发、评论。
xlwt支持excel03版到excel2013版,保存的格式目前只支持xls格式,07以后的版本xlsx不支持。
正文开始!!!
-
对Excel中的单元格cell设置背景颜色
#!/usr/bin/env python3.6
# encoding: utf-8
'''
@author: Leo
@contact:
@software: PyCharm
@file: Add_bg_to_cell.py
@time: 2018/10/16 上午 08:24
@desc:
'''
import xlwt
workbook = xlwt.Workbook() # Create workbook
worksheet = workbook.add_sheet('My sheet')
pattern = xlwt.Pattern() # Create the pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # 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, the list goes on...
style = xlwt.XFStyle() # Create the pattern
style.pattern = pattern # Add pattern to style
worksheet.write(2, 5, 'Cell contents', style)
workbook.save('E:\\test\\xls_xlwt\Add_bg_to_cell.xls')
因为高(zhuang)效(bi),所以在代码中注释使用一些简单的英文注释。
在代码运行后,生成如下Excel样式。
怎么样?是不是很简单-_-!
2.对Excel中的单元格cell设置边框
#!/usr/bin/env python3.6
# encoding: utf-8
'''
@author: Leo
@contact:
@software: PyCharm
@file: Add_border_to_cell.py
@time: 2018/10/15 下午 07:01
@desc:
'''
import xlwt
workbook = xlwt.Workbook() # Create workbook
worksheet = workbook.add_sheet('My sheet') # Create worksheet
borders = xlwt.Borders() # Create borders
borders.left = xlwt.Borders.MEDIUM # 添加边框-虚线边框
borders.right = xlwt.Borders.MEDIUM # 添加边框-虚线边框
borders.top = xlwt.Borders.MEDIUM # 添加边框-虚线边框
borders.bottom = xlwt.Borders.MEDIUM # 添加边框-虚线边框
'''
May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE,
HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED,
THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED,
or 0x00 through 0x0D.
'''
borders.left_colour = 0x90 # 边框上色
borders.right_colour = 0x90
borders.top_colour = 0x90
borders.bottom_colour = 0x90
style = xlwt.XFStyle() # Create style
style.borders = borders # Add borders to style
worksheet.write(2, 3, 'Content', style) # row, col, content
workbook.save('E:\\test\\xls_xlwt\Add_border_to_cell.xls')
在代码运行后,生成如下Excel样式。
到这里,本次的展示就结束了,欢迎大家提问、评论。在本系列的后续,还会有所有方法一块使用的详解。