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

SpreadsheetFormatColumn

Last update:
May 18, 2026
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 the contents of a single column of an Excel spreadsheet object.

Returns

Nothing

Category

Spreadsheet functions, Microsoft Office integration functions

Syntax

SpreadsheetFormatColumn (spreadsheetObj, format, column)

See also

SpreadsheetFormatCell, SpreadsheetFormatColumns, SpreadsheetFormatRow, SpreadsheetFormatRows

History

ColdFusion 9: Added the function.

Parameters

Parameter
Description
spreadsheetObj
The Excel spreadsheet object in which to set the format.
format
A structure containing the format information. For details see SpreadsheetFormatCell.
column
The column number.

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 column
 myFormat=StructNew();
 myFormat.color="blue";
 myFormat.bold="true";
 myFormat.underline="true";
 myFormat.alignment="center";
 myFormat.font="Arial";
 
 // Set formatting to column 4
 SpreadsheetFormatColumn(mySheet,myFormat,4); 
 SpreadsheetWrite(mySheet,"#myFile#",true);
 
</cfscript>

Output

Figure: spreadsheetformatcolumn output
spreadsheetformatcolumn 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";

format1.textwrap="true";

format1.fgcolor="pale_blue";

format1.bottomborder="dotted";

format1.bottombordercolor="blue_grey";

format1.leftborder="thick";

format1.leftbordercolor="blue_grey";

format1.rightborder="thick";

format1.rightbordercolor="blue_grey";

SpreadsheetFormatColumn(theSheet,format1,5);

</cfscript>

<!--- Write the spreadsheet to a file, replacing any existing file. --->

<cfspreadsheet action="write" filename="#theFile#" name="theSheet"

sheet=1 sheetname="courses" overwrite=true>

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