Adjust formulas to column moving

Hello,
Please advise me how to solve my task.
I have the XLS file which contains some data and some cells with formulas. Then I open this file in my .NET application. I want to swap two columns with cells which are used in formula in other cell.
Then I need to save result to anoter file. And formula must contain actual cell addresses in this file.

For example,
the cell with address “A1” contains 100 number;
the cell with address “B1” contains 150 number;
the cell with address “C1” contains some string value (for example, “abc”)
the cell with address “D1” contains formula with sum of A1 and B1 (“=A1+B1”), so this value is equal to 250.

I open this file in my application using Aspose.Cells and then I want to swap B and C columns in code (using Aspose.Cells tools). Then I need to save result to new file. And formula in “D1” must adjust to these changes.
I want the following result in new saved file:
the cell with address “A1” contains 100 number;
the cell with address “B1” contains “abc”;
the cell with address “C1” contains 150 number;
the cell with address “D1” contains formula with sum of A1 and C1 (“=A1+C1”), so this value is equal to 250.

Would you please help me? Is there any way to solve this task using Aspose.Cells methods in .NET application? Thank you.

@MikeKokh,

I have reviewed your requirements. I believe you can use the Cells.InsertCutCells() method for this purpose. Please refer to the sample code below to achieve the task. I have also included the input and output files in the zipped archive at the bottom.
e.g.,
Sample code:

Workbook workbook = new Workbook("e:\\test2\\Bk_swapcells1.xlsx");
Worksheet workSheet = workbook.Worksheets[0];
workSheet.Cells.InsertCutCells(workSheet.Cells.CreateRange("C1"), 0, 1, ShiftType.Right);
workbook.CalculateFormula();
workbook.Save("e:\\test2\\out1.xlsx");

files1.zip (13.1 KB)

Hope this helps a bit.

Thank you very much! I will try to use your solution :slight_smile:

@MikeKokh,

Sure, please take your time to try the suggested solution. Hopefully, it will work for the purpose.

Please help me - is there any way to use trial version? I have installed Aspose.Cells package from nuget and now it does not work - I see the following message in the saved XLS file. Thank you!
warning.png (3,1 КБ)

@MikeKokh,

There are certain limitations when using the trial version (using Aspose.Cells without a valid license), see the document for your reference.
https://docs.aspose.com/cells/net/evaluation-version-limitations/

If you want to test Aspose.Cells without evaluation version limitations, you may request a 30-day temporary license. Please refer to How to get a Temporary License? for more information.

1 Like