Python利用xlwt写Excel文件 | 代码回音

londonKu 2012-10-03

展开全文

前面介绍了Python 利用xlrs读取Excel文件，今天接着介绍如何写Excel文件，用到的工具包为xlwt(by John Machin)。

基本部分

在写入Excel表格之前，你必须初始化workbook对象，然后添加一个workbook对象。比如：

import xlwt
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('sheet 1')

这样表单就被创建了,写入数据也很简单：

1 2	`# indexing is zero based, row then column` `sheet.write(0,1,'test text')`

之后，就可以保存文件(这里不需要想打开文件一样需要close文件)：

1	`wbk.save('test.xls')`

深入探索

worksheet对象，当你更改表单内容的时候，会有警告提示。

sheet.write(0,0,'test')
sheet.write(0,0,'oops')
 
# returns error:
# Exception: Attempt to overwrite cell:
# sheetname=u'sheet 1' rowx=0 colx=0

解决方式：使用cell_overwrite_ok=True来创建worksheet:

sheet2 =  wbk.add_sheet('sheet 2', cell_overwrite_ok=True)
sheet2.write(0,0,'some text')
sheet2.write(0,0,'this should overwrite')

这样你就可以更改表单2的内容了。

# Initialize a style
style = xlwt.XFStyle()
 
# Create a font to use with the style
font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
 
# Set the style's font to this new one you set up
style.font = font
 
# Use the style when writing
sheet.write(0, 0, 'some bold Times text', style)

xlwt 允许你每个格子或者整行地设置格式。还可以允许你添加链接以及公式。其实你可以阅读源代码，那里有很多例子：

dates.py, 展示如何设置不同的数据格式
hyperlinks.py, 展示如何创建超链接 (hint: you need to use a formula)
merged.py, 展示如何合并格子
row_styles.py, 展示如何应用Style到整行格子中.

例子

这里演示的数据并不是很容直接导入Excel:

20 Sep, 263, 1148,   0,   1,   0,   0,   1,   12.1,   13.9, 1+1, 19.9
20 Sep, 263, 1118,   0,   1,   0, 360,   0,   14.1,   15.3, 1+1, 19.9
20 Sep, 263, 1048,   0,   1,   0,   0,   0,   14.2,   15.1, 1+1, 19.9
20 Sep, 263, 1018,   0,   1,   0, 360,   0,   14.2,   15.9, 1+1, 19.9
20 Sep, 263, 0948,   0,   1,   0,   0,   0,   14.4,   15.3, 1+1, 19.9

第一个逗号之前数据表示日期，第二列表示今年的第几天(可忽略)，我们感兴趣的是第九列的温度数据。我们的目的是把感兴趣的数字写入Excel: 第一列为时间，第二列为温度。首先你要把上面的数据保存在一个weather.data.example文件中。

然后运行下面的代码：

'''
Script to convert awkwardly-formatted weather data
into an Excel spreadsheet using Python and xlwt.
'''
 
from datetime import datetime
import xlwt
 
# Create workbook and worksheet
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('temperatures')
 
# Set up a date format style to use in the
# spreadsheet
excel_date_fmt = 'M/D/YY h:mm'
style = xlwt.XFStyle()
style.num_format_str = excel_date_fmt
 
# Weather data has no year, so assume it's the current year.
year = datetime.now().year
 
# Convert year to a string because we'll be
# building a date string below
year = str(year)
 
# The format of the date string we'll be building
python_str_date_fmt = '%d %b-%H%M-%Y'
 
row = 0  # row counter
f = open('c:/baidu/weather.data.example')
for line in f:
    # separate fields by commas
    L = line.rstrip().split(',')
 
    # skip this line if all fields not present
    if len(L) < 12:
        continue
 
    # Fields have leading spaces, so strip 'em
    date = L[0].strip()
    time = L[2].strip()
 
    # Datatypes matter. If we kept this as a string
    # in Python, it would be a string in the Excel sheet.
    temperature = float(L[8])
 
    # Construct a date string based on the string
    # date format  we specified above
    date_string = date + '-' + time + '-' + year
 
    # Use the newly constructed string to create a
    # datetime object
    date_object = datetime.strptime(date_string,
                                    python_str_date_fmt)
 
    # Write the data, using the style defined above.
    sheet.write(row,0,date_object, style)
    sheet.write(row,1,temperature)
 
    row += 1
 
wbk.save('c:/baidu/reformatted.data.xls')