Generating excel files from .NET code is not always a very easy task, especially if you need to make some formatting or you want to do something very specific that requires extra coding. I’ve recently tried the GemBox Spreadsheet and I would like to share my experience with you.
First of all, you can install GemBox Spreadsheet library from VS.NET 2010 Extension manager by searching in the gallery:
Go in the Online Gallery tab (as in the picture bellow) and write GemBox in the Search box on top-right of the Extension Manager, so you will get the following result:
Click Download on GemBox.Spreadsheet and you will be directed to product website.
Click on the marked link then you will get to the following page where you have the component download link
Once you download it, install the MSI file.
Open the installation folder and find the Bin folder. There you have GemBox.Spreadsheet.dll in three folders each for different .NET Framework version.
Now, lets move to Visual Studio.NET.
1. Create sample ASP.NET Web Application and give it a name.
2. Reference The GemBox.Spreadsheet.dll file in your project
So you don’t need to search for the dll file in your disk but you can simply find it in the .NET tab in ‘Add Reference’ window and you have all three versions. I chose the version for 4.0.30319 runtime.
Next, I will retrieve data from my Pubs database. I’m using Entity Framework.
Here is the code (read the comments in it):
//get data from pubs database, tables: authors, titleauthor, titles
pubsEntities context = new pubsEntities();
var authorTitles = (from a in context.authors
join tl in context.titleauthor on a.au_id equals tl.au_id
join t in context.titles on tl.title_id equals t.title_id
select new AuthorTitles
{
Name = a.au_fname,
Surname = a.au_lname,
Title = t.title,
Price = t.price,
PubDate = t.pubdate
}).ToList();
//using GemBox library now
ExcelFile myExcelFile = new ExcelFile();
ExcelWorksheet excWsheet = myExcelFile.Worksheets.Add("Hajan's worksheet");
excWsheet.Cells[0, 0].Value = "Pubs database Authors and Titles";
excWsheet.Cells[0, 0].Style.Borders.SetBorders(MultipleBorders.Bottom,System.Drawing.Color.Red,LineStyle.Thin);
excWsheet.Cells[0, 1].Style.Borders.SetBorders(MultipleBorders.Bottom, System.Drawing.Color.Red, LineStyle.Thin);
int numberOfColumns = 5; //the number of properties in the authorTitles we have
//for each column
for (int c = 0; c < numberOfColumns; c++)
{
excWsheet.Columns[c].Width = 25 * 256; //set the width to each column
}
//header row cells
excWsheet.Rows[2].Cells[0].Value = "Name";
excWsheet.Rows[2].Cells[1].Value = "Surname";
excWsheet.Rows[2].Cells[2].Value = "Title";
excWsheet.Rows[2].Cells[3].Value = "Price";
excWsheet.Rows[2].Cells[4].Value = "PubDate";
//bind authorTitles in the excel worksheet
int currentRow = 3;
foreach (AuthorTitles at in authorTitles)
{
excWsheet.Rows[currentRow].Cells[0].Value = at.Name;
excWsheet.Rows[currentRow].Cells[1].Value = at.Surname;
excWsheet.Rows[currentRow].Cells[2].Value = at.Title;
excWsheet.Rows[currentRow].Cells[3].Value = at.Price;
excWsheet.Rows[currentRow].Cells[4].Value = at.PubDate;
currentRow++;
}
//stylizing my excel file look
CellStyle style = new CellStyle(myExcelFile);
style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
style.VerticalAlignment = VerticalAlignmentStyle.Center;
style.Font.Color = System.Drawing.Color.DarkRed;
style.WrapText = true;
style.Borders.SetBorders(MultipleBorders.Top
| MultipleBorders.Left | MultipleBorders.Right
| MultipleBorders.Bottom, System.Drawing.Color.Black,
LineStyle.Thin);
//pay attention on this, we set created style on the given (firstRow, firstColumn, lastRow, lastColumn)
//in my example:
//firstRow = 2; firstColumn = 0; lastRow = authorTitles.Count+1; lastColumn = numberOfColumns-1; variable
excWsheet.Cells.GetSubrangeAbsolute(3, 0, authorTitles.Count+2, numberOfColumns-1).Style = style;
//save my excel file
myExcelFile.SaveXls(Server.MapPath(".") + @"/myFile.xls");
The AuthorTitles class:
public class AuthorTitles
{
public string Name { get; set; }
public string Surname { get; set; }
public string Title { get; set; }
public decimal? Price { get; set; }
public DateTime PubDate { get; set; }
}
The excel file will be generated in the root of your ASP.NET Web Application.
The result is:
There is a lot more you can do with this library. A set of good examples you have in the GemBox.Spreadsheet Samples Explorer application which comes together with the installation and you can find it by default in Start –> All Programs –> GemBox Software –> GemBox.Spreadsheet Samples Explorer.
Hope this was useful for you.
Best Regards,
Hajan