integrating coldfusion with microsoft office l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Integrating ColdFusion with Microsoft Office PowerPoint Presentation
Download Presentation
Integrating ColdFusion with Microsoft Office

Loading in 2 Seconds...

play fullscreen
1 / 38

Integrating ColdFusion with Microsoft Office - PowerPoint PPT Presentation


  • 830 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Integrating ColdFusion with Microsoft Office' - albert


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
integrating coldfusion with microsoft office

Integrating ColdFusion with Microsoft Office

Samuel Neff

November 19-21, 2003

about the presenter
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)

agenda
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)

why integrate with office
Why integrate with Office?
  • Consistent reproduction of reports

Integrating ColdFusion with Microsoft Office (SS216W)

why integrate with office5
Why integrate with Office?
  • Give users editable documents

Integrating ColdFusion with Microsoft Office (SS216W)

why integrate with office6
Why integrate with Office?
  • Leverage features of the Office suite

Integrating ColdFusion with Microsoft Office (SS216W)

why integrate with office7
Why integrate with Office?
  • Gain control unavailable with HTML

Integrating ColdFusion with Microsoft Office (SS216W)

why so many options
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)

why don t we teach just the best
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)

automation
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)

automation how to
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)

automation example
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)

automation drawbacks
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)

office web components
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)

office web components how to
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)

office web components example
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)

office web components drawbacks
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)

ole properties
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)

ole properties how to
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)

ole properties example
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)

ole properties drawbacks
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)

jet engine
Jet Engine
  • Familiar database interaction
  • Efficient read and write of data
  • Excel 97—2003

Integrating ColdFusion with Microsoft Office (SS216W)

jet engine how to
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)

jet engine example
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)

jet engine drawbacks
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)

html xml css
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)

html xml css how to
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)

html xml css example
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)

html xml css drawbacks
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)

slide30
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)

xml how to
XML How-To
  • Generate XML document
  • Serve XML document
  • Example, sales report
    • 06_XML_Report.cfm

Integrating ColdFusion with Microsoft Office (SS216W)

xml example
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)

xml drawbacks
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)

xml mapping
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)

comparison matrix
Comparison Matrix

Integrating ColdFusion with Microsoft Office (SS216W)

alternatives
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)

resources
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)