Open xml sdk for microsoft office
This presentation is the property of its rightful owner.
Sponsored Links
1 / 48

Open XML SDK for Microsoft Office PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on
  • Presentation posted in: General

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 [email protected] Agenda. About the Open XML SDK Library (5 minutes)

Download Presentation

Open XML SDK for Microsoft Office

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

  • [email protected]


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: [email protected]

    • Come to the front and add your name and email address to the list.

    • www.aspnetwiki.com. Will be posted this weekend.


Questions

Questions


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

  • [email protected]


  • Login