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)
="1.0"="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)
{
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
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>>();
public List<MappingDetail> ReadExcel()
{
List<MappingDetail> li = new List<MappingDetail>();
string Path = @"D:\Mapping.xlsx";
Excel.ApplicationClass app = new ApplicationClass();
Excel.Workbook workBook = app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
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>();
List<MappingDetail> list = ReadExcel();
List<string> year = new List<string>();
List<decimal> Value = new List<decimal>();
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())
{
tempData1.Add(reader.Name);
tempData.Add(reader.Value);
}
string mystring = tempData[2].ToString();
string abc = mystring.Substring(1);
Excel.Worksheet activeSheet = ((Excel.Worksheet)(Excel.Application.ActiveSheet));
}
}
}
}
public class MappingDetail
{
public string TagName { get; set; }
public int Excelrow { get; set; }
}
}
}