Articles → LIGHT SWITCH 2011 → Export To Excel In Lightswitch 2011 For Web
Export To Excel In Lightswitch 2011 For Web
Scenario
Click to Enlarge
Create Multiple Projects
- Lightswitch application (Change application type to web)
- Class library
- Silverlight class library
Adding Domain Service Class In Class Library
namespace Service {
using System.ServiceModel.DomainServices.Hosting;
using System.ServiceModel.DomainServices.Server;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
// TODO: Create methods containing your application logic.
[EnableClientAccess()]
public class MyDomainService: DomainService {
public int ExportToExcel() {
DataSet ds = new DataSet();
using(SqlConnection connection = new SqlConnection(@"
your_db_connection_string;")) {
connection.Open();
using(SqlCommand command = new SqlCommand("Select * From Country", connection)) {
using(SqlDataAdapter adapter = new SqlDataAdapter(command)) {
adapter.Fill(ds);
if (ds != null) {
if (ds.Tables.Count > 0) {
Application ExcelApp = new Application();
Workbook ExcelWorkBook = null;
Worksheet ExcelWorkSheet = null;
ExcelApp.Visible = true;
ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ExcelWorkSheet = ExcelWorkBook.Worksheets[1];
int count = 1;
foreach(DataRow row in ds.Tables[0].Rows) {
ExcelWorkSheet.Cells[count, 1] = row["CountryName"].ToString();
count++;
}
ExcelWorkBook.SaveAs(string.Format(@"c:\temp\{0}.xlsx", System.Guid.NewGuid()));
ExcelWorkBook.Close();
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelWorkSheet);
Marshal.ReleaseComObject(ExcelWorkBook);
Marshal.ReleaseComObject(ExcelApp);
}
}
}
}
}
return 1;
}
}
}
Setting Up The RIA WCF Link In Silverlight Class Library Project
Click to Enlarge
Build Project
- Class library
- Silverlight class library
Adding The Reference Of Service In Lightswitch Project
- Right click on ‘Data Sources’ folder and click on Add Source..
- A popup window appears. From the sources, select WCF RIA service
Click to Enlarge
- Click on Next.
- From the next window select RIA service name. If the name does not appear click on Add Reference button and add class library project
Click to Enlarge
- Click on Next and then click on Finish.
Click to Enlarge
Calling The Export To Excel Code
partial void ExportToExcel_Execute() {
Dispatchers.Main.BeginInvoke(() = >{
MyDomainContext context = new MyDomainContext();
InvokeOperation < int > invoke = context.ExportToExcel(OpCompleted, null);
});
}
private void OpCompleted(InvokeOperation < int > invokeOp) {
if (!invokeOp.HasError) {
this.ShowMessageBox(invokeOp.Value.ToString());
}
}
Click to Enlarge