May 2007

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]

iWoz
JavaScript - The Definitive Guide
Object Thinking
The Art of Interactive Design

Got iWoz and JavaScript today via Amazon (sneak peaked into it), Object Thinking is a bit boring at the beginning but overally good and informative (some background infos which save you some googling).

- Monolithic 2-tier Architecture
- 3.3 Million Line C++ ISAPI DLL (150MB binary)
- Hundreds of developers, all working on the same code
- Hitting compiler limits on number of methods per class (!!)

The eBay Architecture