how to read spread sheet and add full stop in the text before inserting into Database using C# in ASP.Net

Hi kavithav,

I am working on it. I will get back to you once done.

can anyone please help me out as it is urgent requirement and i am unable to get it as i am new to this

Hi Kavitha,

I have created a sample which full requirement

HTML

<div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label>
    <asp:RadioButtonList ID="rbHDR" runat="server">
        <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" ></asp:ListItem>
        <asp:ListItem Text = "No" Value = "No"></asp:ListItem>
    </asp:RadioButtonList>       
</div>

C#

protected void btnUpload_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        //****************Get DataTable from you code Start********************

        string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string filePath = Server.MapPath("~/Files/" + fileName);
        FileUpload1.SaveAs(filePath);
        string conStr = "";
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, filePath, rbHDR.SelectedItem.Text.Trim());
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();

        //****************Get DataTable from you code End********************

        //Filtered Data with Header as 'Add' if you return dataset then uncomment the below line and comment the nextline.
        //DataTable selectedData = dset.Tables[0].Select("Header = 'Add'").CopyToDataTable();
        DataTable selectedData = dt.Select("Header = 'Add'").CopyToDataTable();
        foreach (DataRow dr in selectedData.Rows)
        {
            string header = dr[0].ToString().Trim();
            string code = dr[1].ToString().Trim();
            string description = dr[2].ToString().Trim();
            // if code not exist like "G432J8K"
            if (!HasData(code))
            {
                string codeWithFullStop = code.Insert(1, ".").Insert(6, ".");
                // if code not exist like "G.432J.8K"
                if (!HasData(codeWithFullStop))
                {
                    // insert row with code value as "G43.2J8K"
                    Insert(header, code.Insert(3, "."), description);
                }
            }
        }
    }
}

// Check Record already exist or not.
private bool HasData(string code)
{
    string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    string query = "SELECT * FROM ExcelTable WHERE code='" + code + "'";
    SqlCommand cmd = new SqlCommand(query, con);
    con.Open();
    object sdr = cmd.ExecuteScalar();
    con.Close();
    if (sdr != null)
    {
        return true;
    }
    else
    {
        return false;
    }
}

//If not present in database then Insert row.
private void Insert(string header, string code, string description)
{
    string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    string query = "INSERT INTO ExcelTable(Header,Code,Description)VALUES(@header,@code,@description)";
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand(query, con);
    con.Open();
    cmd.Parameters.AddWithValue("@header", header);
    cmd.Parameters.AddWithValue("@code", code);
    cmd.Parameters.AddWithValue("@description", description);
    cmd.ExecuteNonQuery();
    con.Close();
}

Hope this works for you