Export a DataSet to Microsoft Excel (the XML way)

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]