Export LinqPAD results to Excel file without the rowcount metadata

Question

I was trying to write to an excel file just the resultset from a query but I keep getting the header column with the row count, which is messing up the subsequent data processing I need to do. I could go in the exported file and delete the first row, but it would be much better if I could export a dataset without the header row.

Here's my hack, I wonder if anyone has a better way to do it. I am taking the generated html and using regex to yank out the header row:

public string DumpToHtmlString<T>(T objectToSerialize, string filePath )
    {
        string strHTML = "", outpuWithoutHeader ="";
        try
        {
            var writer = LINQPad.Util.CreateXhtmlWriter(true);
            writer.Write(objectToSerialize);
            strHTML = writer.ToString();
            outpuWithoutHeader = Regex.Replace(strHTML, "<tr><td class=\"typeheader\"((\\s*?.*?)*?)<\\/(tr|TR)>", "", RegexOptions.Multiline);
            System.IO.File.WriteAllText(filePath, outpuWithoutHeader );

        }
        catch (Exception exc)
        {
            Debug.Assert(false, "Investigate why ?" + exc);
        }
        return outpuWithoutHeader;
    }

Answer 1

Is the objectToSerialize an IEnumerable? If so, the LINQPad beta has a WriteCsv method which is designed to create Excel-friendly CSV files:

Util.WriteCsv(data, @"c:\temp\results.csv");

Otherwise, you're safer using the LINQ-to-XML DOM for modifying the output rather than regex. The following code illustrates how to remove formatting from LINQPad output; you can adapt it to remove headings and totals as well:

XDocument doc = XDocument.Load (...);
XNamespace xns = "http://www.w3.org/1999/xhtml";

doc.Descendants (xns + "script").Remove ();
doc.Descendants (xns + "span").Where (el => (string)el.Attribute ("class") == "typeglyph").Remove ();

doc.Descendants ().Attributes ("style").Where (a => (string)a == "display:none").Remove ();

doc.Descendants (xns + "style").Remove ();
doc.Descendants (xns + "tr").Where (tr => tr.Elements ().Any (td => (string)td.Attribute ("class") == "typeheader")).Remove ();
doc.Descendants (xns + "i").Where (e => e.Value == "null").Remove ();

foreach (XElement anchor in doc.Descendants (xns + "a").ToArray ())
    anchor.ReplaceWith (anchor.Nodes ());

var presenters = doc.Descendants (xns + "table")
    .Where (el => (string)el.Attribute ("class") == "headingpresenter")
    .Where (e => e.Elements ().Count () == 2)
    .ToArray ();

foreach (var p in presenters)
{
    var heading = p.Elements ().First ().Elements ();
    var content = p.Elements ().Skip (1).First ().Elements ();

    if (stripFormatting)
        p.ReplaceWith (heading, new XElement (xns + "p", content));
    else
        p.ReplaceWith (
            new XElement (xns + "br"),
            new XElement (xns + "span", new XAttribute ("style", "color: green; font-weight:bold; font-size: 110%;"), heading),
            content);
}

// Excel centre-aligns th even if the style says otherwise. So we replace them with td elements.
foreach (var th in doc.Descendants (xns + "th"))
{
    th.Name = xns + "td";
    if (!stripFormatting && th.Attribute ("style") == null)
        th.Add (new XAttribute ("style", "font-weight: bold; background-color: #ddd;"));
}

string finalResult = doc.ToString().Replace ("Ξ", "").Replace ("▪", "");