- CFML Reference User Guide
- ColdFusion functions
- ColdFusion functions by category
- Functions a-b
- Abs
- ACos
- AddSOAPRequestHeader
- AddSOAPResponseHeader
- AjaxLink
- AjaxOnLoad
- ApplicationStop
- ArrayAppend
- ArrayAvg
- ArrayClear
- ArrayContains
- ArrayContainsNoCase
- ArrayDelete
- ArrayDeleteAt
- ArrayDeleteNoCase
- ArrayEach
- ArrayFilter
- ArrayFind
- ArrayFindAll
- ArrayFindAllNoCase
- ArrayFindNoCase
- ArrayInsertAt
- ArrayIsDefined
- ArrayIsEmpty
- ArrayLen
- ArrayMap
- ArrayMax
- ArrayMin
- ArrayNew
- ArrayPrepend
- ArrayReduce
- ArrayResize
- ArraySet
- ArraySetMetadata
- ArraySlice
- ArraySort
- ArraySum
- ArraySwap
- ArrayToList
- Asc
- ASin
- Atn
- AuthenticatedContext
- AuthenticatedUser
- BinaryDecode
- BinaryEncode
- BitAnd
- BitMaskClear
- BitMaskRead
- BitMaskSet
- BitNot
- BitOr
- BitSHLN
- BitSHRN
- BitXor
- BooleanFormat
- Abs
- Functions-c-d
- CacheGet
- CacheGetAllIds
- CacheGetMetadata
- CacheGetProperties
- CacheGetSession
- CacheIdExists
- CachePut
- CacheRegionExists
- CacheRegionNew
- CacheRegionRemove
- CacheRemove
- CacheRemoveAll
- CacheSetProperties
- CallStackDump
- CallStackGet
- CanDeSerialize
- Canonicalize
- CanSerialize
- Ceiling
- CharsetDecode
- CharsetEncode
- Chr
- CJustify
- Compare
- CompareNoCase
- Cos
- CreateDate
- CreateDateTime
- CreateObject
- CreateObject: .NET object
- CreateObject: COM object
- CreateObject: component object
- CreateObject: CORBA object
- CreateObject: Java or EJB object
- CreateObject: web service object
- CreateODBCDate
- CreateODBCDateTime
- CreateODBCTime
- CreateSignedJWT
- CreateEncryptedJWT
- CreateTime
- CreateTimeSpan
- CreateUUID
- CSRFGenerateToken
- CSRFVerifyToken
- DateAdd
- DateCompare
- DateConvert
- DateDiff
- DateFormat
- DatePart
- DateTimeFormat
- Day
- DayOfWeek
- DayOfWeekAsString
- DayOfYear
- DaysInMonth
- DaysInYear
- DE
- DecimalFormat
- DecodeForHTML
- DecodeFromURL
- DecrementValue
- Decrypt
- DecryptBinary
- DeleteClientVariable
- Deserialize
- DeserializeJSON
- DeserializeXML
- DirectoryCopy
- DirectoryCreate
- DirectoryDelete
- DirectoryExists
- DirectoryList
- DirectoryRename
- DollarFormat
- DotNetToCFType
- Duplicate
- Functions-e-g
- EncodeForCSS
- EncodeForDN
- EncodeForHTML
- EncodeForHTMLAttribute
- EncodeForJavaScript
- EncodeForLDAP
- EncodeForURL
- EncodeForXML
- EncodeForXMLAttribute
- EncodeForXpath
- Encrypt
- EncryptBinary
- EntityDelete
- EntityLoad
- EntityLoadByExample
- EntityLoadByPK
- EntityMerge
- EntityNew
- EntityReload
- EntitySave
- EntityToQuery
- Evaluate
- Exp
- ExpandPath
- FileClose
- FileCopy
- FileDelete
- FileExists
- FileGetMimeType
- FileIsEOF
- FileMove
- FileOpen
- FileRead
- FileReadBinary
- FileReadLine
- FileSeek
- FileSetAccessMode
- FileSetAttribute
- FileSetLastModified
- FileSkipBytes
- FileUpload
- FileUploadAll
- FileWrite
- FileWriteLine
- Find
- FindNoCase
- FindOneOf
- FirstDayOfMonth
- Fix
- Floor
- FormatBaseN
- GeneratePBKDFKey
- GenerateSecretKey
- GetApplicationMetadata
- GetAuthUser
- GetBaseTagData
- GetBaseTagList
- GetBaseTemplatePath
- GetClientVariablesList
- GetComponentMetaData
- GetContextRoot
- GetCPUUsage
- GetCurrentTemplatePath
- GetDirectoryFromPath
- GetEncoding
- GetException
- GetFileFromPath
- GetFileInfo
- GetFreeSpace
- GetFunctionCalledName
- GetFunctionList
- GetGatewayHelper
- GetHttpRequestData
- GetHttpTimeString
- GetK2ServerDocCount
- GetK2ServerDocCountLimit
- GetLocale
- GetLocaleDisplayName
- GetLocalHostIP
- GetMetaData
- GetMetricData
- GetPageContext
- GetPrinterInfo
- GetPrinterList
- GetProfileSections
- GetProfileString
- GetReadableImageFormats
- GetSafeHTML
- GetSAMLAuthRequest
- GetSAMLLogoutRequest
- GenerateSAMLSPMetadata
- GetSOAPRequest
- GetSOAPRequestHeader
- GetSOAPResponse
- GetSOAPResponseHeader
- GetSystemFreeMemory
- GetSystemTotalMemory
- GetTempDirectory
- GetTempFile
- GetTemplatePath
- GetTickCount
- GetTimeZoneInfo
- GetToken
- GetTotalSpace
- GetUserRoles
- GetVFSMetaData
- GetWriteableImageFormats
- Functions-h-im
- Hash
- HMac
- Hour
- HQLMethods
- HTMLCodeFormat
- HTMLEditFormat
- IIf
- ImageAddBorder
- ImageBlur
- ImageClearRect
- ImageCopy
- ImageCreateCaptcha
- ImageCrop
- ImageDrawArc
- ImageDrawBeveledRect
- ImageDrawCubicCurve
- ImageDrawLine
- ImageDrawLines
- ImageDrawOval
- ImageDrawPoint
- ImageDrawQuadraticCurve
- ImageDrawRect
- ImageDrawRoundRect
- ImageDrawText
- ImageFlip
- ImageGetBlob
- ImageGetBufferedImage
- ImageGetEXIFMetadata
- ImageGetEXIFTag
- ImageGetHeight
- ImageGetIPTCMetadata
- ImageGetIPTCTag
- ImageGetMetadata
- ImageGetWidth
- ImageGrayscale
- ImageInfo
- ImageMakeColorTransparent
- ImageMakeTranslucent
- ImageNegative
- ImageNew
- ImageOverlay
- ImagePaste
- ImageRead
- ImageReadBase64
- ImageResize
- ImageRotate
- ImageRotateDrawingAxis
- ImageScaleToFit
- ImageSetAntialiasing
- ImageSetBackgroundColor
- ImageSetDrawingColor
- ImageSetDrawingStroke
- ImageSetDrawingTransparency
- ImageSharpen
- ImageShear
- ImageShearDrawingAxis
- ImageTranslate
- ImageTranslateDrawingAxis
- ImageWrite
- ImageWriteBase64
- ImageXORDrawingMode
- Functions-in-k
- IncrementValue
- InputBaseN
- Insert
- Int
- InvalidateOauthAccesstoken
- Invoke
- InitSAMLAuthRequest
- InitSAMLLogoutRequest
- InvokeCFClientFunction
- IsArray
- IsAuthenticated
- IsAuthorized
- IsBinary
- IsBoolean
- IsClosure
- IsCustomFunction
- IsDate
- IsDateObject
- IsDDX
- IsDebugMode
- IsDefined
- IsImage
- IsImageFile
- IsInstanceOf
- IsIPv6
- IsJSON
- IsK2ServerABroker
- IsK2ServerDocCountExceeded
- IsK2ServerOnline
- IsLeapYear
- IsLocalHost
- IsNull
- IsNumeric
- IsNumericDate
- IsObject
- isOnline
- IsPDFArchive
- IsPDFFile
- IsPDFObject
- IsProtected
- IsQuery
- isSamlLogoutResponse
- isSafeHTML
- IsSimpleValue
- IsSOAPRequest
- IsSpreadsheetFile
- IsSpreadsheetObject
- IsStruct
- IsUserInAnyRole
- IsUserInRole
- IsUserLoggedIn
- IsValid
- IsValidOauthAccesstoken
- IsWDDX
- IsXML
- IsXmlAttribute
- IsXmlDoc
- IsXmlElem
- IsXmlNode
- IsXmlRoot
- JavaCast
- JSStringFormat
- Functions-l
- LCase
- Left
- Len
- ListAppend
- ListChangeDelims
- ListContains
- ListContainsNoCase
- ListDeleteAt
- ListEach
- ListFilter
- ListFind
- ListFindNoCase
- ListFirst
- ListGetAt
- ListInsertAt
- ListLast
- ListLen
- ListMap
- ListPrepend
- ListQualify
- ListReduce
- ListRemoveDuplicates
- ListRest
- ListSetAt
- ListSort
- ListToArray
- ListValueCount
- ListValueCountNoCase
- LJustify
- Location
- Log
- Log10
- LSCurrencyFormat
- LSDateFormat
- LSDateTimeFormat
- LSEuroCurrencyFormat
- LSIsCurrency
- LSIsDate
- LSIsNumeric
- LSNumberFormat
- LSParseCurrency
- LSParseDateTime
- LSParseEuroCurrency
- LSParseNumber
- LSTimeFormat
- LTrim
- Functions-m-r
- Max
- Mid
- Min
- Minute
- Month
- MonthAsString
- Now
- NumberFormat
- ObjectEquals
- ObjectLoad
- ObjectSave
- OnWSAuthenticate
- ORMClearSession
- ORMCloseAllSessions
- ORMCloseSession
- ORMEvictCollection
- ORMEvictEntity
- ORMEvictQueries
- ORMExecuteQuery
- ORMFlush
- ORMFlushall
- ORMGetSession
- ORMGetSessionFactory
- ORMIndex
- ORMIndexPurge
- ORMReload
- ORMSearch
- ORMSearchOffline
- ParagraphFormat
- ParameterExists
- ParseDateTime
- Pi
- PrecisionEvaluate
- ProcessSAMLResponse
- ProcessSAMLLogoutRequest
- Quarter
- PreserveSingleQuotes
- QueryAddColumn
- QueryAddRow
- QueryConvertForGrid
- QueryExecute
- QueryFilter
- QueryGetResult
- QueryGetRow
- QueryKeyExists
- QueryMap
- QueryNew
- QueryReduce
- QuerySetCell
- QuotedValueList
- QueryEach
- Rand
- Randomize
- RandRange
- ReEscape
- REFind
- REFindNoCase
- ReleaseComObject
- REMatch
- REMatchNoCase
- RemoveCachedQuery
- RemoveChars
- RepeatString
- Replace
- ReplaceList
- ReplaceNoCase
- REReplace
- REReplaceNoCase
- RestDeleteApplication
- RestSetResponse
- RestInitApplication
- Reverse
- Right
- RJustify
- Round
- RTrim
- Functions-s
- Second
- SendGatewayMessage
- SendSAMLLogoutResponse
- Serialize
- SerializeJSON
- SerializeXML
- SessionInvalidate
- SessionRotate
- SessionGetMetaData
- SessionInvalidate
- SessionRotate
- SetDay
- SetEncoding
- SetHour
- SetLocale
- SetMonth
- SetProfileString
- SetVariable
- SetYear
- Sgn
- Sin
- Sleep
- SpanExcluding
- SpanIncluding
- SpreadsheetAddAutoFilter
- SpreadsheetAddColumn
- SpreadsheetAddFreezePane
- SpreadsheetAddImage
- SpreadsheetAddInfo
- SpreadsheetAddPageBreaks
- SpreadsheetAddRow
- SpreadsheetAddRows
- SpreadsheetAddSplitPane
- SpreadsheetCreateSheet
- SpreadsheetDeleteColumn
- SpreadsheetDeleteColumns
- SpreadsheetDeleteRow
- SpreadsheetDeleteRows
- SpreadsheetFormatCell
- SpreadsheetFormatColumn
- SpreadsheetFormatCellRange
- SpreadsheetFormatColumn
- SpreadsheetFormatColumns
- SpreadsheetFormatRow
- SpreadsheetFormatRows
- SpreadsheetGetCellComment
- SpreadsheetGetCellFormula
- SpreadsheetGetCellValue
- SpreadsheetGetColumnCount
- SpreadsheetInfo
- SpreadsheetMergeCells
- SpreadsheetNew
- SpreadsheetRead
- SpreadsheetReadBinary
- SpreadsheetRemoveSheet
- SpreadsheetSetActiveSheet
- SpreadsheetSetActiveSheetNumber
- SpreadsheetSetCellComment
- SpreadsheetSetCellFormula
- SpreadsheetSetCellValue
- SpreadsheetSetColumnWidth
- SpreadsheetSetFooter
- SpreadsheetSetHeader
- SpreadsheetSetRowHeight
- SpreadsheetShiftColumns
- SpreadsheetShiftRows
- SpreadsheetWrite
- Sqr
- StripCR
- StructAppend
- StructClear
- StructCopy
- StructCount
- StructDelete
- StructEach
- StructFilter
- StructFind
- StructFindKey
- StructFindValue
- StructGet
- StructGetMetadata
- StructInsert
- StructIsEmpty
- StructKeyArray
- StructKeyExists
- StructKeyList
- StructMap
- StructNew
- StructReduce
- StructSetMetadata
- StructSort
- StructToSorted
- StructUpdate
- StoreSetMetadata
- StoreGetACL
- StoreGetMetadata
- StoreAddACL
- StoreSetACL
- Functions-t-z
- Tan
- ThreadJoin
- ThreadTerminate
- Throw
- TimeFormat
- ToBase64
- ToBinary
- ToScript
- ToString
- Trace
- Transactionandconcurrency
- TransactionCommit
- TransactionRollback
- TransactionSetSavePoint
- Trim
- UCase
- URLDecode
- URLEncodedFormat
- URLSessionFormat
- Val
- ValueList
- VerifyClient
- Week
- Wrap
- WriteDump
- WriteLog
- WriteOutput
- WSGetAllChannels
- WSGetSubscribers
- WSPublish
- WSSendMessage
- XmlChildPos
- XmlElemNew
- XmlFormat
- XmlGetNodeType
- XmlNew
- XmlParse
- XmlSearch
- XmlTransform
- XmlValidate
- Year
- YesNoFormat
- ColdFusion Tags
- ColdFusion tag summary
- ColdFusion tags by category
- Application framework tags
- Communications tags
- Database manipulation tags
- Data output tags
- Debugging tags
- Exception handling tags
- Extensibility tags
- File management tags
- Flow-control tags
- Forms tags
- Internet Protocol tags
- Page processing tags
- Security tags
- Variable manipulation tags
- Other tags
- Tags a-b
- Tags c
- Tags f
- cffeed
- cffile
- cffile action = "append"
- cffile action = "copy"
- cffile action = "delete"
- cffile action = "move"
- cffile action = "read"
- cffile action = "readBinary"
- cffile action = "rename"
- cffile action = "upload"
- cffile action = "uploadAll"
- cffile action = "write"
- cffileupload
- cffinally
- cfflush
- cfform
- cfformgroup
- cfformitem
- cfftp
- cfftp: Connection: file and directory operations
- cfftp: Opening and closing FTP server connections
- cfftp : Opening and closing secure FTP server connections
- cfftp action = "listDir"
- cffunction
- Tags g-h
- Tags i
- Tags j-l
- cfjava
- cflayout
- cflayoutarea
- cfldap
- cflocation
- cflock
- cflog
- cflogin
- cfloginuser
- cflogout
- cfloop
- cfloop : conditional loop
- cfloop : index loop
- cfloop : looping over a COM collection or structure
- cfloop : looping over a date or time range
- cfloop : looping over a list, a file, or an array
- cfloop : looping over a query
- Tags m-o
- cfmail
- cfmailparam
- cfmailpart
- cfmap
- cfmapitem
- cfmediaplayer
- cfmenu
- cfmenuitem
- cfmessagebox
- cfmodule
- cfNTauthenticate
- cfoauth
- cfobject
- cfobject: .NET object
- cfobject: COM object
- cfobject: component object
- cfobject: CORBA object
- cfobject: Java or EJB object
- cfobject: web service object
- cfobjectcache
- cfoutput
- Tags p-q
- Tags r-s
- Tags t
- Tags u-z
- CFML Reference
- Reserved words and variables
- Ajax JavaScript functions
- Ajax JavaScript functions
- Function summary Ajax
- ColdFusion.Ajax.submitForm
- ColdFusion.Autosuggest.getAutosuggestObject
- ColdFusion.Layout.enableSourceBind
- ColdFusion.MessageBox.getMessageBoxObject
- ColdFusion.ProgressBar.getProgressBarObject
- ColdFusion.MessageBox.isMessageBoxDefined
- JavaScriptFunctionsinColdFusion9Update1
- ColdFusion ActionScript functions
- ColdFusion mobile functions
- Application.cfc reference
- Script functions implemented as CFCs
- ColdFusion Flash Form style reference
- Styles valid for all controls
- Styles for cfform
- Styles for cfformgroup with horizontal or vertical type attributes
- Styles for box-style cfformgroup elements
- Styles for cfformgroup with accordion type attribute
- Styles for cfformgroup with tabnavigator type attribute
- Styles for cfformitem with hrule or vrule type attributes
- Styles for cfinput with radio, checkbox, button, image, or submit type attributes
- Styles for cftextarea tag and cfinput with text, password, or hidden type attributes
- Styles for cfselect with size attribute value of 1
- Styles for cfselect with size attribute value greater than 1
- Styles for cfcalendar tag and cfinput with dateField type attribute
- Styles for the cfgrid tag
- Styles for the cftree tag
- ColdFusion Flash Form Style Reference
- ColdFusion event gateway reference
- ColdFusion Event Gateway reference
- addEvent
- CFEvent
- CFEventclass
- Constructor
- Gateway development interfaces and classes
- getStatus
- setCFCPath
- setCFCMethod
- getOriginatorID
- getLogger
- getBuddyList
- getBuddyInfo
- IM gateway message sending commands
- IM Gateway GatewayHelper class methods
- onIncomingMessage
- onIMServerMessage
- onBuddyStatus
- onAddBuddyResponse
- onAddBuddyRequest
- IM Gateway CFC incoming message methods
- IM gateway methods and commands
- CFML CFEvent structure
- warn
- info
- setOriginatorID
- data command
- submit Multi command
- submit command
- setGatewayType
- setGatewayID
- setData
- setCFCListeners
- outgoingMessage
- getStatusTimeStamp
- numberOfMessagesReceived
- numberOfMessagesSent
- removeBuddy
- removeDeny
- removePermit
- setNickName
- setPermitMode
- setStatus
- SMS Gateway CFEvent structure and commands
- SMS Gateway incoming message CFEvent structure
- getStatusAsString
- getProtocolName
- getPermitMode
- getPermitList
- getNickName
- getName
- getDenyList
- getCustomAwayMessage
- getQueueSize
- getMaxQueueSize
- getHelper
- getGatewayType
- getGatewayServices
- getGatewayID_1
- getGatewayID
- getData
- getCFCTimeout
- setCFCTimeout
- getCFCPath
- getCFCMethod
- GatewayServices class
- Gateway interface
- GatewayHelper interface
- addPermit
- addDeny
- addBuddy
- error
- debug
- Logger class
- stop
- start
- CFML event gateway SendGatewayMessage data parameter
- restart
- fatal
- SMS gateway message sending commands
- ColdFusion C++ CFX Reference
- ColdFusion Java CFX reference
- WDDX JavaScript Objects
- Cloud services
- ColdFusion and GCP Storage
- ColdFusion and GCP Firestore
- ColdFusion and GCP PubSub
- ColdFusion and Amazon S3
- ColdFusion and DynamoDB
- ColdFusion and Amazon SQS
- ColdFusion and Amazon SNS
- ColdFusion and MongoDB
- ColdFusion and Azure Blob
- ColdFusion and Azure Service Bus
- Multi-cloud storage services
- Multi-cloud RDS databases
- ColdFusion and Azure Cosmos DB
Description
Manages Excel spreadsheet files:
- Reads a sheet from a spreadsheet file and stores it in a ColdFusion spreadsheet object, query, CSV string, or HTML string.
- Writes single sheet to a new XLS file from a query, ColdFusion spreadsheet object, or CSV string variable.
- Add a sheet an existing XLS file.
Category
Syntax
The tag syntax depends on the action attribute value:
Read <cfspreadsheet action="read" src = "filepath" columns = "range" columnnames = "comma-delimited list" excludeHeaderRow = "true | false" 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" autosize="true|false" filename = "filepath" format = "csv" name = "text" overwrite = "true | false" password = "password" query = "queryname" sheetname = "text" autosize = "true | false" >
See also
Sreadsheet functions.
History
ColdFusion 11: Added the attribute autosize.
ColdFusion 9.0.1: Added the attribute {{excludeHeaderRow}}
ColdFusion 9: Added this tag.
Attributes
Attribute |
Action |
Req/Opt |
Default |
Description |
---|---|---|---|---|
action |
All |
Required |
|
One of the following:
|
autosize | write | Optional | true | By default the value of this attribute is true. The columns in a sheet resize to accommodate the contents. To avoid resizing the columns, set it to false. Note: Auto sizing can be relatively slow on large sheets. |
filename |
{{update, write}}r |
Required |
|
The pathname of the file that is written. |
excludeHeaderRow |
read |
Optional |
false |
If set to true, excludes the headerrow from being included in the query results. |
name |
All |
name or query is required. |
|
|
query |
All |
name or query is required. |
|
|
src |
read |
Required |
|
The pathname of the file to read. |
columns |
read |
Optional |
|
Column number or range of columns. Specify a single number, a hypen-separated column range, a comma-separated list, or any combination of these; for example: 1,3-6,9. Note: Setting a range higher than the actual number of columns in sheet results in an exception when accessing columns which are yet to be added. |
columnnames |
read |
Optional |
|
Comma-separated column names. |
format |
All |
Optional |
For read, save as a spreadsheet object. |
Format of the data represented by the name variable.
|
headerrow |
read |
Optional |
|
Row number that contains column names. |
overwrite |
write |
Optional |
false |
A Boolean value specifying whether to overwrite an existing file. |
password |
updatewrite |
Optional |
|
Set a password for modifying the sheet. |
rows |
read |
Optional |
|
The range of rows to read. Specify a single number, a hypen-separated row range, a comma-separated list, or any combination of these; for example: 1,3-6,9. |
sheet |
read |
Optional |
|
Number of the sheet. For the read action, you can specify sheet or sheetname. |
sheetname |
All |
Optional |
|
Name of the sheet For the read action, you can specify sheet or sheetname. For write and update actions, the specified sheet is renamed according to the value you specify for sheetname. |
Usage
Each ColdFusion spreadsheet object represents Excel sheet:
- To read an Excel file with multiple sheets, use multiple cfspreadsheet tags with the read option and specify different name and sheet or sheetname attributes for each sheet.
- To write multiple sheets to a single file, use the write action to create the file and save the first sheet and use the update action to add each additional sheet.
- To update an existing file, read all sheets in the file, modify one or more sheets, and use the contents, and use the write action and Update actions (for multiple sheet files) to rewrite the entire file.
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.
Example
The 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 rows="3,4" format="csv" name="csvData"> <cfspreadsheet action="read" src="#theFile#" format="html" rows="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>
Let's take chunks of the above code and see each chunk in action. For example, consider a csv file is uploaded on the web, which you want to retrieve and perform some actions.
You can also jump to ths fiddle and try out the code chunks. Sign in with your Google or Facebook credentials and launch the file cfspreadsheet.cfm.
Step 1
Read the csv file and store the response in a variable.
<cfscript> cfhttp( name="mydata", url="https://raw.githubusercontent.com/sauravg94/test-repo/master/MOCK_DATA.csv", firstrowasheaders="true" ,method="GET"); writedump(mydata); </cfscript>
Step 2
- Set the destination for the xlsx or xls file.
- Create an empty spreadsheet object.
- Populate the object with data fetched with cfhttp.
Note:
SpreadsheetNew(true|false)
- True or Yes: Creates an .xlsx file that is supported by Microsoft Office Excel 2007.
- False or No: Creates an .xls file.
<cfscript> //Use an absolute path for the files. theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); theFile=theDir & "mock_data.xlsx"; //Create an empty ColdFusion spreadsheet objects. theSheet = SpreadsheetNew(true); //Populate the object with data fetched with cfhttp SpreadsheetAddRows(theSheet,mydata,1); </cfscript>
Step 3
Write the sheet into a file.
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="mock_data" overwrite=true>
Step 4
Read all or part of the file into a spreadsheet object, CSV string, HTML string, and query.
<cfspreadsheet action="read" src="#theFile#" sheetname="mock_data" name="spreadsheetData"> <cfspreadsheet action="read" src="#theFile#" sheet=1 rows="100-200" format="csv" name="csvData"> <cfspreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData"> <cfspreadsheet action="read" src="#theFile#" sheetname="mock_data" query="queryData">
Step 5
<cfdump var="#spreadsheetData#" > <cfoutput > #csvData# </cfoutput> <cfdump var="#htmlData#" > <cfdump var="#queryData#" >