Fetch values from an xml file and write into a excel sheet row-wise

Question

I have a situation here, I have to write a simple WPF apps with a textbox and a button, textbox is for "en", "fr", "ru", etc...

I have a lot of files with huge number of data like this. Consider this is a file

<?xml version="1.0" encoding="UTF-8"?>
<params>
    <btestsir xml:lang="fr" tId="HHXAF">Test Sirène :</btestsir>
    <btestsir xml:lang="en" tId="HHXAF">Test Siren :</btestsir>
    <btestsir xml:lang="pt" tId="HHXAF">Testar sirene:</btestsir>
    <btestsir xml:lang="ru" tId="HHXAF">Тест сирены:</btestsir>

    <btestbeep xml:lang="fr" tId="HHXA2">Test Bip :</btestbeep>
    <btestbeep xml:lang="en" tId="HHXA2">Test Beep :</btestbeep>
    <btestbeep xml:lang="pt" tId="HHXA2">Testar aviso sonoro:</btestbeep>
    <btestbeep xml:lang="ru" tId="HHXA2">Тест гудка:</btestbeep>
</params>

Now if I select "en" through my application, and click on the button then only two strings whose xml:lang="en" values match will be copied to an excel sheet. and would display something like this.

For English language

For Russian language

I have tried this

StreamReader reader = new StreamReader(strFilepath);
string line;
while(null != (line = reader.ReadLine()))
   {                  
       string[] s1 = line.Split('>');
       string[] s2 = s1[1].Split('<');

       if(s1[0].Contains("xml:lang="))
       {

       }
   }

Logic behind this is simple first I want to split every line using ">" So s1[0] will have <btestsir xml:lang="fr" transId="HHXAF" s1[1] will have Test Sirène :</btestsir>

But here the problem I am facing is how to fetch the "key" for a specific language xml:lang= and put that key-value pair to excel sheet, As I have shown in the picture. Fetching the "value" is easy s2[0] will have the value.

But this two key-value pair should be match and then put into the excel sheet, then it will again continue for the next line. Edit: One point here the key value pair should be put into different excel-sheet for different language files. excel-sheet.en.xlsx will contain all "en", excel-sheet.fr.xlsx will contain all "fr", etc

As I said I have huge files and it should work seamlessly without manual intervention.

Can you help me please!

Thanks

They way you are doing is not ideal. You should rather be working properly with XML. — Yahya
– Yahya, Commented Oct 6, 2015 at 13:05
I know that, but I also know this code is 2002 code and I cannot do better than that, btw its not an XML file, it may look like though. Moreover to get the ideal solution I have come up here. — Debhere
– Debhere, Commented Oct 6, 2015 at 13:09

Geoffrey Fernandez · Accepted Answer · 2015-10-06 13:11:15Z

2

I would use xpath selectors and not try string manipulation of an xml file. So you could do something like declare a nodelist variable and populate it ie: using System.xml; ... XmlNodeList childNodes;

childNodes = xml.SelectNodes("ParentNodeofbtestsirNode/btestsirnode[@lang='en']); ... and go on from there doing your manipulations against each XmlNode such as foreach(XmlNode xnd in childNodes) { '... }

answered Oct 6, 2015 at 13:11

Geoffrey Fernandez

1382 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Debhere Over a year ago

But the main question is how to put those key value pair to excelsheet row by row, fetching values my code is also doing, may be not the efficient way but those values shoudl be put into the different excelsheet for different langauge

Geoffrey Fernandez Over a year ago

Are you now writing to a new excel file and you are just wondering how to create/select sheets or do you have an existing file already that maybe you are appending to?

Debhere Over a year ago

yes i am creating a new file for each language and then i am appending the values row wise in the excel sheet for that particular language, please i have updated my question

Geoffrey Fernandez Over a year ago

Then create a new sheet for each language, say the two character code you are using like "fr" and "en" and then when you have that value in s2[0] use it to select your sheet. Then find the next empty row and append it there. Use something like dim lastRow as integer lastRow = Worksheets("fr").Range("A65536").End(xlUp).Row

Debhere Over a year ago

i have updated the xml file, can you please provide more details, as "ParentNodeofbtestsirNode/btestsirnode[ is confusing to me, its huge file, I cannot change this string as this nodes keep getting changed after r repetitions, it every time changes to new node. So this node value and innertext of this node it has to fetch based upon the lang='en' can you help me.

|

Yahya · Accepted Answer · 2015-10-06 13:35:48Z

2

Something like this should do it:

XmlDocument doc = new XmlDocument();
doc.Load("sample.xml");
var root = doc.DocumentElement;
var elements = root.XPathSelectElements("//[@xml:lang='en']");


foreach (var child in elements) 
{

 //child.Name >> will give you btestsir
 //child.Value >> will give you Test Siren :
}

And to create/manage spreadsheets use EPPlus see sample here

edited Oct 6, 2015 at 13:35

answered Oct 6, 2015 at 13:12

Yahya

3,4743 gold badges25 silver badges42 bronze badges

2 Comments

Debhere Over a year ago

But the main question is how to put those key value pair to excelsheet row by row

Yahya Over a year ago

@Debhere I have added a link to my answer to EPPLus sample. Unfortunately nobody can hold your hand and walk through exact solution. This is a Q&A site.

Collectives™ on Stack Overflow

Fetch values from an xml file and write into a excel sheet row-wise

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related