New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Table Header formatting limitations #287

Closed
pmqs opened this Issue Aug 9, 2015 · 7 comments

Comments

2 participants
@pmqs

pmqs commented Aug 9, 2015

Here's what I'm running

$ python -c 'import xlsxwriter; print(xlsxwriter.__version__)'
0.7.3
Python 2.6.5
Excel 2013

I have an existing script that writes tabular data to a sheet. Would like to modify that code to use a "real" Excel table, but I'm running into trouble porting the formatting of the table header.

I note that the documentation on table_add/columns/format does state

Standard XlsxWriter Format object objects are used for this formatting. However, they should be 
limited to numerical formats. Overriding other table formatting may produce inconsistent results.

so I know I'm in unsupported territory. In the code below I see that attempting to change the formatting via table_add/columns/format does nothing. That's what I expected.

What I can do though is change the formatting of the header after the table_add. Unfortunately that triggers this warning when Excel starts up.

We found a problem with some content in 'tables.xlsx'. Do you want us to try to recover as 
much as we can...

The log for the repair is below.

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error182560_01.xml</logFileName>
    <summary>Errors were detected in file 'C:\tables.xlsx'</summary>
    <repairedRecords summary="Following is a list of repairs:">
    <repairedRecord>Repaired Records: Table from /xl/tables/table1.xml part (Table)</repairedRecord>
    </repairedRecords>
</recoveryLog>

Once Excel does the repair I get my header in D3 formatted as expected.

Is extra formatting of the Table header something that is possible? Alternatively, does anyone know of a way to do the formatting that doesn't trigger the Excel warning?

cheers
Paul

import xlsxwriter

workbook = xlsxwriter.Workbook('/home/paul/share/tables.xlsx')
worksheet = workbook.add_worksheet()
currency_format = workbook.add_format({'num_format': '$#,##0'})

# Some sample data for the table.
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears', 2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges', 500, 300, 200, 700],
]

# Set the columns widths.
worksheet.set_column('B:G', 12)

fmt = workbook.add_format({'bold': True, 'font_color': 'red', 'bg_color': 'white'})
fmt.set_align('center')
fmt.set_text_wrap()

# Table data can also be written separately, as an array or individual cells.

# Check what happens to the 
worksheet.write_row('B3', ["a","b", "c", "d"], fmt)

worksheet.write_row('B4', data[0])
worksheet.write_row('B5', data[1])
worksheet.write_row('B6', data[2])
worksheet.write_row('B7', data[3])


# Add a table to the worksheet.
worksheet.add_table('B3:F7', {'autofilter': 0, 
    "columns": [
        {"header": "H1", "format": fmt},
        {"header": "H2", },
        {},
        {},
        {},
        {}
        ]
    })

worksheet.write_comment('B3', 'This is a Comment')

worksheet.write('D3', "C\nMore", fmt)

workbook.close()
@pmqs

This comment has been minimized.

Show comment
Hide comment
@pmqs

pmqs Aug 9, 2015

Following up on my own post. Trying to change the format of the table header via table_add/columns/format is a complete non-starter. That's for formatting of the table body, not the header. Duh!

Anyway - main question remains. How to change the formatting of the table header that doesn't trigger an Excel warning.

pmqs commented Aug 9, 2015

Following up on my own post. Trying to change the format of the table header via table_add/columns/format is a complete non-starter. That's for formatting of the table body, not the header. Duh!

Anyway - main question remains. How to change the formatting of the table header that doesn't trigger an Excel warning.

@jmcnamara jmcnamara added the question label Aug 10, 2015

@jmcnamara jmcnamara self-assigned this Aug 10, 2015

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Aug 14, 2015

Owner

Is extra formatting of the Table header something that is possible?

No. That isn't something that is currently supported. You can open a separate feature request if you like.

Alternatively, does anyone know of a way to do the formatting that doesn't trigger the Excel warning?

The header names in Excel tables are used to reference columns in formulas so it isn't possible to just overwrite them with strings (like the data section of the table). Doing so will result in a mismatch between the column header name that the user sees and the header name that Excel uses internally. This results in the error/warning that you show above.

You can work around it by overwriting the column headers with the same name and a format. Something like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('tables.xlsx')
worksheet = workbook.add_worksheet()

# Some sample data for the table.
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears', 2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges', 500, 300, 200, 700],
]

# Set the columns widths.
worksheet.set_column('B:G', 12)

fmt = workbook.add_format({'bold': True,
                           'font_color': 'red',
                           'bg_color': 'white'})
fmt.set_align('center')
fmt.set_text_wrap()

worksheet.write_row('B4', data[0])
worksheet.write_row('B5', data[1])
worksheet.write_row('B6', data[2])
worksheet.write_row('B7', data[3])

# Add a table to the worksheet.
worksheet.add_table('B3:F7', {'autofilter': False,
                              'columns': [{'header': 'a'},
                                          {'header': 'b'},
                                          {'header': 'c'},
                                          {'header': 'd'},
                                          {},
                                         ]})

# Overwrite the table column header format. Note the same names as added
# to the table.
worksheet.write_row('B3', ["a", "b", "c", "d"], fmt)

workbook.close()

Output without warnings:

image

Owner

jmcnamara commented Aug 14, 2015

Is extra formatting of the Table header something that is possible?

No. That isn't something that is currently supported. You can open a separate feature request if you like.

Alternatively, does anyone know of a way to do the formatting that doesn't trigger the Excel warning?

The header names in Excel tables are used to reference columns in formulas so it isn't possible to just overwrite them with strings (like the data section of the table). Doing so will result in a mismatch between the column header name that the user sees and the header name that Excel uses internally. This results in the error/warning that you show above.

You can work around it by overwriting the column headers with the same name and a format. Something like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('tables.xlsx')
worksheet = workbook.add_worksheet()

# Some sample data for the table.
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears', 2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges', 500, 300, 200, 700],
]

# Set the columns widths.
worksheet.set_column('B:G', 12)

fmt = workbook.add_format({'bold': True,
                           'font_color': 'red',
                           'bg_color': 'white'})
fmt.set_align('center')
fmt.set_text_wrap()

worksheet.write_row('B4', data[0])
worksheet.write_row('B5', data[1])
worksheet.write_row('B6', data[2])
worksheet.write_row('B7', data[3])

# Add a table to the worksheet.
worksheet.add_table('B3:F7', {'autofilter': False,
                              'columns': [{'header': 'a'},
                                          {'header': 'b'},
                                          {'header': 'c'},
                                          {'header': 'd'},
                                          {},
                                         ]})

# Overwrite the table column header format. Note the same names as added
# to the table.
worksheet.write_row('B3', ["a", "b", "c", "d"], fmt)

workbook.close()

Output without warnings:

image

@pmqs

This comment has been minimized.

Show comment
Hide comment
@pmqs

pmqs Aug 15, 2015

Brilliant! Thanks John.

That sorted out one of the sheets, but another was failing with the same error when Excel starts up. I've tracked that down to the presence of line endings in the header names.

This example should trigger the issue.

import xlsxwriter

workbook = xlsxwriter.Workbook('tables.xlsx')

worksheet = workbook.add_worksheet()

# Some sample data for the table.
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears', 2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges', 500, 300, 200, 700],
]

# Set the columns widths.
worksheet.set_column('B:G', 12)

fmt = workbook.add_format({'bold': True,
                           'font_color': 'red',
                           'bg_color': 'white'})
fmt.set_align('center')
fmt.set_text_wrap()

worksheet.write_row('B4', data[0])
worksheet.write_row('B5', data[1])
worksheet.write_row('B6', data[2])
worksheet.write_row('B7', data[3])

# *** Note header "a" is now "a\nb"
worksheet.add_table('B3:F7', {'autofilter': False,
                              'columns': [{'header': "a\nb"},
                                          {'header': 'b'},
                                          {'header': 'c'},
                                          {'header': 'd'},
                                          {},
                                         ]})


# AND matching change the header "a" here as well
worksheet.write_row('B3', ["a\nb", "b", "c", "d"], fmt)

workbook.close()

pmqs commented Aug 15, 2015

Brilliant! Thanks John.

That sorted out one of the sheets, but another was failing with the same error when Excel starts up. I've tracked that down to the presence of line endings in the header names.

This example should trigger the issue.

import xlsxwriter

workbook = xlsxwriter.Workbook('tables.xlsx')

worksheet = workbook.add_worksheet()

# Some sample data for the table.
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears', 2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges', 500, 300, 200, 700],
]

# Set the columns widths.
worksheet.set_column('B:G', 12)

fmt = workbook.add_format({'bold': True,
                           'font_color': 'red',
                           'bg_color': 'white'})
fmt.set_align('center')
fmt.set_text_wrap()

worksheet.write_row('B4', data[0])
worksheet.write_row('B5', data[1])
worksheet.write_row('B6', data[2])
worksheet.write_row('B7', data[3])

# *** Note header "a" is now "a\nb"
worksheet.add_table('B3:F7', {'autofilter': False,
                              'columns': [{'header': "a\nb"},
                                          {'header': 'b'},
                                          {'header': 'c'},
                                          {'header': 'd'},
                                          {},
                                         ]})


# AND matching change the header "a" here as well
worksheet.write_row('B3', ["a\nb", "b", "c", "d"], fmt)

workbook.close()
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Aug 18, 2015

Owner

That one is probably a bug. You can work around it for now as follows:

worksheet.add_table('B3:F7', {'autofilter': False,
                              'columns': [{'header': "a_x000a_b"}, # This line is changed.
                                          {'header': 'b'},
                                          {'header': 'c'},
                                          {'header': 'd'},
                                          {},
                                         ]})


# AND matching change the header "a" here as well
worksheet.write_row('B3', ["a\nb", "b", "c", "d"], fmt)

Where _x000a_ is the Excel escape for \n.

Note, this workaround might break when I put in the real fix.

John

Owner

jmcnamara commented Aug 18, 2015

That one is probably a bug. You can work around it for now as follows:

worksheet.add_table('B3:F7', {'autofilter': False,
                              'columns': [{'header': "a_x000a_b"}, # This line is changed.
                                          {'header': 'b'},
                                          {'header': 'c'},
                                          {'header': 'd'},
                                          {},
                                         ]})


# AND matching change the header "a" here as well
worksheet.write_row('B3', ["a\nb", "b", "c", "d"], fmt)

Where _x000a_ is the Excel escape for \n.

Note, this workaround might break when I put in the real fix.

John

@pmqs

This comment has been minimized.

Show comment
Hide comment
@pmqs

pmqs Aug 19, 2015

Thanks - that worked fine. Will keep an eye out for a fix.

cheers & thanks for the help
Paul

pmqs commented Aug 19, 2015

Thanks - that worked fine. Will keep an eye out for a fix.

cheers & thanks for the help
Paul

jmcnamara added a commit that referenced this issue Oct 18, 2015

Add support for table header formatting.
Add support for table header formatting and a fix for wrapped lines
in the header.

Issue #287.
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 18, 2015

Owner

Hi,

I've added a fix for the "\n" issues and also the option to add formatting for the header in a column to the GitHub master.

Here is an example:

from xlsxwriter import Workbook

workbook = Workbook('table.xlsx')
worksheet = workbook.add_worksheet()
text_wrap = workbook.add_format({'text_wrap': 1})

worksheet.set_column('C:F', 10.288)
worksheet.set_row(2, 30)

worksheet.add_table('C3:F13',
                    {'columns': [{},
                                 {},
                                 {},
                                 { 'header': "Column\n4",
                                   'header_format': text_wrap }]}
)

workbook.close()

Output:

screenshot

Owner

jmcnamara commented Oct 18, 2015

Hi,

I've added a fix for the "\n" issues and also the option to add formatting for the header in a column to the GitHub master.

Here is an example:

from xlsxwriter import Workbook

workbook = Workbook('table.xlsx')
worksheet = workbook.add_worksheet()
text_wrap = workbook.add_format({'text_wrap': 1})

worksheet.set_column('C:F', 10.288)
worksheet.set_row(2, 30)

worksheet.add_table('C3:F13',
                    {'columns': [{},
                                 {},
                                 {},
                                 { 'header': "Column\n4",
                                   'header_format': text_wrap }]}
)

workbook.close()

Output:

screenshot

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 19, 2015

Owner

Fixed in version 0.7.7.

Owner

jmcnamara commented Oct 19, 2015

Fixed in version 0.7.7.

@jmcnamara jmcnamara closed this Oct 19, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment