Cells.importdata is failing if there is invalid formula

Aspose.Cells Product Family
1

Hi Team,

We are trying to skip the next cells if the error in formula. Below is the code we are trying. Is there an option to skip to next cell if error occurs

// Create a new workbook
Workbook workbook1 = new Workbook();
Cells cells1 = workbook1.Worksheets[0].Cells;
// Create sample data table with formula content
DataTable dt1 = new DataTable();
dt1.Columns.Add(“Column1”);
dt1.Rows.Add(“=”);
dt1.Rows.Add(“=0-( 0 + 0 + 0 + 0 + 0 + 0 ) “);
dt1.Rows.Add(”=AVERAGE(4,5,6)”);

// Set import options with IsFormulas property
ImportTableOptions options = new ImportTableOptions();
options.IsFormulas = new bool[] { true }; // Treat first column as formulas
// workbook1.ParseFormulas(true);
// Import data with formulas
cells1.ImportData(dt1, 0, 0, options);

2

@Balaji04

To handle invalid formulas during the import process using Cells.ImportData, you can implement a custom solution to skip cells with errors. Unfortunately, the ImportData method does not have a built-in option to automatically skip cells that contain invalid formulas. However, you can achieve this by iterating through your data before importing it and checking for valid formulas.

Here’s a sample approach you can take:

  1. Validate Formulas: Before importing, check each formula in your DataTable to see if it is valid.
  2. Create a New DataTable: Create a new DataTable that only includes valid formulas.
  3. Import the Valid Data: Use the ImportData method to import only the valid formulas.

Here’s an example of how you might implement this in C#:

using System;
using System.Data;
using Aspose.Cells;

class Program
{
    static void Main()
    {
        // Create a new workbook
        Workbook workbook = new Workbook();
        Cells cells = workbook.Worksheets[0].Cells;

        // Create sample data table with formula content
        DataTable dt = new DataTable();
        dt.Columns.Add("Formulas");
        dt.Rows.Add("=INVALID_FORMULA");
        dt.Rows.Add("=0-(0 + 0 + 0 + 0 + 0 + 0)");
        dt.Rows.Add("=AVERAGE(4,5,6)");

        // Create a new DataTable for valid formulas
        DataTable validFormulas = new DataTable();
        validFormulas.Columns.Add("Formulas");

        // Validate formulas
        foreach (DataRow row in dt.Rows)
        {
            string formula = row[0].ToString();
            if (IsValidFormula(formula))
            {
                validFormulas.Rows.Add(formula);
            }
        }

        // Set import options
        ImportTableOptions options = new ImportTableOptions();
        options.IsFormulas = new bool[] { true }; // Treat first column as formulas

        // Import valid data
        cells.ImportData(validFormulas, 0, 0, options);

        // Save the workbook
        workbook.Save("ValidFormulas.xlsx");
    }

    static bool IsValidFormula(string formula)
    {
        // Implement your logic to validate the formula
        // For demonstration, let's assume any formula starting with '=' is valid
        return formula.StartsWith("=");
    }
}

In this example, the IsValidFormula method is a placeholder where you can implement your own logic to determine if a formula is valid. The valid formulas are then imported into the workbook, effectively skipping any invalid ones.

This approach allows you to manage errors gracefully while importing data into your Excel sheets.

3

@Balaji04
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58591

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

@Balaji04,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-58591”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.7) that we plan to release in the first half of July 2025. You will be notified when the next version is published.

5

The issues you have found earlier (filed as CELLSNET-58591) have been fixed in this update. This message was posted using Bugs notification tool by leoluo