title: Submitting changes to excel file using Excel Interop in C#
body: <p>I am still stuck in this code and bugs keep showing up..
  I have excel file that is almost 24 columns, I am trying to adapt it to a 7 column excel file because the software I have works only on 7 columns and I don't want to re write the software from the beginning, so you will see some deletion and isertion of columns..</p>
  
  <p>I have 5 columns inside this excel file that have either "x" value or null.</p>
  
  <p>what I am trying to do is to create a new column between range A1,and B1 that is called category, so if there is an x in column 5, i write E in the category field, else if the x is in the 6th column then I write P in the category column.. and so on.
  And then I need to delete these 5 columns that I no longer need (Range E1:I1)</p>
  
  <p>The problem is when I debug the code, I can see that the values[,] have the column inserted, and the values has been transfered correctly, but when the temp_data.csv is produced, it has the new excel file after deletion so now it contains 11 columns, but the new Category column together with the values are not there...</p>
  
  <pre><code>                Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(p_sUBKPath, Type.Missing, false, 4, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                  Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
                  Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;
  
                  // delete columns that we don't need from the new excel file
                  Microsoft.Office.Interop.Excel.Range range2 = ws.get_Range("A1","A1"); 
                  range2.EntireColumn.Delete();
                  Microsoft.Office.Interop.Excel.Range range3 = ws.get_Range("B1", "B1");
                  range3.EntireColumn.Delete();
                  Microsoft.Office.Interop.Excel.Range range4 = ws.get_Range("D1", "L1");
                  range4.EntireColumn.Delete();
                  Microsoft.Office.Interop.Excel.Range range5 = ws.get_Range("I1", "M1");
                  range5.EntireColumn.Delete();
                  Microsoft.Office.Interop.Excel.Range range6 = ws.get_Range("K1", "K1");
                  range6.EntireColumn.Delete();
  
                  //insert a new column ( Category)
                  Microsoft.Office.Interop.Excel.Range range7 = ws.get_Range("B1", "B1");
                  range7.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight);
  
  
                  object[,] tempVal = (object[,])range.Value2;
                  tempVal[1, 2] = (object)"Category";
  
                  for (int row = 2; row &lt;= tempVal.GetUpperBound(0); row++)
                  {
  
                      try
                      {
  
                          if ((!String.IsNullOrEmpty((string)tempVal[row, 5])) &amp;&amp; (string)tempVal[row, 5] == "x")
                          {
                              tempVal[row, 2] = (string)"E";
                          }
                          else if ((!String.IsNullOrEmpty((string)tempVal[row, 6])) &amp;&amp; (string)tempVal[row, 6] == "x")
                          {
                              tempVal[row, 2] = (string)"P";
  
  
                          }
                          else if ((!String.IsNullOrEmpty((string)tempVal[row, 7])) &amp;&amp; (string)tempVal[row, 7] == "x")
                          {
                              tempVal[row, 2] = (string)"Phy";
  
  
                          }
                          else if ((!String.IsNullOrEmpty((string)tempVal[row, 8])) &amp;&amp; (string)tempVal[row, 8] == "x")
                          {
                              tempVal[row, 2] = (string)"L";
  
  
                          }
                          else if ((!String.IsNullOrEmpty(tempVal[row, 9].ToString())) &amp;&amp; (string)tempVal[row, 9] == "x")
                          {
                              tempVal[row, 2] = (string)"Ex";
  
  
                          }
                          else
                              MessageBox.Show("unknow");
                      }
                      catch (Exception ex)
                      {
                          MessageBox.Show(ex.ToString());
                      }
  
  
                  }
  
  
                  object[,] values = tempVal;
  if (Convert.ToString(values[1, 1]).ToUpper().Trim() == "SHORT NAME" &amp;&amp; Convert.ToString(values[1, 2]).ToUpper().Trim() == "CATEGORY" &amp;&amp; Convert.ToString(values[1, 3]).ToUpper().Trim() == "LONG NAME EN" &amp;&amp; Convert.ToString(values[1, 4]).ToUpper().Trim() == "LONG NAME DE" &amp;&amp; Convert.ToString(values[1, 5]).ToUpper().Trim() == "ELEMENT" &amp;&amp; Convert.ToString(values[1, 6]).ToUpper().Trim() == "PROPERNAME" &amp;&amp; Convert.ToString(values[1, 7]).ToUpper().Trim() == "PHYSICAL" &amp;&amp; Convert.ToString(values[1, 8]).ToUpper().Trim() == "LOGICAL" &amp;&amp; Convert.ToString(values[1, 9]).ToUpper().Trim() == "EXTENSION" &amp;&amp; Convert.ToString(values[1, 10]).ToUpper().Trim() == "CREATED BY" &amp;&amp; Convert.ToString(values[1, 11]).ToUpper().Trim() == "CREATED ON" &amp;&amp; Convert.ToString(values[1, 12]).ToUpper().Trim() == "STATE")
  
                  {
                      for (int row = 1; row &lt;= values.GetUpperBound(0); row++)
                          for (int col = 1; col &lt;= values.GetUpperBound(1); col++)
                          {
                              string value = Convert.ToString(values[row, col]);
  
                              if (value.Contains(","))
                              {
                                  range.Cells.set_Item(row, col, value.Replace(",", p_sPsuedostring));
                              }
                              if (value.Contains("    "))
                              {
                                  range.Cells.set_Item(row, col, value.Replace("  ", p_sPsuedostring + "    " + p_sPsuedostring));
                              }
                          }
                      if (File.Exists(System.Windows.Forms.Application.StartupPath + @"\Data_Temp.csv")) 
                          File.Delete(System.Windows.Forms.Application.StartupPath + @"\Data_Temp.csv");
  
                     //Save the Latest databse as Data_Temp.csv
                      ws.SaveAs(System.Windows.Forms.Application.StartupPath + @"\Data_Temp.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlUnicodeText, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                      xl.DisplayAlerts = true;
                      try
                      {
                          xl.Workbooks[1].Close(false, Type.Missing, Type.Missing);
                      }
                      catch(Exception ex)
                      {
                          MessageBox.Show(ex.ToString());
                      }
                      xl.Application.Quit();
                      xl.Quit();
                      l_bClosedSuccessfully = true;
  </code></pre>