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

Last Reply on Sep 30, 2016 03:27 AM By AnandM

Posted on Sep 29, 2016 01:41 AM

Below is the data that is in open office spread sheet

***Header       Code          Description*** Excel sheet columns

Add                 A7694            Some Text under Desription

Remove From   A893421          Some Text under Desription

Move To           M736RV         Some Text under Desription

Delete              V43221J         Some Text under Desription

Add                 G432J8K         Some Text under Desription

Add                  HY8765G       Some Text under Desription

Add                  B7654           Some Text under Desription

Remove From     K897655        Some Text under Desription

Delete              RT998766      Some Text under Desription

Like this i have 9000 rows in excel sheet and 3 columns now what i need is i need to insert the rows which contains Add under Header column,next i need to do the insertion by checking that the code for that particular Add row is not there in database that is directly the code should be checked whether it is there ind database or not and after that i should add a full stop after alphabet and check whether it is there ind database or not,next finally if that code is not there in database then i need to add a full stop for code after 3 digits and then insert into database .Can any one help me out as i am new  to this

C# Code-

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
        // Copy file to app folder
        string fileName = SaveToAppFolder(FileUpload1.PostedFile);

        // Read the File from App folder
        OdsReaderWriter obj = new OdsReaderWriter();
        dset = obj.ReadOdsFile(fileName);
        GridView1.DataSource = dset.Tables[0];
        //DataTable dt = dset.Tables[0];
        GridView1.DataBind();
        Session["DTset"] = dset;
    }
    catch (Exception ex)
    {
        UploadStatusLabel.Text = "Only .ODS Files Are Allowed";
    }
    GridView1.Visible = true;
}
string SaveToAppFolder(HttpPostedFile file)
{

    string savePath = Server.MapPath(".") + "\\TempFiles\\";
    string fileName = FileUpload1.FileName;
    string pathToCheck = savePath + fileName;
    string tempfileName = "";
    if (System.IO.File.Exists(pathToCheck))
    {
        int counter = 2;
        while (System.IO.File.Exists(pathToCheck))
        {

            tempfileName = counter.ToString() + fileName;
            pathToCheck = savePath + tempfileName;
            counter++;
        }

        fileName = tempfileName;
    }
    else
    {

    }
    savePath += fileName;
    FileUpload1.SaveAs(savePath);
    return savePath;

}

Aspx code- 

<asp:FileUpload ID="FileUpload1" runat="server"/>
<asp:Button ID="Button1" runat="server" Text="ReadFile" OnClick="Button1_Click" />

This is my code to read the spread sheet data how should i perform these three operations before inserting the data in data base they are 1)only the  rows Add should be considered for insertion remaining should be excluded2)Check the corresponding Add row Code is already there in database or not,this checking should be done by directly considering the code and by adding full stop after alphabets.3)Finally if that code is not there in database then a full stop should be added after three digits for code and then it should be inserted into data base. For example- 

Header Code Description   
Move To M736RV Some Text under Desription
Delete V43221J Some Text under Desription
Add G432J8 Some Text under Desription
Add HY8765G Some Text under Desription

***only Add should be inserted---->First stepG432J8K check this code is there in databaase or not after this in this same step add full stop after alphabets and check it is there in data base or not in this way G.432J.8----->second stepfinally add full stop after 3 digits and then insert into database that is G43.2J8*** 

 


Posted on Sep 29, 2016 06:14 AM

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


Posted on Sep 30, 2016 03:27 AM

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