如何使用ColdFusion格式化电子表格列？ [英] How to format spreadsheet columns using ColdFusion?

问题描述

我使用SpreadsheetFormatColumns（）格式化电子表格中的列为文本，但我不知道如何做，livedocs中的所有格式是数字，货币或日期...是有类似

SpreadsheetFormatColumns（mySpreadsheet，{dataFormat =text}，1-15）

在那里？

解决方案

ColdFusion 9.0.1（即updater 1），如果你使用SpreadsheetSetCellValue（）它会尊重你先前设置的格式。因此，要在填充表格时强制使用文本列，您可以使用3步过程：

1. 填充电子表格，忽略不正确的


2. 将列的每一行中的不正确值替换为正确的值，




这里有一个例子，您可以将其复制到.cfm中，并按原样运行（需要CF9）。 0.1）

 < cfscript> 
 //创建2列，2行查询。第一列包含我们想要在电子表格中格式化为文本的数字或可能的数字
 q = QueryNew（）; 
 QueryAddColumn（q，NumbersAsText，VarChar，[01050094071094340000，743059E6]）; 
 QueryAddColumn（q，Text，VarChar，[abc，def]）; 
 //获取列名称作为数组，以便我们以后可以更容易地得到它们
 columns = q.getMetaData（）。getColumnLabels（）; 
 //创建一个新的电子表格对象
 sheet = SpreadSheetNew（test）; 
 //指定我们想要的格式为文本的列
 forceTextColumnNumber = 1; 
 //在我们的工作表中使用查询列名作为列标题
 SpreadSheetAddRow（sheet，q.columnList）; 
 //添加数据：现在数字将被插入数字
 SpreadSheetAddRows（sheet，q）; 
 //现在我们将列格式化为文本
 SpreadSheetFormatColumn（sheet，{dataformat =text}，forceTextColumnNumber）; 
 //格式化列，再次从查询中添加列，以便值正确
 while（q.next（））
 {
 //跳过标题行添加一个
 rownumber =（q.currentrow + 1）; 
 //获取循环中当前行的列值
 value = q [columns [forceTextColumnNumber]] [q.currentrow]; 
 //替换以前添加的数字值，现在将被视为文本
 SpreadsheetSetCellValue（sheet，value，rownumber，forceTextColumnNumber）; 
} 
 //将对象下载为文件
 sheetAsBinary = SpreadSheetReadBinary（sheet）; 
 filename =test.xls; 
< / cfscript> 
< cfheader name =Content-Dispositionvalue =attachment; filename =＃Chr（34）## filename ## Chr（34）＃ 
< cfcontent type =application / msexcelvariable =＃sheetAsBinary＃reset =true> 
  

默认情况下，我的查询第一列中的值都将被视为数字第二个为HEX）。使用此方法都会将其原始值保存为文本。




I am using SpreadsheetFormatColumns() to format the columns in a spreadsheet to "text", but I don't know how to do this, all the formats in the livedocs are for numbers, currency or dates... is there something like

SpreadsheetFormatColumns(mySpreadsheet, {dataFormat="text"}, "1-15")

out there? this is really bugging me...

Thanks

解决方案

In ColdFusion 9.0.1 (i.e. updater 1), if you use SpreadsheetSetCellValue() it will respect the format you have previously set. So to force a column to be text when populating a sheet you can use a 3-step process:

1. Populate the spreadsheet, ignoring the incorrectly interpreted number values.
2. Format the column you want as text.
3. Replace the incorrect value in each row of the column with the correct value, which will now be treated as text.

Here's an example which you can copy into a .cfm and run as-is (requires CF9.0.1)

<cfscript>
    // Create a 2 column, 2 row query. The first column contains numbers or possible numbers we want formatted as text in our spreadsheet
    q   =   QueryNew( "" );
    QueryAddColumn( q,"NumbersAsText","VarChar",[ 01050094071094340000,"743059E6" ] );
    QueryAddColumn( q,"Text","VarChar",[ "abc","def" ] );
    // Get the column names as an array so we can get at them more easily later
    columns =   q.getMetaData().getColumnLabels();
    // Create a new spreadsheet object
    sheet   =   SpreadSheetNew( "test" );
    // specify the column we want formatted as text
    forceTextColumnNumber   =   1;
    // Use the query column names as column headers in our sheet
    SpreadSheetAddRow( sheet,q.columnList );
    // Add the data: the numbers will be inserted as numeric for now
    SpreadSheetAddRows( sheet,q );
    // Now we format the column as text
    SpreadSheetFormatColumn( sheet,{ dataformat="text" },forceTextColumnNumber );
    // Having formatted the column, add the column from our query again so the values correct
    while( q.next() )
    {
        // Skip the header row by adding one
        rownumber   =   ( q.currentrow + 1 );
        // Get the value of column at the current row in the loop
        value   =   q[ columns[ forceTextColumnNumber ] ][ q.currentrow ];
        // replace the previously added numeric value which will now be treated as text
        SpreadsheetSetCellValue( sheet,value,rownumber,forceTextColumnNumber );
    }
    // Download the object as a file
    sheetAsBinary   =   SpreadSheetReadBinary( sheet );
    filename    =   "test.xls";
</cfscript>
<cfheader name="Content-Disposition" value="attachment; filename=#Chr(34)##filename##Chr(34)#">
<cfcontent type="application/msexcel" variable="#sheetAsBinary#" reset="true">

By default, both of the values in the first column of my query would be treated as numbers (the second as a HEX). Using this method both preserve their original value as text.

这篇关于如何使用ColdFusion格式化电子表格列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！