A Little Background
As we announced a couple days, Silicon Valley Code Camp is going to have QR Codes on badges this year. We are currently guessing we will have about 3000 people registered and 1500 attending which means that we need to have the entire badge printing process figured out long before the event (which is October 9th and 10th this year, just 4 weeks away). Kevin Nilson is leading the charge on this (another key organizer at code camp) and asked me for a spread sheet with some names in it so he could practice making the actual QR bar code.
Kevin is a Java guy, and I’m a .Net guy so we need a common format. The most simple thing seemed to be a csv. The plan for me is to build a simple aspx page in asp.net that created an Excel file, then I can simply say “Save As” and create the csv file.
SpreadSheetGeat to the Rescue
I looked around for a really simple public domain program that would let me export an excel or csv file from an aspx page. Nothing jumped out as trivial to use. csv’s can be tricky to create because of punctuation rules, and there did not seem to be an easy .net solution to implement (by easy, I was thinking 10 minutes tops).
Then, in my searching the web, I found this post: http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c. The part that got my attention was the post made by Joe Erickson saying:
SpreadsheetGear for .NET will do it.StackOverflow Post By Joe Erickson
You can see live ASP.NET (C# and VB) samples here and download an evaluation version here.
Disclaimer: I own SpreadsheetGear LLC
Then it occured to me I owned a copy of SpreadSheetGear from a much more complex project I worked on a while back. SpreadSheetGear is amazing in how it creates complex excel spread sheets with an amazingly little amount of code. That combined with it works every time is really nice. So, I grabbed the dll, put it in the bin directory of my Silicon Valley Code Camp web project, went into there sample code, copied a few lines (see below), and I was done! Totally amazing. Thanks SpreadSheetGear!
BTW, here is the code I needed to write to have SpreadSheetGear output my simple excel spreadsheet (all this code is simply in a button clicked event on an aspx page. Rec rec is simply defined as List<Attendees>. )
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
SpreadsheetGear.IRange cells = worksheet.Cells;
// Set the worksheet name.
worksheet.Name = "SiliconValleyCodeCamp5";
cells[0, 0].Value = "Id";
cells[0, 1].Value = "FirstName";
cells[0, 2].Value = "LastName";
cells[0, 3].Value = "Website";
cells[0, 4].Value = "AddressLine1";
cells[0, 5].Value = "City";
cells[0, 6].Value = "State";
cells[0, 7].Value = "Zipcode";
cells[0, 8].Value = "Email";
cells[0, 9].Value = "PhoneNumber";
int row = 1;
foreach (var rec in attendeesResults)
{
cells[row, 0].Value = rec.Id;
cells[row, 1].Value = rec.UserFirstName;
cells[row, 2].Value = rec.UserLastName;
cells[row, 3].Value = rec.UserWebsite;
cells[row, 4].Value = rec.AddressLine1;
cells[row, 5].Value = rec.City;
cells[row, 6].Value = rec.State;
cells[row, 7].Value = rec.UserZipCode;
cells[row, 8].Value = rec.Email;
cells[row, 9].Value = rec.PhoneNumber;
row++;
}
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=svcc.xls");
workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
Response.End();
Conclusions
I really like SpreadSheetGear! It took longer for me to write this post than it did to get my code working.