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



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



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

 

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.