Insert/Access/Format/Filter/Se...

• Download source - 267.1 KB
• Download demo - 4 KB

Contents

• Introduction
• Creating a New Workbook
• Creating a New Worksheet
• Accessing Cells by Row Index and Column Index
• Accessing Cells by its Address
• Inserting Values in Cells
• Setting Relative Formula Reference
• Adding Excel Function SUBTOTAL()
• Formatting the Style of a Range of Cells
• Setting Number Format for a Range of Cells
• Enabling Filter Feature of MS Excel
• Enabling 'AutoFit' of Cells
• Setting Header and Footer of the Worksheet
• Setting Printer Properties
• Setting Page Layout
• Setting Custom Property
• Final Output
• Declaration
• History

Introduction

This is a extension of the article Create/Read/Edit Advance Excel 2007/2010 Report in C#.NET using EPPlus. No more talk, let's go directly inside this. :)

Quick Start

Creating a New Workbook

System.IO.FileInfo newFile = new FileInfo(@"E:\Sample1.xlsx");
ExcelPackage package = new ExcelPackage(newFile);	

Creating a New Worksheet

Creating an 'Inventry' Worksheet.

// Add a worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventry");	

Accessing Cells by Row Index and Column Index

To print the value of the first cell of the first row, i.e. A1, see the code below. The first index of the Cells array is the row index and the second index is the column index of the cell which we want to access.

Console.WriteLine(worksheet.Cells[1, 1].Value.ToString());

Accessing Cells by its Address

It is very simple you know, if you want to access Excel Cell 'A1', then just put this address like below:

Console.WriteLine(worksheet.Cells["A1"].Value.ToString());

Inserting Values in Cells

Let's first insert values by Row and Column Index:

// Inserting values in the first row...
worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Product";
worksheet.Cells[1, 3].Value = "Quantity";
worksheet.Cells[1, 4].Value = "Price";
worksheet.Cells[1, 5].Value = "Value";	

Now I'm going to insert values by Cell's Address:

// Add some items...
// Inserting values in the first row for: ID, Product, Quantity & Price respectively
worksheet.Cells["A2"].Value = 12001;
worksheet.Cells["B2"].Value = "Nails";
worksheet.Cells["C2"].Value = 37;
worksheet.Cells["D2"].Value = 3.99;	

Setting Relative Formula Reference

If you don't know the feature 'Relative Formula Reference' of Microsoft Excel, please read it first as now we're going to take advantage of this feature. Do you notice that I didn't do anything with the column 'Value' i.e. Cells[1, 5] or Cells["E2"], oh I really forgot. Ok ok... now I'm going to put the result of the product of 'Quantity' and 'Price' in the cells under 'Value' column. Let's see how we do it:

worksheet.Cells["E2:E4"].Formula = "C2*D2";

Done!!! Oh... really it's so simple. :)

Adding Excel Function SUBTOTAL()

If you don't know SUBTOTAL() function of Microsoft Excel, please see it first and here we also need to use the feature 'Relative Formula Reference'. Now, we're going to show the Subtotal in the footer row of the values of the 'Quantity', 'Price' and 'Value' column:

worksheet.Cells[5, 3, 5, 5].Formula = string.Format
("SUBTOTAL(9, {0})", new ExcelAddress(2, 3, 4, 3).Address);

Formatting the Style of a Range of Cells

Now, we're going to see how to set font style, background color, fill type and font color of the first 5 cells of the first row:

// Formatting style of the header
using (var range = worksheet.Cells[1, 1, 1, 5])
{
	// Setting bold font
	range.Style.Font.Bold = true;
	// Setting fill type solid
	range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
	// Setting background color dark blue
	range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
	// Setting font color
	range.Style.Font.Color.SetColor(Color.White);
}

Setting Number Format for a Range of Cells

Now we need to format the values, as the values in the Excel sheet, some are string, some are double, some are int. First, you need to know Microsoft Excel Number Format. So see it first if you don't know.

• Setting integer format for the column 'Quantity'
  C#

  worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0"; 

• Setting decimal format for the columns 'Price' and 'Value'
  C#

  worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";

• Setting text format for the column 'Product'
  C#

  worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";

Enabling Filter Feature of Microsoft Excel

If you really don't know the 'Filtering' feature of Microsoft Excel, please read it first. Let's apply filter for all the cells:

worksheet.Cells["A1:E4"].AutoFilter = true;

Enabling 'AutoFit' of Cells

Let's apply auto fit for all the cells, it's so simple, just look:

worksheet.Cells.AutoFitColumns(0);

Setting Header and Footer of the Worksheet

• Let's add a header text first for this sheet:
  C#

  worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventry";

  Do you notice that the string that I've assigned in 'CenteredText', containing not only the simple text, but also the formatting text, i.e., Font Size, Font Type, etc.

• Let's add the page number to the right of the footer + total number of pages:
  C#

  worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", 
  	ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);

• Let's add the sheet name to the center of the footer:
  C#

  worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;

• Let's add the filepath to the left of the footer:
  C#

  worksheet.HeaderFooter.OddFooter.LeftAlignedText = 
  	ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

Setting Printer Properties

Setting printing properties will help you at the time of printing. Suppose, when page breaks, then the header will also available in the next page automatically by enabling this settings:

worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:1"];
worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:E"];	

Setting Page Layout

By this setting, we can view the sheet in a page layout mode, let's apply:

worksheet.View.PageLayoutView = true;

Setting Custom Property

package.Workbook.Properties.SetCustomPropertyValue
	("Checked by", "Debopam Pal");
package.Workbook.Properties.SetCustomPropertyValue
	("AssemblyName", "EPPlus");	

Final Output

Footer Image:

Declaration

Please download the source code for details. I hope you'll understand as the source code is documented. If there is any doubt, just post your comments below. Thank you.

History

• First release: Create/Read/Edit Advance Excel 2007/2010 Report in C#.NET using EPPlus
• This is just an extension of the first release.
• I will come up with Excel Chart, Encrypting Excel File, Excel Pivot Table and many more in the next release. Thank you.