Integrating ColdFusion with Microsoft Office - PowerPoint PPT Presentation

About This Presentation
Title:

Integrating ColdFusion with Microsoft Office

Description:

'Microsoft does not currently recommend, and does not support, Automation of ... Even works on Unix servers! Word, Excel, PowerPoint, 2000 2003. 27 ... – PowerPoint PPT presentation

Number of Views:172
Avg rating:3.0/5.0
Slides: 39
Provided by: kristi202
Category:

less

Transcript and Presenter's Notes

Title: Integrating ColdFusion with Microsoft Office


1
Integrating ColdFusion with Microsoft Office
  • Samuel Neff
  • November 19-21, 2003

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 and 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

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

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
  • 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
Office Web Components 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
Office Web Components 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
Office Web Components 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

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
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.
Write a Comment
User Comments (0)
About PowerShow.com