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.