ColdFusion9如何从<cfscript>中格式化excel中的列

Question

我正在使用CF9。我创造了一本多页工作簿。我正在尝试为每个工作表设置列的格式。格式只适用于第一个工作表。我怎样才能把它应用到所有的纸张上？此外，我也不知道如何使列宽度为任何工作表工作。

下面是我目前掌握的代码：

    <cfscript>
    qExecSummary = queryNew("");
    queryAddColumn(qExecSummary, "responsible", [1,12,13]);
    queryAddColumn(qExecSummary, "bud_sum", [100,500,1000]);
    queryAddColumn(qExecSummary, "Spent_YTD", [10,50,100]);
    queryAddColumn(qExecSummary, "Name", ["A","B","C"]);
    queryAddColumn(qExecSummary, "Description", ["Descrip1","Descrip2","Descrip3"]);
    queryAddColumn(qExecSummary, "Committed", ["Committed1","Committed2","Committed3"]);


    //Create new workbook with one sheet
    //by default that sheet is the active sheet
    Workbook = SpreadsheetNew("ExecSummary");
    //Add Data to the sheet
    //Formatting
    format1.bold="true";
    format1.fontsize=12;
    format1.font="Calibri";
    format2.bold="true";
    format2.fontsize=18;
    format2.font="Calibri";
    formatNum.dataformat="0.0%";
    //adding the formating to the cells
    //adding the Headers
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    Spreadsheetformatcell(Workbook,format2,1,1);
    SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
    Spreadsheetformatcell(Workbook,format1,3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    Spreadsheetformatcell(Workbook,format1,5,1);
    SpreadSheetSetColumnWidth(Workbook,1,10);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    Spreadsheetformatcell(Workbook,format1,5,2);
    SpreadSheetSetColumnWidth(Workbook,2,10);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    Spreadsheetformatcell(Workbook,format1,5,3);
    SpreadSheetSetColumnWidth(Workbook,3,17);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    Spreadsheetformatcell(Workbook,format1,5,4);
    SpreadSheetSetColumnWidth(Workbook,4,20);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    Spreadsheetformatcell(Workbook,format1,5,5);
    SpreadSheetSetColumnWidth(Workbook,5,15);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    Spreadsheetformatcell(Workbook,format1,5,6);
    SpreadSheetSetColumnWidth(Workbook,6,15);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    Spreadsheetformatcell(Workbook,format1,5,7);
    SpreadSheetSetColumnWidth(Workbook,7,15);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
    Spreadsheetformatcell(Workbook,format1,5,8);
    SpreadSheetSetColumnWidth(Workbook,8,15);
    //check to make sure that data was pulled back by the query
    if (qExecSummary.recordCount) {
        rowNum = 6;
        do {//if data is pulled back loop through it and add it to the correct cell
            SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-5],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-5],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-5],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-5],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-5],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-5],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-5] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-5]/qExecSummary.bud_sum[rowNum-5]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 6 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }

    SpreadsheetCreateSheet(Workbook,"ExecSummary331-333");
    SpreadsheetSetActiveSheet(Workbook,"ExecSummary331-333");

    //adding the Headers
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    Spreadsheetformatcell(Workbook,{bold="true"},1,1);
    Spreadsheetformatcell(Workbook,{fontsize=18},1,1);
    Spreadsheetformatcell(Workbook,{font="Calibri"},1,1);
    SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
    Spreadsheetformatcell(Workbook,format1,3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    Spreadsheetformatcell(Workbook,format1,5,1);
    SpreadSheetSetColumnWidth(Workbook,1,10);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    Spreadsheetformatcell(Workbook,format1,5,2);
    SpreadSheetSetColumnWidth(Workbook,2,10);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    Spreadsheetformatcell(Workbook,format1,5,3);
    SpreadSheetSetColumnWidth(Workbook,3,17);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    Spreadsheetformatcell(Workbook,format1,5,4);
    SpreadSheetSetColumnWidth(Workbook,4,20);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    Spreadsheetformatcell(Workbook,format1,5,5);
    SpreadSheetSetColumnWidth(Workbook,5,15);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    Spreadsheetformatcell(Workbook,format1,5,6);
    SpreadSheetSetColumnWidth(Workbook,6,15);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    Spreadsheetformatcell(Workbook,format1,5,7);
    SpreadSheetSetColumnWidth(Workbook,7,15);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
    Spreadsheetformatcell(Workbook,format1,5,8);
    SpreadSheetSetColumnWidth(Workbook,8,15);
    //check to make sure that data was pulled back by the query
    if (qExecSummary.recordCount) {
        rowNum = 6;
        do {//if data is pulled back loop through it and add it to the correct cell
            SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-5],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-5],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-5],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-5],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-5],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-5],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-5] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-5]/qExecSummary.bud_sum[rowNum-5]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 6 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }
    //check to make sure that data was pulled back by the query
    if (qExecSummary.recordCount) {
        rowNum = 18;
        do {//if data is pulled back loop through it and add it to the correct cell
            SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-17],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-17],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-17],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-17],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-17],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-17],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-17] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-17]/qExecSummary.bud_sum[rowNum-17]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 17 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }
    //check to make sure that data was pulled back by the query
    if (qExecSummary.recordCount) {
        rowNum = 29;
        do {//if data is pulled back loop through it and add it to the correct cell
            SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-28],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-28],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-28],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-28],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-28],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-28],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-28] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-28]/qExecSummary.bud_sum[rowNum-28]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 28 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }

    SpreadsheetCreateSheet(Workbook,"ExecSummary334-336");
    SpreadsheetSetActiveSheet(Workbook,"ExecSummary334-336");

    //adding the Headers
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    Spreadsheetformatcell(Workbook,format2,1,1);
    SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
    Spreadsheetformatcell(Workbook,format1,3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    Spreadsheetformatcell(Workbook,format1,5,1);
    SpreadSheetSetColumnWidth(Workbook,1,10);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    Spreadsheetformatcell(Workbook,format1,5,2);
    SpreadSheetSetColumnWidth(Workbook,2,10);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    Spreadsheetformatcell(Workbook,format1,5,3);
    SpreadSheetSetColumnWidth(Workbook,3,17);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    Spreadsheetformatcell(Workbook,format1,5,4);
    SpreadSheetSetColumnWidth(Workbook,4,20);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    Spreadsheetformatcell(Workbook,format1,5,5);
    SpreadSheetSetColumnWidth(Workbook,5,15);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    Spreadsheetformatcell(Workbook,format1,5,6);
    SpreadSheetSetColumnWidth(Workbook,6,15);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    Spreadsheetformatcell(Workbook,format1,5,7);
    SpreadSheetSetColumnWidth(Workbook,7,15);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
    Spreadsheetformatcell(Workbook,format1,5,8);
    SpreadSheetSetColumnWidth(Workbook,8,15);
    //check to make sure that data was pulled back by the query
    if (qExecSummary.recordCount) {
        rowNum = 6;
        do {//if data is pulled back loop through it and add it to the correct cell
            SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-5],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-5],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-5],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-5],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-5],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-5],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-5] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-5]/qExecSummary.bud_sum[rowNum-5]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 6 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }
    //check to make sure that data was pulled back by the query
    if (qExecSummary.recordCount) {
        rowNum = 18;
        do {//if data is pulled back loop through it and add it to the correct cell
            SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-17],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-17],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-17],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-17],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-17],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-17],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-17] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-17]/qExecSummary.bud_sum[rowNum-17]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 17 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }
    //check to make sure that data was pulled back by the query
    if (qExecSummary.recordCount) {
        rowNum = 29;
        do {//if data is pulled back loop through it and add it to the correct cell
            SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-28],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-28],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-28],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-28],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-28],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-28],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-28] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-28]/qExecSummary.bud_sum[rowNum-28]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 28 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }

    SpreadsheetSetActiveSheet(Workbook,"ExecSummary");
</cfscript>

<cfheader name="Content-Disposition" value='attachment; filename="execSummaryNew.xls"'>
<cfcontent type="application/msexcel" variable="#SpreadsheetReadBinary(Workbook)#" reset="true">

我知道它真的很长，但是因为我必须使用DB，所以我不得不这样做，我不得不使用几个查询来获得我想要的数据。

我尝试过让SpreadSheetSetColumnWidth在<cfscript>中格式化列，但这也不起作用。现在，格式只适用于第一个工作表，除了宽度之外，该宽度不适用于任何工作表。

编辑

我几乎把一切都做好了。现在只是格式问题。它适用于2.5张纸。在第三张纸上，它中途停止工作。有8列，最后4列没有采用格式设置。我已经试过所有我能想到的让它被拿走的方法了。我添加了它作为一个例子，这里，我知道它很长，但是我不能在任何地方重现这个问题。我只在生产中买到。我已经复制了我在我的电脑和上面链接的例子。无论是在我的本地计算机上还是在示例中，它都工作得很好。但是我有CF 2016，prod服务器是CF 9。

我知道有很多代码要看，但如果有人能帮忙的话，那就太好了。我的头撞在墙上，试图看看我把它搞砸的地方，但是对于每一张纸，我在它之前复制了工作表，然后更改了查询号，所以它应该是工作的。

我也可以更新这里发布的示例，但是正如我所说的，我使用的示例很长。

最终编辑

这里是完整的代码作为一个例子。它生成3个工作表，第二个页面运行3次查询来填充页面。每个工作表都有相同的标题和格式。

Answer 1

SpreadSheetSetColumnWidth只对活动工作表进行操作。因此，一次调用它，在最后，是行不通的。对于您希望修改的每个列，必须在每个工作表上调用该函数。

正如您注意到的，只有在向该列添加一些数据之后，才能更改列的宽度。这样做的原因是，在应用值或公式之前，不实际创建列(或单元格)。因此，如果您尝试修改它们的属性，甚至在它们存在之前，什么都不会发生。同样的规则也适用于“格式”：单元格必须存在，然后才能应用格式。

Optimizations:

以下几个技巧将大大简化原始代码并提高可读性：

1. 由于报表将在每个工作表上使用相同的查询列，因此对于UDF来说，这是一个完美的工作。与其对每个工作表复制相同的代码，只需创建一个函数，使用所提供的查询填充任意工作表名。 函数populateSummarySheet(任意工作簿、字符串sheetName、日期reportDate、查询qryData、布尔createNewSheet ){ 然后根据需要多次调用该函数： 工作簿= SpreadsheetNew("FirstSheet")；populateSummarySheet(工作簿，"FirstSheet"，reportDate，query1，false)；populateSummarySheet(工作簿，"SecondSheet"，reportDate，query2，true)；populateSummarySheet(工作簿，"ThirdSheet"，reportDate，query3，true)；// . 如果您不熟悉CF中的函数，请务必阅读如何正确调整函数局部变量的范围。一个常见的问题是忘记了对所有函数局部变量的范围，这些变量通常会造成奇怪的、很难重现的问题。
2. CF9+支持结构和数组创建的快捷方式。即 headerFormat ={ bold="true"，fontsize=18，font=“杯状”}；
3. 如果需要格式化特定行或列中的所有单元格，则更有效的方法是格式化行或列，而不是每个单元格。参见关于：SpreadSheetFormatRow、SpreadSheetFormatColumn SpreadSheetFormatColumns的文档 此外，Excel还限制了可以应用多少个样式。格式化单个单元格会消耗更多的样式，增加超过限制的可能性：SpreadsheetFormatRow突然停止工作更新的.xlsx格式的限制高于.xls格式的限制。因此，在可能的情况下，最好使用.xlsx工作簿，而不是.xls。

我有CF 2016，prod服务器是CF 9。

正如我在另一个线程中提到的，在Dev和Prod中使用不同的版本是一个非常糟糕的主意。因为这样就不可能测试您的代码。如果进行搜索，仍然可以找到旧版本的下载。例如：ColdFusion 9安装程序的直接下载链接(64位Windows)