open xml sdk for microsoft office
Download
Skip this Video
Download Presentation
Open XML SDK for Microsoft Office

Loading in 2 Seconds...

play fullscreen
1 / 48

Open XML SDK for Microsoft Office - PowerPoint PPT Presentation


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

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 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.
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
ad