Issue with Filtered Table Showing Formula Instead of Evaluated Values("Aspose.Cells.Tables.ListColumn.Name") in Aspose.Cells for .NET C#

Aspose.Cells Product Family
1

Hello,

I am working with Aspose.Cells for .NET C#. I am creating a referenced Excel file from a source file, where each data cell in the referenced file is linked to a corresponding cell in the source file, and the source file is located on a shared drive. However, I am encountering an issue where the filtered table gets corrupted during this process.

I found a workaround by changing the column names, which resolves the issue. But, when I modify the “Aspose.Cells.Tables.ListColumn.Name” object in the “Aspose.Cells.Tables.ListObject.ListColumns”, I face another issue. Specifically, when I set the “Aspose.Cells.Tables.ListColumn.Name” to a cell’s name (e.g., passing cell.Name), it displays specific cell references like A5, B5, etc. If I pass the same reference formula, it shows the formula itself instead of the evaluated value.

I also tried using “Aspose.Cells.Tables.ListColumn.Formula”, but it results in an extra row appearing below the headers.

Can someone help me resolve this issue?

2

@PriteshWani0

It seems you are encountering issues with filtered tables in Aspose.Cells for .NET, particularly when dealing with formulas and column names. Here are some suggestions to help you resolve the problems you’re facing:

  1. Setting Column Names: When you modify the ListColumn.Name property, ensure that the names you are assigning do not conflict with existing names or reserved keywords in Excel. If you are passing a cell’s name directly, it may be interpreted as a reference rather than a value. Instead, consider using a more descriptive name that does not resemble a cell reference.

  2. Using Formulas: If you want to display evaluated values instead of the formula itself, you should set the formula in a way that Excel can interpret it correctly. For example, you can set the formula for a column like this:

    listObject.ListColumns[1].Formula = "=[Column A] + 1";

    This will ensure that the formula propagates correctly to new rows without showing the raw formula in the table.

  3. Avoiding Extra Rows: If setting the ListColumn.Formula results in an extra row appearing below the headers, check if the table is being resized or if there are any additional settings that might be affecting the table structure. Ensure that the range defined for the list object does not include extra rows.

  4. Debugging the Filtered Table: If the filtered table gets corrupted, try to isolate the issue by creating a minimal example that reproduces the problem. This can help you identify whether the issue is with the data source, the way the table is being created, or how the formulas are being applied.

If these suggestions do not resolve your issue, consider sharing a minimal code snippet that reproduces the problem on the forum for more targeted assistance.

Feel free to reach out if you have further questions or need additional help!

Sources:
[1]: PropagateFormulaInTable.cs

3

@PriteshWani0,

Could you please provide the zipped template Excel file and a sample (runnable) code to demonstrate the issue(s)? This will help us evaluate your issue precisely and figure it out soon.

4

referance_issue.zip (41.7 KB)

@amjad.sahi ,Thank you for your attention.
I have attached a ZIP file with a runnable function. Please update the file paths according to your needs. As I was unable to add the Aspose license to the new console app, I am providing the code only. Please run this on your setup.

I apologize for the inconvenience. There are two input files, which are identical, but they are kept separate to avoid a circular reference to the same file.

5

@PriteshWani0,

Thanks for the template Excel file.

I tested your scenario/case using your template Excel file and sample code segment. I noticed the issue as you pointed out in the output Excel file generated by Aspose.Cells. I found that for table headers, if we add reference to other table headers (in other workbook) in the formula, it is not evaluated/calculated so we cannot get header text/value. Instead it gives formula strings. Then, I tried the scenario/case manually in MS Excel and got the same results. The formulas in the headers are not calculated to give header text/value, instead we have formula strings in the headers. I think this might be a limitation regarding table headers formulas. Could you perform your task in MS Excel manually and if so, kindly give us a sample Excel file with your expected results. We can look into it soon.

6

referance_issue.zip (71.7 KB)

@amjad.sahi
Thanks for looking into this. I have updated my code and added two new Excel files. In these files, I removed the table and re-added it manually, as well as programmatically. When I re-add the table manually, it removes the formulas from the column headers, but when done programmatically, it labels them as “Column1,” “Column2,” etc.

I have figured out a way to correct tables that are the same as the manual approach by simply removing the cell references from the header cells.

Thank you for your support.

7

@PriteshWani0
Please refer to the following example code and add the code of copying the ListColumn name after setting the properties.

Workbook newWorkbook = new Workbook(filePath + "tesinput.xlsx");
string path = filePath + @"tesinput - Copy.xlsx";
newWorkbook.AbsolutePath = null;
foreach (Worksheet newSheet in newWorkbook.Worksheets)
{
    string sheetName = newSheet.Name.Replace("'", "''");
    string cellReference = $"='[{path}]{sheetName}'!";
    Aspose.Cells.Range usedRange = newSheet.Cells.MaxDisplayRange;
    if (usedRange == null)
        continue;

    foreach (Aspose.Cells.Cell currentCell in usedRange)
    {

        if (currentCell.Type != CellValueType.IsNull)
            currentCell.Formula = cellReference + currentCell.Name;
    }
    //var los = newSheet.ListObjects;
    List<Aspose.Cells.Tables.ListObject> los = new List<Aspose.Cells.Tables.ListObject>();
    if (newSheet.ListObjects.Count > 0)
    {
        foreach (Aspose.Cells.Tables.ListObject lo in newSheet.ListObjects)
        {
            los.Add(lo);
        }
        newSheet.ListObjects.Clear();
    }

    //newSheet.ListObjects.Clear();
    if (los.Count > 0)
    {
        foreach (Aspose.Cells.Tables.ListObject lo in los)
        {
            var ind = newSheet.ListObjects.Add(lo.StartRow, lo.StartColumn, lo.EndRow, lo.EndColumn, true);
            var newLo = newSheet.ListObjects[ind];
            newLo.DisplayName = lo.DisplayName;
            newLo.ShowHeaderRow = lo.ShowHeaderRow;
            newLo.TableStyleType = lo.TableStyleType;
            newLo.ShowTotals = lo.ShowTotals;

            //add the following code to copy the name of listcolumn
            ListColumnCollection columns = lo.ListColumns;
            for (int i = 0; i < columns.Count; i++)
            {
                ListColumn column = columns[i];
                newLo.ListColumns[i].Name = column.Name;
            }
            //foreach (Aspose.Cells.Tables.ListColumn lc in lo.ListColumns)
            //{
            //    //added same formula for referance in both header cell and ListColumn.Name
            //    //newSheet.Cells[lc.Range.Address.Split(':')[0]].Formula = $"='[{path}]{sheetName}'!{lo.DisplayName}[[#Headers],[{lc.Name}]]";
            //    //lc.Name = $"='[{path}]{sheetName}'!{lo.DisplayName}[[#Headers],[{lc.Name}]]";

            //    //adding direct cell referance 
            //    //lc.Name = cellReference + lc.Range.Address.Split(':')[0];

            //    //added table  referance got this by manually adding ref to header cell 
            //    //lc.Name = cellReference + $"[[#Headers],[{lc.Name}]]";

            //    //tried to assign evaluated value so it dosenot display wrong data 
            //    //lc.Name = newSheet.Cells[lc.Range.Address.Split(':')[0]].DisplayStringValue;


            //    //added above used formula over here
            //    //lc.Formula = cellReference + lc.Range.Address.Split(':')[0];
            //}

        }
    }
}
newWorkbook.Save(filePath + "out_net.xlsx");

Hope helps a bit.

8

@John.He
Thank you for your suggestion. I have implemented something similar, so I appreciate the example you provided.

9

@PriteshWani0
You are welcome. If you have any questions, please feel free to contact us at any time.

10

@John.He
Hi, sorry for the interruption.
In the code above:

foreach (Aspose.Cells.Cell currentCell in usedRange)
    {

        if (currentCell.Type != CellValueType.IsNull)
            currentCell.Formula = cellReference + currentCell.Name;
    }

This loop is taking a significant amount of time depending on the size of the Excel file. To address this, I implemented a parallel loop as shown below:

 Parallel.ForEach(newWorkbook.Worksheets, (newSheet) =>
 {
     string sheetName = newSheet.Name.Replace("'", "''");
     string cellReference = $"='[{p}]{sheetName}'!";
     Aspose.Cells.Range usedRange = newSheet.Cells.MaxDisplayRange;
     if (usedRange != null)
     {
         foreach (Aspose.Cells.Cell currentCell in usedRange)
         {
             if (currentCell.Type != CellValueType.IsNull)
             {
                 currentCell.Formula = cellReference + currentCell.Name;
             }
         }
         if (newSheet.ListObjects.Count > 0)
         {
             Parallel.ForEach(newSheet.ListObjects, (lo) =>
             {
                 Parallel.ForEach(lo.ListColumns, (lc) =>
                 {
                     newSheet.Cells[lc.Range.Address.Split(':')[0]].Formula = null;
                     newSheet.Cells[lc.Range.Address.Split(':')[0]].PutValue(lc.Name);
                 });
             });
         }
     }
 });

However, this parallel loop is still taking a considerable amount of time. Do you know of any other optimized methods that could further reduce the execution time? any other method then using newSheet.Cells.MaxDisplayRange ?

11

@PriteshWani0,

I tested the code snippet shared by @John.He. Using the original files (“tesinput.xlsx” and “tesinput - Copy.xlsx”), it works fine and instantly, and I do not see any performance issue. I even tried your code snippet, and it works OK without any performance lagging. It seems you are using some other file(s) having large tables in them. Could you please zip and attach your source Excel files and share the code snippet? We will evaluate your issue soon.