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 /> -
留个脚印
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login