Extract values in xml files and transfer them to excel worksheet in c#

[caseyl89]

Hi so i'm working on a project in c#. I have hundreds of xml files and I need to extract 6 values from each of the xml files and somehow display them on an excel datasheet. Here is an example of the xml file:

<XAxisCalib>
          <Max>288.93</Max>

          <Min>-48.08</Min>

          <MaxS>200</MaxS>

          <DfTHt>0</DfTHt>

          <LdVe>0</LdVe>

          <EjVe>0</EjVe>

        </XAxisCalib>

        <YAxisCalib>

          <Max>646.01</Max>

          <Min>-90.9</Min>

          <MaxS>200</MaxS>

          <DfTHt>0</DfTHt>

          <LdVe>0</LdVe>

          <EjVe>0</EjVe>

        </YAxisCalib>

        <ZAxisCalib>

          <Max>16.1</Max>

          <Min>-146.82</Min>

I need to extract the <Max> and <Min> value of the x,y,and z calib. How can I do this. I've done tons of research but no examples show some of the xml file all sources are to extract all of the xml values to the excel sheet. This is an example of what I have so far tell me if I'm on the right track....

using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Xml.XPath;
using System.Xml;
using System.Collections;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel; 

namespace MaxMinParser
{
    class Program
    {
        static void Main(string[] args)
        {
            using System;

 //calls for Sciclone Cfg files to be accessed
                string appContent = ""; 
            FileStream appFile = new FileStream("c:\\Sciclone UAC.cfg", FileMode.Open, FileAccess.ReadWrite);
            StreamReader myReader = new StreamReader(appFile);
            appContent = myReader.ReadToEnd();
            appFile.Close();
            myReader.Close();

            SummaryRecord summaryRecord;
            SummaryRecordsCollection summaryRecords = new SummaryRecordsCollection();

    //creates a summary/results file of the MaxMin Values

            ResultsParser resultsParser;
            string summaryFile = Properties.Settings.Default.SummaryFile;
            int resultCount = Int32.Parse(Properties.Settings.Default.ResultCount);           
             
            resultsParser = new ResultsParser();

            
                            Stream xmlStream = GetXMLStream(fileName);
                            System.Diagnostics.Debug.WriteLine(fileName);
                            XmlReaderSettings settings = new XmlReaderSettings();
                            settings.ConformanceLevel = ConformanceLevel.Document;
                            settings.IgnoreComments = true;
                            settings.IgnoreWhitespace = true;
                            XmlReader reader = XmlReader.Create(xmlReader, Settings);
                            
//Using the config files, calls for the X,Y,Z max/min values for each build
            XPathDocument resultsXMLDoc = new XPathDocument(reader);
            int XmaxTests = resultsParser.GetTestResultCount(resultsXMLDoc, "<XAxisCalib>" + result "</Max>");
            int XminTests = resultsParser.GetTestResultCount(resultsXMLDoc, "<XAxisCalib>" + result "</Min>");
            int YmaxTests = resultsParser.GetTestResultCount(resultsXMLDoc, "<YAxisCalib>" + result"</Max>");
            int YminTests = resultsParser.GetTestResultCount(resultsXMLDoc, "<YAxisCalib>" + result "</Min>");
            int ZmaxTests = resultsParser.GetTestResultCount(resultsXMLDoc, "<ZAxisCalib>" + result "</Max>");
            int ZminTests = resultsParser.GetTestResultCount(resultsXMLDoc, "<ZAxisCalib>" + result "</Min>");
            summaryRecord = new SummaryRecord(fileName, XmaxTests, XminTests, YmaxTests, YminTests, ZmaxTests, ZminTests);
                 summaryRecords.AddRecords(summaryRecord);
                       }
                    }
                }
            }
            if (summaryRecords.RecordCount > 0)
            {
               if (File.Exists(summaryFile))
                {
                    File.Delete(summaryFile);
                }
                 StreamWriter MaxMinResults = new StreamWriter(summaryFile, false);
        
            
                 foreach(SummaryRecord record in summaryRecords)
                {
                MaxMinResults.WriteLine("<SummaryRecord>");
                MaxMinResults.WriteLine("<XMax>" + record.XMax + "</XMax>");
                MaxMinResults.WriteLine("<XMin>" + record.XMin + "</XMin>");
                MaxMinResults.WriteLine("<YMax>" + record.YMax + "</YMax>");
                MaxMinResults.WriteLine("<YMin>" + record.YMin + "</YMin>");
                MaxMinResults.WriteLine("<ZMax>" + record.ZMax + "</ZMax>");
                MaxMinResults.WriteLine("<ZMin>" + record.ZMin + "</ZMin>");
                MaxMinResults.WriteLine("</SummaryRecord>");
                MaxMinResults.Flush();
            
            {
         Excel.Workbooks objBooks;
         Excel.Sheets objSheets;
         Excel._Worksheet objSheet;
         Excel.Range range;
         try
         {
//opens Excel workbook and creates a worksheet and calls for sheet1
{            
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));


// Creates an array for the headers and add it to cells A1 through F1.
object[] objHeaders = {"XMax", "XMin", "YMax", "YMin", "ZMax", "ZMin"};
m_objRange = m_objSheet.get_Range("A1", "F1");
m_objRange.Value = objHeaders;

//creates an array with 200 rows and 6 columns that will
// add the specified data to the specified row and column
object[,] objData = new Object[200,6];
for(int r=0;r<200;r++)
{
	objData[r,0] = ("int XMaxTests" + value);
	
	objData[r,1] = ("int XMinTests" + value);
	
	objData[r,2] = ("int YMaxTests" + value);

	objData[r,3] = ("int YMinTests" + value);

	objData[r,4] = ("int ZMaxTests" + value);

	objData[r,5] = ("int ZMinTests" + value);

}
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange = m_objRange.get_Resize(200,6);
m_objRange.Value = objData;


// Save the Workbook
m_objBook.SaveAs(m_strSampleFolder + "MaxMinValues.xls", m_objOpt, m_objOpt, 
	m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, 
	m_objOpt, m_objOpt, m_objOpt, m_objOpt);

m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
        }
    }
}

Any help would be appreciated please...?

[kvprajapati]

>I need to extract the <Max> and <Min> value of the x,y,and z calib.

Use Xml Linq to read/parse XML documents. Its an easy to understand and learn.

Ex:

string file = @"c:\file.xml";
  XDocument doc = XDocument.Load(file);

  int i = 0;

  var result = from ele in doc.Descendants()
                    where ele.HasElements == false //read leaf node
                       select new
                         {
                             ParentName=ele.Parent.Name,
                             Name=ele.Name ,
                             Value=ele.Value 
                            

                         };
  foreach (var ele in result )
     {
          Console.WriteLine(ele.ParentName + " " + ele.Name + " " + ele.Value);
     }

[caseyl89]

Hi adatapost thanks for your reply.

For example you show how do I extract the max and min value out from the document though, in this line:

Console.WriteLine(ele.ParentName + " " + ele.Name + " " + ele.Value);

in the quotes do I write "<xcalib>" and "max" then another line doing "min" and so forth?

Also I don't want to write the element result to the computer I want to display them in an excel spreadsheet. The xmax in one column min in the other and so forth. is their an easy way to do this. I've never used linq before.

[caseyl89]

So I have this so far for the XAxisCalib. Does this look right I get a few syntax errors like max and min don't exist is current context. Instead of writing out result can I save to file or dataset somehow to intergrate into excel?

using System;
using System.Collections;
using System.Xml.Linq;
using System.Linq;
using System.Xml;
using System.Text;



namespace TestCFG
{
    class Program
    {
        static void Main(string[] args)
        {
              string file = @"c:\ScicloneUAC.xml"; 
            XDocument doc = XDocument.Load(file);   
            var query = from x in doc.Descendants("XAxisCalib")                   
                         where x.HasElements == false               
                         select new                                                 {                                                                                    
                             Max=x.Element("Max").value,
    Min=x.Element("Min").value,                                                     
                         };  
            foreach (var XAxisCalib in query)    
            {                                                                                                         
                Console.WriteLine(" " + Max + "\n");
                Console.WriteLine(" " + Min + "\n");
            }