Python利用xlwt写Excel文件

最新推荐文章于 2024-01-19 09:45:30 发布

wst521

最新推荐文章于 2024-01-19 09:45:30 发布

阅读量954

点赞数 1

文章标签： excel python

基本部分

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

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

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

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

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

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

怎么样，很有意思吧。有兴趣的试试看。

参考：http://scienceoss.com/write-excel-files-with-python-using-xlwt/