Blog

Scenerio: I’ve recently had a a few projects that I’ve created forms for.  I have given the client a login to view all the form submissions, but in the end they want this data in an Excel file so they can easily manage it.

Research: I have found there a quite a few ways to achieve this.  Some take advantage of the Excel interop, some create a .csv, some use third party tools, some use xml. Here is a good discussion I found on the topic at Stack Overflow, http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c.
All have pros and cons.

Solution: Here is what I have found to be the simplest way to read your dataset in an Excel file. First off, this doesn’t product a true Excel file, but it is something Excel can read into a table which often is all you need.

What I found is you can write the contents of a datagrid to an output stream that can be saved as a .xls file.

code snippet: I am using linq to bind to a datagrid

var registrations = from reg in db.Registrations
                    where reg.JumpPageId == int.Parse((String)e.CommandArgument)
                    orderby reg.TimeStamp descending
                    select reg;
HttpResponse response = HttpContext.Current.Response;

// first let's clean up the response.object
response.Clear();
response.Charset = "";

// set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment; filename=registrants.xls");

// create a string writer
using (StringWriter sw = new StringWriter())
{
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
        // instantiate a datagrid
        DataGrid dg = new DataGrid();
        dg.DataSource = registrations;
        dg.DataBind();
        dg.RenderControl(htw);
        response.Write(sw.ToString());
        response.End();
    }
}
 

LIke I said, this doesn’t output a true Excel file so when you go to open it, you will be prompted a warning:

It is saying the contents of the file are in a different format than what the .xls extension says it is. 

Click Yes and it will open.

If you were to open this file in a text editor you will see that it really is just the datagrid html output.

Not the most elegant solution but it is a quick way to throw an Export to Excel button on a report page.

My next blog post will show how to create an Excel file using a third party tool.

Filed under: asp.net   Tags: .NET, Excel, linq,
 View Comments

Using Linq to Sql Auto IDs do not work by default. 

Lets say you have a table “Property” with a primary key of “PropertyId”.  In the table definition it is set as a unqueidentifier and by default it is assigned a NewID().

Now go to your Linq to Sql and create your dbml, drag your Property table into it.

In order to have the PropertyId generated using the Default Value go  to the properties of the PropertyId field and set Auto Generate Value to True and Auto-Sync to OnInsert.

I am sure many already know this, but I hope it can help save some time for others. 

Filed under: Web Development   Tags: .NET, linq, MSSQL,
 View Comments