Direct access to custom formulas

Aspose.Cells Product Family
1

dear aspose.cells support,


I have an application that looks for all custom formula’s ( UDF - User Defined Formula ) inside a spreadsheet.

Currently, I am looping over all worksheets, then looping over all cells in that worksheet, then checking the contents of each cell if it is a formula : ( in VB .NET coding )

for each w1 as Aspose.Cells.Worksheet In x.Worksheets
for each c1 as Aspose.Cells.Cell in w1.cells
if c1.IsFormula andalso c1.IsArrayHeader andalso …

This works fine, however, there seems to be a large performance penalty when processing large files - that is spreadsheets with lots of cells.

Is there no simpler way to get directly to all cells inside a workbook that have a (custom) formula, so that i dont have to loop over all cells to test is they are a formula ?

thanks for you much appreciated help,

guido
2

Hi Guido,

Thanks for your posting and using Aspose.Cells.

To help us better understand your problem, please provide us your sample excel file which you can create manually using Microsoft Excel 2010/2013 and runnable sample code preferably a console application project. We will investigate your issue and help you asap. Thanks for your cooperation.

3

please find attached the simple example spreadhseet

Unfortunately, there seems to be something wrong with your server to attach zipped files. I cant upload any zipped directories, it gives me the error : “The transfer attempted appeared to contain a data leak” ??
In any case, the exmaple VB .NET code is very simple, just create a new console application and insert following lines
Module Module1
Dim p As String = “C:\test.xlsx”
Sub Main()
Dim b As New Aspose.Cells.Workbook§
For Each w1 As Aspose.Cells.Worksheet In b.Worksheets
For Each c1 As Aspose.Cells.Cell In w1.Cells
If c1.IsFormula AndAlso c1.IsArrayHeader Then
Console.WriteLine(c1.Formula)
End If
Next c1
Next w1
Console.ReadLine()
End Sub
End Module

the problem is that I dont find a way to loop directly over all formula cells, so I have to loop over all cells and check if it is a custom formula, which takes quite some processing time in case of large sheets.

thanks for your help,

guido

4

Hi Guido,


Thank you for sharing additional details.

I am afraid, I was not able to find an API that could list only the formula cells from a particular worksheet, however, I think you may use Cells.Find method for your requirements. Please check the following piece of code and give it a try on your side in comparison to your existing solution.

VB.NET

Dim workbook = New Workbook(“D:/test.xlsx”)
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim opts As New FindOptions()
opts.LookInType = LookInType.OnlyFormulas
opts.LookAtType = LookAtType.StartWith

Dim cell As Cell = Nothing
Do
cell = worksheet.Cells.Find("=", cell, opts)
If cell IsNot Nothing AndAlso cell.IsFormula Then
Console.WriteLine("Cell " & cell.Name & " Formula " & cell.Formula)
End If
Loop While cell IsNot Nothing
5

hello support,


thnak you for your suggested solution.

I tested it and the suggested solution is even much slower than the original one. It seems the Find function on a cell is really very, very slow. I found spreadheets where the find option takes double the time of looping over all cells.

Maybe you can list the suggestion of having a Formulas collection directly on the worksheet as an improvement for a next version ?

guido
6

Hi Guido,

Thanks for your posting and using Aspose.Cells.

Do you want us to create some formulas collection which should contains all the formulas of the worksheet/workbook? For example Worksheet.Cells.Formulas etc.

7

that would be great !

Byt any solution that gives fast access to all formulas would be appreciated.
8

Hi Guido,

Thanks for your clarification and using Aspose.Cells.

We have logged your feature requirement as a New Feature request in our database to support fast access to all formulas. We will look into it and implement it if possible. Once there is some news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43969 - Fast access to all formulas

9

Hi,

Thanks for using Aspose.Cells.

Please access all formulas as your mentioned in the post

( Direct access to custom formulas )

We have no plan to implement it because we have to use same code in inner model to implement this feature.