django把页面数据导出excel文件

2019独角兽企业重金招聘Python工程师标准>>>

目前，需要把一个页面的值，导出来，用excel存放。如下图：

当点击查询的时候，会根据过滤条件显示出相应的值，此时如果点击导出的话，会生成一个excel文件，里面存放页面上的数据。

html代码如下：

<form role="form"  name="filter_form" method="get">
      <div class="content_head">
         <div>
            <br/><br/>
            <span>投放效果分析 :</span>
            <br/>
            <div class="key_word">
                <span style="float: left;font-size: 12px;line-height: 35px">关键字：</span>
                   <ul class="main_box" style="position:absolute;display:block;z-index:100;margin-left:82px;">
                       <li class="select_box">
                            {% if type == '1' %}
                                  <span class="img" style="margin-left:0;">活动编号</span>
                                <input type="hidden" name="type" class="type_text" value="1" />
                            {% else %}
                               <span class="img" style="margin-left:0;">活动名称</span>
                                <input type="hidden" name="type" class="type_text" value="0" />
                            {% endif %}
                           <ul class="son_ul">
                               <li value="0">活动名称</li>
                               <li value="1">活动编号</li>
                           </ul>
                       </li>
                   </ul>

                  &nbsp;&nbsp;&nbsp;
                  <input type="text" name="key" class="key_text" placeholder="请输入关键字"  value="{{ key }}" style="margin-left:200px;"/>
            </div>
         </div>
         <div>
            <div class="key_word">
               <span style="float: left;font-size: 12px;line-height: 35px">日期：</span>
               <div class="date">
                  <input type="text" name="time" placeholder="请选择日期区间" class="choose-date" value="{{ timereport }}"/>
               </div>

               <div class="date_detail" id="today">今天</div>
               <div class="date_detail" id="yesterday">昨天</div>
               <div class="date_detail" id="last7">最近7天</div>
               <div class="date_detail" id="last30">最近30天</div>
            </div>
         </div>

         <div class="key_button">
            <button type="submit" class="btn btn-default normal" style="float: left;margin-right:10px;" >查询</button>
{#                <a οnclick="textinput()" id="export" class="btn btn-default click" style="float: left;margin-right:10px;" >导出</a>#}

                <a οnclick="textinput()" id="ep">{# {% url 'datainput' %} #}
                <span class="blue"
                    style="border: 0;display: inline-block;
                    width: 98px;height: 35px;border-radius: 5px;
                    text-align: center;line-height: 35px;">导出
                </span>
            </a>
         </div>
      </div>
</form>

js代码如下：

function textinput(){
    var activity=$(".type_text").val()
    var gjz=$(".key_text").val()
    var dateTime=$("input[name=time]").val()
    $("#ep").find("span").click(function(){
        window.location.href="datainput?activity="+activity+"&gjz="+gjz+"&dateTime="+dateTime
    })
}

后端代码如下：

#导出excel表格数据
def datainput(request):
    type = request.GET.get('activity','')
    key = request.GET.get('gjz','')
    timereport =request.GET.get('dateTime','')
    if timereport != '':
        a = timereport[:10]
        b = timereport[-10:]+'/23/59/59'
        c = datetime.strptime(a, '%Y-%m-%d')
        d = datetime.strptime(b,'%Y-%m-%d/%H/%M/%S')
        if type == '0':
            if key != '':
                activity_list=Order.objects.filter(activity_id__start_date__gte=c).filter(activity_id__start_date__lte=d).filter(activity_id__name__contains=key).values_list( 'activity_id__name','name','start_time','end_time',
                                             'report_order__zhibo_click','report_order__zhibo_read','report_order__zhibo_call',
                                           'activity_id__start_date','activity_id__end_date', 'activity_id__advertiser_name')
            else:
                activity_list=Order.objects.filter(activity_id__start_date__gte=c).filter(activity_id__start_date__lte=d).values_list('activity_id__name','name','start_time','end_time',
                                             'report_order__zhibo_click','report_order__zhibo_read','report_order__zhibo_call',
                                             'activity_id__start_date','activity_id__end_date','activity_id__advertiser_name')
        else:
            if key != '':
                activity_list = Order.objects.filter(activity_id__start_date__gte=c).filter(activity_id__start_date__lte=d).filter(activity_id__id__contains=key).values_list( 'activity_id__name','name','start_time','end_time',
                                             'report_order__zhibo_click','report_order__zhibo_read','report_order__zhibo_call',
                                           'activity_id__start_date','activity_id__end_date', 'activity_id__advertiser_name')
            else:
                activity_list =Order.objects.filter(activity_id__start_date__gte=c).filter(activity_id__start_date__lte=d).values_list( 'activity_id__name','name','start_time','end_time',
                                             'report_order__zhibo_click','report_order__zhibo_read','report_order__zhibo_call',
                                          'activity_id__start_date','activity_id__end_date',  'activity_id__advertiser_name')

    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=reportdata.xls'  # 返回下载文件的名称(activity.xls)
    workbook = xlwt.Workbook(encoding='utf-8')  # 创建工作簿
    mysheet = workbook.add_sheet(u'活动')  # 创建工作页
    rows=activity_list
    cols = 10#每行的列
    aaa = ['活动名称','订单名称','投放日期','结束日期','直播点击数','直播订阅量','直播访问量','开始时间','结束时间','广告主名称']  #表头名
    for c in range(len(aaa)):
        mysheet.write(0,c,aaa[c])
    for r in range(0, len(rows)):      #对行进行遍历
        for c in range(cols):          #对列进行遍历
            mysheet.write(r+1,c,str(rows[r][c]))
            response = HttpResponse(
                content_type='application/vnd.ms-excel')  # 这里响应对象获得了一个特殊的mime类型,告诉浏览器这是个excel文件不是html
            response[
                'Content-Disposition'] = 'attachment; filename=reportdata.xls'  # 这里响应对象获得了附加的Content-Disposition协议头,它含有excel文件的名称,文件名随意,当浏览器访问它时,会以"另存为"对话框中使用它.
            workbook.save(response)
    return response

流程步骤：

（1）、html页面中的导出中定义一个事件，当点击导出的时候，触发该事件。

（2）、js代码中，主要取得活动名称，和关键字，以及时间值并传给后端进行处理。

（3）、在后端对传来的值进行过滤，取出来合适的值，然后把这些值写进一个excel里，并导出来。

最后结果如下图：

 

 

转载于:https://my.oschina.net/zhangyangyang/blog/719357