CODEWITHSUNDEEP
pythonexcelxlsxwriter
smackenzie
smackenzie

Reputation: 3022

How to format a range of cells in python using xlsxwriter

How do I write a format to a range of cells.

What I am doing is looping over the column names in a list from oracle, and formatting the columns as dates, where the column name starts with "DT". But I also want to make the entire data range have borders.

I would like to really apply the date format to the columns, and then separately apply the borders...but the last format applies wins, and the application of the borders overwrites the date formatting on the columns.

Ideally I want to blast the data range with borders, and then apply date formats to the date columns, while retaining the borders.

Can you select a range and then apply formatting or do range intersections as you can in VBA?

  # Generate EXCEL File
        xl_filename = "DQ_Valid_Status_Check.xlsx"

        workbook = xlsxwriter.Workbook(xl_filename)

        # Add a bold format to use to highlight cells.
        bold = workbook.add_format({'bold': True})

        date_format = workbook.add_format(
            {'num_format': 'dd-mmm-yyyy hh:mm:ss'})

        border = workbook.add_format()
        border.set_bottom()
        border.set_top()
        border.set_left()
        border.set_right()

        worksheet_info = workbook.add_worksheet()
        worksheet_info.name = "Information"
        worksheet_info.write('A1', 'Report Description:', bold)
        worksheet_info.write('B1', 'ARIEL Data Quality Report for Checking Authorisation Status of Marketing Applications')
        worksheet_info.write('A2', 'Report Date:', bold)
        worksheet_info.write('B2', datetime.datetime.now(), date_format)

        worksheet_data = workbook.add_worksheet()
        worksheet_data.name = "DQ Report"
        worksheet_data.write_row('A1', col_names)

        for i in range(len(results)):
            print("result " + str(i) + ' of' + str(len(results)))
            print(results[i])
            worksheet_data.write_row('A' + str(i + 2), results[i])
            #worksheet_data.set_row(i + 2, None, border)




        # add borders
        for i in range(len(results)):
            worksheet_data.set_row(i + 2, None, border)

        # format date columns
        for i in range(len(col_names)):
            col_name = col_names[i]
            if col_name.startswith("DT"):
                print(col_name)
                worksheet_data.set_column(i, i, None, date_format)

        workbook.close()

Upvotes: 2

Views: 10003

Answers (2)

João Vitor Barbosa
João Vitor Barbosa

Reputation: 314

To apply borders to all columns at once you can do something like:

border = workbook.add_format({'border':2})
worksheet_info.set_column(first_col=0, last_col=10, cell_format=border)

And to retain the border format you can modify your date_format to:

date_format = workbook.add_format(
        {'num_format': 'dd-mmm-yyyy hh:mm:ss',
         'border': 2})

Upvotes: 1

Marie
Marie

Reputation: 167

According to the FAQ, it is not currently possible to format a range of cells at once, but a future feature might allow this.

You could create Format objects containing multiple format properties and apply your custom format to each cell as you write to it. See "Creating and using a Format Object".

Upvotes: 2

Related Questions