I have one XML file I want to read that XML using vsto and then find recored in it and update into excel

See more:

C#

VSTO

I have three files 1 XML and 2 Excel

fist excel (mapping files is as which contain some data and line no as follows)

--------------------------------------
TagName	                  Excel row
--------------------------------------
ShareCapital	          418
ReservesAndSurplus	  423
ShareholdersFunds	  5000
--------------------------------------

2 nd (xml file which contain data as follows)

<?xml version="1.0" encoding="UTF-8"?>
<in-gaap:ShareCapital id="TAG70" contextRef="I2015" unitRef="INR" decimals="-3">1240954000</in-gaap:ShareCapital>

<in-gaap:ReservesAndSurplus id="TAG71" contextRef="I2016" unitRef="INR" decimals="-3">8337184000</in-gaap:ReservesAndSurplus>

<in-gaap:ReservesAndSurplus id="TAG72" contextRef="I2015" unitRef="INR" decimals="-3">6633045000</in-gaap:ReservesAndSurplus>

<in-gaap:ShareholdersFunds id="TAG73" contextRef="I2016" unitRef="INR" decimals="-3">9578138000</in-gaap:ShareholdersFunds>

<in-gaap:ShareholdersFunds id="TAG74" contextRef="I2015" unitRef="INR" decimals="-3">7873999000</in-gaap:ShareholdersFunds>

Third excel file which has following structure this file should be open when i run my excel add-ins project

1	                          "31-Mar-2016	"31-Mar-2015	"31-Mar-2014
2 Input - Profit and Loss Account	   0.00		0.00		0.00
3 Revenue from Operations                 0.00		0.00		0.00
4 (Less): Inter departmental Sale	   0.00		0.00		0.00

and so on.

I want to read that xml line by line then compare it with mapping files records (word i.e ShareCapital, ReservesAndSurplus etc) if line contain records then take all tag and their values also xml values 1240954000) and update/insert values in 2 nd excel files (against line no given in mapping file in above mapping file 'share capital' at 418 line so i want insert it in 2nd file in 418 line also it has year in xml files tag i.e I2015 so in 2 nd excel file I want insert it at line no 418 and year 2015 column if cell is empty if cell contain already values then add this values to previous value and update this new values in excel

this process is happens until end of reading xml

for this I have created visual studio excel add-Ins (ExcelAddIn1) in that I have added user control (UserControl1) on that I have added button control name as (btnGetExcl)

What I have tried:

ThisAddIn.cs code is as follows

namespace ExcelAddIn1
{
    public partial class ThisAddIn
    {
        private Microsoft.Office.Tools.CustomTaskPane customPane;
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
   // I open excel file when project is run i.e   CorporateTemplate in whic data has to update/insert       this.Application.Workbooks.Open(@"C:\Users\user1\Desktop\Vinayak\Finolex\CorporateTemp late.xlsx");
            ShowShanuControl();
        }

        public void ShowShanuControl()
        {
            var txtObject = new UserControl1();
            customPane = this.CustomTaskPanes.Add(txtObject, "Enter Text");
            customPane.Width = txtObject.Width;
            customPane.Visible = true;
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}


UserControl1.cs code is as follows

<pre lang="c#">
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Xml;
using System.Xml.Linq;
using System.IO;
using System.Web;

namespace ExcelAddIn1
{
    public partial class UserControl1 : UserControl
    {
        public UserControl1()
        {
            InitializeComponent();
        }
        
        static bool addToFile = false;
        static List<List<string>> imageData = new List<List<string>>();
        
        // code to read mapping xml files and stored its values in list 
        public List<MappingDetail> ReadExcel()       
        {
            List<MappingDetail> li = new List<MappingDetail>();
            string Path = @"D:\Mapping.xlsx";
            // initialize the Excel Application class
            Excel.ApplicationClass app = new ApplicationClass();
            // create the workbook object by opening the excel file.
            Excel.Workbook workBook = app.Workbooks.Open(Path,
                                                         0,
                                                         true,
                                                         5,
                                                         "",
                                                         "",
                                                         true,
                                                         Excel.XlPlatform.xlWindows,
                                                         "\t",
                                                         false,
                                                         false,
                                                         0,
                                                         true,
                                                         1,
                                                         0);
            // get the active worksheet using sheet name or active sheet
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
            int index = 0; // This row,column index should be changed as per your need.
                           // i.e. which cell in the excel you are interesting to read.
            object rowIndex = 2;
            object colIndex1 = 1;
            object colIndex2 = 2;
            try
            {
                while (((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
                {
                    MappingDetail objMappingDetails = new MappingDetail();                    
                    rowIndex = 2 + index;

                    objMappingDetails.TagName = ((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
                    objMappingDetails.Excelrow = Convert.ToInt32(((Excel.Range)workSheet.Cells[rowIndex, colIndex2]).Value2.ToString());                   

                    index++;
                    li.Add(objMappingDetails);
                    if (li.Count == 25)
                        break;
                }

            }
            catch (Exception ex)
            {
                app.Quit();
                Console.WriteLine(ex.Message);
            }
            return li;
        }

        private void btnGetExcl_Click(object sender, EventArgs e)
        {
            List<string> tempData = new List<string>();
            List<string> tempData1 = new List<string>();
          
// calling function which stored mapping excel values into list and return list           
            List<MappingDetail> list = ReadExcel();
            
           List<string> year = new List<string>();
            List<decimal> Value = new List<decimal>();   
// here i am reading xml file present in document folder       
            XmlTextReader reader = new XmlTextReader("Product.xml");

            while (reader.Read())
            {
                string str = reader.Name.ToString();
                string str1 = reader.Value.ToString();                
                foreach (var iteam in list)
                {
                    int lineno = iteam.Excelrow;
                    string sss1 = iteam.TagName.ToString();

                    if (str.Contains(sss1) || str1.Contains(sss1))
                    {

                        while (reader.MoveToNextAttribute())
                        {
                            //Console.Write(xmlReader.Name);
                            //Console.Write(xmlReader.Value + " ");
                            tempData1.Add(reader.Name);  // here I am getting all tag in this list
                            tempData.Add(reader.Value);   // here I am getting all tag values in this list  but I am not getting actual values i.e in xml fist lines values '1240954000' in first iteration of loop while in second iteration I am not getting tag and tag values 
// so here how can I get all values of tag 'id="TAG70" contextRef="I2015" unitRef="INR" decimals="-3"' and actual values '1240954000' in same time(in one iteration of loop)
                        }

                        string mystring = tempData[2].ToString();
                        string abc = mystring.Substring(1); // here I am taking year in one variables also I have line no in  int lineno = iteam.Excelrow; so now I want to insert values '1240954000' in year and line no cell in 2nd excel so how can I do It 


//Excel.Worksheet activeSheet = ((Excel.Worksheet)(application.ActiveSheet));
Excel.Worksheet activeSheet = ((Excel.Worksheet)(Excel.Application.ActiveSheet)); // here giving error object reference is required for non-static field,Method or property '_Application.ActiveSheet'               
//Excel.Worksheet activeWorksheet = ((Excel.Worksheet)obj.ActiveSheet);
                    }
                    
                }              
               
            }

        }

        public class MappingDetail
        {
            public string TagName { get; set; }
            public int Excelrow { get; set; }
        }
    }
}