Issue with Formulas Not Reflecting in Upload and Download Sheets

[Ajinkya]

Hi Team,

I am facing an issue where formulas in the spreadsheet are not properly reflecting when importing or downloading sheets in my application. Specifically, the formulas are not retained during the upload process or included correctly during the export/download operation.

Below are the reference codes for upload and download functionality:

Upload Code:
using (var package = new ExcelPackage(stream))
{
    var workbook = package.Workbook;
    var worksheet = workbook.Worksheets[0];

    var rows = new List<dynamic>();
    for (int row = 1; row <= worksheet.Dimension.End.Row; row++)
    {
        var rowData = new List<string>();
        for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            rowData.Add(worksheet.Cells[row, col].Text);
        }
        rows.Add(new { cells = rowData.Select(val => new { value = val }).ToArray() });
    }

    var sheetData = new
    {
        name = worksheet.Name,
        rows = rows.ToArray()
    };

    return Json(new { success = true, data = sheetData });
}



Download Code:
using (var package = new ExcelPackage())
{
    var sheet = spreadsheetData.sheets.FirstOrDefault();
    if (sheet != null)
    {
        var worksheet = package.Workbook.Worksheets.Add(sheet.name);

        foreach (var row in sheet.rows)
        {
            foreach (var cell in row.cells)
            {
                var excelCell = worksheet.Cells[row.index + 1, cell.index + 1];

                // Set font properties
                excelCell.Style.Font.Size = cell.fontSize > 0 ? cell.fontSize : sheet.defaultCellStyle.fontSize;
                excelCell.Style.Font.Bold = cell.bold;

                // Set background color
                if (!string.IsNullOrEmpty(cell.background))
                {
                    excelCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    excelCell.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(cell.background));
                }

                // Set font color
                if (!string.IsNullOrEmpty(cell.color))
                {
                    excelCell.Style.Font.Color.SetColor(ColorTranslator.FromHtml(cell.color));
                }

                // Set text alignment
                excelCell.Style.HorizontalAlignment = cell.textAlign switch
                {
                    "center" => ExcelHorizontalAlignment.Center,
                    "right" => ExcelHorizontalAlignment.Right,
                    _ => ExcelHorizontalAlignment.Left,
                };

                // Set number format
                if (!string.IsNullOrEmpty(cell.format))
                {
                    excelCell.Style.Numberformat.Format = cell.format;
                }

                // Set formula if available
                excelCell.Formula = cell.formula;
                
                // Set cell value
                excelCell.Value = cell.value;
            }
        }
    }

    fileStream = new MemoryStream(package.GetAsByteArray());
}


Could you please guide me on resolving this issue to ensure formulas are preserved and processed accurately?

Thank you for your assistance!

[Anton Mironov]

Hello Ajinkya,

Thank you for the code snippet and the details provided.

The Telerik UI Scheduler Component is a wrapper of the Kendo UI Scheduler Widget. It is responsible for visualizing the provided data and the BackEnd is not actually part of the client(browser) UI.

However, I would recommend double-checking the following:

• In your upload process, formulas are not being extracted from the Excel file because the implementation is using "worksheet.Cells[row, col].Text". This property retrieves the cell's displayed value, not the underlying formula. To preserve formulas, you have to use "worksheet.Cells[row, col].Formula".
• In your download process, the formulas are being set via "excelCell.Formula = cell.formula", but cell.formula might be null or not correctly passed from the front-end. Ensure that the formula is preserved during the upload process.

Here is an example of the JSON:

{
  "value": "50",
  "formula": "=SUM(A1:A3)",
  "background": "#FFCC66",
  "color": "#000000",
  "format": "#,##0.00",
  "fontSize": 12,
  "bold": true,
  "textAlign": "center"
}

I hope this information helps.

Kind Regards,
Anton Mironov
Progress Telerik