ImportData method overwriting the formula in last column

Aspose.Cells Product Family
1

AsposeBug.zip (14.2 KB)

Hi,

I am using Aspose.Cells for .Net and facing 1 weird issue wherein when I import data to an existing workbook using the ImportData method its overwriting formula in the last column of first row.

Setup - I have an excel with some headers. The first 4 colums are expected to be filled from code. The next 4 columns have some formula in the second row.

Aspose Version - Aspose.Cells (25.9.0)

PFA, the sample console app and sample workbook.

Please let me know if you have any questions.

2

@rahulsingh27

It appears you are encountering an issue where the ImportData method in Aspose.Cells is overwriting the formula in the last column of the first row when importing data into an existing workbook. This typically occurs if the range you are importing data into overlaps with cells that contain formulas.

To prevent overwriting formulas, ensure that the range specified for the ImportData method does not include any cells with existing formulas. You can adjust the starting row or column of the import range accordingly.

Here’s a basic example of how to use ImportData while avoiding overwriting formulas:

// Load the existing workbook
Workbook workbook = new Workbook("path/to/your/existingWorkbook.xlsx");

// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Define the data to import
object[,] data = new object[,] {
    { "Header1", "Header2", "Header3", "Header4" },
    { "Value1", "Value2", "Value3", "Value4" }
};

// Import data starting from the second row and first column
worksheet.Cells.ImportData(data, 1, 0, false);

// Save the workbook
workbook.Save("path/to/your/updatedWorkbook.xlsx");

In this example, the data is imported starting from the second row (index 1), which should help prevent overwriting any formulas that are in the first row.

If you continue to experience issues, please verify the specific range you are importing into and ensure it does not overlap with any cells containing formulas.

3

I already tried with both TotalColumns & ‘ColumnIndexes’ options but no luck.

new ImportTableOptions { IsFieldNameShown = false, ShiftFirstRowDown = false, TotalColumns = dt.Columns.Count, ColumnIndexes = [0,1,2,3] }

4

@rahulsingh27,

I tested your scenario/case using your template Excel file and code snippet. I found formula is not inserted/accommodated in the last (H) column of the Table/ListObject. To cope with it, you may copy/paste range in accordance with the number of records you are entering into the table. Here, you may also set “InsertRows” Boolean attribute of ImportTableOptions to “false”, so no new (empty) rows should be inserted.

See the updated code segment that you may try and it will work for your requirements.

Console.WriteLine(ws.Cells[1, 7].Formula);
Console.WriteLine(ws.Cells[1, 7].R1C1Formula);

//Add these lines to your code
Aspose.Cells.Range sourceRange = ws.Cells.CreateRange("A2:H2");
Aspose.Cells.Range destRange = ws.Cells.CreateRange("A3:H3");
destRange.Copy(sourceRange);

ws.Cells.ImportData(dt, 1, 0, new ImportTableOptions { IsFieldNameShown = false, ShiftFirstRowDown = false, InsertRows = false });
.....

Hope, this helps a bit.

5

Thank you for your reply!

Seems I need to copy the full range as per my dynamic data which in reality is a lot. Alternatively, since the issue pertains to few last columns, I am currently capturing the formula and re-applying if it becomes empty.

Can we expect a fix for this?

6

@rahulsingh27

The moved formula was caused by the option “InsertRows” with the default value “True”. When inserting rows, the existing row is sure to be moved downward so the formula be moved too. This behavior is as designed and we should not change it. As Amjad’s sample code shows, you may set this option to false, so the formula will be kept in the original position.

However, if you need the formula to be applied to all inserted rows for the importing, the design of the template or your code should be changed a bit. You may create one table for the destination range manually in ms excel, or by code like:

...
                row2["TotalSales"] = 110;
                dt.Rows.Add(row2);
                //create table for the destination
                ws.RemoveAutoFilter();
                ws.ListObjects.Add("A1", "H2", true);
                ListObject lo = ws.ListObjects[0];
                lo.ListColumns[lo.ListColumns.Count - 1].Formula = ws.Cells[1, 7].Formula;

                Console.WriteLine(ws.Cells[1, 7].Formula);
                ws.Cells.ImportData(dt, 1, 0, new ImportTableOptions { IsFieldNameShown = false, ShiftFirstRowDown = false });
...

For formula of table’s column, it will be copied for all rows that be imported.

Another solution is that you apply the formula for all imported rows by extra code:

...
                row2["TotalSales"] = 110;
                dt.Rows.Add(row2);
                Console.WriteLine(ws.Cells[1, 7].Formula);
                string fml = ws.Cells[1, 7].Formula; //record the original formula
                ws.Cells.ImportData(dt, 1, 0, new ImportTableOptions { IsFieldNameShown = false, ShiftFirstRowDown = false });
                ws.Cells[1, 7].SetSharedFormula(fml, dt.Rows.Count, 1); //apply formula to all imported rows
...
7

@rahulsingh27,

We appreciate your patience as we work to address your concern. In the meantime, we have provided some solutions and workarounds that you may try to resolve the issue temporarily. Additionally, we have logged internal tickets to investigate the matter further, specifically why the Table’s column formula was not copied accurately or became invalid after inserting records during data import. We might be evaluating potential fix for the relevant internal modules, and we will keep you informed here as soon as we have new updates on it.

1 Like
8

Thank you! Really appreciate it.

9

@rahulsingh27,

You are welcome. We will make sure to keep you informed with any updates regarding this issue.

10

@rahulsingh27
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-59125 - Importing data with true for "InsertRows" option caused invalid formula

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.

11

@rahulsingh27
We have fixed this issue that the formula in last column was overwritten. The fix will be included the next version 25.10. You can simply use the following line to import data:

ws.Cells.ImportData(dt, 1, 0, new ImportTableOptions { IsFieldNameShown = false, ShiftFirstRowDown = false });
1 Like
12

Thank you so much!

13

@rahulsingh27,

You are welcome. We will keep you informed by posting/notifying you in this thread once the new version is released. Aspose.Cells v25.10 is scheduled to be published in the first half of October 2025.

14

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