Generate Excel Report with Graph in Asp.NET

• Generate Excel file with graph from database in asp.net (export to excel)

In this post we will learn how to generate/Export excel file in asp.net C# with graph and data (from database). This type of report is useful in visualizing your data.

• Steps for generating excel file.

1. Create new website or web application in visual studio
2. download EpPlus (An opensource library for Excel) from Here  OR you can add it to your project via Nuget package manager
3. Download DocumentFormat.OpenXml from here OR you can add it to your project via Nuget package manager.
4. Add Reference of Epplus and DocumentFormat.OpenXml.dll to your project 

  5. Add web form to your project (default.aspx) to your project should look like this.

 <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">

    <div class="jumbotron">
        <h1>ASP.NET</h1>
        <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS, and JavaScript.</p>
        <p><a href="http://www.asp.net" class="btn btn-primary btn-lg">Learn more &raquo;</a>
        <asp:Button ID="btnExportToExcel" runat="server" Text="Export to Excel" CssClass="btn btn-danger btn-lg" OnClick="btnExportToExcel_Click" /></p>
    </div>

    <div class="row">
        <div class="col-md-4">
            <h2>Getting started</h2>
            <p>
                ASP.NET Web Forms lets you build dynamic websites using a familiar drag-and-drop, event-driven model.
            A design surface and hundreds of controls and components let you rapidly build sophisticated, powerful UI-driven sites with data access.
            </p>
            <p>
                <a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301948">Learn more &raquo;</a>
            </p>
        </div>
        <div class="col-md-4">
            <h2>Get more libraries</h2>
            <p>
                NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.
            </p>
            <p>
                <a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301949">Learn more &raquo;</a>
            </p>
        </div>
        <div class="col-md-4">
            <h2>Web Hosting</h2>
            <p>
                You can easily find a web hosting company that offers the right mix of features and price for your applications.
            </p>
            <p>
                <a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301950">Learn more &raquo;</a>
            </p>
        </div>
    </div>
</asp:Content>

The code behind for default.aspx.cs should look like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using OfficeOpenXml.Drawing.Chart;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        ExcelPackage pck = new ExcelPackage();
        pck.Workbook.Worksheets.Add("Summary Report");
        ExcelWorksheet ws = pck.Workbook.Worksheets[1];

        ws.PrinterSettings.Orientation = eOrientation.Landscape;
        ws.Cells.AutoFitColumns();

        SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Accounting");
        SqlDataAdapter da = new SqlDataAdapter(@"SELECT     Department.DepartmentName AS [Department Name], COUNT(Employee.EmployeeID) AS Employees
FROM   Department INNER JOIN Employee ON Department.DepartmentID = Employee.DepartmentID GROUP BY Department.DepartmentName", con);
        DataTable dt = new DataTable();
        da.Fill(dt);
        ws.Cells["A1:H2"].Merge = true;
        ws.Cells["A1:H2"].Style.Font.Size = 14.3f;
        ws.Cells["A1:H2"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
        ws.Cells["A1:H2"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
        ws.Cells["A1:H2"].Value = "Employees by Department";
        ws.Cells[3, 1].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light11);

        var chart = ws.Drawings.AddChart("Employee by department", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
        
        chart.Series.Add("B3:B9", "A3:A9");
        chart.SetPosition(2, 4, 3,4);
        chart.SetSize(500, 300);
        chart.ShowHiddenData = true;
        chart.XAxis.Orientation = OfficeOpenXml.Drawing.Chart.eAxisOrientation.MinMax;


        chart.Title.Text = "Employee by department";
        chart.ShowDataLabelsOverMaximum = true;

        Response.Clear();
        Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
        Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
        Response.AddHeader("content-disposition", "attachment;  filename=MyReport.xlsx");
        Response.ContentType = "application/text";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Response.BinaryWrite(pck.GetAsByteArray());
        Response.End();


    }
}

6. Run the follwing sql script in sql server for database & table creation & record insertion

create database Accounting
go
use Accounting
go

create table Department
(
 DepartmentID int primary key identity,
 DepartmentName varchar(200)
)

create table Employee
(
  EmployeeID int primary key identity,
  EmpName varchar(200),
  Address varchar(200),
  DepartmentID int,
  constraint fk_dept_emp foreign key(DepartmentID) references Department

(departmentID)
)

insert into Department values('Physics')
insert into Department values('Biology')
insert into Department values('Chemistry')
insert into Department values('Computer Science')
insert into Department values('Botony')
insert into Department values('Geology')

insert into Employee values('Ahmad','Add 1',1)
insert into Employee values('Mahmood','Add 2',1)
insert into Employee values('Zahir','Add 3',1)
insert into Employee values('Aziz','Add 4',1)
insert into Employee values('Khalid','Add 5',2)
insert into Employee values('Mirwaise','Add 6',2)
insert into Employee values('Inayat','Add 2',2)
insert into Employee values('Jamil','Add 3',3)
insert into Employee values('Bari','Add 4',3)
insert into Employee values('Qureshi','Add 1',3)
insert into Employee values('Farooq','Add 9',3)
insert into Employee values('Qazi','Add 8',3)
insert into Employee values('massod','Add 13',3)
insert into Employee values('Fahim','Add 8',3)

insert into Employee values('Javed','Add 1',1)
insert into Employee values('Sakhee','Add 2',1)
insert into Employee values('Yunis','Add 3',1)
insert into Employee values('Yusuf','Add 4',1)
insert into Employee values('ilyas','Add 5',2)
insert into Employee values('Abid','Add 6',2)
insert into Employee values('Shahid','Add 2',2)
insert into Employee values('Saad','Add 3',3)
insert into Employee values('Ashraf','Add 4',3)
insert into Employee values('Gul','Add 1',3)
insert into Employee values('Tariq','Add 9',3)
insert into Employee values('Zarif','Add 8',3)
insert into Employee values('Toryalai','Add 13',3)
insert into Employee values('Shakoor','Add 8',3)
insert into Employee values('jalal','Add 1',5)
insert into Employee values('Mahmood','Add 2',5)
insert into Employee values('Zahir','Add 3',5)
insert into Employee values('Aziz','Add 4',5)
insert into Employee values('Khalid','Add 5',5)
insert into Employee values('Mirwaise','Add 6',5)
insert into Employee values('Inayat','Add 2',6)
insert into Employee values('Jamil','Add 3',6)
insert into Employee values('Bari','Add 4',6)
insert into Employee values('Qureshi','Add 1',6)
insert into Employee values('Farooq','Add 9',6)
insert into Employee values('Qazi','Add 8',6)
insert into Employee values('massod','Add 13',6)
insert into Employee values('Fahim','Add 8',6)

insert into Employee values('Javed','Add 1',4)
insert into Employee values('Sakhee','Add 2',4)
insert into Employee values('Yunis','Add 3',4)
insert into Employee values('Yusuf','Add 4',4)
insert into Employee values('ilyas','Add 5',4)
insert into Employee values('Abid','Add 6',4)
insert into Employee values('Shahid','Add 2',4)
insert into Employee values('Saad','Add 3',4)
insert into Employee values('Ashraf','Add 4',4)
insert into Employee values('Gul','Add 1',5)
insert into Employee values('Tariq','Add 9',5)
insert into Employee values('Zarif','Add 8',5)
insert into Employee values('Toryalai','Add 13',6)
insert into Employee values('Shakoor','Add 8',6)

7. Run your project and click Export button and you will see the result