Adjusting cell formulas

[Ryan]

Is there any way to alter cell formulas when I read a formula from 1 sheet and insert that formula into a different row # of a new sheet?

I'm reading data from several different worksheets and placing them into a new worksheet I've created using the following line of code:
--------------------------------------
dataTable = sheet.ExportDataTable(1, 1, sheet.UsedRange.End.Row, sheet.UsedRange.End.Column, ExcelExportDataTableOptions.ColumnNames);
--------------------------------------

The problem I'm having however is one of the cells in these rows contains a formula which looks for a value from another cell in the same row. The formula looks like the following:
=($A$1-F4)*12-(F4-$A$1)

When I insert this row into a new worksheet however, the data is not inserted into the same row #'s however the formula doesn't change like it should. So, for instance, the above formula is for row #4 however I may insert it into row #430 of the new sheet. I would need to change the formula to the following:

=($A$1-F430)*12-(F430-$A$1)

Any idea how I can change these formulas?

Thanks.

-Ryan

[Melba Winshia]

Hi Ryan,

I was not able to reproduce the issue. I created a sample to copy a row that contains a formula, which looks for a value from another cell in the same row, and insert that row in to a new worksheet in to row #430. But the formula is changed from =($A$1-F4)*12-(F4-$A$1) to =($A$1-F424)*12-(F424-$A$1). Here is the test sample that I used to test this issue:

http://websamples.syncfusion.com/samples/XlsIO.Windows/F61020/main.htm

Would you be so kind to look up the above sample and let me know if you still find any problem, if not could you please modify this sample so that I can reproduce the issue? This would help me in investigating this issue further.

Thanks,
Melba

[Ryan]

Hi Melba.

The problem however is I'm not using the "sheet.Range.EntireRow.CopyTo" method. Instead, I'm using "sheet.ExportDataTable" to copy ALL rows from one sheet and paste them to another and for some reason these formula row #'s are not adjusting. I've attached a file to help explain what I'm doing.

So basically, this file reads data from the 1st worksheet using sheet.ExportDataTable and then pastes all rows from this first worksheet into a new sheet. It then reads data from worksheet #2 and appends this data to the new sheet as well.

Thanks again.

-ryan

ExcelFormulas.zip

[Melba Winshia]

Hi Ryan,

Thanks for the update.

I was able to reproduce the problem with ExportDataTable. However, you can avoid this by using CopyTo method. But by using ExportDataTable method it is not possible to get the formula adjusted. Please use the following code snippet to achieve this:

[C#]

sheet1.UsedRange.CopyTo(sheet2.Range[1,1]);

Here is the sample for your reference:

http://websamples.syncfusion.com/samples/XlsIO.Web/4.4.0.51/F61020/main.htm

Kindly let me know if you have any other questions.

Thanks,
Melba

[Ryan]

Hi Melba.

I tried implementing your code however I keep receiving an error on my "sheet.usedrange.copyto" line that reads:
"System.NotImplementedException: The method or operation is not implemented."

Here's the code I'm using:
-----------------------------------------
Object[,] data;
Object[,] headerData;

IWorksheet sheet1 = workbook.Worksheets[2];
IWorksheet sheet2 = workbook.Worksheets[3];
IWorksheet sheet3 = workbook.Worksheets[4];
IWorksheet sheet4 = workbook.Worksheets[5];
IWorksheet sheet5 = workbook.Worksheets[6];
IWorksheet sheet6 = workbook.Worksheets[7];
IWorksheet newSheet = workbook.Worksheets[8];

currentColumn = 1;

//1st sheet
IRange range = sheet1.UsedRange;

// Formatting
range = sheet1.Range[currentRow, currentColumn];
range.CellStyle.Font.Size = 8;
range.CellStyle.Font.FontName = "Verdana";
range.RowHeight = 14.25;
range.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
range.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
range.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
range.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;

sheet1.UsedRange.CopyTo(newSheet.Range[1, 1]);
----------------------------------------------

As I said, it's failing on the final line of code. Any idea what I'm doing wrong?

Thanks again.

-ryan

[Melba Winshia]

Hi Ryan,

Thanks for the update.

I created a sample with your code snippet and I was not able to reproduce the issue. Here is the sample that I used to test this issue:

http://websamples.syncfusion.com/samples/XlsIO.Web/4.4.0.51/F61020_1/main.htm

Could you please take a look at the sample above and kindly let me know if you still find any problem, if not could you please modify this sample so that I can reproduce the issue? This would help me in investigating this issue further. Please let me know if I am missing something.

Thanks,
Melba

[Ryan]

Hi Melba,

Ok, I've isolated the incident to 2 formula based columns, which are nearly identical.

In the test file, there are 2 columns: Q & V. These 2 columns contain a formula that reads:
=IF(P1<=3,"Y","N")

When I try to copy these 2 columns using the "CopyTo" method, my application crashes. However, if I switch the string data to numeric, it works fine:
=IF(P1<=3,0,1)

So the error seems to involve an issue with reading string data in a formula.

Here's the code I'm using:
-----------------------------------------------
sheet1 = workbook.Worksheets[2];
sheet2 = workbook.Worksheets[3];
sheet3 = workbook.Worksheets[4];
sheet4 = workbook.Worksheets[5];
sheet5 = workbook.Worksheets[6];
sheet6 = workbook.Worksheets[7];
newSheet = workbook.Worksheets[8];

//1st sheet
sheet1.Range[1,1,1,22].CopyTo(newSheet.Range[1, 1]);

workbook.ActiveSheetIndex = 8;
----------------------------------------------
So, using this code, the application will crash on columns 17 & 22 (columns Q & V). If you set the range to "[1, 1, 1, 16]", it works fine.

Any idea how I can work around this?

Thanks again.

-Ryan




TestFile.zip

[Melba Winshia]

Hi Ryan,

Could you please create a Direct-Trac incident with the subject as " Adjusting cell formulas_61020"? So that it would be possible for us to provide more details regarding this issue.

Thanks,
Melba