tracker issue : CF-3505475

Duplicate ID:	CF-3114288

Problem Description:   This document, http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html, has an example for creating a workbook with two sheets.  It says to write the file and then update it with a 2nd spreadsheet object.  If you do that, and you have formatting code in both objects, the code in the second object won't be applied.  Plus, the code in the first object will be applied to the second.

Steps to Reproduce:  Run this code:  	
       <cfscript>
	Sheet1 = Spreadsheetnew("Sheet1");
	SpreadSheetAddRow(Sheet1, "fred");
	SheetNumber = 1;
	
	Format = {};
	format.bold = true;
	format.color = "blue";
	MYfile = "d:\dw\dwtest\dan\abc.xls";
	writedump(format);
	SpreadsheetFormatCell(Sheet1, Format, 1, 1); 
	
	Values = "a,b,a,b";
	
	</cfscript>
	<cfspreadsheet action="write" filename="#MYFile#" name="Sheet1"  
		sheet=1 sheetname="fred" overwrite=true>
	
	<cfloop list="a" index="letter">
	<cfscript>
	RowNumber = 1;
	SheetNumber ++;
	ThisSheet = SpreadSheetNew(letter);
	for (i = 1; i <= 4; i ++) {

	SpreadsheetAddRow(ThisSheet, ListGetAt(Values, i));
	if (ListGetAt(Values, i) == "a") {
	format.color = "green";
	SpreadsheetFormatCell(ThisSheet, Format, RowNumber, 1); 
           //SpreadsheetFormatCell(ThisSheet, {bold="true",color="green"}, RowNumber, 1); 
	}
	else {
	format.color = "red";
	SpreadsheetFormatCell(ThisSheet, Format, RowNumber, 1); 
          //SpreadsheetFormatCell(ThisSheet, {bold="true",color="green"}, RowNumber, 1); 
	
	}
	RowNumber ++;	
	}
	
	</cfscript>
	<cfspreadsheet action="update" filename="#MYFile#" name="ThisSheet"  
		sheet="#sheetNumber#" sheetname="#letter#" >
	
	</cfloop>


Actual Result:   In sheet a, cells a1 and a3 have the letter a in bold blue font.  Cells a2 and a4 have the letter b, unformatted.


Expected Result:  cells a1 and a3 to be in bold green font.  Cells a2 and a4 to be in bold red font.

Any Workarounds:  Don't use cfspreadsheet action=update to add new sheets.  Use SpreadSheetCreateSheet() instead.  Sample below.

    <cfscript>
    	// Create new sheet and add one row
    	Workbook = Spreadsheetnew("Sheet1");
    	SpreadSheetAddRow(Workbook, "fred");
    
    	// Apply formatting to new cell	
    	format = {bold = true, color = "blue"};
    	SpreadsheetFormatCell(Workbook, Format, 1, 1); 
    	WriteDump(format);
    
    	//Add another worksheet and make it active
    	letter = "a";
    	SpreadSheetCreateSheet(Workbook, letter);
    	SpreadSheetSetActiveSheet(Workbook, letter);
    
    	//Add rows to the active worksheet
    	RowNumber = 1;
    	Values = "a,b,a,b";
    	for (i = 1; i <= 4; i ++) {
    		SpreadsheetAddRow(Workbook, ListGetAt(Values, i), RowNumber, 1);
    		if (ListGetAt(Values, i) == "a") {
    			Format = {bold = true, color = "green"};
    			SpreadsheetFormatCell(Workbook, Format, RowNumber, 1); 
    			WriteDump(var=format, label="RowNumber="& RowNumber);
    		}
    		else {
    			Format = {bold = true, color = "red"};
    			SpreadsheetFormatCell(Workbook, Format, RowNumber, 1); 
    			WriteDump(var=format, label="RowNumber="& RowNumber);
    		}
    		RowNumber++;   
    	}
    	/*
          Set the active worksheet back to the original.  If you don't 
          the last worksheet name will be the name of the spreadsheet
          object, in this case, workbook.
          */
           SpreadSheetSetActiveSheetNumber(Workbook, 1);
    	//Finally, save it to disk
    	SpreadSheetWrite(Workbook, "c:/path/to/yourFile.xls", true);
    </cfscript>

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3505475

External Customer Info:
External Company:  
External Customer Name: Dan Bracuk
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments: