HOW TO DISPLAY TWO DIFERENTES DATASOURCES ON FPSPREAD IN THE SCREEN

Posted by: jbingenieriasoftware1921 on 18 October 2019, 3:47 pm EST

  • Posted 18 October 2019, 3:47 pm EST

    i HAVE THIS CODE WORKS FINE
    globalDA = new OleDbDataAdapter();
    globalDA.SelectCommand = oCommand;
    DataSet dsGlobal = new DataSet("rtpGlobal");

    globalDA.Fill(dsGlobal);

    fpSpread1.DataSource = dsGlobal; //ALM01

    BUT I NEED TO SHOW ANOTHER ONE
  • Replied 20 October 2019, 4:10 pm EST

    Hello,

    Sorry to mention that there is no way to bind Spread with multiple datasource.

    Thanks,
    Mohit
  • Replied 30 October 2019, 3:54 am EST

    but I need to fill sheet area maybe with TableView I can do it



    but I need convert dataset on object[,]
  • Replied 30 October 2019, 11:07 am EST

    iT IS MY CODE

    BUT ON THIS LINE
    sheet.Cells[iRow2, col].Value = dataB[row2 - 4, col - 1];

    SAY ME System.IndexOutOfRangeException

    I need one table at top and another below

          private void SetSheetData(SheetView sheet)
    {
    oCommand = new OleDbCommand("[dbo].[sp_CortesxTurno]", dbConn);
    oCommand.CommandType = CommandType.StoredProcedure;
    OleDbParameter oDate1 = oCommand.Parameters.Add("@date1", OleDbType.VarChar);
    OleDbParameter oDate2 = oCommand.Parameters.Add("@date2", OleDbType.VarChar);
    OleDbParameter sParam1 = oCommand.Parameters.Add("@sParam1", OleDbType.VarChar);
    OleDbParameter sParam2 = oCommand.Parameters.Add("@sParam2", OleDbType.VarChar);
    OleDbParameter sParam3 = oCommand.Parameters.Add("@sParam3", OleDbType.VarChar);
    //OleDbParameter nUsuario = oCommand.Parameters.Add("@pUsuario_Id", OleDbType.VarChar); // usuario


    oDate1.Direction = ParameterDirection.Input;
    oDate2.Direction = ParameterDirection.Input;
    sParam1.Direction = ParameterDirection.Input;
    sParam2.Direction = ParameterDirection.Input;
    sParam3.Direction = ParameterDirection.Input;
    //nUsuario.Direction = ParameterDirection.Input;

    oDate1.Value = dateTimePicker1.Text.Replace(" p.m.", "");
    oDate2.Value = dateTimePicker2.Text.Replace(" p.m.", "");

    sParam3.Value = "";
    sParam1.Value = "";
    sParam2.Value = "3";

    turnosDA = new OleDbDataAdapter();
    turnosDA.SelectCommand = oCommand;
    DataSet dsTurnos = new DataSet("rtpTurnos");

    turnosDA.Fill(dsTurnos);

    var rowsTurnos = dsTurnos.Tables[0].Rows.Count;
    //List<CortesxTurnos> lsCTurnos = new List<CortesxTurnos>();

    object[,] data = new object[rowsTurnos, 7];
    int x = 0;
    try
    {
    foreach (DataRow row in dsTurnos.Tables[0].Rows)
    {

    for (var c = 0; c < 7; c++)
    {
    data[x, c] = row.ItemArray[c];
    }

    x++;
    }
    }
    catch (Exception ex)
    { var _ = ex.Message; }


    //object[,] data = { { "STATUS", "OPERATING", "BUDGET", "ACTUAL", "DIFFERENCE ($)", "DIFFERENCE (%)" },
    // { 0, "Advertising", 600, 545, 0, 0 },
    // { 0, "Debts", 125, 150, 0, 0 },
    // { 0, "Benefits", 100, 100, 0, 0 },
    // { 0, "Supplies", 100, 90, 0, 0 },
    // { 0, "Postage", 150, 145, 0, 0 },
    // { 0, "Rent or mortgage", 1000, 1000, 0, 0 },
    // { 0, "Sales expenses", 500, 630, 0, 0 },
    // { 0, "Taxes", 350, 375, 0, 0 },
    // { 0, "Utilities", 400, 370, 0, 0 },
    // { 0, "Other", 500, 435, 0, 0 },
    // { 0, "Total Expenses", 0, 0, 0, 0 }
    //};

    for (int row = 4; row < rowsTurnos; ++row)
    {
    for (int col = 1; col < 7; ++col)
    {
    sheet.Cells[row, col].Value = data[row - 4, col - 1];
    }
    }
    sheet.FpSpread.AllowUserFormulas = true;
    TableView table = sheet.AddTable("tblOperatingExpense", 4, 1, rowsTurnos, 6, TableStyle.TableStyleLight1);

    //sheet.Cells[15, 3].Formula = "Sum($D$6:$D$15)";
    //sheet.Cells[15, 4].Formula = "Sum($E$6:$E$15)";
    //sheet.Cells[15, 5].Formula = "Sum($F$6:$F$15)";
    /*for (int row = 5; row < 15; ++row)
    {
    sheet.Cells[row, 1].Formula = "IFERROR([ACTUAL] / [BUDGET], \"\")";
    sheet.Cells[row, 5].Formula = "[BUDGET] - [ACTUAL]";
    sheet.Cells[row, 6].Formula = "IFERROR([DIFFERENCE ($)] / [BUDGET], \"\")";
    }*/
    //fpSpread1.ActiveSheet.SetFormula(iRowCount + 1, 8, "SUM(I1:I" + iRowCount + ")");
    sheet.Cells[rowsTurnos, 6].Formula = "SUM(G4:G" + rowsTurnos + ")";
    //sheet.Cells[15, 6].Formula = "IFERROR($F$16 / $D$16, \"\")";

    //2DA Consulta
    //---------------------------------------------------------------------
    oCommand = new OleDbCommand("[dbo].[sp_CortesxTurno]", dbConn);
    oCommand.CommandType = CommandType.StoredProcedure;
    OleDbParameter oDate1b = oCommand.Parameters.Add("@date1", OleDbType.VarChar);
    OleDbParameter oDate2b = oCommand.Parameters.Add("@date2", OleDbType.VarChar);
    OleDbParameter sParam1b = oCommand.Parameters.Add("@sParam1", OleDbType.VarChar);
    OleDbParameter sParam2b = oCommand.Parameters.Add("@sParam2", OleDbType.VarChar);
    OleDbParameter sParam3b = oCommand.Parameters.Add("@sParam3", OleDbType.VarChar);
    //OleDbParameter nUsuario = oCommand.Parameters.Add("@pUsuario_Id", OleDbType.VarChar); // usuario


    oDate1b.Direction = ParameterDirection.Input;
    oDate2b.Direction = ParameterDirection.Input;
    sParam1b.Direction = ParameterDirection.Input;
    sParam2b.Direction = ParameterDirection.Input;
    sParam3b.Direction = ParameterDirection.Input;
    //nUsuario.Direction = ParameterDirection.Input;

    oDate1b.Value = dateTimePicker1.Text.Replace(" p.m.", "");
    oDate2b.Value = dateTimePicker2.Text.Replace(" p.m.", "");

    sParam3b.Value = "";
    sParam1b.Value = "";
    sParam2b.Value = "2";

    turnosDA2 = new OleDbDataAdapter();
    turnosDA2.SelectCommand = oCommand;
    DataSet dsTurnosb = new DataSet("rtpTurnos");

    turnosDA2.Fill(dsTurnosb);

    var rowsTurnosB = dsTurnosb.Tables[0].Rows.Count;
    //List<CortesxTurnos> lsCTurnos = new List<CortesxTurnos>();

    object[,] dataB = new object[rowsTurnosB, 7];
    int x2 = 0;
    try
    {
    foreach (DataRow row in dsTurnosb.Tables[0].Rows)
    {

    for (var c = 0; c < 7; c++)
    {
    dataB[x2, c] = row.ItemArray[c];
    }

    x2++;
    }
    }
    catch (Exception ex)
    { var _ = ex.Message; }


    //object[,] data = { { "STATUS", "OPERATING", "BUDGET", "ACTUAL", "DIFFERENCE ($)", "DIFFERENCE (%)" },
    // { 0, "Advertising", 600, 545, 0, 0 },
    // { 0, "Debts", 125, 150, 0, 0 },
    // { 0, "Benefits", 100, 100, 0, 0 },
    // { 0, "Supplies", 100, 90, 0, 0 },
    // { 0, "Postage", 150, 145, 0, 0 },
    // { 0, "Rent or mortgage", 1000, 1000, 0, 0 },
    // { 0, "Sales expenses", 500, 630, 0, 0 },
    // { 0, "Taxes", 350, 375, 0, 0 },
    // { 0, "Utilities", 400, 370, 0, 0 },
    // { 0, "Other", 500, 435, 0, 0 },
    // { 0, "Total Expenses", 0, 0, 0, 0 }
    //};
    // 4 + el tamaño de el array
    int iRow2 = rowsTurnos + 4;


    for (int row2= 0; row2 < rowsTurnosB; ++row2)
    {
    for (int col = 1; col < 7; ++col)
    {
    sheet.Cells[iRow2, col].Value = dataB[row2 - 4, col - 1];
    }
    iRow2++;
    }
    sheet.FpSpread.AllowUserFormulas = true;
    TableView table2 = sheet.AddTable("tblOperatingExpense2", rowsTurnos+4, 1, rowsTurnosB, 6, TableStyle.TableStyleLight1);

    //sheet.Cells[15, 3].Formula = "Sum($D$6:$D$15)";
    //sheet.Cells[15, 4].Formula = "Sum($E$6:$E$15)";
    //sheet.Cells[15, 5].Formula = "Sum($F$6:$F$15)";
    /*for (int row = 5; row < 15; ++row)
    {
    sheet.Cells[row, 1].Formula = "IFERROR([ACTUAL] / [BUDGET], \"\")";
    sheet.Cells[row, 5].Formula = "[BUDGET] - [ACTUAL]";
    sheet.Cells[row, 6].Formula = "IFERROR([DIFFERENCE ($)] / [BUDGET], \"\")";
    }*/
    //fpSpread1.ActiveSheet.SetFormula(iRowCount + 1, 8, "SUM(I1:I" + iRowCount + ")");
    //sheet.Cells[rowsTurnosB, 6].Formula = "SUM(G4:G" + rowsTurnosB + ")";
    //-------------
    }
  • Replied 30 October 2019, 11:09 am EST

    I need one table and another one below

  • Replied 30 October 2019, 4:30 pm EST

    Hello,

    Could you please check the row and column count of sheet before this code:
     for (int row2= 0; row2 < rowsTurnosB; ++row2)
    {
    for (int col = 1; col < 7; ++col)
    {
    sheet.Cells[iRow2, col].Value = dataB[row2 - 4, col - 1];
    }
    iRow2++;
    }

    If it show less number of row or column, please increase the row and column count as follow:
    sheet.Rows.Count = 7;
    sheet.Columns.Count = 7;

    Hope it helps.

    Thanks,
  • Replied 31 October 2019, 4:24 am EST

    is the same error
  • Replied 31 October 2019, 4:12 pm EST

    Hello,

    The problem is in dataB array. row2 value starts from 0. if you subtract 4 from row2, it gives the -4 value which causes this exception. Please try with the modified code:

    for (int row2= 0; row2 < rowsTurnosB; ++row2)
    {
    for (int col = 1; col < 7; ++col)
    {

    sheet.Cells[iRow2, col].Value = dataB[row2, col - 1];
    }
    iRow2++;
    }

    Hope it clarifies.

    Thanks,
    Mohit
  • Replied 1 November 2019, 6:09 am EST

    Yes I can do
  • Replied 1 November 2019, 6:10 am EST

    I receive this error when I leave the application open
  • Replied 1 November 2019, 6:51 am EST

    How can I add a header to TableView?
  • Replied 1 November 2019, 9:18 am EST

    It is show like this
    [code][/code]
  • Replied 1 November 2019, 10:19 am EST

    I found How to change name of columns with
    table2.SetColumnNames(new string[] { "some", "some"...... 
  • Replied 3 November 2019, 7:42 pm EST

    Hello,

    I think your issue is resolved now. If you are still getting the error while closing the application then could you please share the stripped-down application with us. Are you trying to dispose the element in form close event?

    Thanks,
    Mohit
  • Replied 5 November 2019, 6:56 am EST

    how can I cleared the query results for second query
  • Replied 5 November 2019, 1:13 pm EST

    I can cleared with fpSpread.ActiveSheet.Reset();

    but I can't hide header on creation of PDF

  • Replied 5 November 2019, 4:38 pm EST

    Hello,

    You can specify that only a range of cells within a sheet prints, rather than the entire sheet. After specifying the range of cells with the PrintInfo object, use the PrintSheet method as described in Printing an Entire Sheet. Please refer to the following link:
    https://help.grapecity.com/spread/SpreadNet12/WF/webframe.html#spwin-printrange.html

    Thanks,
    Mohit
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels