Forums Home / Spread / Spread for WinForms
Posted by: jbingenieriasoftware1921 on 18 October 2019, 3:47 pm EST
jbingenieriasoftware1921
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
mohitg
Posted 20 October 2019, 4:10 pm EST
Hello,
Sorry to mention that there is no way to bind Spread with multiple datasource.
Thanks,
Mohit
jbingenieriasoftware1921
Posted 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[,]
jbingenieriasoftware1921
Posted 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 + ")";
//-------------
}
mohitg
Posted 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,
mohitg
Posted 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
jbingenieriasoftware1921
Posted 1 November 2019, 6:51 am EST
How can I add a header to TableView?
jbingenieriasoftware1921
Posted 1 November 2019, 9:18 am EST
It is show like this
```[img]https://gccontent.blob.core.windows.net/forum-uploads/file-d6d48749-7c4c-4c3b-916b-154f22cbce54.jpg[/img]jbingenieriasoftware1921
Posted 1 November 2019, 10:19 am EST
I found How to change name of columns with
table2.SetColumnNames(new string[] { "some", "some"......
mohitg
Posted 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
jbingenieriasoftware1921
Posted 5 November 2019, 6:56 am EST
how can I cleared the query results for second query
mohitg
Posted 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
