Integrating coldfusion with microsoft office
Download
1 / 38

Integrating ColdFusion with Microsoft Office - PowerPoint PPT Presentation


  • 809 Views
  • Uploaded on

Integrating ColdFusion with Microsoft Office Samuel Neff November 19-21, 2003 About the Presenter Samuel Neff ([email protected]) 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 l.jpg

Integrating ColdFusion with Microsoft Office

Samuel Neff

November 19-21, 2003


About the presenter l.jpg
About the Presenter

  • Samuel Neff ([email protected])

    • 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 l.jpg
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 l.jpg
Why integrate with Office?

  • Consistent reproduction of reports

Integrating ColdFusion with Microsoft Office (SS216W)


Why integrate with office5 l.jpg
Why integrate with Office?

  • Give users editable documents

Integrating ColdFusion with Microsoft Office (SS216W)


Why integrate with office6 l.jpg
Why integrate with Office?

  • Leverage features of the Office suite

Integrating ColdFusion with Microsoft Office (SS216W)


Why integrate with office7 l.jpg
Why integrate with Office?

  • Gain control unavailable with HTML

Integrating ColdFusion with Microsoft Office (SS216W)


Why so many options l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Jet Engine

  • Familiar database interaction

  • Efficient read and write of data

  • Excel 97—2003

Integrating ColdFusion with Microsoft Office (SS216W)


Jet engine how to l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
XML How-To

  • Generate XML document

  • Serve XML document

  • Example, sales report

    • 06_XML_Report.cfm

Integrating ColdFusion with Microsoft Office (SS216W)


Xml example l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Comparison Matrix

Integrating ColdFusion with Microsoft Office (SS216W)


Alternatives l.jpg
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 l.jpg
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)



ad