大江东去,浪淘尽,千古风流人物。故垒西边,人道是,三国周郎赤壁。乱石穿空,惊涛拍岸,卷起千堆雪。江山如画,一时多少豪杰。遥想公瑾当年,小乔初嫁了,雄姿英发。羽扇纶巾,谈笑间,樯橹灰飞烟灭。故国神游,多情应笑我,早生华发。人生如梦,一尊还酹江月。

南鱼

果然,我没有抓住重点

导航

python 数据生成excel导出(xlwt,wlsxwrite)

话不多说,看代码:

from xlwt import *
import StringIO
from apps.song.models import Song
def excel_ktvsong(request):

  """
导出excel表格
"""

  _id = request.GET.get('id', 0)

  list_obj = Song.objects.filter(is_delete__exact=False)
 # django orm  
  if list_obj:
 # 创建工作薄
    ws = Workbook(encoding='utf-8')

    w = ws.add_sheet(u"歌曲列表")

    w.write(0, 0, u"歌曲名称")

    w.write(0, 1, u"歌手")

    # 写入数据
    excel_row = 1

    for obj in list_obj:

      data_song = obj.song

      data_singer_name = obj.singer_name

      w.write(excel_row, 0, data_song)

      w.write(excel_row, 1, data_singer_name)


      excel_row += 1


    sio = StringIO.StringIO()

    ws.save(sio)

    sio.seek(0)

    response = HttpResponse(sio.getvalue(),     
    content_type='application/vnd.ms-excel')

    response['Content-Disposition'] = 'attachment;filename=%s.xls' % time.strftime('%Y%m%d%H%M%S')

    response.write(sio.getvalue())

    return response

  else:

    return HttpResponse("无数据")

上边我也是盗的,只不过当时有需求,数据量大只能用xlsxwriter,然后下边是我用xlsxwriter写的,边学边写,还请多多关照:

 import xlsxwriter,StringIO
        output = StringIO.StringIO()
        workbook = Workbook(output)
        if id:
            sheet_name = _(u"vvv")
            w = workbook.add_worksheet(sheet_name)
        else:
            sheet_name = _(u"vvvvvvv")
            w = workbook.add_worksheet(sheet_name)
""" 表格单元格样式"""
        head_cell_xf = workbook.add_format({
            'font_name': 'SimSun',
            'bold': True,
            'text_wrap': True,
            'valign': 'vcenter',
            'align': 'left',

            'bg_color': 'gray',
            'pattern': 1,
            'bottom': 1,
            'left': 1,
            'right': 1,
            'top': 1,
        })
        body_cell_xf = workbook.add_format({
            'font_name': 'SimSun',
            'text_wrap': True,
            'valign': 'vcenter',
            'align': 'left',

            'bg_color': 'gray',
            'pattern': 1,
            'bottom': 1,
            'left': 1,
            'right': 1,
            'top': 1,
        })

        w.write(0, 0, 'xxxx', head_cell_xf)
        w.write(0, 1, u'xxxx', head_cell_xf)
        w.set_column(1, 0, 18)
        w.set_column(1, 1, 100)
        excel_row = 1
        # cve_id = set()
        # i18n_name = set()
        data={}
        if id:
            res = xx.objects.get(id=id)
            res = res.vuls.split(';')
            for re in res:
                re = xx.objects.get(pk=xx)
                data[re.cve_id]=re.i18n_name[1]
                # w.write(excel_row, 0, re.cve_id,body_cell_xf)
                # w.write(excel_row, 1, re.i18n_name[1], body_cell_xf)
                # cve_id.add(re.cve_id)
                # cve_id.add(re.i18n_name[1])
                excel_row += 1
                progress_status = excel_row*100/len(res)  # 获取进度

        else:
            res = xx.objects.get(pk=xx)
            res = res.white_list.split(',')
            for re in res:
                re = Vuln.objects.get(vul_id=re)
                data[re.cve_id] = re.i18n_name[1]
            
                excel_row += 1
                progress_status = excel_row * 100 / len(res)   # 获取进度
        w.write_column('A2', data.keys(), body_cell_xf)
        w.write_column('B2', data.values(), body_cell_xf)
        workbook.close()
        response = HttpResponse(output.getvalue(),
                                content_type='application/octet-stream')
        response['Content-Disposition'] = 'attachment;filename=%s.xlsx' % xxx
        response.write(output.getvalue())
        progress_status = 0
        return response

 

posted on 2018-11-19 19:16  南鱼羁荒渡  阅读(10963)  评论(0编辑  收藏  举报