Convert an Excel File Stored in the Cloud to JSON
Cloud storages are widely used to store documents, images, and videos since they provide easy remote access to the users. Even organizations use the Cloud to store Excel, Word, and PDF files to leverage the hardware and reduce on-site storage costs.
Since most of these Cloud storages are third-party services, they have authentication protocols that must be followed to access files. This requires users to login.
Let's say an organization that stores historical ledgers on the Cloud wants to give the finance department access to the ledgers in the form of an Excel spreadsheet. Each member of the team will need to access the cloud using the required provider, which could become cumbersome over time.
For instance, if the organization decides to change the authentication details, then this information would need to be shared with all employees. Additionally, management may not want the finance team to view every file that is stored on the Cloud.
The ideal solution requires an application hosted on-site with the following features:
- Access to the cloud storage API and document retrieval
- Domain authentication management for users
- Permission sets management for different user groups
- Selected Excel file display inside a Container (grid)
Implementing such a design would require knowledge of the cloud storage API and writing custom code. Features 2 & 3 are specific to the internal authentication mechanism of the organization and have a generic implementation.
We shall focus on the implementation of features 1 & 4.
- Fetching files to the Client using the API
- Convert this Excel file to JSON so that it can be consumed by the container (grid)
For both steps, ComponentOne provides APIs named Cloud Service and Excel Service respectively.
Here is a brief introduction of the APIs:
Cloud Service: This service provides features to fetch the files list stored on the Cloud (e.g. Azure, AWS, OneDrive, DropBox, Google Drive). It also uploads, deletes, and downloads files from the cloud using the API calls from the projects. Please refer to the documentation for detailed information.
Excel Service: This service provides features to generate, split, merge, find/replace Excel files and convert the Excel file to various formats (e.g. CSV, JSON or XML). Please refer to the documentation for detailed information.
Refer to the WebAPI Explorer sample to see the APIs in action.
Implementation:
First, let's add the required packages from the NuGet server to the existing project.
Packages required:
- C1.AspNetCore.Api
- C1.AspNetCore.Api.Excel
- C1.AspNetCore.Api.Cloud
Step 1: Registering Cloud Storage in Startup.cs
To use the Cloud Service API, you must register the corresponding cloud storage in the Startup.cs file’s Configuration() method.
Step 2: Fetch files & folder list from cloud storage
Now that we have the API configured and ready to use we will make an Ajax call to fetch the file list and folders.
Step 3: Binding File list to TreeView and handling the selectedItemChanged event
Bind the files to the TreeView control to display folders and files. Handle the selectedItemChanged event to fetch the file content and convert the content to JSON.
Step 4: Fetch Excel file as a Blob
Start the Ajax call to fetch the Excel file as a Blob from the Cloud using Cloud Services so that it can be sent to the Excel API.
Step 5: Convert the Blob to JSON
Using the Excel Service convert the Excel to JSON. Start the Ajax call to the Excel Service using the fetched blob as a parameter.
Step 6: Load JSON to the FlexGrid
After getting the JSON from the Excel API, the JSON must be converted as a workbook so it can be loaded in the FlexGrid.
In each Ajax call from Step 4 to 6, _updateSuccesser() method is called, which is used to update the progress of the operation and displays using the Linear Gauge. Here is the implementation code for method:
Now you know how to convert an Excel file stored in the Cloud to JSON. Even though you may not be able to design a custom solution that addresses all issues with cloud storage and access, these steps can help you get what you need in the interim.
If you would like to see more blogs regarding this issue, please let us know in the comments. Happy coding!