FormulaLocal

Aspose.Cells Product Family
1

Hi there,

I am creating an Excel sheet with formulas via aspose.cells. Works great.

For documentation purposes i would like to print the formula to the Report, too so that Auditors can Audit the Report.

Works, too, i use “’” & c.formula to print the formula to a cell.

BUT: We have German Auditors and of course they would like to see the German formula Name and also number formats in regional Settings. And we have Auditors from other countries that of course would like to see the formulas in their language when they create the Report.

How can i convert c.formula to c.formulalocal? Microsoft Interop has formulalocal that does the Job. is there something similar in aspose.cells?

Thank you in advance

Kind regards
Norman

2

@norman.neubert

Thanks for using Aspose APIs.

We are able to understand your issue. However, before we log it, please provide us some sample Excel file(s), screenshot(s) and sample code, We will then look into this issue further and log the tickets for it to support them in future releases. Thanks for your cooperation in this regard and have a good day.

3

Hi Shakeel,

thanks a lot! This is a key issue to our internation Auditors and we really Need a solution for it. Thanks for taking a look at the aspect.

Here is some Sample-Code and a screenshot with “the Problem”. As you can see SUM is Englisch whereas SUMME is German. Number-Format is “wrong”, too: . instead of , (not in the sample function but I am sure you know what I mean).

Screenshot: Dropbox - File Deleted - Simplify your life

Namespace Introduction
Public Class FirstApplication
Public Shared Sub Run()
’ ExStart:1
’ The path to the documents directory.
Dim dataDir As String = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)

        Try
            ' Create a License object
            Dim license As New License()

            ' Set the license of Aspose.Cells to avoid the evaluation limitations
            license.SetLicense(dataDir & Convert.ToString("Aspose.Cells.lic"))
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        ' Instantiate a Workbook object that represents Excel file.
        Dim wb As New Workbook()

        ' When you create a new workbook, a default "Sheet1" is added to the workbook.
        Dim sheet As Worksheet = wb.Worksheets(0)

        ' Access the "A1" cell in the sheet.
        Dim cell As Cell = sheet.Cells("A1")

        ' Input the "Hello World!" text into the "A1" cell
        cell.PutValue(1)

        cell = sheet.Cells("A2")
        cell.PutValue(2.6)

        cell = sheet.Cells("A3")
        cell.Formula = "SUM(A1:A2)"

        cell = sheet.Cells("A4")
        cell.Value = "'" & sheet.Cells("A3").Formula


        ' Save the Excel file.
        wb.Save(dataDir & Convert.ToString("MyBook_out.xlsx"), SaveFormat.Xlsx)
        ' ExEnd:1
    End Sub
End Class

End Namespace

4

@norman.neubert

We have logged a New Feature request in our database to investigate and implement this feature.

This issue has been logged as

  • CELLSNET-45697 - Implement Cell.FormulaLocal similar to Microsoft Interop FormulaLocal
5

Dear Shakeel,

thank you very much. It’s great how fast you react to issues.

Looking Forward to the next release :slight_smile:

Kind regards
Norman

6

@norman.neubert

Thanks for your appreciation and considering Aspose APIs.

We will keep you updated with any progress in this ticket. Have a good day. :slight_smile:

7

@norman.neubert,

Our product team has evaluated your issue in details. Well, it is complicated task to support getting and setting formulas in locale formatting. We are afraid we cannot support it soon. For this task, we will support the getter of FormulaLocal firstly. And, we need user to implement the literal text map between standard(en-US locale) and the expected locale by themselves for tokens, such as function name, Name object text, …etc. For example, if we reach one SUM function token while getting the formula string, we provide user “SUM” and user returns “SUMME” if the locale is German. And if we reach the “SUMME” function token while parsing the formula string, we provide “SUMME” to user and user returns “SUM”, so we can know it is SUM function. In this way, we plan to support the getter of Cell.FormulaLocal in the next month.

For parsing formula in local format, it will surely need more time and we cannot provide exact ETA for it currently.

8

@norman.neubert,

As we have said, we provide the API Cell.FormulaLocal but for those locale dependent items, such as the separators for functions parameters and array items, function names, …etc. we cannot get and map them for every locale. You have to provide your own implementation of GlobalizationSettings for them(GetErrorValueString, GetBooleanValueString, GetLocalFunctionName, GetLocalBuiltInName, ListSeparator, RowSeparatorOfFormulaArray, ColumnSeparatorOfFormulaArray).

9

Hi Amjad,

thats cool, thanks! We’ll implement it, seems to make things a lot easier!

Kind regards
Norman

10

The issues you have found earlier (filed as CELLSNET-45697) have been fixed in this Aspose.Cells for .NET 17.12 update.

Please also check the following article:

11

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan