Cell's Formula Without Spaces

Aspose.Cells Product Family
1

Hey,

I am looking to your Aspose.Cells for .net and i am facing and issue.
Excel is providing the Cell’s formula as its is.
such as, if formula is having spaces it not removing that spaces and aspose cells does that.

Excel:
image.png (772 Bytes)

Aspose Cells:

image.png (4.4 KB)

2

@harshCIPL22,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as
CELLSNET-47125 – Spaces are lost from the formula when fetched using Aspose.Cells for .NET

3

@harshCIPL22,
We have investigated it bit more. Those spaces are useless, so Aspose.Cells does not keep them.

4

Thanks for your quick responses.

As you said spaces are useless, It may useless for your side but we are replacing our code from Excel to Aspose so at some stage we need the formula exactly as it is in Excel we can not allow the change in formulas.

So, Is there any way to get the exact formula because Excel It self keeping the formula as it is so Its better that Aspose should also follow the same.

Thanks

5

@harshCIPL22,
Thank you for explaining. We will analyse it and provide our feedback accordingly.

6

@harshCIPL22,

We evaluated your issue further. Well, spaces are special tokens for the formula structure. For our component, the formula model was originally designed for keeping the main logic for calculation. To support spaces and keep them in the formula data, we need to change many things, such as the formula parser, builder, calculating engine, …etc. We need to make further investigations for this special feature but we are afraid we cannot support it in near future.

Once we have an update on it, we will let you know.

7

Thanks for your brief explanation.

As per my requirement It is also necessary to have the same fomula as excel it self.
So, If It is possible that you can provide the Formula as it is by creating any new one more property without any changes like parsing etc.
It will be very helpful for us and you too,

8

@harshCIPL22,
We have recorded your comments and will provide our feedback after the further discussion here.

9

@harshCIPL22,

We might consider to provide an option for you to set formula without being parsed for cell. However, in this way those formulas can only be used to save OOXML file formats, such as XLSX, XLSM, …etc., and cannot be used for operations, such as calculation, copy, insert, delete, …etc. If this can fit your requirements, we will investigate this feature further before we can provide an ETA, please confirm?

10

Thanks for understanding our issue.

We do not want to save the file.
We just open the file and get the data and perform some calculations.
So, As per your previous comment you can provide it.

11

@harshCIPL22,
Thank you for the feedback. We have recorded it for our reference with the ticket and will update you here once any further feedback is ready to share.

12

@harshCIPL22,
If you just need to get the formulas string from OOXML files such as xlsx, you may use “LoadOptions.ParsingFormulaOnOpen = False” to load the template file. In this way the formulas will be returned just as what saved the in the template file. However, after you do other operations, such as calculating formulas, copy/insert/delete cells, …etc., those formulas will be re-parsed and those spaces will be lost.

13

Sorry, but we can not use that property directly.
That may affects to our product too.
So, If its possible to give a new property that will be better.

14

@harshCIPL22,

I am afraid, that is not straight forward as it looks. Anyways, I have reopened your ticket. We will soon start investigating it before we could share an ETA (if possible).

Once we have any new information available, we will share it with you.

15

@harshCIPL22,
We have thoroughly investigated the issue again and observed that if you need to keep those formulas as what they are in the template file, we are afraid LoadOptions.ParsingFormulaOnOpen is the only available one. What we can provide as new option is for setting formula at runtime by Cell.SetFormula(). With the new option, those formulas set to cell will not be parsed until some other operations need the parsed data, such as formula calculation. Please confirm whether it can fit your need or not.

16

I tired by setting the property value to False “LoadOptions.ParsingFormulaOnOpen = False”
but the formula is coming with the same trimmed space.
It should come: “=COS($D$15) * 10” but its having showing “=COS($D$15)*10” after setting the false too.

I am attaching a sample file to make it understandable to you too.
If its happening with the ParsingFormulaOnOpen = False then please provide the sample project that is coming with the same values else Its better to provide the new property of Cell like. FormulaWithoutParsing or anything else named.

FormulawithSpace.zip (6.1 KB)

17

@harshCIPL22,
Thank you for providing the sample file. I have tried the scenario using the attached project. It shows spaces properly in the formula and no space is trimmed. Please give it a try and share the feedback. Please ensure that you use latest version of Aspose.Cells for .NET v20.1.

TestCellsApp.zip (11.1 KB)

18

Thanks for your sample code.

i am doing the same thing but still I am not able to get the Formula without space.
Even I am using the latest version of DLL “20.1.7.0”.

You can verify the below attached code of mine too.
You can see that in grid Its showing the four values in that one of them is Cell Formula.

AsposeSampleCodes.zip (5.0 MB)

19

@harshCIPL22,
I am testing your sample project. In the meanwhile could you please download the following project and test at your end. I have not cleaned it and modified it to display all the formulas which your are displaying in your program. Give it a try and share the feedback.

20

@harshCIPL22,
I have modified your following function a little bit to perform a simplest test for displaying the formula. Replace this function in your project and just check that spaces are maintained in the formulas.

Private Sub OpenExcelFile(ByVal pstrFilePath As String)
    Dim objLoadOptions As LoadOptions
    Try
        objLoadOptions = New LoadOptions
        objLoadOptions.ParsingFormulaOnOpen = False
        objWorkbook = New Aspose.Cells.Workbook(pstrFilePath, objLoadOptions)
        Dim msg As String = String.Empty

        For r As Integer = 2 To 8
            Console.Write(objWorkbook.Worksheets(0).Cells(r, 4).Formula)
            msg &= objWorkbook.Worksheets(0).Cells(r, 4).Formula & Environment.NewLine
        Next
        MessageBox.Show(msg)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

You may please review following code in your project which seems to create issue and try to change your logic for getting formula as mentioned above.

objCell = CType(objRangeEnumerator.Current, Aspose.Cells.Cell)

When this line of code is executed, it returns formula without space. Please change this construct with some other option and try your project again.