Export to MacroEnableExcel File

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EPPlusForMacroExcel.aspx.cs" Inherits="WebApplication1.EPPlusForMacroExcel" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery-3.3.1.js"></script>
    <script src="JavaScript/JavaScript1.js"></script>
    <script>
        $(document).ready(function () {

            $("#Button0").click(function () {
                //****Set validation****
                var startDt = $("#Text1").val();
                var endDt = $("#Text2").val();

                $.ajax({
                    type: "POST",
                    //aspx file.aspx
                    url: "EPPlusForMacroExcel.aspx/ExportToExcel",
                    data: "{startDate:'" + startDt + "',endDate:'" + endDt + "'}",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (responce) {
                        responce.d.forEach(function (file) {
                            //Download file 1 by 1
                            //window.open(Parameter 1: Path, 2: WindowName)
                            window.open("TempFiles/" + file, file);
                        });
                    }
                });

            });

            $("#Button1").click(function () {
                //****Set validation****
                var startDt = $("#Text1").val();
                var endDt = $("#Text2").val();

                $.ajax({
                    type: "POST",
                    //aspx file.aspx
                    url: "EPPlusForMacroExcel.aspx/ExportToMacroEnableExcel",
                    data: "{startDate:'" + startDt + "',endDate:'" + endDt + "'}",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (responce) {
                        responce.d.forEach(function (file) {
                            //Download file 1 by 1
                            //window.open(Parameter 1: Path, 2: WindowName)
                            window.open("TempFiles/" + file, file);
                        });
                    }
                });

            });

            $("#Text1").datepicker({ dateFormat: "mm/dd/yy" });
            $('#Text2').datepicker({ dateFormat: "mm/dd/yy" });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <input id="Text1" type="date" />
            <input id="Text2" type="date" />
            <input id="Button0" type="button" value="Download xlsx (Excel file)" />
            <input id="Button1" type="button" value="Download xlsm (Macro Excel file)" />
        </div>
    </form>
</body>
</html>

-------------------------------------------------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using OfficeOpenXml;
using OfficeOpenXml.VBA;
using OfficeOpenXml.Style;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Web.Services;

namespace WebApplication1
{
    public partial class EPPlusForMacroExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        [WebMethod]
        public static List<string> ExportToExcel(string startDate, string endDate)
        {
            //******Set your location******
            //Also Add "TempFiles" and "Files" folder inside project
            // Keep original files inside "Files"
            string projectLocation = @"C:\Users\Harshal\source\repos\EPPlusWithMacroExcel\WebApplication1\";
            DirectoryInfo di = new DirectoryInfo(projectLocation + "TempFiles\\");
            foreach (FileInfo file in di.GetFiles())
            {
                //It will delete old or existing files
                file.Delete();
            }

            string connetionString = @"Data Source=WINDOWS-4MDA05O\SA;Initial Catalog=Quantam;User ID=sa;Password=harshal008";
            SqlConnection cnn = new SqlConnection(connetionString);
            DataSet ds = new DataSet();

            //Set StoreProcedure
            SqlCommand cmd = new SqlCommand("MicroTest", cnn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cnn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            cnn.Close();
            Dictionary<string, string> tableFilePair = new Dictionary<string, string>() {
                //be carefull with name. do not make mistake
                { "Table","ExcelFile0" },
                {"Table1","ExcelFile1" },
                {"Table2","ExcelFile2" }
            };
            foreach (DataTable dt in ds.Tables)
            {
                string fileName = tableFilePair[dt.TableName];
                //Set relative URL ex. (~/TempFile/)
                ExcelPackage excel = new ExcelPackage(new FileInfo(projectLocation + "Files\\" + fileName + ".xlsx"));
                ExcelWorksheet excelWorkbook = excel.Workbook.Worksheets.First();

                int columnCount = dt.Columns.Count;

                //Data will start with 2nd row in sheet.
                int recordIndexStart = 2;
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < columnCount; i++)
                    {
                        excelWorkbook.Cells[recordIndexStart, i + 1].Value = dr[i];
                    }
                    recordIndexStart++;
                }

                for (int i = 0; i < columnCount; i++)
                {
                    excelWorkbook.Column(i + 1).AutoFit();
                }
                //Set relative URL ex. (~/TempFile/)
                excel.SaveAs(new FileInfo(projectLocation + "TempFiles\\" + fileName + ".xlsx"));
            }

            List<string> listOfFileNames = new List<string>();
            //Set relative URL ex. (~/TempFile/)
            DirectoryInfo diTempFiles = new DirectoryInfo(projectLocation + "TempFiles\\");

            foreach (FileInfo file in diTempFiles.GetFiles())
            {
                listOfFileNames.Add(file.Name);
            }
            return listOfFileNames;
        }

        [WebMethod]
        public static List<string> ExportToMacroEnableExcel(string startDate, string endDate)
        {
            string projectLocation = @"C:\Users\Harshal\source\repos\EPPlusWithMacroExcel\WebApplication1\";
            DirectoryInfo di = new DirectoryInfo(projectLocation + "TempFiles\\");
            foreach (FileInfo file in di.GetFiles())
            {
                //It will delete old or existing files
                file.Delete();
            }

            string connetionString = @"Data Source=WINDOWS-4MDA05O\SA;Initial Catalog=Quantam;User ID=sa;Password=harshal008";
            SqlConnection cnn = new SqlConnection(connetionString);
            DataSet ds = new DataSet();

            //Set StoreProcedure
            SqlCommand cmd = new SqlCommand("MicroTest", cnn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cnn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            cnn.Close();
            foreach (DataTable dt in ds.Tables)
            {
                //Set relative URL ex. (~/TempFile/)
                ExcelPackage excelMicroEnableTest = new ExcelPackage(new FileInfo(projectLocation + "Files\\" + dt.TableName + ".xlsm"));
                ExcelWorksheet excelWorkbook = excelMicroEnableTest.Workbook.Worksheets.First();

                int columnCount = dt.Columns.Count;

                //Data will start with 2nd row in sheet.
                int recordIndexStart = 2;
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < columnCount; i++)
                    {
                        excelWorkbook.Cells[recordIndexStart, i + 1].Value = dr[i];
                    }
                    recordIndexStart++;
                }

                for (int i = 0; i < columnCount; i++)
                {
                    excelWorkbook.Column(i + 1).AutoFit();
                }
                //Set relative URL ex. (~/TempFile/)
                excelMicroEnableTest.SaveAs(new FileInfo(projectLocation + "TempFiles\\" + dt.TableName + ".xlsm"));
            }

            List<string> listOfFileNames = new List<string>();
            //Set relative URL ex. (~/TempFile/)
            DirectoryInfo diTempFiles = new DirectoryInfo(projectLocation + "TempFiles\\");

            foreach (FileInfo file in diTempFiles.GetFiles())
            {
                listOfFileNames.Add(file.Name);
            }
            return listOfFileNames;
        }
    }
}
------------------------------------------------------------------------------------------------------------
Need to work
Validation on dates
Please check column time cordination between file and datatable

Doubts
what type of date format is required
what if result is zero rows in any table? Should it download empty file
what if number of file column and datatable column are mismatched
  ------------------------------------------------------------------------------------------------------
How to create a macro excel link

https://www.guru99.com/introduction-to-macros-in-excel.html