Open XML SDK for Microsoft Office Learn How

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

Introductions � John De. Vight � Herndon, Virginia � Senior Principal Software Engineer at Man. Tech � Telerik MVP � www. aspnetwiki. com � John. De. Vight@gmail. com

Agenda the Open XML SDK Library (5 minutes) � “Telerik Products” Demo (5 minutes) � Getting Started (10 minutes) � About ◦ Installing the Open XML SDK Library ◦ Reference the Open XML SDK Library ◦ Open XML SDK 2. 0 Productivity Tool � Creating Word Documents (45 minutes) � Creating Excel Documents (20 minutes) ◦ Using “Open XML SDK 2. 0 Productivity Tool for MS Office” ◦ Using Bookmarks ◦ Using XSLT ◦ Using “Open XML SDK 2. 0 Productivity Tool for MS Office” ◦ Using the SDK Directly ◦ Using Spreadsheet. Light

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 Open XML Package is a zip file containing XML documents, images and other files needed to display the document in a Microsoft Office application. � An 1. http: //msdn. microsoft. com/en-us/library/office/bb 448854(v=office. 14). aspx

“Telerik Products” Demo

Getting Started: Installing the Open XML SDK Library � Open XML SDK 2. 0 for Microsoft Office Download � Install the “Open XML SDK” from the Open. XMLSDKv 2. msi installer. � Install the “Open XML SDK Productivity Tool for Microsoft Office” from the Open. XMLSDKTool. msi installer.

Getting Started: Reference the Open XML SDK Library � In the. NET Assembly application, reference: Visual Studio 2012 Document. Format. Open. Xml Assemblies -> Extensions Windows. Base Assemblies -> Framework * Word. Lite Browse ** Spreadsheet. Light Browse * Word. Lite is a class library that I created to make it easier to work with Word Documents. ** Spreadsheet. Light is freely available under the MIT License that simplifies using the Open XML SDK Library when creating Excel Documents.

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.

Creating Word Documents: Options � Open XML SDK 2. 0 Productivity Tool for MS Office � Bookmarks � XSLT

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.

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 Create. Package from Main.

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.

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

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

Create Word Documents: Using Bookmarks � Demo Creating Bookmarks in a Word Document

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

Create Word Documents: Using Bookmarks and Word. Lite � 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.

Create Word Documents: Using Bookmarks and Word. Lite using (Word. Document doc = new Word. Document()) { doc. Open(@"C: docsmytemplate. docx"); doc. Bookmarks["Name"]. Replace. With. Text("Kendo UI"); doc. Replace. Image("image 1", @"C: imageskendo. png"); var bookmarks = doc. Header("header 2"). Bookmarks; bookmarks["Header"]. Replace. With. Text("My Header"); } doc. Close(); buffer = doc. Get. Buffer(); Response. Buffer = true; Response. Add. Header("Content-Disposition", "attachment; filename=Product. docx"); return File(buffer, "application/vnd. openxmlformatsofficedocument. wordprocessingml. document", "Product. docx");

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

Create Word Documents: Using Bookmarks and Word. Lite � Review Word. Controller. Product. Bookmarks Controller Action.

Create Word Documents: Using Bookmarks and Word. Lite � Add bookmark to the document. � Replace bookmark with image. doc. Bookmarks["Image"]. Replace. With. Image(@“C: Images", “kendo. png”, "image/png"); � Review Word. Controller. Product. Bookmarks. Add. Image Controller Action.

Create Word Documents: Using Bookmarks � Demo Allowing End User to Create Templates

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.

Creating Word Documents: Using XSLT : “Real World” Implementation � Creating Government Contracting Documents

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

Creating Word Documents: Using XSLT � Create Word Document � Create XSLT file ◦ Put “placeholders” for values ◦ Save document ◦ ◦ 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. w 3. org/1999/XSL/Transform"> � <xsl: template match=“/"> ◦ Add to the end of the file: � </xsl: template> �</xsl: stylesheet>

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

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

Creating Word Documents: Using XSLT � Demo Implementing XSL Template. � Review Action. Word. Controller. Product. Xslt Controller

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#" implementsprefix="cs"> <![CDATA[ public string Format. Price(int price) { return string. Format("{0: c 0}", price); } ]]> </msxsl: script>

Creating Word Documents: Using XSLT � Demo Implementing C# Support to XSLT � Review Word. Controller. Product. Xslt. With. Script Controller Action.

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 file and 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. w 3. org/1999/XSL/Transform"> � <xsl: template match=“/"> ◦ Add to the end of the file: � </xsl: template> �</xsl: stylesheet>

Creating Word Documents: Using XSLT � Demo Locating the Header and Footer � Review Word. Controller. Product. Xslt. With. Header. Footer Controller Action.

Creating Word Documents: Using XSLT � Combining XSLT and Bookmarks � Adding Image � Review Word. Controller. Product. Xslt. Add. Image Controller Action.

Creating Excel Documents: Options � Open XML SDK 2. 0 Productivity Tool for MS Office � Using the SDK � Using Spreadsheet. Light

Creating Excel Documents: “Open XML SDK 2. 0 Productivity Tool” � Why? Learn how the SDK works.

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 Create. Package from Main.

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

Creating Excel Documents: Using the SDK � Demo � Review Using SDK Excel. Controller. Product. Widgets. Using. Sdk Controller Action.

Creating Excel Documents: Using Spreadsheet. Light � http: //spreadsheetlight. com/ � “Spreadsheet. Light 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 Libre. Office Calc). ”

Creating Excel Documents: Using Spreadsheet. Light � Features ◦ ◦ ◦ ◦ used: Setting Document Properties Rename Worksheet Set Column Heading Styles Set Column Widths Format Currency Cells Define Formulas Create Charts Protect Worksheet

Creating Excel Documents: Using Spreadsheet. Light � Demo using Spreadsheet. Light � Review Excel. Controller. Product. Widgets. Spreadsheet. Light Controller Action. � Review Action. Excel. Controller. Sales. By. Year Controller

Creating Excel Documents: Using Spreadsheet. Light � Adding a Chart ◦ SLChart �Create. Chart – define the range for the data to be used. �Set. Chart. Type – set the type of chart to create �Set. Chart. Position – where the chart will appear in the worksheet �Set. Chart. Style – style for the chart. �SLDocument. Insert. Chart – insert the chart into the current worksheet.

Creating Excel Documents: Using Spreadsheet. Light � Demo � Review creating Chart Excel. Controller. Sales. By. Year. With. Chart Controller Action.

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

Questions

Future Presentations � Creating MVC Extensions for the j. Query UI Library that can be used with the Razor and ASPX (Web. Forms) View Engines. � Creating mobile application games using the “Crafty” Java. Script Gaming Engine and Phone. Gap.

Thank You � John De. Vight � www. aspnetwiki. com � John. De. Vight@gmail. com