CODEWITHSUNDEEP
c#linqexcellinqpad
Alexandre
Alexandre

Reputation: 7212

How to export linq result to excel just like Linqpad

I have an object with a lot of subqueries and I want do export to excel just like Linqpad does, here is an example:

enter image description here

Any help?

Tks

Upvotes: 4

Views: 3787

Answers (4)

dovid
dovid

Reputation: 6452

As noted, LinqPad is not really export to an Excel format. but it create an HTML file, and opens it with Ms-Excel. (but for a download file, you cant force the client how open th file, but you can naming the extension to XLS, and EXCEL open it with a warning).

for generate the HTML output, LinqPad use the hyperlinq library.

void Main()
{
    var filePath = Path.GetTempPath() + "output.html"; //or output.xls but with ms-excel warning
    var iEnumerbleValue = Enumerable.Range(1, 500).Select(e => new { a = 1, b = e });
    File.WriteAllText(filePath,  CreateHtml(iEnumerbleValue).ToString());
    Process.Start("EXCEL.EXE", filePath); 
}



HDoc CreateHtml<T>(IEnumerable<T> coll)
{
    var fields = typeof(T).GetProperties();

    return H.Doc(
            H.head(H.style()),
            H.body(
             H.table(
                H.tbody(
                    H.tr(fields.Select(f => H.th(f.Name))),
                    from item in coll
                    select H.tr(
                        fields.Select(f => H.td(f.GetValue(item)))
                    )
                        )
                    )
                  )
                );
}

Upvotes: 1

loctrice
loctrice

Reputation: 2543

I wanted to add this solution for the next person who comes searching. I realize it's an old question, but it's the only one that popped up in my search.

You can run lprun on the command line.

lprun -format=html yourQuery.linq > output.xls

Excel is able to open html as long as it's xls extension and not xlsx. If you use xlsx it will complain that it's in the wrong format and fail to open. With xls extension it gives a message, but it is able to open.

Just as a note- I needed a database connection string for entity framework. I had it in the linqpad.config and it took me a while to figure out why lprun couldn't read it. You have to add the connection string section to lprun.exe.config so lprun can use it.

Upvotes: 0

sgmoore
sgmoore

Reputation: 16067

If you include a reference to linqpad.exe in your project, you can then use it to do the export to html

eg, 

List<User> users  = ....
var filename = "test.html";

var writer = LINQPad.Util.CreateXhtmlWriter();
writer.Write(users);

System.IO.File.WriteAllText(filename, writer.ToString());

// Open the file in excel
Process.Start("Excel" , filename);

Upvotes: 10

Dave Bish
Dave Bish

Reputation: 19636

Excel can actually open an HTML doc, renamed "xls" - reading-in HTML table structures as Excel cells.

You'd have to output your data as HTML, though.

Upvotes: 3

Related Questions