Articles → LIGHT SWITCH 2011 → Export To Excel In Lightswitch 2011 For Web

Export To Excel In Lightswitch 2011 For Web






Scenario




Picture showing the Country table in design mode and the data in the table
Click to Enlarge




Create Multiple Projects




  1. Lightswitch application (Change application type to web)
  2. Class library
  3. 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




Picture showing setting up the RIA WCF link in silverlight class library project
Click to Enlarge


Build Project




  1. Class library
  2. Silverlight class library

Adding The Reference Of Service In Lightswitch Project




  1. Right click on ‘Data Sources’ folder and click on Add Source..
  2. A popup window appears. From the sources, select WCF RIA service
  3. Picture showing selecting the WCF  RIA service as data source
    Click to Enlarge

  4. Click on Next.
  5. From the next window select RIA service name. If the name does not appear click on Add Reference button and add class library project
  6. Picture showing selecting the WCF RIA service class
    Click to Enlarge

  7. Click on Next and then click on Finish.


  8. Picture showing the data source added in the lightswitch project
    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());
	}
}




Picture showing the data exported to excel
Click to Enlarge


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

Query/Feedback


Your Email Id
 
Subject
 
Query/FeedbackCharacters remaining 250