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 ComponentOne Web API's. These REST services can be hosted and can be consumed by clients built on any platform—WinForms, XAML, JavaScript, ASP.NET MVC.

ComponentOne Web API's comprise of following API's:

• Excel - Generate Excel from JSON, XML, Collections, merge, split excel files, api to rows and columns and searching.
• PDF - Build RESTful HTTP services for viewing, loading and caching PDF documents inside FlexViewer in Wijmo, ASP.NET MVC applications. Has Windows dependency.
• Reporting - Build RESTful HTTP services for loading and caching FlexReports and SSRS reports. Export reports in various formats, View FlexReport in FlexViewer inside Wijmo, ASP.NET MVC applications. Has Windows dependency.
• BarCode - Generate scannable bar codes from text and save to an image format.
• DataEngine - Analyze large amount of data by slicing, aggregating in-memory, databind to OLAP controls in MVC and Wijmo to further analyze on client.
• Cloud Storage - Perform CRUD operations on cloud storages such as Azure, AWS, OneDrive, DropBox, GoogleDrive. View files in FileManager inside ASP.NET MVC applications.
• Visitor - Collect visitor data such as IP, location, language, referring site, operating system, and browser. It's useful to deliver custom content for individual users.

Generate Excel From ODATA JSON

In this example, we're going to use Northwind JSON data from services.odata.org to fetch (Check how ODATA DataConnector eases data fetch and other operations.) 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:

private void GetExcel_Click(object sender, EventArgs e)  
        {  
            GenerateFromJSON(apiurl);  
        }  
 string apiurl = "https://www.grapecity.com/componentone/demos/aspnet/5/C1WebAPI/latest/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 Excel File:

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. It is not necessary to use a DataTable XML, The content of the xml data file should be collection-like, a root element with multiple same elements as its children:

<orders>
    <order id="1">
        <price>1000</price>
    </order>
    ......
 </orders>

DataTable XML:

private void GenerateExcel_Click(object sender, EventArgs e)  
       {  
           GenerateFromXML(apiurl);  
       }  
 string apiurl = "https://www.grapecity.com/componentone/demos/aspnet/5/C1WebAPI/latest/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 Excel from XML

You can find more samples under "~\Documents\ComponentOne Samples\Web Api" folder when you install the Web API or DataServices edition.

Web API is available with DataServices, WinForms DataServices and Enterprise editions. DataService Edition provides below capabilities that make application development easy:

• Access and integrate data from various online and cloud-based sources
• Take advantage of in-memory caching and data virtualization to deliver faster loading and analysis of large data sets
• Parse text files and HTML pages to extract data from sources other than a database
• Empower and extend your apps with Web services for exporting, uploading and caching files and data.
• Export anything from images to PDFs through a Web API
• Web APIs for downloading, uploading, caching, generating and exporting images, documents, reports and barcodes.
• Supports .NET 6, .NET Core, ASP.NET MVC, WinForms, WPF, WinUI, UWP, Xamarin, and OS X

How to Configure and Host the Web API >>

See the Web API Explorer Demo >>