I am trying to assign a 2 dimensional object array to the value of an Excel interop Range.Value

I pull the 2 object dimensional array directly from the Range.Value of the UsedRange then do some changes to the data and put it back in as a 2 dimensional object[,].

When I save the workbook either as a copy or the original the new values I assign to the Range do not persist.

Here is my code:

            /// <summary>
            /// Writes the data from this excel document to <see cref="ExcelDoc.FilePath"/>
            /// </summary>
            /// <param name="newFilePath">used to specify a new file path</param>
            /// <remarks>This will overwrite any data in the file</remarks>
            public void WriteDataToExcelInterop(string newFilePath = "")
            {
                Excel.Application exApp = new();
                GetWindowThreadProcessId(exApp.Hwnd, out int excelProcessId);
                Process excelProcess = Process.GetProcessById(excelProcessId);
                try
                {
                    exApp.DisplayAlerts = false;
                    exApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;

                    Excel.Workbook workbook = exApp.Workbooks.Open(FilePath);
                    for (int i = 1; i <= workbook.Worksheets.Count; i  )
                    {
                        Excel.Worksheet worksheet = workbook.Worksheets[i];
                        string name = worksheet.Name;
                        Excel.Range range = worksheet.UsedRange;
                        for (int j = 0; j < Worksheets.Count; j  )
                        {
                            if (Worksheets[j].Name == name)
                            {
                                range.Value = Worksheets[j].DataObjects; //this does not work
                                range.Formula = Worksheets[j].FormulaObjects; //this does work
                            } 
                        }
                    }
                    if (newFilePath == "")
                    {
                        workbook.Close(true, FilePath);
                        exApp.Quit();
                        excelProcess.Kill();
                    }
                    else
                    {
                        workbook.SaveCopyAs(newFilePath);
                        workbook.Close(false, FilePath);
                        exApp.Quit();
                        excelProcess.Kill();
                    }
                }
                catch (Exception ex)
                {
                    exApp.Quit();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    excelProcess.Kill();
                    ExceptionHandling.ExceptionData exData = new(ex);
                    MessageBox.Show(exData.FormatedMessage);
                }
            }

I thought maybe it had something to do with the fact that the UsedRange is ReadOnly according to documentation but the fact that I can change the formulas just fine says otherwise.

So I thought maybe it had to do with the datatype going into the variable so I tried changing the datatype of DataObjects but that did also not work.

I also thought maybe the line exApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable; was causing an issue with saving since this particular excel file is macro enabled. I commented out the line but this did nothing.

I'm thinking maybe the issue is Range.Value is not changing after save because some flag somewhere has not been raised properly.

I could really use a fresh set of eyes on this to see if I'm missing something.

CodePudding user response：

I found what the issue was.

I assumed wrongly that Range.Value and Range.Formula were considered separate but when I make changes to Range.Formula it overwrites any changes I made to Range.Value since I am modifying Value BEFORE Formula. I will now have to change my code a little to account for this but my code was working as intended I just didn't realize why I was getting the results I was. Hopefully this can be helpful to anyone confused by this interaction in the future.