SpecialCells

Aspose.Cells Product Family
1

is there anything in Aspose that would allow me to set a named range = cells with formulas.

In VBA it was Set FormulaCells = Selection.SpecialCells(xlCellTypeFormulas)

What I am trying to do is to lock and hide all formulas on a worksheet so I am open to any workaround.

2

Hi,

Thanks for considering Aspose.

Well, the feature is not supported yet. We may consider it in future.

Currently you can create a named range of cells based on adjacent cells or create a named range based on adjoining rectangular cells only and not the non adjacent or cells.

Thank you.

3

I do not see this useful feature too. So is it supported after 4 years? Or how can i fast get range of several cells with formulas from sheet that have about 65000 cells without iterating them (using interop i can)?

4

Hi,

Please see the code below, it creates a range of your choice, then it prints the values of all cells falling in that range.

Please see the source file and the output of the code below.

I have used the latest version:
Aspose.Cells for .NET v7.0.1.5


C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];


//Create your range

Range range = worksheet.Cells.CreateRange(“A1:D25”);


//Get enumerator from your range

var obj = range.GetEnumerator();


//Print the values of all the cells in your range

while (obj.MoveNext())

{

Cell cell = obj.Current as Cell;


System.Diagnostics.Debug.WriteLine(cell.StringValue);

}



Output
4
311
995
134
715
303
575
120
78
172
450
606
728
657
318
214
331
833
698
480
546
507
275
499
153
819
222
42
144
927
685
358
735
447
548
407
919
711
793
467
498
1
778
925
84
799
121
490
258
158
815
266
62
155
45
969
358
173
180
604
666
5
275
484
275
212
800
369
151
912
184
470
387
290
169
633
718
543
10
830
783
54
266
362
783
22
799
138
19
14
652
876
349
881
878
647
736
319
724
537

5

So, you think this is an answer for my question? Our company have deal with xls documents, that have about 2 million cells. So you propose me to just iterate them? If I will do this, I’l being kicked out the same day =) Probably i need response from a developer, not from QA.

6
Hi,

We can provide the following two methods to get all cells which contain formula, e.g:
IEnumerator ie = Cells.GetEnumerator(SpecialCellType type, object value);
IEnumerator ie = Range.GetEnumerator(SpecialCellType type, object value);

Which one fits your need? We will support for your need.

Thank you.
7

Hi,

In fact if the cells dataset is large, we are afraid it will not give much better performance by providing you an enumerator for the special cell type than enumerating and check those cells by yourself. If you just want to make the formulas hidden, we think the best way is that you set the default style of the workbook to hide all the formulas and protect worksheets in it. The sample code should be like the following:

Workbook wb = new Workbook();
wb.Worksheets[0].Protect(ProtectionType.All);
Style style = wb.DefaultStyle;
style.IsLocked = false;
style.IsFormulaHidden = true;
wb.DefaultStyle = style;
wb.Save(“res.xls”);

Hope, this helps.

Thank you.