xlwt设置单元格格式

xlwt设置单元格格式

设置单元格对对齐方式

import xlwt
excel_path = r'C:\Users\ruobiw\code\python\xlwt\testCenter.xls'
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet1')
style = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = 0x02        # 设置水平居中
alignment.vert = 0x01        # 设置垂直居中
style.alignment = alignment 
sheet.write(0, 0, 1, style) 
book.save(excel_path)

设置冻结窗口

设置冻结窗口需先将sheet表的冻结属性设置为真，然后再对水平、垂直需冻结行数、列数进行设置，如将表格文的前两行、第一列设置冻结窗口

import xlwt
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet1', cell_overwrite_ok=True)
# 设置冻结窗口
sheet.set_panes_frozen('1')# 设置冻结为真
sheet.set_horz_split_pos(2)# 水平冻结
sheet.set_vert_split_pos(1)# 垂直冻结
book.save(r'C:\Users\ruobiw\code\python\xlwt\testFrozen.xls')

设置边框

import xlwt
book = xlwt.Workbook(encoding = 'utf-8')
sheet = book.add_sheet('json_data', cell_overwrite_ok = True)
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
style = xlwt.XFStyle()
style.borders = borders
sheet.write(1, 1, 0, style)
book.save(r'C:\Users\ruobiw\code\python\xlwt\testBorder.xls')

设置粗体

import xlwt
book = xlwt.Workbook(encoding = 'utf-8')
sheet = book.add_sheet('json_data', cell_overwrite_ok = True)
font = xlwt.Font()
font.bold = True 
style = xlwt.XFStyle()
style.font = font
sheet.write(1, 1, 0, style)
book.save(r'C:\Users\ruobiw\code\python\xlwt\testbold.xls')

设置单元格宽度

xlwt创建时使用的默认宽度为2960，既11个字符0的宽度

import xlwt
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet1')
sheet.col(0).width=256*20          #xlwt中是行和列都是从0开始计算的
book.save(r'C:\Users\ruobiw\code\python\xlwt\testWidth.xls')

合并单元格

参数对应：行 列 值

import xlwt
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet1')
sheet.write_merge(1, 2, 0, 3, 'merge') #可以有style
book.save(r'C:\Users\ruobiw\code\python\xlwt\testMerge.xls')

设置单元格背景颜色

方式一：

import xlwt
excel_path = r'C:\Users\ruobiw\code\python\xlwt\testColor.xls'
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet1')
style2 = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 45
style2.pattern = pattern  
sheet.write(0, 1, 1, style2) 
book.save(excel_path)

方式二：

import xlwt
excel_path = r'C:\Users\ruobiw\code\python\xlwt\testColor.xls'
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet1')
# 创建一个样式对象，初始化样式 style
style1 = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']# 把背景颜色加到表格样式里去
style1.pattern = pattern  
sheet.write(0, 0, 1, style1) 
book.save(excel_path)

设置单元格格式

为显示20位小数，自定义单元格格式

import patterns as patterns
import xlwt
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet1', cell_overwrite_ok=True)
sheet.col(0).width=256*20
# 初始化样式
style = xlwt.XFStyle()
style.num_format_str = '#.###############'
sheet.write(0, 0, 1.2, style)
sheet.write(1, 0, 11.2, style)
sheet.write(2, 0, 1.23456789012345)
sheet.write(3, 0, 1.23456789012345, style)
sheet.write(4, 0, 0, style)
sheet.write(5, 0, 0.12345678901234, style)
sheet.col(1).width=256*20
style2 = xlwt.XFStyle()
style2.num_format_str = '#0.###############'
sheet.write(0, 1, 1.2, style2)
sheet.write(1, 1, 11.2, style2)
sheet.write(2, 1, 1.23456789012345)
sheet.write(3, 1, 1.23456789012345, style2)
sheet.write(4, 1, 0, style2)
sheet.write(5, 1, 0.12345678901234, style2)
book.save(r'C:\Users\ruobiw\code\python\xlwt\testNum_format_str.xls')