Creating Reports in Excel 2007 using EPPlus (Header, Footer, Comments, Image, Formatting, Shape and Formula)

Introduction

EPPlus is really a powerful tool to generate excel based reports on server side and it is becoming my favorite tool as I am getting more experienced with it. Previously I wrote a post about Creating advanced Excel 2007 Reports on Server. This post is update on the last post and I am sharing more advanced feature of EP Plus.

Code which I used in this post can be downloaded from this location.

Create Excel Worksheet

First of all we have to create a worksheet, here is the function which creates a worksheet:-

private static ExcelWorksheet CreateSheet(ExcelPackage p, string sheetName)

 {

    p.Workbook.Worksheets.Add(sheetName);

    ExcelWorksheet ws = p.Workbook.Worksheets[1];

    ws.Name = sheetName; //Setting Sheet's name

    ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet

    ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet

    return ws;

 }

Above code creates a worksheet and assigned name to the sheet. Also set the default font for all cells as Calibri and 11 size.

Merge Excel Columns

Now here is how you can merge several columns into one:-

//Merging cells and create a center heading for out table

ws.Cells[1, 1].Value = "Sample DataTable Export"; // Heading Name

ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true; //Merge columns start and end range

ws.Cells[1, 1, 1, dt.Columns.Count].Style.Font.Bold = true; //Font should be bold

ws.Cells[1, 1, 1, dt.Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

Excel Cell Background Color

Here is how you can modify the background color of a cell:-

//Setting the background color of header cells to Gray

var fill = cell.Style.Fill;

fill.PatternType = ExcelFillStyle.Solid;

fill.BackgroundColor.SetColor(Color.Gray);

Excel Cell Border

You can set borders to cell like this:-

//Setting Top/left,right/bottom borders.

    var border = cell.Style.Border;

    border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;

Excel Formula

Formulas is really powerful feature of Excel. You can apply any formula through the help of EP Plus. Here is how you can add a sum formula:-

//Setting Sum Formula

cell.Formula = "Sum(" + ws.Cells[3, colIndex].Address + ":" + ws.Cells[rowIndex - 1, colIndex].Address + ")";

Note that

ws.Cells[3, colIndex].Address

give the cell Address like A1 or C10 and same address will be used when you edit a formula in Excel.

Add Comments to Excel Cell

Here is the wrapper function through which you can add comments to a cell:-

private static void AddComment(ExcelWorksheet ws, int colIndex, int rowIndex, string comment, string author)

{

    //Adding a comment to a Cell

    var commentCell = ws.Cells[rowIndex, colIndex];

    commentCell.AddComment(comment, author);

}

I tried to enter blank 'Author Name' in comments but it failed to work. So always put a 'Author Name' in comments as well.

Add Image in Excel Sheet

With the help of this function, you can add an Image to your excel sheet:-

private static void AddImage(ExcelWorksheet ws, int columnIndex, int rowIndex, string filePath)

{

    //How to Add a Image using EP Plus

    Bitmap image = new Bitmap(filePath);

    ExcelPicture picture = null;

    if (image != null)

    {

        picture = ws.Drawings.AddPicture("pic" + rowIndex.ToString() + columnIndex.ToString(), image);

        picture.From.Column = columnIndex;

        picture.From.Row = rowIndex;

        picture.From.ColumnOff = Pixel2MTU(2); //Two pixel space for better alignment

        picture.From.RowOff = Pixel2MTU(2);//Two pixel space for better alignment

        picture.SetSize(100, 100);

    }

}

Add Custom objects to Excel Sheet

Here is the function through which you can add a custom shape to your excel sheet:-

private static void AddCustomShape(ExcelWorksheet ws, int colIndex, int rowIndex, eShapeStyle shapeStyle, string text)

{

    ExcelShape shape = ws.Drawings.AddShape("cs" + rowIndex.ToString() + colIndex.ToString(), shapeStyle);

    shape.From.Column = colIndex;

    shape.From.Row = rowIndex;

    shape.From.ColumnOff = Pixel2MTU(5);

    shape.SetSize(100, 100);

    shape.RichText.Add(text);

}

Conclusion

EPPlus is really a nice library to generate creative Excel reports for demanding industry. I have tried to share what I have learned so far. See the image below showing the output of my code.Feel free to comment. Happy coding !!!