python之 pyExcelerator寫excel

不說了，直接上代碼

寫入 

from time import *from pyExcelerator import *style = XFStyle()wb = Workbook()ws0 = wb.add_sheet('0')colcount = 200 + 1rowcount = 6000 + 1t0 = time()print "\nstart: %s" % ctime(t0)print "Filling..."for col in xrange(colcount):    for row in xrange(rowcount):        print "%d, %d" % (col, row)        ws0.write(row, col, "%d, %d" % (row, col))        #ws0.write(row, col, "BIG")t1 = time() - t0print "\nsince starting elapsed %.2f s" % (t1)print "Storing..."wb.save('big-35Mb.xls')

時間格式

from pyExcelerator import *from datetime import datetimew = Workbook()ws = w.add_sheet('Hey, Dude')fmts = [    'M/D/YY',    'D-MMM-YY',    'D-MMM',    'MMM-YY',    'h:mm AM/PM',    'h:mm:ss AM/PM',    'h:mm',    'h:mm:ss',    'M/D/YY h:mm',    'mm:ss',    '[h]:mm:ss',    'mm:ss.0',]i = 0for fmt in fmts:    ws.write(i, 0, fmt)    style = XFStyle()    style.num_format_str = fmt    ws.write(i, 4, datetime.now(), style)    i += 1w.save('dates.xls')

合併儲存格

from pyExcelerator import *fnt = Font()fnt.name = 'Arial'fnt.colour_index = 4fnt.bold = Trueborders = Borders()borders.left = 6borders.right = 6borders.top = 6borders.bottom = 6al = Alignment()al.horz = Alignment.HORZ_CENTERal.vert = Alignment.VERT_CENTERstyle = XFStyle()style.font = fntstyle.borders = bordersstyle.alignment = alwb = Workbook()ws0 = wb.add_sheet('sheet0')ws1 = wb.add_sheet('sheet1')ws2 = wb.add_sheet('sheet2')for i in range(0, 0x200, 2):    ws0.write_merge(i, i+1, 1, 5, 'test %d' % i, style)    ws1.write_merge(i, i, 1, 7, 'test %d' % i, style)    ws2.write_merge(i, i+1, 1, 7 + (i%10), 'test %d' % i, style)wb.save('merged.xls')

格式

from pyExcelerator import *style0 = XFStyle()style0.font.name = 'Times New Roman'style0.font.struck_out = Truestyle0.font.bold = Truewb = Workbook()ws0 = wb.add_sheet('0')ws0.write(1, 1, 'Test', style0)for i in range(0, 0x53):    style = XFStyle()    style.font.name = 'Arial'    style.font.colour_index = i    style.font.outline = True    style.borders.left = i    ws0.write(i, 2, 'colour', style)    ws0.write(i, 3, hex(i), style0)wb.save('format.xls')

插入圖片

from pyExcelerator import *w = Workbook()ws = w.add_sheet('Image')ws.insert_bitmap('python.bmp', 2, 2)ws.insert_bitmap('python.bmp', 10, 2)w.save('image.xls')

函數運算

from pyExcelerator import *from pyExcelerator.Worksheet import *w = Workbook()ws = w.add_sheet('F')ws.frmla_opts=NoCalcsws.write(0, 0, Formula("-(1+1)", None))ws.write(1, 0, Formula("-(1+1)/(-2-2)", 3.14))ws.write(2, 0, Formula("-(134.8780789+1)", ErrorCode("#NULL!"), CalcOnOpen))ws.write(3, 0, Formula("-(134.8780789e-10+1)", ErrorCode("#NAME?")))ws.write(4, 0, Formula("-1/(1+1)+9344", 12345))ws.write(0, 1, Formula("-(1+1)", "this is 2", CalcOnOpen))ws.write(1, 1, Formula("-(1+1)/(-2-2)", u"this is 0.5"))ws.write(2, 1, Formula("-(134.8780789+1)", ""))ws.write(3, 1, Formula("-(134.8780789e-10+1)"))ws.write(4, 1, Formula("-1/(1+1)+9344"))ws.write(0, 2, Formula("A1*B1"))ws.write(1, 2, Formula("A2*B2"))ws.write(2, 2, Formula("A3*B3"))ws.write(3, 2, Formula("A4*B4*sin(pi()/4)"))ws.write(4, 2, Formula("A5%*B5*pi()/1000"))################ NOTE: parameters are separated by semicolon!!!##############ws.write(5, 2, Formula("C1+C2+C3+C4+C5/(C1+C2+C3+C4/(C1+C2+C3+C4/(C1+C2+C3+C4)+C5)+C5)-20.3e-2"))ws.write(5, 3, Formula("C1^2"))ws.write(6, 2, Formula("SUM(C1;C2;;;;;C3;;;C4)"))ws.write(6, 3, Formula("SUM($A$1:$C$5)"))ws.write(7, 0, Formula('"lkjljllkllkl"'))ws.write(7, 1, Formula('"yuyiyiyiyi"'))ws.write(7, 2, Formula('A8 & B8 & A8'))ws.write(8, 2, Formula('now()'))ws.write(10, 2, Formula('TRUE'))ws.write(11, 2, Formula('FALSE'))ws.write(12, 3, Formula('IF(A1>A2;3;"hkjhjkhk")'))w.save('formulas.xls')