Note:

You can find the CFFiddle demo of this function and other spreadsheet functions as part of a project that is shared with you.

Click the button below to launch CFFiddle.

To copy the project in your workspace in CFFiddle, follow the steps below:

  1. Log in with your Gmail or Facebook credentials.
  2. Navigate to the project in the left pane.
  3. Once you make some changes in any cfm in the project, a pop up displays asking you to save the project.
  4. Give the project a suitable name and click Save.

Description

Formats cells within a specified range.

Returns

Nothing

Category

Syntax

SpreadsheetFormatCellRange (spreadsheetObj, format, startRow, startColumn, endRow, endColumn)

See also

History

ColdFusion 9.0.1: Added the function.

Parameters

Parameter

Description

spreadsheetObj

The Excel spreadsheet object for which you want to format the cells.

format

A structure that contains format information.

startRow

The number of the first row to format.

startColumn

The number of the first column to format.

endRow

The number of the last row to format.

endColumn

The number of the last column to format.

Example

<cfscript>
	ArtOrders=QueryExecute("SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders
	ORDER BY orderid",[],{datasource="cfartgallery"});
	// Set the file path in the same location as this cfm
	myFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "myexcelfile.xls";
	// create spreadsheet object. Set xml to true as this is an xlsx file
	mySheet=SpreadsheetNew("ColdFusion",false);
	// Create another worksheet coldfusion1
	SpreadsheetCreateSheet(mySheet,"ColdFusion1");
	// Set headers for the xlsx file
	SpreadSheetAddRow(mySheet,"Order,First Name,Last Name,Address,Amount,City");
	SpreadSheetAddRows(mySheet,ArtOrders);
	
	// set coldfusion as active sheet
	SpreadsheetSetActiveSheet(mySheet,"ColdFusion1");
	// new query for the new sheet
	Artists=QueryExecute("Select * from APP.ARTISTS",[],{datasource="cfartgallery"});
	SpreadSheetAddRow(mySheet,"ARTISTID,First Name,Last Name,Address,CITY,STATE,POSTALCODE,EMAIL,PHONE,FAX,THEPASSWORD");
	SpreadSheetAddRows(mySheet,Artists);
	
		
	// Define the structure for formatting a cell range
	myFormat=StructNew();
	myFormat.color="blue";
	myFormat.bold="true";
	myFormat.underline="true";
	myFormat.alignment="center";
	myFormat.font="Arial";
	
	// Set formatting to cell range (7,1) to (8,9)
	SpreadsheetFormatCellRange(mySheet,myFormat,7,1,8,9); 
	SpreadsheetWrite(mySheet,"#myFile#",true);
	
</cfscript>

Output

spreadsheetformatcellrange output
spreadsheetformatcellrange output

<cfquery
name="courses" datasource="cfdocexamples"
cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">
SELECT CORNUMBER,DEPT_ID,CORLEVEL,COURSE_ID,CORNAME,CORDESC,LASTUPDATE
FROM COURSELIST
</cfquery>
<cfscript>
///We need an absolute path, so get the current directory path.
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "courses.xls";
//Create a new Excel spreadsheet object and add the query data.
theSheet = SpreadsheetNew("CourseData");
SpreadsheetAddRows(theSheet,courses);
// Define a format for the column.
format1=SructNew()
format1.font="Courier";
format1.fontsize="10";
format1.color="dark_blue;";
format1.italic="true";
format1.bold="true";
format1.alignment="left";
SpreadsheetFormatCellRange(theSheet,format1, 3,4,30,10);
</cfscript>
<!--- Write the spreadsheet to a file, replacing any existing file. --->
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"
sheet=1 sheetname="courses" overwrite=true>

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy