excel - 不带引号显示前导零

Question

我想删除单个，但是当我删除它时，不显示前导零。

我希望显示前导零但不带引号。

我的列数据类型是 varchar :

<cfquery name="getStudent" datasource="#Application.dsn#">
        SELECT CONCAT('\'',U.user_gov_id) as User_gov_id  , 
        U.user_first_name , U.user_last_name , CONCAT('\'', U.user_mobile ) as Mobile_Number  
        , U.user_address, U.user_street_number, U.user_city , U.user_post_code , 
        DATE_FORMAT(U.delivery_date, '%Y-%M-%d %h:%i:%s')  
        FROM tblUSER U INNER JOIN tblUSER_PAYMENT P ON U.user_id = P.user_id 
        WHERE P.other_amount = '250' AND U.isdelivered = 0 AND U.matrix_node_number > 155
    </cfquery>

用于 Excel 生成

<cfspreadsheet action="write" filename="#fileName1#.xlsx" query="getStudent" overwrite="true" >
  <cfheader   name="Content-Disposition" value="inline; filename=#fileName1#.xlsx">
   <cfcontent  type="application/csv" file="#ExpandPath('#fileName1#.xlsx')#" deletefile="yes"> 

Answer 1

在写出电子表格之前将列格式化为文本。在以下示例中，第 2 列和第 3 列被格式化为文本。

<cfscript>
    format={};

    format.date={};
    format.date.dataformat="mm/dd/yyyy";

    format.text={};
    format.text.dataformat="@";

    var theSheet=SpreadSheetNew("OutputSheet");
    SpreadsheetAddRow(theSheet,ArrayToList(query.getColumnNames()));
    SpreadsheetAddRows(theSheet,query);
    SpreadsheetFormatColumn(theSheet,format.text,2);
    SpreadsheetFormatColumn(theSheet,format.text,3);
    SpreadsheetFormatColumn(theSheet,format.date,6);
</cfscript>


<cfspreadsheet  action="write"
                filename="#ExpandPath(filename)#"
                name="theSheet"
                sheet="1"
                sheetName="Sheet 1"
                overwrite="true">