Unable to write to excel file using c# .net 3.5

excel , dataset , datareader , write , read Cambridge, United Kingdom
  • 11 years ago

    I have one excellsheet with one workspace (Sheet1) with two columns : 1) FileName 2) FileCode

    This file has 120 records (rows).

    I want to read from FileName column, append ".bak1" to it and save the result in Code column

    e.g:

    FileName : TestFile.txt FileCode : TestFile.cs.bak1

    The Code that I have written compiles with zero errors but does not updates the FileCode column values. Please suggest the best way of manipulating records in excel file. What is missing from my code?

        /// <summary>
        /// Read from Excel Sheet
        /// </summary>
        private void UsingDataSet()
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Sales.xls;Extended Properties=Excel 8.0");
            con.Open();
            OleDbDataAdapter objAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);
            DataSet objDataSet = new DataSet();
            string strFileName;
            int intCntr=0;
    
            objAdapter.Fill(objDataSet, "MyTableName");
    
            foreach (DataRow pRow in objDataSet.Tables["MyTableName"].Rows)
            {
                strFileName = pRow["FileName"].ToString();
    
                pRow["FileCode"] = strFileName + ".bak1";
                objDataSet.Tables["MyTableName"].Rows[intCntr++]["FileCode"] = pRow["FileCode", DataRowVersion.Current];
                objDataSet.AcceptChanges();
            }
            con.Close();
    
        }
    
  • 11 years ago

    Please suggest what is wrong with the code and how it can be optimised so that changes are reflected back in excel file.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Never trust a programmer in a suit.” - Anonymous