ASP.NET MVC - Export Multiple Data tables to Excel With Multiple Sheets using C#

In order to export multiple data tables to excel with multiple sheets, you can use the NuGet package ClosedXML. With this package, you can create the desired structure using the class "XLWorkbook". This class is going to provide a byte array which you can later bind to Memory Stream and return as "FileStreamResult" with the header "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" along with file name having the extension ".xlsx". For details, please follow the steps below.

Step 1:

First of all, we have to create a new Asp.Net Application; let us see the description with images of how to create it.

• Open Visual Studio
• File>New>Project
• Visual C#>Web>ASP.NET Web Application(.NET Framework)>Enter the Project Name and Browse the file path

• Select MVC > OK

Step 2:

• Open SQL-Management Studio, Create a database "College.Database"

• Creating an "Employee" table, execute the following script.

USE [College.Database]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 7/24/2018 6:04:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
 [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](100) NOT NULL,
 [DepartmentName] [varchar](100) NOT NULL,
 [Designation] [varchar](100) NOT NULL,
 [Address] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
 [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (1, N'Shivaraj', N'CSE', N'HOD', N'Xyz')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (2, N'Naveen', N'CSE', N'Lecturer', N'aaaaa')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (3, N'Seetha ', N'CSE', N'Lecturer', N'bbbb')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (4, N'Bhargavi', N'ISE', N'HOD', N'zccczx')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (5, N'Akshatha', N'ISE', N'Lecturer', N'fghjk')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (6, N'Shashidhar', N'EC ', N'HOD', N'qwert')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (7, N'Prashanth', N'EC', N'Lecturer', N'dfgh')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (8, N'Clinton', N'ME', N'HOD', N'qwert')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (9, N'Deeksha', N'ME', N'Lecturer', N'qwert')
GO
SET IDENTITY_INSERT [dbo].[Employees] OFF
GO

• Creating an "UspGetExcelData" stored procedure execute the following script

USE [College.Database]
GO
CREATE PROCEDURE [dbo].[UspGetExcelData] 
 @DepartmentName VARCHAR(100)
AS
BEGIN
 
 SELECT *
 FROM Employees
 WHERE DepartmentName = @DepartmentName
END

Step 3:

• We should install the "ClosedXML" NuGet package in your application
• Right click on application > Manage NuGet Packages > Browse > Search ClosedXML > Click install

• Open the "Web.config" file and add the following code for database connection string

• Create a "Repository" folder
• Create "BaseRepository" class under Repository  folder and add the following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace ExportExcelExample.Repository
{
    public abstract class BaseRepository
    {
        protected string connectionString = WebConfigurationManager.ConnectionStrings["collegeDB"].ConnectionString;
    }
}

• Create "StoredProcedureParameters" class under Models folder and add the following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ExportExcelExample.Models
{
    public class StoredProcedureParameters
    {
        public string Name { get; set; }
        public string Value { get; set; }
    }
}

• Create "EmployeeRepository" class under Repository folder and add the following code

using System;
using ExportExcelExample.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using System.Web;

namespace ExportExcelExample.Repository
{
    public class EmployeeRepository: BaseRepository
    {
        public DataSet GetDataSetExportToExcel()
        {
            DataSet ds = new DataSet();
            var departments = new List() { "CSE", "ISE", "EC", "ME" };

            foreach (var department in departments)
            {
                DataTable dt = new DataTable();
                dt = GetDataTableExportToExcel(department);
                ds.Tables.Add(dt);
            }

            return ds;
        }

        private DataTable GetDataTableExportToExcel(string departmentName)
        {
            DataTable dt = new DataTable();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand("UspGetExcelData", con))
                {
                    var parameters = new List()
                    {
                        new StoredProcedureParameters(){Name = "@DepartmentName",Value= departmentName }
                    };

                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));
                    }

                    using (var sda = new SqlDataAdapter(cmd))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }

    }
}

• Create an "Employee" controller class and add the following code

using ClosedXML.Excel;
using ExportExcelExample.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;

namespace ExportExcelExample.Controllers
{
    public class EmployeeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult ExportToExcel()
        {
            var sheetNames = new List() { "CSE", "ISE", "EC", "ME" };
            string fileName = "Department.xlsx";

            EmployeeRepository employeeRepository = new EmployeeRepository();
            DataSet ds = employeeRepository.GetDataSetExportToExcel();

            XLWorkbook wbook = new XLWorkbook();

            for (int k = 0; k < ds.Tables.Count; k++)
            {
                DataTable dt = ds.Tables[k];
                IXLWorksheet Sheet = wbook.Worksheets.Add(sheetNames[k]);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Sheet.Cell(1, (i + 1)).Value = dt.Columns[i].ColumnName;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Sheet.Cell((i + 2), (j + 1)).Value = dt.Rows[i][j].ToString();
                    }
                }
            }

            Stream spreadsheetStream = new MemoryStream();
            wbook.SaveAs(spreadsheetStream);
            spreadsheetStream.Position = 0;

            return new FileStreamResult(spreadsheetStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { FileDownloadName = fileName };
        }

    }
}

• Create "Index" view in following path, Views>Employee>Index.cshtml

@{
    ViewBag.Title = "Index";
}


Export Data Tables to Excel With Multiple Sheets





            

Results:

• Run the application and click on the Export button to download the excel file.

• Downloaded excel file will look like this

To download the source code, please click the following GitHub link and clone the repository  https://github.com/shivarajad27/ExportExcelDemo.git.