{"action":"created","issue":{"url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/548","repository_url":"https://api.github.com/repos/jmcnamara/XlsxWriter","labels_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/548/labels{/name}","comments_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/548/comments","events_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/548/events","html_url":"https://github.com/jmcnamara/XlsxWriter/issues/548","id":348825583,"node_id":"MDU6SXNzdWUzNDg4MjU1ODM=","number":548,"title":"Issue writing empty (but formatted) cells","user":{"login":"typhoon71","id":1866248,"node_id":"MDQ6VXNlcjE4NjYyNDg=","avatar_url":"https://avatars0.githubusercontent.com/u/1866248?v=4","gravatar_id":"","url":"https://api.github.com/users/typhoon71","html_url":"https://github.com/typhoon71","followers_url":"https://api.github.com/users/typhoon71/followers","following_url":"https://api.github.com/users/typhoon71/following{/other_user}","gists_url":"https://api.github.com/users/typhoon71/gists{/gist_id}","starred_url":"https://api.github.com/users/typhoon71/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/typhoon71/subscriptions","organizations_url":"https://api.github.com/users/typhoon71/orgs","repos_url":"https://api.github.com/users/typhoon71/repos","events_url":"https://api.github.com/users/typhoon71/events{/privacy}","received_events_url":"https://api.github.com/users/typhoon71/received_events","type":"User","site_admin":false},"labels":[{"id":753729862,"node_id":"MDU6TGFiZWw3NTM3Mjk4NjI=","url":"https://api.github.com/repos/jmcnamara/XlsxWriter/labels/awaiting%20user%20feedback","name":"awaiting user feedback","color":"e892cf","default":false},{"id":23779652,"node_id":"MDU6TGFiZWwyMzc3OTY1Mg==","url":"https://api.github.com/repos/jmcnamara/XlsxWriter/labels/question","name":"question","color":"cc317c","default":true}],"state":"open","locked":false,"assignee":{"login":"jmcnamara","id":94267,"node_id":"MDQ6VXNlcjk0MjY3","avatar_url":"https://avatars3.githubusercontent.com/u/94267?v=4","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},"assignees":[{"login":"jmcnamara","id":94267,"node_id":"MDQ6VXNlcjk0MjY3","avatar_url":"https://avatars3.githubusercontent.com/u/94267?v=4","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":5,"created_at":"2018-08-08T17:42:34Z","updated_at":"2018-08-12T19:39:40Z","closed_at":null,"author_association":"NONE","body":"Hi,\r\n\r\nI am using Python version 3.6.4 and XlsxWriter 1.0.4; the issue happens when opening the generated xlsx with Excel version 2016 (Windows10) or 2013 (Windows7).\r\nTried running the code both with Pycharm and the Python IDLE, nothing changes (makes sense).\r\nTried running the code on different devices too, all the same.\r\n\r\nI am using XlsxWriter to create a xlsx file from a list of dictionaries (1 dict becames one row).\r\nEverything works fine till I try to write an empty but formatted cell: if that cell has any kind of format I get a visual glitch once I open it in Excel.\r\n\r\nBasically I open it in Excel and as soon I move the sheet (it's like 150 columns) some of the cells that should be empty show some garbled characters on them, characters that I recognize as part of other cells content; if I click on the cell the cell is empty, like it should be. It's like there's an overlay of characters that appear or disappear as soon I move the sheet right and left or zoom it, just to reappear later.\r\n\r\nI used:\r\n\r\n```python\r\nworksheet.write(i1, i2, '', frmt)          \r\nworksheet.write(i1, i2, None, frmt)\r\nworksheet.write_blank(i1, i2, None, frmt)\r\n```\r\n\r\nAll of them gives me the glitch. Using:\r\n\r\n```python\r\nworksheet.write(i1, i2, '')   \r\n```\r\nThere's no more visual glitch, but I lose the format.\r\n\r\nI need to know if there are any known issues with writing big xlsx files with a lot of blank formatted cells.\r\n\r\nHere is a cutdown of the full code, I hope it can help to understand what I'm doing. \r\n\r\n```python\r\nimport xlsxwriter\r\n\r\ndata_dict_list = [{'key1': 1, 'key2': 'gl', 'key3': True}, {'key1': 42, 'key2': 'hfds', 'key3': False}, ]\r\ndata_key_list = 'key1', 'key2', 'key3'\r\ncell_formats = {'align': 'left'}, {'align': 'left', 'bold': True}, {'align': 'center'}\r\n\r\n\r\nclass XlsxStuff:\r\n\r\n    def __init__(self):\r\n        self.workbook = None\r\n\r\n    def xlsx_open(self, xlsx_path):\r\n        self.workbook = xlsxwriter.Workbook(xlsx_path)\r\n\r\n    def sheet_write(self, sheet_name, data_set, key_list, formats):\r\n\r\n        worksheet = self.workbook.add_worksheet(sheet_name)\r\n\r\n        for i1 in range(len(data_set)):\r\n            for i2 in range(len(key_list)):\r\n                frmt = self.workbook.add_format(formats[i2])\r\n                if key_list[i2] in data_set[i1] and (data_set[i1])[key_list[i2]] is not None:\r\n                    worksheet.write(i1, i2, (data_set[i1])[key_list[i2]], frmt)\r\n                else:\r\n                    worksheet.write_blank(i1, i2, None, frmt)\r\n\r\n    def xlsx_close(self):\r\n        self.workbook.close()\r\n\r\n\r\nexcel = XlsxStuff()\t\r\nexcel.xlsx_open('excel_file.xlsx')\r\nexcel.sheet_write('DataSet1', data_dict_list, data_key_list, cell_formats)\r\nexcel.xlsx_close()\r\n```\r\n\r\nI'd like to point out that this issue happened to me in the past too, when I used a simpler 1 sheet output without the need of a class; back then I didn't need to format the empty cells so I just didn't write them as a workaround.\r\n\r\nThe actual data set is around 400+ dicts with 150 key/value pair each (so 400+ rows * 150 colums); I can't share the data unfortunately.\r\n\r\nI don't understand what is going wrong, xlsxwriter never had any issue before this one; I don't think I'm doing it wrong but if I am I'll be grateful if I can be helped.\r\n\r\nThanks a lot. any help will be appreciated. \r\n\r\nOh, and thak you for xlsxwriter, it's really a neat xlsx file writer (like: it works like I want it to XD)"},"comment":{"url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/comments/412366707","html_url":"https://github.com/jmcnamara/XlsxWriter/issues/548#issuecomment-412366707","issue_url":"https://api.github.com/repos/jmcnamara/XlsxWriter/issues/548","id":412366707,"node_id":"MDEyOklzc3VlQ29tbWVudDQxMjM2NjcwNw==","user":{"login":"jmcnamara","id":94267,"node_id":"MDQ6VXNlcjk0MjY3","avatar_url":"https://avatars3.githubusercontent.com/u/94267?v=4","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},"created_at":"2018-08-12T19:39:40Z","updated_at":"2018-08-12T19:39:40Z","author_association":"OWNER","body":"Thanks for the test case. I can't reproduce the issue with Excel 2007 for Windows or Excel 2011 for the Mac but I do see it in Excel 2015 for Windows.\r\n\r\nI'll investigate further. In the meantime can you confirm that the following variant of your program causes the same issue. It does for me with Excel 2015. It is slightly simpler that the original version which make it slightly easier to reproduce. Functionally it should be the same in terms of formatting.\r\n\r\n```python\r\nimport json\r\nimport xlsxwriter\r\n\r\nwith open('Merged_0.json') as json_data:\r\n    row_data = json.load(json_data)\r\nwith open('Merged_1.json') as json_data:\r\n    key_list = json.load(json_data)\r\nwith open('Merged_4.json') as json_data:\r\n    frmt_list = json.load(json_data)\r\n\r\nworkbook = xlsxwriter.Workbook('test2.xlsx')\r\nworksheet = workbook.add_worksheet('Label')\r\n\r\nfor i2 in range(len(key_list)):\r\n    frmt = workbook.add_format(frmt_list[i2])\r\n    worksheet.set_column(i2, i2, None, frmt)\r\n\r\nfor i1 in range(len(row_data)):\r\n    for i2 in range(len(key_list)):\r\n        if key_list[i2] in row_data[i1] and (row_data[i1])[key_list[i2]] is not None:\r\n            worksheet.write(i1, i2, (row_data[i1])[key_list[i2]])\r\n\r\nworkbook.close()\r\n```\r\n"}}
{"actor":{"display_login":"jmcnamara"}}