Wednesday 13 December 2006

Xml Compare and Excel Routines

A few days ago I mentioned the Xml Compare and Excel Routines that I use for testing. I'm posting them here ... they are quite handy routines. Both are written in C#.

Xml Compare:


/*

COPYRIGHT © 2006 George Zabanah

Please feel free to use this code and distribute,

but leave this header at the top.

No warranty of any kind is implied by using this code.

Use at your own risk.

*/




/// <summary>

/// Displays the difference between two xml strings

/// </summary>

/// <param name="expected">expected result</param>

/// <param name="actual">actual result</param>

private void XmlCompare(string expected,string actual)

{

XmlDocument xExpected = new XmlDocument();

xExpected.LoadXml(expected);



XmlDocument xActual = new XmlDocument();

xActual.LoadXml(actual);

System.Xml.XPath.XPathNavigator xeNav = xExpected.CreateNavigator();

System.Xml.XPath.XPathNavigator xaNav = xActual.CreateNavigator();



// Move to the first non-comment element.

xeNav.MoveToChild(System.Xml.XPath.XPathNodeType.Element);

xaNav.MoveToChild(System.Xml.XPath.XPathNodeType.Element);



System.Xml.XPath.XPathNodeIterator xeNodeIterator = xeNav.SelectDescendants(System.Xml.XPath.XPathNodeType.Element,true);

System.Xml.XPath.XPathNodeIterator xaNodeIterator = xaNav.SelectDescendants(System.Xml.XPath.XPathNodeType.Element,true);

TestContext.WriteLine(string.Empty.PadLeft(80, '='));

TestContext.WriteLine("XML COMPARISON");

TestContext.WriteLine(string.Empty.PadLeft(80, '='));

while (xeNodeIterator.MoveNext() && xaNodeIterator.MoveNext())

{



if (xeNodeIterator.Current.Name != xaNodeIterator.Current.Name)

{

TestContext.WriteLine("Actual Node Name: {0} Expected Node Name: {1}", xaNodeIterator.Current.Name, xeNodeIterator.Current.Name);

// return;

}

if ((xeNodeIterator.Current.Value == xeNodeIterator.Current.InnerXml xaNodeIterator.Current.Value == xaNodeIterator.Current.InnerXml) && xeNodeIterator.Current.Value != xaNodeIterator.Current.Value)

{

TestContext.WriteLine(String.Empty.PadLeft(80, '-'));

TestContext.WriteLine("Actual Node \t{0}: {1} \r\nExpected Node \t{2}: {3}", xaNodeIterator.Current.Name,xaNodeIterator.Current.Value, xeNodeIterator.Current.Name,xeNodeIterator.Current.Value);

TestContext.WriteLine(String.Empty.PadLeft(80, '-'));

}

}



}






Excel Routines:


/*

COPYRIGHT © 2006 George Zabanah

Please feel free to use this code and distribute,

but leave this header at the top.

No warranty of any kind is implied by using this code.

Use at your own risk.

*/




public static class ExcelFunctions

{

public static void WriteToExcel(DataSet ds, String MyFileName, bool Append)

{

string connect3 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + MyFileName + "\";User ID=Admin;Password=;Extended Properties=\"Excel 8.0;HDR=YES\";";

OleDbConnection _to = new OleDbConnection(connect3);

bool blnFileExists = File.Exists(MyFileName);

if (blnFileExists && !Append) File.Delete(MyFileName);

_to.Open();

foreach (DataTable dt in ds.Tables)

{

if (!Append !blnFileExists)

{

String table = "CREATE TABLE " + dt.TableName + " (";

foreach (DataColumn dc in ds.Tables[dt.TableName].Columns)

{

String typename = " [" + dc.ColumnName + "] " + (dc.DataType.ToString() == "System.Int64" dc.DataType.ToString() == "System.Double" dc.DataType.ToString() == "System.Int32" ? "NUMERIC" : "TEXT");

table += typename + (dc == ds.Tables[dt.TableName].Columns[ds.Tables[dt.TableName].Columns.Count - 1] ? ")" : ",");

}

OleDbCommand cmd = new OleDbCommand(table, _to);

try

{

cmd.ExecuteNonQuery();

}

catch (Exception e)

{

_to.Close();

_to.Dispose();

Console.Error.WriteLine(e.Message);

Console.Error.WriteLine(e.StackTrace);

return;

}

}

OleDbDataAdapter daTo = new OleDbDataAdapter("select * from [" + dt.TableName + "$]", _to);

OleDbCommandBuilder x = new OleDbCommandBuilder(daTo);

x.QuotePrefix = "[";

x.QuoteSuffix = "]";

daTo.InsertCommand = x.GetInsertCommand();

try

{

daTo.Update(ds, dt.TableName);

}

catch (Exception e)

{

Console.Error.WriteLine(e.Message);

Console.Error.WriteLine(e.StackTrace);

}



}

_to.Close();

_to.Dispose();

}

public static DataSet ReadFromExcel(string MyFileName)

{

string connect3 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + MyFileName + "\";User ID=Admin;Password=;Extended Properties=\"Excel 8.0;HDR=YES\";";

OleDbConnection _to = new OleDbConnection(connect3);

DataSet ds = new DataSet();

_to.Open();

DataTable schemaTable = _to.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,

new object[] { null, null, null, "TABLE" });

foreach (DataRow dr in schemaTable.Rows)

{

OleDbDataAdapter daTo = new OleDbDataAdapter(dr["TABLE_NAME"].ToString(), _to);

daTo.SelectCommand.CommandType = CommandType.TableDirect;

daTo.AcceptChangesDuringFill = false;

if (dr["TABLE_NAME"].ToString().IndexOf("$") == dr["TABLE_NAME"].ToString().Length - 1)

{

daTo.Fill(ds, dr["TABLE_NAME"].ToString());

ds.Tables[dr["TABLE_NAME"].ToString()].TableName = dr["TABLE_NAME"].ToString().Replace("$", "");

}

}

_to.Close();

return ds;



}



}






Also, I have used this handy little utility to generate the HTML (from the code) by doing the following.

1) Install RTF to HTML utility found here (freeware).
2) Open the code in VS2005.
3) Run the RTFtoHTML utility. Convert to HTML and post in your blog.

I'd be interested to know if someone has a better way of doing this.

No comments: