Python xlsxwriter.Workbook() Examples

def __init__(self, path, engine=None, encoding=None, mode='w',
                 **engine_kwargs):
        # Use the xlwt module as the Excel writer.
        import xlwt
        engine_kwargs['engine'] = engine

        if mode == 'a':
            raise ValueError('Append mode is not supported with xlwt!')

        super(_XlwtWriter, self).__init__(path, mode=mode, **engine_kwargs)

        if encoding is None:
            encoding = 'ascii'
        self.book = xlwt.Workbook(encoding=encoding)
        self.fm_datetime = xlwt.easyxf(num_format_str=self.datetime_format)
        self.fm_date = xlwt.easyxf(num_format_str=self.date_format) 

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 __export_to_xlsx(self):
        r"""Export the results in the xlsx form.

        See Also:
                :func:`NiaPy.Runner.__generateExportName`

        """

        self.__create_export_dir()
        workbook = xlsxwriter.Workbook(self.__generate_export_name("xlsx"))
        worksheet = workbook.add_worksheet()
        row, col, nRuns = 0, 0, 0

        for alg in self.results:
            _, col = worksheet.write(row, col, alg), col + 1
            for bench in self.results[alg]:
                worksheet.write(row, col, bench)
                nRuns = len(self.results[alg][bench])
                for i in range(len(self.results[alg][bench])):
                    _, row = worksheet.write(row, col, self.results[alg][bench][i]), row + 1
                row, col = row - len(self.results[alg][bench]), col + 1
            row, col = row + 1 + nRuns, col - 1 + len(self.results[alg])

        workbook.close()
        logger.info("Export to XLSX completed!") 

def export_campaign_xlsx(self):
		"""Export the current campaign to an Excel compatible XLSX workbook."""
		dialog = extras.FileChooserDialog('Export Campaign To Excel', self)
		file_name = self.config['campaign_name'] + '.xlsx'
		response = dialog.run_quick_save(file_name)
		dialog.destroy()
		if not response:
			return
		destination_file = response['target_path']
		campaign_tab = self.tabs['campaign']
		workbook = xlsxwriter.Workbook(destination_file)
		title_format = workbook.add_format({'bold': True, 'size': 18})
		for tab_name, tab in campaign_tab.tabs.items():
			if not isinstance(tab, CampaignViewGenericTableTab):
				continue
			tab.export_table_to_xlsx_worksheet(workbook.add_worksheet(tab_name), title_format)
		workbook.close() 

def main():
    args = make_args()
    config = configparser.ConfigParser()
    utils.load_config(config, args.config)
    for cmd in args.modify:
        utils.modify_config(config, cmd)
    with open(os.path.expanduser(os.path.expandvars(args.logging)), 'r') as f:
        logging.config.dictConfig(yaml.load(f))
    model_dir = utils.get_model_dir(config)
    path, step, epoch = utils.train.load_model(model_dir)
    state_dict = torch.load(path, map_location=lambda storage, loc: storage)
    mapper = [(inflection.underscore(name), member()) for name, member in inspect.getmembers(importlib.machinery.SourceFileLoader('', __file__).load_module()) if inspect.isclass(member)]
    path = os.path.join(model_dir, os.path.basename(os.path.splitext(__file__)[0])) + '.xlsx'
    with xlsxwriter.Workbook(path, {'strings_to_urls': False, 'nan_inf_to_errors': True}) as workbook:
        worksheet = workbook.add_worksheet(args.worksheet)
        for j, (key, m) in enumerate(mapper):
            worksheet.write(0, j, key)
            for i, (name, variable) in enumerate(state_dict.items()):
                value = m(name, variable)
                worksheet.write(1 + i, j, value)
            if hasattr(m, 'format'):
                m.format(workbook, worksheet, i, j)
        worksheet.autofilter(0, 0, i, len(mapper) - 1)
        worksheet.freeze_panes(1, 0)
    logging.info(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 __init__(self, path, engine=None, mode='w', **engine_kwargs):
        # Use the openpyxl module as the Excel writer.
        from openpyxl.workbook import Workbook

        super(_OpenpyxlWriter, self).__init__(path, mode=mode, **engine_kwargs)

        if self.mode == 'a':  # Load from existing workbook
            from openpyxl import load_workbook
            book = load_workbook(self.path)
            self.book = book
        else:
            # Create workbook object with default optimized_write=True.
            self.book = Workbook()

            if self.book.worksheets:
                try:
                    self.book.remove(self.book.worksheets[0])
                except AttributeError:

                    # compat - for openpyxl <= 2.4
                    self.book.remove_sheet(self.book.worksheets[0]) 

def __init__(self, path, engine=None,
                 date_format=None, datetime_format=None, mode='w',
                 **engine_kwargs):
        # Use the xlsxwriter module as the Excel writer.
        import xlsxwriter

        if mode == 'a':
            raise ValueError('Append mode is not supported with xlsxwriter!')

        super(_XlsxWriter, self).__init__(path, engine=engine,
                                          date_format=date_format,
                                          datetime_format=datetime_format,
                                          mode=mode,
                                          **engine_kwargs)

        self.book = xlsxwriter.Workbook(path, **engine_kwargs) 

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 xlsx():
    '''Returns an xlsx file containing the entire dataset for the current
    workspace.'''
    sfp = BytesIO()
    with xlsxwriter.Workbook(sfp) as workbook:
        # create a worksheet for each table in the current workspace
        for table in recon.get_tables():
            rows = recon.query(f"SELECT * FROM {table}", include_header=True)
            columns = rows.pop(0)
            rows = columnize(columns, rows)
            add_worksheet(workbook, table, rows)
    sfp.seek(0)
    return send_file(
        sfp,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        as_attachment=True,
        attachment_filename=f"{current_app.config['WORKSPACE']}.xlsx"
    ) 

def __init__(self, path, engine=None, **engine_kwargs):
        # Use the openpyxl module as the Excel writer.
        from openpyxl.workbook import Workbook

        super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs)

        # Create workbook object with default optimized_write=True.
        self.book = Workbook()

        # Openpyxl 1.6.1 adds a dummy sheet. We remove it.
        if self.book.worksheets:
            try:
                self.book.remove(self.book.worksheets[0])
            except AttributeError:

                # compat
                self.book.remove_sheet(self.book.worksheets[0]) 

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 transfer_and_save(self, json_str):
        
        json_orders = json.loads(json_str)

        for key in json_orders:
                if key == 'OrderEnities':
                    json_order_lists = json_orders[key]

        book = xlsxwriter.Workbook('ctrip_order.xlsx')
        sheet = book.add_worksheet()
        sheet.write(0, 0, 'Date')
        sheet.write(0, 1, 'OrderDetails')
        sheet.write(0, 2, 'Price')

        for i in range(len(json_order_lists)):
            json_order = json_order_lists[i]
            sheet.write(i+1, 0, json_order['BookingDate'])
            sheet.write(i+1, 1, json_order['OrderName'])
            sheet.write(i+1, 2, json_order['OrderTotalPrice'])
        
        book.close()

    # download orders and save them in an excel file 

def transfer_and_save_bill(self, bill_json_str):
        
        bill_json = json.loads(bill_json_str)
        bill_json_month_lists = bill_json['data']

        book = xlsxwriter.Workbook('yidong_bill.xlsx')
        sheet = book.add_worksheet()
        sheet.write(0, 0, 'Date')
        sheet.write(0, 1, 'ItemDetails')
        sheet.write(0, 2, 'Price')
        sheet_count = 0

        for i in range(len(bill_json_month_lists)):
            bill_json_month = bill_json_month_lists[i]
            month = bill_json_month['billMonth']
            month_item_lists = bill_json_month['billMaterials']
            for j in range(len(month_item_lists)):
                bill_item = month_item_lists[j]['billMaterialInfos']
                if len(bill_item) != 0:
                    sheet_count += 1
                    sheet.write(sheet_count, 0, month)
                    sheet.write(sheet_count, 1, bill_item[0]['itemName'])
                    sheet.write(sheet_count, 2, bill_item[0]['itemValue']) 

def __init__(self, path, engine=None, mode='w', **engine_kwargs):
        # Use the openpyxl module as the Excel writer.
        from openpyxl.workbook import Workbook

        super(_OpenpyxlWriter, self).__init__(path, mode=mode, **engine_kwargs)

        if self.mode == 'a':  # Load from existing workbook
            from openpyxl import load_workbook
            book = load_workbook(self.path)
            self.book = book
        else:
            # Create workbook object with default optimized_write=True.
            self.book = Workbook()

            if self.book.worksheets:
                try:
                    self.book.remove(self.book.worksheets[0])
                except AttributeError:

                    # compat - for openpyxl <= 2.4
                    self.book.remove_sheet(self.book.worksheets[0]) 

def __init__(self, path, engine=None,
                 date_format=None, datetime_format=None, mode='w',
                 **engine_kwargs):
        # Use the xlsxwriter module as the Excel writer.
        import xlsxwriter

        if mode == 'a':
            raise ValueError('Append mode is not supported with xlsxwriter!')

        super(_XlsxWriter, self).__init__(path, engine=engine,
                                          date_format=date_format,
                                          datetime_format=datetime_format,
                                          mode=mode,
                                          **engine_kwargs)

        self.book = xlsxwriter.Workbook(path, **engine_kwargs) 

def module_run(self):
        filename = self.options['filename']
        # create a 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(f'SELECT * FROM "{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(f"All data written to '{filename}'.") 

def set_row_height(worksheet,
                   row_start,
                   row_stop,
                   row_height,
                   text_size=1):
    '''
    Sets the row height for all rows in range.

    Parameters
    ----------
    worksheet : xlsxwriter.Workbook.worksheet
    row_start : int
        first row (index)
    row_stop : int
        last row (index)
    '''
    for row in xrange(row_start, row_stop+1):
        worksheet.set_row(row, row_height*text_size) 

def save_profile_result(filename, table):
    import xlsxwriter
    workbook = xlsxwriter.Workbook(filename)
    worksheet = workbook.add_worksheet()
    keys = ["Name", "Self CPU total %", "Self CPU total", "CPU total %" , "CPU total", \
            "CPU time avg", "Number of Calls"]
    for j in range(len(keys)):
        worksheet.write(0,j,keys[j])

    lines = table.split("\n")
    for i in range(3,len(lines)-4):
        words = lines[i].split(" ")
        j = 0
        for word in words:
            if not word == "":
                worksheet.write(i-2, j, word)
                j += 1
    workbook.close() 

def export_xlsx(self, f_obj):
        workbook = xlsxwriter.Workbook(f_obj)
        # machines
        for title, headers, rows in self.export_sheets_data():
            ws = workbook.add_worksheet(title)
            row_idx = col_idx = 0
            for header in headers:
                ws.write_string(row_idx, col_idx, header)
                col_idx += 1
            for row in rows:
                row_idx += 1
                col_idx = 0
                for value in row:
                    if isinstance(value, (int, float)):
                        ws.write_number(row_idx, col_idx, value)
                    else:
                        if not isinstance(value, str):
                            value = str(value)
                        ws.write_string(row_idx, col_idx, value)
                    col_idx += 1
        workbook.close() 

def Open(self):
    """Creates a new workbook.

    Raises:
      IOError: if the specified output file already exists.
      OSError: if the specified output file already exists.
      ValueError: if the filename is not set.
    """
    if not self._filename:
      raise ValueError('Missing filename.')

    if os.path.isfile(self._filename):
      raise IOError((
          'Unable to use an already existing file for output '
          '[{0:s}]').format(self._filename))

    options = {
        'constant_memory': True,
        'strings_to_urls': False,
        'strings_to_formulas': False,
        'default_date_format': self._timestamp_format}
    self._workbook = xlsxwriter.Workbook(self._filename, options)
    self._sheet = self._workbook.add_worksheet('Sheet')
    self._current_row = 0 

def save_xlsx(self, df, path, worksheet='worksheet'):
        with xlsxwriter.Workbook(path, {'strings_to_urls': False, 'nan_inf_to_errors': True}) as workbook:
            worksheet = workbook.add_worksheet(worksheet)
            for j, key in enumerate(df):
                worksheet.write(0, j, key)
                try:
                    m = self.mapper[key]
                except (KeyError, AttributeError):
                    m = None
                if hasattr(m, 'get_format'):
                    fmt = m.get_format(workbook, worksheet)
                else:
                    fmt = None
                for i, value in enumerate(df[key]):
                    worksheet.write(1 + i, j, value, fmt)
                if hasattr(m, 'format'):
                    m.format(workbook, worksheet, i, j)
            worksheet.autofilter(0, 0, i, len(self.mapper) - 1)
            worksheet.freeze_panes(1, 0) 

def xlsx_write_rows(filename, rows):
    """
    Write XLSX rows from an iterable of rows.
    Each row must be an iterable of writeable values.
    Returns the number of rows written
    """
    workbook = xlsxwriter.Workbook(filename)
    worksheet = workbook.add_worksheet()
    # Write values
    nrows = 0
    for i, row in enumerate(rows):
        for j, val in enumerate(row):
            worksheet.write(i, j, val)
        nrows += 1
    # Cleanup
    workbook.close()
    return nrows 

def ExportResults(data,array_ip,output):
	# Start from the first cell. Rows and columns are zero indexed.
	row = 0
	col = 0
	if output == "js": 
		#Export the results in json format
		print "Exporting the results in an json"
		with open ('output.json','w') as f:
			json.dump(data,f)
	elif (output == "xl"):
		#Export the results in excel format
		print "\nExporting the results in an excel"
		# Create a workbook and add a worksheet.
		workbook = xlsxwriter.Workbook('output.xlsx')
		worksheet = workbook.add_worksheet()
		worksheet.write(row, col, "Domain")
		worksheet.write(row, col+1, "IP")
		row +=1
		for domain in data:
			col = 0
			worksheet.write(row, col, domain)
			row += 1
		#update row
		row = 1
		for ip in array_ip:
			col = 1
			worksheet.write(row, col, ip)
			row += 1
		#close the excel
		workbook.close()
	else:
		exit(1) 

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 write_question_label(worksheet,
                         label,
                         existing_format,
                         row,
                         col,
                         row_height,
                         row_wrap_trigger,
                         format_label_row=False,
                         view_sizes=None):
    '''
    Writes question labels

    Parameters
    ----------
    worksheet : xlsxwriter.Workbook.worksheet
    labels : list
        list of column labels. Last item is column heading
    existing_formats : list
        list of formats to use
    row : int
        row index
    col : int
        column index
    '''
    if len(label) < row_wrap_trigger:
        set_row_height(worksheet, row, row, row_height)
        worksheet.write(row, col, label, existing_format)
    else:
        worksheet.write(row, col, label, existing_format)
    if format_label_row:
        write_string = worksheet.write_string
        for col_idx in xrange(sum([yk[0][1] for yk in view_sizes])):
            write_string(
                row=row,
                col=col+col_idx+1,
                string='',
                cell_format=existing_format) 

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 ExportResults(domain,ip,export):

	print "\n"
	row = 0
	col = 0
	if export == "js": 
		#Export the results in json format
		print "Exporting the results in an json"
		with open ('output.json','w') as f:
			json.dump(ip,f)
	elif (export == "xl"):
		#Export the results in excel format
		print "\nExporting the results in an excel"
		# Create a workbook and add a worksheet.
		workbook = xlsxwriter.Workbook('output.xlsx')
		worksheet = workbook.add_worksheet()
		worksheet.write(row, col, "Domain")
		worksheet.write(row, col+1, "IP")
		row +=1
		for dom in domain:
			col = 0
			worksheet.write(row, col, dom)
			row += 1
		#update row
		row = 1
		for direction_ip in ip:
			col = 1
			worksheet.write(row, col, direction_ip)
			row += 1
		#close the excel
		workbook.close() 

def ExportResults(domain,ip,export):
	"""
	This function exports the results in xlsx format
	"""
	row = 0
	col = 0
	try:
		print ("\n")
		if export == "js": 
			#Export the results in json format
			print ("Exporting the results in an json")
			with open ('output.json','w') as f:
				json.dump(domain,f)
		elif (export == "xl"):
			#Export the results in excel format
			print ("\nExporting the results in an excel")
			# Create a workbook and add a worksheet.
			workbook = xlsxwriter.Workbook('output.xlsx')
			worksheet = workbook.add_worksheet()
			worksheet.write(row, col, "Domain")
			worksheet.write(row, col+1, "IP")
			row +=1
			for dom in domain:
				col = 0
				worksheet.write(row, col, dom)
				row += 1
			#update row
			row = 1
			for direction_ip in ip:
				col = 1
				worksheet.write(row, col, direction_ip)
				row += 1
			#close the excel
			workbook.close()
	except Exception as e:
		print ("Error in function ExportResults" + str(e)) 

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