如何使用 ColdFusion 格式化电子表格列? [英] How to format spreadsheet columns using ColdFusion?
问题描述
我正在使用 SpreadsheetFormatColumns() 将电子表格中的列格式化为文本",但我不知道该怎么做,livedocs 中的所有格式都是数字、货币或日期...像
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")
SpreadsheetFormatColumns(mySpreadsheet, {dataFormat="text"}, "1-15")
在外面吗?这真是烦死我了……
out there? this is really bugging me...
谢谢
推荐答案
在 ColdFusion 9.0.1(即更新程序 1)中,如果您使用 SpreadsheetSetCellValue() 它将尊重您之前设置的格式.因此,要在填充工作表时强制将列作为文本,您可以使用 3 步过程:
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:
- 填充电子表格,忽略错误解释的数字值.
- 将您想要的列格式化为文本.
- 用正确的值替换列中每一行中的错误值,该值现在将被视为文本.
这是一个示例,您可以将其复制到 .cfm 中并按原样运行(需要 CF9.0.1)
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屋!