Archive for the ‘Q News & Events’ Category

Monday’s historic Presidential Inauguration took weeks of careful planning and attention to detail as Obama was sworn in for his second term.   One million+ supporters came out to support the President and capture each moment for their friends and family.  Check out the agencyQ “Obameter” below for a look at some of the most interesting event planning, facts and photos from the attendees!

Founded in 1999, agencyQ delivers award winning social, mobile and interactive solutions that transform the way organizations reach, engage and inspire their key audiences. With a unique ability to align our creative, technology and marketing acumen with clients’ strategic goals, agencyQ has generated measurable results for a global base of corporations, governments and associations over the past three decades. To learn more about agencyQ and its work, visit www.agencyQ.com or follow @agencyQ on Twitter.

On January 2, Q welcomed Andrew Dawson, the newest member of our digital team. With a background in music and history he brings an eclectic range of skills in content creation and digital strategy. Prior to joining agencyQ, he worked as a account associate for MSL Group in New York City, focusing on social media strategy and content creation for brands that ranged from Swiffer to Nutella. He is a graduate of American University.

The problem with exporting an Excel spreadsheet from .NET is you really have two options (natively): Interop calls and rendering it from an HTML/table export. Interop calls are usually the best way but they communicate with an Excel process to build the worksheet, workbooks, etc. Building from the ASP.NET worker process is where we have the issue of hanging threads from Interop calls. In an ideal world, there would be no loose calls and everything would be perfectly cleaned up. My experience has shown me though that this isn’t the case and that even if you recycle the worker process constantly, there will still be EXCEL processes in the background that just sit around and take up resources. These hanging resources will usually sleep in the background, you would have to find a way to either clean them up manually or write some sort of process killer on a timer.

So what do we do? Microsoft has provided a way for Excel to render a Workbook through a MIME type outlined here (http://msdn.microsoft.com/en-us/library/office/aa140062(v=office.10).aspx). In combination with the GridView control, which emits data as a table. Because of this, we can pair these two concepts and render a table. There is another resource that will aid in this, an old Microsoft help file that defines the styles for rendering these tables (http://msdn.microsoft.com/en-us/library/cc313118.aspx).

2) Starting our project

To start our project, we create a web application called “GridViewExcelExport”. This should be ASP.NET 4.0.

For this demo, we’re going to use Entity Framework (4.0) table as a datasource but anything will do. The first step is to add a SQL-Server Database, I called mine “Database.mdf”. Next we add a table with the attributes outlined below (Id should be a primary key):

Additional fields should be “Name” and “Color”, both VARCHAR(50)s and Length should be DECIMAL(18,6). All fields shouldn’t allow nulls. Let’s also insert some dummy data…

With this table complete, we can now tie EF to it. We’ll be using Entity Framework to do our basic operation of querying this database and populating our DataGrid. This is optional… any datasource can populate the GridView.

      

Next up, we’re gonna use the default.aspx page to host our GridView control. We’re also gonna include the column information we have from our datasource (Cats table).

<asp:GridView runat="server" ID="gv" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="Id" DataField="Id" />
<asp:BoundField HeaderText="Name" DataField="Name" />
<asp:BoundField HeaderText="Color" DataField="Color" />
<asp:BoundField HeaderText="Length" DataField="Length" />
</Columns>
</asp:GridView>

Let’s also insert a button control we’ll use to render the event…

<asp:Button runat="server" ID="btnExport"
Text="Export"  />

At this point we just have a grid and a button. Let’s bind the grid to a datasource. If we go to the code-behind, we’ll create a small helper and attach it to the page load event.

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}

protected void BindGrid()
{
using (var context = new DatabaseEntities())
{
gv.DataSource = context.Cats;
gv.DataBind();
}
}

The grid should now bind on start-up. If we start up our page, it should look like this.

Now we want to start rendering this grid out to the client as an Excel spreadsheet. Let’s attach an event to the Export button and wire it up in the code-behind.

<asp:Button runat="server" ID="btnExport"
Text="Export"
OnClick="btnExport_Click" />

protected void btnExport_Click(object sender, EventArgs e)
{

}

Now to export we need to setup the Response stream to allow for exporting. Basically instead of sending the entire page down on a Postback, we clear the response stream and render our associated controls out.

this.Response.Clear();
this.Response.Charset = "";
this.Response.ContentType = "application/x-excel";
this.Response.AddHeader("content-disposition", "attachment; filename='export.xls'");

We change the content-type, so that the browser knows how to interpret it and make it so it acts as an attachment. This will cause a pop-up window on most browsers to display whether they want to open this attachment or not. Assuming you have Microsoft Excel installed, this will allow it. Next up we need to actually render some of the contents out in the Response stream…

StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);

BindGrid();

We create writers. These are going to be used to hold the contents of our rendered GridView to output to the Response stream. We also bind the grid.

gv.RenderControl(hw);
this.Response.Write(sw.ToString());
this.Response.End();

The last part of a export is needing to render the control out to our HtmlTextWriter and to close the Response stream. We do that with Response.End. If we start this project up though, it’s gonna throw an error specifying that our GridView is not in a form with a runat=”server” tag. This is because we’re just rendering it. We need to turn off rendering so we override this method on the page.

public override void VerifyRenderingInServerForm(Control control)
{
if (control.ID != "gv")
{
base.VerifyRenderingInServerForm(control);
}
}

At this point we can run the export. Excel will prompt if you want to open from this source, just hit “Yes” and something like this should appear in the window:

So after this we have a basic Excel export… now what? Say we want to add styles to our Grid… and name the Worksheet? How do we do that? In the MIME type definition, it specifies we can insert a header just like this:

<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sample Workbook</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->

Along with a stylesheet where we can include some styles to attach to our columns:

 .xlColName
 {
 color:Red;
 }

.xlColLength
 {
 mso-width-source:userset;
 mso-width-alt:1205;
 mso-number-format:\@;
 }

So the final result which will go into our mark-up, is this:

<div runat="server" id="divHdr">
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sample Workbook</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->

<style type="text/css">
.xlColName
{
color:Red;
}

.xlColLength
{
mso-width-source:userset;
mso-width-alt:1205;
mso-number-format:\@;
}
</style>
</div>

The styles above style the column “Length” with a different width for the column than just the regular width. It will also render the numbers as strings, so that there is no formatting. The name column is just going to be red. And our Workbook will be called “Cats Workbook”. Then we just need to add one line so that we can render it with our export. The final code block for rendering in the Button export should look like this.

divHdr.RenderControl(hw);
gv.RenderControl(hw);
this.Response.Write(sw.ToString());
this.Response.End();

The final mark-up for GridView should look like:

<asp:GridView runat="server" ID="gv" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="Id" DataField="Id" />
<asp:BoundField HeaderText="Name" DataField="Name" ItemStyle-CssClass="xlColName" />
<asp:BoundField HeaderText="Color" DataField="Color" />
<asp:BoundField HeaderText="Length" DataField="Length" ItemStyle-CssClass="xlColLength" />
</Columns>
</asp:GridView>

Our final rendered Excel spreadsheet should look like (notice the shortened column):

It’s not pretty but the flexibility of Excel spreadsheets without having to perform any Interop calls is pretty nice in ASP.NET. There’s also a high value in the customization that can be performed by just changing some of the response headers. Some of the things that weren’t explored in this were the addition of additional Spreadsheets (through modifying the Header xml definition) and changing Print sizes. The possibilities are sort of endless if you have the time to dig through the Excel format above.

We welcome our new Chief Operating Officer, Stephen Marino, to the Q team! Steve brings over 19 years of experience in developing digital business and communication strategies, as well as social Web development solutions. In his role, Steve develops and drives both social digital strategy and manages and develops digital talent within the agency. He has worked on such accounts as BP where he headed up and executed the social media response to the Gulf of Mexico crisis in 2010, P&G, Siemens, Ford, General Motors, Unilever, Johnson & Johnson, Kellogg’s, Lenovo, and many more. On all of these accounts the focus has been on increasing the value of a company’s footprint in the digital space, whether it is a site redesign or focusing on deploying social media strategies and implementation focused on the social Web.

Prior to joining agencyQ, most recently, Steve was SVP, Americas Director of Social & Digital Media with responsibility for the day-to-day operations for the Americas network for MSLGROUP. Prior to that, Steve was Senior Vice President, 360° Digital Influence of Ogilvy Public Relations Worldwide, with responsibility for the day-to-day operations for the 360° Digital Influence group in North America.

Prior to that, Steve spent 10 years at one of the original and most influential interactive agencies here in the U.S., Magnet Interactive. Based in Georgetown, Magnet was a pioneering force in bringing some of the most well-known corporate names online for the very first time. Kellogg Company, Mercedes-Benz, N.A., Nissan, and Infiniti, N.A., M&M Mars Snickers brand, The Mayo Clinic and Merck, and Texas Instruments were just some of his clients. Stephen was part of the executive team that sold and merged Magnet Interactive into AKQA, a current creative force in the interactive and traditional advertising and marketing arena with offices in London, New York, D.C., San Francisco, and Singapore. He was a managing director at AKQA responsible for the D.C. office, and Global Alliance Director, managing and acting as the primary contact for AKQA’s strategic partner Accenture. Accenture and AKQA partnered together on many complex business intranets and extranets for clients such as Boston Scientific, SprintPCS, and AT&T.

Stephen holds a bachelor’s degree in mass communications from Boston University and a master’s degree from New York University’s Tisch School of the Arts Interactive Telecommunications Program.

On August 13, Q welcomed Michael Trexler, the newest member of our development team. Mike is a native of Kensington, Maryland, and a University of Maryland graduate. He brings knowledge of cross-platform web development, and is especially fond of front-end coding. In his spare time Mike likes to play online games, experiment with new kinds of cuisine, and enjoy the world outdoors.

On August 20^th, Thomas Taylor joined the Q development team. Thomas grew up in the DC metro area and his affection for programming started at an early age, manifesting itself building websites and demo video games. After deciding to make it a career, he attended George Mason University. After graduating he joined a federal contractor, CACI, building websites for the Army on contract. In his spare time, he enjoys triathlon training, volunteering with an organization in DC that serves underprivileged youth in southeast and cooking and baking up anything and everything, from empanadas to raspberry cheesecake to anything spicy with Thai basil in it.