Integrating ColdFusion with Microsoft Office - PowerPoint PPT Presentation

About This Presentation
Title:

Integrating ColdFusion with Microsoft Office

Description:

Before CF, three years specific experience in Office dev ... application or component (including ASP, DCOM, and NT Services), because Office ... – PowerPoint PPT presentation

Number of Views:174
Avg rating:3.0/5.0
Slides: 39
Provided by: kristi200
Category:

less

Transcript and Presenter's Notes

Title: Integrating ColdFusion with Microsoft Office


1
Integrating ColdFusion with Microsoft Office
  • Samuel Neff
  • June 26, 2004

2
About the Presenter
  • Samuel Neff (sam_at_blinex.com)
  • Senior Software Engineer at B-Line Express
  • Team Macromedia Volunteer for CF
  • Before CF, three years specific experience in
    Office dev
  • Word, Excel, Visual Basic for Applications

3
Agenda
  • Introduction Why integrate?
  • Automation
  • Office Web Components
  • OLE Document Properties
  • Microsoft Jet
  • HTML/XML/CSS Hybrid
  • Pure XML
  • Comparisons, alternatives, and resources

4
Why integrate with Office?
  • Consistent reproduction of reports

5
Why integrate with Office?
  • Give users editable documents

6
Why integrate with Office?
  • Leverage features of the Office suite

7
Why integrate with Office?
  • Gain control unavailable with HTML

8
Why so many options?
  • Long lived product
  • Evolution of technologies
  • Techniques intended for specific integration
    environments
  • "Flavor of the week" technology

9
Why don't we teach just the best?
  • All options have pros and cons
  • Trade off features, performance, and complexity
  • Apply most appropriate option for situation
  • not one size fits all

10
Automation
  • Launch MS Office on the server
  • Control through COM
  • Most common and most powerful
  • Can do anything an Office user can do
  • Record Macro to learn office model
  • Word, Excel, PowerPoint, 972003

11
Automation How-To
  • Start or connect to Excel
  • Create or open a workbook
  • Manipulate the data
  • Save to a file
  • Serve the file
  • Example, create a pie chart
  • 01_Autmation_Create_Excel_Chart.cfm

X
12
Automation Example
  • ltcfobject name"xlApp" action"create"
    class"Excel.Application"gt
  • ltcfset xlWbs xlApp.Workbooksgt
  • ltcfset xlWb xlWbs.add("")gt
  • ltcfset xlWs xlWb.activeSheetgt
  • ltcfset c xlWs.range("A2")gt
  • ltcfset c.value2 "Tom"gt
  • ltcfset c xlWs.range("B2")gt
  • ltcfset c.value2 120gt
  • ltcfset xlRange xlWs.range("A1B4")gt
  • ltcfset xlCharts xlWb.chartsgt
  • ltcfset xlChart xlCharts.add()gt
  • ltcfset xlChart.chartType -4102gt
  • ltcfset xlChart.setSourceData(xlRange, 2)gt
  • ltcfset xlChart.location(1, "Sales By Employee")gt

X
13
Automation Drawbacks
  • Slowest method
  • Single threadednot scalable
  • Requires read/write from file system
  • Requires Office and Windows on server
  • Not safe for unattended execution
  • "Microsoft does not currently recommend, and does
    not support, Automation of Microsoft Office
    applications from any unattended, non-interactive
    client application or component (including ASP,
    DCOM, and NT Services), because Office may
    exhibit unstable behavior and/or deadlock when
    run in this environment."
  • http//support.microsoft.com/default.aspx?scidkb
    EN-US257757

14
Office Web Components (OWC)
  • COM interface to MS Office data objects
  • Much MS Office functionality
  • Safe for server side use
  • Thread safe Office XP 2003 versions only
  • Office 20002003
  • Spreadsheet, PivotTable, Chart

15
OWC How-To
  • Very similar to Automation
  • Create a reference to an OWC components
  • Manipulate the object
  • Save the file
  • Serve the file
  • Example, create a bar chart
  • 02_OWC_Chart_Image.cfm

16
OWC Example
  • ltcfset chSpace createObject("com",
    "OWC10.ChartSpace")gt
  • ltcfset chChart chSpace.Charts.Add()gt
  • ltcfset chChart.Type 0gt
  • ltcfset chSer chChart.SeriesCollection.Add()gt
  • ltcfset chSer.SetData(1, -1, valueList(salesData.na
    me))gt
  • ltcfset chSer.SetData(2, -1, valueList(salesData.sa
    les))gt
  • ltcfset chSer.Caption "Sales"gt
  • ltcfset chSpace.Border.Color -2gt
  • ltcfset chChart.HasLegend Truegt
  • ltcfset chChart.HasTitle Truegt
  • ltcfset chSpace.ExportPicture(absPath, "gif", 600,
    512)gt
  • ltimg src"relPath" width"600" height"512"
    border"0" /gt

17
OWC Drawbacks
  • Requires Office and Windows on server
  • Office 2000 version not designed for server use
  • http//support.microsoft.com/default.aspx?scidkb
    en-usQ317316

18
OLE Properties
  • COM interface to any OLE compound document
  • Simple two-way transfer of small amounts of data
  • Word, Excel, PowerPoint, 972003

19
OLE Properties How To
  • Instantiate a PropertyReader object
  • Get a reference to the document properties
  • Read or set properties as desired
  • Release the COM object
  • WARNING There is no "close" method
  • The file is closed when the object is released
  • Can only be done on-command in CFMX 6.1
  • Example, read the author of documents
  • 03_DSO_List_With_Info.cfm

20
OLE Properties Example
  • ltcfset fileName expandPath("files\03_DSO_List\Fl
    ashForCFers.ppt")gt
  • ltcfobject
  • action"create"
  • type"com"
  • class"DSOleFile.PropertyReader"
  • name"propReader"gt
  • ltcfset fileProps propReader.GetDocumentPropertie
    s(fileName)gt
  • ltcfoutputgt
  • fileName is written by fileProps.author
  • lt/cfoutputgt
  • ltcfset releaseComObject(fileProps)gt
  • ltcfset releaseComObject(propReader)gt

21
OLE Properties Drawbacks
  • Appropriate only for small specific data
  • Often requires VBA code within the document
    template
  • Requires existing file as template for create
  • Requires Windows on the server

22
Jet Engine
  • Familiar database interaction
  • Efficient read and write of data
  • Excel 972003

23
Jet Engine How-To
  • Create a datasource to an empty MS Access
    database
  • Proxy datasource, used for dynamic connections
  • Query the Excel file as if it's a database
  • Specify connection info in the FROM clause
  • Example, reading uploaded data
  • 04_Jet_Read.cfm

24
Jet Engine Example
  • ltcfset tempFile expandPath("files\04_Jet_Read_Te
    mp.xls")gt
  • ltcffile
  • action "upload"
  • fileField "fileName"
  • destination "tempFile"
  • nameConflict "overwrite"gt
  • ltcfquery name"excelData" datasource"proxy"gt
  • SELECT Salesperson, SalesAmount
  • FROM "Excel 8.0 DATABASEtempFile
    HDRYES".Sales
  • lt/cfquerygt

25
Jet Engine Drawbacks
  • Very specific formatting requirements
  • Highly subject to user error, particularly
    reading
  • Requires existing file as template for create
  • Requires Windows server
  • Technically feasible on Unix boxes, but requires
    special drivers

26
HTML/XML/CSS
  • Fast and familiar technologies
  • Code is more often reusable
  • Create most common documents
  • Easy to create examples of target output
  • Save as HTML
  • Does not require anything more than ColdFusion on
    server
  • Even works on Unix servers!
  • Word, Excel, PowerPoint, 20002003

27
HTML/XML/CSS How-To
  • Create HTML Document
  • Include MS Office specific CSS and XML as needed
  • Serve to HTML
  • Example, create mailing labels
  • 05_HTML_Labels.cfm

28
HTML/XML/CSS Example
  • ltstylegt
  • _at_page Section1
  • size8.5in 11.0in
  • margin.5in 13.6pt 0in 13.6pt
  • div.Section1
  • pageSection1
  • p
  • margin0in 5.3pt 0in 5.3pt
  • mso-paginationwidow-orphan
  • font-size12.0pt
  • font-family"Times New Roman"
  • lt/stylegt
  • ltcfcontent type"application/msword" reset"no"gt
  • ltcfheader name"Content-Disposition"
    value"attachment filenameLabels01.doc"gt

29
HTML/XML/CSS Drawbacks
  • Limited documentation
  • Can't reproduce all functionality
  • Charts, forms, some labels
  • Some features require Web Archive filter in
    Office 2000
  • Some Office 2000 installations require an MHT
    extension for Multipart-MIME files

30
XML
  • Pure XML implementation
  • Create nearly any document
  • Easy to create examples of target output
  • Save as XML
  • Does not require anything more than ColdFusion on
    server
  • Even works on Unix servers!
  • Excel 2002, Word, Excel, PowerPoint 2003

31
XML How-To
  • Generate XML document
  • Serve XML document
  • Example, sales report
  • 06_XML_Report.cfm

32
XML Example
  • lt?xml version"1.0"?gt
  • ltWorkbook
  • xmlns"urnschemas-microsoft-comofficespreadsh
    eet"
  • xmlnsss"urnschemas-microsoft-comofficesprea
    dsheet"gt
  • ltWorksheet ssName"Sales Report"gt
  • ltTablegt
  • ltRowgt
  • ltCellgt
  • ltData ssType"String"gtTomlt/Datagt
  • lt/Cellgt
  • ltCellgt
  • ltData ssType"String"gtBookslt/Datagt
  • lt/Cellgt
  • ltCellgt
  • ltData ssType"Number"gt50lt/Datagt
  • lt/Cellgt
  • lt/Rowgt
  • lt/Tablegt
  • lt/Worksheetgt

33
XML Drawbacks
  • Does not support charts
  • For XP, Excel only
  • Excel and Word in Office 2003
  • Custom XML Schema

34
XML Mapping
  • New feature in Office 2003
  • Use an XML Schema to map data elements to fields
    or cells
  • After mapping, import a conforming XML file
  • Fields automatically filled in
  • Data can be changed and re-exported back to XML
  • Requires a lot of user interaction

35
Comparison Matrix
Auto OWC OLE JET HTML XML Map
Safe for server
Office on Server
Windows on Server
Read data
Write data
Word
Excel
PowerPoint
Leverage ever-day knowledge
Auto-generate examples
Often requires client-code
Subject to user error
36
Alternatives
  • RTF
  • Supported on most word processors
  • Marker based text encoding
  • PDF
  • Supported by most users
  • COM, Executable, XSL-FO
  • SWF
  • Supported by most users
  • FlashPapernot server-side yet, but possibly in
    future
  • XSL-FO ?SWF
  • RVML (http//www.kinesissoftware.com)

37
Resources
  • CF Comet Coldfusion COM
  • http//www.cfcomet.com/
  • INFO Considerations for Server-Side Automation
    of Office
  • http//support.microsoft.com/default.aspx?scidkb
    EN-US257757
  • OFF2000 Licensing the Office 2000 Web Components
    and Office Server Extensions
  • http//support.microsoft.com/default.aspx?scidkb
    en-us243006
  • HOWTO Use Server-Side Charting to Generate
    Charts Dynamically
  • http//support.microsoft.com/default.aspx?scidkb
    en-us244049
  • INFO Limitations of Office 2000 Web Components
    When Used Server-Side
  • http//support.microsoft.com/default.aspx?scidkb
    en-usQ317316
  • Microsoft Office XP Web Component Toolpack
  • http//www.microsoft.com/downloads/details.aspx?Fa
    milyIdBEB5D477-2100-4586-A13C-50E56F101720displa
    ylangen
  • Dsofile.exe Lets You Edit Office Document
    Properties from Visual Basic and ASP
  • http//support.microsoft.com/default.aspx?scidkb
    en-us224351
  • Microsoft Office HTML and XML Reference
  • http//msdn.microsoft.com/library/default.asp?url
    /library/en-us/dnoffxml/html/ofxml2k.asp?frametru
    e
  • Serving Word (HTML/XML/CSS)
  • http//www.rewindlife.com/archives/000032.cfm
  • HOWTO Format an Excel Workbook While Streaming
    MIME Content

38
Thank you.Blog http//www.rewindlife.comThis
Presentation http//www.rewindlife.com/archives/0
00118.cfm Contact sam_at_blinex.comSee me at
MAX 2004 http//www.macromedia.com/go/max MXE
urope 2004 http//www.mxeurope.org
Write a Comment
User Comments (0)
About PowerShow.com