Short introduction to my challenge
I was building a small CRM tool for a client. The purpose of the tool was to filter owners of vehicles, then put together a list that the sales team can start pitching to. One of the ways that the client wanted to distribute the tasks to its sales team was via Excel, hence I needed to build an Excel export function.
Requirements for a potential Excel library:
- Should work on a server that doesn’t have Excel installed
- XLSX-format is the preferred output i.e. use the Open Office Xml format
- Simple styling of the excel file is requested
- Complete integration with .Net with a fresh API
Example of how to output a simple spreadsheet
First of all you should create a file based on the FileInfo object:
// Set the file name and get the output directory
var fileName = "Example-CRM-" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
var outputDir = context.Server.MapPath("/media/generatedfiles/");
// Create the file using the FileInfo object
var file = new FileInfo(outputDir +fileName);
It could also be a good idea to add some handling for the fact that the file could already exist, but this I leave to you.
Next step is to actually create the Excel object. In EPPlus this is called an ExcelPackage.To the excel object we add one workbook, you can of course add as many as you’d like (is probably some restriction in Excel though..).
// Create the package and make sure you wrap it in a using statement
using (var package = new ExcelPackage(file))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sales list - " + DateTime.Now.ToShortDateString());
// --------- Data and styling goes here -------------- //
}
Code that follow below should be inserted within the using-statement above.
Add styling to the workbook is quite simple:
// Add some formatting to the worksheet
worksheet.TabColor = Color.Blue;
worksheet.defaultRowHeight = 12;
worksheet.HeaderFooter.firstFooter.LeftAlignedText = string.Format("Generated: {0}", DateTime.Now.ToShortDateString());
worksheet.Row(1).Height = 20;
worksheet.Row(2).Height = 18;
I added two levels of headers, because that was suitable in our case. It is also quite a good demo to show how this works when working with the EPPlus library. So now I’ll populate the header:
// Start adding the header // First of all the first row worksheet.Cells[1, 1].Value = "Company name"; worksheet.Cells[1, 2].Value = "Address"; worksheet.Cells[1, 3].Value = "Status (unstyled)"; // Add the second row of header data worksheet.Cells[2, 1].Value = "Vehicle registration plate"; worksheet.Cells[2, 2].Value = "Vehicle brand";
Next step was to style the header. In this exampel I only show how I styled the top row, but you can obviously follow the same pattern for the second row as well. In EPPlus they have a really neat way of handling ranges. In this case I wanted to style one row and only a couple of the columns. This is done by using the method in ExcelWorksheet.cells[from row, from column, to row, to column].
//Ok now format the first row of the heade, but only the first two columns;
using (var range = worksheet.Cells[1, 1, 1, 2])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.Black);
range.Style.Font.Color.SetColor(Color.WhiteSmoke);
range.Style.ShrinkToFit = false;
}
Finally lets start add some data to our Excel sheet. In our case I had a collection of companies that had vehicles attached to it. So as you might have guessed I wanted to add one row containing the company, that I style particularly and then add vehicles belonging to that company below it.
// Keep track of the row that we're on, but start with four to skip the header
int rowNumber = 4;
// Loop through all the companies and add their vehicles
foreach (var company in companies)
{
worksheet.Cells[rowNumber, 1].Value = company.Name;
worksheet.Cells[rowNumber, 2].Value = company.Address;
worksheet.Cells[rowNumber, 7].Value = company.Status;
//Ok now format the company row
using (var range = worksheet.Cells[rowNumber, 1, rowNumber, 7])
{
range.Style.Font.Bold = false;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightSteelBlue);
range.Style.Font.Color.SetColor(Color.Black);
range.Style.ShrinkToFit = false;
}
// Add one row and start add the vehicles
rowNumber++;
foreach (var vehicle in rankedCompanyVehicleFleet.Vehicles)
{
worksheet.Cells[rowNumber, 2].Value = vehicle.RegistrationNumber;
worksheet.Cells[rowNumber, 3].Value = vehicle.Brand;
}
}
Finally we add some trimming and final data before saving the Excel document and return the file.
// Fit the columns according to its content worksheet.Column(1).AutoFit(); worksheet.Column(2).AutoFit(); worksheet.Column(3).AutoFit(); // Set some document properties package.Workbook.Properties.Title = "Sales list"; package.Workbook.Properties.Author = "Gustaf Lindqvist @ Ted & Gustaf"; package.Workbook.Properties.Company = "Ted & Gustaf"; // save our new workbook and we are done! package.Save(); //-------- Now leaving the using statement } // Outside the using statement return file;
This is the final result:
I hope that this short example gave you some insight into EPPlus. If you have other Excel libraries that I should try out, don’t hesitate to leave a comment.
Why check out the EPPlus Library?
I must say that this is one of the easiest Excel libraries that I have used. It was extremely easy to use from a development perspective and fast to get going with. You finde more examples on codeplex.com.
Resources:
- This is where you can find all the EPPlus library: http://epplus.codeplex.com/.
- Another site with a few examples on how to use EPPlus: http://zeeshanumardotnet.blogspot.se/2011/06/creating-reports-in-excel-2007-using.html