Replace special character and spaces from column name while Importing Excel file in ASP.Net

Hi telldurges,

Please share sample data and expected output.

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
                    {
                        using (DbContext db = new DbContext())
                        {
                            string cs = db.CONNECTION_STRING;
                            Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
                            Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
                            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
                            DataTable dt = new DataTable();
                            //bool firstRow = true;
                            foreach (Row row in rows)
                            {
                                if (row.RowIndex.Value == 1)
                                {
                                    AddQuery += "IF OBJECT_ID('dbo." + map.FileName + "', 'U') IS NULL ";
                                    AddQuery += "BEGIN ";
                                    AddQuery += "CREATE TABLE [dbo].[" + map.FileName + "](";
                                   // AddQuery += "id int identity , ";
                                    foreach (Cell cell in row.Descendants<Cell>())
                                    {
                                        dt.Columns.Add(GetValue(doc, cell));
                                        AddQuery += "[" + GetValue(doc, cell).ToString() + "]" + " VARCHAR(MAX),";
                                    }
                                    //firstRow = false;
                                    AddQuery = AddQuery.TrimEnd(',');
                                    AddQuery += ")";
                                    AddQuery += " END";
                                }
                                else
                                {
                                    dt.Rows.Add();
                                    int i = 0;
                                    foreach (Cell cell in row.Descendants<Cell>())
                                    {
                                        dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
                                        i++;
                                    }
                                }
                            }
                            using (SqlConnection con1 = new SqlConnection(cs))
                            {
                                string newquery = "";
                                SqlCommand cmd = new SqlCommand(AddQuery);
                                cmd.Connection = con1;
                                con1.Open();
                                cmd.ExecuteNonQuery();
                                cmd.CommandTimeout = 0;
                                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con1);
                                sqlBulkCopy.DestinationTableName = "dbo." + "[" + map.FileName + "]";
                                sqlBulkCopy.BulkCopyTimeout = 0;
                                sqlBulkCopy.BatchSize = 50000;
                                sqlBulkCopy.WriteToServer(dt);                                
                                sqlCommand.Connection = con1;
                                // con1.Open();
                                sqlCommand.ExecuteNonQuery();                                
                                con1.Close();
                            }                           
                        }
                    } 
					
					        private string GetValue(SpreadsheetDocument doc, Cell cell)
        {
            string value = cell.CellValue.InnerText;
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
            }
            return value;
        }

this is code when i browse excel and click save button then it dynamically create table . if in excel file 

     Invoice     Number*  	P /O  S	 I nvoice@  Amount 	#Tax  Amou nt	Doc  Type$
Inv-01	07	1000	100	INV
Inv-02	09	1010	110	invoice
Inv-03	33	1020	120	invioce
Inv-04	45	1030	130	Credit Note
Inv-05	23	1040	140	Revised note
Inv-06	12	1050	150	Debit Note
Inv-06	12	1050	150	Debit Note
Inv-06	12	1050	150	Credit Note

name column have space and special chartect i want exact name

telldurges says:

name column have space and special chartect i want exact name

 There is no name column in the sample data.

Are you asking about Invoice Number column?

telldurges says:

if (row.RowIndex.Value == 1)

                                {

                                    AddQuery += "IF OBJECT_ID('dbo." + map.FileName + "', 'U') IS NULL ";

                                    AddQuery += "BEGIN ";

                                    AddQuery += "CREATE TABLE [dbo].[" + map.FileName + "](";

                                   // AddQuery += "id int identity , ";

                                    foreach (Cell cell in row.Descendants<Cell>())

                                    {

                                        dt.Columns.Add(GetValue(doc, cell));

                                        AddQuery += "[" + GetValue(doc, cell).ToString() + "]" + " VARCHAR(MAX),";

                                    }

                                    //firstRow = false;

                                    AddQuery = AddQuery.TrimEnd(',');

                                    AddQuery += ")";

                                    AddQuery += " END";

                                }

Replace the above code with the below.

if (row.RowIndex.Value == 1)
{
    AddQuery += "IF OBJECT_ID('dbo." + "Test" + "', 'U') IS NULL ";
    AddQuery += "BEGIN ";
    AddQuery += "CREATE TABLE [dbo].[" + "Test" + "](";
    // AddQuery += "id int identity , ";
    foreach (Cell cell in row.Descendants<Cell>())
    {
        dt.Columns.Add(RemoveSpecialCharacters(GetValue(doc, cell)));
        AddQuery += "[" + RemoveSpecialCharacters(GetValue(doc, cell)) + "]" + " VARCHAR(MAX),";
    }
    //firstRow = false;
    AddQuery = AddQuery.TrimEnd(',');
    AddQuery += ")";
    AddQuery += " END";
}

For RemoveSpecialCharacters use the below method.

public string RemoveSpecialCharacters(string text)
{
    return System.Text.RegularExpressions.Regex.Replace(text, @"(\s+|\*|\#|\@|\$)", "");
}

The query will be generated like below.

IF OBJECT_ID('dbo.Test', 'U') IS NULL 
BEGIN 
CREATE TABLE [dbo].[Test]
(
	[InvoiceNumber] VARCHAR(MAX),
	[P/OS] VARCHAR(MAX),
	[InvoiceAmount] VARCHAR(MAX),
	[TaxAmount] VARCHAR(MAX),
	[DocType] VARCHAR(MAX)
) 
END

Check the exmple.

HTML

<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

Namespaces

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using System.Data;

Code

protected void ImportExcel(object sender, EventArgs e)
{
    //Save the uploaded Excel file.
    string filePath = @"C:\Users\Test\Desktop\Test.xlsx";
    string AddQuery = "";
    //Open the Excel file in Read Mode using OpenXml.
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        //Read the first Sheets from Excel file.
        Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();

        //Get the Worksheet instance.
        Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;

        //Fetch all the rows present in the Worksheet.
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();

        //Create a new DataTable.
        DataTable dt = new DataTable();

        //Loop through the Worksheet rows.
        foreach (Row row in rows)
        {
            //Use the first row to add columns to DataTable
            if (row.RowIndex.Value == 1)
            {
                AddQuery += "IF OBJECT_ID('dbo." + "Test" + "', 'U') IS NULL ";
                AddQuery += "BEGIN ";
                AddQuery += "CREATE TABLE [dbo].[" + "Test" + "](";
                // AddQuery += "id int identity , ";
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Columns.Add(RemoveSpecialCharacters(GetValue(doc, cell)));
                    AddQuery += "[" + RemoveSpecialCharacters(GetValue(doc, cell)) + "]" + " VARCHAR(MAX),";
                }
                //firstRow = false;
                AddQuery = AddQuery.TrimEnd(',');
                AddQuery += ")";
                AddQuery += " END";
            }
            else
            {
                //Add rows to DataTable.
                dt.Rows.Add();
                int i = 0;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
                    i++;
                }
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

private string GetValue(SpreadsheetDocument doc, Cell cell)
{
    string value = cell.CellValue.InnerText;
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
    }
    return value;
}

public string RemoveSpecialCharacters(string text)
{
    return System.Text.RegularExpressions.Regex.Replace(text, @"(\s+|\*|\#|\@|\$)", "");
}

Screenshot