I am able to populate data from a query into a spreadsheet. However, I am having problems getting "ranged" formatting to work properly. The formatting for specific column (date) and row (header) work fine. But SpreadsheetFormatColumns, ...Rows, ...CellRange is not. I need to set the font and fontsize to the whole dataset.
Here is what I have tried.
<cfscript>
//Current directory path.
theFile = GetDirectoryFromPath(GetCurrentTemplatePath()) & "GridDump.xls";
//Create a new Excel spreadsheet object and add the query data.
theSheet = SpreadsheetNew("Raw Data");
FormatDate.dataformat = "dd-mmm-yy";
//Get Row Count and Row Range
RC = toString(result.recordcount+1);
RR = "1-" & RC;
//Get Column Count
CC = toString(ListLen(GridFieldNames));
//Get Column Letter
CL = chr(CC + 64);
//Get Column Range (Nummerical)
CRN = "1-" & CC;
//Get Column Range (Alphabetical)
CRA = "A-" & CL;
//Set Sheet Format
WholeSheet = StructNew();
WholeSheet.font="Consolas";
WholeSheet.fontsize=12;
//Set header Row Format
HeadRow = StructNew();
HeadRow.bold="true";
//Insert the Header Row
SpreadsheetAddRow(theSheet,GridFieldNames);
//Insert the Data
SpreadsheetAddRows(theSheet,result);
//Format the Data
SpreadsheetFormatCellRange(theSheet,WholeSheet,1,1,RC,CC);
//SpreadsheetFormatRows(theSheet,WholeSheet,RR);
//SpreadsheetFormatColumns(theSheet,WholeSheet,CRN);
SpreadsheetFormatRow(theSheet,HeadRow,1);
//Header Row
SpreadsheetFormatColumn(theSheet,FormatDate,1);//Date Column
SpreadsheetAddFreezePane(theSheet,0,1);//Top Row Only
//SpreadSheetAddAutofilter(theSheet,"A1:J1");
</cfscript>
I'm getting the same result for all three of the "ranged" formatting functions. The format stops part way through the spreadsheet. I expect the whole dataset to accept any of the ranged function formats.
I got the same result with CF 2018,0,04,314546. Could just be a limitation of XLS format. Switching to XLSX worked fine for me:
theSheet = SpreadsheetNew("Raw Data", true);
YMMV, but what also worked with CF2018 was using SpreadsheetFormatColumns()
instead of SpreadsheetFormatCellRange()
.