Hi,
I have just started working with data validation in in excel/XlsIO in Blazor. I am trying to include a formula in a cell (it works), that is dependent from another cell which includes a data validation list. However, the cell with Formula always shows 0 when the file is downloaded, and only works properly once I hit the Enable Editing button.
Is there a way around this? This is the code I am using at the moment:
protected async Task CreateExcel()
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["C3"].Text = "USD";
IDataValidation listValidation = worksheet.Range["C3"].DataValidation;
worksheet.Range["C1"].Text = "Data Validation List in C3";
worksheet.Range["C1"].AutofitColumns();
listValidation.ListOfValues = new string[] { "USD", "EUR" };
worksheet.Range["A1"].Text = "True";
worksheet.Range["A1"].Formula = @"=IF(C3=""USD"",123553,123445*.85)";
//Shows the error message
listValidation.ErrorBoxText = "Choose the value from the list";
listValidation.ErrorBoxTitle = "ERROR";
//listValidation.PromptBoxText = "Data validation for list";
listValidation.IsPromptBoxVisible = true;
listValidation.ShowPromptBox = true;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
stream.Position = 0;
await JS.SaveAs("TestExcel.xlsx", stream.ToArray());
workbook.Close();
await stream.DisposeAsync();
stream.Close();
}
}
Thank you for your help in advance.
Eric
