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.

Get ComponentOne's Web API

Download the latest version of ComponentOne Studio Enterprise

Download Now!

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

C# Code:


  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(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 to 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

C# Code:


 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 to 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.

Get ComponentOne's Web API

Download the latest version of ComponentOne Studio Enterprise

Download Now!