Reading Data From Excel and then updating It

Question

My Excel workbook is stored in some location, Basically i'm using Export to excel from a gridview when i Click on a button.

All works fine,

My Question is, " I want to first lookup the file, and if its already present, then update it with the data i got with my gridview, if there are any new data present."

My function just creates a whole lot of new data which overrides the previous one.

Thanks

Updating the data as you are requesting is a process much more costly than overriding the previous file since you will open the file and check the data inside, and compare it to what you have... — Boomer, Dec 10 '12 at 10:12
The reason i asked is, because i already have an excel file created manually, what i need is to get the values from gridview and store it accordingly in the excel fields....thats why... — Sandeep, Dec 10 '12 at 10:28

score 0 · Answer 1 · edited May 23 '17 at 12:21

You can check for the file first as follows

 string filePath = Request.PhysicalApplicationPath + "filename.xls";  
 FileInfo imageFile = new FileInfo(filePath);  
 bool fileExists = imageFile.Exists;  
 Label1.Text = "File exits?: " + fileExists.ToString();

Here is a code for reading and updating the excel file

    var myConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Language_Batch1_OneClick.xls';Extended Properties=Excel 8.0;"); ;
    var myCommand = new OleDbCommand();
    var upCommand = new OleDbCommand();
    int i = 0;
    try
    {

        string sql = null;
        myConnection.Open();
        myCommand.Connection = myConnection;
        sql = "select ANSWER_CODE,Punjabi from [Batch_Lang_1$]";
        myCommand.CommandText = sql;
        var dataReader = myCommand.ExecuteReader();

        while (dataReader.Read())
        {
            var langText = Convert.ToBase64String(Encoding.UTF8.GetBytes(dataReader["Punjabi"].ToString()));
            if (langText.Length >= 1000)
            {
                continue;
            }
            var ansCode = dataReader["ANSWER_CODE"].ToString();
            sql = "update [Batch_Lang_1$] set Punjabi= '" + langText + "'  where ANSWER_CODE='" + ansCode + "'";
            upCommand.Connection = myConnection;
            upCommand.CommandText = sql;
            upCommand.ExecuteNonQuery();
            i++;
        }

Here is a link which has code for reading and updating the excel file.
C# Convert MS Excel column to utf-8 and then to base64

Reading Data From Excel and then updating It

1 Answers1