Post
Ask Question
Ekrem Tapan

Ekrem Tapan

  • NA
  • 967
  • 81.7k

Import Excel to Json format on ASP.NET

May 15 2016 9:58 AM

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<Position>();  
  7.        }  
  8.   
  9.        public string Code { get; set; }  
  10.        public string Title { get; set; }  
  11.        public string Value { get; set; }  
  12.   
  13.        public List<Position> Children { get; set; }  
  14.    }  
  15.   
  16.    Position Parsing(string value)  
  17.    {  
  18.        string pattern = "\\((?<Code>[\\w]+)\\)(?<Title>.+)";  
  19.        var regex = new Regex(pattern);  
  20.        var match = regex.Match(value);  
  21.   
  22.        if (match.Success)  
  23.        {  
  24.            return new Position()  
  25.            {  
  26.                Code = match.Groups["Code"].Value,  
  27.                Title = match.Groups["Title"].Value,  
  28.                Value = match.Groups["Value"].Value  
  29.            };  
  30.        }  
  31.        else  
  32.        {  
  33.            return null;  
  34.        }  
  35.    }  
  36.    protected void Button1_Click(object sender, EventArgs e)  
  37.    {  
  38.        string sSourceConstr = "";  
  39.        string fileName = Path.GetFileName(fuExcel.PostedFile.FileName);  
  40.   
  41.        if (fuExcel.FileName == string.Empty)  
  42.        {  
  43.            JSBuilder.AlertMessage(this, "ERROR");  
  44.        }  
  45.   
  46.        string fileExtension = Path.GetExtension(fuExcel.PostedFile.FileName);  
  47.        string fileLocation = Server.MapPath("~/" + fileName);  
  48.        fuExcel.SaveAs(fileLocation);  
  49.   
  50.   
  51.        if (fuExcel.HasFile)  
  52.        {  
  53.            if (fileExtension == ".xls")  
  54.            {  
  55.                sSourceConstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";  
  56.            }  
  57.            else if (fileExtension == ".xlsx")  
  58.            {  
  59.                sSourceConstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";  
  60.            }  
  61.            try  
  62.            {  
  63.                IWorkbook workbook = default(IWorkbook);  
  64.                using (FileStream file = new FileStream(fileLocation, FileMode.Open))  
  65.                {  
  66.   
  67.                    workbook = WorkbookFactory.Create(file);  
  68.   
  69.                    List<string> items = new List<string>();  
  70.   
  71.                    Position posnTop = null;  
  72.                    List<Position> posnList = new List<Position>();  
  73.                    ISheet sheet = workbook.GetSheetAt(0);  
  74.                    for (int row = 1; row <= sheet.LastRowNum; row++)  
  75.                    {  
  76.                        if (sheet.GetRow(row) != null)  
  77.                        {  
  78.                            DataTable dt = new DataTable();  
  79.   
  80.                            IRow excelRow = default(IRow);  
  81.                            excelRow = sheet.GetRow(1);  
  82.                            for (int Cel = 1; Cel <= excelRow.LastCellNum - 1; Cel++)  
  83.                            {  
  84.   
  85.                                string industry = sheet.GetRow(1).GetCell(Cel).ToString();  
  86.                                string IndustryCode = sheet.GetRow(2).GetCell(Cel).ToString();  
  87.   
  88.                                for (int k = 4; k <= sheet.LastRowNum; k++)  
  89.                                {  
  90.                                    items.AddRange(sheet.GetRow(k).GetCell(Cel - 1).ToString().Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries));  
  91.   
  92.                                    foreach (var item in items)  
  93.                                    {  
  94.                                        if (!string.IsNullOrEmpty(item) && item.Length > 1)  
  95.                                        {  
  96.                                            if ("\t".Equals(item.Substring(0, 1)))  
  97.                                            {  
  98.                                                //child  
  99.                                                var posn = this.Parsing(item.Trim());  
  100.                                                if (posn != null)  
  101.                                                {  
  102.                                                    posnTop.Children.Add(posn);  
  103.                                                }  
  104.                                            }  
  105.                                            else  
  106.                                            {  
  107.                                                var posn = this.Parsing(item);  
  108.                                                if (posn != null)  
  109.                                                {  
  110.                                                    posnTop = posn;  
  111.                                                    posnList.Add(posn);  
  112.                                                }  
  113.                                            }  
  114.   
  115.   
  116.                                              if (sheet.GetRow(k).GetCell(Cel) != null)  
  117.                                            {  
  118.                                                string value = sheet.GetRow(k).GetCell(Cel).ToString();  
  119.                                            }  
  120.                                        }  
  121.                                    }  
  122.   
  123.   
  124.                                }  
  125.                            }  
  126.                            TextBox txtMsg = new TextBox();  
  127.                            this.txtMsg.Text = Newtonsoft.Json.JsonConvert.SerializeObject(posnList);  
  128.                            #region information Import to SQLSERVER  
  129.                            ////////////////////  
  130.                            //import sql part//   
  131.                            ////////////////////  
  132.                            #endregion  
  133.                        }  
  134.                        else  
  135.                        {  
  136.                            JSBuilder.AlertMessage(this, true, "import succesfull");  
  137.                        }  
  138.                    }  
  139.                }  
  140.            }  
  141.            catch (Exception ex)  
  142.            {  
  143.                Response.Write(ex);  
  144.            }  
  145.        }  
  146.    } 

Answers (1)