{"action":"created","issue":{"url":"https://api.github.com/repos/python-excel/xlrd/issues/207","repository_url":"https://api.github.com/repos/python-excel/xlrd","labels_url":"https://api.github.com/repos/python-excel/xlrd/issues/207/labels{/name}","comments_url":"https://api.github.com/repos/python-excel/xlrd/issues/207/comments","events_url":"https://api.github.com/repos/python-excel/xlrd/issues/207/events","html_url":"https://github.com/python-excel/xlrd/issues/207","id":215068329,"number":207,"title":"Cells with conditional formulas with an empty result return 0 instead of an empty string","user":{"login":"mspacek","id":799467,"avatar_url":"https://avatars3.githubusercontent.com/u/799467?v=3","gravatar_id":"","url":"https://api.github.com/users/mspacek","html_url":"https://github.com/mspacek","followers_url":"https://api.github.com/users/mspacek/followers","following_url":"https://api.github.com/users/mspacek/following{/other_user}","gists_url":"https://api.github.com/users/mspacek/gists{/gist_id}","starred_url":"https://api.github.com/users/mspacek/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mspacek/subscriptions","organizations_url":"https://api.github.com/users/mspacek/orgs","repos_url":"https://api.github.com/users/mspacek/repos","events_url":"https://api.github.com/users/mspacek/events{/privacy}","received_events_url":"https://api.github.com/users/mspacek/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-03-17T17:17:09Z","updated_at":"2017-03-17T23:03:49Z","closed_at":null,"body":"Hello! I have an .xls sheet with a formula that's been extended part way down a column. The formula, say in cell C9, has a conditional, something of the form:\r\n```\r\nIF(A9=\"foo\", 2*B9, \"\")\r\n```\r\nMost of the time, column C is not equal to \"foo\", and therefore remains empty. However, xlrd returns such cells as having an integer value of 0. This is unexpected behaviour, and causes downstream problems in Pandas. In this particular case, all of column C is formatted as \"General Number\". Note that the rows in column C to which the formula has not been extended are correctly returned as empty strings.\r\n\r\nIs there some way to make xlrd only consider the actual value of a cell, as displayed in Excel or Libreoffice Calc, instead of considering whether or not it has a formula? Apparently, openpyxl has a kwarg called `data_only=True` that does exactly this:\r\n\r\nhttp://stackoverflow.com/a/28100021\r\nhttp://openpyxl.readthedocs.io/en/default/usage.html#read-an-existing-workbook\r\n\r\nIf xlrd can't currently do this, would it be hard to implement? I could try and give it a shot, if pointed in the right direction."},"comment":{"url":"https://api.github.com/repos/python-excel/xlrd/issues/comments/287492145","html_url":"https://github.com/python-excel/xlrd/issues/207#issuecomment-287492145","issue_url":"https://api.github.com/repos/python-excel/xlrd/issues/207","id":287492145,"user":{"login":"sjmachin","id":1508240,"avatar_url":"https://avatars2.githubusercontent.com/u/1508240?v=3","gravatar_id":"","url":"https://api.github.com/users/sjmachin","html_url":"https://github.com/sjmachin","followers_url":"https://api.github.com/users/sjmachin/followers","following_url":"https://api.github.com/users/sjmachin/following{/other_user}","gists_url":"https://api.github.com/users/sjmachin/gists{/gist_id}","starred_url":"https://api.github.com/users/sjmachin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sjmachin/subscriptions","organizations_url":"https://api.github.com/users/sjmachin/orgs","repos_url":"https://api.github.com/users/sjmachin/repos","events_url":"https://api.github.com/users/sjmachin/events{/privacy}","received_events_url":"https://api.github.com/users/sjmachin/received_events","type":"User","site_admin":false},"created_at":"2017-03-17T23:03:49Z","updated_at":"2017-03-17T23:03:49Z","body":"Re Most of the time, column C is not equal to \"foo\" -- don't you mean column A?\r\n\r\nRe therefore [col C] remains empty -- it's not empty, the cell type is \"text\" and the value is the zero-length string.\r\n\r\nxlrd takes no notice of the formula. What it gives you is what the cell contained when the file was saved. If the file was produced by software that has no formula evaluater (e.g. xlwt), you will get a default value, typically zero. Try opening the file in Excel and saving it again; this should recalculate the formula values.\r\n\r\nopenpyxl works on xlsx files, not xls. Totally different formula mechanism."}}
{"actor":{"display_login":"sjmachin"}}