Community discussion forum

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

• • Reply

  4 months ago

  by queries.queries@gmail.com

  Queries Queries

  Cambridge, United Kingdom

  Joined 4 months 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();
  
      }
  

  Post was edited on 14/07/2009 16:01:58 Report abuse
• • Reply

  4 months ago

  by queries.queries@gmail.com

  Queries Queries

  Cambridge, United Kingdom

  Joined 4 months ago

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

  Report abuse