Odoo 读写excel
-
odoo经常要导入和导出数据，excel是比较常见的文档，读写excel，上代码
<br /># -*- coding: utf-8 -*- <br /># author:ghoti<br />import xlrd<br />import xlwt<br />import base64<br />import cStringIO<br />class Read_Excel:<br /> '''<br /> 根据索引获取Excel表格中的数据 参数:file_name：Excel文件路径;file_contents:二进制文件/字符串<br /> col_n：表头列名所在行数 ;del_n:表的后几行不读入; by_index：表的索引（默认第一个sheet）<br /> 返回表格中某一行作为key的字典列表,key为unicode类型<br /> 校验:不能有重复的项,不能有空项<br /> '''<br /> def __init__(self,file_contents=None,file_name=None,col_n=1,del_n=0,by_index=0,):<br /> self.file_name = file_name<br /> self.file_contents = file_contents<br /> self.col_n = col_n<br /> self.del_n = del_n<br /> self.by_index = by_index<br /> <br /> def __call__(self):<br /> try:<br /> data = xlrd.open_workbook(filename=self.file_name,file_contents=self.file_contents)<br /> except Exception,e:<br /> print str(e)<br /> return None<br /> col_n = self.col_n<br /> del_n = self.del_n<br /> by_index = self.by_index<br /> table = data.sheets()[by_index]<br /> nrows = table.nrows #行数<br /> #ncols = table.ncols #列数<br /> colnames = table.row_values(col_n-1) #某一行数据做字典的key<br /> #validate<br /> if colnames:<br /> repeat_colnames = set([str(i) for i in colnames if colnames.count(i)!=1])<br /> for i in colnames:<br /> if not i:<br /> raise NameError('Null colname exist')<br /> if repeat_colnames:<br /> alert_info = ';'.join(repeat_colnames)<br /> raise NameError('repeat colname exist : %s'%alert_info)<br /> rsp =[]<br /> for rownum in range(col_n,nrows-del_n):<br /> row = table.row_values(rownum)<br /> if row:<br /> app = {}<br /> for i in range(len(colnames)):<br /> app[colnames[i].strip()] = row[i]<br /> rsp.append(app)<br /> return rsp<br /><br />class Export_Excel: <br /> '''返回导出excel二进制数据,sheet_name为sheet名,headings为第一行,data为第二行后,2个列表的内容一一对应'''<br /> def __init__(self,headings,data,sheet_name='export_xls',file_name=None):<br /> self.sheet_name = sheet_name<br /> self.headings = headings<br /> self.data = data<br /> self.file_name = file_name<br /> <br /> def __call__(self):<br /> book = xlwt.Workbook()<br /> sheet = book.add_sheet(self.sheet_name)<br /> rowx = 0<br /> for colx, value in enumerate(self.headings):<br /> sheet.write(rowx, colx, value)<br /> sheet.set_panes_frozen(True) # frozen headings instead of split panes<br /> sheet.set_horz_split_pos(rowx+1) # in general, freeze after last heading row<br /> sheet.set_remove_splits(True) # if user does unfreeze, don't leave a split there<br /> for row in self.data:<br /> rowx += 1<br /> for colx, value in enumerate(row):<br /> sheet.write(rowx, colx, value.encode('utf-8').decode('utf-8'))<br /> buf = cStringIO.StringIO()<br /> if self.file_name:<br /> book.save(self.file_name)<br /> book.save(buf)<br /> out = base64.encodestring(buf.getvalue())<br /> buf.close()<br /> return out<br /><br />''' <br />##test_for_use<br /><br />r = Read_Excel(file_contents=base64.decodestring(form['import']),col_n=3,del_n=1)<br />trades = r()[:]<br />print trades[0][u'收货人姓名']<br />print len(tables)<br />for row in tables:<br /> print row<br /> for i in row:<br /> print i,row[i]<br /> <br />headings = [u'订单号',u'下单日期']<br />values = [['12345','2012-12-12'],['12346','2012-12-12']]<br />out = Export_Excel(headings,values)()<br />_result_fields['data']['default'] = out<br />'''<br />'''<br />from read_excel import Read_Excel<br />print Read_Excel(file_name='a.xls')()<br />[{'c_1':'a_1','c_2':'b_1'},{'c_1':'a_2','c_2':'b_2'}]<br />'''<br /> -
留个脚印
