前面介绍了Python 利用xlrs读取Excel文件,今天接着介绍如何写Excel文件,用到的工具包为xlwt(by John Machin)。
在写入Excel表格之前,你必须初始化workbook对象,然后添加一个workbook对象。比如:
1 2 3 | 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对象,当你更改表单内容的时候,会有警告提示。
1 2 3 4 5 6 | 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:
1 2 3 | sheet2 = wbk.add_sheet( 'sheet 2' , cell_overwrite_ok = True ) sheet2.write( 0 , 0 , 'some text' ) sheet2.write( 0 , 0 , 'this should overwrite' ) |
这样你就可以更改表单2的内容了。
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 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.exampl
e文件中。
然后运行下面的代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | ''' 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/
联系客服