Python xlsxwriter 模块，Workbook() 实例源码

def invalid_excel_file_path(tmpdir):
    test_file_path = tmpdir.join("invalid.xlsx")
    workbook = xlsxwriter.Workbook(str(test_file_path))

    write_worksheet(workbook.add_worksheet("testsheet1"), table=[
        ["", "", "", ""],
        ["", "a", "", "c"],
        ["", "aa", "ab", ""],
        ["", "", 1.1, "a"],
    ])

    worksheet = workbook.add_worksheet("testsheet2")

    workbook.close()

    return str(test_file_path)

def execute(self, context, filepath, sheets=None, workbook_handler=None):
        if filepath is None:
            # ???????????
            filepath = "/tmp/{}_{}.xlsx".format(
                int(time.time()), random.randint(100, 999))
            workbook = xlsxwriter.Workbook(filepath)
        elif filepath.startswith("memory:"):
            output = StringIO()
            workbook = xlsxwriter.Workbook(output, {'in_memory': True})
            context[filepath[len("memory:"):]] = output
        else:
            workbook = xlsxwriter.Workbook(filepath)

        for sheet in sheets:
            sheet(context, workbook)

        if workbook_handler:
            workbook_handler(workbook)

        workbook.close()
        return filepath

def open(self, file_name, **keywords):
        """
        Open a file for writing

        Please note that this writer configure xlsxwriter's BookWriter to use
        constant_memory by default.

        :param keywords: **default_date_format** control the date time format.
                         **constant_memory** if true, reduces the memory
                         footprint when writing large files. Other parameters
                         can be found in `xlsxwriter's documentation
                         <http://xlsxwriter.readthedocs.io/workbook.html>`_
        """
        keywords.setdefault('default_date_format', 'dd/mm/yy')
        keywords.setdefault('constant_memory', True)
        BookWriter.open(self, file_name, **keywords)

        self._native_book = xlsxwriter.Workbook(
            file_name, keywords
         )

def download_xlsx(filename, info):
    '''Çreate xlsx file for given info and download it '''
    output = StringIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet = workbook.add_worksheet()
    row_num = 0
    col_num = 0

    for row in info:
        for grid in row:
            worksheet.write(row_num, col_num, grid)
            col_num += 1
        col_num = 0
        row_num += 1
    workbook.close()
    output.seek(0)
    headers = Headers()
    headers.set('Content-Disposition', 'attachment', filename=filename)
    return Response(output.read(), mimetype='application/vnd.openxmlformats-'
                    'officedocument.spreadsheetml.sheet', headers=headers)

def __init__(self, output_filename="output.xlsx"):
        """ Generate XLSX """
        self.workbook = xlsxwriter.Workbook(output_filename)
        self.core_worksheet = self.workbook.add_worksheet("Core")
        self.core_alteration_worksheet = self.workbook.add_worksheet("Core Alteration")

        self.plugins_worksheet = self.workbook.add_worksheet("Plugins")
        self.plugins_vulns_worksheet = self.workbook.add_worksheet("Plugins Vulns")
        self.plugins_alteration_worksheet = self.workbook.add_worksheet("Plugins Alteration")

        self.themes_worksheet = self.workbook.add_worksheet("Themes")
        self.themes_vulns_worksheet = self.workbook.add_worksheet("Themes Vulns")
        self.themes_alteration_worksheet = self.workbook.add_worksheet("Themes Alteration")

        self.generate_heading()
        self.generate_formatting(self.workbook)

def save_excel(count_dict, file_name):
    book = xlsxwriter.Workbook(r'C:\Users\Administrator\Desktop\%s.xls' % file_name)
    tmp = book.add_worksheet()
    row_num = len(count_dict)
    for i in range(1, row_num):
        if i == 1:
            tag_pos = 'A%s' % i
            tmp.write_row(tag_pos, ['???', '??'])
        else:
            con_pos = 'A%s' % i
            k_v = list(count_dict[i-2])
            tmp.write_row(con_pos, k_v)
    chart1 = book.add_chart({'type': 'area'})
    chart1.add_series({
        'name': '=Sheet1!$B$1',
        'categories': '=Sheet1!$A$2:$A$80',
        'values':  '=Sheet1!$B$2:$B$80'
    })
    chart1.set_title({'name': '?????'})
    chart1.set_x_axis({'name': '???'})
    chart1.set_y_axis({'name': '???/??'})
    tmp.insert_chart('C2', chart1, {'x_offset': 25, 'y_offset': 10})

######################################################################################

def module_run(self):
        filename = self.options['filename']
        # create an new xlsx file
        with xlsxwriter.Workbook(filename, {'strings_to_urls': False}) as workbook:
            tables = self.get_tables()
            # loop through all tables in the database
            for table in tables:
                # create a worksheet for the table
                worksheet = workbook.add_worksheet(table)
                # build the data set
                rows = [tuple([x[0] for x in self.get_columns(table)])]
                rows.extend(self.query('SELECT * FROM "%s"' % (table)))
                # write the rows of data to the xlsx file
                for r in range(0, len(rows)):
                    for c in range(0, len(rows[r])):
                        worksheet.write(r, c, rows[r][c])
        self.output('All data written to \'%s\'.' % (filename))

def xls(self):
        from xlwt import Workbook

        filename = self.filename_front + '.xls'
        wb = Workbook(encoding='utf-8')  
        wb_sheet = wb.add_sheet("sheet1")
        for title in self.title_list:
            wb_sheet.write(0, self.title_list.index(title), title)

        for line in self.query_set :
            row_no = (self.query_set.index(line) + 1)
            print(line)
            col_no = 0
            for column in line[1:] :
                if col_no == len(line) - 2:
                    # 2016-10-17 15:21:33.348313+00:00
                    re.split('\.' , str(column))[0]
                wb_sheet.write(row_no, col_no, column)
                col_no += 1
                # wb.write(?, ?, ??)
        return filename

def valid_excel_file_1():
    file_path = "valid_underscore.xlsx"
    workbook = xlsxwriter.Workbook(str(file_path))

    worksheet = workbook.add_worksheet("sheet_a")
    table = [
        ["data", "_data", "da_ta", "data_"],
        [1, 0.0, "a", "aaaa"],
        [2, 0.1, "b", "bbbb"],
        [3, 0.2, "c", "cccc"],
    ]
    for row_idx, row in enumerate(table):
        for col_idx, item in enumerate(row):
            worksheet.write(row_idx, col_idx, item)

    workbook.close()

    return str(file_path)

def invalid_excel_file_1():
    file_path = "invalid.xlsx"
    workbook = xlsxwriter.Workbook(file_path)

    worksheet = workbook.add_worksheet("testsheet1")
    table = [
        ["", "", "", ""],
        ["", "a", "", "c"],
        ["", "aa", "ab", ""],
        ["", "", 1.1, "a"],
    ]
    for row_idx, row in enumerate(table):
        for col_idx, item in enumerate(row):
            worksheet.write(row_idx, col_idx, item)

    worksheet = workbook.add_worksheet("testsheet2")

    workbook.close()

    return file_path

def save_excel(count_dict, file_name):
    book = xlsxwriter.Workbook(r'E:\positions\%s.xls' % file_name)
    tmp = book.add_worksheet()
    row_num = len(count_dict)
    for i in range(1, row_num):
        if i == 1:
            tag_pos = 'A%s' % i
            tmp.write_row(tag_pos, ['???', '??'])
        else:
            con_pos = 'A%s' % i
            k_v = list(count_dict[i-2])
            tmp.write_row(con_pos, k_v)
    chart1 = book.add_chart({'type':'area'})
    chart1.add_series({
        'name' : '=Sheet1!$B$1',
        'categories' : '=Sheet1!$A$2:$A$80',
        'values' : '=Sheet1!$B$2:$B$80'
    })
    chart1.set_title({'name':'?????'})
    chart1.set_x_axis({'name': '???'})
    chart1.set_y_axis({'name': '??(/?)'})
    tmp.insert_chart('C2', chart1, {'x_offset':15, 'y_offset':10})
    book.close()

def createXcel():
    workbook = xlsxwriter.Workbook('Data.xlsx')
    title = workbook.add_format({"bold": True, "align": "center", "font_size": 14})
    norm  = workbook.add_format({"italic": True, "align": "center"})
    worksheet = workbook.add_worksheet()
    worksheet.set_column('B:B', 15)
    worksheet.set_column('C:C', 15)
    worksheet.set_column('D:D', 15)
    worksheet.set_column('E:E', 15)
    worksheet.write(1, 1, "User", title)
    worksheet.write(1, 2, "Drinks Made", title)
    worksheet.write(1, 3, "Drinks Drunk", title)
    worksheet.write(1, 4, "Ratio", title)
    sorteddb = sorted(database.data.items(), key=lambda x: tryDivide('(x[1]["drinks"]/x[1]["made"])', x))
    for index, user in enumerate(sorteddb):
        worksheet.write(index + 2, 0 , index + 1, norm)
        worksheet.write(index + 2, 1 , userInfo(user[0])['name'], norm)
        worksheet.write(index + 2, 2 , user[1]["made"], norm)
        worksheet.write(index + 2, 3 , user[1]["drinks"], norm)
        worksheet.write(index + 2, 4 , str(tryDivide('(x[1]["made"]/x[1]["drinks"])', user)), norm)
    worksheet.insert_image('F3', 'KoalaTea.jpg')
    workbook.close()

def createXcel():
    workbook = xlsxwriter.Workbook('Data.xlsx')
    title = workbook.add_format({"bold": True, "align": "center", "font_size": 14})
    norm  = workbook.add_format({"italic": True, "align": "center"})
    worksheet = workbook.add_worksheet()
    worksheet.set_column('B:B', 15)
    worksheet.set_column('C:C', 15)
    worksheet.set_column('D:D', 15)
    worksheet.set_column('E:E', 15)
    worksheet.write(1, 1, "User", title)
    worksheet.write(1, 2, "Drinks Made", title)
    worksheet.write(1, 3, "Drinks Drunk", title)
    worksheet.write(1, 4, "Ratio", title)
    sorteddb = sorted(database.data.items(), key=lambda x: tryDivide('(x[1]["drinks"]/x[1]["made"])', x))
    for index, user in enumerate(sorteddb):
        worksheet.write(index + 2, 0 , index + 1, norm)
        worksheet.write(index + 2, 1 , userInfo(user[0])['name'], norm)
        worksheet.write(index + 2, 2 , user[1]["made"], norm)
        worksheet.write(index + 2, 3 , user[1]["drinks"], norm)
        worksheet.write(index + 2, 4 , str(tryDivide('(x[1]["made"]/x[1]["drinks"])', user)), norm)
    worksheet.insert_image('F3', 'KoalaTea.jpg')
    workbook.close()

def _generate_xlsx(self):
        stream = io.BytesIO()
        workbook = xlsxwriter.Workbook(stream, {
            'in_memory': True,
            'strings_to_formulas': False
        })
        worksheet = workbook.add_worksheet()

        worksheet.write(0, 0, _('Form Field'))
        worksheet.write(0, 1, _('Response'))

        field_values = self.kwargs.get('field_values')
        for rownum, row in enumerate(field_values.items(), start=1):
            worksheet.write(rownum, 0, row[0])
            worksheet.write(rownum, 1, self._fix_newline_if_string(row[1]))

        workbook.close()
        return stream.getvalue()

def get(self, request, *args, **kwargs):
        response = HttpResponse(
            content_type='application/vnd.openxmlformats-officedocument'
                         '.spreadsheetml.sheet')
        response['Content-Disposition'] = \
            'attachment; filename="%s"' % self.get_filename()

        workbook = xlsxwriter.Workbook(response, {
            'in_memory': True,
            'strings_to_formulas': False
        })
        worksheet = workbook.add_worksheet()

        for colnum, field in enumerate(self.get_header()):
            worksheet.write(0, colnum, field)

        for rownum, row in enumerate(self.export_rows(), start=1):
            for colnum, field in enumerate(row):
                worksheet.write(rownum, colnum, self._clean_field(field))

        workbook.close()

        return response

def module_run(self):
        filename = self.options['filename']
        # create an new xlsx file
        with xlsxwriter.Workbook(filename, {'strings_to_urls': False}) as workbook:
            tables = self.get_tables()
            # loop through all tables in the database
            for table in tables:
                # create a worksheet for the table
                worksheet = workbook.add_worksheet(table)
                # build the data set
                rows = [tuple([x[0] for x in self.get_columns(table)])]
                rows.extend(self.query('SELECT * FROM "%s"' % (table)))
                # write the rows of data to the xlsx file
                for r in range(0, len(rows)):
                    for c in range(0, len(rows[r])):
                        worksheet.write(r, c, rows[r][c])
        self.output('All data written to \'%s\'.' % (filename))

def save_excel(count_dict, file_name):
    book = xlsxwriter.Workbook(r'C:\Users\Administrator\Desktop\%s.xls' % file_name)
    tmp = book.add_worksheet()
    row_num = len(count_dict)
    for i in range(1, row_num):
        if i == 1:
            tag_pos = 'A%s' % i
            tmp.write_row(tag_pos, ['???', '??'])
        else:
            con_pos = 'A%s' % i
            k_v = list(count_dict[i-2])
            tmp.write_row(con_pos, k_v)
    chart1 = book.add_chart({'type': 'area'})
    chart1.add_series({
        'name': '=Sheet1!$B$1',
        'categories': '=Sheet1!$A$2:$A$80',
        'values':  '=Sheet1!$B$2:$B$80'
    })
    chart1.set_title({'name': '?????'})
    chart1.set_x_axis({'name': '???'})
    chart1.set_y_axis({'name': '???/??'})
    tmp.insert_chart('C2', chart1, {'x_offset': 25, 'y_offset': 10})

######################################################################################

def dict_to_xlsx(d,fname,headers):

        import xlsxwriter

        xlsx_file = xlsxwriter.Workbook(fname)
        xlsx_worksheet = xlsx_file.add_worksheet()
        bold = xlsx_file.add_format({'bold': True})

        for i,h in enumerate(headers):
            xlsx_worksheet.write(0,i,h,bold)

        keys = list(d.keys())
        keys.sort()

        for i, key in enumerate(keys):
            xlsx_worksheet.write(i+1,0,key)
            for j in range(len(d[key])):
                xlsx_worksheet.write(i+1,j+1,d[key][j])

def get(self, request, *args, **kwargs):
        response = HttpResponse(
            content_type='application/vnd.openxmlformats-officedocument'
                         '.spreadsheetml.sheet')
        response['Content-Disposition'] = \
            'attachment; filename="%s"' % self.get_filename()

        workbook = xlsxwriter.Workbook(response, {'in_memory': True})
        worksheet = workbook.add_worksheet()

        for col, field in enumerate(self.get_header()):
            worksheet.write(0, col, field)

        for rownum, row in enumerate(self.export_rows(), start=1):
            for col, field in enumerate(row):
                worksheet.write(rownum, col, field)

        workbook.close()

        return response

def __enter__(self):
        """Enter from a context manager."""
        self.filepath = self.gen_tmpfilename()
        self.workbook = xlsxwriter.Workbook(self.filepath)
        self.worksheet = self.workbook.add_worksheet()

        self.header_format = self.workbook.add_format({'bold': True, 'bg_color': '#C9C9C9'})
        self.footer_format = self.workbook.add_format({'bold': True, 'bg_color': '#DDDDDD'})

        self.current_row = 0

        self.line_widths = {}

        self.has_header = False

        return self

def runnerCase():
    starttime = datetime.now()
    init()
    suite = unittest.TestSuite()
    suite.addTest(ParametrizedTestCase.parametrize(LoginTest))
    suite.addTest(ParametrizedTestCase.parametrize(SubmitExecResultTest))
    suite.addTest(ParametrizedTestCase.parametrize(SendEmailTest))
    unittest.TextTestRunner(verbosity=2).run(suite)
    endtime = datetime.now()
    print(u"???"+str((endtime - starttime).seconds) + "?")

    info = readInfo(PATH("../Log/info.log"))
    workbook = xlsxwriter.Workbook('report.xlsx')
    worksheet = workbook.add_worksheet("????")
    re = OperateReport(wd=workbook)
    re.report(worksheet, data=info)
    destroy()

def randomxls (path) :

    numxls = (randint(2000,5000))

    for i in range(10):

        name = path + ''.join([random.choice(string.ascii_letters + string.digits) for n in xrange(randint(5,15))]) + ".xlsx"

        workbook = xlsxwriter.Workbook(name)
        worksheet = workbook.add_worksheet()


        numrows = (randint(100,500))

        for i in range(numrows):

            coord = 'A' + str(i)

            textinrow = ''.join([random.choice(string.ascii_letters + string.digits) for n in xrange(randint(5,15))])

            worksheet.write(coord , textinrow)


        workbook.close()

        for i in range(numxls):

            dupli =  path + ''.join([random.choice(string.ascii_letters + string.digits) for n in xrange(randint(5,15))]) + ".xlsx"

            copyfile(name, dupli)


#PDF Files

def __init__(self, public_client, ticker):
        self._client = public_client
        self._ticker = ticker
        self._workbook = xlsxwriter.Workbook(str(self._ticker) + '-HISTORICAL.xlsx')
        self._worksheet = self._workbook.add_worksheet()
        self._rowCount = 0
        self._lastTick = ""
        self._lastTickTime = 0

def saveContactFile(memberList):
    _data = [(u'??', u'???', u'???', u'???', u'??', u'??', u'??', u'??', ' ')]
    for contact in memberList:
        if not isPerson(contact):
            continue
        info = (
            removeEmoji(contact['NickName']),
            removeEmoji(contact['RemarkName']),
            pickScreenName(contact['NickName'], contact['RemarkName']),
            contact['Alias'],
            convertGender(contact['Sex']),
            contact['Province'],
            contact['City'],
            removeEmoji(contact['Signature']),
            formatQuanPin(contact['PYQuanPin'], contact['RemarkPYQuanPin'])
        )
        _data.append(info)
    _data.sort(key=lambda x: x[-1])
    _data = [e[:-1] for e in _data]
    filename = u'%s_????_%s.xlsx' % (
        removeEmoji(w.nickName),
        strftime('%Y%m%d-%H%M%S', localtime()),
    )
    workbook = xlsxwriter.Workbook(filename)
    worksheet = workbook.add_worksheet()
    row = 0
    col = 0
    for nickName, remarkName, screenName, alias, gender, province, city, signature in OrderedSet(_data):
        worksheet.write(row, col, nickName)
        worksheet.write(row, col + 1, remarkName)
        worksheet.write(row, col + 2, screenName)
        worksheet.write(row, col + 3, alias)
        worksheet.write(row, col + 4, gender)
        worksheet.write(row, col + 5, province)
        worksheet.write(row, col + 6, city)
        worksheet.write(row, col + 7, signature)
        row += 1
    workbook.close()
    print 'total: %d' % (row - 1)
    return

def saveGroupFile(groups, firends):
    friendsUserName = [contact['UserName'] for contact in firends]
    for group in groups:
        gMemberList = queryGMemberList(group['UserName'])
        _data = [(u'??', u'???', u'???', u'????', u'???', u'??', u'??', u'??', u'??')]
        for contact in gMemberList:
            info = (
                removeEmoji(contact['NickName']),
                contact['Alias'],
                removeEmoji(contact['DisplayName']),
                isFriend(friendsUserName, contact['UserName']),
                removeEmoji(contact['RemarkName']),
                convertGender(contact['Sex']),
                contact['Province'],
                contact['City'],
                removeEmoji(contact['Signature'])
            )
            _data.append(info)
        filename = u'%s_???_%s.xlsx' % (
            removeEmoji(group['NickName']),
            strftime('%Y%m%d-%H%M%S', localtime()),
        )
        workbook = xlsxwriter.Workbook(filename)
        worksheet = workbook.add_worksheet()
        row = 0
        col = 0
        for nickName, alias, displayName, friend, remarkName, gender, province, city, signature in _data:
            worksheet.write(row, col, nickName)
            worksheet.write(row, col + 1, alias)
            worksheet.write(row, col + 2, displayName)
            worksheet.write(row, col + 3, friend)
            worksheet.write(row, col + 4, remarkName)
            worksheet.write(row, col + 5, gender)
            worksheet.write(row, col + 6, province)
            worksheet.write(row, col + 7, city)
            worksheet.write(row, col + 8, signature)
            row += 1
        workbook.close()
        print '%s: %d' % (removeEmoji(group['NickName']), row - 1)
    return

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.StringIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = io.StringIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = StringIO.StringIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = StringIO.StringIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def __writexls__(self, filename="output.xlsx", outputdict={}):

        if outputdict is None:
            raise ValueError('An output argument was expected')

        if not isinstance(outputdict, dict):
            raise TypeError('Was expecting a dictionary for outputdict but got a %s' % type(dict))

        extension = '.xlsx'
        if not filename.endswith(extension):
            filename += extension

        #TODO check location and filename are valid

        workbook = xlsxwriter.Workbook(filename)

        for sheet, grid in sorted(outputdict.items()):
            worksheet = workbook.add_worksheet(name=sheet[:32]) #worksheet name cannot be more than 32 chars long
            if not isinstance(grid, Matrix):
                raise TypeError('Expected a Matrix but got a %s' % type(grid))
            for i, row in enumerate(grid):
                worksheet.write_row(i, 0, row)

        workbook.close()

        return True

def ExportResults(data,cms,export):
    #var's of excel
    # Start from the first cell. Rows and columns are zero indexed.
    row = 0
    col = 0
    i = 0
    #json
    if (export == 1):
        print "Exporting the results in a json"
        for domain in data:
            for j in cms:
                filename = "output_domains" + code + ".json"
                with open(filename, 'w') as f:
                    json.dump(domain,j, f)
    #excel
    if (export ==2):
        print "Exporting the results in an excel"
        # Create a workbook and add a worksheet.
        workbook = xlsxwriter.Workbook('Status_domains.xlsx')
        worksheet = workbook.add_worksheet()
        worksheet.write(row, col, "Domain")
        worksheet.write(row, col+1, "cms")
        row += 1
        # Iterate over the data and write it out row by row.
        for domain in data:
                col = 0
                worksheet.write(row, col, domain)
                worksheet.write(row, col+1, cms[i])
                row += 1
                i += 1
        #close the excel
        workbook.close()

def __init__(self, filename):
        self.workbook = xlsxwriter.Workbook(filename)

def __init__(self, filename):
        self.__fileName = filename
        try:
            xlrd.open_workbook(filename)
        except:
            work_book = xlwt.Workbook()
            work_sheet = work_book.add_sheet("Sheet1")
            work_book.save(filename)

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def save_contacts_info(self):
        import xlsxwriter
        from PIL import Image
        from io import BytesIO
        workbook = xlsxwriter.Workbook(os.path.join(self.temp_pwd,'contact_list.xlsx')) #?????
        '''
        ?????sheet:
            sheet1
        '''
        sheet1 = workbook.add_worksheet() #??sheet
        row0 = [u'NickName', u'HeadImgUrl', u'RemarkName']

        sheet1.set_column('A:C', 18)
        #?????
        for i in range(0,len(row0)):
            sheet1.write(0,i,row0[i])

        for i in range(0, len(self.contact_list)):
            try:
                sheet1.set_row(i+1, 100)
                sheet1.write(i+1,0,self.contact_list[i]['NickName'])
                # ???????https://wx2.qq.com
                url ="https://wx.qq.com" + self.contact_list[i]['HeadImgUrl']
                print(url)
                r = self.session.get(url)
                pic = Image.open(BytesIO(r.content))
                pic.save(os.path.join(self.temp_pwd, 'img', self.contact_list[i]['NickName']+'.jpg'))
                #sheet1.write(i+1,1,self.contact_list[i]['HeadImgUrl'])
                sheet1.insert_image(i+1,1,os.path.join(self.temp_pwd, 'img', self.contact_list[i]['NickName']+'.jpg'))
                sheet1.write(i+1,2,self.contact_list[i]['RemarkName'])
            except Exception as e:
                print(e)
        workbook.close()

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = StringIO.StringIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = StringIO.StringIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def make_xlsx(rows):
    tmp_path = tempfile.mktemp()
    workbook = xlsxwriter.Workbook(tmp_path, {'strings_to_numbers': False,
                                              'strings_to_formulas': False,
                                              'strings_to_urls': False})
    worksheet = workbook.add_worksheet()
    worksheet.write_row(0, 0, rows.headers)
    for i, row in enumerate(rows):
        worksheet.write_row(i + 1, 0, row)
    workbook.close()
    contents = open(tmp_path, "rb").read()
    os.remove(tmp_path)
    return contents