JSON data converting to Excel File

Question

I have two sets of data coming in from the front end. One is the table header and the other is the table body. the data is in the form of JSON.

Header

in the following format

[
 "Title 1 ",
 "Title 2"
]

or a real word example

[
 "name",
 "strength"
]

Body

in the following format

[
 [
 "Row A 1",
 "Row A 2"
 ],
 [
 "Row B 1",
 "Row B 2"
 ]
]

or a real world example

[
 [
 "lisinopril",
 "10 mg Tab"
 ],
 [
 "nitroglycerin",
 "0.4 mg Sublingual Tab"
 ],
 [
 "warfarin sodium",
 "3 mg Tab"
 ],
 [
 "metoprolol tartrate",
 "25 mg Tab"
 ]
]

I convert the two objects into list and loop them creating the excel file.

This works, but having to use objects because I want it to be flexible with different inbound data. Also would be nice if someone can tell me whether the solution can be optimized.

Code

 public class GenerateDataTableExportExcel : IHttpHandler
 {
 public void ProcessRequest(HttpContext context)
 {
 // data coming from front end using httpContext.Current.Request
 //var header = HttpContext.Current.Request["header"];
 //var body = HttpContext.Current.Request["body"];
 //var fileName = HttpContext.Current.Request["fileName"];
 
 //Real data from backend system
 string header = "[\"name\",\"strength\"]";
 string body = "[[\"lisinopril\",\"10 mg Tab\"],[\"nitroglycerin\",\"0.4 mg Sublingual Tab\"],[\"warfarin sodium\",\"3 mg Tab\"][\"metoprolol tartrate\",\"25 mg Tab\"]]";
 
 var headerObjects = JsonConvert.DeserializeObject<List<object>>(header);
 var bodyObjects = JsonConvert.DeserializeObject<List<object>>(body);
 
 var objectHeaderList = headerObjects.ToList();
 var objectsList = bodyObjects.ToList(); 
 
 ExcelPackage excel = new ExcelPackage();
 var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
 
 int row = 1;
 int col = 1;
 foreach (var x in objectHeaderList)
 {
 var cell = workSheet.Cells[row, col];
 cell.Value = x;
 col++;
 }
 
 row = 2;
 col = 1;
 foreach (var x in objectsList)
 { 
 
 foreach (JValue y in (JToken)x)
 { 
 var cell = workSheet.Cells[row,col];
 cell.Value = y;
 col++;
 }
 row++;
 }
 context.Response.Clear();
 context.Response.Buffer = true;
 context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 context.Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
 context.Response.Charset = "";
 context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
 context.Response.BinaryWrite(excel.GetAsByteArray());
 context.ApplicationInstance.CompleteRequest();
 
 }
 }

Working sample

https://dotnetfiddle.net/Ky7ljP

• tdy: Please embed the code directly into the question.
• radarbob: Why inline declarations? You will just have to rewrite all this code to accept JSON. Just pass in that sample data as JSON.
• radarbob: I'm not sure this is ready for code review. It is not handling "the data ... in the form of JSON."
• radarbob: So, every time new data is fetched, you manually edit this method to replace the hard-coded header and body? That is very inefficient and error prone.
• Peter Csala: @Jefferson Your shared code fragment does not have any error handling (like column number mismatch between header and body). Did you omit them for the sake of simplicity?

Answer 1

My first thought was why don't you use DataTable?

• Json.Net supports (de)ser from/to DataTable
• EPPlus supports loading from DataTable

Unfortunately the input jsons aren't in the format which is expected by the Json.Net.
But it is quite easy to populate a DataTable from these json files:

Header

public static DataTable ProcessHeader(DataTable dt, string headerJson)
{
 foreach(var header in JsonConvert.DeserializeObject<string[]>(headerJson))
 {
 dt.Columns.Add(header);
 }
 
 return dt;
}

• Json.Net deserializes the header json as a string array
• Custom logic populates the Columns with the header information

Body

public static DataTable ProcessBody(DataTable dt, string bodyJson)
{
 foreach (var body in JsonConvert.DeserializeObject<JArray[]>(bodyJson))
 {
 var row = dt.NewRow();
 dt.Rows.Add(row);
 for(int idx = 0; idx < dt.Columns.Count; idx++)
 {
 row[dt.Columns[idx]] = body[idx];
 }
 }
 
 return dt;
}

• Json.Net deserializes the body json as a JArray array
  • A JArray represents a row
• Custom logic populates the Rows from the semi-deserialized data

Core

With these in our hand the core logic is straight-forward

var data = new DataTable();
data = ProcessHeader(data, headerJson);
data = ProcessBody(data, bodyJson); 
var xlsxFile = new FileInfo("test.xlsx");
var package = new ExcelPackage(xlsxFile);
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("Sheet1");
workSheet.Cells["A1"].LoadFromDataTable(data, true);
package.Save();

For the sake of simplicity I've used a file as the output instead of constructing an HttpResponseMessage.

Please bear in mind that this code is far from production-ready because it contains no error handling.