python - 将数据导出到 Excel

Question

这是我的代码:

import xlwt
file = 'C://Users/MM/Desktop/'
wb = xlwt.Workbook()
sh = wb.add_sheet('S1')

d = [['Numbers','4,849,377','736,732','6,731,484']['Money','$43,253','$70,760','$49,774'],['Decnum','1.22','3.45','9.10']]

for row, nums in enumerate(d):
    for col, value in enumerate(nums):
        sh.write(col, row , value)

wb.save(file +'try.xls')
print('Exported')

以下是我想要完成的事情:

1)第一行中的所有内容都需要粗体和斜体。

2)所有列的宽度需要相同(固定)。

3) “Numbers”、“Money”和“Decnum”在 Excel 中给我一个错误，告诉我将它们转换为带有绿色错误箭头的数字。

4)数据需要居中。

这是展望的示例

Answer 1

您正在寻找replace()

>>> x = '1,2,3'
>>> x.replace(',', '')
'123'

for row, nums in enumerate(d):
    for col, value in enumerate(nums):
        try:
            value = int(value.replace(',', ''))
        except:
            pass
        sh.write(col, row , value)

样式格式:

style = xlwt.XFStyle()

font = xlwt.Font()
font.bold = True
style.font = font

sh.write(col, row , value, style=style)

总结:

style = xlwt.XFStyle()

font = xlwt.Font()
font.bold = True
style.font = font

for row, nums in enumerate(d):
    for col, value in enumerate(nums):
        try:
            value = int(value.replace(',', ''))
        except:
            pass
        if col == 0:
            sh.write(col, row , value, style=style)
        else:
            sh.write(col, row , value)

另一种方法是使用easyxf():

head = xlwt.easyxf('align: horiz center; font: bold 1,')
common = xlwt.easyxf('align: horiz center;')

for row, nums in enumerate(d):
    for col, value in enumerate(nums):
        try:
            value = int(value.replace(',', ''))
        except:
            pass
        if col == 0:
            sh.write(col, row, value, style=head)
        else:
            sh.write(col, row, value, style=common)

宽度和高度

添加 bedore wb.save()

import itertools

col_width = 26 * 20   

try:
    for i in itertools.count():
        sh.col(i).width = col_width
except ValueError:
    pass

<小时/>

设置单元格格式

Additional formats

num = xlwt.easyxf('align: horiz center;', '#,##')

if col == 0:
    sh.write(col, row, value, style=head)
else:
    if type(value) is int:
        sh.write(col, row, value, style=num)
    else:
        sh.write(col, row, value, style=common)

<小时/>

完整片段:

import itertools
import xlwt

wb = xlwt.Workbook()
sh = wb.add_sheet('S1')

d = [['Numbers', '4,849,377', '736,732', '6,731,484'],
     ['Letters', 'a', 'b', 'c'], ['Colors', 'red', 'green', 'yellow']]


head = xlwt.easyxf('align: horiz center; font: bold 1,')
common = xlwt.easyxf('align: horiz center;')
num = xlwt.easyxf('align: horiz center;', '#,##')

for row, nums in enumerate(d):
    for col, value in enumerate(nums):
        try:
            value = int(value.replace(',', ''))
        except:
            pass
        if col == 0:
            sh.write(col, row, value, style=head)
        else:
            if type(value) is int:
                sh.write(col, row, value, style=num)
            else:
                sh.write(col, row, value, style=common)

    col_width = 200 * 20

    try:
        for i in itertools.count():
            sh.col(i).width = col_width
    except ValueError:
        pass

wb.save('tryx.xls')
print 'Exported'