Adjusting formula based on number of rows inserted

I’m importing rows from DataTable into worksheet and at the bottom I have to show total of a column.

So let’s say I started importing row from Cell A11

Designer.Workbook.Worksheets[worksheetIndex].Cells.ImportDataTable(tempTable, false, “A11”);

and I have formula in cell C12 as SUM(C11:C11)

Do I need to programmatically determine the formula by counting the number of rows or Aspose will adjust it accordingly

I have noticed that when I use smartmarker approach Aspose does adjust the formula accordingly, is that not true when we import rows using Import functions

Hi,


Well, when you use Cells.ImportDataTable() method (by default), new blank rows are inserted first (based on the number of records in the data table), then data is filled into the rows.

Could you give us a sample code (runnable) or create a sample console application, zip it and post it here. Also attach your input and output files, we will check it soon.


Thank you.

Please extract attached zip file and run the console application. Here are my issues

1> The code start importing the data from cell “A2”. After executing the application you will notice that, importing function creates one extra blank row. (row number 4)

2>In the template cell B3 I have setup function as SUM(B2:B2). After executing the application you will notice that, the function parameters are NOT getting updated. I was hoping it will reset the function to SUM(B2:B3) because there are two new extra rows now.

3>In the template cell B13 and B17 I have setup functions, however these function’s parameters get updated as expected. ( so why it is not working for cell B3 as mentioned above in Q2)

Please note that in this case I don’t want to use smart markers approach.

Hi,


Well, if you could manually insert two rows at second row (A2 cell location) in MS Excel, you will notice that the formula would become “=SUM(B4:B4)”, which is not what you want I think. I think for your need you have to insert one extra blank row b/w your formula cell row and starting row and change your formula to “=SUM(B2:B3)”. Now process your code but you have to add one line of code i.e., designer.Workbook.Worksheets[0].Cells.DeleteBlankRows(); to your code for your needs.

See the updated sample code and try it with the updated template file (attached). You may just refer to it and adjust your other segments of your program or project accordingly for your needs.

Sample code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Aspose.Cells;

namespace AsposeImportRows
{
class Program
{
static void Main(string[] args)
{
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(“Template1.xlsx”);
designer.Workbook.Worksheets[0].Cells.ImportDataTable(GetData(), false, “A2”);
designer.Workbook.CalculateFormula();
designer.Workbook.Worksheets[0].Cells.DeleteBlankRows();
designer.Workbook.Save(“output_new.xlsx”);
System.Diagnostics.Process.Start(“output_new.xlsx”);
}

private static DataTable GetData()
{
DataTable table = new DataTable();
table.Columns.Add(new DataColumn(“Code”, typeof(string)));
table.Columns.Add(new DataColumn(“Cost”, typeof(decimal)));


var r = table.NewRow();
r[“Code”] = “A”;
r[“Cost”] = 100;
table.Rows.Add®;

r = table.NewRow();
r[“Code”] = “B”;
r[“Cost”] = 200;
table.Rows.Add®;

return table;
}
}
}


Thank you.