Import Excel to dynamically created Table based on Excel data in ASP.Net

Hi sudhir0538,

I have created one sample that full fill your requirement.

Namespace

using System.IO;
using System.Configuration;
using System.Data.OleDb;
using ClosedXML.Excel;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

Webconfig

<appSettings>
  <add key="FolderPath" value="Files/"/>
</appSettings>
<connectionStrings>
  <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
  <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
  <add name="conString" connectionString="Data Source=.;database=Test;UID=sa;PWD=123" providerName="System.Data.SqlClient"/>
</connectionStrings>

HTML

<asp:Panel ID="Panel1" runat="server">
    <table>
        <tr>
            <td>
                <asp:FileUpload ID="FileUpload1" runat="server" />
            </td>
            <td>
                <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
            </td>
        </tr>
        <tr>
            <td>
                <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>
            </td>
        </tr>
    </table>
</asp:Panel>
<asp:Panel ID="Panel2" runat="server" Visible="false">
    <table>
        <tr>
            <td>
                <asp:Label ID="Label5" runat="server" Text="File Name" />
            </td>
            <td>
                <asp:Label ID="lblFileName" runat="server" Text="" />
            </td>
        </tr>
        <tr>
            <td>
                <asp:Label ID="Label2" runat="server" Text="Select Sheet" />
            </td>
            <td>
                <asp:DropDownList ID="ddlSheets" runat="server" AppendDataBoundItems="true">
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td>
                <asp:Label ID="Label3" runat="server" Text="Enter Table Name" />
            </td>
            <td>
                <asp:TextBox ID="txtTable" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td align="right">
                <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" />
            </td>
            <td>
                <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
            </td>
        </tr>
    </table>
</asp:Panel>

Code

protected void btnUpload_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string FilePath = Server.MapPath(FolderPath + FileName);
        FileUpload1.SaveAs(FilePath);
        GetExcelSheets(FilePath, Extension, "Yes");
    }
}
private void GetExcelSheets(string FilePath, string Extension, string isHDR)
{
    string conStr = "";
    switch (Extension)
    {
        case ".xls":
            conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx":
            conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
            break;
    }
    conStr = String.Format(conStr, FilePath, isHDR);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    cmdExcel.Connection = connExcel;
    connExcel.Open();
    ddlSheets.Items.Clear();
    ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));
    ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    ddlSheets.DataTextField = "TABLE_NAME";
    ddlSheets.DataValueField = "TABLE_NAME";
    ddlSheets.DataBind();
    connExcel.Close();
    txtTable.Text = "";
    lblFileName.Text = Path.GetFileName(FilePath);
    Panel2.Visible = true;
    Panel1.Visible = false;
}
protected void btnSave_Click(object sender, EventArgs e)
{
    string FileName = lblFileName.Text;
    string Extension = Path.GetExtension(FileName);
    DataTable dt = new DataTable();
    string insertQuery = string.Empty;
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileName);
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                if (row.Cells().ToArray().Length > 0)
                {
                    dt.Rows.Add();
                    int i = 0;
                    insertQuery += "INSERT INTO " + txtTable.Text.Trim() + " VALUES (";
                    int count = row.CellCount();
                    foreach (IXLCell cell in row.Cells())
                    {
                        insertQuery += "'" + cell.Value.ToString() + "',";
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                    insertQuery = insertQuery.Remove(insertQuery.Length - 1, 1) + " );";
                }
            }
        }
    }
    int j = 0;
    string query = "CREATE TABLE [dbo].[" + txtTable.Text.Trim() + "](";
    foreach (DataColumn column in dt.Columns)
    {
        var columsData = from columnData in dt.AsEnumerable()
                            select new
                            {
                                column = columnData.Field<string>(j)
                            };
        string dataType = string.Empty;
        var maxlengthItem = columsData.Max(element => element.column.Length);
        var IsDecimal = Array.Exists(columsData.ToArray(), element => element.column.Contains("."));
        var IsChar = columsData.Any(c => !c.column.Any(char.IsDigit));
        var IsSymbol = columsData.Any(c => !Regex.IsMatch(c.column, "^[a-zA-Z0-9 ]*$"));
        if (!IsDecimal && !IsSymbol && !IsChar)
        {
            dataType = "INT";
            if (maxlengthItem > 4)
            {
                dataType = "BIGINT";
            }
        }
        else if (IsDecimal)
        {
            dataType = "DECIMAL";
            dataType = dataType + "(" + (maxlengthItem + 5) + ",2)";
        }
        else if (IsSymbol)
        {
            dataType = "NVARCHAR";
            dataType = dataType + "(" + (maxlengthItem + 10) + ")";
        }
        else if (IsChar)
        {
            dataType = "VARCHAR";
            dataType = dataType + "(" + (maxlengthItem + 10) + ")";
            if (maxlengthItem == 1)
            {
                dataType = "CHAR(1)";
            }
        }
        j++;
        query += "[" + column.ColumnName + "] " + dataType + ",";
    }
    query = query.Remove(query.Length - 1, 1) + " )";
    string str = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(str);
    try
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
        using (SqlCommand cmd1 = new SqlCommand(insertQuery, con))
        {
            cmd1.ExecuteNonQuery();
        }
        lblMessage.ForeColor = System.Drawing.Color.Green;
        lblMessage.Text = "Records inserted successfully.";
    }

    catch (Exception ex)
    {
        lblMessage.ForeColor = System.Drawing.Color.Red;
        lblMessage.Text = ex.Message;
    }
    finally
    {
        con.Close();
        con.Dispose();
        Panel1.Visible = true;
        Panel2.Visible = false;
    }
}
protected void btnCancel_Click(object sender, EventArgs e)
{
    Panel1.Visible = true;
    Panel2.Visible = false;
}

 Screenshot

Excel 

DB