locked
Export CRM Data to Excell using C# RRS feed

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