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