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.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
...
// Allow CrossOrigin
app.UseCors();
app.UseMvc();
// Get AzureConnection string from appsettings.json
var azureConnectionString = Configuration.GetSection("Data").GetSection("AzureConnectionString").Value;
// Register Azure Storage using AzureConnectionString
app.UseStorageProviders().AddAzureStorage("Azure", azureConnectionString);
// Use static pages from wwwroot folder
app.UseDefaultFiles();
app.UseStaticFiles();
}
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.
// Add sub nodes to Folder types object to fetch list on demand
function addChildItems(list) {
list.forEach(function (item, index, arr) {
// add Empty nodes to Folder type objects
if (item.type === 0) {
item.Nodes = [];
// add ParentPath to access the files
item.ParentPath = item.name;
}
// add condition to show only the Xlsx files
if (item.name.indexOf(".xlsx";) === -1 && item.type !== 0) {
arr.splice(index, 1);
}
});
return list;
}
// Cloud Storage URL to fetch the list
var _cloudApiUrl = '/api/storage/List/Azure/test/Ledgers/';
// Cloud Storage URL to performs operations
var _cloudUrl = '/api/storage/Azure/test/Ledgers/';
onload = function () {
// Initial Call to fetch the Level 0 files list
$.ajax({
url: _cloudApiUrl,
method: 'GET',
success: function (d) {
tree.itemsSource = addChildItems(d);
}
});
};
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.
// fetch files list from SubFolder on demand
function lazyLoadFunction(node, callback) {
// selected node object
var item = node.dataItem;
var subPath = item.ParentPath;
var url = _cloudApiUrl + subPath;
$.ajax({
url: url
}).then(function (data) {
// add Empty nodes to Folder type objects
data.forEach(function (item) {
if (item.type === 0) {
item.Nodes = [];
item.ParentPath = subPath + item.name;
} else {
item.ParentPath = subPath +"/";
}
});
// send back the list to TreeView to render
callback(data);
});
}
// Events to fetch the JSON from Cloud Storage for the selected Excel file
function treeSelectedItemChanged(s, e) {
var _item = s.selectedItem;
if (_item.type === 1) {
var _url = _cloudUrl+"?subpath=";
if (_item.ParentPath) {
_url= _url+_item.ParentPath + _item.name;
} else {
_url = _url+ _item.name;
} <br>
// fetch the JSON data and bind to the FlexGrid
convertExcelToJSON(_url, {}, "/api/excel");
}
}
// TreeView control to show the files list
var tree = new wijmo.nav.TreeView("#list", {
displayMemberPath: "name",
childItemsPath: 'Nodes',
lazyLoadFunction: lazyLoadFunction,
selectedItemChanged: treeSelectedItemChanged
});
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.
var _excelApiUrl;
var apiHost = window.location.origin;
function convertExcelToJSON(cloudUrl, headers, excelApiUrl) {
// show progress
_updateSuccesser(content, value);
// add origin to url
_excelApiUrl = apiHost + excelApiUrl;
cloudUrl = apiHost + cloudUrl;
// Start XMLHttpRequest to fetch the Blob content
var request = new XMLHttpRequest();
request.responseType = "blob";
request.onload = handleFile;
request.open("GET", cloudUrl);
for (var prop in headers) {
request.setRequestHeader(prop, headers[prop]);
}
request.send();
}
// success callback function
function handleFile(data) {
_updateSuccesser(50);
jsonConverter((this.response || data));
}
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.
// convert Blob to JSON
function jsonConverter(blob) {
// Add variables in FormData to pass along with Ajax call
var excelFile = new File([blob], "excel.xlsx");
var formData = new FormData();
formData.append("FileName", "excel");
formData.append("type", "json");
formData.append("WorkbookFile", excelFile);
// make Ajax call
$.ajax({
url: _excelApiUrl,
type: 'POST',
data: formData,
cache: false,
contentType: false,
processData: false
}).then(function (json) {
_updateSuccesser(75);
convertToWorkBook(json);
}, function (err) {
console.log(err);
});
}
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.
// convert JSON to Workbook instance
function convertToWorkBook(json) {
var wb = new wijmo.xlsx.Workbook();
wb.sheets.push(j.sheets[0]);
_updateSuccesser(95);
}
//load Workbook instance into FlexGrid
function loadWorkbook(wb) {
var grid = wijmo.Control.getControl("#grid");
wijmo.grid.xlsx.FlexGridXlsxConverter.load(grid, wb, { includeColumnHeaders: false, includeCellStyles: false });
// resize columns/rows to fit content
grid.beginUpdate();
grid.deferUpdate(function () {
grid.autoSizeColumns(1, 4);
grid.autoSizeRows();
});
grid.endUpdate();
_updateSuccesser(100);
}
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:
// Update ProgressBar
function _updateSuccesser(value) {
// Add Linear Gauge to show progress
if (_gauge) {
_gauge.value = value;
} else {
_gauge = new wijmo.gauge.LinearGauge("#gauge", {
value: value
});
}
_gauge.value = value;
}
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!
Manish Gupta