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











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









Adding the reference of service in lightswitch project








Picture showing selecting the WCF  RIA service as data source

Click to Enlarge





Picture showing selecting the WCF RIA service class

Click to Enlarge





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