I need to ask about import excel sheet question with child-parent relationship.

below i have a sample table

 
 

output i want to like that 

[{"Code":"888800","Title":"Category","Value":"123","Children":[]}, 
 {"Code":"100000","Title":"Category1","Value":"123","Children": 
   [ 
      {"Code":"120000","Title":"SubCategory","Value":"123","Children":[]}, 
      {"Code":"352000","Title":"SubCategory2","Value":"123","Children":[]}, 
      {"Code":"200000","Title":"SubCategory3","Value":"123","Children":[]}, ]}, 
 {"Code":"212101","Title":"Category3","Value":"123","Children": 
   [ 
      {"Code":"213100","Title":"SubCategory4","Value":"123","Children":[]}, 
      {"Code":"213200","Title":"SubCategory4","Value":"123","Children":[]} 
   ]} 
]


below I have a my code, but I don't know where is my wrong point. When I'm creating Json Serialization value and children part not correctly

  1. int sheetname = 0;  
  2.    class Position  
  3.    {  
  4.        public Position()  
  5.        {  
  6.            this.Children = new List();  
  7.        }  
  8.   
  9.        public string Code { get; set; }  
  10.        public string Title { get; set; }  
  11.        public string Value { get; set; }  
  12.   
  13.        public List Children { get; set; }  
  14.    }  
  15.   
  16.    Position Parsing(string value)  
  17.    {  
  18.        string pattern = "\\((?[\\w]+)\\)(?.+)"</span><span>;  </span></span></li><li class="alt"><span>       var regex = <span class="keyword">new</span><span> Regex(pattern);  </span></span></li><li class=""><span>       var match = regex.Match(value);  </span></li><li class="alt"><span>  </span></li><li class=""><span>       <span class="keyword">if</span><span> (match.Success)  </span></span></li><li class="alt"><span>       {  </span></li><li class=""><span>           <span class="keyword">return</span><span> </span><span class="keyword">new</span><span> Position()  </span></span></li><li class="alt"><span>           {  </span></li><li class=""><span>               Code = match.Groups[<span class="string">"Code"</span><span>].Value,  </span></span></li><li class="alt"><span>               Title = match.Groups[<span class="string">"Title"</span><span>].Value,  </span></span></li><li class=""><span>               Value = match.Groups[<span class="string">"Value"</span><span>].Value  </span></span></li><li class="alt"><span>           };  </span></li><li class=""><span>       }  </span></li><li class="alt"><span>       <span class="keyword">else</span><span>  </span></span></li><li class=""><span>       {  </span></li><li class="alt"><span>           <span class="keyword">return</span><span> </span><span class="keyword">null</span><span>;  </span></span></li><li class=""><span>       }  </span></li><li class="alt"><span>   }  </span></li><li class=""><span>   <span class="keyword">protected</span><span> </span><span class="keyword">void</span><span> Button1_Click(</span><span class="keyword">object</span><span> sender, EventArgs e)  </span></span></li><li class="alt"><span>   {  </span></li><li class=""><span>       <span class="keyword">string</span><span> sSourceConstr = </span><span class="string">""</span><span>;  </span></span></li><li class="alt"><span>       <span class="keyword">string</span><span> fileName = Path.GetFileName(fuExcel.PostedFile.FileName);  </span></span></li><li class=""><span>  </span></li><li class="alt"><span>       <span class="keyword">if</span><span> (fuExcel.FileName == </span><span class="keyword">string</span><span>.Empty)  </span></span></li><li class=""><span>       {  </span></li><li class="alt"><span>           JSBuilder.AlertMessage(<span class="keyword">this</span><span>, </span><span class="string">"ERROR"</span><span>);  </span></span></li><li class=""><span>       }  </span></li><li class="alt"><span>  </span></li><li class=""><span>       <span class="keyword">string</span><span> fileExtension = Path.GetExtension(fuExcel.PostedFile.FileName);  </span></span></li><li class="alt"><span>       <span class="keyword">string</span><span> fileLocation = Server.MapPath(</span><span class="string">"~/"</span><span> + fileName);  </span></span></li><li class=""><span>       fuExcel.SaveAs(fileLocation);  </span></li><li class="alt"><span>  </span></li><li class=""><span>  </span></li><li class="alt"><span>       <span class="keyword">if</span><span> (fuExcel.HasFile)  </span></span></li><li class=""><span>       {  </span></li><li class="alt"><span>           <span class="keyword">if</span><span> (fileExtension == </span><span class="string">".xls"</span><span>)  </span></span></li><li class=""><span>           {  </span></li><li class="alt"><span>               sSourceConstr = <span class="string">"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="</span><span> + fileLocation + </span><span class="string">";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""</span><span>;  </span></span></li><li class=""><span>           }  </span></li><li class="alt"><span>           <span class="keyword">else</span><span> </span><span class="keyword">if</span><span> (fileExtension == </span><span class="string">".xlsx"</span><span>)  </span></span></li><li class=""><span>           {  </span></li><li class="alt"><span>               sSourceConstr = <span class="string">"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="</span><span> + fileLocation + </span><span class="string">";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""</span><span>;  </span></span></li><li class=""><span>           }  </span></li><li class="alt"><span>           <span class="keyword">try</span><span>  </span></span></li><li class=""><span>           {  </span></li><li class="alt"><span>               IWorkbook workbook = <span class="keyword">default</span><span>(IWorkbook);  </span></span></li><li class=""><span>               <span class="keyword">using</span><span> (FileStream file = </span><span class="keyword">new</span><span> FileStream(fileLocation, FileMode.Open))  </span></span></li><li class="alt"><span>               {  </span></li><li class=""><span>  </span></li><li class="alt"><span>                   workbook = WorkbookFactory.Create(file);  </span></li><li class=""><span>  </span></li><li class="alt"><span>                   List<<span class="keyword">string</span><span>> items = </span><span class="keyword">new</span><span> List<</span><span class="keyword">string</span><span>>();  </span></span></li><li class=""><span>  </span></li><li class="alt"><span>                   Position posnTop = <span class="keyword">null</span><span>;  </span></span></li><li class=""><span>                   List<Position> posnList = <span class="keyword">new</span><span> List<Position>();  </span></span></li><li class="alt"><span>                   ISheet sheet = workbook.GetSheetAt(0);  </span></li><li class=""><span>                   <span class="keyword">for</span><span> (</span><span class="keyword">int</span><span> row = 1; row <= sheet.LastRowNum; row++)  </span></span></li><li class="alt"><span>                   {  </span></li><li class=""><span>                       <span class="keyword">if</span><span> (sheet.GetRow(row) != </span><span class="keyword">null</span><span>)  </span></span></li><li class="alt"><span>                       {  </span></li><li class=""><span>                           DataTable dt = <span class="keyword">new</span><span> DataTable();  </span></span></li><li class="alt"><span>  </span></li><li class=""><span>                           IRow excelRow = <span class="keyword">default</span><span>(IRow);  </span></span></li><li class="alt"><span>                           excelRow = sheet.GetRow(1);  </span></li><li class=""><span>                           <span class="keyword">for</span><span> (</span><span class="keyword">int</span><span> Cel = 1; Cel <= excelRow.LastCellNum - 1; Cel++)  </span></span></li><li class="alt"><span>                           {  </span></li><li class=""><span>  </span></li><li class="alt"><span>                               <span class="keyword">string</span><span> industry = sheet.GetRow(1).GetCell(Cel).ToString();  </span></span></li><li class=""><span>                               <span class="keyword">string</span><span> IndustryCode = sheet.GetRow(2).GetCell(Cel).ToString();  </span></span></li><li class="alt"><span>  </span></li><li class=""><span>                               <span class="keyword">for</span><span> (</span><span class="keyword">int</span><span> k = 4; k <= sheet.LastRowNum; k++)  </span></span></li><li class="alt"><span>                               {  </span></li><li class=""><span>                                   items.AddRange(sheet.GetRow(k).GetCell(Cel - 1).ToString().Split(<span class="keyword">new</span><span> </span><span class="keyword">string</span><span>[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries));  </span></span></li><li class="alt"><span>  </span></li><li class=""><span>                                   <span class="keyword">foreach</span><span> (var item </span><span class="keyword">in</span><span> items)  </span></span></li><li class="alt"><span>                                   {  </span></li><li class=""><span>                                       <span class="keyword">if</span><span> (!</span><span class="keyword">string</span><span>.IsNullOrEmpty(item) && item.Length > 1)  </span></span></li><li class="alt"><span>                                       {  </span></li><li class=""><span>                                           <span class="keyword">if</span><span> (</span><span class="string">"\t"</span><span>.Equals(item.Substring(0, 1)))  </span></span></li><li class="alt"><span>                                           {  </span></li><li class=""><span>                                               <span class="comment">//child</span><span>  </span></span></li><li class="alt"><span>                                               var posn = <span class="keyword">this</span><span>.Parsing(item.Trim());  </span></span></li><li class=""><span>                                               <span class="keyword">if</span><span> (posn != </span><span class="keyword">null</span><span>)  </span></span></li><li class="alt"><span>                                               {  </span></li><li class=""><span>                                                   posnTop.Children.Add(posn);  </span></li><li class="alt"><span>                                               }  </span></li><li class=""><span>                                           }  </span></li><li class="alt"><span>                                           <span class="keyword">else</span><span>  </span></span></li><li class=""><span>                                           {  </span></li><li class="alt"><span>                                               var posn = <span class="keyword">this</span><span>.Parsing(item);  </span></span></li><li class=""><span>                                               <span class="keyword">if</span><span> (posn != </span><span class="keyword">null</span><span>)  </span></span></li><li class="alt"><span>                                               {  </span></li><li class=""><span>                                                   posnTop = posn;  </span></li><li class="alt"><span>                                                   posnList.Add(posn);  </span></li><li class=""><span>                                               }  </span></li><li class="alt"><span>                                           }  </span></li><li class=""><span>  </span></li><li class="alt"><span>  </span></li><li class=""><span>                                             <span class="keyword">if</span><span> (sheet.GetRow(k).GetCell(Cel) != </span><span class="keyword">null</span><span>)  </span></span></li><li class="alt"><span>                                           {  </span></li><li class=""><span>                                               <span class="keyword">string</span><span> value = sheet.GetRow(k).GetCell(Cel).ToString();  </span></span></li><li class="alt"><span>                                           }  </span></li><li class=""><span>                                       }  </span></li><li class="alt"><span>                                   }  </span></li><li class=""><span>  </span></li><li class="alt"><span>  </span></li><li class=""><span>                               }  </span></li><li class="alt"><span>                           }  </span></li><li class=""><span>                           TextBox txtMsg = <span class="keyword">new</span><span> TextBox();  </span></span></li><li class="alt"><span>                           <span class="keyword">this</span><span>.txtMsg.Text = Newtonsoft.Json.JsonConvert.SerializeObject(posnList);  </span></span></li><li class=""><span><span class="preprocessor">                           #region information Import to SQLSERVER</span><span>  </span></span></li><li class="alt"><span>                           <span class="comment">////////////////////</span><span>  </span></span></li><li class=""><span>                           <span class="comment">//import sql part// </span><span>  </span></span></li><li class="alt"><span>                           <span class="comment">////////////////////</span><span>  </span></span></li><li class=""><span><span class="preprocessor">                           #endregion</span><span>  </span></span></li><li class="alt"><span>                       }  </span></li><li class=""><span>                       <span class="keyword">else</span><span>  </span></span></li><li class="alt"><span>                       {  </span></li><li class=""><span>                           JSBuilder.AlertMessage(<span class="keyword">this</span><span>, </span><span class="keyword">true</span><span>, </span><span class="string">"import succesfull"</span><span>);  </span></span></li><li class="alt"><span>                       }  </span></li><li class=""><span>                   }  </span></li><li class="alt"><span>               }  </span></li><li class=""><span>           }  </span></li><li class="alt"><span>           <span class="keyword">catch</span><span> (Exception ex)  </span></span></li><li class=""><span>           {  </span></li><li class="alt"><span>               Response.Write(ex);  </span></li><li class=""><span>           }  </span></li><li class="alt"><span>       }  </span></li><li class=""><span>   }  <br /></span></li></ol></div></div></div></div></article><section class="comments" id="replies"><header class="comments-head"><span class="comments-badge" aria-hidden="true"><svg xmlns="http://www.w3.org/2000/svg" width="19" height="19" viewBox="0 0 16 16" fill="none" stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round" aria-hidden="true" focusable="false"><path d="M3 3h10a2 2 0 0 1 2 2v4.5a2 2 0 0 1-2 2H7.5L4 14.5V11.5H3a2 2 0 0 1-2-2V5a2 2 0 0 1 2-2z"></path></svg></span><div><h2 class="comments-title">1 Reply</h2><p class="comments-sub">Know the answer? Post it — somebody with the same question will find it here.</p></div></header><div class="comment-signin"><span class="comment-signin-badge" aria-hidden="true"><svg viewBox="0 0 20 20" fill="none" stroke="currentColor" stroke-width="1.6"><path d="M11.5 3.5h3.2a1 1 0 0 1 1 1v11a1 1 0 0 1-1 1h-3.2" stroke-linecap="round"></path><path d="M4.5 10h7" stroke-linecap="round"></path><path d="M8.8 7.2 11.6 10l-2.8 2.8" stroke-linecap="round" stroke-linejoin="round"></path></svg></span><div class="comment-signin-body"><p class="comment-signin-title">Sign in to <!-- -->answer this question</p><p class="comment-signin-text">It is the same account you read, post and publish with — and you will come straight back to this page.</p></div><div class="comment-signin-actions"><a class="btn primary-btn comment-signin-btn" href="/login?returnurl=%2Fforums%2Fimport-excel-to-json-format-on-asp-net">Sign in</a><a class="comment-signin-alt" href="/register?returnurl=%2Fforums%2Fimport-excel-to-json-format-on-asp-net">Create an account</a></div></div><ul class="comment-list"><li class="comment-thread"><article class="comment"><a class="comment-photo-link" href="/members/ashok-yadav7" title="Ashok Yadav"><img class="comment-photo" src="https://cdn.c-sharpcorner.com/uploadfile/authorimage/84d67420141128050554.webp" alt="Ashok Yadav" title="Ashok Yadav" loading="lazy" data-avatar-name="Ashok Yadav" referrerPolicy="no-referrer"/></a><div class="comment-main"><div class="comment-head"><p class="comment-byline"><a href="/members/ashok-yadav7" class="comment-author">Ashok Yadav</a><span class="posted-when" tabindex="0"><time class="posted-when-value comment-time" dateTime="2016-05-17T00:38:47.790Z">10y<!-- --> ago</time><span class="posted-when-pop" role="tooltip"><span>Posted<!-- --> <!-- -->May 17, 2016, 12:38 AM</span></span></span></p></div><div class="article-body"><div id="pastingspan1"> string sqlquery = "Select * From [SheetName$] ";</div><div id="pastingspan1"> DataSet ds = new DataSet();</div><div id="pastingspan1"> string constring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";</div><div id="pastingspan1"> OleDbConnection con = new OleDbConnection(constring + "");</div><div id="pastingspan1"> OleDbDataAdapter da = new OleDbDataAdapter(sqlquery, con);</div><div id="pastingspan1"> da.Fill(ds);</div><div id="pastingspan1"> DataTable dt = ds.Tables[0];</div><div id="pastingspan1"> string JSONresult;</div><div id="pastingspan1"> JSONresult = JsonConvert.SerializeObject(dt);</div><div id="pastingspan1"> Response.Write(JSONresult);</div></div><div class="comment-foot"><span class="reply-vote"><a href="/login?returnurl=%2Fforums%2Fimport-excel-to-json-format-on-asp-net" class="reply-vote-btn reply-vote-up" title="Sign in to vote up" aria-label="Vote up"><svg width="14" height="14" viewBox="0 0 16 16" fill="currentColor" aria-hidden="true"><path d="M8 2.5 14.5 12h-13z"></path></svg></a><span class="reply-vote-score" title="0 up, 0 down">0</span><a href="/login?returnurl=%2Fforums%2Fimport-excel-to-json-format-on-asp-net" class="reply-vote-btn reply-vote-down" title="Sign in to vote down" aria-label="Vote down"><svg width="14" height="14" viewBox="0 0 16 16" fill="currentColor" aria-hidden="true"><path d="M8 13.5 1.5 4h13z"></path></svg></a></span></div></div></article></li></ul></section></div></div><script>$RS=function(a,b){a=document.getElementById(a);b=document.getElementById(b);for(a.parentNode.removeChild(a);a.firstChild;)b.parentNode.insertBefore(a.firstChild,b);b.parentNode.removeChild(b)};$RS("S:2","P:2")</script><script>$RB=[];$RV=function(a){$RT=performance.now();for(var b=0;b<a.length;b+=2){var c=a[b],e=a[b+1];null!==e.parentNode&&e.parentNode.removeChild(e);var f=c.parentNode;if(f){var g=c.previousSibling,h=0;do{if(c&&8===c.nodeType){var d=c.data;if("/$"===d||"/&"===d)if(0===h)break;else h--;else"$"!==d&&"$?"!==d&&"$~"!==d&&"$!"!==d&&"&"!==d||h++}d=c.nextSibling;f.removeChild(c);c=d}while(c);for(;e.firstChild;)f.insertBefore(e.firstChild,c);g.data="$";g._reactRetry&&requestAnimationFrame(g._reactRetry)}}a.length=0}; $RC=function(a,b){if(b=document.getElementById(b))(a=document.getElementById(a))?(a.previousSibling.data="$~",$RB.push(a,b),2===$RB.length&&("number"!==typeof $RT?requestAnimationFrame($RV.bind(null,$RB)):(a=performance.now(),setTimeout($RV.bind(null,$RB),2300>a&&2E3<a?2300-a:$RT+300-a)))):b.parentNode.removeChild(b)};$RC("B:1","S:1")</script><div hidden id="S:3"></div><script>$RC("B:3","S:3")</script><div hidden id="S:0"></div><script>$RC("B:0","S:0")</script></body></html>