Whatever message this page gives is out now! Go check it out!

StreamingspreadsheetRead

Last update:
May 18, 2026
Caution:
Some older methods for reading and writing spreadsheets may not work for streaming spreadsheet objects if they attempt to operate on rows that are not yet loaded into memory at the time of invocation.

Description

Reads a sheet from source Excel file and converts it to html, csv, or query object.

Returns 

Returns query for format = 'query', string for format = 'csv', and format = 'html'.

History

  • ColdFusion (2025 release): Added the function.

Syntax

streamingSpreadsheetRead(source [, options])

Parameters

Name
Required
Type
Description
source
Yes
String
The file path of the spreadsheet to be read.
options
No
Struct
Configuration options for reading the spreadsheet.
The following values are allowed in the struct:
Key
Type
Description
sheetName
String
Name of the sheet to be read.
format
String
'html', 'csv', or 'query'.
headerRow
Number
Row number that contains column names.
rows
String
The range of rows to read. Specify a single number, a hyphen-separated row range, a comma-separated list, or any combination of these; for example: 1,3-6,9. If you do not specify a row or a range of rows, all rows are returned.
columns
String
Column number or range of columns. Specify a single number, a hyphen-separated column range, a comma-separated list, or any combination of these; for example: 1,3-6,9.
destinationFilePath
String
The file path to save the html/csv string.
excludeHeaderRow
Boolean
The flag to exclude the header row from results.
randomAccessWindowSize
Number
Maximum number of rows to be kept in memory at a time. The default value is 100.

Example

  1. Reading streaming spreadsheet as html.
<cfscript>  
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "file-read-example.xlsx";  
    readStruct={sheetName="Sheet1",format="html",headerRow=1, excludeHeaderRow="TRUE", rows="1-9"}  
    value=streamingspreadsheetread(#theFile#,readStruct)  
    writeDump(value)  
</cfscript>
Output
<TH>SR.</TH> <TH>NAME</TH> <TH>GENDER</TH> <TH>AGE</TH> <TH>DATE </TH> <TH>COUNTRY</TH> <TR> <TD> 1 </TD> <TD> Dett </TD> <TD> Male </TD> <TD> 18 </TD> <TD> 21/05/2015 </TD> <TD> Great Britain </TD> </TR> <TR> <TD> 2 </TD> <TD> Nern </TD> <TD> Female </TD> <TD> 19 </TD> <TD> 15/10/2017 </TD> <TD> France </TD> </TR> <TR> <TD> 3 </TD> <TD> Kallsie </TD> <TD> Male </TD> <TD> 20 </TD> <TD> 16/08/2016 </TD> <TD> France </TD> </TR> <TR> <TD> 4 </TD> <TD> Siuau </TD> <TD> Female </TD> <TD> 21 </TD> <TD> 21/05/2015 </TD> <TD> Great Britain </TD> </TR> <TR> <TD> 5 </TD> <TD> Shennice </TD> <TD> Male </TD> <TD> 22 </TD> <TD> 21/05/2016 </TD> <TD> France </TD> </TR> <TR> <TD> 6 </TD> <TD> Chasse </TD> <TD> Female </TD> <TD> 23 </TD> <TD> 15/10/2018 </TD> <TD> France </TD> </TR> <TR> <TD> 7 </TD> <TD> Tommye </TD> <TD> Male </TD> <TD> 24 </TD> <TD> 16/08/2017 </TD> <TD> United States </TD> </TR> <TR> <TD> 8 </TD> <TD> Dorcast </TD> <TD> Female </TD> <TD> 25 </TD> <TD> 21/05/2016 </TD> <TD> United States </TD> </TR>
2. Reading streaming spreadsheet as query
<cfscript>  
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "file-read-example.xlsx";  
    readStruct={sheetName="Sheet1",format="query",headerRow=1, excludeHeaderRow="TRUE", rows="1-9"}  
    value=streamingspreadsheetread(#theFile#,readStruct)  
    writeDump(value)  
</cfscript>
Figure: Reading streaming spreadsheet as query
streamingspreadsheetread output
3. Reading streaming spreadsheet as CSV.
<cfscript>  
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetReadTestFile.xlsx";  
    readStruct={sheetName="Sheet1",format="csv",rows="1-9"}  
    value=streamingspreadsheetread(#theFile#,readStruct)  
    writeDump(value)  
</cfscript>

Output

1,Dett,Male,18,21/05/2015,Great Britain 2,Nern ,Female,19,15/10/2017,France 3,Kallsie,Male,20,16/08/2016,France 4,Siuau,Female,21,21/05/2015,Great Britain 5,Shennice,Male,22,21/05/2016,France 6,Chasse,Female,23,15/10/2018,France 7,Tommye,Male,24,16/08/2017,United States 8,Dorcast,Female,25,21/05/2016,United States

Share this page

Was this page helpful?
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Thank you for your feedback. Your response will help improve this page.

On this page