Answered by:
Export CRM Data to Excell using C#

Question
-
Hi,
I need to export Crm Entity (lead,contact) recors to excell sheet using C#.
I got the bellow link but it doesn't work.
http://suresh-kumar-patra.blogspot.in/2012/06/take-automatic-backup-for-record-in-ms.html
any other method or plugin code available . .
Grateful for any help!!
Wednesday, September 4, 2013 8:54 AM
Answers
-
try below code,
static void WriteContactsToExcel() { //Create Excel object var excel = new Application(); if (excel == null) throw new ApplicationException("Error occurred while instantiating Excel object"); //Set these to speed up the Excel application. excel.Visible = false; excel.ScreenUpdating = false; excel.DisplayAlerts = false; object misValue = System.Reflection.Missing.Value; object missing = Type.Missing; string destinationFilePath = @"C:\Excel\WriteExcelExample.xlsx"; Workbook workbook = excel.Workbooks.Add(); Worksheet worksheet = workbook.Sheets[1] as Worksheet; //Create Excel Sheet Header Columns//mentioned as in FetchXML worksheet.Cells[1, "A"] = "ContactId"; worksheet.Cells[1, "B"] = "FirstName"; worksheet.Cells[1, "C"] = "LastName"; worksheet.Cells[1, "D"] = "FullName"; worksheet.Cells[1, "E"] = "Phone"; worksheet.Cells[1, "F"] = "Fax"; //Get your CRM connection IOrganizationService orgService = GetCRMConnection(); string fetchXML = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'> <entity name='contact'> <attribute name='contactid' /> <attribute name='firstname' /> <attribute name='lastname' /> <attribute name='fullname' /> <attribute name='telephone1' /> <attribute name='fax' /> </entity> </fetch>"; var fetchExp = new FetchExpression(fetchXML); var contactEntityCollection = orgService.RetrieveMultiple(fetchExp); for (int i = 1; i < contactEntityCollection.Entities.Count; i++) { worksheet.Cells[i, "A"] = contactEntityCollection.Entities[i].Id.ToString(); worksheet.Cells[i, "B"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("firstname"); worksheet.Cells[i, "C"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("lastname"); worksheet.Cells[i, "D"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("fullname"); worksheet.Cells[i, "E"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("telephone1"); worksheet.Cells[i, "F"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("fax"); Console.WriteLine("Writing " + i + " Contact " + contactEntityCollection.Entities[i].Id.ToString()); } if (!string.IsNullOrEmpty(destinationFilePath)) { //Save the Excel File workbook.SaveAs(destinationFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue); workbook.Close(missing, missing, missing); excel.Quit(); } }
- Proposed as answer by VSomireddy Thursday, December 12, 2013 6:09 PM
- Marked as answer by kMAT1 Friday, December 13, 2013 4:58 AM
Thursday, December 12, 2013 6:09 PM
All replies
-
Hi Vinoth,
You can give this a try, Just use C#/workflow assembly to retrieve all columns of required entities(Lead,Contact), And add to the 2 Entity Collection(Lead,Contact). Retrieve Each Entity Record with Values and use below sample Export to Excel Function
public void ExportToExcel(DataTable dt) { if (dt.Rows.Count > 0) { string filename = "DownloadCRMData.xls"; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); DataGrid dgGrid = new DataGrid(); dgGrid.DataSource = dt; dgGrid.DataBind(); //Get the HTML for the control. dgGrid.RenderControl(hw); //Write the HTML back to the browser. //Response.ContentType = application/vnd.ms-excel; Response.ContentType = "application/vnd.ms-excel"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ""); this.EnableViewState = false; Response.Write(tw.ToString()); Response.End(); } }
Thanks
T.Vinoth
Wednesday, September 4, 2013 9:13 AM -
Hi,
let me try and Update you.
This code seams to retrieve data in data set and copy to an file .
If any other direct method available let me know .. .
Wednesday, September 4, 2013 9:29 AM -
Or you can use a codeless option by building a SSRS report and schedule it to run and export the result in csv or xls.
Eric UNG [Senior Analyst Programmer :: Sydney, Australia]
- Edited by Eric Ung Wednesday, September 4, 2013 9:36 AM
Wednesday, September 4, 2013 9:36 AM -
Hi ,
Using CRM plugin is possible (code). as i don need a schedule backup. Or can go for Custom Workflow process.
Wednesday, September 4, 2013 9:52 AM -
any sample plugin (or) C# code available. .Wednesday, September 4, 2013 1:28 PM
-
Any Reply . .
Thanks .
Thursday, December 12, 2013 5:00 PM -
Hi,
Plugin and workflow fires on some events.. You can fire workflow on demand as well.. Why do you want it through workflow or Plugin..?
You can Develop SSRS with design Or custom application that will export data to excel.. will you please elaborate further I can provide some guidance.
Thanks!
Thursday, December 12, 2013 5:37 PM -
try below code,
static void WriteContactsToExcel() { //Create Excel object var excel = new Application(); if (excel == null) throw new ApplicationException("Error occurred while instantiating Excel object"); //Set these to speed up the Excel application. excel.Visible = false; excel.ScreenUpdating = false; excel.DisplayAlerts = false; object misValue = System.Reflection.Missing.Value; object missing = Type.Missing; string destinationFilePath = @"C:\Excel\WriteExcelExample.xlsx"; Workbook workbook = excel.Workbooks.Add(); Worksheet worksheet = workbook.Sheets[1] as Worksheet; //Create Excel Sheet Header Columns//mentioned as in FetchXML worksheet.Cells[1, "A"] = "ContactId"; worksheet.Cells[1, "B"] = "FirstName"; worksheet.Cells[1, "C"] = "LastName"; worksheet.Cells[1, "D"] = "FullName"; worksheet.Cells[1, "E"] = "Phone"; worksheet.Cells[1, "F"] = "Fax"; //Get your CRM connection IOrganizationService orgService = GetCRMConnection(); string fetchXML = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'> <entity name='contact'> <attribute name='contactid' /> <attribute name='firstname' /> <attribute name='lastname' /> <attribute name='fullname' /> <attribute name='telephone1' /> <attribute name='fax' /> </entity> </fetch>"; var fetchExp = new FetchExpression(fetchXML); var contactEntityCollection = orgService.RetrieveMultiple(fetchExp); for (int i = 1; i < contactEntityCollection.Entities.Count; i++) { worksheet.Cells[i, "A"] = contactEntityCollection.Entities[i].Id.ToString(); worksheet.Cells[i, "B"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("firstname"); worksheet.Cells[i, "C"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("lastname"); worksheet.Cells[i, "D"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("fullname"); worksheet.Cells[i, "E"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("telephone1"); worksheet.Cells[i, "F"] = contactEntityCollection.Entities[i].GetAttributeValue<string>("fax"); Console.WriteLine("Writing " + i + " Contact " + contactEntityCollection.Entities[i].Id.ToString()); } if (!string.IsNullOrEmpty(destinationFilePath)) { //Save the Excel File workbook.SaveAs(destinationFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue); workbook.Close(missing, missing, missing); excel.Quit(); } }
- Proposed as answer by VSomireddy Thursday, December 12, 2013 6:09 PM
- Marked as answer by kMAT1 Friday, December 13, 2013 4:58 AM
Thursday, December 12, 2013 6:09 PM