ColdFusion 9.0 Resources |
cfspreadsheetDescriptionManages Excel spreadsheet files:
SyntaxThe tag syntax depends on the action attribute value: Read <cfspreadsheet action="read" src = "filepath" columns = "range" columnnames = "comma-delimited list" format = "CSV|HTML" headerrow = "row number" name = "text" query = "query name" rows = "range" sheet = "number" sheetname = "text"> Update <cfspreadsheet action="update" filename = "filepath" format = "csv" name = "text" password = "password" query = "query name" sheetname = "text" > Write <cfspreadsheet action="write" filename = "filepath" format = "csv" name = "text" overwrite = "true | false" password = "password" query = "queryname" sheetname = "text" > Attributes
UsageEach ColdFusion spreadsheet object represents Excel sheet:
The cfspreadsheet tag writes only XLS format files. To write a CSV file, put your data in a CSV formatted string variable and use the cffile tag to write the variable contents in a file. Use the ColdFusion Spreadsheet* functions, such as SpreadsheetNew and SpreadsheetAddColumn to create a new ColdFusion Spreadsheet object and modify the spreadsheet contents. ExampleThe following example uses the cfspreadsheet tag to read and write Excel spreadsheets using various formats. It also shows a simple use of ColdFusion Spreadsheet functions to modify a sheet. <!--- Read data from two datasource tables. ---> <cfquery name="courses" datasource="cfdocexamples" cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME FROM COURSELIST </cfquery> <cfquery name="centers" datasource="cfdocexamples" cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> SELECT * FROM CENTERS </cfquery> <cfscript> //Use an absolute path for the files. ---> theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); theFile=theDir & "courses.xls"; //Create two empty ColdFusion spreadsheet objects. ---> theSheet = SpreadsheetNew("CourseData"); theSecondSheet = SpreadsheetNew("CentersData"); //Populate each object with a query. ---> SpreadsheetAddRows(theSheet,courses); SpreadsheetAddRows(theSecondSheet,centers); </cfscript> <!--- Write the two sheets to a single file ---> <cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="courses" overwrite=true> <cfspreadsheet action="update" filename="#theFile#" name="theSecondSheet" sheetname="centers"> <!--- Read all or part of the file into a spreadsheet object, CSV string, HTML string, and query. ---> <cfspreadsheet action="read" src="#theFile#" sheetname="courses" name="spreadsheetData"> <cfspreadsheet action="read" src="#theFile#" sheet=1 row="3,4" format="csv" name="csvData"> <cfspreadsheet action="read" src="#theFile#" format="html" row="5-10" name="htmlData"> <cfspreadsheet action="read" src="#theFile#" sheetname="centers" query="queryData"> <h3>First sheet row 3 read as a CSV variable</h3> <cfdump var="#csvData#"> <h3>Second sheet rows 5-10 read as an HTML variable</h3> <cfdump var="#htmlData#"> <h3>Second sheet read as a query variable</h3> <cfdump var="#queryData#"> <!--- Modify the courses sheet. ---> <cfscript> SpreadsheetAddRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1); SpreadsheetAddColumn(spreadsheetData, "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced", 3,2,true); </cfscript> <!--- Write the updated Courses sheet to a new XLS file ---> <cfspreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData" sheetname="courses" overwrite=true> <!--- Write an XLS file containing the data in the CSV variable. ---> <cfspreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData" format="csv" sheetname="courses" overwrite=true> |