1 Reply Latest reply on Sep 5, 2014 7:10 AM by CFMXDeveloper

    Multiple Excel (xlsx) Sheets And Formatting

    CFMXDeveloper

      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, but the format needs to be "xlsx". 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.  I've can format anything in the first sheet except the row height or column width.  I've tried the CF spreadsheet functions (SetColumnWidth) and the POI functions.  Neither do anything.

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