We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Adjusting cell formulas

Thread ID:

Created:

Updated:

Platform:

Replies:

61020 May 16,2007 08:05 PM UTC May 23,2007 12:18 PM UTC ASP.NET Web Forms (Classic) 7
loading
Tags: XlsIO
Ryan
Asked On May 16, 2007 08:05 PM UTC

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 [Syncfusion]
Replied On May 17, 2007 11:15 AM UTC

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
Replied On May 17, 2007 06:46 PM UTC

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 [Syncfusion]
Replied On May 18, 2007 11:04 AM UTC

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
Replied On May 21, 2007 06:57 PM UTC

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 [Syncfusion]
Replied On May 22, 2007 10:06 AM UTC

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
Replied On May 22, 2007 03:56 PM UTC

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 [Syncfusion]
Replied On May 23, 2007 12:18 PM UTC

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

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;