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

Why inline declarations? You will just have to rewrite all this code to accept JSON. Just pass in that sample data as JSON. — radarbob
– radarbob, Commented May 18, 2024 at 2:50
I'm not sure this is ready for code review. It is not handling "the data ... in the form of JSON." — radarbob
– radarbob, Commented May 18, 2024 at 2:56
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. — radarbob
– radarbob, Commented May 21, 2024 at 0:46
@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? — Peter Csala
– Peter Csala, Commented May 21, 2024 at 7:08

Peter Csala · Accepted Answer · 2024-05-21 11:32:58Z

My first thought was why don't you use 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.

Stack Exchange Network

JSON data converting to Excel File

Header

Body

Code

1 Answer 1

Header

Body

Core

You must log in to answer this question.

Hot Network Questions

JSON data converting to Excel File

Header

Body

Code

1 Answer 1

Header

Body

Core

You must log in to answer this question.

Related

Hot Network Questions