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


Posted By  -  Karan Gupta
 
Posted On  -  Saturday, July 09, 2016

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250