Excel Coloring 1

[webmigit]

Does anyone know of a way to color rows and text colors in excel (using cfcontent)? Even cell by cell is fine, I'd just like to highlight the header row...

Tony

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[bhightower]

here is a test of what i use....

and go here to see the color index for excel

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/owcvba10/html/ocprocolorindex.asp

<cfscript>
	temp = StructNew();
	
	temp["1"] 						= structNew();
	temp["1"]["FUND"] 		= "1010";
	temp["1"]["AGENCY"] 	= "111";
	temp["1"]["ORG"] 		= "1000";
	
	temp["2"] 						= structNew();
	temp["2"]["FUND"] 		= "2020";
	temp["2"]["AGENCY"] 	= "222";
	temp["2"]["ORG"] 		= "2000";
	
	temp["3"] 						= structNew();
	temp["3"]["FUND"] 		= "3030";
	temp["3"]["AGENCY"] 	= "333";
	temp["3"]["ORG"] 		= "3000";
	
	temp["4"] 						= structNew();
	temp["4"]["FUND"] 		= "4040";
	temp["4"]["AGENCY"] 	= "444";
	temp["4"]["ORG"] 		= "4000";
	
	temp["5"] 						= structNew();
	temp["5"]["FUND"] 		= "5050";
	temp["5"]["AGENCY"] 	= "555";
	temp["5"]["ORG"] 		= "5000";
</cfscript>
<!--- Try to connect to the Excel application object --->
<CFTRY>
    <!--- If it exists, connect to it --->
    <CFOBJECT
        ACTION="CONNECT"
        CLASS="Excel.Application"
        NAME="objExcel"
        TYPE="COM">
  <CFCATCH>
    <!--- The object doesn't exist, so create it --->
    <CFOBJECT
        ACTION="CREATE"
        CLASS="Excel.Application"
        NAME="objExcel"
        TYPE="COM">
  </CFCATCH>
</CFTRY>

<CFSCRIPT>
    // Open Excel in the background
    objExcel.Visible = false;
    // Disable client alerts such as: 'Save this workbook?'
    objExcel.DisplayAlerts = false;    
    // Define the workbooks object 
    objWorkBook = objExcel.Workbooks;
		// Add a new workbook
    objOpenedBook = objWorkBook.Add();
    // Get the WorkSheets' collection 
    objWorkSheets = objExcel.WorkSheets;
    // Add a new worksheet (this will contain our data)
    objWorkSheet = objWorkSheets;
    // Add a new worksheet (this will contain our data)
    objWorkSheet =  objWorkSheets.Add();
    //Set header - 
    objRange = objExcel.Range("A1:A1");
    objF = objRange.Font;
    objF.Size = 16;
    objF.Bold = True;
    objRange.HorizontalAlignment = 1;
    objRange.value = "FAO LIST";
    //Create object Interior for solid field some color
    objRange = objExcel.Range("A1:C1");
    objI = objRange.Interior;
    objI.ColorIndex = 6; // EXCEL COLOR INDEX
    //Set variables from A2 to C2
    objRange = objExcel.Range("A2:C2");
    objIn_V = objRange.Interior;
    objIn_V.ColorIndex = 15;// EXCEL COLOR INDEX
    objV = objRange.Font;
    objV.Size = 08;
    objRange.HorizontalAlignment = 3;
    objRange.VerticalAlignment = 1;
    objLine = objRange.Borders;
    objLine.LineStyle = 1;
    objRange.WrapText = True;
    objRange.ShrinkToFit = True;
    //Create field N
    objRange = objExcel.Range("A2:A2");
    objRange.ColumnWidth = 10.00;
    objRange.Value = "FUND";
    //Create field Date of information
    objRange = objExcel.Range("B2:B2");
    objRange.ColumnWidth = 10.00;
    objRange.Value = "AGENCY";
    //Create field Error type Options available:- Requirements error- Implementation error- Issue
    objRange = objExcel.Range("C2:C2");
    objRange.ColumnWidth = 10.00;
    objRange.Value = "ORG";
		cItem = 3;
		for( x = 1; x LTE structCount(temp); x=x+1 ){
			// COLUMN A
			columnA = "A" & cItem & ":A" & cItem;
	    objRange = objExcel.Range(columnA);
	    objRange.ColumnWidth = 10.00;
	    objRange.Value = temp[X]["FUND"];
			// COLUMN B
			columnB = "B" & cItem & ":B" & cItem;
	    objRange = objExcel.Range(columnB);
	    objRange.ColumnWidth = 10.00;
	    objRange.Value = temp[X]["AGENCY"];
			// COLUMN C
			columnC = "C" & cItem & ":C" & cItem;
	    objRange = objExcel.Range(columnC);
			objRange.NumberFormat = "0000";
	    objRange.ColumnWidth = 10.00;
	    objRange.Value = temp[X]["ORG"];
			cItem = cItem + 1;
		}
    // SaveAs() does not work with workbooks, only with worksheets
		dirPath = GetDirectoryFromPath( GetCurrentTemplatePath() );
		excelFilePath = dirPath & "test.xls";
    objWorkSheet.SaveAs( excelFilePath ,Val(1));
    // Close the document
    objWorkBook.Close();
    // Quit Excel
    objExcel.Quit();
    // Release the object
    objExcel = "Nothing";
</CFSCRIPT>

------------------------------------------------------------------------------
brannonH
if( !succeed ) try( );