python 读写 excel 的简单封装

2010-12-24 11:42:26 461 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

python封装函数自动读写excel数据 python封装函数自动读写excel数据 import openpyxl class CaseData: pass class HandleExcel: def __init__(self, filename, sheetname): """获取文件名和表单名""" self.filename = filename sel... 阅读详情

恭请批评

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