1 / 38

Integrating ColdFusion with Microsoft Office

Integrating ColdFusion with Microsoft Office. Samuel Neff June 26, 2004. About the Presenter. Samuel Neff (sam@blinex.com) Senior Software Engineer at B-Line Express Team Macromedia Volunteer for CF Before CF, three years specific experience in Office dev

maurice
Download Presentation

Integrating ColdFusion with Microsoft Office

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. About the Presenter • Samuel Neff (sam@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, 97—2003

  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 <cfobject name="xlApp" action="create" class="Excel.Application"> <cfset xlWbs = xlApp.Workbooks> <cfset xlWb = xlWbs.add("")> <cfset xlWs = xlWb.activeSheet> <cfset c = xlWs.range("A2")> <cfset c.value2 = "Tom"> <cfset c = xlWs.range("B2")> <cfset c.value2 = 120> <cfset xlRange = xlWs.range("A1:B4")> <cfset xlCharts = xlWb.charts> <cfset xlChart = xlCharts.add()> <cfset xlChart.chartType = -4102> <cfset xlChart.setSourceData(xlRange, 2)> <cfset xlChart.location(1, "Sales By Employee")> <cfset xlWb.saveAs(fileName)> <cfset xlWb.close()> <cfcontent type="application/vnd.ms-excel" file="#fileName#"> X

  13. Automation Drawbacks • Slowest method • Single threaded—not 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?scid=kb;EN-US;257757

  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 2000—2003 • 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 <cfset chSpace = createObject("com", "OWC10.ChartSpace")> <cfset chChart = chSpace.Charts.Add()> <cfset chChart.Type = 0> <cfset chSer = chChart.SeriesCollection.Add()> <cfset chSer.SetData(1, -1, valueList(salesData.name))> <cfset chSer.SetData(2, -1, valueList(salesData.sales))> <cfset chSer.Caption ="Sales"> <cfset chSpace.Border.Color = -2> <cfset chChart.HasLegend = True> <cfset chChart.HasTitle = True> <cfset chSpace.ExportPicture(absPath, "gif", 600, 512)> <img src="#relPath#" width="600" height="512" border="0"/>

  17. OWC Drawbacks • Requires Office and Windows on server • Office 2000 version not designed for server use • http://support.microsoft.com/default.aspx?scid=kb;en-us;Q317316

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

  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 <cfset fileName = expandPath("files\03_DSO_List\FlashForCFers.ppt")> <cfobject action="create" type="com" class="DSOleFile.PropertyReader" name="propReader"> <cfset fileProps = propReader.GetDocumentProperties(fileName)> <cfoutput> #fileName# is written by #fileProps.author# </cfoutput> <cfset releaseComObject(fileProps)> <cfset releaseComObject(propReader)>

  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 97—2003

  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 <cfset tempFile = expandPath("files\04_Jet_Read_Temp.xls")> <cffile action ="upload" fileField ="fileName" destination ="#tempFile#" nameConflict ="overwrite"> <cfquery name="excelData" datasource="proxy"> SELECT Salesperson, SalesAmount FROM "Excel 8.0; DATABASE=#tempFile#; HDR=YES".[Sales$] </cfquery>

  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, 2000—2003

  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 <style> @page Section1 { size:8.5in 11.0in; margin:.5in 13.6pt 0in 13.6pt; } div.Section1 { page:Section1; } p { margin:0in 5.3pt 0in 5.3pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; } </style> <cfcontent type="application/msword" reset="no"> <cfheader name="Content-Disposition" value="attachment; filename=Labels01.doc"> ...html...

  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 <?xml version="1.0"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <Worksheet ss:Name="Sales Report"> <Table> <Row> <Cell> <Data ss:Type="String">Tom</Data> </Cell> <Cell> <Data ss:Type="String">Books</Data> </Cell> <Cell> <Data ss:Type="Number">50</Data> </Cell> </Row> </Table> </Worksheet> </Workbook>

  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 • FlashPaper—not 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?scid=kb;EN-US;257757 • OFF2000: Licensing the Office 2000 Web Components and Office Server Extensions • http://support.microsoft.com/default.aspx?scid=kb;en-us;243006 • HOWTO: Use Server-Side Charting to Generate Charts Dynamically • http://support.microsoft.com/default.aspx?scid=kb;en-us;244049 • INFO: Limitations of Office 2000 Web Components When Used Server-Side • http://support.microsoft.com/default.aspx?scid=kb;en-us;Q317316 • Microsoft Office XP Web Component Toolpack • http://www.microsoft.com/downloads/details.aspx?FamilyId=BEB5D477-2100-4586-A13C-50E56F101720&displaylang=en • Dsofile.exe Lets You Edit Office Document Properties from Visual Basic and ASP • http://support.microsoft.com/default.aspx?scid=kb;en-us;224351 • Microsoft® Office HTML and XML Reference • http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffxml/html/ofxml2k.asp?frame=true • Serving Word (HTML/XML/CSS) • http://www.rewindlife.com/archives/000032.cfm • HOWTO: Format an Excel Workbook While Streaming MIME Content • http://support.microsoft.com/default.aspx?scid=kb;en-us;271572&Product=asp • Microsoft Office 2003 XML • http://www.microsoft.com/seminar/shared/asp/view.asp?url=/seminar/en/20030801DEVT1_76/manifest.xml • XML in Office XP • http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffpro01/html/XMLOfficeXPPartI.asp • HOWTO: Use ASP to Generate a Rich Text Format (RTF) Document to Stream to Microsoft Word • http://support.microsoft.com/default.aspx?scid=kb;en-us;270906&Product=asp

  38. Thank you.Blog: http://www.rewindlife.comThis Presentation: http://www.rewindlife.com/archives/000118.cfmContact: sam@blinex.comSee me at MAX 2004: http://www.macromedia.com/go/max MXEurope 2004: http://www.mxeurope.org

More Related