tracker issue : CF-3505475

select a category, or use search below
(searches all categories and all time range)

Formatting Gives Unexpected Results When Adding Spreadsheet Using cfspreadsheet action=update

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/

Reporter/Name(from Bugbase): dan bracuk / dan bracuk (Dan Bracuk)

Created: 02/24/2013

Components: Document Management, Office Integration

Versions: 9.0.1

Failure Type:

Found In Build/Fixed In Build: 9.0.1 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2003 Server

Vote Count: 0

Duplicate ID:	CF-3114288

Problem Description:   This document,, 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:  	
	Sheet1 = Spreadsheetnew("Sheet1");
	SpreadSheetAddRow(Sheet1, "fred");
	SheetNumber = 1;
	Format = {};
	format.bold = true;
	format.color = "blue";
	MYfile = "d:\dw\dwtest\dan\abc.xls";
	SpreadsheetFormatCell(Sheet1, Format, 1, 1); 
	Values = "a,b,a,b";
	<cfspreadsheet action="write" filename="#MYFile#" name="Sheet1"  
		sheet=1 sheetname="fred" overwrite=true>
	<cfloop list="a" index="letter">
	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 ++;	
	<cfspreadsheet action="update" filename="#MYFile#" name="ThisSheet"  
		sheet="#sheetNumber#" sheetname="#letter#" >

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.

    	// 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); 
    	//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);
          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);

----------------------------- 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:



bug verified on CF 9,0,2,282541 and CF10. formatting info is lost with action update. This is fixed on CF11.
Comment by Piyush K.
16216 | November 15, 2013 09:06:07 AM GMT