C# Excel Interop Manipulation

[Mike Askew]

I've been toying with some code for the last thirty mins to try move a substring from one cell to another. In debug I can see the correct contents being picked up and moved to the new cell and even got a save prompt popup when running over line 31.

private void button1_Click(object sender, EventArgs e)
        {
            string FileName = @"D:FooBar.xlsx";

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbooks xlWBs = xlApp.Workbooks;
            Excel.Workbook xlWB = xlWBs.Open(FileName);

            ProcessChanges(xlWB);

            xlWB.Close();
            xlWB = null;
            xlWBs = null;
            xlApp.Quit();
            xlApp = null;

            MessageBox.Show("Done");
        }

        private void ProcessChanges(Excel.Workbook workBook)
        {
            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[2];

            Excel.Range excelRange = sheet.UsedRange;

            object[,] valueArray = (object[,])excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);

            string TextToMove = valueArray[1, 1].ToString().Substring(valueArray[1, 1].ToString().IndexOf("Prerequisites"), valueArray[1, 1].ToString().IndexOf("2."));
            valueArray[6, 3] = string.Concat(valueArray[6, 3].ToString(), "\n\n", TextToMove);
            workBook.Save();
        }

However the document didn't seem to save the actual changes and can't figure out why. It also locks open the file until I kill off excel via task manager so I'm clearly doing something wrong there also.

Any ideas?

[jhoisington]

You are modifying the object valueArray, not the actual values in the workbook. Locking the file until you kill off Excel has been an annoying feature of Excel for years.
jim...

[Mike Askew]

Functioning code for those interested.

        private void button1_Click(object sender, EventArgs e)
        {
            string FileName = @"D:\FooBar.xlsx";

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbooks xlWBs = xlApp.Workbooks;
            Excel.Workbook xlWB = xlWBs.Open(FileName);

            ProcessChanges(xlWB);

            Marshal.ReleaseComObject(xlWB);
            Marshal.ReleaseComObject(xlWBs);
            Marshal.ReleaseComObject(xlApp);

            var processes = from p in Process.GetProcessesByName("EXCEL")
                            select p;

            foreach (var process in processes)
            {
                    process.Kill();
            }

            MessageBox.Show("Done");
        }

        private void ProcessChanges(Excel.Workbook workBook)
        {

            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[2];

            Excel.Range excelRange = sheet.UsedRange;

            object[,] valueArray = (object[,])excelRange.Value2;

            string TextToMove = valueArray[1, 1].ToString().Substring(valueArray[1, 1].ToString().IndexOf("Prerequisites"), valueArray[1, 1].ToString().IndexOf("2."));

            excelRange.Cells.set_Item(6, 3, string.Concat(valueArray[6, 3].ToString(), "\n\n", TextToMove));

            workBook.Save();
        }