Open xml sdk for microsoft office
Download
1 / 48

Open XML SDK for Microsoft Office - PowerPoint PPT Presentation


  • 217 Views
  • Uploaded on

Open XML SDK for Microsoft Office. Learn How to Create Word and Excel Documents. Introductions. John DeVight Herndon, Virginia Senior Principal Software Engineer at ManTech Telerik MVP www.aspnetwiki.com John.DeVight@gmail.com. Agenda. About the Open XML SDK Library (5 minutes)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Open XML SDK for Microsoft Office' - hova


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Open xml sdk for microsoft office

Open XML SDK for Microsoft Office

Learn How to Create

Word and Excel Documents


Introductions
Introductions

  • John DeVight

  • Herndon, Virginia

  • Senior Principal Software Engineer at ManTech

  • Telerik MVP

  • www.aspnetwiki.com

  • John.DeVight@gmail.com


Agenda
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


About the open xml sdk library what is it
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


Telerik products demo
Telerik Products” Demo


Getting started installing the open xml sdk library
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.


Getting started reference the open xml sdk library
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.


Getting started open xml sdk 2 0 productivity tool
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
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
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 tool1
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.


Create word documents using bookmarks
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
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 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 bookmarks1
Create Word Documents:Using Bookmarks

  • Demo Creating Bookmarks in a Word Document


Create word documents using bookmarks2
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 wordlite
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.


Create word documents using bookmarks and wordlite1
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");


Create word documents using bookmarks3
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.


Create word documents using bookmarks and wordlite2
Create Word Documents:Using Bookmarks and WordLite

  • Review WordController.ProductBookmarks Controller Action.


Create word documents using bookmarks and wordlite3
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.


Create word documents using bookmarks4
Create Word Documents: Using Bookmarks

  • Demo Allowing End User to Create Templates


Creating word documents using xslt
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 Word Documents:Using XSLT : “Real World” Implementation

  • Creating Government Contracting Documents


Creating word documents review open xml package definition
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.


Creating word documents using xslt1
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>


Creating word documents using xslt2
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


Creating word documents using xslt3
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 xslt4
Creating Word Documents:Using XSLT

  • Demo Implementing XSL Template.

  • Review WordController.ProductXslt Controller Action.


Creating word documents using xslt5
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>


Creating word documents using xslt6
Creating Word Documents:Using XSLT

  • Demo Implementing C# Support to XSLT

  • Review WordController.ProductXsltWithScript Controller Action.


Creating word documents using xslt7
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>


Creating word documents using xslt8
Creating Word Documents:Using XSLT

  • Demo Locating the Header and Footer

  • Review WordController.ProductXsltWithHeaderFooter Controller Action.


Creating word documents using xslt9
Creating Word Documents:Using XSLT

  • Combining XSLT and Bookmarks

  • Adding Image

  • Review WordController.ProductXsltAddImage Controller Action.


Creating excel documents options
Creating Excel Documents: Options

  • Open XML SDK 2.0 Productivity Tool for MS Office

  • Using the SDK

  • Using SpreadsheetLight


Creating excel documents open xml sdk 2 0 productivity tool
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 tool1
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.


Creating excel documents using the sdk
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


Creating excel documents using the sdk1
Creating Excel Documents:Using the SDK

  • Demo Using SDK

  • Review ExcelController.ProductWidgetsUsingSdk Controller Action.


Creating excel documents using spreadsheetlight
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).”


Creating excel documents using spreadsheetlight1
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


Creating excel documents using spreadsheetlight2
Creating Excel Documents:Using SpreadsheetLight

  • Demo using SpreadsheetLight

  • Review ExcelController.ProductWidgetsSpreadsheetLight Controller Action.

  • Review ExcelController.SalesByYear Controller Action.


Creating excel documents using spreadsheetlight3
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.


Creating excel documents using spreadsheetlight4
Creating Excel Documents:Using SpreadsheetLight

  • Demo creating Chart

  • Review ExcelController.SalesByYearWithChart Controller Action.


Presentation and source code
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.



Future presentations
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.


Thank you
Thank You

  • John DeVight

  • www.aspnetwiki.com

  • John.DeVight@gmail.com