prompt: We are currently solving the following issue within our repository. Here is the issue text:
  --- BEGIN ISSUE ---
  Download DataSheets as Excel file.
  Csv file with utf-8 is hard to use in excel. So I want to download data sheets as .xlsx file
  
  
  --- END ISSUE ---
  
  Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
  --- BEGIN FILES ---
  Path: `redash/handlers/query_results.py`
  Content:
  ```
  1 import csv
  2 import json
  3 import cStringIO
  4 import time
  5 
  6 from flask import make_response, request
  7 from flask.ext.restful import abort
  8 
  9 from redash import models, settings, utils
  10 from redash.wsgi import api
  11 from redash.tasks import QueryTask, record_event
  12 from redash.permissions import require_permission, not_view_only, has_access
  13 from redash.handlers.base import BaseResource, get_object_or_404
  14 
  15 
  16 class QueryResultListAPI(BaseResource):
  17     @require_permission('execute_query')
  18     def post(self):
  19         params = request.get_json(force=True)
  20         data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org)
  21 
  22         if not has_access(data_source.groups, self.current_user, not_view_only):
  23             return {'job': {'status': 4, 'error': 'You do not have permission to run queries with this data source.'}}, 403
  24 
  25         self.record_event({
  26             'action': 'execute_query',
  27             'timestamp': int(time.time()),
  28             'object_id': data_source.id,
  29             'object_type': 'data_source',
  30             'query': params['query']
  31         })
  32 
  33         max_age = int(params.get('max_age', -1))
  34 
  35         if max_age == 0:
  36             query_result = None
  37         else:
  38             query_result = models.QueryResult.get_latest(data_source, params['query'], max_age)
  39 
  40         if query_result:
  41             return {'query_result': query_result.to_dict()}
  42         else:
  43             query_id = params.get('query_id', 'adhoc')
  44             job = QueryTask.add_task(params['query'], data_source,
  45                                      metadata={"Username": self.current_user.name, "Query ID": query_id})
  46             return {'job': job.to_dict()}
  47 
  48 
  49 ONE_YEAR = 60 * 60 * 24 * 365.25
  50 
  51 
  52 class QueryResultAPI(BaseResource):
  53     @staticmethod
  54     def add_cors_headers(headers):
  55         if 'Origin' in request.headers:
  56             origin = request.headers['Origin']
  57 
  58             if origin in settings.ACCESS_CONTROL_ALLOW_ORIGIN:
  59                 headers['Access-Control-Allow-Origin'] = origin
  60                 headers['Access-Control-Allow-Credentials'] = str(settings.ACCESS_CONTROL_ALLOW_CREDENTIALS).lower()
  61 
  62     @require_permission('view_query')
  63     def options(self, query_id=None, query_result_id=None, filetype='json'):
  64         headers = {}
  65         self.add_cors_headers(headers)
  66 
  67         if settings.ACCESS_CONTROL_REQUEST_METHOD:
  68             headers['Access-Control-Request-Method'] = settings.ACCESS_CONTROL_REQUEST_METHOD
  69 
  70         if settings.ACCESS_CONTROL_ALLOW_HEADERS:
  71             headers['Access-Control-Allow-Headers'] = settings.ACCESS_CONTROL_ALLOW_HEADERS
  72 
  73         return make_response("", 200, headers)
  74 
  75     @require_permission('view_query')
  76     def get(self, query_id=None, query_result_id=None, filetype='json'):
  77         should_cache = query_result_id is not None
  78         if query_result_id is None and query_id is not None:
  79             query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
  80             if query:
  81                 query_result_id = query._data['latest_query_data']
  82 
  83         if query_result_id:
  84             query_result = get_object_or_404(models.QueryResult.get_by_id_and_org, query_result_id, self.current_org)
  85 
  86         if query_result:
  87             if isinstance(self.current_user, models.ApiUser):
  88                 event = {
  89                     'user_id': None,
  90                     'org_id': self.current_org.id,
  91                     'action': 'api_get',
  92                     'timestamp': int(time.time()),
  93                     'api_key': self.current_user.id,
  94                     'file_type': filetype
  95                 }
  96 
  97                 if query_id:
  98                     event['object_type'] = 'query'
  99                     event['object_id'] = query_id
  100                 else:
  101                     event['object_type'] = 'query_result'
  102                     event['object_id'] = query_result_id
  103 
  104                 record_event.delay(event)
  105 
  106             if filetype == 'json':
  107                 response = self.make_json_response(query_result)
  108             else:
  109                 response = self.make_csv_response(query_result)
  110 
  111             if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0:
  112                 self.add_cors_headers(response.headers)
  113 
  114             if should_cache:
  115                 response.headers.add_header('Cache-Control', 'max-age=%d' % ONE_YEAR)
  116 
  117             return response
  118 
  119         else:
  120             abort(404)
  121 
  122     def make_json_response(self, query_result):
  123         data = json.dumps({'query_result': query_result.to_dict()}, cls=utils.JSONEncoder)
  124         return make_response(data, 200, {})
  125 
  126     @staticmethod
  127     def make_csv_response(query_result):
  128         s = cStringIO.StringIO()
  129 
  130         query_data = json.loads(query_result.data)
  131         writer = csv.DictWriter(s, fieldnames=[col['name'] for col in query_data['columns']])
  132         writer.writer = utils.UnicodeWriter(s)
  133         writer.writeheader()
  134         for row in query_data['rows']:
  135             writer.writerow(row)
  136 
  137         headers = {'Content-Type': "text/csv; charset=UTF-8"}
  138         return make_response(s.getvalue(), 200, headers)
  139 
  140 
  141 api.add_org_resource(QueryResultListAPI, '/api/query_results', endpoint='query_results')
  142 api.add_org_resource(QueryResultAPI,
  143                  '/api/query_results/<query_result_id>',
  144                  '/api/queries/<query_id>/results.<filetype>',
  145                  '/api/queries/<query_id>/results/<query_result_id>.<filetype>',
  146                  endpoint='query_result')
  147 
  148 
  149 class JobAPI(BaseResource):
  150     def get(self, job_id):
  151         # TODO: if finished, include the query result
  152         job = QueryTask(job_id=job_id)
  153         return {'job': job.to_dict()}
  154 
  155     def delete(self, job_id):
  156         job = QueryTask(job_id=job_id)
  157         job.cancel()
  158 
  159 api.add_org_resource(JobAPI, '/api/jobs/<job_id>', endpoint='job')
  160
  ```
  --- END FILES ---
  
  Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.
  
  Here is an example:
  
  ```diff
  diff --git a/examples/server_async.py b/examples/server_async.py
  --- a/examples/server_async.py
  +++ b/examples/server_async.py
  @@ -313,4 +313,4 @@
   
   
   if __name__ == "__main__":
  -    asyncio.run(run_async_server("."), debug=True)
  +    asyncio.run(run_async_server(), debug=True)
  diff --git a/examples/server_sync.py b/examples/server_sync.py
  --- a/examples/server_sync.py
  +++ b/examples/server_sync.py
  @@ -313,5 +313,5 @@
   
   
   if __name__ == "__main__":
  -    server = run_sync_server(".")
  +    server = run_sync_server()
       server.shutdown()
  
  ```
  
golden_diff: diff --git a/redash/handlers/query_results.py b/redash/handlers/query_results.py
  --- a/redash/handlers/query_results.py
  +++ b/redash/handlers/query_results.py
  @@ -5,7 +5,7 @@
   
   from flask import make_response, request
   from flask.ext.restful import abort
  -
  +import xlsxwriter
   from redash import models, settings, utils
   from redash.wsgi import api
   from redash.tasks import QueryTask, record_event
  @@ -105,6 +105,8 @@
   
               if filetype == 'json':
                   response = self.make_json_response(query_result)
  +            elif filetype == 'xlsx':
  +                response = self.make_excel_response(query_result)
               else:
                   response = self.make_csv_response(query_result)
   
  @@ -137,6 +139,28 @@
           headers = {'Content-Type': "text/csv; charset=UTF-8"}
           return make_response(s.getvalue(), 200, headers)
   
  +    @staticmethod
  +    def make_excel_response(query_result):
  +        s = cStringIO.StringIO()
  +
  +        query_data = json.loads(query_result.data)
  +        book = xlsxwriter.Workbook(s)
  +        sheet = book.add_worksheet("result")
  +
  +        column_names = []
  +        for (c, col) in enumerate(query_data['columns']):
  +            sheet.write(0, c, col['name'])
  +            column_names.append(col['name'])
  +
  +        for (r, row) in enumerate(query_data['rows']):
  +            for (c, name) in enumerate(column_names):
  +                sheet.write(r+1, c, row[name])
  +
  +        book.close()
  +
  +        headers = {'Content-Type': "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}
  +        return make_response(s.getvalue(), 200, headers)
  +
   
   api.add_org_resource(QueryResultListAPI, '/api/query_results', endpoint='query_results')
   api.add_org_resource(QueryResultAPI,
  
verification_info: {"golden_diff": "diff --git a/redash/handlers/query_results.py b/redash/handlers/query_results.py\n--- a/redash/handlers/query_results.py\n+++ b/redash/handlers/query_results.py\n@@ -5,7 +5,7 @@\n \n from flask import make_response, request\n from flask.ext.restful import abort\n-\n+import xlsxwriter\n from redash import models, settings, utils\n from redash.wsgi import api\n from redash.tasks import QueryTask, record_event\n@@ -105,6 +105,8 @@\n \n             if filetype == 'json':\n                 response = self.make_json_response(query_result)\n+            elif filetype == 'xlsx':\n+                response = self.make_excel_response(query_result)\n             else:\n                 response = self.make_csv_response(query_result)\n \n@@ -137,6 +139,28 @@\n         headers = {'Content-Type': \"text/csv; charset=UTF-8\"}\n         return make_response(s.getvalue(), 200, headers)\n \n+    @staticmethod\n+    def make_excel_response(query_result):\n+        s = cStringIO.StringIO()\n+\n+        query_data = json.loads(query_result.data)\n+        book = xlsxwriter.Workbook(s)\n+        sheet = book.add_worksheet(\"result\")\n+\n+        column_names = []\n+        for (c, col) in enumerate(query_data['columns']):\n+            sheet.write(0, c, col['name'])\n+            column_names.append(col['name'])\n+\n+        for (r, row) in enumerate(query_data['rows']):\n+            for (c, name) in enumerate(column_names):\n+                sheet.write(r+1, c, row[name])\n+\n+        book.close()\n+\n+        headers = {'Content-Type': \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\"}\n+        return make_response(s.getvalue(), 200, headers)\n+\n \n api.add_org_resource(QueryResultListAPI, '/api/query_results', endpoint='query_results')\n api.add_org_resource(QueryResultAPI,\n", "issue": "Download DataSheets as Excel file.\nCsv file with utf-8 is hard to use in excel. So I want to download data sheets as .xlsx file\n\n", "before_files": [{"content": "import csv\nimport json\nimport cStringIO\nimport time\n\nfrom flask import make_response, request\nfrom flask.ext.restful import abort\n\nfrom redash import models, settings, utils\nfrom redash.wsgi import api\nfrom redash.tasks import QueryTask, record_event\nfrom redash.permissions import require_permission, not_view_only, has_access\nfrom redash.handlers.base import BaseResource, get_object_or_404\n\n\nclass QueryResultListAPI(BaseResource):\n    @require_permission('execute_query')\n    def post(self):\n        params = request.get_json(force=True)\n        data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org)\n\n        if not has_access(data_source.groups, self.current_user, not_view_only):\n            return {'job': {'status': 4, 'error': 'You do not have permission to run queries with this data source.'}}, 403\n\n        self.record_event({\n            'action': 'execute_query',\n            'timestamp': int(time.time()),\n            'object_id': data_source.id,\n            'object_type': 'data_source',\n            'query': params['query']\n        })\n\n        max_age = int(params.get('max_age', -1))\n\n        if max_age == 0:\n            query_result = None\n        else:\n            query_result = models.QueryResult.get_latest(data_source, params['query'], max_age)\n\n        if query_result:\n            return {'query_result': query_result.to_dict()}\n        else:\n            query_id = params.get('query_id', 'adhoc')\n            job = QueryTask.add_task(params['query'], data_source,\n                                     metadata={\"Username\": self.current_user.name, \"Query ID\": query_id})\n            return {'job': job.to_dict()}\n\n\nONE_YEAR = 60 * 60 * 24 * 365.25\n\n\nclass QueryResultAPI(BaseResource):\n    @staticmethod\n    def add_cors_headers(headers):\n        if 'Origin' in request.headers:\n            origin = request.headers['Origin']\n\n            if origin in settings.ACCESS_CONTROL_ALLOW_ORIGIN:\n                headers['Access-Control-Allow-Origin'] = origin\n                headers['Access-Control-Allow-Credentials'] = str(settings.ACCESS_CONTROL_ALLOW_CREDENTIALS).lower()\n\n    @require_permission('view_query')\n    def options(self, query_id=None, query_result_id=None, filetype='json'):\n        headers = {}\n        self.add_cors_headers(headers)\n\n        if settings.ACCESS_CONTROL_REQUEST_METHOD:\n            headers['Access-Control-Request-Method'] = settings.ACCESS_CONTROL_REQUEST_METHOD\n\n        if settings.ACCESS_CONTROL_ALLOW_HEADERS:\n            headers['Access-Control-Allow-Headers'] = settings.ACCESS_CONTROL_ALLOW_HEADERS\n\n        return make_response(\"\", 200, headers)\n\n    @require_permission('view_query')\n    def get(self, query_id=None, query_result_id=None, filetype='json'):\n        should_cache = query_result_id is not None\n        if query_result_id is None and query_id is not None:\n            query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)\n            if query:\n                query_result_id = query._data['latest_query_data']\n\n        if query_result_id:\n            query_result = get_object_or_404(models.QueryResult.get_by_id_and_org, query_result_id, self.current_org)\n\n        if query_result:\n            if isinstance(self.current_user, models.ApiUser):\n                event = {\n                    'user_id': None,\n                    'org_id': self.current_org.id,\n                    'action': 'api_get',\n                    'timestamp': int(time.time()),\n                    'api_key': self.current_user.id,\n                    'file_type': filetype\n                }\n\n                if query_id:\n                    event['object_type'] = 'query'\n                    event['object_id'] = query_id\n                else:\n                    event['object_type'] = 'query_result'\n                    event['object_id'] = query_result_id\n\n                record_event.delay(event)\n\n            if filetype == 'json':\n                response = self.make_json_response(query_result)\n            else:\n                response = self.make_csv_response(query_result)\n\n            if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0:\n                self.add_cors_headers(response.headers)\n\n            if should_cache:\n                response.headers.add_header('Cache-Control', 'max-age=%d' % ONE_YEAR)\n\n            return response\n\n        else:\n            abort(404)\n\n    def make_json_response(self, query_result):\n        data = json.dumps({'query_result': query_result.to_dict()}, cls=utils.JSONEncoder)\n        return make_response(data, 200, {})\n\n    @staticmethod\n    def make_csv_response(query_result):\n        s = cStringIO.StringIO()\n\n        query_data = json.loads(query_result.data)\n        writer = csv.DictWriter(s, fieldnames=[col['name'] for col in query_data['columns']])\n        writer.writer = utils.UnicodeWriter(s)\n        writer.writeheader()\n        for row in query_data['rows']:\n            writer.writerow(row)\n\n        headers = {'Content-Type': \"text/csv; charset=UTF-8\"}\n        return make_response(s.getvalue(), 200, headers)\n\n\napi.add_org_resource(QueryResultListAPI, '/api/query_results', endpoint='query_results')\napi.add_org_resource(QueryResultAPI,\n                 '/api/query_results/<query_result_id>',\n                 '/api/queries/<query_id>/results.<filetype>',\n                 '/api/queries/<query_id>/results/<query_result_id>.<filetype>',\n                 endpoint='query_result')\n\n\nclass JobAPI(BaseResource):\n    def get(self, job_id):\n        # TODO: if finished, include the query result\n        job = QueryTask(job_id=job_id)\n        return {'job': job.to_dict()}\n\n    def delete(self, job_id):\n        job = QueryTask(job_id=job_id)\n        job.cancel()\n\napi.add_org_resource(JobAPI, '/api/jobs/<job_id>', endpoint='job')\n", "path": "redash/handlers/query_results.py"}], "after_files": [{"content": "import csv\nimport json\nimport cStringIO\nimport time\n\nfrom flask import make_response, request\nfrom flask.ext.restful import abort\nimport xlsxwriter\nfrom redash import models, settings, utils\nfrom redash.wsgi import api\nfrom redash.tasks import QueryTask, record_event\nfrom redash.permissions import require_permission, not_view_only, has_access\nfrom redash.handlers.base import BaseResource, get_object_or_404\n\n\nclass QueryResultListAPI(BaseResource):\n    @require_permission('execute_query')\n    def post(self):\n        params = request.get_json(force=True)\n        data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org)\n\n        if not has_access(data_source.groups, self.current_user, not_view_only):\n            return {'job': {'status': 4, 'error': 'You do not have permission to run queries with this data source.'}}, 403\n\n        self.record_event({\n            'action': 'execute_query',\n            'timestamp': int(time.time()),\n            'object_id': data_source.id,\n            'object_type': 'data_source',\n            'query': params['query']\n        })\n\n        max_age = int(params.get('max_age', -1))\n\n        if max_age == 0:\n            query_result = None\n        else:\n            query_result = models.QueryResult.get_latest(data_source, params['query'], max_age)\n\n        if query_result:\n            return {'query_result': query_result.to_dict()}\n        else:\n            query_id = params.get('query_id', 'adhoc')\n            job = QueryTask.add_task(params['query'], data_source,\n                                     metadata={\"Username\": self.current_user.name, \"Query ID\": query_id})\n            return {'job': job.to_dict()}\n\n\nONE_YEAR = 60 * 60 * 24 * 365.25\n\n\nclass QueryResultAPI(BaseResource):\n    @staticmethod\n    def add_cors_headers(headers):\n        if 'Origin' in request.headers:\n            origin = request.headers['Origin']\n\n            if origin in settings.ACCESS_CONTROL_ALLOW_ORIGIN:\n                headers['Access-Control-Allow-Origin'] = origin\n                headers['Access-Control-Allow-Credentials'] = str(settings.ACCESS_CONTROL_ALLOW_CREDENTIALS).lower()\n\n    @require_permission('view_query')\n    def options(self, query_id=None, query_result_id=None, filetype='json'):\n        headers = {}\n        self.add_cors_headers(headers)\n\n        if settings.ACCESS_CONTROL_REQUEST_METHOD:\n            headers['Access-Control-Request-Method'] = settings.ACCESS_CONTROL_REQUEST_METHOD\n\n        if settings.ACCESS_CONTROL_ALLOW_HEADERS:\n            headers['Access-Control-Allow-Headers'] = settings.ACCESS_CONTROL_ALLOW_HEADERS\n\n        return make_response(\"\", 200, headers)\n\n    @require_permission('view_query')\n    def get(self, query_id=None, query_result_id=None, filetype='json'):\n        should_cache = query_result_id is not None\n        if query_result_id is None and query_id is not None:\n            query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)\n            if query:\n                query_result_id = query._data['latest_query_data']\n\n        if query_result_id:\n            query_result = get_object_or_404(models.QueryResult.get_by_id_and_org, query_result_id, self.current_org)\n\n        if query_result:\n            if isinstance(self.current_user, models.ApiUser):\n                event = {\n                    'user_id': None,\n                    'org_id': self.current_org.id,\n                    'action': 'api_get',\n                    'timestamp': int(time.time()),\n                    'api_key': self.current_user.id,\n                    'file_type': filetype\n                }\n\n                if query_id:\n                    event['object_type'] = 'query'\n                    event['object_id'] = query_id\n                else:\n                    event['object_type'] = 'query_result'\n                    event['object_id'] = query_result_id\n\n                record_event.delay(event)\n\n            if filetype == 'json':\n                response = self.make_json_response(query_result)\n            elif filetype == 'xlsx':\n                response = self.make_excel_response(query_result)\n            else:\n                response = self.make_csv_response(query_result)\n\n            if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0:\n                self.add_cors_headers(response.headers)\n\n            if should_cache:\n                response.headers.add_header('Cache-Control', 'max-age=%d' % ONE_YEAR)\n\n            return response\n\n        else:\n            abort(404)\n\n    def make_json_response(self, query_result):\n        data = json.dumps({'query_result': query_result.to_dict()}, cls=utils.JSONEncoder)\n        return make_response(data, 200, {})\n\n    @staticmethod\n    def make_csv_response(query_result):\n        s = cStringIO.StringIO()\n\n        query_data = json.loads(query_result.data)\n        writer = csv.DictWriter(s, fieldnames=[col['name'] for col in query_data['columns']])\n        writer.writer = utils.UnicodeWriter(s)\n        writer.writeheader()\n        for row in query_data['rows']:\n            writer.writerow(row)\n\n        headers = {'Content-Type': \"text/csv; charset=UTF-8\"}\n        return make_response(s.getvalue(), 200, headers)\n\n    @staticmethod\n    def make_excel_response(query_result):\n        s = cStringIO.StringIO()\n\n        query_data = json.loads(query_result.data)\n        book = xlsxwriter.Workbook(s)\n        sheet = book.add_worksheet(\"result\")\n\n        column_names = []\n        for (c, col) in enumerate(query_data['columns']):\n            sheet.write(0, c, col['name'])\n            column_names.append(col['name'])\n\n        for (r, row) in enumerate(query_data['rows']):\n            for (c, name) in enumerate(column_names):\n                sheet.write(r+1, c, row[name])\n\n        book.close()\n\n        headers = {'Content-Type': \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\"}\n        return make_response(s.getvalue(), 200, headers)\n\n\napi.add_org_resource(QueryResultListAPI, '/api/query_results', endpoint='query_results')\napi.add_org_resource(QueryResultAPI,\n                 '/api/query_results/<query_result_id>',\n                 '/api/queries/<query_id>/results.<filetype>',\n                 '/api/queries/<query_id>/results/<query_result_id>.<filetype>',\n                 endpoint='query_result')\n\n\nclass JobAPI(BaseResource):\n    def get(self, job_id):\n        # TODO: if finished, include the query result\n        job = QueryTask(job_id=job_id)\n        return {'job': job.to_dict()}\n\n    def delete(self, job_id):\n        job = QueryTask(job_id=job_id)\n        job.cancel()\n\napi.add_org_resource(JobAPI, '/api/jobs/<job_id>', endpoint='job')\n", "path": "redash/handlers/query_results.py"}]}