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
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
Adding the reference of service in lightswitch project
Click to Enlarge
Click to Enlarge
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