Generating Excel files from data is a common requirement, and we usually have to write custom code to do it. It's far easier to send the data to a service and get an Excel file in return. The data can come from varied sources, including JSON, ODATA, XML, or a DataTable. Let's look at how to generate Excel from data using Excel API's of the Web API Edition.

Note: These REST services can be hosted on IIS and can be consumed by clients built on any platform—ASP.NET Web Forms, MVC, WinForms, WPF, Silverlight, LightSwitch, WinRT, UWP, and ActiveX.

Generate Excel From ODATA JSON

In this example, we're going to use Northwind JSON data from services.odata.org and send the JSON to the Web API. Note: The service also sends extra data that we'll need to strip out before sending the JSON. Here, the JSONConvert class helps to get the required data by deserializing, then serializing, the raw JSON.

Raw JSON:

Raw JSON Data

Raw JSON Data


  private void GetExcel_Click(object sender, EventArgs e)
        {
            GenerateFromJSON(apiurl);
        }
 string apiurl = "http://demos.componentone.com/ASPNET/C1WebAPIService/api/excel";  //web api demo url

 public void GenerateFromJSON(string webapiurl)
        {
            var url = "http://services.odata.org/V4/Northwind/Northwind.svc/Products?$format=json";  //get products from ODATA service

            using (var clientjs = new HttpClient())
            {
                HttpResponseMessage responseJSON = clientjs.GetAsync(url).Result;  
                responseJSON.EnsureSuccessStatusCode();
                var responseBody = responseJSON.Content.ReadAsStringAsync().Result;  //Get JSON from ODATA service

                var data = JsonConvert.DeserializeObject<Hashtable>(responseBody)["value"];  //use JsonConvert to deserialize raw json

                using (var client = new HttpClient())
                using (var formData = new MultipartFormDataContent())
                {
                    var fileFormat = "xlsx";
                    formData.Add(new StringContent("Test"), "FileName");
                    formData.Add(new StringContent(fileFormat), "FileFormat");
                    formData.Add(new StringContent(JsonConvert.SerializeObject(data)), "Data"); 
                   //Call WebAPI to get Excel
                    var response = client.PostAsync(webapiurl, formData).Result;
                    if (!response.IsSuccessStatusCode)
                    {
                        MessageBox.Show("Invalid response.");
                        return;
                    }
                    var tempPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
                    if (!Directory.Exists(tempPath))
                    {
                        Directory.CreateDirectory(tempPath);
                    }
                    //Save Excel to Tem directory.
                    var tempFilePath = Path.Combine(tempPath, string.Format("{0}.{1}", "Test", fileFormat));
                    using (var newFile = File.Create(tempFilePath))
                    {
                        response.Content.ReadAsStreamAsync().Result.CopyTo(newFile);
                    }
                    //Open Excel to view.
                    Process.Start(tempFilePath);
                }
            }
        }

 

Result:

Generate Excel from JSON

Generate Excel from JSON

Generate Excel From DataTable

Now we'll look at generating an Excel file from an ADO.NET DataTable. We'll use DataTable.WriteXml method to get the XML, then send the XML to the Web API to generate the Excel.

DataTable XML:

XML Data

XML Data


 private void GenerateExcel_Click(object sender, EventArgs e)
       {
           GenerateFromXML(apiurl);
       }
 string apiurl = "http://demos.componentone.com/ASPNET/C1WebAPIService/api/excel";  //web api demo url

 public void GenerateFromXML(string webapiURL)
        {
          
            DataTable dt = new System.Data.DataTable();
            dt.TableName = "Products";
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Product", typeof(string));
            dt.Columns.Add("Active", typeof(bool));
            dt.Rows.Add(1, "Electronics", true);
            dt.Rows.Add(2, "Food", true);
            dt.Rows.Add(3, "Garments", true);
            dt.Rows.Add(4, "Stationary", true);
            dt.Rows.Add(5, "Antiques", false);
          
           //Create Temp directory to save xml file
            var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
             Directory.CreateDirectory(tempDir);
            string path = Path.Combine(tempDir, string.Format("{0}.{1}", "Prodcuts", "xml"));
           //Write to xml file
            dt.WriteXml(path, System.Data.XmlWriteMode.IgnoreSchema);

            //Create HttpClient and MultipartFormDataContent
            using (var client = new HttpClient())
            using (var formData = new MultipartFormDataContent())
            using (var fromFile=File.OpenRead(path))
            {

                formData.Add(new StringContent("Test"), "FileName");
                formData.Add(new StringContent("xlsx"), "FileFormat");
                formData.Add(new StreamContent(fromFile), "DataFile",Path.GetFileName(path));
                //Call WebAPI
                var response = client.PostAsync(webapiURL, formData).Result;
                if (!response.IsSuccessStatusCode)
                {
                    MessageBox.Show("Invalid response.");
                    return;
                }
                var tempPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
                if (!Directory.Exists(tempPath))
                {
                    Directory.CreateDirectory(tempPath);
                }
                //Save Excel file to Temp directory
                var tempFilePath = Path.Combine(tempPath, string.Format("{0}.{1}", "Test", fileFormat));
                using (var newFile = File.Create(tempFilePath))
                {
                    response.Content.ReadAsStreamAsync().Result.CopyTo(newFile);
                }
                //Open In Excel
                Process.Start(tempFilePath);
            }
        }

 

Result:

Generate Excel from XML

Generate Excel from XML

 

Web API is available with C1Studio and Ultimate editions. Again, these REST services can be hosted on IIS and can be consumed by clients built on any platform.

Tags