Programmatically open an Excel document with connection to a Tabular Model

Question

I would like to create a spreadsheet programmatically with a connection to my Tabular Model that exists in an Azure Analysis Server when the user clicks on some button. Similar to the option available in Azure to View Tabular Model which creates an odc file which you can open in Excel. My application is hosted on Azure and I am using .NET Core 2.2 as back-end.

I am quite clueless how can I achieve this. Has anyone managed to implement such functionality?

Answer 1

The simplest thing is to just generate and deliver an odc file, which will open with Excel.

First create an .odc file pointing to your AAS server, per docs: Create an Office Data Connection file.

Then add that .odc file to your web app, and serve it through a controller like this:

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using System.IO;
using System.Text;

namespace WebApplication4.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class OdcController : ControllerBase
    {
        private IHostingEnvironment hostingEnvironment;
        public OdcController(IHostingEnvironment env)
        {
            hostingEnvironment = env;
        }
        // GET api/values
        [HttpGet]
        public ActionResult Get()
        {
            var fp = Path.Combine(_env.ContentRootPath, "model.odc");
            var odcText = System.IO.File.ReadAllText(fp);
            //optionally modify odcText
            return File(Encoding.ASCII.GetBytes(odcText), "application/octet-stream", "model.odc");
        }

    }
}