# ows-xlsx #### A microservice for converting Excel 2010 files to json and json files to Excel 2010. ## ## Methods ## ### xlsxtojson ### This **POST** method will accept an AWS S3 path to an Excel 2010 file, and will return an AWS S3 path to the json data from the file. Note that this method is currently synchronous. Sample request: ``` { "owner_type": "vendor", "owner_id": "1236", "filename": "data_file.xlsx" } ``` Sample response: ``` {"dev-orcdbucket/ows-xlsx/json/vendor_1236/data_file.json"} ``` ###jsontoxlsx### This **POST** method will accept an AWS S3 path to a json data file and an AWS S3 path to an Excel 2010 template file, and will return a job_id that can be used to request further status updates via ```status```. Sample request: ``` { "owner_type": "vendor", "owner_id": "1236", "filename": "data_file.xlsx", "template": "Manage Rights Template V1.0.xlsx" } ``` Sample response: ``` {"job_id": "vendor_1236", "job_status", "PENDING"} ``` ###jsontoxlsx/status#### This **GET** method will accept an job id and will return the status of that job. Sample request: ``` /jsontoxlsx/status/vendor/1236 ``` Sample responses: ``` {"job_status", "PENDING"} ``` ``` {"job_status", "SUCCESS", "xlsx_path": "dev-orcdbucket/ows-xlsx/xlsx/vendor/1236/data_file.xlsx"} ``` ## Additional Information ## ***File Storage*** All files will be stored in AWS S3 in a directory named after the files "owner", which is generally a string consisting of a concatenation of user_type and the id: ie. vendor_8869 or subaccount_1234 ***Quirks and Gotchas*** 0. All JSON is "[line-delimited](http://jsonlines.org/)". 1. When creating a spreadsheet from json data, the json keys must match the template header column in a case-sensitive and character-perfect fashion. 2. Its still fairly slow when working with large data sets - in anecdotal tests, 10,000 rows can take ~20 min to process. 3. Comments are currently bugged - see [this JIRA issue](http://jira.theorchard.com/browse/LNC-714) for more details. 4. All column widths and other styles are copied from the source template. This includes the "hidden" flag. 5. **DO NOT** apply data validations or other styles in the templates to the entire column. This will cause the openpyxl optimized library to scan the entire column of the template, resulting in very slow processing. Instead, apply the data validations to individual cells in the 2nd row of the template - they will be applied to all cells in the output spreadsheet columns. 6. If a key in the json data file does not match a column in the template header row, it will be ignored. 7. Currently the jsontoxlsx code is designed to process only two sheets - sheet 1 being "Data" and sheet 2 being "Lookups". You MUST include these two sheets in your templates. See [this block of code](https://github.com/theorchard/ows-xlsx/blob/fbb39ae45e7a281053a1c940387f06142b361931/app/logic/converter.py#L259-L264) for the details. This should be easy enough to change if/when there is a reason to. 8. Currently the xlsxtojson code is designed to process only a single sheet. See [this block of code](https://github.com/theorchard/ows-xlsx/blob/fbb39ae45e7a281053a1c940387f06142b361931/app/logic/converter.py#L203) for the details. This should be easy enough to change if/when there is a reason to. ## Usage ## ##### Installation: See [vagrant Docker host](https://github.com/theorchard/orchard/tree/master/vagrant_boxes/docker) instructions on the main Orchard repo. The ows-xlsx service is installed as a demo. If you want to run the docker container yourself, you can try running commands similar to what is included [in the setup script](https://github.com/theorchard/orchard/blob/master/vagrant_boxes/docker/setup.sh#L30). If you want to just run the code directly outside of a docker container, you should be able to run ```python dev.py``` similarly to [the Flask quickstart documentation page](http://flask.pocoo.org/docs/0.10/quickstart/). You will want to additionally modify .env with values to be populated by honcho into your dev environment. ```bash pyvenv-3.4 env source env/bin/activate honcho start # dev (with auto-reload) running a celery worker ``` ##### Running Tests: ``` py.test --cov xslx tests/ ``` #### Try it out: ##### XLSX to JSON 1. Make sure your xlsx file lives in s3 at a path corresponding to the POST to be issued. In the case of a dev environment a file named `foo.xlsx` to be processed for vendor 1236 would live at `dev-orcdbucket/xlsx/vendor_1236/foo.xlsx`. 2. With the file on s3 you can perform a `POST` against the service from the command line as shown here: ```bash curl --data "owner_type=vendor&owner_id=1236&filename=foo.xlsx" http://127.0.0.1:5001/xlsxtojson ``` 3. This is a synchronous process, the path to the generated JSON file will be returned ##### JSON to XLSX 1. Make sure your json file lives in s3 at the path corresponding to the POST to be issued. In the case of a dev environment a file named `foo.json` to be processed for vendor 1236 would live at `dev-orccbucket/json/vendor_1236/foo.json`. 2. Make sure the XLSX template file to which you are requesting conversion is available in the xlsx folder of the service on s3. In a dev environment this would be at `dev-orcdbucket/xlsx/Template Foo.xlsx`, for example. 3. With the file on s3 you can perform a `POST` against the service from the command line as shown here: ```bash curl --data "owner_type=vendor&owner_id=1236&filename=foo.json&template=Template+Foo.xlsx" http://127.0.0.1:5001/jsontoxlsx ``` 4. This is an asynchronous process. The response you will get back from the above `POST` will have a status of `PENDING` and a job_id. The job_id is unique to the owner_type/owner_id: only one simultaneous conversion per user. Generating XLSX can take some time, as such a job is scheduled for the owner_type/owner_id. To check the status of the process you can make requests (in your browser should be fine) against: ``` http://127.0.0.1:5001/jsontoxlsx/status/vendor/1236 ``` When complete the path to the generated XLSX file will be returned along with a `SUCCESS` status.