1 / 38

Integrating ColdFusion with Microsoft Office

Integrating ColdFusion with Microsoft Office Samuel Neff November 19-21, 2003 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

albert
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 November 19-21, 2003

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  4. Why integrate with Office? • Consistent reproduction of reports Integrating ColdFusion with Microsoft Office (SS216W)

  5. Why integrate with Office? • Give users editable documents Integrating ColdFusion with Microsoft Office (SS216W)

  6. Why integrate with Office? • Leverage features of the Office suite Integrating ColdFusion with Microsoft Office (SS216W)

  7. Why integrate with Office? • Gain control unavailable with HTML Integrating ColdFusion with Microsoft Office (SS216W)

  8. Why so many options? • Long lived product • Evolution of technologies • Techniques intended for specific integration environments • "Flavor of the week" technology Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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#"> Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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 2000—2003 • Spreadsheet, PivotTable, Chart Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  16. Office Web Components 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"/> Integrating ColdFusion with Microsoft Office (SS216W)

  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?scid=kb;en-us;Q317316 Integrating ColdFusion with Microsoft Office (SS216W)

  18. OLE Properties • COM interface to any OLE compound document • Simple two-way transfer of small amounts of data • Word, Excel, PowerPoint, 97—2003 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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)> Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  22. Jet Engine • Familiar database interaction • Efficient read and write of data • Excel 97—2003 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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> Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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... Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  31. XML How-To • Generate XML document • Serve XML document • Example, sales report • 06_XML_Report.cfm Integrating ColdFusion with Microsoft Office (SS216W)

  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> Integrating ColdFusion with Microsoft Office (SS216W)

  33. XML Drawbacks • Does not support charts • For XP, Excel only • Excel and Word in Office 2003 • Custom XML Schema Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  35. Comparison Matrix Integrating ColdFusion with Microsoft Office (SS216W)

  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) Integrating ColdFusion with Microsoft Office (SS216W)

  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 Integrating ColdFusion with Microsoft Office (SS216W)

  38. Thank you.

More Related