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. :)
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.
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:
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:
worksheet.Cells["A2"].Value = 12001;
worksheet.Cells["B2"].Value = "Nails";
worksheet.Cells["C2"].Value = 37;
worksheet.Cells["D2"].Value = 3.99;
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);
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:
using (var range = worksheet.Cells[1, 1, 1, 5])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
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
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
