Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Generate Excel 2007 Sheets from MSCRM Data – OpenXML Show

Post Author: Joe D365 |

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

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

Leave a Reply

Your email address will not be published. Required fields are marked *

2 comments on “Generate Excel 2007 Sheets from MSCRM Data – OpenXML Show”

PowerObjects Recommends