Convert Excel to JSON Using DreamFactory

If Benjamin Franklin were alive today, some of his his famous words would have instead read, “Nothing is certain but death and taxes and Excel“. Microsoft’s ubiquitous spreadsheet application is as firmly embedded within the corporate world as TPS reports and the reek of a poorly chosen microwavable lunch (I swear it wasn’t fish). Given their prominence, it’s no surprise developers often spend a great deal of time wrangling spreadsheets, and among other questions wonder how to convert Excel to JSON format. Doing so is incredibly useful for a variety of reasons. Among other uses the JSON format would allow you to:

• Render the spreadsheet within a web page using a JavaScript tabular library such as Tabulator or DataTables.
• Periodically import the spreadsheet data into a database.
• Manipulate the spreadsheet data before importing it into any number of other systems or services.
• Parse the spreadsheet and deliver e-mail or text notifications based on values or thresholds.

The initial use case is particularly critical, because departments often use Excel as a group-based metric reporting tool. Frustration often sets in due to numerous duplicate and outdated versions eventually entering circulation. Of course there are more sophisticated solutions for workbook sharing, SharePoint being chief among them, however adoption of such technologies isn’t as widespread as Excel itself. So how can one go about sanely converting Excel workbooks to JSON?

Introducing the DreamFactory Excel to JSON Connector

Our new Excel connector (available as a beta release within our paid versions of the platform) is capable of turning an entire Excel workbook or specific worksheet into a JSON response (CSV files are supported too!). The workbooks can be retrieved from any DreamFactory-supported file system (AWS S3, SFTP, and Azure Blob Storage, among others), or can be uploaded directly to the server’s local file system. Access is restricted using DreamFactory’s native role-based access controls and API keys, and user authentication (Active Directory, Okta, and OpenID Connect are supported, among others) can optionally be layered on as an additional layer of security.

To use the new connector, you’ll choose it from the Service Type select box as depicted below:

Next, you’ll specify an API namespace, label, and description as is typical of all DreamFactory connectors:

Click the Next button and you’ll be prompted to identify the file storage service. For instance if you choose Local File Storage (available by default on all DreamFactory environments), you’ll be prompted to enter the path to the spreadsheet(s) directory. You can also upload a spreadsheet to that directory using the Browse button (spreadsheets can also be uploaded to the destination file storage service via API calls):

You’re not limited to local file storage of course; you can use any of DreamFactory’s supported file service types. For instance if you’ve configured an AWS S3 API, you’ll be able to select it via the Storage Service select box:

Once configured, you’ll be able to access the API endpoint documentation via the API Docs tab:

We’ll soon be updating this blog post to include a link to a demo application that renders an Excel workbook into a fully responsive web-based tabular interface. Stay tuned! In the meantime, if you’d like to test out this great new feature, e-mail us at [email protected].

Jason Gilmore

Jason is the author of almost a dozen books on web development, including most recently Easy Laravel 5, and Beginning PHP and MySQL, 4th Edition. He’s the co-founder of the CodeMash Conference, one of the largest software conferences in the Midwestern United States.

Jason serves as a technical advisor to the boards of several technology startups. His free time is spent playing with his kids and reading.