Assigning formulas to a specific range

Aspose.Cells Product Family
1

Team, is there a way to assign a formula in Aspose.Cells, to a particular range in one go rather assigning formula in each cell of the range. Assigning formula in each is time consuming and need to take care of incrementing row and column indices.
If you are in Excel, copy a formula from a single cell and copy to range, Excel takes care of incrementing row and column indices automatically - which is what I am looking for in Aspose. Equivalent code of VBA/COM approach is given below for your reference. Please let me know.

In this example code, I am reading formula string (stored in 1st row without ‘=’ symbol) and appending ‘=’ and assigning the formula to a range in each column.

For iCol = iFirstCol_FormulaeRange To iLastCol_FormulaeRange
    If Len(wstData.Cells(1, iCol).Value) <> 0 Then
        strFormula = "=" & wstData.Cells(1, iCol)
        wstData.Cells(gc_Template_Start_Row, iCol).Resize(iNoOfRows_Data - gc_Template_Start_Row + 1).Formula = strFormula
    End If
Next iCol

Thanks.

2

@SubbuatAspose,

Thanks for your query.

Please try following sample code which performs same task as mentioned by you and share the feedback.

Workbook wb = new Workbook();

//Set the max rows of shared formula to 5
wb.Settings.MaxRowsOfSharedFormula = 5;

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Access cell D1
Cell cell = ws.Cells["D1"];

//Set the shared formula in 100 rows
cell.SetSharedFormula("=Sum(A1:A2)", 100, 1);

//Save the output Excel file
wb.Save("outputSpecifyMaximumRowsOfSharedFormula.xlsx");
3

Thank you very much the reply.

For my aforementioned code snippet, I need to find out first non-empty cell in a row and last non-empty cell in a row. I see you have ‘LastDataCell’ in Aspose library. Can I know equivalent version of FirstDataCell.

int formulaRangeLastColumn = rawDataSheet.Cells.GetRow(0).LastDataCell.Column;

You may understood by now, I am looking for,
int formulaRangeFirstColumn = rawDataSheet.Cells.GetRow(0).FirstDataCell.Column;

But this does not work.

4

@SubbuatAspose,

We have logged an issue in our database for further investigations. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46304 - Support for FirstDataCell property
5

Hi Team, Is there any update on this. Please let me know. Thanks.

6

@SubbuatAspose,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46304”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

7

Thanks team. Will wait for the fix.

8

@SubbuatAspose,

You are welcome.

9

@SubbuatAspose,

Please try our latest version/fix: Aspose.Cells for .NET v18.8.3:

Your issue should be fixed in it.

Let us know your feedback.

10

Thank you very much for your efforts. I am not getting v18.8.3 yet in NuGet Package Manager. Does it take time generally to reflect there? Please let me know. Thanks once again.

11

@SubbuatAspose,

Well, you may use this fix as an official release for production, it includes all the functionality full fledged (of v18.8) and includes other enhancements and fixes. However, we are scheduled to release our next official release (i.e., Aspose.Cells for .NET v18.9) to Nuget repos. in the third or fourth week of September 2018 (the release will include all previous fixes including this one). Please note only official releases (we publish once in a month) are published to Nuget repos.

12

Ah, ok, I dont have access to download your previous zip file from Dropbox.

13

@SubbuatAspose,

Please find the fixes (.NET 2.0 and .NET 4.0) attached here for your requirements.
Aspose.Cells18.8.3 For .Net2_AuthenticodeSigned.Zip (4.6 MB)
Aspose.Cells18.8.3 For .Net4.0.Zip (4.6 MB)

14

Thanks Amjad. Its working as expected. Will wait for official release so that I can update the library from NuGet.

15

@SubbuatAspose,

Thanks for your feedback.

Good to know that your issue is sorted out by the new fix. Feel free to write us back if you have further comments or questions, we will be happy to assist you soon.

16

The issues you have found earlier (filed as CELLSNET-46304) have been fixed in Aspose.Cells for .NET v18.9. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi