Coding

There’s an article at codeproject.com which transform a .NET DataSet to an XML Spreadsheet document which can be opened in Microsoft Excel. Unfortunately the code doesn’t seem to be generate valid XML, at least not for Excel. I’ve modified it but didn’t use some parts of if (e.g. I don’t use the time formatting at the moment so I stripped out all the styles using it,…).

Here’s the new code:

[code lang="csharp"] ///

system.io.streamwriter exceldoc;

exceldoc = new system.io.streamwriter(filename);
const string startexcelxml = 
  "<?xml version=\"1.0\"?>\r\n" +
  "<?mso-application progid=\"excel.sheet\"?>\r\n" +
  "<workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
  " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" +
  " xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n" +
  " xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n" +
  " <styles>\r\n" +
  "  <style ss:id=\"default\" ss:name=\"normal\">\r\n" +
  "   <alignment ss:vertical=\"bottom\"/>\r\n" +
  "   <borders/>\r\n" +
  "   <font/>\r\n" +
  "   <interior/>\r\n" +
  "   <numberformat/>\r\n" +
  "   <protection/>\r\n" +
  "  </style>\r\n" +
  "  <style ss:id=\"bold\">\r\n" +
  "   <font x:family=\"swiss\" ss:bold=\"1\"/>\r\n" + 
  "  </style>\r\n" + 
  " </styles>\r\n";
const string endexcelxml = "</workbook>";

int rowcount = 0;
int sheetcount = 1;

exceldoc.write(startexcelxml);
exceldoc.write("<worksheet ss:name=\"sheet" + sheetcount + "\">\r\n");
exceldoc.write("<table>\r\n");

for (int x = 0; x < source.tables[0].columns.count; x++)
{
  int width = source.tables[0].columns[x].columnname.length * 8;
  exceldoc.write("<column ss:width=\"" + width + "\"/>\r\n");
}

exceldoc.write("<row ss:styleid=\"bold\">\r\n");

for (int x = 0; x < source.tables[0].columns.count; x++)
{
  exceldoc.write("<cell><data ss:type=\"string\">");
  exceldoc.write(source.tables[0].columns[x].columnname);
  exceldoc.write("</data></cell>\r\n");
}

exceldoc.write("</row>\r\n");

foreach (datarow x in source.tables[0].rows)
{
  rowcount++;
  //if the number of rows is > 64000 create a new page to continue output
  if (rowcount == 64000)
  {
    rowcount = 0;
    sheetcount++;
    exceldoc.write("</table>\r\n");
    exceldoc.write(" </worksheet>\r\n");
    exceldoc.write("<worksheet ss:name=\"sheet" + sheetcount + "\">\r\n");
    exceldoc.write("<table>\r\n");
  }

  exceldoc.write("<row>\r\n"); //id=" + rowcount + "

  for (int y = 0; y < source.tables[0].columns.count; y++)
  {
    system.type rowtype;
    rowtype = x[y].gettype();
    switch (rowtype.tostring())
    {
      case "system.string":
        string xmlstring = x[y].tostring();
        xmlstring = xmlstring.trim();
        xmlstring = xmlstring.replace("&", "&amp;");
        xmlstring = xmlstring.replace(">", "&gt;");
        xmlstring = xmlstring.replace("<", "&lt;");
        exceldoc.write("<cell><data ss:type=\"string\">");
        exceldoc.write(xmlstring);
        exceldoc.write("</data></cell>\r\n");
        break;
      case "system.datetime":
        //excel has a specific date format of yyyy-mm-dd followed by  
        //the letter 't' then hh:mm:sss.lll example 2005-01-31t24:01:21.000
        //the following code puts the date stored in xmldate 
        //to the format above
        datetime xmldate = (datetime)x[y];
        string xmldatetostring = ""; //excel converted date
        xmldatetostring = xmldate.year.tostring() +
          "-" +
          (xmldate.month < 10 ? "0" +
          xmldate.month.tostring() : xmldate.month.tostring()) +
          "-" +
          (xmldate.day < 10 ? "0" +
          xmldate.day.tostring() : xmldate.day.tostring()) +
          "t" +
          (xmldate.hour < 10 ? "0" +
          xmldate.hour.tostring() : xmldate.hour.tostring()) +
          ":" +
          (xmldate.minute < 10 ? "0" +
          xmldate.minute.tostring() : xmldate.minute.tostring()) +
          ":" +
          (xmldate.second < 10 ? "0" +
          xmldate.second.tostring() : xmldate.second.tostring()) +
          ".000";

        exceldoc.write("<cell><data ss:type=\"datetime\">");
        exceldoc.write(xmldatetostring);
        exceldoc.write("</data></cell>\r\n");
        break;
      case "system.boolean":
        exceldoc.write("<cell><data ss:type=\"string\">");
        exceldoc.write(x[y].tostring());
        exceldoc.write("</data></cell>\r\n");
        break;
      case "system.int16":
      case "system.int32":
      case "system.int64":
      case "system.byte":
        exceldoc.write("<cell><data ss:type=\"number\">");
        exceldoc.write(x[y].tostring());
        exceldoc.write("</data></cell>\r\n");
        break;
      case "system.decimal":
      case "system.double":
        exceldoc.write("<cell><data ss:type=\"number\">");
        exceldoc.write(x[y].tostring());
        exceldoc.write("</data></cell>\r\n");
        break;
      case "system.dbnull":
        exceldoc.write("<cell><data ss:type=\"string\">");
        exceldoc.write("");
        exceldoc.write("</data></cell>\r\n");
        break;
      default:
        xmlstring = x[y].tostring();
        xmlstring = xmlstring.trim();
        xmlstring = xmlstring.replace("&", "&amp;");
        xmlstring = xmlstring.replace(">", "&gt;");
        xmlstring = xmlstring.replace("<", "&lt;");
        xmlstring = xmlstring.replace("\r\n", " - ");
        exceldoc.write("<cell><data ss:type=\"string\">\r\n");
        exceldoc.write(xmlstring);
        exceldoc.write("</data></cell>\r\n");
        break;
      //throw (new exception(rowtype.tostring() + " not handled."));
      }
    }
  exceldoc.write("</row>\r\n");
}

exceldoc.write("</table>\r\n");
exceldoc.write(" </worksheet>\r\n");
exceldoc.write(endexcelxml);
exceldoc.close();

} } [/code]

I suppose that Justin wanted to play around with the shifting operators, because there’s an easier way to convert a number from decimal to hex representation:

[code lang="javascript"] Number.prototype.toHex = function() { return this.toString(16) }

(255).toHex(); //ff [255, 255, 255].invoke(’toHex’).join(”); //ffffff [/code]

If you ever want to use Postgres along with Ruby under Windows, go the straight way: gem install ruby-postgres Just a hint - took me some hours.

If you wanna know where all the great code snippets of The Daily WTF come from, read Why Can’t Programmers.. Program?.

[code lang="ruby"] require ‘uri’ require ‘net/http’ uri = URI::HTTP.build(:host => “rpc.bloglines.com”, :path => “/update”, :query => “ver=1&user=user@domain.com”) number = Net::HTTP.get(uri).strip!.delete!(”|”) p “You have #{number} unread messages in your Inbox.” [/code]

btw, yes, I still use Bloglines. John Hicks’ OS X skin makes it look beautiful.

Thomas Fuchs, the creator of script.aculo.us, just returned from the “Canada on Rails” meeting where he did a presentation about the new RJS features in Rails 1.1 (you can get the slides here).

To tell the truth: That looks awesome! It has never been so easy to create a webapplication (application defined as something that really has interactivity). It’s a pity that I’m quite busy at the moment and do not have time to play around with these new features. I actually didn’t even have the time to install Rails 1.1. Shame on me.

Time for a short news-wrap. So, what’s new in the land of Rails?

At first we have a wonderful, time consuming, ideal-for-office-hours new webapp: Iron Sudoku. Quite addicting and well done. Then some news for more-the-techguy: Robot Co-op tell us something about their hardware in use (2.5 million requests per day, quite impressive). There’s another article about hardware and especially scaling, The adventures of scaling, Stage I written by Patrick Lenz, who’s responisble for eins.de, a community network with about 1.2 million page impressions per day. And, there’s even an interview with Tobias Lutke from Shopify on slash7. So, some exciting days for people interested in Rails and webapps. And before I forget: if your Rails application hangs without a trivial cause, you should maybe make a checkout from svn.

You’re really a lucky man if you own a Mac because then it seems fairly easy to use Switchtower Capistrano for your application deployment (and if you aren’t a Dreamhost customer you may be even happier). But, after about six hours of trying nearly every configuration, I finally managed to deploy my neat little Rails application to my Dreamhost space. Which was really a huge success for me and I’m sure even my neighbours noticed that ;-)

There are serveral guides and tutorials out there which cover this topic but if you wanna know about my configuration, just drop me an email or leave a comment.

If you ever plan to make use of formremotetag make sure that your xhtml code validates. Because that can really save you hours and hours of guessing why you don’t get any data from the form. My problem was that it’s not valid to put the form into a table data cell, it has to be outside of the whole table.

And, just as a little note, if it’s a normal form (using POST or GET), it works both ways.

[code lang="rails"] <%= form_remote_tag( :url => { :action => ‘add’ }, :update => ‘items’, :loading => “Element.show(’spinner’)”, :complete => “Element.hide(’spinner’), $(’item_user_name’).value = ”, $(’item_email’).value = ”, Effect.toggle(’form’, ’slide’)” ) %>

<%= end_form_tag %> [/code]

You may have noticed, and I’ve already reported about this in another article, that installing fcgi on Windows is impossible. Even running Lighttpd isn’t quite easy, especially for newcomers. So this is why I’m writing this step-by-step guide, to make your life easier (at least I hope so - if you don’t like it, send me an email or design an anti-step-by-step-guide-button). Another note: I won’t cover the topic ‘How to install Ruby on Windows’ here. There’s a good entry about that on the Ruby on Rails Wiki. You basically only have to get the Ruby one-click installer and install RubyGems, a package manager.

1. Lighttpd for Windows

Lighttpd is actually designed for Linux and Darwin so it’s a bit hard to compile it on your own, especially if you don’t have cygwin installed. Generously there’s a person like Kevin Worthington who has a on category on his blog where you can inform yourself of the latest compiled versions of Lighttpd for Windows (at the moment that’s 1.4.10a). One note: Lighttpd for Windows is sometimes still buggy so I wouldn’t recommend it for production but it’s really fine for testing and development.

Installing it should be really no problem.

2. SCGI Ruby on Rails Runner

Thus FastCGI won’t work with Lighttpd under Windows, we need an alternative: SCGI, which is at least as fast as FastCGI and a lot easier to install. You first have to download the gem package and install it with RubyGems:

[code lang="bash"]gem install scgi_rails-0.4.3.gem[/code]

3. Configuring Lighttpd

You now have to change your settings for Lighttpd. These are written down in lighttpd.conf, which should be in a folder called ‘etc’ in your Lighttpd installation folder. Make a copy of it and alter the file like that:

[code lang="bash"] server.modules = ( “modrewrite”, “modredirect”, “modaccess”, “modaccesslog”, “modstatus”, “modscgi”)

server.document-root = “” server.errorlog = “” accesslog.filename = “” static-file.exclude-extensions = ( “.scgi” ) server.error-handler-404 = “/dispatch.scgi” scgi.server = ( “dispatch.scgi” => (( “host” => “127.0.0.1″, “port” => 9999, “check-local” => “disable” )) ) scgi.debug = 3 status.status-url = “/server-status” status.config-url = “/server-config” [/code]

Note the line which says ’scgi.debug = 3′. That means that your scgi-server will log a lot of debugging-information. Change this to 0 if you want to run in deployment mode (which you shouldn’t do on Windows anyway).

4. Configuring SCGI

Open a command window, change to your ruby application directory and type: [code lang="bash"]scgictrl config[/code] It will ask you for your password. Write it down and don’t tell anybody. The command will generate a scgi.yaml file in your config directory where you can edit the mode of your scgi server (production | development) and some other stuff. scgictrl has some other nice abilities, like monitoring your application (just add ‘monitor’ after the scgi_ctrl command), which you can read about in the SCGI Ruby Runner Howtos

5. Starting the servers

Now the only thing we have to is starting our servers. That’s Lighttpd and SCGI: [code lang="bash"] lighttpd.exe -f \etc\lighttpd.conf scgi_service [/code] You have to execute this from within your rails application folder. And basically that’s it. To kill your servers use these lines: [code lang="bash"] process.exe -k lighttpd.exe > nul process.exe -k ruby.exe > nul [/code]

If you have any questions, just write me an email or post a comment (and don’t be afraid of telling me your opinion, I don’t bite, usually)