Last updated on
Feb 25, 2025
Disclaimer
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 a spreadsheet by executing a user defined function for each row.
Returns
Processed streaming spreadsheet object.
History
ColdFusion (2025 release): Added the function.
Syntax
streamingSpreadsheetProcess(source [, options],rowProcessor)
streamingSpreadsheetProcess(source [, options],rowProcessor)
streamingSpreadsheetProcess(source [, options],rowProcessor)
Parameters
| Name | Required | options | rowProcessor |
| source | Yes | String | File path of the spreadsheet to be read. |
| options | No | Struct | A struct containing the configuration of reading the streaming spreadsheet. The keys are:
|
| rowProcessor | Yes | UDF | Method to run for each row. This method accepts two parameters - spreadsheet object and row number. It needs to return spreadsheet object. |
Example
<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetProcessInput.xlsx";
theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetProcessOutput.xlsx";
obj = streamingSpreadsheetNew("theSheet",4)
SpreadsheetAddRow(obj,"150,ENGL1,,",1,1);
SpreadsheetAddRow(obj,"150,ENGL2,97,Poetry 1",2,1);
for ( i=3;i<=10000;i++){
SpreadsheetAddRow(obj,"150,ENGL,95,Poetry 1",i,1);
}
spreadsheetwrite(obj, theFile,"",true,false);
myFormat = StructNew();
myFormat.color = "blue";
myFormat.bold = "true";
myFormat.underline = "true";
myFormat.alignment = "center";
myFormat.font = "Arial";
secondFormat = StructNew();
secondFormat.color = "red";
secondFormat.bold = "true";
secondFormat.underline = "true";
secondFormat.alignment = "center";
secondFormat.font = "Arial";
function func(spreadsheetObj, row)
{
if(row % 2 eq 1){
SpreadsheetFormatRow(spreadsheetObj, myFormat, row);
}else{
SpreadsheetFormatRow(spreadsheetObj, secondFormat, row);
}
return spreadsheetObj
}
processedSpreadsheet = StreamingSpreadsheetProcess(#theFile#, func)
writeoutput(processedSpreadsheet.SummaryInfo.SPREADSHEETTYPE)
spreadsheetwrite(processedSpreadsheet, "#theFile1#", "", "yes", "no")
</cfscript>
<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetProcessInput.xlsx";
theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetProcessOutput.xlsx";
obj = streamingSpreadsheetNew("theSheet",4)
SpreadsheetAddRow(obj,"150,ENGL1,,",1,1);
SpreadsheetAddRow(obj,"150,ENGL2,97,Poetry 1",2,1);
for ( i=3;i<=10000;i++){
SpreadsheetAddRow(obj,"150,ENGL,95,Poetry 1",i,1);
}
spreadsheetwrite(obj, theFile,"",true,false);
myFormat = StructNew();
myFormat.color = "blue";
myFormat.bold = "true";
myFormat.underline = "true";
myFormat.alignment = "center";
myFormat.font = "Arial";
secondFormat = StructNew();
secondFormat.color = "red";
secondFormat.bold = "true";
secondFormat.underline = "true";
secondFormat.alignment = "center";
secondFormat.font = "Arial";
function func(spreadsheetObj, row)
{
if(row % 2 eq 1){
SpreadsheetFormatRow(spreadsheetObj, myFormat, row);
}else{
SpreadsheetFormatRow(spreadsheetObj, secondFormat, row);
}
return spreadsheetObj
}
processedSpreadsheet = StreamingSpreadsheetProcess(#theFile#, func)
writeoutput(processedSpreadsheet.SummaryInfo.SPREADSHEETTYPE)
spreadsheetwrite(processedSpreadsheet, "#theFile1#", "", "yes", "no")
</cfscript>
<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetProcessInput.xlsx";
theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetProcessOutput.xlsx";
obj = streamingSpreadsheetNew("theSheet",4)
SpreadsheetAddRow(obj,"150,ENGL1,,",1,1);
SpreadsheetAddRow(obj,"150,ENGL2,97,Poetry 1",2,1);
for ( i=3;i<=10000;i++){
SpreadsheetAddRow(obj,"150,ENGL,95,Poetry 1",i,1);
}
spreadsheetwrite(obj, theFile,"",true,false);
myFormat = StructNew();
myFormat.color = "blue";
myFormat.bold = "true";
myFormat.underline = "true";
myFormat.alignment = "center";
myFormat.font = "Arial";
secondFormat = StructNew();
secondFormat.color = "red";
secondFormat.bold = "true";
secondFormat.underline = "true";
secondFormat.alignment = "center";
secondFormat.font = "Arial";
function func(spreadsheetObj, row)
{
if(row % 2 eq 1){
SpreadsheetFormatRow(spreadsheetObj, myFormat, row);
}else{
SpreadsheetFormatRow(spreadsheetObj, secondFormat, row);
}
return spreadsheetObj
}
processedSpreadsheet = StreamingSpreadsheetProcess(#theFile#, func)
writeoutput(processedSpreadsheet.SummaryInfo.SPREADSHEETTYPE)
spreadsheetwrite(processedSpreadsheet, "#theFile1#", "", "yes", "no")
</cfscript>
The script creates a streaming spreadsheet, applies conditional formatting to its rows, and saves the processed spreadsheet. The formatting options are defined as structs. The UDF func accepts the spreadsheet object and the row index as parameters. The function checks for even and odd row index and applies cell formatting accordingly. The streamingSpreadsheetProcess function then processes the spreadsheet using the formatting UDF. The resultant spreadsheet has the defined formatting applied to it.
Output - truncated
Example- using async
<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetInput.xlsx";
theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) &"streamingSpreadsheetOutput.xlsx";
obj = streamingSpreadsheetNew("theSheet",4)
SpreadsheetAddRow(obj,"150,ENGL1,1,",1,1);
SpreadsheetAddRow(obj,"150,ENGL2,97,Poetry 1",2,1);
for ( i=3;i<=100000;i++){
SpreadsheetAddRow(obj,"150,ENGL,95,Poetry 1",i,1);
}
spreadsheetwrite(obj, theFile,"",true,false);
myFormat = StructNew();
myFormat.color = "blue";
myFormat.bold = "true";
myFormat.underline = "true";
myFormat.alignment = "center";
myFormat.font = "Arial";
secondFormat = StructNew();
secondFormat.color = "red";
secondFormat.bold = "true";
secondFormat.underline = "true";
secondFormat.alignment = "center";
secondFormat.font = "Arial";
function func(spreadsheetObj, row)
{
if(row % 2 eq 1){
SpreadsheetFormatRow(spreadsheetObj, myFormat, row);
}else{
SpreadsheetFormatRow(spreadsheetObj, secondFormat, row);
}
return spreadsheetObj
}
function writeResult(spreadsheetObj) {
spreadsheetwrite(spreadsheetObj, "#theFile1#", "", "yes", "no")
}
processedSpreadsheet = StreamingSpreadsheetProcess(#theFile#, func,{"sheetName":"theSheet", "async":true})
processedSpreadsheet.then(writeResult)
data =processedSpreadsheet.get();
writeoutput(data.SummaryInfo.SPREADSHEETTYPE)
writeOutput(" ")
writeoutput(data.SummaryInfo.SHEETNAMES)
fileExist = FileExists(theFile1);
writeOutput(" ")
writeOutput("#fileExist#")
writeOutput(" ")
</cfscript>
<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetInput.xlsx";
theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) &"streamingSpreadsheetOutput.xlsx";
obj = streamingSpreadsheetNew("theSheet",4)
SpreadsheetAddRow(obj,"150,ENGL1,1,",1,1);
SpreadsheetAddRow(obj,"150,ENGL2,97,Poetry 1",2,1);
for ( i=3;i<=100000;i++){
SpreadsheetAddRow(obj,"150,ENGL,95,Poetry 1",i,1);
}
spreadsheetwrite(obj, theFile,"",true,false);
myFormat = StructNew();
myFormat.color = "blue";
myFormat.bold = "true";
myFormat.underline = "true";
myFormat.alignment = "center";
myFormat.font = "Arial";
secondFormat = StructNew();
secondFormat.color = "red";
secondFormat.bold = "true";
secondFormat.underline = "true";
secondFormat.alignment = "center";
secondFormat.font = "Arial";
function func(spreadsheetObj, row)
{
if(row % 2 eq 1){
SpreadsheetFormatRow(spreadsheetObj, myFormat, row);
}else{
SpreadsheetFormatRow(spreadsheetObj, secondFormat, row);
}
return spreadsheetObj
}
function writeResult(spreadsheetObj) {
spreadsheetwrite(spreadsheetObj, "#theFile1#", "", "yes", "no")
}
processedSpreadsheet = StreamingSpreadsheetProcess(#theFile#, func,{"sheetName":"theSheet", "async":true})
processedSpreadsheet.then(writeResult)
data =processedSpreadsheet.get();
writeoutput(data.SummaryInfo.SPREADSHEETTYPE)
writeOutput(" ")
writeoutput(data.SummaryInfo.SHEETNAMES)
fileExist = FileExists(theFile1);
writeOutput(" ")
writeOutput("#fileExist#")
writeOutput(" ")
</cfscript>
<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "streamingSpreadsheetInput.xlsx";
theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) &"streamingSpreadsheetOutput.xlsx";
obj = streamingSpreadsheetNew("theSheet",4)
SpreadsheetAddRow(obj,"150,ENGL1,1,",1,1);
SpreadsheetAddRow(obj,"150,ENGL2,97,Poetry 1",2,1);
for ( i=3;i<=100000;i++){
SpreadsheetAddRow(obj,"150,ENGL,95,Poetry 1",i,1);
}
spreadsheetwrite(obj, theFile,"",true,false);
myFormat = StructNew();
myFormat.color = "blue";
myFormat.bold = "true";
myFormat.underline = "true";
myFormat.alignment = "center";
myFormat.font = "Arial";
secondFormat = StructNew();
secondFormat.color = "red";
secondFormat.bold = "true";
secondFormat.underline = "true";
secondFormat.alignment = "center";
secondFormat.font = "Arial";
function func(spreadsheetObj, row)
{
if(row % 2 eq 1){
SpreadsheetFormatRow(spreadsheetObj, myFormat, row);
}else{
SpreadsheetFormatRow(spreadsheetObj, secondFormat, row);
}
return spreadsheetObj
}
function writeResult(spreadsheetObj) {
spreadsheetwrite(spreadsheetObj, "#theFile1#", "", "yes", "no")
}
processedSpreadsheet = StreamingSpreadsheetProcess(#theFile#, func,{"sheetName":"theSheet", "async":true})
processedSpreadsheet.then(writeResult)
data =processedSpreadsheet.get();
writeoutput(data.SummaryInfo.SPREADSHEETTYPE)
writeOutput(" ")
writeoutput(data.SummaryInfo.SHEETNAMES)
fileExist = FileExists(theFile1);
writeOutput(" ")
writeOutput("#fileExist#")
writeOutput(" ")
</cfscript>
