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

EDN Admin · Sep 1, 2011

Hi so im 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. Ive 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 Im 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...?

View the full article

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

EDN Admin

Well-known member

Similar threads