Range / Name Issue

[Cyril Hochet]

Hello,
I have defined a range named "Currency" in an excel worksheet and i'm trying to work with it.

Ranges such as "=Feuil1!$G2:$G20" are working fine but i'm trying to work in a more complicated way with a range of this kind : "=Feuil1!$G:$G". (that is to say the "G" column)

Then I have a problem : the recognized range in my code is a single cell : C7 (AddressGlobal = "'Feuil1'!$C$7").

Please find my excel template file (rfp_template.xls) and the resulting file (Sample.xls) in attachment.

Here is a part of my code :
//(...)
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;

IWorkbook workbook = excelEngine.Excel.Workbooks.Open(@"C:\rfp_template.xls");
IWorksheet sheet = workbook.Worksheets[0];
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
marker.AddVariable("Item", dsItems.Tables[0]);
marker.ApplyMarkers();

//here is the problem :
if (workbook.Names.Contains("Currency"))
{
IName n = workbook.Names["Currency"];
IRange r = n.RefersToRange;

IDataValidation validation = r.DataValidation; validation.ListOfValues = new string[] { "EUR", "USD", "GBP" }; validation.PromptBoxText = "currency list"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true;
}
//(...)

The result is :
- all the rows of my dataset are successfully put in the file. (great !)
- but there is only one list of currencies in the file (cell C7) (I'm expecting the whole column G to be filled with dropdown lists).

Any help would be appreciated.

cyril

files9.zip

[Melba Winshia]

Hi Cho,

Thanks for your interest in Essentail XlsIO.

I was able to reproduce the issue in 4.4.0.51. However, this issue is fixed in our new version V5. Here is the screenshot for your reference:

http://www.syncfusion.com/Support/user/uploads/Compare_407482b8.png

You can also test this by getting the beta version of Essential Studio v5 by referring the following forum thread

http://www.syncfusion.com/support/Forums/message.aspx?&MessageID=57573

Kindly let me know if you have any other questions.

Thanks,
Melba

[Cyril Hochet]

OK,
thanks.

Cyril

>Hello,
I have defined a range named "Currency" in an excel worksheet and i'm trying to work with it.

Ranges such as "=Feuil1!$G2:$G20" are working fine but i'm trying to work in a more complicated way with a range of this kind : "=Feuil1!$G:$G". (that is to say the "G" column)

Then I have a problem : the recognized range in my code is a single cell : C7 (AddressGlobal = "'Feuil1'!$C$7").

Please find my excel template file (rfp_template.xls) and the resulting file (Sample.xls) in attachment.

Here is a part of my code :
//(...)
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;

IWorkbook workbook = excelEngine.Excel.Workbooks.Open(@"C:\rfp_template.xls");
IWorksheet sheet = workbook.Worksheets[0];
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
marker.AddVariable("Item", dsItems.Tables[0]);
marker.ApplyMarkers();

//here is the problem :
if (workbook.Names.Contains("Currency"))
{
IName n = workbook.Names["Currency"];
IRange r = n.RefersToRange;

IDataValidation validation = r.DataValidation; validation.ListOfValues = new string[] { "EUR", "USD", "GBP" }; validation.PromptBoxText = "currency list"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true;
}
//(...)

The result is :
- all the rows of my dataset are successfully put in the file. (great !)
- but there is only one list of currencies in the file (cell C7) (I'm expecting the whole column G to be filled with dropdown lists).

Any help would be appreciated.

cyril

files9.zip

[Melba Winshia]

Hi Cyril,

Thanks for the update.

Kindly let me know if you have any other questions.

Thanks,
Melba