Click here to Skip to main content
Click here to Skip to main content

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

, 13 Nov 2013
Rate this:
Please Sign up or sign in to vote.
Export Advanced Excel 2007 Report

Contents

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. Smile | :)

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. Smile | :)

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'
    worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0"; 
  • Setting decimal format for the columns 'Price' and 'Value'
    worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
  • Setting text format for the column 'Product'
    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);
  • Let's add a header text first for this sheet:
    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:
    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:
    worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
  • Let's add the filepath to the left of the footer:
    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

Final Output

Footer Image:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Debopam Pal
Software Developer National Informatics Centre (NIC)
India India
Hello! Myself Debopam Pal. I've completed my MCA degree from West Bengal University of Technology at 2013. I'm from India. I’ve started to work with MS Technologies in 2013 specially in C# 4.0, ASP.NET 4.0. I've also worked in PHP 5. Now I work in JAVA/J2EE, Struts2. Currently I'm involved in a e-Governance Project since Jan, 2014. In my leisure time I write Blog, Articles as I think that every developer should contribute something otherwise resource will be finished one day. Thank you for your time.
 
Visit: Linkedin Profile | Facebook Profile | Google+ Profile | CodeProject Profile
Follow on   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 368286619-Nov-13 6:46 
GeneralRe: My vote of 5 PinmemberDebopam Pal19-Nov-13 7:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 14 Nov 2013
Article Copyright 2013 by Debopam Pal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid