Export Formula from a Range

Aspose.Cells Product Family
1

Dear Sir,

Currently we are in a process of converting Interop.Excel with Aspose.cells. We have a requirement to export the formulas & values in a range to an array. Do we have any methods to do this in aspose.cells. I see we have exporttodatatable for values ina range but no methods to export formulas

The corresponding Interop code is attaching here

Dim shtTSRangeArray(shtTSRange.RowCount, 1) As Object
Dim shtTSRangeFormulaArray(shtTSRange.RowCount, 1) As Object

shtTSRangeArray = shtTSRange.Value2
shtTSRangeFormulaArray = shtTSRange.Formula

''Do Some manipulation in the array and finally write back the array to the same range

shtTSRange.Value2 = shtTSRangeArray
shtTSRange.Formula = shtTSRangeFormulaArray

2

Hi,


Well, you may try to use Transpose function of Ms Excel. For example, you may define your source range, now you may create your destination range accordingly, copy the formulas from the source to paste into the destination range

See the code segment below:

Sample code:

Cells cells = workbook.Worksheets[0].Cells;
Range range = cells.CreateRange(“A10:A20”);
Range dRange = cells.CreateRange(“Y10:Y20”);
PasteOptions options = new PasteOptions();
options.PasteType = PasteType.Formulas; // You may choose other options accordingly as well.
options.Transpose = true;
dRange.Copy(range, options);

Also, if you need to get the formulas into array, then iterate through the range of cells and use cell.Formula attribute to import the formula to fill into an array e.g

string [,] arr = new string[10,1] ;
for (int row=0;row<11;row++)
{
for (int col =0;col< 1; col++ )
{
//Fill the array
arr[row,col]= worksheet.Cells[row, col].Formula;
}
}


Hope, this helps.

Thank you.
3

Thanks for the reply.

Asmentioned if I loop thru a range and get the formulas for each cell by cell to an array, will it affect the performance?. The range in our scenarios will span say more than 1000 rows and 48 columns

4

Hi,

Thanks for your posting and using Aspose.Cells.

Well, I think, it will not affect the performance. Because Aspose.Cells will return references of cells only and will not create new objects.

Let us know if you face any issue. We will be glad to assist you further.

5
Anishc:

Thanks for the reply.

Asmentioned if I loop thru a range and get the formulas for each cell by cell to an array, will it affect the performance?. The range in our scenarios will span say more than 1000 rows and 48 columns

Hi,

We have further looked into your issue and got following additional advice for you to tune up your code.

If there are many empty cells in the range, please change your code like this:

arr[row,col]= worksheet.Cells[row, col].Formula;

to

Cell cell = worksheet.Cells.CheckCell(row, col);
if(cell != null){
arr[row,col]= cell.Formula;//or cell.Value
}

This way, you will get better performance because it will avoid to create many empty cell objects unnecessarily.

6

Thanks for the reply. one more question is it possible to assign the formulas in an array back to the range at once like below

shtTSRange.Formula = shtTSRangeFormulaArray ' this is the array which holds the formulas

7

Hi,


I am afraid, there is no such feature available to specify cells formulas (in the range) to be set to an array in single step, although if you need to set a common value to all the cells in the range, you may use Range.Value property. We may look into it later if we can support it.

I think you may iterate though the range cells and assign the formulas accordingly from your array by using Range[row,col].Formula attribute.

We also recommend you to kindly see the topic for your reference about Named Ranges:
http://www.aspose.com/docs/display/cellsnet/Named+Ranges

8

Hi,

I am trying to copy the formula as suggested by you (on 11-15-2012) above but the result is not proper. I suppose the issue is because of the transpose key word. Can you please suggest why we need to use the transpose while copying formula's from a range.

options.PasteType = PasteType.Formulas;
options.Transpose = true;

Thanks
Anish
9

Hi,



Please download and try this latest fix/version: Aspose.Cells for .NET (Latest Version)

Transpose function is same as MS Excel while copy data/values or formulas from one range to other. You may try confirming it via copying a range and using Paste Special. Also, you may skip setting Transpose to true and simply copy the range accordingly. If you still find the issue, kindly give us your sample code and template file, we will check your issue soon.


Thank you.

10

Hi,

Thanks. Currently I am using 7.3.3.0. Can you please let me know what is new in the 7.3.3.4 version. Does my current license can be used for this. Also please see below this is what I am trying to acheive. CopyRange is my source and DestRange is my destination. will this work. Requirement is to copy formulas and formats.

?CopyRange.RefersTo "=Sheet1!$C$15:$C$194"
?DestRange.RefersTo "=Sheet1!$D$15:$S$194"

11

Hi,


Well, your license should work fine with the latest fix.
For your information, when you purchase a License Subscription, then you are entitled to use or upgrade your application with newer releases for a period of next 1-year starting from the Date of your Purchase.
The license will never expire if you use a version of the product that should be released before your subscription expiry date (you may open your license file into notepad and check the license expiry date). If you want to use a version that is released after your subscription expiry date, then you have to upgrade your subscription.

Also, your source range and destination range is not of same size, please correct / fix it.

Thank you.
12

Hi ,

Thanks. My ranges mentioned are correct only. Requirement is to copy the formulas from 1 source column (partial areas in th column $C$15:$C$193) to multiple columns

Anish

13

Hi,

As I mentioned, Aspose.Cells follows MS Excel standards, so you might get “=#REF!”, it not it? This is due to fact that when you copy a range having formulas to other location/cells, Ms Excel will try to copy the formulas accordingly into destination cells in accordance with the source range which might result in “=#REF!” error in the cells.

Sample code:

Workbook workbook = new Workbook(“e:\test2\Bk_exportformulas.xlsx”);

Cells cells = workbook.Worksheets[0].Cells;

Range range = cells.CreateRange(“C15:C193”);

Range dRange = cells.CreateRange(“D15:S193”);

PasteOptions options = new PasteOptions();

options.PasteType = PasteType.Formulas;

dRange.Copy(range, options);

//dRange.Copy(range);

workbook.Save(“e:\test2\outBk_exportFormulas.xls”);

Please see the attached Excel file in which I get the “=#REF!” error when manually performing the steps i.e. copying range i.e. C15:C193 to other ranges / cells e.g D15:S193 etc.

By the way if you do not use PasteOptions and simply copy the source range to destination, it might work for your needs, see the sample code below and find the attached output file.

Sample code:

Workbook workbook = new Workbook(“e:\test2\Bk_exportformulas.xlsx”);

Cells cells = workbook.Worksheets[0].Cells;

Range range = cells.CreateRange(“C15:C193”);

Range dRange = cells.CreateRange(“D15:D193”);

dRange.Copy(range);

workbook.Save(“e:\test2\outBk_exportFormulas_new.xlsx”);

If you still have any confusion, kindly update my attached file for your needs and provide steps on how can you do this in MS Excel. We will provide APIs/codes on how to do it by Aspose.Cells.

Thank you.

14

Hi,

I was able to progress a little bit but I am still not able the achieve the functionality to copy the formulas to multiple columns. Request you to please help. I am attaching the code below . Also attaching the sample sheet which we are trying.

The formula is getting copied but the problem is its not relative. All columns has the same formula as of the firstcolumn.

Requirement is to copy is to copy formulas & Formats from "C15:C194" and paste it to multiple columnd from Column D15 to U194. I am looping the destination columns and trying to achieve this.

Private Sub CopyFormulaFormatsToColumns()

Try

Dim workbook As New Workbook("D:\AsposeTest\AsposeSample\AsposeSample\ExcelSource\RPT1.xls")

Dim DataSheet As Aspose.Cells.Worksheet

DataSheet = workbook.Worksheets(0)

Dim CopyRange As Aspose.Cells.Range

Dim DestRange As Aspose.Cells.Range

Dim DestRangeStr As String

Dim stCol As Int32 = 3

Dim endCol As Int32 = 20

For index As Integer = stCol To endCol

DestRangeStr = CellsHelper.CellIndexToName(14, index) & ":" & CellsHelper.CellIndexToName(193, index)

Dim opt As New PasteOptions

opt.PasteType = PasteType.FormulasAndNumberFormats

CopyRange = DataSheet.Cells.CreateRange("C15:C194")

DestRange = DataSheet.Cells.CreateRange(DestRangeStr)

DestRange.Copy(CopyRange, opt)

Next

workbook.Save("D:\AsposeTest\AsposeSample\AsposeSample\ExcelSource\RPT2_new.xls")

Catch ex As Exception

Throw

End Try

15

Hi,


Thanks for the sample file and sample code.

I can notice the behavior as you mentioned. The feature that provides Copy Formulas/Number formats from the range to destination range (via Paste Special feature) is not working the same way as MS Excel.

I think you are right as I tested. The formulas should be copied accordingly instead of copying a constant formula to the cells/in the column(s). Also, there should be flexibility to copy a source range (with its formulas & formats etc.) to all its destination range cells if the number of rows are equal to the source range (even if the destination range is bigger than source (regarding columns)) while using Paste Special feature of MS Excel.

We need to look into it if we can enhance this feature (Paste Special) accordingly.

I have logged a ticket with an id: “CELLSNET-41206” for your case. We will look into it and get back to you soon.

Thank you.
16

Hi Team,

Thanks for the update. Please note that we are in a real bad situation we require this feature extensively in our application. Request your immediate help on this.

- Anish

17

Hi,


We are working over your issue and we will try to provide a fix or supported version before the end of this week or so. The supported version/fix (that we will provide you) should fix your issue and work in accordance with MS Excel.

Thank you.
18

Thanks a lot

19

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

20

Thanks a lot for the quick fix.

We were now able to copy the formula across a range successfully. 1 Question will this build have all the existing features or is there any limitation to use this DLL which you have provided now. Will the next Aspose.Cells release will include this fix.

-Anish