python类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()