SQL到Excel会丢失尾随零 [英] SQL to Excel loses trailing zeros

问题描述

我有一个包含价格列的SQL表。字段类型为 varchar ，因为某些价格字段必须具有市场价格作为数据值。我通过ColdFusion查询此数据，并使用cfspreadsheet创建导出：

I have a SQL table that has columns of prices. The field type is varchar because some of the price fields must have "Market Price" as the data value. I am querying this data via ColdFusion and using cfspreadsheet to create an export:

<cfspreadsheet action="write" filename="prices.xlsx" 
       query="qTierPrices" 
       sheetname="Food Pricing" 
       overwrite="yes">

查看电子表格时， 1.00 在电子表格单元格中变为 1 和 4.50 变为 4.5 。有没有办法修复这个程序，以便电子表格准确地反映出来的表？提前感谢！

When looking at the spreadsheet, prices like 1.00 become 1 and 4.50 becomes 4.5 in the spreadsheet cells. Is there a way to fix this programmatically so the spreadsheet reflects exactly what's coming out of the table? Thanks in advance!

推荐答案

用于格式化电子表格中的整个列。

Like Dan said, you can use spreadsheetFormatColumn for format an entire column in a spreadsheet.

下面的示例使用虚拟数据创建电子表格，并将价格格式设置为您的规范。

The example below creates a spreadsheet using dummy data, and formats the price column to your specification.

<!--- Generating some dummy data --->
<cfset qData = queryNew('foo,bar,price','varchar,date,varchar')>

<cfset queryAddRow(qData)>
<cfset querySetCell(qData, 'foo', createUUID())>
<cfset querySetCell(qData, 'bar', now())>
<cfset querySetCell(qData, 'price', 'Market Price')>

<cfset queryAddRow(qData)>
<cfset querySetCell(qData, 'foo', createUUID())>
<cfset querySetCell(qData, 'bar', now())>
<cfset querySetCell(qData, 'price', '4.55')>

<cfset queryAddRow(qData)>
<cfset querySetCell(qData, 'foo', createUUID())>
<cfset querySetCell(qData, 'bar', now())>
<cfset querySetCell(qData, 'price', '1.5')>

<cfset queryAddRow(qData)>
<cfset querySetCell(qData, 'foo', createUUID())>
<cfset querySetCell(qData, 'bar', now())>
<cfset querySetCell(qData, 'price', '1')>

<cfset queryAddRow(qData)>
<cfset querySetCell(qData, 'foo', createUUID())>
<cfset querySetCell(qData, 'bar', now())>
<cfset querySetCell(qData, 'price', '7.6')>

<!--- create the spreadsheet and add a header row --->
<cfset theSheet = spreadsheetNew("Food Pricing", true)>
<cfset spreadsheetAddRow( theSheet, 'foo,bar,price' )>

<cfset spreadsheetAddRows( theSheet, qData )>

<!--- Format the third column (price) to a number with two decimal places --->
<cfset spreadSheetFormatColumn( theSheet, {dataformat='0.00'}, 3)>

<!--- Output the spreadsheet to a file --->
<cfspreadsheet action="write" filename="prices.xlsx" name="theSheet" sheetname="Food Pricing" overwrite="yes" >

这篇关于SQL到Excel会丢失尾随零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！