Integrating Cold Fusion with Microsoft Office Samuel Neff

Integrating Cold. Fusion 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 – Word, Excel, Visual Basic for Applications Integrating Cold. Fusion with Microsoft Office (SS 216 W) 2

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 Cold. Fusion with Microsoft Office (SS 216 W) 3

Why integrate with Office? § Consistent reproduction of reports Integrating Cold. Fusion with Microsoft Office (SS 216 W) 4

Why integrate with Office? § Give users editable documents Integrating Cold. Fusion with Microsoft Office (SS 216 W) 5

Why integrate with Office? § Leverage features of the Office suite Integrating Cold. Fusion with Microsoft Office (SS 216 W) 6

Why integrate with Office? § Gain control unavailable with HTML Integrating Cold. Fusion with Microsoft Office (SS 216 W) 7

Why so many options? § Long lived product § Evolution of technologies § Techniques intended for specific integration § environments "Flavor of the week" technology Integrating Cold. Fusion with Microsoft Office (SS 216 W) 8

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 Cold. Fusion with Microsoft Office (SS 216 W) 9

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, Power. Point, 97— 2003 Integrating Cold. Fusion with Microsoft Office (SS 216 W) 10

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 Cold. Fusion with Microsoft Office (SS 216 W) 11

Automation Example <cfobject name="xl. App" action="create" class="Excel. Application"> <cfset xl. Wbs = xl. App. Workbooks> <cfset xl. Wb = xl. Wbs. add("")> <cfset xl. Ws = xl. Wb. active. Sheet> <cfset c = xl. Ws. range("A 2")> c. value 2 = "Tom"> c = xl. Ws. range("B 2")> c. value 2 = 120> <cfset <cfset xl. Range = xl. Ws. range("A 1: B 4")> xl. Charts = xl. Wb. charts> xl. Chart = xl. Charts. add()> xl. Chart. chart. Type = -4102> xl. Chart. set. Source. Data(xl. Range, 2)> xl. Chart. location(1, "Sales By Employee")> <cfset xl. Wb. save. As(file. Name)> <cfset xl. Wb. close()> <cfcontent type="application/vnd. ms-excel" file="#file. Name#"> Integrating Cold. Fusion with Microsoft Office (SS 216 W) 12

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, noninteractive 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 Cold. Fusion with Microsoft Office (SS 216 W) 13

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, Pivot. Table, Chart Integrating Cold. Fusion with Microsoft Office (SS 216 W) 14

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 Cold. Fusion with Microsoft Office (SS 216 W) 15

Office Web Components Example <cfset ch. Space = create. Object("com", "OWC 10. Chart. Space")> <cfset ch. Chart = ch. Space. Charts. Add()> <cfset ch. Chart. Type = 0> <cfset ch. Ser = ch. Chart. Series. Collection. Add()> <cfset ch. Ser. Set. Data(1, -1, value. List(sales. Data. name))> <cfset ch. Ser. Set. Data(2, -1, value. List(sales. Data. sales))> <cfset ch. Ser. Caption = "Sales"> ch. Space. Border. Color = -2> ch. Chart. Has. Legend = True> ch. Chart. Has. Title = True> <cfset ch. Space. Export. Picture(abs. Path, "gif", 600, 512)> <img src="#rel. Path#" width="600" height="512" border="0" /> Integrating Cold. Fusion with Microsoft Office (SS 216 W) 16

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; e n-us; Q 317316 Integrating Cold. Fusion with Microsoft Office (SS 216 W) 17

OLE Properties § COM interface to any OLE compound § § document Simple two-way transfer of small amounts of data Word, Excel, Power. Point, 97— 2003 Integrating Cold. Fusion with Microsoft Office (SS 216 W) 18

OLE Properties How To § § Instantiate a Property. Reader 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 Cold. Fusion with Microsoft Office (SS 216 W) 19

OLE Properties Example <cfset file. Name = expand. Path("files3_DSO_ListFlash. For. CFers. ppt")> <cfobject action="create" type="com" class="DSOle. File. Property. Reader" name="prop. Reader"> <cfset file. Props = prop. Reader. Get. Document. Properties(file. Name)> <cfoutput> #file. Name# is written by #file. Props. author# </cfoutput> <cfset release. Com. Object(file. Props)> <cfset release. Com. Object(prop. Reader)> Integrating Cold. Fusion with Microsoft Office (SS 216 W) 20

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 Cold. Fusion with Microsoft Office (SS 216 W) 21

Jet Engine § Familiar database interaction § Efficient read and write of data § Excel 97— 2003 Integrating Cold. Fusion with Microsoft Office (SS 216 W) 22

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 Cold. Fusion with Microsoft Office (SS 216 W) 23

Jet Engine Example <cfset temp. File = expand. Path("files4_Jet_Read_Temp. xls")> <cffile action = "upload" file. Field = "file. Name" destination = "#temp. File#" name. Conflict = "overwrite"> <cfquery name="excel. Data" datasource="proxy"> SELECT Salesperson, Sales. Amount FROM "Excel 8. 0; DATABASE=#temp. File#; HDR=YES". [Sales$] </cfquery> Integrating Cold. Fusion with Microsoft Office (SS 216 W) 24

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 Cold. Fusion with Microsoft Office (SS 216 W) 25

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 Cold. Fusion on server – Even works on Unix servers! § Word, Excel, Power. Point, 2000— 2003 Integrating Cold. Fusion with Microsoft Office (SS 216 W) 26

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 Cold. Fusion with Microsoft Office (SS 216 W) 27

HTML/XML/CSS Example <style> @page Section 1 { size: 8. 5 in 11. 0 in; margin: . 5 in 13. 6 pt 0 in 13. 6 pt; } div. Section 1 { page: Section 1; } p { margin: 0 in 5. 3 pt; mso-pagination: widow-orphan; font-size: 12. 0 pt; font-family: "Times New Roman"; } </style> <cfcontent type="application/msword" reset="no"> <cfheader name="Content-Disposition" value="attachment; filename=Labels 01. doc">. . . html. . . Integrating Cold. Fusion with Microsoft Office (SS 216 W) 28

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 Cold. Fusion with Microsoft Office (SS 216 W) 29

XML § Pure XML implementation § Create nearly any document § Easy to create examples of target output – Save as XML § Does not require anything more than Cold. Fusion on server – Even works on Unix servers! § Excel 2002, Word, Excel, Power. Point 2003 Integrating Cold. Fusion with Microsoft Office (SS 216 W) 30

XML How-To § Generate XML document § Serve XML document § Example, sales report – 06_XML_Report. cfm Integrating Cold. Fusion with Microsoft Office (SS 216 W) 31

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> <Data ss: Type="String">Books</Data> </Cell> <Data ss: Type="Number">50</Data> </Cell> </Row> </Table> </Worksheet> </Workbook> Integrating Cold. Fusion with Microsoft Office (SS 216 W) 32

XML Drawbacks § Does not support charts § For XP, Excel only – Excel and Word in Office 2003 § Custom XML Schema Integrating Cold. Fusion with Microsoft Office (SS 216 W) 33

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 Cold. Fusion with Microsoft Office (SS 216 W) 34

Comparison Matrix Auto OWC OLE JET HTML XML Safe for server • Office on Server Windows on Server Read data Write data Word Excel Power. Point Leverage ever-day knowledge Auto-generate examples Often requires client-code Subject to user error • • • • • • • Integrating Cold. Fusion with Microsoft Office (SS 216 W) • • Map • • • • 35

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 Flash. Paper—not server-side yet, but possibly in future XSL-FO SWF RVML (http: //www. kinesissoftware. com) Integrating Cold. Fusion with Microsoft Office (SS 216 W) 36

Resources § CF Comet : : Coldfusion & COM § INFO: Considerations for Server-Side Automation of Office § OFF 2000: Licensing the Office 2000 Web Components and Office Server Extensions § HOWTO: Use Server-Side Charting to Generate Charts Dynamically § INFO: Limitations of Office 2000 Web Components When Used Server-Side § Microsoft Office XP Web Component Toolpack § Dsofile. exe Lets You Edit Office Document Properties from Visual Basic and ASP § Microsoft® Office HTML and XML Reference § Serving Word (HTML/XML/CSS) – – – – – http: //www. cfcomet. com/ http: //support. microsoft. com/default. aspx? scid =kb; EN-US; 257757 http: //support. microsoft. com/default. aspx? scid=kb; en-us; 243006 http: //support. microsoft. com/default. aspx? scid=kb; en-us; 244049 http: //support. microsoft. com/default. aspx? scid=kb; en-us; Q 317316 http: //www. microsoft. com/downloads/details. aspx? Family. Id=BEB 5 D 477 -2100 -4586 -A 13 C-50 E 56 F 101720&displaylang=en http: //support. microsoft. com/default. aspx? scid=kb; en-us; 224351 http: //msdn. microsoft. com/library/default. asp? url=/library/en-us/dnoffxml/html/ofxml 2 k. asp? frame=true http: //www. rewindlife. com/archives/000032. cfm § HOWTO: Format an Excel Workbook While Streaming MIME Content § Microsoft Office 2003 XML – – § http: //www. microsoft. com/seminar/shared/asp/view. asp? url=/seminar/en/20030801 DEVT 1_76/manifest. xml XML in Office XP – § http: //support. microsoft. com/default. aspx? scid=kb; en-us; 271572&Product=asp http: //msdn. microsoft. com/library/default. asp? url=/library/en-us/dnoffpro 01/html/XMLOffice. XPPart. I. 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 Cold. Fusion with Microsoft Office (SS 216 W) 37

Thank you.