GrapeCity Forums

Hi All,

According to my project, i have to bind two or three datatable to one same sheet one after another.

The first DT has 50 rows, and i can add two additional rows with AddUnboundRows method for some labeling stuff..
But how can i add second DT just behind the row 52 ?

Hello Adam, 

One of the approachs would be to create a temp DataSet and use the Merge method to merge the DataSet or DataTables and then bind the spread to this temp DataSet. 

Another approach would be to use the DefaultSheetDataModel class, add the columns from both the datatables manually and then use the AddUnBoundRows to manually add all the rows to the datamodel.

Regards, 

it is a balance sheet. And it consist of two datatables. One for Assets and other is Liabilities.
Can you give me more specific hints for that approach ?

 a sample design would be like that;

Here is the full sample picture of the sheet.

To create such spreadsheet you have to do a lot of formatting at runtime. The best way to fill the spread with the data would be to execute a Union SQL command on your database. If that option is not available then you may apply the approach I explained earlier. Below is the sample code that will provide some similar behaviour: 

        //create Asset DataTable and add columns and rows

        DataTable Assets = new DataTable();

        Assets.Columns.Add("Items", typeof(string));

        Assets.Columns.Add("Amount", typeof(decimal));

        Assets.Rows.Add(new object[] { "Cash", 15300 });

        Assets.Rows.Add(new object[] { "Land", 10 });

        //create Liabilities DataTable and add columns and rows

        DataTable Liabilities = new DataTable();

        Liabilities.Columns.Add("Items", typeof(string));

        Liabilities.Columns.Add("Amount", typeof(decimal));

        Liabilities.Rows.Add(new object[] {"Equity",600});

        Liabilities.Rows.Add(new object[] {"Capital",512});

         //Bind the spread to Assets

       FpSpread1.ActiveSheetView.DataSource = Assets;

       FpSpread1.ActiveSheetView.DataMember = "Assets";

        //get the reference of DefaultSheetDataModel      

        FarPoint.Web.Spread.Model.DefaultSheetDataModel dm = (FarPoint.Web.Spread.Model.DefaultSheetDataModel)FpSpread1.ActiveSheetView.DataModel;

        //add header rows

        dm.AddUnboundRows(0, 1);

        dm.SetValue(0, 0, "ASSETS");

        dm.SetValue(0, 1, "AMOUNT");

        int addRowIndex=dm.RowCount;

        dm.AddUnboundRows(addRowIndex, 1);

        dm.SetValue(addRowIndex, 0, "LIABILITIES");

        dm.SetValue(addRowIndex, 1, "AMOUNT");

        //Append the rows from Liabilities DataTable

       addRowIndex = dm.RowCount;

       dm.AddRows(addRowIndex, Liabilities.Rows.Count);

       foreach (DataRow r in Liabilities.Rows)

       {

           dm.SetValue(addRowIndex, 0, r[0]);

           dm.SetValue(addRowIndex, 1, r[1]);

           addRowIndex++;

       }

Regards, 

Hi Harish,

Thanks for your quick reply. 
my both datatable has five same columns.
GROUP_CODE, DESCP, FIRST_AMOUNT, SECOND_AMOUNT, THIRD_AMOUNT

But i am getting System.Data.NoNullAllowedException error on dm.AddRows(addRowIndex, Liabilities.Rows.Count);  line
the message is : Column 'GROUP_CODE' does not allow nulls.

But there is not any null value on GROUP_CODE column of the Liabilities ( and also Asset ) datatable.

I think, i am missing something ...

Do you have any idea ?

Adam,

Is there any primary key defined on that column or is there any setting on the column to not allow any null values? Because this exception is thrown when there is an attempt to insert a null value into a column where AllowDBNull is set to false or the Primary key was not selected (Reference)

Suresh ,

No there is not any primary key definition on that column. cranky point is, i am not addinq any additional row that Liabilities datatable.

The error occurs when binding Liabilities datatable to the sheetview with AddRows method, not adding any rows to the Liabilities datatable.
on that line ..
model.AddRows(addRowIndex, Liabilities.Rows.Count);

My code is :

// some process for Assets
Assets= new DataTable();
Assets.Load(comm.ExecuteReader(CommandBehavior.CloseConnection));
Liabilities= new DataTable();
// some process for Liabilities
Liabilities.Load(comm.ExecuteReader(CommandBehavior.CloseConnection));
SheetView sheet = new SheetView();
sheet.DataSource = Assets;
sheet.DataMember = "Assets";

DefaultSheetDataModel model = (FarPoint.Web.Spread.Model.DefaultSheetDataModel)sheet.DataModel;

Hi All,

I think problem is using the AddRows method.
But if i use AddUnboundRows method instead of AddRows, it works..

Are there any differences with this two method on my approach ?

Thanks...

Adam, 

The AddRows method directly inserts the row in the database. Thus, all the validations and constrains needs to be fullfiled before the AddRows method executes successfully. This is not the case with AddUnboundRows. 

Regards,