It is very common for companies to use Excel Spreadsheets both to store data and derive insights from that data. We encounter many companies for whom Excel is a useful tool, but find the tool is unable to keep pace with the data they are generating. There is a drive to produce more powerful analytics and insights from their data, and to achieve this the data needs to be moved to the cloud. To achieve these ends we need to think a little differently and treat an Excel spreadsheet as an input device rather than as a repository of data. We can achieve this through an Excel Add-In that manifests as a bespoke Ribbon Menu.
We can provide the ability to select rows/columns and cells and to map this to an intermediate domain i.e. CSV or Parquet .Net. We can then orchestrate the transfer of data from the intermediate domain to a database or other storage medium using Azure Functions, which can provide lightweight services to perform specific tasks while keeping costs down. Alternatives involve Service Fabrics or API Apps which can be expensive in comparison to Azure Functions, as they would be called less frequently. Following this, deriving analytics and insights and to visualise the results through PowerBI will become very easy.