python之 pyExcelerator写excel

不说了，直接上代码

写入 

from time import *
from pyExcelerator import *

style = XFStyle()

wb = Workbook()
ws0 = wb.add_sheet('0')

colcount = 200 + 1
rowcount = 6000 + 1

t0 = 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() - t0
print "\nsince starting elapsed %.2f s" % (t1)

print "Storing..."
wb.save('big-35Mb.xls')

时间格式

from pyExcelerator import *
from datetime import datetime

w = 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 = 0
for fmt in fmts:
    ws.write(i, 0, fmt)

    style = XFStyle()
    style.num_format_str = fmt

    ws.write(i, 4, datetime.now(), style)

    i += 1

w.save('dates.xls')

合并单元格

from pyExcelerator import *

fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True

borders = Borders()
borders.left = 6
borders.right = 6
borders.top = 6
borders.bottom = 6

al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER

style = XFStyle()
style.font = fnt
style.borders = borders
style.alignment = al


wb = 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 = True
style0.font.bold = True


wb = 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=NoCalcs

ws.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')