All rows at run time with carrying down the formulas in Excel spreadsheet in .NET

Aspose.Cells Product Family
1

Hi,

We have excel sheet with tabular data and formulas in the end to show aggregate. Now we can’t know in advance the total number of rows. How can we import data and keep the formulas working. Screenshot attached to elaborate the scenario.

Aspose.Cells-Import-Data.png (45.4 KB)
Thanks

2

@smumtaz,

Thanks for the screenshot.

I guess you are using Cells.ImportCustomObjects to import data into the range/table (in the worksheet). Well, you may simply set InsertRows Boolean parameter to true. This way, new rows would be inserted and the formulas would be updated/adjusted accordingly, see the following code segment for your reference:
e.g
Sample code:

..........
//Access first worksheet
            Worksheet ws = wb.Worksheets[0];

            //Specify import table options
            ImportTableOptions opts = new ImportTableOptions();
             opts.InsertRows = true;
             ...........

            //Import custom objects
            ws.Cells.ImportCustomObjects(dis, 0, 0, opts);

Hope, this helps a bit.

3

Thanks for your response, i tried the opts.InsertRows = true; option but it is shifting the existing empty formatted rows down.

4

@smumtaz,

Yes, this would happen, InsertRows parameter does the same thing as Insert… (Range) (context menu command) with shift options of MS Excel. How could you cope with it in MS Excel manually, please elaborate. There is no systematic or automatic way to cope with it in MS Excel either, you may manually insert values into the table cells. In that case, you should have number of records equal to the blank cells of the table (Please note, if the number of records are more than the blank cells of the table, it will overwrite the bottom area for sure).

By the way, you may try to evaluate the range of the table using the following line of code. Once you obtain the range of cells of the table, you may calculate the number of rows easily and then decide if you should set InsertRows parameter or not:
e.g
Sample code:

Range tableRange1 = worksheet.ListObjects[0].DataRange;