How to create an Out of Browser Silverlight Application for generating Excel? How to create a drop down (combo-box) cell programmatically in Silverlight?
In my current project, I wanted to create a template form in excel that the user can create on his local disk. And later on populate the data according to his wish.
Depending upon the questions in the template, the cells should be customized. E.g. If the question's answer could have 2 choices, say Yes/No then the cell corresponding to such a question must show a drop down for answer choices Yes/No, all done programmatically, behind the scenes.
Assumptions:
1) Silverlight 5 is used
2) Visual Studio 2010 is the development environment.
3) A Silverlight 5 project is created and the reference to Microsoft.CSharp is already added.
A) How to create a out-of-browser Silverlight application?
What is an out-of-browser application: An out-of-browser application (web application) is one which allows users of that application to access the local hard disk. The exact definition could be easily found on several sites.
Configuring Out-of-Browser settings in VS2010:
For the already created Silverlight project, open the project properties by right clicking the mouse. Select the Silverlight tab on the left.
In Silverlight build options: Select the appropriate Silverlight Target Version, in our case Silverlight 5
Then, tick the "Enable running application out of browser" check box. This would enable the "out-of-browser Settings" button. click on that.
In the Out-of-browser settings new window tick the "Require elevated trust when running outside the browser.
Click OK.
B) How to create a drop down in the cell
In silverlight we have to use the AutomationFactory for creating an Excel Application object. Here is the code for it.
dynamic ExcelApp;
try
{
ExcelApp = AutomationFactory.GetObject("Excel.Application");
}
catch
{
ExcelApp = AutomationFactory.CreateObject("Excel.Application");
}
// control visibility of the excel tool
ExcelApp.Visible = true;
dynamic m_Workbook = ExcelApp.workbooks;
m_Workbook.Add();
dynamic m_WorkSheet = ExcelApp.ActiveSheet;
dynamic cell = null;
Now the tricky part is to do the drop down. And here is the code snippet for creating a drop down in cell [2,2] with options Yes and No.
dynamic list = new List<string>();
list.Add("Yes");
list.Add("No");
dynamic flatList = string.Join(",", list.ToArray());
cell = m_WorkSheet.Cells[2, 2];
cell.Validation.Delete();
cell.Validation.Add( 3, //XlDVType.xlValidateList,
3, //XlDVAlertStyle.xlValidAlertInformation,
1, //XlFormatConditionOperator.xlBetween,
flatList,
Type.Missing);
cell.Validation.IgnoreBlank = true;
cell.Validation.InCellDropdown = true;
You will find many links that use the Microsoft.Office.Interop.Excel class library for excel Data validation but sadly I did not find a way to reference this library in Silverlight 5.
Hence, I had to hard-code the XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, and XlFormatConditionOperator.xlBetween enums in the cell.Validation.Add( ) function above to get all working.
So hope this solves a lot of head ache for others.
In my current project, I wanted to create a template form in excel that the user can create on his local disk. And later on populate the data according to his wish.
Depending upon the questions in the template, the cells should be customized. E.g. If the question's answer could have 2 choices, say Yes/No then the cell corresponding to such a question must show a drop down for answer choices Yes/No, all done programmatically, behind the scenes.
Assumptions:
1) Silverlight 5 is used
2) Visual Studio 2010 is the development environment.
3) A Silverlight 5 project is created and the reference to Microsoft.CSharp is already added.
A) How to create a out-of-browser Silverlight application?
What is an out-of-browser application: An out-of-browser application (web application) is one which allows users of that application to access the local hard disk. The exact definition could be easily found on several sites.
Configuring Out-of-Browser settings in VS2010:
For the already created Silverlight project, open the project properties by right clicking the mouse. Select the Silverlight tab on the left.
In Silverlight build options: Select the appropriate Silverlight Target Version, in our case Silverlight 5
Then, tick the "Enable running application out of browser" check box. This would enable the "out-of-browser Settings" button. click on that.
In the Out-of-browser settings new window tick the "Require elevated trust when running outside the browser.
Click OK.
B) How to create a drop down in the cell
In silverlight we have to use the AutomationFactory for creating an Excel Application object. Here is the code for it.
dynamic ExcelApp;
try
{
ExcelApp = AutomationFactory.GetObject("Excel.Application");
}
catch
{
ExcelApp = AutomationFactory.CreateObject("Excel.Application");
}
// control visibility of the excel tool
ExcelApp.Visible = true;
dynamic m_Workbook = ExcelApp.workbooks;
m_Workbook.Add();
dynamic m_WorkSheet = ExcelApp.ActiveSheet;
dynamic cell = null;
Now the tricky part is to do the drop down. And here is the code snippet for creating a drop down in cell [2,2] with options Yes and No.
dynamic list = new List<string>();
list.Add("Yes");
list.Add("No");
dynamic flatList = string.Join(",", list.ToArray());
cell = m_WorkSheet.Cells[2, 2];
cell.Validation.Delete();
cell.Validation.Add( 3, //XlDVType.xlValidateList,
3, //XlDVAlertStyle.xlValidAlertInformation,
1, //XlFormatConditionOperator.xlBetween,
flatList,
Type.Missing);
cell.Validation.IgnoreBlank = true;
cell.Validation.InCellDropdown = true;
You will find many links that use the Microsoft.Office.Interop.Excel class library for excel Data validation but sadly I did not find a way to reference this library in Silverlight 5.
Hence, I had to hard-code the XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, and XlFormatConditionOperator.xlBetween enums in the cell.Validation.Add( ) function above to get all working.
So hope this solves a lot of head ache for others.
