Spreadsheet Cell Formatting

Question

Edit at the end

Can anyone see what I am doing wrong? These are my expectations and observations:

I expect a worksheet named Search Parameters to have cell a1 in bold red font. I get what I expect.

I expect a worksheet named "4D,CCCU,SDAU" which has data starting in row 3. I get this.

I expect row 3 to have the text "bold font 3 green true", and to be in bold green font. I get the text, but I get bold red font, which matches cell a1 in the other sheet. In fact, the formatting in this cell will always match cell a1 from the other sheet.

I expect the remaining cells to have a value like "normal font 4 blue true" and be in blue bold font. I get the values, but no formatting at all.

My writedumps always show me the values I expect.

The code is below. AddNewRow is a udf.

To reiterate, the question is, why are the cells not formatting the way I expect them to?

    <cfscript>
    FileName = "d:\dw\dwweb\work\Antibiotics.xls";

    SearchParameters = QueryNew("Item,Value","varchar,varchar");
    AddNewRow(SearchParameters, "Item,Value","Date Range,#DateRange#");

    SearchParametersSheet = Spreadsheetnew("SearchParameters");
    SpreadSheetAddRows(SearchParametersSheet, SearchParameters);
    SheetNumber = 1;
    DrugsByCategory = QueryNew("Item,font","varchar,varchar");
    format1 = StructNew();
    format1.bold = true;
    format1.color = "red";
    SpreadsheetFormatCell(SearchParametersSheet, format1, 1, 1); 
    </cfscript>

    <cfspreadsheet action="write" filename="#FileName#"
                name="SearchParametersSheet"  
    sheet=1 sheetname="Search Parameters" overwrite=true>

    <cfoutput query="AllDrugs" group="CategoryName">
    <cfset AddNewRow(DrugsByCategory,"Item#Chr(30)#font"
                 ,"#CategoryName##Chr(30)#bold",Chr(30))>
    <cfoutput>
    <cfset AddNewRow(DrugsByCategory,"Item#Chr(30)#font"
                 ,"#StandardSpelling##Chr(30)#normal",Chr(30))>
    </cfoutput>
    </cfoutput>


    <cfquery name="units" dbtype="query">
    select distinct unit
    from initialresults
    </cfquery>

    <cfloop query="units">
    <cfscript>
    SheetNumber ++;
    ThisSpreadSheet = SpreadSheetNew(unit);
    RowNumber = 3;

    for (i = 1; i <= DrugsByCategory.recordcount; i ++) {
    // note that the data might contain commas, so we have to use two commands 
    SpreadsheetAddRow(ThisSpreadSheet, "", RowNumber, 1);
    SpreadSheetSetCellValue(ThisSpreadSheet, DrugsByCategory.Item[i], RowNumber, 1);

    if (DrugsByCategory.font[i] == "bold"){
    format1.bold = true;
    format1.color = "green";
    writedump(var="#format1#" label="#RowNumber#");

    SpreadSheetSetCellValue(ThisSpreadSheet
                , "bold font #Rownumber# #format1.color# #format1.bold#"
                , RowNumber, 1);
    SpreadsheetFormatCell(ThisSpreadSheet, format1, RowNumber, 1); 


    }
    else {
    format1.color = "blue";
    format1.bold = true;
    writedump(var="#format1#" label="#RowNumber#");
    SpreadSheetSetCellValue
                 (ThisSpreadSheet, "normal font 
                  #Rownumber# #format1.color# #format1.bold#"
                , RowNumber, 1);
    SpreadsheetFormatCell(ThisSpreadSheet, format1, RowNumber, 1); 
    }
    RowNumber ++;

    }

    </cfscript>
    <cfspreadsheet action="update" filename="#FileName#" name="ThisSpreadSheet"  
        sheet=#SheetNumber# sheetname="#unit#" >

    </cfloop>

Edit Starts Here

This is the self contained code Leigh suggested. Travis's suggestion wrt format methods are commented out, but when I used them, the results did not change.

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

The results are: Sheet fred is as expected, bold blue font in cell a1.

In sheet a, cells a1 and a3 have the letter a in bold blue font. I expected bold green. Cells a2 and a4 have the letter b, unformatted. I expected bold and red.

Am I doing something stupid, or is something wrong. I am using ColdFusion 9.01 and Excel 2010.

Answer 1

I suspect you may have run into a bug with <cfspreadsheet action="update" ..>.

When CF performs the "update" it attempts to copy everything from sheet "a" (values, formats, formulas, etcetera) into a new sheet within the workbook you saved on disk. Apparently CF is not copying everything. Hence the missing formats. You can prove this by saving sheet "a"(only) to a separate file. Notice when you use action="write" the cell formats are correct (red and green appear)?

  ...
  <!--- save separate copy of "ThisSheet" only --->
  <cfspreadsheet action="write" filename="c:/thisSheetOnly.xls" name="ThisSheet"  ....>
  <!--- attempt to combine the sheets --->
  <cfspreadsheet action="update" filename="#MyFile#" name="ThisSheet" ... >

Honestly, creating a complete copy of an entire worksheet is tricky. There are a lot of moving pieces and it is easy to miss something, which is what seems to be happening here. Personally, I would avoid using action="update" if at all possible. Too many things can go wrong. Besides, in most cases you do not need it. You can easily create a workbook, then add and populate multiple sheets. Here is a simplified example that creates and saves two sheets, then saves them to disk. (Tested with CF10)

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