本文以编写小学10以内加减法为例，简单介绍使用xlwt模块操作Excel表格的方法。
xlwt库官方说明文档
xlrd模块简单介绍
本文源代码下载
使用pip安装xlwt模块
python">$ pip install xlwt
简单的官方例子
python">import xlwt
from datetime import datetime
style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')
ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))
wb.save('example.xls')
设置显示样式
python"># 设置显示样式
styletitle = xlwt.XFStyle()
# 为样式创建字体
fonttitle = xlwt.Font()
fonttitle.name = 'Time New Roman'
# 字体颜色
fonttitle.colour_index = 0
# 字体大小，20为衡量单位，22为字号
fonttitle.height = 20 * 22
# 字体加粗
fonttitle.bold = True
# 下划线
fonttitle.underline = False
# 斜体字
fonttitle.italic = False
# 设置单元格对齐方式
alignment = xlwt.Alignment()
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.horz = 0x01
#alignment.horz = xlwt.Alignment.HORZ_LEFT
# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.vert = 0x01
#alignment.vert = xlwt.Alignment.VERT_CENTER
# 设置自动换行
alignment.wrap = 1
# 设置边框
borderstitle = xlwt.Borders()
# 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
# 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
borderstitle.top,borderstitle.bottom,borderstitle.left,borderstitle.right = (1,1,1,1)
styletitle.font = fonttitle
styletitle.alignment=alignment
styletitle.borders = borderstitle
设置行列宽度
python"> def setTitle(self):
# 设置行高，20为衡量单位，80为字符数
# 容许用户修改行高
self._worksheet.row(0).height_mismatch = True
self._worksheet.row(0).height = 20 * 80
for i in range(len(self._titles)):
# 设置列宽，256为衡量单位，11为字符数
self._worksheet.col(i).width = 256 * 24
self._worksheet.write(0,i,self._titles[i],self._titleSytle)
完整代码
python">import xlwt,random
class TestPaper():
def __init__(self,rows=25,cols=4):
self._titles = ('姓名：','班级：','用时：','评分：')
self._rowNum = rows
self._colNum = cols
self.setStyle()
def setStyle(self):
# 设置显示样式
styletitle = xlwt.XFStyle()
# 为样式创建字体
fonttitle = xlwt.Font()
fonttitle.name = 'Time New Roman'
# 字体颜色
fonttitle.colour_index = 0
# 字体大小，22为字号，20为衡量单位
fonttitle.height = 20 * 22
# 字体加粗
fonttitle.bold = True
# 下划线
fonttitle.underline = False
# 斜体字
fonttitle.italic = False
# 设置单元格对齐方式
alignment = xlwt.Alignment()
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.horz = 0x01
#alignment.horz = xlwt.Alignment.HORZ_LEFT
# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.vert = 0x01
#alignment.vert = xlwt.Alignment.VERT_CENTER
# 设置自动换行
alignment.wrap = 1
# 设置边框
borderstitle = xlwt.Borders()
# 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
# 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
borderstitle.top,borderstitle.bottom,borderstitle.left,borderstitle.right = (1,1,1,1)
borderstitle.top_colour,borderstitle.bottom_colour,borderstitle.left_colour,borderstitle.right_colour=(0,0,0,0)
styletitle.font = fonttitle
styletitle.alignment=alignment
styletitle.borders = borderstitle
stylerecord = xlwt.XFStyle()
fontrecord = xlwt.Font()
fontrecord.name = 'Time New Roman'
fontrecord.height = 20 * 18
fontrecord.bold = False
fontrecord.underline = False
fontrecord.italic = False
stylerecord.font = fontrecord
bordersrecord = xlwt.Borders()
bordersrecord.top, bordersrecord.bottom, bordersrecord.left, bordersrecord.right = (1,1,1,1)
stylerecord.borders = bordersrecord
self._titleSytle = styletitle
self._recordStyle = stylerecord
def setTitle(self):
# 设置列宽，一个中文等于两个英文等于两个字符，80为字符数，20为衡量单位
self._worksheet.row(0).height_mismatch = True
self._worksheet.row(0).height = 20 * 80
for i in range(len(self._titles)):
# 设置列宽，一个中文等于两个英文等于两个字符，11为字符数，256为衡量单位
self._worksheet.col(i).width = 256 * 24
self._worksheet.write(0,i,self._titles[i],self._titleSytle)
def getFormula(self):
operator = ('+','-')
x,y = random.randint(0,9),random.randint(0,9)
if x + y <= 9:
k = random.randint(0,2)
if k == 0:
return '%s+%s=%s'%(' ( ) ',' '+str(y)+' ',' '+str(x+y)+' ')
if k == 1:
return '%s+%s=%s' % (' '+str(x)+ ' ', ' ( ) ',' '+str(x+y)+' ')
else:
return '%s+%s=%s' % (' '+str(x)+' ',' '+str(y)+' ',' ')
else:
x,y = max(x,y),min(x,y)
k = random.randint(0, 2)
if k == 0:
return '%s-%s=%s' % (' ( ) ', ' '+str(y)+' ',' '+str(x-y)+' ')
if k == 1:
return '%s-%s=%s' % (' '+str(x)+' ', ' ( ) ',' '+str(x-y)+' ')
else:
return '%s-%s=%s' % (' '+str(x)+' ',' '+str(y)+' ', ' ')
def setFormula(self,number=None):
_number = self._colNum * self._rowNum if not number else number
formulas = set()
while True:
formulas.add(self.getFormula())
if len(formulas) >= _number:
return formulas
def getNewTestPaper(self,filename='NewPaper.xls'):
self._workbook = xlwt.Workbook(encoding='utf-8', style_compression=0)
self._worksheet = self._workbook.add_sheet('Paper1', cell_overwrite_ok=False)
self.setTitle()
formulas = self.setFormula()
for row in range(1,self._rowNum+1):
self._worksheet.row(0).height_mismatch = True
self._worksheet.row(0).height = 20 * 50
for col in range(self._colNum):
self._worksheet.write(row,col,formulas.pop(),self._recordStyle)
self._workbook.save(filename)
if __name__ == '__main__':
paper = TestPaper()
paper.getNewTestPaper()
