C# and Excel Interop Functions

General

I have just had to write an application to complete some missing data in an Excel spreadheet from our internal database. One thing that irritated me was the dearth of information on the web regarding how to close a document properly, and ensure that the instance of Excel that the application starts is correctly colsed down.

To open the document, I used some fairly standard code.

private Excel.Application _excel;

private void openOriginalExcel(string _filename)
{
	try
	{
		_excel = new Excel.Application();
	}
	catch(Exception e3)
	{
		MessageBox.Show(e3.Message);
		return;
	}
	wb = _excel.Workbooks.Open(_filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
	_excel.Visible = true;
	ws = (Excel.Worksheet)_excel.ActiveSheet;
}

You can now access the readily documented functions to access and update the contents of the worksheet. In order to correctly close the document, you should use the following…

private void killOriginalExcel()
{
	wb.Close(null,null,null);
	_excel.Workbooks.Close();
	_excel.Quit();
	System.Runtime.InteropServices.Marshal.ReleaseComObject (_excel);
	System.Runtime.InteropServices.Marshal.ReleaseComObject (ws);
	System.Runtime.InteropServices.Marshal.ReleaseComObject (wb);
	ws=null;
	wb=null;
	_excel=null;
	GC.Collect();
}

There are now no running instances of Excel left behind by the code.

BizTalk EDI Problems

BizTalk

I have had a few problems with Biztalk this morning. I’ve been trying to validate an EDI instance file against one of the standard EDI schemas supplied with Biztalk. It was bahaving strangely. Initially, the error I was presented with was :

Could not find file “C:\DOCUME~1\USER1\LOCALS~1\Temp\validins.xml”.

Confused, I tried to use the “Generate Instance” function to try to see what was going wrong. This time, I got another error :

XSD2EDI failed to convert XSD: Compiling repository failed

A quick Google on this turned up this page in Microsoft’s knowledge base. In short, if you are logged on to the BT server with a domain account, and the EDI service is running with a machine-local account, you need to ensure that your domain account is in the “EDI Subsystem Users” group on the BT server.

As it happens, my schema instance failed to validate, but that’s life, I suppose.

Technorati Tags: , , ,

XSL for iTunes XML Export

General

I have just spent a few hours putting together an XSL stylesheet for the default output from iTunes when you export the library as XML. The real upset about the whole thing is Apples method of storing the tags as

<key>Name</key><string>value</string>

It forces a fairly hacky use of following-sibling processors in the XSL.

The code for the XSL sheet is as follows

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:fo="http://www.w3.org/1999/XSL/Format"
      xmlns:xs="http://www.w3.org/2001/XMLSchema"
      xmlns:fn="http://www.w3.org/2005/xpath-functions"
      xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
<xsl:template match="/" >
<html>
<head>
<link rel="stylesheet" type="text/css"  xhref="style.css" mce_href="style.css" />
<title><xsl:value-of select="key[1]" /></title>
</head>
<body>
<h1>music</h1>
<xsl:for-each select="plist/dict/dict/dict[not(key='Podcast')]">
<div class="track">
<span class="title"><xsl:value-of select="key/following-sibling::node()[1]" /></span>
<span class="album"><span class="artist"><xsl:value-of select="key/following-sibling::node()[1]" /></span> :: <xsl:value-of select="key/following-sibling::node()[1]" />
<xsl:if test="key">
(<xsl:value-of select="key/following-sibling::node()[1]" />)
</xsl:if>
</span>
<xsl:if test="key">
<span class="tracks"><xsl:value-of select="key/following-sibling::node()[1]" /> of <xsl:value-of select="key/following-sibling::node()[1]" /></span>
</xsl:if>
<xsl:variable name="tracklength" select="key/following-sibling::node()[1] div(1000)" />
    <span class="time"><xsl:value-of select="round($tracklength div 60)" />:
    <xsl:value-of select="round($tracklength mod 60)" /></span>
<a><xsl:attribute name="href"><xsl:value-of select="key/following-sibling::node()[1]" /></xsl:attribute>Play</a>
</div>
</xsl:for-each>
<h1>podcasts</h1>
<xsl:for-each select="plist/dict/dict/dict/key/..">
<div class="podcast">
<span class="title"><xsl:value-of select="key/following-sibling::node()[1]" /></span>
<span class="album"><span class="artist"><xsl:value-of select="key/following-sibling::node()[1]" /></span> :: <xsl:value-of select="key/following-sibling::node()[1]" />
<xsl:if test="key">
(<xsl:value-of select="key/following-sibling::node()[1]" />)
</xsl:if>
</span>
<xsl:if test="key">
<span class="tracks"><xsl:value-of select="key/following-sibling::node()[1]" /> of <xsl:value-of select="key/following-sibling::node()[1]" /></span>
</xsl:if>
<xsl:variable name="tracklength" select="key/following-sibling::node()[1] div(1000)" />
<span class="time"><xsl:value-of select="round($tracklength div 60)" />:<xsl:value-of select="round($tracklength mod 60)" /></span>
</div>
</xsl:for-each>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

As far as styling the output is concerned, you’ll have to worry about that yourself.

Technorati Tags: , , ,

Sending an HTTP POST request to a web server

C# Snippets 
ASCIIEncoding encoding = new ASCIIEncoding();
string post_url = "http://172.16.24.44/postdata.php";
string poststring = "monkeys=blue\&trousers=black";
byte[] data = encoding.GetBytes(poststring);

HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(post_url);
httpRequest.Method = "POST";
httpRequest.ContentType="application/x-www-form-urlencoded";
httpRequest.ContentLength = data.Length;
Stream newStream=httpRequest.GetRequestStream();
newStream.Write(data,0,data.Length);
newStream.Close();

HttpWebResponse response = (HttpWebResponse)httpRequest.GetResponse ();
Stream receiveStream = response.GetResponseStream ();
StreamReader readStream = new StreamReader (receiveStream);
textBox1.Text = "Response stream received.";
textBox1.Text = readStream.ReadToEnd().Replace("\\n","\\r\\n");
response.Close ();
readStream.Close ();

Google’s “Personlised Home”

General

Google have just released their “Personalised Home” for those of us with a Google account. (Gmail, for example)

It allows you to subscribe to feeds right there on the search engine home page, as well as numerous other bits of RSS goodness. I like it.

(You may be able to see it at Google.com, if you have an account…)

Spam just gets better and better!

General

This just arrived in my work inbox. It is great on so many levels…

Dear Home Owner,
After satisfying the analysis we are jubilant to provide for you this endorsement,

Your current mortgage qualifies you for more than a 3.4 lesser rate!

——————————————————————
!! ATTAINING THE LOWEST RATES IN THE US HAS NEVER BEEN EASIER !!
——————————————————————

Hundreds of thousands of residential owners have re-financed this month alone!

So why not you?

Go HERE to make that change.

If you prefer to be left out of this superfluous offer go here.

I am overjoyed to be the cause of this company’s jubliation, but I’m afraid that – even though this offer is indeed superfluous – I will probably not be clicking on that link.