TODAY() function returns numeric value

Aspose.Cells Product Family
1

Hello,
I have a program which accepts an excel formula from the user and pastes the result in a designated excel file. When using TODAY() formula the CalculateFormula function returns a numeric value and not a date. This is my code:

public void SetCellValue(int row, int column, string formula, Worksheet worksheet)
{
var result = worksheet.CalculateFormula("=" + formula);
worksheet.Cells[row, column].PutValue(result.ToString(), true, true);
}

Is there a way to automatically detect the correct format of a formula result and return it in this format?(for example a numeric format for a SUM function and a date format for TODAY() function)

I’m using Aspose for .Net, version 20.7.

Thanks!

2

@galgonen,
I have tried to perform the same task using MS Excel only however could not succeed as there is only one function TYPE that can be used to get the type of value however it returns 1 indicating Number for the result of TODAY(). There is no built-in feature available in MS Excel to get the function return type and the same is the case with Aspose.Cells. You may please share steps to achieve this using MS Excel for our reference. We will try to provide you assistance for the same using Aspose.Cells.

3

Hi, thanks for the reply!
What we’re trying to get is to pass the formula and get the formatted value and not the numeric value.
For example, I’m running the formula:
worksheet.Cells[row, column].Formula = “=today()”;
And I’m getting the correct result, numerically. Is there a way to automatically format the result?
When using excel interop, it works.

4

@galgonen,
You may achieve this feature by manually setting style for different type of formulas based on their returned type and your desired display format. Here is a sample code that demonstrates this idea however you may modify it according to your scenario.

List<string> formulas = new List<string>() { "TODAY()", "SUM(B2:C2)" };
Workbook workbook = new Workbook();
Worksheet ws = workbook.Worksheets[0];
            
foreach (var (formula, index) in formulas.Select((value, i) => (value, i)))
{
    string CellRef = CellsHelper.CellIndexToName(index, 0); 
    ws.Cells[CellRef].Formula = "=" + formula;
    Style style = ws.Cells[CellRef].GetStyle();
    if (formula.StartsWith("TODAY"))
    {
        style.Number = 14;
    }
    else if (formula.StartsWith("SUM"))
    {
        ws.Cells["B2"].Value = 5;
        ws.Cells["C2"].Value = 10;
        style.Number = 3;//just for example
    }
    ws.Cells[CellRef].SetStyle(style);
}
workbook.CalculateFormula();
ws.AutoFitColumns();
workbook.Save("Output.xlsx");

Here is the program output:

Refer to the following document for more details on setting format:
Data Formatting