Maarten Balliauw {blog}

For everyone who has read my article on NHibernate, here's a story for you...

When building an application, everyone comes to a point where one needs to batch-update records in a database table, based on one or more criteria. Let's say, for example, there's a table "User" containing an activation date. And you want to remove all users that have activated in 1999. In a regular database environment, or when using ADO coding, one would write a DbCommand "DELETE FROM User WHERE activationdate < '2000-01-01'".

This can also be done using NHibernate, by fetching an IList<User> from your database, and calling session.Delete(user); for each user in the list. Another idea is to use a HQL query: session.Delete("from User u where u.ActivationDate < '2000-01-01'"); A good thing about NHibernate is that it supports caching of data, but for this batch-delete purpose, it sucks. NHibernate will, in both previous cases, fetch all affected data, map it to objects, store it in first-level cache, ... Overhead galore!

Luckily, I saw a blog post on this by jlockwood. He simply tells to enlist a regular SQL statement in a NHibernate transaction, and you're ready to go. His code isn't provider-independent, so here's an improved version:

A quick annoyed post... I just spent two hours searching the Internet for a means on how to recursively download a complete FTP folder, command-line, and in a simple way. Oh yeah, and preferably freeware.

The solutions I found were not what I expected: a $50 software product providing a GUI (I said command-line! [:@]), a bloated scheduler thingy that does download in the background (I said simple! [8o|]), to batch-files relying on Windows built-in ftp.exe and a gigantic list of all files that need to be downloaded.

Here's the thing: the searching really p*ssed me off! Not one thing provides the amount of ease I demand! Luckily, my good friend C# came to the rescue. CodeProject.com provided me this article on a ready-to-use FTP client class. Some additional magic, a glass of cola and... Here's FTPFolderDownload version 1.0! Feel free to download, compile, modify, abuse, ... this piece of code.

Usage is simple: pass along some command line arguments (list is below), and see your FTP files coming in.

List of arguments:
        /server="<server hostname>"
        /username="<username>"
        /password="<password>"
        /remoteFolder="<remote folder>"
        /localFolder="<local folder>"
        /recursive

Just finished painting, unpacked some boxes, and here we are: a new home! The people from Eurobesthosting.com (shameless commercial plug in my blog) provided me my own ASP.NET server, which is now serving this page/RSS feed to you!

A new home also means new URL's... I did my best forwarding all old URL's to this new page, but I won't keep the forwarding for the next century... The only noteworthy thing for you as a RSS reader, is the RSS feed URL, which has changed to: http://blog.maartenballiauw.be/rss.aspx.

If you placed a link to my blog on your website, please change that link to http://blog.maartenballiauw.be/. 

A lot of news around OpenXML these days, so I decided to bundle some things into one big blog post.

1. Microsoft released a Microsoft SDK for Open XML Formats

In .NET 3.0, there's the System.IO.Packaging API, which allows programmatic access to OpenXML packages (amongst them Office2007 files).
Since this API is quite low-level, the Microsoft people introduced a new SDK built on top of System.IO.Packaging, which allows you to use strongly typed classes for document parts. Checkout a code sample on Wouter's blog and see for yourself: this SDK provides access to an OpenXML package in a much easier way than System.IO.Packaging. Download the SDK here.

2. PackageExplorer 3.0 beta

Wouter released a new (beta) version of his PackageExplorer, and I assume he uses the new SDK mentioned above. Main new feature seems to be adding document parts using a template system, allowing you and I to create an OpenXML package using a set pre-defined templates. You can download PackageExplorer on CodePlex.

3. Altova XML Spy supports OpenXML

I saw this on Altova's website: "XMLSpy provides powerful support for accessing, editing, transforming, and querying XML data saved in Microsoft® Office 2007 documents and other zipped files."
Says enough, I think. You can download a free trial to check if this all is true.

4. Trying to compile PHPExcel using Phalanger

The last few days, I've been trying to compile PHPExcel to a .NET class library using Phalanger. Phalanger is a PHP compiler for .NET.
Compiling works quite well, but not all class definitions are compiled into a usable .NET alternative... Creating and saving a Spreadshete currently works, but adding data into cells doesn't. I guess thats a feature that can not be missing :-)
I'll keep you informed on the progress of this. If anyone feels interested in porting this PHP library to C#, please contact me!

5. OpenXML for JAVA

JAVA people now also have an OpenXML library: OpenXML4J. Not production-stable yet, but alpha versions are available.

Some new TFS links I found:

Team Development with Visual Studio Team Foundation Server Guide
http://www.codeplex.com/TFSGuide

Work Item Creator
http://www.codeplex.com/wicreator/

The new version of PHPExcel has just been released, bringing 1.3.0 to the public. New features include formula calculation, inserting and removing columns/rows, auto-sizing columns, freezing panes, ...

One of the new features in PHPExcel is formula calculation. Just like Excel or any other spreadsheet application, PHPExcel now provides support for calculating certain cell values, using a formula. For example, the formula "=SUM(A1:A10)" evaluates to the sum of values in A1, A2, ..., A10.

Have a look at this: if you write the following line of code in the invoice demo included with PHPExcel, it evaluates to the value "64":

$objPHPExcel->getActiveSheet()->getCell('E11')->getCalculatedValue();

Another nice feature of PHPExcel's formula parser, is that it can automatically adjust a formula when inserting/removing rows/columns. Here's an example:

You see that the formula contained in cell E11 is "SUM(E4:E9)". Now, when I write the following line of code, two new product lines are added:

$objPHPExcel->getActiveSheet()->insertNewRowBefore(7, 2);

Did you notice? The formula in the former cell E11 (now E13, as I inserted 2 new rows), changed to "SUM(E4:E11)". Also, the inserted cells duplicate style information of the previous cell, just like Excel's behaviour.

Curious about all this? Want to play with it? Find the source code download and demo code on www.phpexcel.net!

One of the new (planned) features of PHPExcel is to implement parsing and calculating Excel formulas. One thing every developer should do is not to try to reinvent the wheel. Therefore, a Google search learned me someone wrote a Excel expression parser in JavaScript, which parses an expression into a tree.

Parsing Excel formulas (expressions) in JavaScript is done here. Someone ported this to C# too, and as of today, it is ported to PHP5 too 8-).

The only thing left to do is building this into PHPExcel, and performing calculations using the parsed tree...

Have you ever used the ASP.NET PasswordRecovery control, or the CreateUserWizard? Probably, you used the mail capabilities of these controls too, and set up a MailDefinition to send an e-mail when the control did his job. Personally, I missed this functionality when wanting to send mails to users.

Luckily, ASP.NET is very extensible. I decided to create my own control providing an easy and convenient way to sending templated e-mails. Just set the From, CC, Subject and Body properties in the designer, and use the Send() method from code. Not the cleanest implementation of catching SMTP errors, but it was sufficient for my use. If you need to catch SMTP errors, you still need to add that... Anyway, as a gift for anyone who needs it, please find my SendMailControl underneath:

Yay! My new article on Excel, OpenXML and PHP has just been released in php|architect! A copy-paste action from http://www.phparch.com/issue.php?mid=102:

"A few months ago, Microsoft released Office 2007, a version of their office suite that generates open source documents. Here, Maarten Balliauw gives an overview of the history and the politics surrounding the release before moving on to introduce his PHPExcel project, an early adoption of Microsoft's OpenXML API that enables Excel 2007 spreadsheets to be generated with PHP."