Home > CRM Integrations > Generate Excel 2007 Sheets from MSCRM Data – OpenXML Show

Generate Excel 2007 Sheets from MSCRM Data – OpenXML Show


Office Open XML is an XML-based file format specification for electronic documents such as memos, reports, books, spreadsheets, charts, presentations and word processing documents. The specification has been developed by Microsoft as a successor of its binary office file formats and was published by Ecma International as the Ecma 376 standard in December 2006. The format specification is available for free at Ecma International. 

Let me introduce you the technique I have used to generate the Excel 2007 sheet from CRM data. I have downloaded an Excel package. ExcelPackage provides server-side generation of Excel 2007 spreadsheets. See http://www.codeplex.com/ExcelPackage for details. 

Specifying Output Directory and Template Directory

 Output directory is where you place the generated excel file and Template  directory is where you place the template file. Template file is simply a .xlsx file without data. Use following code to specify both directories. 

DirectoryInfo outputDir = new DirectoryInfo(@”GenerateExceloutput”);
DirectoryInfo templateDir = new DirectoryInfo(@”GenerateExceltemplates”); 
FileInfo newFile = new FileInfo(outputDir.FullName + @”File-” + strRecordID + “.xlsx”);
FileInfo template = new FileInfo(templateDir.FullName + @”TempReport.xlsx”);
if (!template.Exists)         
throw new Exception(“Template file does not exist! i.e. template.xlsx”);
 

Write data to Excel File 

ExcelPackage contains library functions for accessing worksheet, cells etc. For details please have a look into the Excel package code. Uses following code to write data to individual cell in excel worksheet. 

using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))     

{           
try
           
{
               
 
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[“Sheet1”];
               
if (worksheet != null)
               
{
                   
worksheet.Cell(2, 9).Value = ClearText(strData);                   
                   
xlPackage.Save();
               
}
           
}
           
catch (Exception ex)
           
{
            }         
}
 

Adding Rows to Sheet          

worksheet.InsertRow(iRow); 

Return Excel File for Download 

Simply redirect the response object in ASP.NET to the excel file generated. 

Response.Redirect(“http://” + Request.Url.Host + “:” + Request.Url.Port + “/” + “GenerateExcel/output/” + newFile.Name); 

All cell styles, formates and pictures objects that you set once in Template will remain the same in the generated file. 

Please find below my sample quote geneation application. I have placed a button at Quotes toolbar named Generate Quote. It will generate quote report in excel 2007 with the Quote data from crm currently selected.

 pic1.jpg 

My program fetches data from MSCRM through web services and populated my Excel sheet. It then returns my generated excel sheet with options to open or download.

pic2.jpg

Advertisements
  1. August 23, 2007 at 7:14 pm

    Dude, I wondered what happened. You hadn’t blogged in almost three months! :o)

  2. August 23, 2007 at 7:21 pm

    Hi Gandalfe,

    I was busy in a couple of time critical projects. so never get time to write anything for CRM folks. now i am back and planning to write some good stuff learned from those projects.

    Thanks for your consideration.

    Ayaz

  3. August 29, 2007 at 3:02 pm

    Hi Ayaz,

    Runnig this sample on my crm server i got this error
    How can I solve it
    Thanks

    Server Error in ‘/’ Application.
    ——————————————————————————–

    Configuration Error
    Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

    Parser Error Message: Unrecognized configuration section ‘connectionStrings’

    Source Error:

    Line 4:
    Line 5:
    Line 6:
    Line 7:
    Line 8:

    Source File: c:\inetpub\wwwroot\excel\web.config Line: 6

    ——————————————————————————–
    Version Information: Microsoft .NET Framework Version:1.1.4322.2379; ASP.NET Version:1.1.4322.2379

  4. September 5, 2007 at 3:27 pm

    please install .net 3.0 at server and use VS.NET 2005 for development of this application

  5. Salvatore
    September 7, 2007 at 8:35 am

    Thank you very much Ayaz

  6. September 27, 2007 at 5:18 am

    Hi Ayaz,

    Searching on the web about Customization on Microsoft CRM I found your blog, congrats! excellent blog.

    Do you have any customization on the Service Calendar Form? I have a very tough customization, I have to send an IP and filter the view using this IP. I’m working on this for about 2 weeks and I didn’t find anything that could help me. If you have any information on this, I would really appreciate.

    Thanks

    -Caleb

  7. September 27, 2007 at 7:49 pm

    Hi Caleb,

    Thanks for the blog. Please write me a bit more detail regarding the customization you are planning and the issues. Although its a tough part of MSCRM but i will try my level best to sort out some solution for you.

    Thanks and best regards,

    Ayaz

  8. September 27, 2007 at 10:33 pm

    Hi Ayaz,

    Thanks for your response, the thing is: on the Service Calendar Form the client wants to filter the default view for Facility/Equipment and the view All Facility/Equipment. They have a lot of sites and users to do the service on those sites, so they asign an IP on every client (PC) and based on that IP they want to filter that view.

    So what I need is to make a link between Sites and these IPs, it’s a new table, but I cannot do that because I can’t make any customization on that table. Maybe if I make another table with a Replication on that table could be solved, but this is not supported by Microsoft, I don’t care if it run I’ll use it (I’m a little desperate!!). What I’m trying to do is take the IP of the client (javascript) and sending to the view, but I’m trying to get some info on Site Entity and Service Calendar Form, and I don’t find anything that could help me. I received 8531 and nothing there, on the Microsoft site, nothing.

    This is my horrible situation, so if you have something to help me, I’ll really appreciate.

    -Caleb

  9. October 26, 2007 at 9:22 am

    good

  10. October 26, 2007 at 9:22 am

    raj

  11. November 21, 2007 at 4:11 pm

    thanks for the GREAT post! Very useful…

  12. Ryan
    June 9, 2008 at 10:36 am

    Hi;

    I am doing exactly the same thing using this ExcelPackage object and it produces the Xls file nicely.
    Now business has a requirement to use RMS (Microsoft Rights Management Server) to protect the Xls document that is being produced.
    Do you have any idea where in this OOXML format to put this RMS protection information?

    Thanks
    Ryan

  13. Timo
    November 12, 2008 at 4:28 pm

    Hi Ayaz,

    I’m new to MsCRM 4.0, i wanna know how to pass some entity information to a custom workflow and then let that workflow do something with these information.

    Many thanks

  14. February 15, 2010 at 7:49 pm

    Just what I was looking for – thanks

  1. July 6, 2008 at 12:02 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: