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.

Convert an Excel File Stored in the Cloud to JSON

The ideal solution requires an application hosted on-site with the following features:

  1. Access to the cloud storage API and document retrieval
  2. Domain authentication management for users
  3. Permission sets management for different user groups
  4. 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.

  1. Fetching files to the Client using the API
  2. 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!

Try ComponentOne

Download the latest version of ComponentOne now

Download Now!