Maarten Balliauw {blog}

ASP.NET MVC, Microsoft Azure, PHP, web development ...

NAVIGATION - SEARCH

PHPExcel 1.3.5 released

Just a quick note on the new PHPExcel 1.3.5 release. There are some cool new features included!

One of the new features is rich text: one can now write coloured and styled text in a cell. Here's an example of how the feature demo result file looks:

This is of course not all. Jakub had a couple of sleepless nights, but managed to port in the PEAR Spreadsheet classes. Meaningless? No! PHPExcel now supports Excel2007 and older versions, too. Want to write an Excel document for Excel200? No problem:

[code:c#]

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('excel2000file.xls');

[/code]

There's even a cooler part related to this, and that is .xlsx to .xls conversion! Here's how:

[code:c#]

$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load('excel2007file.xlsx');

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('excel2000file.xls');

[/code]

As always, you can get the new release on www.phpexcel.net!

ASP.NET URL rewrites using .htaccess-like syntax

Having a PHP background, I've been using .htaccess mod_rewrite in Apache for ages. ASP.NET allows rewriting too, but using a different syntax than mod_rewrite. Using the attached library, you can now use mod_rewrite syntax to perform rewrites on your ASP.NET application. Here's how...

First of all, you need to download the attached library. Reference it from your web project, and register it as a module in Web.config, preferrably as the first one:

[code:xml]

<httpModules>
    <add name="UrlRewriter" type="MaartenBalliauw.UrlRewriter.Rewriter"/>
    <!-- Other modules can be put here... -->
</httpModules>

[/code]

Second, create a file UrlRewriter.xml in the root of your web project, and add rewrite conditions in there:

[code:xml]

<?xml version="1.0" encoding="utf-8" ?>
<UrlRewriter>
    <Mapping>
        <From><![CDATA[^\/([_a-zA-Z0-9-]+).php]]></From>
        <To><![CDATA[$1.aspx]]></To>
    </Mapping>
    <Mapping>
        <From><![CDATA[^\/([_a-zA-Z0-9-]+)\/([_a-zA-Z0-9-]+)\.php]]></From>
        <To><![CDATA[Default.aspx]]></To>
    </Mapping>
    <Mapping>
        <From><![CDATA[^\/search\/region\/([_a-zA-Z0-9-]+)\/number\/(\d+)]]></From>
        <To><![CDATA[Default.aspx?region=$1&number=$2]]></To>
    </Mapping>
</UrlRewriter>

[/code]

The above code has 3 possible rewrite conditions. If a URL is in the form of "xxxx.php", it is rewritten to "xxxx.aspx". If a URL is in the form "/x/xxxx.php", it is rewritten to "Default.aspx". The third one is a bit more complicated, as it rewrites "search/region/xxxxx/number/yyyyy" to "Default.aspx?region=xxxxx&number=yyyyy". Easy, no?

Enlisting an ADO.NET command in an NHibernate transaction

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:

[code:c#]

ISession session = sessionFactory.GetSession();

using(ITransaction transaction = session.BeginTransaction())
{
    IDbCommand command = session.Connection.CreateCommand();
    command.Connection = session.Connection;

    transaction.Enlist(command);

    command.CommandText = "delete from User where activationdate < '2000-01-01'";
    command.ExecuteNonQuery();

    transaction.Commit();
}

[/code]

Commandline FTP folder download

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

My blog has just moved...

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/

 

Disclaimer

The information on this website represents my personal opinion. My opinion may differ with other people's opinion and my employer's opinion. This website is by no means related to other people nor my employer.

Copyright and contents

Texts, lay-out, images, script and other items on this site are protected by copyright. Copies, adaptations, translations, versions, modifications of whole or of a part of this site, in any form or manner, are prohibited, unless preceding written authorisation has been granted by the owner of this site. Each violation can lead to civil rights or criminal prosecution. Deeplinking is allowed when mentioning the original source.

External information

This Internet site contains links to Internet sites or to web pages of third parties. I have no control of the contents or other characteristics of these sites and I am under no circumstances responsible for the contents of it.

Blog comments

It is allowed for registered people to post comments on articles I write. I have no control of the contents or other characteristics of these comments and I am under no circumstances responsible for the contents of it. You can contact me in order to remove certain comments.

Link to this site

It is permitted, without preceding written authorisation of the website owner, to put a link between your site and this site.

Purpose of using personal information

If you transfer your personal details to me, these are incorporated in my files and are processed for the management of my member file and the offered service. Data are under no circumstances passed on without your personal authorisation to third parties.

Cookies

To offer you an optimal service, I make use of "cookies", which are a small piece information that is stored on your computer: these cookies can be picked up during a later visit to the same site. The cookie are not possible read by another Internet site than my website. You have the possibility in your browser to configure it that it informs you each time cookies are produced or used. Blocking cookies of my site may result in incorrect behaviour of my website.

OpenXML news overview

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.

New PHPExcel release: 1.3.0

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!