# -*- coding: utf-8 -*-
import time
import xlwt
from io import BytesIO
from django.http import HttpResponse
from JiWu.query import *

def export_excel(request):
    # 设置HTTPResponse的类型
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment;filename=order.xls'
    # 创建一个文件对象
    wb = xlwt.Workbook(encoding='utf8')
    # 创建一个sheet对象
    sheet = wb.add_sheet('picSum')
    columns = ["","","","",""]
    columnHeight = [1,1,1,1,1]

    # 设置文件头的样式,这个不是必须的可以根据自己的需求进行更改
    style_heading = xlwt.easyxf("""
            font:
                name Arial,
                colour_index white,
                bold on,
                height 0xA0;
            align:
                wrap off,
                vert center,
                horiz center;
            pattern:
                pattern solid,
                fore-colour 0x19;
            borders:
                left THIN,
                right THIN,
                top THIN,
                bottom THIN;
            """)

    # 写入文件标题
    sheet.write(0,0,'编号',style_heading)
    sheet.write(0,1,'名称',style_heading)
    sheet.write(0,2,'联系方式',style_heading)
    sheet.write(0,3,'公司',style_heading)
    sheet.write(0,4,'店铺',style_heading)
    sheet.write(0,5,'处理时间',style_heading)
    sheet.col(2).width = 3000
    sheet.col(3).width = 3000
    sheet.col(4).width = 5000
    sheet.col(5).width = 3000
    # 写入数据
    data_row = 1
    # UserTable.objects.all()这个是查询条件,可以根据自己的实际需求做调整.
    for i in select_many("select u.id,u.name,u.phone,s.company,s.shop,u.register from user u join shop s on u.belong_id=s.id"):
        # 格式化datetime
        # pri_time = i.pri_date.strftime('%Y-%m-%d')
        reg_time = time.strftime('%Y-%m-%d',time.localtime(i[5]))
        sheet.write(data_row,0,i[0])
        sheet.write(data_row,1,i[1])
        sheet.write(data_row,2,i[2])
        sheet.write(data_row,3,i[3])
        sheet.write(data_row,4,i[4])
        sheet.write(data_row,5,reg_time)
        data_row = data_row + 1

    # 写出到IO
    output = BytesIO()
    wb.save(output)
    # 重新定位到开始
    output.seek(0)
    response.write(output.getvalue())
    return response


def exportmysql(request):
    import codecs
    import csv
    # 指定csv请求回应
    response = HttpResponse(content_type='text/csv')

    results = select_many("select id,name,phone from user")
    # 声明一个csv的响应
    response['Content-Disposition'] = 'attachment; filename="asd.csv"'
    # csv的响应的编码格式声明
    response.write(codecs.BOM_UTF8)
    writer = csv.writer(response)
    for result in results:
        writer.writerow([obj.encode('utf-8') if type(obj)==u'中' else obj for obj in result])
        # i = i + 1
    response.close()
    return response