{"action":"created","issue":{"url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/287","labels_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/287/labels{/name}","comments_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/287/comments","events_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/287/events","html_url":"https://github.com/jmcnamara/XlsxWriter/issues/287","id":99922206,"number":287,"title":"Table Header formatting limitations","user":{"login":"pmqs","id":2691108,"avatar_url":"https://avatars.githubusercontent.com/u/2691108?v=3","gravatar_id":"","url":"https://api.github.com/users/pmqs","html_url":"https://github.com/pmqs","followers_url":"https://api.github.com/users/pmqs/followers","following_url":"https://api.github.com/users/pmqs/following{/other_user}","gists_url":"https://api.github.com/users/pmqs/gists{/gist_id}","starred_url":"https://api.github.com/users/pmqs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pmqs/subscriptions","organizations_url":"https://api.github.com/users/pmqs/orgs","repos_url":"https://api.github.com/users/pmqs/repos","events_url":"https://api.github.com/users/pmqs/events{/privacy}","received_events_url":"https://api.github.com/users/pmqs/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/jmcnamara/XlsxWriter/labels/question","name":"question","color":"cc317c"},{"url":"https://api.github.com/repos/jmcnamara/XlsxWriter/labels/ready%20to%20close","name":"ready to close","color":"bfe5bf"}],"state":"open","locked":false,"assignee":{"login":"jmcnamara","id":94267,"avatar_url":"https://avatars.githubusercontent.com/u/94267?v=3","gravatar_id":"","url":"https://api.github.com/users/jmcnamara","html_url":"https://github.com/jmcnamara","followers_url":"https://api.github.com/users/jmcnamara/followers","following_url":"https://api.github.com/users/jmcnamara/following{/other_user}","gists_url":"https://api.github.com/users/jmcnamara/gists{/gist_id}","starred_url":"https://api.github.com/users/jmcnamara/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jmcnamara/subscriptions","organizations_url":"https://api.github.com/users/jmcnamara/orgs","repos_url":"https://api.github.com/users/jmcnamara/repos","events_url":"https://api.github.com/users/jmcnamara/events{/privacy}","received_events_url":"https://api.github.com/users/jmcnamara/received_events","type":"User","site_admin":false},"milestone":null,"comments":3,"created_at":"2015-08-09T19:40:45Z","updated_at":"2015-08-15T15:55:39Z","closed_at":null,"body":"Here's what I'm running\r\n\r\n    $ python -c 'import xlsxwriter; print(xlsxwriter.__version__)'\r\n    0.7.3\r\n    Python 2.6.5\r\n    Excel 2013\r\n\r\nI 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.\r\n\r\nI note that the documentation on table_add/columns/format does state\r\n\r\n    Standard XlsxWriter Format object objects are used for this formatting. However, they should be \r\n    limited to numerical formats. Overriding other table formatting may produce inconsistent results.\r\n\r\nso 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.\r\n\r\nWhat I can do though is change the formatting of the header after the table_add. Unfortunately that triggers this warning when Excel starts up.\r\n\r\n    We found a problem with some content in 'tables.xlsx'. Do you want us to try to recover as \r\n    much as we can...\r\n\r\nThe log for the repair is below. \r\n\r\n    <?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"true\"?>\r\n    <recoveryLog xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">\r\n        <logFileName>error182560_01.xml</logFileName>\r\n        <summary>Errors were detected in file 'C:\\tables.xlsx'</summary>\r\n        <repairedRecords summary=\"Following is a list of repairs:\">\r\n        <repairedRecord>Repaired Records: Table from /xl/tables/table1.xml part (Table)</repairedRecord>\r\n        </repairedRecords>\r\n    </recoveryLog>\r\n\r\nOnce Excel does the repair I get my header in D3 formatted as expected.\r\n\r\nIs 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?\r\n\r\ncheers\r\nPaul\r\n\r\n    import xlsxwriter\r\n    \r\n    workbook = xlsxwriter.Workbook('/home/paul/share/tables.xlsx')\r\n    worksheet = workbook.add_worksheet()\r\n    currency_format = workbook.add_format({'num_format': '$#,##0'})\r\n    \r\n    # Some sample data for the table.\r\n    data = [\r\n        ['Apples', 10000, 5000, 8000, 6000],\r\n        ['Pears', 2000, 3000, 4000, 5000],\r\n        ['Bananas', 6000, 6000, 6500, 6000],\r\n        ['Oranges', 500, 300, 200, 700],\r\n    ]\r\n    \r\n    # Set the columns widths.\r\n    worksheet.set_column('B:G', 12)\r\n    \r\n    fmt = workbook.add_format({'bold': True, 'font_color': 'red', 'bg_color': 'white'})\r\n    fmt.set_align('center')\r\n    fmt.set_text_wrap()\r\n    \r\n    # Table data can also be written separately, as an array or individual cells.\r\n    \r\n    # Check what happens to the \r\n    worksheet.write_row('B3', [\"a\",\"b\", \"c\", \"d\"], fmt)\r\n    \r\n    worksheet.write_row('B4', data[0])\r\n    worksheet.write_row('B5', data[1])\r\n    worksheet.write_row('B6', data[2])\r\n    worksheet.write_row('B7', data[3])\r\n    \r\n    \r\n    # Add a table to the worksheet.\r\n    worksheet.add_table('B3:F7', {'autofilter': 0, \r\n        \"columns\": [\r\n            {\"header\": \"H1\", \"format\": fmt},\r\n            {\"header\": \"H2\", },\r\n            {},\r\n            {},\r\n            {},\r\n            {}\r\n            ]\r\n        })\r\n    \r\n    worksheet.write_comment('B3', 'This is a Comment')\r\n    \r\n    worksheet.write('D3', \"C\\nMore\", fmt)\r\n    \r\n    workbook.close()\r\n    \r\n\r\n"},"comment":{"url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/comments/131395755","html_url":"https://github.com/jmcnamara/XlsxWriter/issues/287#issuecomment-131395755","issue_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/287","id":131395755,"user":{"login":"pmqs","id":2691108,"avatar_url":"https://avatars.githubusercontent.com/u/2691108?v=3","gravatar_id":"","url":"https://api.github.com/users/pmqs","html_url":"https://github.com/pmqs","followers_url":"https://api.github.com/users/pmqs/followers","following_url":"https://api.github.com/users/pmqs/following{/other_user}","gists_url":"https://api.github.com/users/pmqs/gists{/gist_id}","starred_url":"https://api.github.com/users/pmqs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pmqs/subscriptions","organizations_url":"https://api.github.com/users/pmqs/orgs","repos_url":"https://api.github.com/users/pmqs/repos","events_url":"https://api.github.com/users/pmqs/events{/privacy}","received_events_url":"https://api.github.com/users/pmqs/received_events","type":"User","site_admin":false},"created_at":"2015-08-15T15:55:39Z","updated_at":"2015-08-15T15:55:39Z","body":"Brilliant! Thanks John.\r\n\r\nThat 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.\r\n\r\nThis example should trigger the issue.\r\n\r\n    import xlsxwriter\r\n    \r\n    workbook = xlsxwriter.Workbook('tables.xlsx')\r\n    \r\n    worksheet = workbook.add_worksheet()\r\n    \r\n    # Some sample data for the table.\r\n    data = [\r\n        ['Apples', 10000, 5000, 8000, 6000],\r\n        ['Pears', 2000, 3000, 4000, 5000],\r\n        ['Bananas', 6000, 6000, 6500, 6000],\r\n        ['Oranges', 500, 300, 200, 700],\r\n    ]\r\n    \r\n    # Set the columns widths.\r\n    worksheet.set_column('B:G', 12)\r\n    \r\n    fmt = workbook.add_format({'bold': True,\r\n                               'font_color': 'red',\r\n                               'bg_color': 'white'})\r\n    fmt.set_align('center')\r\n    fmt.set_text_wrap()\r\n    \r\n    worksheet.write_row('B4', data[0])\r\n    worksheet.write_row('B5', data[1])\r\n    worksheet.write_row('B6', data[2])\r\n    worksheet.write_row('B7', data[3])\r\n    \r\n    # *** Note header \"a\" is now \"a\\nb\"\r\n    worksheet.add_table('B3:F7', {'autofilter': False,\r\n                                  'columns': [{'header': \"a\\nb\"},\r\n                                              {'header': 'b'},\r\n                                              {'header': 'c'},\r\n                                              {'header': 'd'},\r\n                                              {},\r\n                                             ]})\r\n    \r\n\r\n    # AND matching change the header \"a\" here as well\r\n    worksheet.write_row('B3', [\"a\\nb\", \"b\", \"c\", \"d\"], fmt)\r\n    \r\n    workbook.close()\r\n\r\n\r\n"}}