python 读写 excel 的简单封装

[tim_spac]

恭请批评

# Encoding: utf-8

import xlrd
import xlwt

import datetime as DateTime


style_date = xlwt.easyxf(
    'font: name Times New Roman',
    num_format_str='YYYY-MM-DD' )

style_datetime = xlwt.easyxf(
    'font: name Times New Roman',
    num_format_str='YYYY-MM-DD HH:MM:SS' )

style_money = xlwt.easyxf(
    'font: name Times New Roman',
    num_format_str='####.00')

style_int = xlwt.easyxf(
    'font: name Times New Roman',
    num_format_str='####')

dttryformats = ('%Y-%m-%d %H:%M:%S',
    '%Y.%m.%d %H:%M:%S',
    '%Y/%m/%d %H:%M:%S',
    '%Y%m%d%H%M%S',
    '%Y%m%d%H:%M:%S',
    '%Y-%m-%d',
    '%Y.%m.%d',
    '%Y/%m/%d',
    '%Y%m%d',
    )

def trys2dt(s):
    for format in dttryformats:
        try:
            return DateTime.datetime.strptime(s,format)
        except:
            continue
    else:
        return s


def _tp(thetype, value): 
    return value if not thetype or type(value) == unicode else thetype(value)


def dt(xldate):
    return xlrd.xldate_as_tuple(xldate, datemode=0)


class ExcelFile:
    
    def __init__(self):
        self.handle, self.book = None, None
        self.sheets = {}
    
    def read(self, filename):
        self.handle = xlrd.open_workbook(filename)
        self.sheets = dict([(sheetname,self.handle.sheet_by_name(sheetname))
            for sheetname in self.handle.sheet_names()])
    
    def sheet_row(self, sheetname, rownumber = 0):
        if sheetname not in self.sheets: return None
        return self.sheets[sheetname].row_values(rownumber)
    
    def sheet_datas(self, sheetname, start_row, types = []):
        for r in xrange(start_row, self.sheets[sheetname].nrows, 1):
            try:
                yield map(_tp, types, self.sheets[sheetname].row_values(r))
            except Exception:
                pass
    
    def addsheet(self, name, title, data, styles=[]):
        ''' 添加Sheet页
        name: 指定sheet页标签
        title: 指定各列标题
        data: 数组数据
        styles: 指定各列格式
        '''
        if not self.book: self.book = xlwt.Workbook()
        if name not in self.sheets:
            self.sheets[name] = self.book.add_sheet(name)
        sheet = self.sheets[name]
        styles = ['Normal' if not item else item for item in styles]
        
        r = 0
        def write(d):
            i, c = d
            c = u'' if not c else (c if type(c)!=str else unicode(c,'gbk'))
            style = 'Normal' if i<0 or i>=len(styles) else styles[i]
            if style == 'DATE':
                sheet.write(r, i, c, style_date)
            elif style == 'DATETIME':
                if type(c)==DateTime.datetime:
                    sheet.write(r, i, c, style_datetime)
                else:
                    sheet.write(r, i, trys2dt(c), style_datetime)
            elif style == 'MONEY':
                sheet.write(r, i, c, style_money)
            elif style == 'INT':
                sheet.write(r, i, c, style_int)
            elif style:
                try:
                    sheet.write(r, i, c, style)
                except:
                    sheet.write(r, i, c)
            else:
                sheet.write(r, i, c)
        
        map( write, enumerate(title) )
        for rid, row in enumerate(data):
            r = rid+1
            map( write, enumerate(row) )

    def saveto(self, filename):
        self.book.save(filename)

[tim_spac]

# Encoding: utf-8
'''
Created on 2010-12-24

@author: tim.wang
'''

import datetime

from decimal import Decimal
from lib import excelFile


def gbks(s):
    return ('%s'%s).encode('gbk')

def money(s):
    return Decimal('%.2f'%s)

def dt(s):
    return datetime.datetime(*excelFile.dt(s))

if __name__ == '__main__':
    excelfile = excelFile.ExcelFile()
    excelfile.addsheet(
        name = 'summary',
        title = ['No','Name','Qty.','Amount','Date/Time'],
        data = [
            (1,u'测试人员',12,.6, datetime.datetime.now()),
            (2,'Bruce Wang',16,.8,'2010-10-31'),
            (3,'Hans Wang',10,.5,'2010-11-12 12:32:23'),
            ],
        styles = ['INT',None,'INT','MONEY','DATETIME'],
        )
    excelfile.saveto('Test.xls')
    
    excelfile = excelFile.ExcelFile()
    excelfile.read('Test.xls')
    titles = excelfile.sheet_row('summary')
    datas = excelfile.sheet_datas('summary',1,[int,str,int,money,dt])
    for r in datas:
        print ', '.join(map(gbks, r))

[iambic]

如果直接贴代码连代码的功能都不肯用自然语言介绍下，我也只有一句编译通过可以作为评论了。

[tim_spac]

[Quote=引用 2 楼 iambic 的回复:]
如果直接贴代码连代码的功能都不肯用自然语言介绍下，我也只有一句编译通过可以作为评论了。
[/Quote]中肯！

说明补充如下:
ExcelFile 类用于python简单地读写excel文件。
写入时，指定sheet名称，首行标题，表格数据数组，各列样式
读取时，指定sheet名称，可单独读取一行(通常用于读取首行标题)
或,指定sheet名称，读取从指定行开始的所有表格数据(后面的列表指定各列数据类型转换的方法)