SpreadSheetGear Made My Life Easy Today For Creating Excel File In ASP.NET

 

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.

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Comments

  1. nice stuff.
    btw, how does “filename=svcc” works? is it possible to have a prefix like “filename”, where ddmm is date+month?
    * pardon my ignorance

  2. Don’t now why the linkroller redirection, use this directly: http://www.carlosag.net/Tools/ExcelXmlWriter/

  3. SpreadsheetGear is a bit overkill for this.

    There is rather easy to generate SpreadsheetML XML formatted which is part in Excel 2002 and later.

    Either look at the schema directly, or use free tools. Search google for “spreadsheetml xml 2002″.

    One hit was this:

    http://linkroller.com/fullpage/ad/13754/?http%3A//www.carlosag.net/Tools/ExcelXmlWriter/

Follow

Get every new post delivered to your Inbox

Join other followers: