Dropdown list in Excel sheet using c#

In this section we are discussing about how to add table column data through dropdown list in excel sheet and download excel sheet.

First installing and adding reference of ClosedXML library.

• Right click the Project in Solution Explorer and select 'Manage NuGet Packages'.
• Now you will need to look for ClosedXML package and once found, you need to click the Install Button.
• The reference of ClosedXML library automatically added in your reference file, You can check it.

Add Web Page for Generate Excel Sheet.

•  Right click the Project in Solution Explorer and select 'Add' and then select 'New Item'.
•  Now you will select on WebForm Page. and named as 'ExportExcel.aspx'. Then add that page.

Write this below code in your page load of that 'ExportExcel.aspx' webpage.

Used Namespace:

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


Code for Export Excel File:

//CREATE OBJECT OF WORKBOOK
XLWorkbook oWB = new XLWorkbook();

//create worksheet for group master table
 // CREATE A DATATABLE AND ADD DATA FROM TABLE TO DATATABLE
DataTable gdt = new DataTable("Group Master");
gdt.Columns.Add("Id");
gdt.Columns.Add("Group");
//Here i call a method, in this method i store the table data in list format. You can direct write the Linq query here...
var g = da.GetGroup().ToList();
foreach (var gs in g.ToList())
{
   DataRow dr = gdt.NewRow();
   dr["Id"] = gs.GRP_SR_CODE;
   dr["Group"] = gs.GRP_NAME;
   gdt.Rows.Add(dr);
}
// COUNT TABLE DATA
int lastCellNo1 = gdt.Rows.Count + 1;
//ADD THAT DATATABLE IN WORKSHEET AND ADD THAT WORKSHEET IN WORKBOOK
oWB.AddWorksheet(gdt);
var worksheet1 = oWB.Worksheet(1);

//create worksheet for sub group master table
DataTable sgdt = new DataTable("Sub Group Master");
sgdt.Columns.Add("Id");
sgdt.Columns.Add("SubGroup");
var sg = da.GetSubGroup().ToList();
foreach (var gs in sg.ToList())
{
   DataRow dr = sgdt.NewRow();
   dr["Id"] = gs.SGRP_SR_CODE;
   dr["SubGroup"] = gs.SGRP_NAME;
   sgdt.Rows.Add(dr);
}
   int lastCellNo2 = sgdt.Rows.Count + 1;
   oWB.AddWorksheet(sgdt);
   var worksheet2 = oWB.Worksheet(2);

//create worksheet for unit master table
DataTable udt = new DataTable("Unit Master");
udt.Columns.Add("Id");
udt.Columns.Add("Unit");
var u = da.GetUnits().ToList();
foreach (var gs in u.ToList())
{
   DataRow dr = udt.NewRow();
   dr["Id"] = gs.MEAS_NO;
   dr["Unit"] = gs.MEAS_NAME;
   udt.Rows.Add(dr);
}
int lastCellNo3 = udt.Rows.Count + 1;
oWB.AddWorksheet(udt);
var worksheet3 = oWB.Worksheet(3);

//create worksheet for status master table
DataTable stsdt = new DataTable("Status Master");
stsdt.Columns.Add("status");
stsdt.Rows.Add("True");
stsdt.Rows.Add("False");
oWB.AddWorksheet(stsdt);
var worksheet4 = oWB.Worksheet(4);
int lastCellNo4 = stsdt.Rows.Count + 1;

//create worksheet for Mandatory Status master table
DataTable stdt = new DataTable("Mandatory Status Master");
stdt.Columns.Add("mandatorystatus");
stdt.Rows.Add("YES");
stdt.Rows.Add("NO");
oWB.AddWorksheet(stdt);
var worksheet5 = oWB.Worksheet(5);
int lastCellNo5 = stdt.Rows.Count + 1;

//create worksheet for material table and add these columns in that MAS_MATERIAL table
DataTable validationTable = new DataTable();
validationTable.Columns.Add("GRP_SR_CODE");
validationTable.Columns.Add("SGRP_SR_CODE");
validationTable.Columns.Add("MATERIAL_ID");
validationTable.Columns.Add("MAT_TYPE");
validationTable.Columns.Add("MEAS_NO");
validationTable.Columns.Add("DEL_STATUS");
validationTable.Columns.Add("ALLOW_SERIAL_NO");
validationTable.Columns.Add("ALLOW_SALES_PRICE");
validationTable.TableName = "MAS_MATERIAL";                 
var worksheet = oWB.AddWorksheet(validationTable);

//ADD DATA VALIDATION IN MATERIAL WORKSHEET. FOR DROPDOWN LIST SHOWN IN EXCEL SHEET...
worksheet.Column(1).SetDataValidation().List(worksheet1.Range("A2:A" + lastCellNo1), true);
//IN THIS ABOVE LINE Column(1) DENOTES THE MAS_MATERIAL WORKSHEET COLUMN NUMBER
// worksheet1.Range("A2:A" + lastCellNo1)-- HERE WE MENTION WHICH WORKSHEET VALUE WILL SHOWN IN YOUR COLUMN OF MAS_MATERIAL //WORKSHEET AND ALSO WE HAVE TO GIVE THE COLUMN NAME OF THAT MENTIONING WORKSHEET AND ALSO THE RANGE OF THAT COLUMN E.G. : //Range("A2:A" + lastCellNo1).

worksheet.Column(2).SetDataValidation().List(worksheet2.Range("A2:A" + lastCellNo2), true);
worksheet.Column(5).SetDataValidation().List(worksheet3.Range("A2:A" + lastCellNo3), true);
worksheet.Column(6).SetDataValidation().List(worksheet4.Range("A2:A" + lastCellNo4), true);
worksheet.Column(7).SetDataValidation().List(worksheet5.Range("A2:A" + lastCellNo5), true);
worksheet.Column(8).SetDataValidation().List(worksheet5.Range("A2:A" + lastCellNo5), true);

Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=ExcelFormat.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Charset = "";
using (MemoryStream MyMemoryStream = new MemoryStream())
{
    oWB.SaveAs(MyMemoryStream);
    MyMemoryStream.WriteTo(Response.OutputStream);
    Response.Flush();
    Response.End();
}

AFTER RUN THIS CODE THE EXCEL FILE WILL AUTOMATICALLY DOWNLOADED.