• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Multiple Excel (xlsx) Sheets And Formatting

New Here ,
Sep 04, 2014 Sep 04, 2014

Copy link to clipboard

Copied

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

Views

322

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

New Here , Sep 05, 2014 Sep 05, 2014

I wasn't sure how to mark this as answered, but it is.  A user on another site pointed me in the direction of the POI function setActiveSheet(0).  It worked beautifully to remove the linking/grouping of the two sheets and preserve the column widths of the first sheet.

Votes

Translate

Translate
New Here ,
Sep 05, 2014 Sep 05, 2014

Copy link to clipboard

Copied

LATEST

I wasn't sure how to mark this as answered, but it is.  A user on another site pointed me in the direction of the POI function setActiveSheet(0).  It worked beautifully to remove the linking/grouping of the two sheets and preserve the column widths of the first sheet.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation