Friends, I need to insert the values from Excel to SQL using C#. But in Excel, all the values are ...

Friends,

I need to insert the values from Excel to SQL using C#.

But in Excel, all the values are dynamic and in various cells.

Clients (More than 1000 Clients) will fill the excel (Already format created) and they need to upload.

Please find the picture below....

The data (highlighted) must transfer to SQL.

I had tried many ways. But still problem.

Please help me.

Thanks in advance.

Regards, Guna

Friend,

Please find my code below.....[One of my friend suggest that].

namespace TestInsertSpecifiedCells
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\00-Excel\\TestData.xlsx;Extended Properties='Excel 12.0 Xml;HDR=No';";
            OleDbConnection Con = new System.Data.OleDb.OleDbConnection(sql);
            OleDbCommand cmd = new OleDbCommand();
            Con.Open();
            cmd.Connection = Con;
            cmd.CommandText = "Update [Sheet1$A1:B1] Set F2='8545'";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "Update [Sheet1$A5:G5] Set F1='18000',F2='5',F3='7800',F4='0',F5='0',F6='18000',F7='18000'";
            cmd.ExecuteNonQuery();
            Con.Close();
        }
    }
}

But....I don't know F1,F2,F3,F4,F5 values. Because all values are dynamic. Many clients submit their data simultaneously. So that values should be dynamically.

I shall be happy if you could help me to change the above code.

Regards,

Guna

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guna14y ago
Friend,

Please find my code below.....[One of my friend suggest that].

namespace TestInsertSpecifiedCells
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\00-Excel\\TestData.xlsx;Extended Properties='Excel 12.0 Xml;HDR=No';";
            OleDbConnection Con = new System.Data.OleDb.OleDbConnection(sql);
            OleDbCommand cmd = new OleDbCommand();
            Con.Open();
            cmd.Connection = Con;
            cmd.CommandText = "Update [Sheet1$A1:B1] Set F2='8545'";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "Update [Sheet1$A5:G5] Set F1='18000',F2='5',F3='7800',F4='0',F5='0',F6='18000',F7='18000'";
            cmd.ExecuteNonQuery();
            Con.Close();
        }
    }
}

But....I don't know F1,F2,F3,F4,F5 values. Because all values are dynamic. Many clients submit their data simultaneously. So that values should be dynamically.

I shall be happy if you could help me to change the above code.

Regards,

Guna
Andile Choko14y ago
Show us your code,so that we could help you.

Kind Regards
Andile