Excel Sheet Grouping and Format

Question

I've searched for answers here many times, but this is my first time asking a question of my own. I have about 15 years of Cf experience with versions 4-10, so, although I'm no expert, I know what I'm doing with most things.

I currently have a project that I took over from another developer. Part of the application outputs data from a SQL Server 2008 database to an Excel file that has three sheets or tabs, which are created in order, left to right. I am working to fix a problem in which when opening the Excel file, the first and third sheets are "grouped". Next to the file name in the title bar, it says [Group] and both sheets appear to be active. The problem with this is that, if the user were to, say, highlight row 5 in sheet 3, row 5 also gets highlighted in sheet 1. I have searched everywhere with every search string I can think of and can't find anything! I even tried recreating a skeleton of the functionality based on the CF documentation and ran into the same problems. I have figured out a few things on my own.

First, the middle sheet is not affected at all. Second, once in Excel, if you select the middle sheet, everything else gets fixed (of course, that's not a great solution for the end user). Third, if I create the spreadsheet with "xmlformat" equal to "false" and output it as an "xls", this problem goes away. Finally, I tried adding an extra line of code to set the active sheet back to the first one once the third is generated. This did break the grouping problem, but it undid any row or column sizing in the first sheet and I can't get it back.

My code is below. If anyone can help, I'd be very appreciative!

    <cfscript>

    /* Format for data rows */
    dataFormat = StructNew();
    dataFormat.font="Arial"; 
    dataFormat.fontsize="10";
    dataFormat.italic="false"; 
    dataFormat.bold="false"; 
    dataFormat.alignment="left"; 
    dataFormat.textwrap="true"; 
    dataFormat.fgcolor="white"; 
    dataFormat.bottomborder="thin"; 
    dataFormat.bottombordercolor="black"; 
    dataFormat.topbordercolor="black"; 
    dataFormat.topborder="thin"; 
    dataFormat.leftborder="thin"; 
    dataFormat.leftbordercolor="black"; 
    dataFormat.rightborder="thin"; 
    dataFormat.rightbordercolor="black"; 
    dataFormat.locked = "true";
    //dataFormat.dataformat="@";

    spreadsheetVar= spreadSheetNew("New", "true");
    spreadsheetSetCellValue(spreadsheetVar, "123", 1, 1);
    spreadsheetSetCellValue(spreadsheetVar, "This is some medium length text.", 1, 2);
    spreadsheetSetCellValue(spreadsheetVar, "Here is longer text that will need to wrap once I am done.", 1, 3);
    spreadsheetSetCellValue(spreadsheetVar, "This is a very short amount of text.", 1, 4);
    spreadsheetSetCellValue(spreadsheetVar, "And here is the final column that needs to be formatted.", 1, 5);

    spreadsheetFormatRow(spreadsheetVar, dataFormat, 1);
    spreadsheetSetColumnWidth(spreadsheetVar, 1, 30);

    spreadsheetCreateSheet(spreadsheetVar,"A");
    spreadsheetSetActiveSheet(spreadsheetVar,"A");
    spreadsheetSetCellValue(spreadsheetVar, "Sheet A test", 1, 1);

    spreadsheetCreateSheet(spreadsheetVar,"B"); 
    spreadsheetSetActiveSheet(spreadsheetVar,"B");
    spreadsheetSetCellValue(spreadsheetVar, "Sheet B test", 1, 1);

    //This line gets rid of the grouping, but destroys the formatting for the sheet "New".  To see the grouping, comment it out.
    spreadsheetSetActiveSheet(spreadsheetVar, "New");

</cfscript>
<cfspreadsheet action="write" filename="c:/mySpreadSheet.xlsx"  name="spreadsheetVar" overwrite="true" >

+1. Wish all first questions were this well composed and researched :) — Leigh

Leigh Leigh · Accepted Answer · 2014-09-04T21:17:25

I am not sure whether CF or POI is ultimately at fault, but the issue revolves around the difference between two Excel concepts: active and selected sheets.

Selection allows you to manipulate one or more sheets at a time. Therefore, multiple sheets can be marked as selected. Active, on the other hand, is roughly analogous to the sheet that has the "focus". So obviously only a single sheet can be marked as active at any given time.

What is happening in your code is that CF marks the third sheet as active. However, behind the scenes, it also marks the first sheet as selected. As a result, both sheets are in play when the workbook is opened. That is why Excel treats them as grouped.

    // display "active" and "selected" sheets
    wb = spreadsheetVar.getWorkBook();
    writeOutput("active sheet ="& wb.getActiveSheetIndex());
    for (i = 0; i < wb.getNumberOfSheets(); i++) {
        writeOutput("<br>["& i &"] selected = "& wb.getSheetAt(i).isSelected());
    }

I ran your code under CF10 and resetting the active sheet seemed to fix the problem. (The column widths were not affected). Since it sounds like CF9 behaves differently, you will probably need to dip into the underlying POI library to get around the issue. Try using POI to change the active sheet, instead of the CF function:

   // note: sheet indexes are zero based
   spreadsheetVar.getWorkBook().setActiveSheet(0)

Internally, that is the method SpreadsheetSetActiveSheet uses anyway. However, I suspect the CF function also performs a few other operations as well. Those "other" operations are probably what cause the column widths to be lost. Invoking the underlying method directly lets you avoid those negative side effects.

Excel Sheet Grouping and Format

1 Answers