Our Blog

Export to Excel Using C#

Stefan Stefanov
by Stefan Stefanov on Wed 27 February 2013 1 comment

 

Many business applications need functionality for exporting data to Excel. There are various options to implement it, i.e. – using Excel interop object, OleDb, XML Spreadsheet, render ASP.NET GridView control to Excel, etc. A developer will decide which technique to choose based on the specific scenario, type of the application or user requirements.

This article which will be separated in several parts will look at some of the ways for working with MS Excel using C#. Let’s start with EPPlus – open source library created by CodePlex. Since Microsoft Excel 2007 all versions support Office Open XML standard. This is a zipped XML-based file format developed by Microsoft (more information http://en.wikipedia.org/wiki/XLSX). By using the EPPlus library (a .Net library for creating, reading or editing advanced Excel spreadsheets) we can deal with these Excel formats. We not only export data but we also can present well formatted data. The library supports styles, ranges definition, worksheets view adjustment, printer settings, sets various protection modes, writes comments, presents graphical charts, Pivot tables, data validation, marge cells down or across and many others. For every style we can set font-size, font-style, horizontal and vertical align, bold, italic, underline, number-format, fore-color, background-color, etc.

I will make a demo how to create Excel (.xlsx, .xlsm) files using some capabilities of the EPPlus. To start you have to download EPPlus.dll from CodePlex site (http://epplus.codeplex.com). Currently I am using version 3.1 I also use is AdventureWorks2012 database for retrieving some data which will be exported to Excel.

Entry point to access methods and properties of EPPlus library is creating new ExcePackage object.


ExcelPackage package = new ExcelPackage();

The ExcePackage implements IDisposable interface and after we finish using it, we have to explicitly call Dispose() method to release system resources. In my demo I create new file and at the end I call SaveAs() method. According the documentation the package is closed after it has been saved and we don’t need to use Dispose(). But if we are reading Excel by ExcelPackage without saving nothing, then initialization of new ExcePackage should be put into using() block or try-finally { package.Dispose()} statement.

Now I can start designing styles of the first worksheet.  My target is to achieve formatting like in Figure 1.

Figure 1

I will go through the following steps:

  1. Create the first worksheet called “Orders”.
  2. Define range for columns header – so now I can format all column headers together.
  3. Freeze header row – I want when scroll down header to stays on top.
  4. Set names and width of the columns.
using (var rng = ws1.Cells["A1:F1"])

{

  rng.Style.Font.Bold = true;
  rng.Style.WrapText = true;
  rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
  rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
  rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
  rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(237, 237, 237));
  rng.Style.Font.Size = 12;

}

ws1.View.FreezePanes(2, 1);

ws1.Cells["A1"].Value = "Product";
ws1.Cells["A1"].AutoFitColumns(25);

ws1.Cells["B1"].Value = "Vendor";
ws1.Cells["B1"].AutoFitColumns(35);

ws1.Cells["C1"].Value = "Order Date";
ws1.Cells["C1"].AutoFitColumns(12);

ws1.Cells["D1"].Value = "Quantity";
ws1.Cells["D1"].AutoFitColumns(10);

ws1.Cells["E1"].Value = "Unit Price";
ws1.Cells["E1"].AutoFitColumns(12);

ws1.Cells["F1"].Value = "Total Price";
ws1.Cells["F1"].AutoFitColumns(15);

The header of “Orders” worksheet is ready. Next step is retrieve data and export it. I select some order’s details from tables as shown in Figure 2.

Figure 2

For selecting projection add class Order:

public class Order

{
  public string Vendor { get; set; }
  public string Product { get; set; }
  public DateTime OrderDate { get; set; }
  public int Quantity { get; set; }
  public decimal UnitPrice { get; set; }
}

Retrieve random 200 orders:

var query = from purchaseOrderHeader in dataContext.PurchaseOrderHeaders

join vendor in dataContext.Vendors on purchaseOrderHeader.VendorID equals vendor.BusinessEntityID

join purchaseOrderDetails in dataContext.PurchaseOrderDetails on purchaseOrderHeader.PurchaseOrderID equals purchaseOrderDetails.PurchaseOrderID

join product in dataContext.Products on purchaseOrderDetails.ProductID equals product.ProductID

select new Order

{
   Vendor = vendor.Name,
   Product = product.Name,
   Quantity = purchaseOrderDetails.OrderQty,
   UnitPrice = purchaseOrderDetails.UnitPrice,
   OrderDate = purchaseOrderHeader.OrderDate
};
return query.OrderBy(random => Guid.NewGuid()).Take(200).ToList();

Now start populating Excel rows with orders:

int rowIndexBeginOrders = 2;
int rowIndexCurrentRecord = rowIndexBeginOrders;
foreach (var order in orders)
{
   ws1.Cells["A" + rowIndexCurrentRecord].Value = order.Product;
   ws1.Cells["B" + rowIndexCurrentRecord].Value = order.Vendor;
   ws1.Cells["C" + rowIndexCurrentRecord].Value = order.OrderDate;
   ws1.Cells["C" + rowIndexCurrentRecord].Style.Numberformat.Format = "dd.MM.yyyy";
   ws1.Cells["D" + rowIndexCurrentRecord].Value = order.Quantity;
   ws1.Cells["E" + rowIndexCurrentRecord].Value = order.UnitPrice;
   ws1.Cells["F" + rowIndexCurrentRecord].FormulaR1C1 = "RC[-2]*RC[-1]";
   rowIndexCurrentRecord++;
}

I want to focus on using formulas. This is helpful feature provided by CodePlex. Many clients prefer in their report instead of pre-calculated totals to see pattern and chronology of calculation.

As you see in the code, “Total Price” column is calculated dynamically by formula. What does the formula mean?

“RC[-2]*RC[-1]” – get value from cell with address: the same row index and column with index equal to current column index minus 2 (shift two position to left). Multiplying this with value from address current row and previous column.

Let’s calculate and “Grand Total”:


int rowIndexEndOrders = rowIndexCurrentRecord - 1;
var cellGrandTotalFormula = ws1.Cells["F" + rowIndexCurrentRecord];
cellGrandTotalFormula.Style.Font.Bold = true;
string sumFormula = "SUM(R[" + (rowIndexBeginOrders - rowIndexCurrentRecord) + "]C:R[-1]C)";
cellGrandTotalFormula.FormulaR1C1 = sumFormula;

For this purpose use SUM formula. I know Excel row indexes from where start and end order records and builds the grand total formula.

Second worksheet I called “Total Prices per Vendor” Figure 3.

Figure 3

The column “A” contains names of all vendors. In the second column I want to sum the total prices of all ordered products per vendor which are exported in the first worksheet. Here in order to use again Excel features, I have to build more complex formula.

SUMIF(range, criteria, sum_range)

The idea is to load the local Excel engine on the user’s machine to look matches of the assigned criteria and make calculations instead of putting that load on our application.

Here’s how it looks:


int rowIndexBeginVendors = 2;
int rowIndexCurrentVendor = rowIndexBeginVendors;
foreach (var vendor in vendors)
{
   string formulaSumIf = string.Format("SUMIF({0}!R[{1}]C:R[{2}]C,RC[-1],{3}!R[{4}]C[4]:R[{5}]C[4])",
   ws1.Name, //Worksheet1 name
   rowIndexBeginOrders - rowIndexCurrentVendor,
   rowIndexEndOrders - rowIndexCurrentVendor,
   ws1.Name,
   rowIndexBeginOrders - rowIndexCurrentVendor,
   rowIndexEndOrders - rowIndexCurrentVendor
   );
   ws2.Cells["B" + rowIndexCurrentVendor].FormulaR1C1 = formulaSumIf;
   rowIndexCurrentVendor++;
}

What does the formula do? – gets vendor name from current row, previous column and look up into column “B” from sheet 1. If there are found results – sums their values from column “F” – Total Prices.

And here is the full code:

using CSharpAndExcel.DAL;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Drawing;
using System.IO;

namespace CSharpAndExcel.ExportToExcel
{
 public class EPPlusExportToExcel
 {
 private readonly DirectoryInfo outputDir;
 public EPPlusExportToExcel(string outputDir)
 {
 this.outputDir = new DirectoryInfo(outputDir);
 }
public string ExportToExcel()
 {
string fileName = "EPPlusExportToExcel.xlsx";
FileInfo newFile = new FileInfo(Path.Combine(outputDir.FullName, fileName));
 if (newFile.Exists)
 {
 newFile.Delete();
 newFile = new FileInfo(Path.Combine(outputDir.FullName, fileName));
 }
 ExcelPackage package = new ExcelPackage();
 using (var dbAccess = new AdventureWorksDBAccess())
 {
 #region Worksheet1
 var ws1 = package.Workbook.Worksheets.Add("Orders");

//Format the header
 using (var rng = ws1.Cells["A1:F1"])
 {
 rng.Style.Font.Bold = true;
 rng.Style.WrapText = true;
 rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
 rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
 rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
 rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(237, 237, 237));
 rng.Style.Font.Size = 12;
 }
 ws1.View.FreezePanes(2, 1);

 ws1.Cells["A1"].Value = "Product";
 ws1.Cells["A1"].AutoFitColumns(25);

 ws1.Cells["B1"].Value = "Vendor";
 ws1.Cells["B1"].AutoFitColumns(35);

 ws1.Cells["C1"].Value = "Order Date";
 ws1.Cells["C1"].AutoFitColumns(12);

 ws1.Cells["D1"].Value = "Quantity";
 ws1.Cells["D1"].AutoFitColumns(10);

 ws1.Cells["E1"].Value = "Unit Price";
 ws1.Cells["E1"].AutoFitColumns(12);

 ws1.Cells["F1"].Value = "Total Price";
 ws1.Cells["F1"].AutoFitColumns(15);

 var orders = dbAccess.GetOrders();
 int rowIndexBeginOrders = 2;
 int rowIndexCurrentRecord = rowIndexBeginOrders;

foreach (var order in orders)
 {
 ws1.Cells["A" + rowIndexCurrentRecord].Value = order.Product;
 ws1.Cells["B" + rowIndexCurrentRecord].Value = order.Vendor;
 ws1.Cells["C" + rowIndexCurrentRecord].Value = order.OrderDate;
 ws1.Cells["C" + rowIndexCurrentRecord].Style.Numberformat.Format = "dd.MM.yyyy";
 ws1.Cells["D" + rowIndexCurrentRecord].Value = order.Quantity;
 ws1.Cells["E" + rowIndexCurrentRecord].Value = order.UnitPrice;
 ws1.Cells["F" + rowIndexCurrentRecord].FormulaR1C1 = "RC[-2]*RC[-1]";
 rowIndexCurrentRecord++;
 }
 int rowIndexEndOrders = rowIndexCurrentRecord - 1;
 var cellGrandTotalLabel = ws1.Cells["E" + rowIndexCurrentRecord];
 cellGrandTotalLabel.Style.Font.Bold = true;
 cellGrandTotalLabel.Value = "Grand Total:";

 var cellGrandTotalFormula = ws1.Cells["F" + rowIndexCurrentRecord];
 cellGrandTotalFormula.Style.Font.Bold = true;
 string sumFormula = "SUM(R[" + (rowIndexBeginOrders - rowIndexCurrentRecord) + "]C:R[-1]C)";
 cellGrandTotalFormula.FormulaR1C1 = sumFormula;

#endregion

#region Worksheet2
 var ws2 = package.Workbook.Worksheets.Add("Total Prices per Vendor");
 ws2.View.FreezePanes(2, 1);

 var cellWS2TotalPrice = ws2.Cells[1, 1];
 cellWS2TotalPrice.Value = "Vendors";
 cellWS2TotalPrice.AutoFitColumns(35);
 cellWS2TotalPrice.Style.Font.Bold = true;
 cellWS2TotalPrice.Style.Font.Size = 12;
 cellWS2TotalPrice.Style.Fill.PatternType = ExcelFillStyle.Solid;
 cellWS2TotalPrice.Style.Fill.BackgroundColor.SetColor(Color.Silver);

 ws2.Cells["B1"].AutoFitColumns(14);
 ws2.Cells["B1"].AddComment("This column uses sum formula with condition.", "S.S.").AutoFit = true;
 var vendors = dbAccess.GetVendors();

 int rowIndexBeginVendors = 2;
 int rowIndexCurrentVendor = rowIndexBeginVendors;
 foreach (var vendor in vendors)
 {
 ws2.Cells["A" + rowIndexCurrentVendor].Value = vendor.Name;
 ExcelStyle styleTotalsLabel = ws2.Cells["A" + rowIndexCurrentVendor].Style;
 styleTotalsLabel.Border.Bottom.Style = ExcelBorderStyle.Double;
 styleTotalsLabel.Border.Bottom.Color.SetColor(Color.FromArgb(91, 155, 213));
 styleTotalsLabel.Border.Top.Style = ExcelBorderStyle.Thin;
 styleTotalsLabel.Border.Top.Color.SetColor(Color.FromArgb(91, 155, 213));
 styleTotalsLabel.Fill.PatternType = ExcelFillStyle.Solid;
 styleTotalsLabel.Fill.BackgroundColor.SetColor(Color.FromArgb(237, 237, 237));
 string formulaSumIf = string.Format("SUMIF({0}!R[{1}]C:R[{2}]C,RC[-1],{3}!R[{4}]C[4]:R[{5}]C[4])",
 ws1.Name,
 rowIndexBeginOrders - rowIndexCurrentVendor,
 rowIndexEndOrders - rowIndexCurrentVendor,
 ws1.Name,
 rowIndexBeginOrders - rowIndexCurrentVendor,
 rowIndexEndOrders - rowIndexCurrentVendor
 );
 ws2.Cells["B" + rowIndexCurrentVendor].FormulaR1C1 = formulaSumIf;
 ExcelStyle styleTotals = ws2.Cells["B" + rowIndexCurrentVendor].Style;
 styleTotals.Border.Bottom.Style = ExcelBorderStyle.Double;
 styleTotals.Border.Bottom.Color.SetColor(Color.FromArgb(91, 155, 213));
 styleTotals.Border.Top.Style = ExcelBorderStyle.Thin;
 styleTotals.Border.Top.Color.SetColor(Color.FromArgb(91, 155, 213));
 styleTotals.Font.Bold = true;
 rowIndexCurrentVendor++;
 }
#endregion
 }
 package.Workbook.Properties.Title = "Excel workbook using EPPlus";
 package.Workbook.Properties.Author = string.Join(Environment.NewLine, "hrisimow@gmail.com", "Stefan Hrisimov Stefanov");
 package.Workbook.Properties.Company = "Up2 Technology LTD";
 package.SaveAs(newFile);
 return newFile.FullName;
 }
public void EPPlusReadExcel()
 {
 throw new NotImplementedException();
 }
 }
}

And LINQ queries to database:

namespace CSharpAndExcel.DAL
{
 public class AdventureWorksDBAccess : IDisposable
 {
 private AdventureWorks2012Entities dataContext;
 public AdventureWorksDBAccess()
 {
 dataContext = new AdventureWorks2012Entities();
 }
 public List GetOrders()
 {
 var query = from purchaseOrderHeader in dataContext.PurchaseOrderHeaders
 join vendor in dataContext.Vendors on purchaseOrderHeader.VendorID equals vendor.BusinessEntityID
 join purchaseOrderDetails in dataContext.PurchaseOrderDetails on purchaseOrderHeader.PurchaseOrderID equals purchaseOrderDetails.PurchaseOrderID
 join product in dataContext.Products on purchaseOrderDetails.ProductID equals product.ProductID
 select new Order
 {
 Vendor = vendor.Name,
 Product = product.Name,
 Quantity = purchaseOrderDetails.OrderQty,
 UnitPrice = purchaseOrderDetails.UnitPrice,
 OrderDate = purchaseOrderHeader.OrderDate
 };
 return query.OrderBy(random => Guid.NewGuid()).Take(200).ToList();
}
public List GetVendors()
 {
 return dataContext.Vendors.ToList();
 }
#region IDisposable
 public void Dispose()
 {
 try
 {
 if (dataContext != null)
 dataContext.Dispose();
 }
 catch
 {
 throw;
 }
 }
 #endregion
 }
}

Consumes ExportToExcel() method:


namespace CSharpAndExcel
{
   class Program
   {
       static void Main(string[] args)
       {
          string outputDir = AppDomain.CurrentDomain.BaseDirectory;
          Console.WriteLine("Start export orders.");
          EPPlusExportToExcel EPPlusExcel = new EPPlusExportToExcel(outputDir);
          string ExcelPath = EPPlusExcel.ExportToExcel();
          Console.WriteLine("New Excel file created {0}.", ExcelPath);
          Console.WriteLine();
          Console.WriteLine("Press the return key to exit...");
          Console.Read();
       }
    }
}

Advantages of using EPPlus library:

  • EPPlus does not require any Office software installed on the machine where you are using it. All it does is reading and writing xml files in the same format as Excel 2007 and higher. (from http://epplus.codeplex.com/discussions/).
  • Creating rich content Excel files
  • The library can be used in different type .NET application – ASP.NET, WinForms, WPF, WCF, etc.
  • Open source library

Disadvantages:

  • If the customers have Excel 2003 or older version they cannot open generated files with EPPlus library
  • Including third party assembly in the application

Demo source code.

Stefan StefanovExport to Excel Using C#

1 comment

Join the conversation

Join the conversation