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.