Open XML SDK for Microsoft Office

1. Open XML SDK for Microsoft Office Learn How to Create Word and Excel Documents

2. Introductions • John DeVight • Herndon, Virginia • Senior Principal Software Engineer at ManTech • Telerik MVP • www.aspnetwiki.com • John.DeVight@gmail.com

3. Agenda • About the Open XML SDK Library (5 minutes) • “Telerik Products” Demo (5 minutes) • Getting Started (10 minutes) • Installing the Open XML SDK Library • Reference the Open XML SDK Library • Open XML SDK 2.0 Productivity Tool • Creating Word Documents (45 minutes) • Using “Open XML SDK 2.0 Productivity Tool for MS Office” • Using Bookmarks • Using XSLT • Creating Excel Documents (20 minutes) • Using “Open XML SDK 2.0 Productivity Tool for MS Office” • Using the SDK Directly • Using SpreadsheetLight

4. About the Open XML SDK Library:What is it? • “The Open XML SDK 2.0 simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML SDK 2.0 encapsulates many common tasks that developers perform on Open XML packages, so that you can perform complex operations with just a few lines of code.” 1 • An Open XML Package is a zip file containing XML documents, images and other files needed to display the document in a Microsoft Office application. 1. http://msdn.microsoft.com/en-us/library/office/bb448854(v=office.14).aspx

5. “Telerik Products” Demo

6. Getting Started:Installing the Open XML SDK Library • Open XML SDK 2.0 for Microsoft Office Download • Install the “Open XML SDK” from the OpenXMLSDKv2.msi installer. • Install the “Open XML SDK Productivity Tool for Microsoft Office” from the OpenXMLSDKTool.msi installer.

7. Getting Started:Reference the Open XML SDK Library • In the .NET application, reference: * WordLite is a class library that I created to make it easier to work with Word Documents. ** SpreadsheetLight is freely available under the MIT License that simplifies using the Open XML SDK Library when creating Excel Documents.

8. Getting Started:Open XML SDK 2.0 Productivity Tool • Open Microsoft Office document and generate C# code to create the same document. • View the Open XML SDK Documentation.

9. Creating Word Documents: Options • Open XML SDK 2.0 Productivity Tool for MS Office • Bookmarks • XSLT

10. Creating Word Documents: Using “Open XML SDK 2.0 Productivity Tool” • Pros • All the code is generated for you. • Fast and Easy. • Cons • The tool generates a lot of code. • Every change requires the code to be updated and deployed.

11. Creating Word Documents: Using “Open XML SDK 2.0 Productivity Tool” • Create and Save Word Document • Open the Word Document using “Open XML SDK 2.0 Productivity Tool” • In “Document Explorer”, right-click on root and select “Reflect Code”. • Create console app in Visual Studio. Add references to required assemblies. • Create new class for the “generated code”. • Instantiate class and call CreatePackage from Main.

12. Create Word Documents:Using Bookmarks • Pros • Easy to create and update the “template” document without having to write code. • The end user could create their own “template” documents. • Cons • Requires code to know about all the bookmarks.

13. Create Word Documents:Using Bookmarks: “Real World” Implementation • Application for police departments to create Wanted Posters.

14. Create Word Documents:Using Bookmarks – Create “Template” Document • Create Word Document • Insert Bookmark • Insert -> Bookmark • Give the bookmark a name • Click “Add” button • Making Bookmarks visible • File -> Options • Advanced -> Show document content • Show bookmarks

15. Create Word Documents:Using Bookmarks • Demo Creating Bookmarks in a Word Document

16. Create Word Documents:Using Bookmarks • Header and Footer Bookmarks • Create Header • Insert -> Header • Insert Bookmark • Insert -> Bookmark • Give the bookmark a name • Click “Add” button • Design -> Close Header and Footer

17. Create Word Documents:Using Bookmarks and WordLite • Open a word document with Bookmarks. • Replace bookmarks with text. • Replace image. • Get the header and replace bookmark with text. • Get the footer and replace bookmark with text.

18. Create Word Documents:Using Bookmarks and WordLite using (WordDocument doc = new WordDocument()) { doc.Open(@"C:\docs\mytemplate.docx"); doc.Bookmarks["Name"].ReplaceWithText("Kendo UI"); doc.ReplaceImage("image1", @"C:\images\kendo.png"); var bookmarks = doc.Header("header2").Bookmarks; bookmarks["Header"].ReplaceWithText("My Header"); doc.Close(); buffer = doc.GetBuffer(); } Response.Buffer = true; Response.AddHeader("Content-Disposition", "attachment; filename=Product.docx"); return File(buffer, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", "Product.docx");

19. Create Word Documents:Using Bookmarks • Identifying the header and footer • Open the document in the “Open XML SDK 2.0 Productivity Tool”. • Open each header and footer and look for the bookmark. • Identifying the image name (option 1) • Open the document in the “Open XML SDK 2.0 Productivity Tool” • Locate the image. • Identifying the image name (option 2) • Change the extension on the Word document to .zip • Extract the contents of the .zip file and look at each image.

20. Create Word Documents:Using Bookmarks and WordLite • Review WordController.ProductBookmarks Controller Action.

21. Create Word Documents:Using Bookmarks and WordLite • Add bookmark to the document. • Replace bookmark with image. doc.Bookmarks["Image"].ReplaceWithImage(@“C:\Images", “kendo.png”, "image/png"); • Review WordController. ProductBookmarksAddImage Controller Action.

22. Create Word Documents: Using Bookmarks • Demo Allowing End User to Create Templates

23. Creating Word Documents: Using XSLT • Pros • Templates can be changed without having to recompile. • .NET code is simplified. • Good approach when the document format does not change much over time. • Cons • Templates can get a bit complicated to manipulate.

24. Creating Word Documents:Using XSLT : “Real World” Implementation • Creating Government Contracting Documents

25. Creating Word Documents:Review Open XML Package Definition • An Open XML Package is a zip file containing XML documents, images and other files needed to display the document in a Microsoft Office application.

26. Creating Word Documents:Using XSLT • Create Word Document • Put “placeholders” for values • Save document • Create XSLT file • Rename .docx to .zip • Open zip file and get the word/document.xml file • Rename with .xslt extension • Replace: • <?xml version="1.0" encoding="UTF-8" standalone="yes"?> • With: • <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> • <xsl:template match=“/"> • Add to the end of the file: • </xsl:template> • </xsl:stylesheet>

27. Creating Word Documents:Using XSLT • products.xml <productList> <products> <product id=“…“ name=“…“ summary=“...“ image=“…“ price=“…“ overview=“..." /> </products> </productList> • XPath editor - http://qutoric.com/xmlquire/ • XPath examples: • Select product names • Select product where id = 5 • Select products that have widgets

28. Creating Word Documents:Using XSLT • <xsl:value-of select=“”/> • <xsl:if test=“”></xsl:if> • <xsl:for-each select=“”></xsl:for-each>

29. Creating Word Documents:Using XSLT • Demo Implementing XSL Template. • Review WordController.ProductXslt Controller Action.

30. Creating Word Documents:Using XSLT • Adding C# Support to XSLT • xmlns:msxsl="urn:schemas-microsoft-com:xslt“ • xmlns:cs="urn:custom-csharp“ <msxsl:script language="C#" implements-prefix="cs"> <![CDATA[ public string FormatPrice(int price) { return string.Format("{0:c0}", price); } ]]> </msxsl:script>

31. Creating Word Documents:Using XSLT • Demo Implementing C# Support to XSLT • Review WordController.ProductXsltWithScript Controller Action.

32. Creating Word Documents:Using XSLT • Adding Header and Footer • Create document with header and footer • Rename .docx to .zip • Open zip file and get the word/header.xml fileand word/footer.xml file • Rename with .xslt extension • Replace: • <?xml version="1.0" encoding="UTF-8" standalone="yes"?> • With: • <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> • <xsl:template match=“/"> • Add to the end of the file: • </xsl:template> • </xsl:stylesheet>

33. Creating Word Documents:Using XSLT • Demo Locating the Header and Footer • Review WordController.ProductXsltWithHeaderFooter Controller Action.

34. Creating Word Documents:Using XSLT • Combining XSLT and Bookmarks • Adding Image • Review WordController.ProductXsltAddImage Controller Action.

35. Creating Excel Documents: Options • Open XML SDK 2.0 Productivity Tool for MS Office • Using the SDK • Using SpreadsheetLight

36. Creating Excel Documents: “Open XML SDK 2.0 Productivity Tool” • Why? Learn how the SDK works.

37. Creating Excel Documents: “Open XML SDK 2.0 Productivity Tool” • Create and Save Excel Document • Open the Excel Document using “Open XML SDK 2.0 Productivity Tool” • In “Document Explorer”, right-click on root and select “Reflect Code”. • Create console app in Visual Studio. Add references to required assemblies. • Create new class for the “generated code”. • Instantiate class and call CreatePackage from Main.

38. Creating Excel Documents:Using the SDK • Code Project article: Creating basic Excel workbook with Open XML by Mika Wendelius • Extended Excel.cs to: • CreateWorkbook using a Stream • Add additional styles for headers

39. Creating Excel Documents:Using the SDK • Demo Using SDK • Review ExcelController.ProductWidgetsUsingSdk Controller Action.

40. Creating Excel Documents:Using SpreadsheetLight • http://spreadsheetlight.com/ • “SpreadsheetLight is an open source spreadsheet library/component for .NET Framework written in C#. It is freely available and uses the MIT License. It generates Open XML spreadsheets that are compatible with Microsoft Excel 2007/2010/2013 (and even LibreOffice Calc).”

41. Creating Excel Documents:Using SpreadsheetLight • Features used: • Setting Document Properties • Rename Worksheet • Set Column Heading Styles • Set Column Widths • Format Currency Cells • Define Formulas • Create Charts • Protect Worksheet

42. Creating Excel Documents:Using SpreadsheetLight • Demo using SpreadsheetLight • Review ExcelController.ProductWidgetsSpreadsheetLight Controller Action. • Review ExcelController.SalesByYear Controller Action.

43. Creating Excel Documents:Using SpreadsheetLight • Adding a Chart • SLChart • CreateChart – define the range for the data to be used. • SetChartType – set the type of chart to create • SetChartPosition – where the chart will appear in the worksheet • SetChartStyle – style for the chart. • SLDocument.InsertChart – insert the chart into the current worksheet.

44. Creating Excel Documents:Using SpreadsheetLight • Demo creating Chart • Review ExcelController.SalesByYearWithChart Controller Action.

45. Presentation and Source Code • Where can I find the presentation and source code? • Email me at: John.DeVight@gmail.com • Come to the front and add your name and email address to the list. • www.aspnetwiki.com. Will be posted this weekend.

46. Questions

47. Future Presentations • Creating MVC Extensions for the jQuery UI Library that can be used with the Razor and ASPX (WebForms) View Engines. • Creating mobile application games using the “Crafty” JavaScript Gaming Engine and PhoneGap.

48. Thank You • John DeVight • www.aspnetwiki.com • John.DeVight@gmail.com