Add hyper link in Excel using EPPlus

Many People has asked me that how they can add hyper link to another sheet through EPPlus. This is bit tricky because ws.Cells[1,1].Hyperlink takes Uri object which is generally used for external references only (file or website).

So after doing bit RnD I have developed a function through which you can easily add hyperlinks within your sheets. Here is the function:-

/// 

/// Adds the hyper link.
/// 
/// "ws">The ws.
/// "source">The source.
/// "destination">The destination.
/// "displayText">The display text.
private static void AddHyperLink(ExcelWorksheet ws, ExcelRange source, ExcelRange destination, string displayText)
{
	source.Formula = "HYPERLINK(\"[\"&MID(CELL(\"filename\"),SEARCH(\"[\",CELL(\"filename\"))+1, SEARCH(\"]\",CELL(\"filename\"))-SEARCH(\"[\",CELL(\"filename\"))-1)&\"]" + destination.FullAddress + "\",\"" + displayText + "\")";
}

Here is how you can call above function to add hyperlinks in your cell:-

ExcelWorksheet ws = CreateSheet(p,"Sample Sheet");
ExcelWorksheet sheet2 = CreateSheet(p, "Sample Sheet 2");
sheet2.Cells[1, 1].Value = "Hyperlink Target";
AddHyperLink(ws, ws.Cells[13, 1], sheet2.Cells[1,1], "Sample Hyperlink");

Hopefully this tip will be useful for you, Happy Coding !!!

Read/Edit Excel 2007 File Contents using EPPlus

In my previous articles related to EPPlus I have shown you that how you can generate Basic and Advance reports using EPPlus. Many people has asked me that how we can read the contents of an excel file using EPPlus. This is actually quite simple task. Here is the code to do this:-

using (ExcelPackage p = new ExcelPackage())
{
    using (FileStream stream = new FileStream("92b69c48-dda7-4544-bdcc-c6925a5f1bec.xlsx", FileMode.Open))
    {
        p.Load(stream);
 
        ExcelWorksheet ws = p.Workbook.Worksheets["Sample Sheet"];
        int rowIndex = 2;
 
        string text = ws.Cells[rowIndex, 1].Value.ToString(); //Read Text
        MessageBox.Show("Text in [" + rowIndex + ",1]=" + text);
 
        string comment = ws.Comments[0].Text; // Access Comments
        MessageBox.Show("Comments = " + comment);
 
        string pictureName = ws.Drawings[0].Name;// Access Picture
        MessageBox.Show("Picture = " + pictureName);
    }
}

Things are very simple so I am not sharing more details. You can use above code to edit your existing excel files as well.

To download code, use this link.

Feel free to comment. Happy Coding !!!

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 !!!

Creating advanced Excel 2007 Reports on Server

Recently I was looking for an Advance tool through which I can generate complex Excel Reports. And after going through many tools I found EP Plus. For further details see this link. Through this tool we can easily create reports with charts, graphs and other drawing objects. I have planned to shared few samples with the community, so if any one is interested in using this library he will get a good kick start.

In this sample application I have tried to explain that how you can generate a report using DataTable. I also demonstrated how to use different formatting options and formulas.

So, here is the code:-

/// <summary>

/// Creates the data table.

/// </summary>

/// <returns>DataTable</returns>

private static DataTable CreateDataTable()

{

    DataTable dt = new DataTable();

    for (int i = 0; i < 10; i++)

    {

        dt.Columns.Add(i.ToString());

    }

    for (int i = 0; i < 10; i++)

    {

        DataRow dr = dt.NewRow();

        foreach (DataColumn dc in dt.Columns)

        {

            dr[dc.ToString()] = i;

        }

        dt.Rows.Add(dr);

    }

    return dt;

}

private void button1_Click(object sender, EventArgs e)

{

    using (ExcelPackage p = new ExcelPackage())

    {

        //Here setting some document properties

        p.Workbook.Properties.Author = "Zeeshan Umar";

        p.Workbook.Properties.Title = "Office Open XML Sample";

        //Create a sheet

        p.Workbook.Worksheets.Add("Sample WorkSheet");

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

        ws.Name = "Sample Worksheet"; //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

        DataTable dt = CreateDataTable(); //My Function which generates DataTable

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

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

        ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true;

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

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

        int colIndex = 1;

        int rowIndex = 2;

        foreach (DataColumn dc in dt.Columns) //Creating Headings

        {

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

            //Setting the background color of header cells to Gray

            var fill = cell.Style.Fill;

            fill.PatternType = ExcelFillStyle.Solid;

            fill.BackgroundColor.SetColor(Color.Gray);

            //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;

            //Setting Value in cell

            cell.Value = "Heading " + dc.ColumnName;

            colIndex++;

        }

        foreach (DataRow dr in dt.Rows) // Adding Data into rows

        {

            colIndex = 1;

            rowIndex++;

            foreach (DataColumn dc in dt.Columns)

            {

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

                //Setting Value in cell

                cell.Value = Convert.ToInt32(dr[dc.ColumnName]);

                //Setting borders of cell

                var border = cell.Style.Border;

                border.Left.Style =

                    border.Right.Style = ExcelBorderStyle.Thin;

                colIndex++;

            }

        }

        colIndex = 0;

        foreach (DataColumn dc in dt.Columns) //Creating Headings

        {

            colIndex++;

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

            //Setting Sum Formula

            cell.Formula = "Sum("+ 

                            ws.Cells[3, colIndex].Address+

                            ":"+

                            ws.Cells[rowIndex-1, colIndex].Address+

                            ")";

            //Setting Background fill color to Gray

            cell.Style.Fill.PatternType = ExcelFillStyle.Solid;

            cell.Style.Fill.BackgroundColor.SetColor(Color.Gray);

        }

        //Generate A File with Random name

        Byte[] bin = p.GetAsByteArray();

        string file = "d:\\" + Guid.NewGuid().ToString() + ".xlsx";

        File.WriteAllBytes(file, bin);

    }

}

Here is the snapshot of Excel File which is created through above code:-

Hopefully this will be useful for you, keep posting your comments.For further details and download see EP Plus home page.

Export to Excel in .Net

There is a wonderful open source Excel Library through which you can easily convert DataSet into multi sheet excel file, just by one line of code like this:-

ExcelXmlWorkbook sheet = ExcelXmlWorkbook.DataSetToWorkbook(sourceDataSet);

Also adding a sample code which will really help you to create reports:-

private void YougeshSample()

{

    DataTable dt = CreateDataTable();

    ExcelXmlWorkbook book = new ExcelXmlWorkbook();

    book.Properties.Author = "Zeeshan Umar";

    book.Properties.Company = "Sample Company";

    book.Properties.Title = "Sample Title";

    book.Properties.Subject = "Subject";

    Worksheet ws = book[0];

    ws.Name = "Sample Sheet Name"; //Sheet Name

    ws.Font.Name = "Calibri";//Setting font for all sheet

    ws.Font.Size = 11;

    int rowIndex = 0;

    Row row;

    row = ws[rowIndex++];

    int colIndex = 0;

    foreach (DataColumn dc in dt.Columns) //Creating Headings

    {

        row[colIndex].Value = "Heading " + dc.ColumnName;

        row[colIndex].Border.Sides = BorderSides.All;

        row[colIndex].Style.Interior.Color = Color.LightGray;

        colIndex++;

    }

    foreach (DataRow dr in dt.Rows) // Adding Data into rows

    {

        colIndex = 0;

        row = ws[rowIndex++];

        foreach (DataColumn dc in dt.Columns)

        {

            row[colIndex].Value = Convert.ToInt32(dr[dc.ColumnName]);

            row[colIndex].Border.Sides = BorderSides.Left | BorderSides.Right;

            setIntegerFormat(row[colIndex]);

            colIndex++;

        }

    }

    row = ws[rowIndex++];

    colIndex = 0;

    foreach (DataColumn dc in dt.Columns) //Adding summ formula for last row

    {

        row[colIndex].Value = FormulaHelper.Formula("sum", 

            new Range(ws[colIndex, 1], ws[colIndex, 9]));

        row[colIndex].Border.Sides = BorderSides.All;

        row[colIndex].Style.Interior.Color = Color.LightGray;

        colIndex++;

    }

    string s = "c:\\" + Guid.NewGuid().ToString() + ".xml";

    book.Export(s);

}

private static DataTable CreateDataTable()

{

    DataTable dt = new DataTable();

    for (int i = 0; i < 300; i++)

    {

        dt.Columns.Add(i.ToString());

    }

    for (int i = 0; i < 10; i++)

    {

        DataRow dr = dt.NewRow();

        foreach (DataColumn dc in dt.Columns)

        {

            dr[dc.ToString()] = i;

        }

        dt.Rows.Add(dr);

    }

    return dt;

}

private void setIntegerFormat(Cell cell)

{

    cell.DisplayFormat = DisplayFormatType.Custom;

    cell.CustomFormatString = "#,##0";

}

private void setDateFormat(Cell cell)

{

    cell.DisplayFormat = DisplayFormatType.GeneralDate;

    cell.CustomFormatString = "dd\\-mmm\\-yyyy\\ hh:mm";

}

For further details see this link:-
A Very Easy to Use Excel XML Import-Export Library

To download latest version of library see this link:-
Excel Xml Library 2.45 released