Cfrgtkky

I am trying to define the formatting that needs to be applied to each column of an excel spreadsheet based on the column name.

For example, if column name is 'count' then 'number_format' needs to be used. If column name is 'sale_date' then 'date_format' needs to be used.

number_format = workbook.add_format({'num_format': '0', 'font_size': 12})

date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 12})

Using the above two formats in the respective columns as shown below:

worksheet1.write('A1', 'count', number_format)

worksheet1.write('B1', 'sale_date', date_format)

Could I make this dynamic based on the column name instead of defining format by column label. Thanks

Update:

Loop that displays the header column in the excel spreadsheet

for data in title:

    worksheet.write(row, col, data, number_format)

    col += 1

Comment: date_format = workbook.add_format({'num_format': 'dd/mm/yy'}), shows the date column as unix number rather than a proper date.

Sample value shown is : 42668 instead of displaying "24-10-16".

This is default behavior defined by Windows Excel.

Read Excel for Windows stores dates by default as the number of days

Documentation: XlsxWriter Working with Dates and Time

Comment: ...that I could use the appropriate format based on column name (namely count, sale_date)

You can use worksheet.set_column() to set a Style for a whole Column.

Documentation: XlsxWriter worksheet.set_column()

Precondition: The Order of the Columns Name/Style must be in sync with your table.

E.g. count == 'A', sale_date == 'B' and so on...

from collections import OrderedDict



_styles = OrderedDict([('count',number_format), ('sale_date', date_format), ('total', number_format), ('text', string_format)])



for col, key in enumerate(_styles):

    A1_notation = '{c}:{c}'.format(c=chr(col + 65))

    worksheet.set_column(A1_notation, None, _styles[key])

    print("worksheet.set_column('{}', None, {})".format(A1_notation, _styles[key]))

Output:

worksheet.set_column('A:A', None, number_format)

worksheet.set_column('B:B', None, date_format)

worksheet.set_column('C:C', None, number_format)

worksheet.set_column('D:D', None, string_format)

For subsequent writes you don't need to assign a style, e.g. use

worksheet.write('A1', 123)  

will default to A:A number_format

Question: Could I make this dynamic based on the column name

You are not using "column name", it's called Cell A1 Notation.

Setup a mapping dict, for example:

style_map = {'A': number_format, 'B':date_format}

Usage:
Note: This will only work with single letter, from A to Z

def write(A1_notation, value):

    worksheet1.write(A1_notation, value, style_map[A1_notation[0]])

For Row-column notation (0, 0):

style_map = {'0': number_format, '1':date_format}

Usage:

def write(row, col, value):

    worksheet1.write(row, col, value, style_map[col])



from xlsxwriter.utility import xl_rowcol_to_cell



def write(A1_notation, value):

    worksheet1.write(A1_notation, value, style_map[xl_cell_to_rowcol(A1_notation)[1]])