(补充)追加映射写入excel

有时候：查询的某个字段的值很多，需要分离，并写入到excel

# -- coding: utf-8 --

from xlwt import *
import os
import oss2
from xlrd import open_workbook
from xlutils.copy import copy
import datetime
import random
import sys
import traceback


PREFIX = 'http://'
class cexport_excel():

    """excel追加写入"""
    # excheader表头
    # datasrcset  二维元祖
    # path = 'hh.xls'
    def export_excel(self,path,excname,excheader,datasrcset,username, ossinfo,urlload):
        result = {}
        try:
            w = Workbook()
            sheet = w.add_sheet(excname)

            # 写头
            headercol = 0
            for header in excheader:
                sheet.write(0, headercol, str(header).decode('utf-8'))
                headercol += 1

            datarow = 1
            for datas in datasrcset:
                datacol = 0
                for data in datas:
                    if datacol == 4:
                        datacol_col = datacol
                        data = data.split('|')
                        print len(data)
                        for data_col in data:
                            urlall = (datacol_col + 1) - datacol
                            # 最多8个副图
                            if urlall > 8:
                                break
                            sheet.write(datarow, datacol_col, str(data_col).decode('utf-8'))
                            datacol_col += 1
                    else:
                        sheet.write(headerrow, datacol, str(data).decode('utf-8'))
                    headercol += 1
                datarow += 1
            w.save(path)

            osspath = username + '_' + datetime.now().strftime('%Y%m%d%H%M%S') + '_' + str(random.randint(0, 99)) + '.xls'
            auth = oss2.Auth(ossinfo['ACCESS_KEY_ID'], ossinfo['ACCESS_KEY_SECRET'])
            bucket = oss2.Bucket(auth, ossinfo['ENDPOINT_OUT'], ossinfo['BUCKETNAME_XLS'])
            bucket.put_object('%s/%s' % (urlload, osspath), open(path))
            result['osspath'] = PREFIX + ossinfo['BUCKETNAME_XLS'] + '.' + ossinfo['ENDPOINT_OUT'] + '/' + urlload + '/' + osspath
            result['errorcode'] = 0
            return result
        except Exception as ex:
            result['errorcode'] = -1
            result['errortext'] = '%s:Exception = %s ex=%s  __LINE__=%s' % (traceback.print_exc(), Exception, ex, sys._getframe().f_lineno)
            return result

简单的测试

t = ['头','w','d','c','u1','u2','u3','u4','u5','u6','u7','u8']
a = ((1,2,3,4,'h|g|k'),(1,3,5,7,'j|k|k'),(3,5,7,9,'h|m'),(3,5,7,9,'h'),(3,5,7,9,'a|b|c|d|e|f|g|h|i|j|k|h|pppp'))

w = Workbook()
sheet = w.add_sheet('enen')

# 写头
headercol = 0
for h in t:
    sheet.write(0, headercol, str(h).decode('utf-8'))
    headercol += 1


headerrow = 1
for heades in a:
    headercol = 0
    for header in heades:
        if headercol == 4:
            headercol_col = headercol
            header = header.split('|')
            print len(header)
            for header_col in header:
                urlall = (headercol_col + 1) - headercol
                #最多8个副图
                if urlall > 8:
                    break
                sheet.write(headerrow, headercol_col, str(header_col).decode('utf-8'))
                headercol_col += 1
        else:
            sheet.write(headerrow, headercol, str(header).decode('utf-8'))
        headercol += 1
    headerrow += 1
w.save('download.xls')

效果：