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.