Warm tip: This article is reproduced from stackoverflow.com, please click
coldfusion cfml

SpreadsheetFormats not working as expected

发布于 2020-03-27 10:28:38

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>

Here are the results Green highlight is correct format, orange is incorrect.

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.

Questioner
Mike
Viewed
47
Ageax 2019-07-03 23:20

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().