Converting Excel To Json
Converting Excel to a JSON Response
DreamFactory's Excel connector is capable of turning an entire Excel workbook or specific worksheet into a JSON response (CSV files are also supported). Workbooks can be retrieved from any DreamFactory supported file-system (AWS S3, SFTP, and Azure Blob Storage, among others), or as in the example we will run through below, uploaded directly to the server's local file system. Access is controlled just like any other connector with role-based access control and API keys.
Creating an Excel Connector Service
To create an Excel connector, log in to your DreamFactory instance using an administrator account and select the API Generation & Connections tab. Set your API Type to File, and then click the purple plus button to establish a new connection:
Navigate to the Excel option in the available service types:
Next, provide a name, label and description for your Excel connector service:
In the configuration section, you'll need to set the file storage service. If you have already setup an AWS S3 API or other file service, then that service will be available to you. For this example, we will use the local file storage service, and we will put our Excel file in /opt/dreamfactory/storage/app which means we will set the container path as / (the 'root' of where DreamFactory will look for locally stored files).
The root location for local file storage is /opt/dreamfactory/storage/app. If you store an Excel file(s) here, your Storage Container Path will be /. If you create a folder in here, e.g. /opt/dreamfactory/storage/app/my-folder then your Storage Container Path will be /my-folder/.
Using the Excel API
Once configured, you will be able to access the API endpoint documentation via the API Docs tab. Here we have an example call to our workbook financial-sample.xlsx:
After configuring your Role and generating an API Key, you can interact with the Excel service. The below is a GET call to /api/v2/<excelServiceName>/_spreadsheet/<excelFilename>:
When creating a role for your Excel connector, you will also need to provide service access to the relevant file storage service, in addition to the Excel service. For local file storage this will be the default "files" service.