Forums Home / Document Solutions / Document Solutions for Excel, .NET Edition
Posted by: joao.barros on 26 July 2023, 1:53 am EST
joao.barros
Posted 26 July 2023, 1:53 am EST
I have a template that has a field in the sheet name. This field works fine when placed in one of the cells. However, when it’s placed in the sheet name, it gives an error indicating that it does not exist in the table.
prabhat.sharma
Posted 26 July 2023, 2:34 pm EST
Hello,
We do not face the issue at our end in applying the sheet binding. Please find the attached sample implementing the same and let us know if you are doing anything different.
If you still face any issues then please modify the attached sample accordingly to replicate your scenario.
Regards,
Prabhat Sharma.
DataSetIssue.zip
joao.barros
Posted 30 July 2023, 10:00 pm EST
If the field has no value, error. What should happen is to set a default value when the field is empty.
joao.barros
Posted 31 July 2023, 12:03 am EST
I send the following code and I send the template.
I’ve been running some tests and found that if I remove the reference to datasource p in the template, it doesn’t throw an error. If I keep the datasource p, it throws the error: System.ArgumentException: ‘The ‘CodEstado’ column doesn’t belong to the table.’
private string ConnectionString()
{
System.Data.SqlClient.SqlConnectionStringBuilder builder =
new System.Data.SqlClient.SqlConnectionStringBuilder();
builder["Data Source"] = @"alg4\sql2017";
builder["Initial Catalog"] = "MT205_2";
builder["Persist Security Info"] = false;
builder["Password"] = "";
builder["User"] = "sa";
builder["Application Name"] = "Analogon.Excel";
return builder.ConnectionString;
}
private System.Data.DataTable GetDataTable(System.Data.SqlClient.SqlConnection pCon, string pSQL)
{
System.Data.DataTable dt;
using (System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(pSQL, pCon))
{
dt = new System.Data.DataTable();
adapter.Fill(dt);
}
return dt;
}
private void button3_Click(object sender, EventArgs e)
{
GrapeCity.Documents.Excel.Workbook.SetLicenseKey(LicenseKey);
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open(@"C:\Lab\GRA\GRA209\reports\xLstAPAGuia02_t.xlsx");
IWorkbook wb;
using (var con = new System.Data.SqlClient.SqlConnection(ConnectionString()))
{
con.Open();
var dt = GetDataTable(con, "SELECT QuantidadeAtual=(CASE WHEN quantidadeCrr=0 THEN Quantidade ELSE quantidadeCrr END),isNull(codigoOperacaoCrr,codigoOperacao) CodigoOperacaoAtual,isNull(codigoResiduoLerCrr,codigoResiduoLer) codigoResiduoLerAtual,APAGuia.* FROM APAGuia ");
workbook.AddDataSource("d", dt);
var dtp = GetDataTable(con, "SELECT Instituicao Empresa,* FROM Parametros");
workbook.AddDataSource("p", dtp);
wb = workbook.GenerateReport();
wb.Save(@"c:\anexos\xLstAPAGuia02.xlsx", SaveFileFormat.Xlsx);
con.Close();
}
}
Stack:
System.Data.dll!System.Data.DataRow.GetDataColumn(string columnName)
System.Data.dll!System.Data.DataRow.this[string].get(string columnName) GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.as6.ar4(string A_0) GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.atb.a(string[] A_0, object A_1)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.atb.a(string A_0, object A_1) GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.auo.a(GrapeCity.Documents.Excel.gz A_0, string A_1, string A_2, object A_3)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.auo.a(string A_0)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aut.a(GrapeCity.Documents.Excel.atl A_0, GrapeCity.Documents.Excel.aug A_1, GrapeCity.Documents.Excel.auo A_2)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aud.a(GrapeCity.Documents.Excel.aug A_0, GrapeCity.Documents.Excel.auo A_1)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aud.a(System.Collections.Generic.List<GrapeCity.Documents.Excel.aug> A_0, GrapeCity.Documents.Excel.h9 A_1, GrapeCity.Documents.Excel.at5 A_2, System.Collections.Generic.Dictionary<string, System.Collections.Generic.Dictionary<string, GrapeCity.Documents.Excel.atl>> A_3, System.Collections.Generic.List<GrapeCity.Documents.Excel.aub> A_4, GrapeCity.Documents.Excel.auo A_5)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aus.axj()
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.at7.aw2()
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.Workbook.ProcessTemplate()prabhat.sharma
Posted 31 July 2023, 3:41 pm EST
Hello,
It seems there is a simple request on the support portal. So we have replied to this on the support case on the given link:
https://www.grapecity.com/my-account/my-support/case/61d5af6a-5c2d-ee11-bdf4-000d3a4e0eeb
Please continue the thread there only to avoid the confusion.
Regards,
Prabhat Sharma.
joao.barros
Posted 31 July 2023, 7:54 pm EST
And the resolution of this problem?
prabhat.sharma
Posted 1 August 2023, 9:08 pm EST
Hello Joao,
Apologies for the misunderstanding of the case.
>>If the field has no value, error. What should happen is to set a default value when the field is empty.
The Default value is used when a particular field is empty or missing from the data source. Please see the following attached sample demonstrating the same.
>>I send the following code and I send the template.
Thanks for the code but we do not see any template file attached. Please find the attached sample and modify it accordingly as per use-case so that we can assist you further.
Regards,
Prabhat Sharma.
joao.barros
Posted 2 August 2023, 3:14 am EST
And this problem?
I send the following code and I send the template.
I’ve been running some tests and found that if I remove the reference to datasource p in the template, it doesn’t throw an error. If I keep the datasource p, it throws the error: System.ArgumentException: ‘The ‘CodEstado’ column doesn’t belong to the table.’
private string ConnectionString()
{
System.Data.SqlClient.SqlConnectionStringBuilder builder =
new System.Data.SqlClient.SqlConnectionStringBuilder();
builder[“Data Source”] = @“alg4\sql2017”;
builder[“Initial Catalog”] = “MT205_2”;
builder[“Persist Security Info”] = false;
builder[“Password”] = “”;
builder[“User”] = “sa”;
builder[“Application Name”] = “Analogon.Excel”;
return builder.ConnectionString;
} private System.Data.DataTable GetDataTable(System.Data.SqlClient.SqlConnection pCon, string pSQL) { System.Data.DataTable dt; using (System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(pSQL, pCon)) { dt = new System.Data.DataTable(); adapter.Fill(dt); } return dt; } private void button3_Click(object sender, EventArgs e) { GrapeCity.Documents.Excel.Workbook.SetLicenseKey(LicenseKey); var workbook = new GrapeCity.Documents.Excel.Workbook(); workbook.Open(@"C:\Lab\GRA\GRA209\reports\xLstAPAGuia02_t.xlsx"); IWorkbook wb; using (var con = new System.Data.SqlClient.SqlConnection(ConnectionString())) { con.Open(); var dt = GetDataTable(con, "SELECT QuantidadeAtual=(CASE WHEN quantidadeCrr=0 THEN Quantidade ELSE quantidadeCrr END),isNull(codigoOperacaoCrr,codigoOperacao) CodigoOperacaoAtual,isNull(codigoResiduoLerCrr,codigoResiduoLer) codigoResiduoLerAtual,APAGuia.* FROM APAGuia "); workbook.AddDataSource("d", dt); var dtp = GetDataTable(con, "SELECT Instituicao Empresa,* FROM Parametros"); workbook.AddDataSource("p", dtp); wb = workbook.GenerateReport(); wb.Save(@"c:\anexos\xLstAPAGuia02.xlsx", SaveFileFormat.Xlsx); con.Close(); } }
Stack:
System.Data.dll!System.Data.DataRow.GetDataColumn(string columnName)
System.Data.dll!System.Data.DataRow.this[string].get(string columnName) GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.as6.ar4(string A_0) GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.atb.a(string A_0, object A_1)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.atb.a(string A_0, object A_1) GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.auo.a(GrapeCity.Documents.Excel.gz A_0, string A_1, string A_2, object A_3)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.auo.a(string A_0)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aut.a(GrapeCity.Documents.Excel.atl A_0, GrapeCity.Documents.Excel.aug A_1, GrapeCity.Documents.Excel.auo A_2)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aud.a(GrapeCity.Documents.Excel.aug A_0, GrapeCity.Documents.Excel.auo A_1)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aud.a(System.Collections.Generic.List<GrapeCity.Documents.Excel.aug> A_0, GrapeCity.Documents.Excel.h9 A_1, GrapeCity.Documents.Excel.at5 A_2, System.Collections.Generic.Dictionary<string, System.Collections.Generic.Dictionary<string, GrapeCity.Documents.Excel.atl>> A_3, System.Collections.Generic.List<GrapeCity.Documents.Excel.aub> A_4, GrapeCity.Documents.Excel.auo A_5)
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.aus.axj()
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.at7.aw2()
GrapeCity.Documents.Excel.dll!GrapeCity.Documents.Excel.Workbook.ProcessTemplate()
prabhat.sharma
Posted 2 August 2023, 8:58 pm EST
Hello Joao,
>>I remove the reference to datasource p in the template, it doesn’t throw an error.
What do you mean by removing reference to data source p in the template? Are you doing some updation in the template Excel or in code?
>>If I keep the datasource p, it throws the error: System.ArgumentException: ‘The ‘CodEstado’ column doesn’t belong to the table.’
We did not face such an issue so we would request you to update the last given sample and template accordingly to replicate the same behavior so that we can assist you further accordingly.
Regards,
Prabhat Sharma.
joao.barros
Posted 3 August 2023, 12:31 am EST
If you open the template, you will see that it has two datasources p and d. If you run the code with the template, you get the error that the field placed in the name of the sheet is not found. Removing the datasource p does not give an error. The field that is on the sheet belongs to datasource d.
To simulate this error just create a model with two datasources. The first one (p) with a line of data to fill in the header and the second one for the detail (d). If you put a field of datasource d gives error. Removing the reference to datasource p from the excel model does not give an error.
prabhat.sharma
Posted 3 August 2023, 4:09 pm EST
Hello Joao,
>If you open the template, you will see that it has two data sources p and d.
We have checked this post and your support post but there is no such template file attached from your end and the template file that we have attached contains only a single data source.
However, based on your latest comments, we have replicated the issue at our end and reported this to the development team to look into this matter.
Please check the attached sample and confirm if you are having a similar use case.
[Internal Tracking ID: DOCXLS-8659]
Regards,
Prabhat Sharma.
joao.barros
Posted 3 August 2023, 9:22 pm EST
I modified your example to simulate this error. Send attached.DataTable_csharp.zip
prabhat.sharma
Posted 3 August 2023, 9:33 pm EST - Updated 3 August 2023, 9:38 pm EST
Hello Joao,
As per the developers, we do not support such usage for multiple data sources. The template cell must be constrained by its sheet name if the sheet name is a template.
https://www.grapecity.com/documents-api-excel/docs/online/template-fields.html
Please see the attached screenshot for the same.
As a workaround, you need to merge two data sources into one and change the sheet name of the template file.
Regards,
Prabhat Sharma.
joao.barros
Posted 4 August 2023, 2:43 am EST
Thanks.
I got around the situation passing an object as datasource header.
