python怎么使用xlwt操作excel你知道吗

目录

• 总结

一、安装

pip install xlwt

二、创建表格并写入

import xlwt
# 创建一个workbook并设置编码
workbook = xlwt.workbook(encoding = 'utf-8')
# 添加sheet
worksheet = workbook.add_sheet('飞兔小哥')
# 写入excel, 参数对应 行, 列, 值
worksheet.write(1,0, label = '我爱autofelix')
# 保存
workbook.save('xlwt教学.xls')

三、设置单元格样式

import xlwt
workbook = xlwt.workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('飞兔小哥')
 # 初始化样式
style = xlwt.xfstyle()
# 为样式创建字体
font = xlwt.font()
# 黑体
font.name = 'times new roman'
font.bold = true
# 下划线
font.underline = true
# 斜体字
font.italic = true
# 设定样式
style.font = font
# 带样式的写入
worksheet.write(0, 0, '带样式的单元格', style)
# 保存文件
workbook.save('xwlt单元格样式教学.xls')

 四、设置单元格宽度

import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
worksheet.write(0, 0,'我爱autofelix')
# 设置单元格宽度
worksheet.col(0).width = 3000
workbook.save('xwlt单元格宽度教学.xls')

五、设置单元格背景色

import xlwtworkbook = xlwt.workbook()worksheet = workbook.add_sheet('飞兔小哥')# 创建模式对象create the patternpattern = xlwt.pattern()# may be: no_pattern, solid_pattern, or 0x00 through 0x12pattern.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, the list goes on...pattern.pattern_fore_colour = 5# 创建样式对象create the patternstyle = xlwt.xfstyle()# 将模式加入到样式对象add pattern to stylestyle.pattern = pattern#向单元格写入内容时使用样式对象styleworksheet.write(0, 0, '单元格内容', style)workbook.save('xwlt单元格背景色教学.xls')import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
# 创建模式对象create the pattern
pattern = xlwt.pattern()
# may be: no_pattern, solid_pattern, or 0x00 through 0x12
pattern.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, the list goes on...
pattern.pattern_fore_colour = 5
# 创建样式对象create the pattern
style = xlwt.xfstyle()
# 将模式加入到样式对象add pattern to style
style.pattern = pattern
#向单元格写入内容时使用样式对象style
worksheet.write(0, 0, '单元格内容', style)
workbook.save('xwlt单元格背景色教学.xls')

六、设置单元格内容对齐方式

import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
# 创建对其格式的对象 create alignment
alignment = xlwt.alignment()
#水平居中 may be: horz_general, horz_left, horz_center, horz_right, horz_filled, horz_justified, horz_center_across_sel, horz_distributed
alignment.horz = xlwt.alignment.horz_center
#我上下对齐 may be: vert_top, vert_center, vert_bottom, vert_justified, vert_distributed
alignment.vert = xlwt.alignment.vert_center 
#创建样式对象 create style
style = xlwt.xfstyle()
# 将格式alignment对象加入到样式对象add alignment to style
style.alignment = alignment
#写入的时候调用样式style
worksheet.write(0, 0, '单元居中', style)
workbook.save('xlwt单元格对齐教学.xls')

七、单元格添加超链接

import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
worksheet.write(0, 0, xlwt.formula('hyperlink("https://www.baidu.com";"百度")'))
workbook.save('xwlt单元格超链接教学.xls')

八、单元格添加公式

import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
worksheet.write(0, 0, 5) # outputs 5  #第一行第一列5
worksheet.write(0, 1, 2) # outputs 2  #第一行第二列2
#(a1[5] * a2[2])  第二行第一列，输出第一行数字乘积
worksheet.write(1, 0, xlwt.formula('a1*b1'))
#(a1[5] + a2[2])  第二行第二列，输出第一行数字之和
worksheet.write(1, 1, xlwt.formula('sum(a1,b1)'))
workbook.save('xwlt添加公式教学.xls')

九、单元格中输入日期

import xlwt
import datetime
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
style = xlwt.xfstyle()
#其他格式包括: d-mmm-yy, d-mmm, mmm-yy, h:mm, h:mm:ss, h:mm, h:mm:ss, m/d/yy h:mm, mm:ss, [h]:mm:ss, mm:ss.0
style.num_format_str = 'm/d/yy'
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('xwlt日期格式教学.xlsx')

十、合并行和列

import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
worksheet.write_merge(0, 1, 0, 3, '合并从第一行到第二行，第一列到第四列')
#创建字体示例
font = xlwt.font()
#bold设置为黑体字
font.bold = true
# create style
style = xlwt.xfstyle()
# add bold font to style
style.font = font
worksheet.write_merge(2,3, 0, 3, '合并从第三行到第四行，第一列到第四列', style)
workbook.save('xwlt合并单元格教学.xls')

十一、单元格添加边框

import xlwtworkbook = xlwt.workbook()worksheet = workbook.add_sheet('飞兔小哥')# 创建边框对象create bordersborders = xlwt.borders()#对边框对象进行操作，指定边框上下左右的边框类型为虚线# dashed虚线# no_line没有# thin实线# 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 = xlwt.borders.dashedborders.right = xlwt.borders.dashedborders.top = xlwt.borders.dashedborders.bottom = xlwt.borders.dashed#指定上下左右的边框颜色为0x40borders.left_colour = 0x40borders.right_colour = 0x40borders.top_colour = 0x40borders.bottom_colour = 0x40# create style   #创建样式对象style = xlwt.xfstyle()# 将设置好的边框对象borders 加到样式对象style中。add borders to stylestyle.borders = borders#向单元格第一行第一列写入“单元格内容”，并使用创建好的样式对象styleworksheet.write(0, 0, '单元格内容', style)workbook.save('xwlt添加边框教学.xls')import xlwt
workbook = xlwt.workbook()
worksheet = workbook.add_sheet('飞兔小哥')
# 创建边框对象create borders
borders = xlwt.borders()
#对边框对象进行操作，指定边框上下左右的边框类型为虚线
# dashed虚线
# no_line没有
# thin实线
# 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 = xlwt.borders.dashed
borders.right = xlwt.borders.dashed
borders.top = xlwt.borders.dashed
borders.bottom = xlwt.borders.dashed
#指定上下左右的边框颜色为0x40
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
# create style   #创建样式对象
style = xlwt.xfstyle()
# 将设置好的边框对象borders 加到样式对象style中。add borders to style
style.borders = borders
#向单元格第一行第一列写入“单元格内容”，并使用创建好的样式对象style
worksheet.write(0, 0, '单元格内容', style)
workbook.save('xwlt添加边框教学.xls')

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注www.887551.com的更多内容!