Create Folder (Directory) and save file using C# and VB.Net in ASP.Net

Hi BugHunter,

Refer below sample.

HTML

<asp:Label Text="Excelasoft" ID="lblCompanyName" runat="server" Visible="false" />
<asp:Button Text="Download File" runat="server" OnClick="DownLoad" />

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using ClosedXML.Excel;

VB.Net

Imports System.Data.SqlClient
Imports System.Data
Imports ClosedXML.Excel
Imports System.IO

Code

C#

protected void DownLoad(object sender, EventArgs e)
{
    string SelectQuery = "SELECT * FROM Customers";
    String Month = DateTime.Now.ToString("MMMM");
    String Year = DateTime.Now.Year.ToString();
    string Month_Year = string.Concat(Month, "__", Year);
    string CompanyName = lblCompanyName.Text.ToString();
    string fileName = CompanyName + "_" + Month_Year + ".xlsx";
    string clientName = "Test";
    string path = @"D:\Users" + "//" + clientName;

    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(SelectQuery))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        wb.Worksheets.Add(dt, "DownloadedSheet1");
                        if (!Directory.Exists(path))
                        {
                            Directory.CreateDirectory(path);
                        }

                        using (MemoryStream MyMemoryStream = new MemoryStream())
                        {
                            wb.SaveAs(path + "//" + fileName);
                            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('File Saved Succesfully in " + path + " Drive.');", true);
                        }
                    }
                }
            }
        }
    }
}

VB.Net

Protected Sub DownLoad(ByVal sender As Object, ByVal e As EventArgs)
    Dim SelectQuery As String = "SELECT * FROM Customers"
    Dim Month As String = DateTime.Now.ToString("MMMM")
    Dim Year As String = DateTime.Now.Year.ToString()
    Dim Month_Year As String = String.Concat(Month, "__", Year)
    Dim CompanyName As String = lblCompanyName.Text.ToString()
    Dim fileName As String = CompanyName & "_" & Month_Year & ".xlsx"
    Dim clientName As String = "Test"
    Dim path As String = "D:\Users" & "//" & clientName
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(SelectQuery)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd

                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)

                    Using wb As XLWorkbook = New XLWorkbook()
                        wb.Worksheets.Add(dt, "DownloadedSheet1")

                        If Not Directory.Exists(path) Then
                            Directory.CreateDirectory(path)
                        End If

                        Using MyMemoryStream As MemoryStream = New MemoryStream()
                            wb.SaveAs(path & "//" & fileName)
                            ScriptManager.RegisterStartupScript(Me, Me.GetType(), "Message", "alert('File Saved Succesfully in " & path & " Drive.');", True)
                        End Using
                    End Using
                End Using
            End Using
        End Using
    End Using
End Sub