使用CFspreadsheet编写电子表格以避免错误，该文件可能已损坏 [英] Writing a spreadsheet using CFspreadsheet to avoid error this file might be corrupted
问题描述
我试图弄清楚如何将我的html表添加到CFspreadsheet中以在excel中显示。我在网上发现的所有示例都没有我的疯狂（只是一个简单的基本查询）。任何帮助，将不胜感激。到目前为止，这是我能够弄清的我的电子表格的地方：
I am trying to figure out how to add my html table to a CFspreadsheet to show in excel. All the examples online that I have found are not as crazy as mine (only a simple one basic query). Any help with this would be greatly appreciated. This is what I have been able to figure out so far for my spreadsheet:
<cfset objSpreadsheet = SpreadsheetNew()>
<cfset filename = expandPath("./myexcel.xls")>
<!--- Create and format the header row. --->
<cfset SpreadsheetAddRow( objSpreadsheet, "Associate Name,Location,Checklists Generated by Associate,Checklists Generated by Selected Location(s),Associate Percentage of Location Total" )>
<cfset SpreadsheetFormatRow( objSpreadsheet, {bold=true, alignment="center"}, 1 )>
<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfcontent type="application/vnd.ms-excel" variable="#SpreadsheetReadBinary( objSpreadsheet )#">
我的表格正在尝试转换：
<table class="table table-hover">
<thead>
<th><strong>Associate Name</strong></th>
<th><strong>Location</strong></th>
<th><strong>Checklists Generated by Associate</strong></th>
<th><strong>Checklists Generated by Selected Location(s)</strong></th>
<th><strong>Associate Percentage of Location Total</strong></th>
</thead>
<tbody>
<cfoutput query="GetEmployeeInfo">
<tr>
<td><cfif rnA EQ 1><strong>#assoc_name#</strong></cfif></td>
<td><cfif rnL EQ 1>#trans_location#</cfif></td>
<td>#checklistsByAssocLoc#</td>
<td>#assocChecklistsByLoc#</td>
<td>#DecimalFormat(totalChecklistsByAssocLocPct)# %</td>
<!---<td> rnA: #rnA# | rnL: #rnL# | rnTotAssoc: #rnTotAssoc# </td> --->
</tr>
<cfif rnTotAssoc EQ 1>
<tr>
<td>Associate Total</td>
<td></td>
<td>#totalChecklistsByAssoc#</td>
<td>#totalAssocChecklistsByAllFilteredLoc#</td>
<td>#DecimalFormat(totalChecklistsByLocPct)# %</td>
</tr>
</cfif>
</cfoutput>
</tbody>
</table>
我疯狂的查询！：
<cfquery datasource="#application.dsn#" name="GetEmployeeInfo">
SELECT s4.associate /* Associate's ID */
, s4.assoc_name /* Associate's Name */
, s4.trans_location /* Associate's Location */
, s4.checklistsByAssocLoc /* Gives you a count of Checklists by Associate for a specific Location. */
, s4.assocChecklistsByLoc /* Gives you a count of Total Checklists by All Associates in a Location. */
, s4.totalChecklistsByAssoc /** Gives you a count of Total Checklists by Specific Associate in All Locations. */
, s4.totalAssocChecklistsByAllFilteredLoc /* Gives you a count of Total Checklists by Specific Associates in All Locations. */
, CASE WHEN ( coalesce(s4.assocChecklistsByLoc,0) > 0 ) THEN (CAST(s4.checklistsByAssocLoc AS decimal(8,2))/s4.assocChecklistsByLoc) * 100 ELSE 0 END AS totalChecklistsByAssocLocPct /* This gives you a percent of associate location checklists over count of checklists by Associate in a Location. */
, CASE WHEN ( coalesce(s4.totalAssocChecklistsByAllFilteredLoc,0) > 0 ) THEN (CAST(s4.totalChecklistsByAssoc AS decimal(8,2))/s4.totalAssocChecklistsByAllFilteredLoc) * 100 ELSE 0 END AS totalChecklistsByLocPct /* This gives you a percent of Total Associate Checklists in All Locations over count of Checklists by All Associate in All Locations. */
, s4.rnA /* Placeholder for a record to display the Associate Name. */
, s4.rnL /* Placeholder for a record to display the Location. */
, s4.rnTotAssoc /* Placeholder for the last Associate Location row. The next row should be an Associate Total. */
FROM (
SELECT s3.*
, SUM(s3.assocChecklistsByLoc) OVER (PARTITION BY s3.associate) AS totalAssocChecklistsByAllFilteredLoc /* Gives you a count of Total Checklists by Specific Associates in All Locations. */
FROM (
SELECT s2.*
FROM (
SELECT a.assoc_name
, s1.associate
, s1.trans_location
, s1.checklistsByAssocLoc
, s1.assocChecklistsByLoc
, s1.totalChecklistsByAssoc
, ROW_NUMBER() OVER (PARTITION BY s1.associate ORDER BY s1.associate, s1.trans_location) AS rnA /* Placeholder for a record to display the Associate Name */
, ROW_NUMBER() OVER (PARTITION BY s1.associate, s1.trans_location ORDER BY s1.associate, s1.trans_location) AS rnL /* Placeholder for a record to display the Location */
, ROW_NUMBER() OVER (PARTITION BY s1.associate ORDER BY s1.trans_location DESC) AS rnTotAssoc /* Placeholder for the last Associate Location row. The next row should be an Associate Total. */
FROM (
SELECT c.associate
, c.trans_location
, COUNT(*) OVER (PARTITION BY c.associate, c.trans_location) AS checklistsByAssocLoc /* Gives you a count of Checklists by Associate for a specific Location. */
, COUNT(*) OVER (PARTITION BY c.associate) AS totalChecklistsByAssoc /* Gives you a count of Total Checklists by Associate in All Locations. */
, COUNT(*) OVER (PARTITION BY c.trans_location) AS assocChecklistsByLoc /* Gives you a count of Total Checklists by All Associates in a Location. */
FROM cl_checklists c
LEFT OUTER JOIN tco_associates a ON c.associate = a.assoc_id
AND a.assoc_id IN ( <cfqueryparam value="#FORM.EmployeeName#" cfsqltype="cf_sql_varchar" list="true" /> ) /* SELECTED ASSOCIATE IDs */
WHERE c.[DATE] >= <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" /> /* SELECTED DATES */
AND c.[DATE] <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
AND c.trans_location IN ( <cfqueryparam value="#locList#" cfsqltype="cf_sql_varchar" list="true" /> ) /* SELECTED LOCATIONS */
) s1
INNER JOIN tco_associates a ON s1.associate = a.assoc_id
AND a.assoc_id IN ( <cfqueryparam value="#FORM.EmployeeName#" cfsqltype="cf_sql_varchar" list="true" /> ) /* SELECTED ASSOCIATE IDs */
) s2
WHERE s2.rnA = 1 OR s2.rnL = 1 /* There will be a final Location (rnL=1 and rnTotAssoc=1). This is the final row. */
) s3
) s4
ORDER BY s4.assoc_name, s4.trans_location
</cfquery>
这是我在想的路径，但我真的不理解如何称呼行和列。
This is the path I was thinking but I truly dont understand calling the rows and columns. Do I even have the right idea or am I way off?
<cfoutput query="GetEmployeeInfo">
<cfif rnA EQ 1><cfset SpreadsheetSetCellValue( objSpreadsheet, #assoc_name#, 2, 1) ></cfif>
<cfif rnL EQ 1><cfset SpreadsheetSetCellValue( objSpreadsheet, #trans_location#, 2, 1) ></cfif>
<cfset SpreadsheetSetCellValue( objSpreadsheet, #checklistsByAssocLoc#, 2, 1) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, #assocChecklistsByLoc#, 2, 1) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, #DecimalFormat(totalChecklistsByAssocLocPct)# %, 2, 1) >
<cfif rnTotAssoc EQ 1>
<cfset SpreadsheetSetCellValue( objSpreadsheet, 'Associate Total', 2, 1) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, '', 2, 1) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, #totalChecklistsByAssoc#, 2, 1) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, #totalAssocChecklistsByAllFilteredLoc#, 2, 1) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, #DecimalFormat(totalChecklistsByLocPct)# %, 2, 1) >
</cfif>
</cfoutput>
也尝试过：
<cfoutput query="GetEmployeeInfo">
<cfset SpreadsheetAddRow( objSpreadsheet, "<cfif rnA EQ 1>#assoc_name#</cfif>,<cfif rnL EQ 1>#trans_location#</cfif>,#checklistsByAssocLoc#,#assocChecklistsByLoc#,#DecimalFormat(totalChecklistsByAssocLocPct)# %" )>
<cfif rnTotAssoc EQ 1>
<cfset SpreadsheetAddRow( objSpreadsheet, "Associate Total,'',#totalChecklistsByAssoc#,#totalAssocChecklistsByAllFilteredLoc#,#DecimalFormat(totalChecklistsByLocPct)# %" )>
</cfif>
</cfoutput>
推荐答案
对于您的最终摘要，ColdFusion标签不会在字符串文字。对于if else语句，可以使用三元运算符来切换某些输出。另外，如果您的任何数据包含逗号，都会在单元格之间分割数据。为了解决这个问题，请尝试将每个单元格值都用引号引起来。这可能会将您的文本保留在一个单元格中。 其他人在此修复程序上仍然有问题。 / p>
For your final snippet ColdFusion tags will not be evaluated in a string literal. For if else statements, you could use the ternary operator to toggle some of the output. Also, if any of your data contains comma/s it'll split the data between cells. To combat this, try wrapping each cell value in quotes. This may keep your text in one cell. Others still have had issues with this fix.
<cfset rowNumber = 0 />
<cfoutput query="GetEmployeeInfo">
<cfset rowNumber++ />
<cfset rowList = "'#(rnA eq 1)?assoc_name:''#', '#(rnl eq 1)?trans_location:''#', '#checklistsByAssocLoc#,#assocChecklistsByLoc#', '#DecimalFormat(totalChecklistsByAssocLocPct)# %'">
<cfset SpreadsheetAddRow( objSpreadsheet, rowList)>
<cfset spreadsheetFormatCell( objSpreadsheet, {bold: true}, rowNumber, 1 )>
<cfif rnTotAssoc EQ 1>
<cfset rowNumber++ />
<cfset rowList = "'Associate Total','','#totalChecklistsByAssoc#','#totalAssocChecklistsByAllFilteredLoc#','#DecimalFormat(totalChecklistsByLocPct)# %'" >
<cfset SpreadsheetAddRow( objSpreadsheet, rowList )>
</cfif>
</cfoutput>
个人而言，由于使用许多（几百种以上） spreadsheetAddRow的渲染时间较慢来创建电子表格，并且使用字符串数据列表可能很麻烦，我几乎总是将电子表格数据放入查询对象中。数据进入查询对象后，只需调用一次 spreadsheetAddRows 即可将数据放入电子表格。
Personally, due to slow rendering time of using many (few hundred+) spreadsheetAddRows to create a spread sheet, and working with lists of string data can be a pain, I almost always place my spreadsheet data into a query object. Once the data is in a query object, it takes one call to spreadsheetAddRows to get the data into the spreadsheet.
qReportData = queryNew("Name, Age",
"varchar, integer",
[{name: "Tom", age: 25},{name: "Dick", age: 40},{name: "Harry", age: 55}]
);
sheet = SpreadsheetNew(false);
//Add and format headers
bold = {bold: true};
spreadsheetAddRow(sheet, "Name, Age");
spreadsheetFormatRow(sheet, bold, 1);
spreadsheetAddRows(sheet, qReportData);
鉴于某些报告数据可以位于多行中，在特定情况下，您不会能够仅导出您的报告查询，因此我们必须使用代码构建一个新查询。我们将遍历该报告并在电子表格查询中生成行。在我的示例中，只要该人超过40岁，我都会添加一行。
Given that some of your report data can be on multiple rows, under specific situations, you won’t be able to just export your report query, so we'll have to build a new one with code. We'll iterate over the report and generate rows in our spreadsheet query. In my example, I'll add an extra row any time the person is over the age of 40.
qSheetOutput = queryNew("Name, Age");
for(row in qReportData){
queryAddRow(qSheetOutput, {
name: row.name,
age: row.age
});
if(row.age > 40){
queryAddRow(qSheetOutput, {
name: row.name & " is over 40"
});
}
}
// now writing the generated query to the spreadsheet
spreadsheetAddRows(sheet, qSheetOutput);
最后一步是迭代和格式化电子表格的单元格。当我遍历输出时，我必须通过工作表中的标题数来偏移正在处理的行，本示例中的witch为1。同样，在此示例中，对于40岁以上的人，额外的行将
The last step will be to iterate and format the cells of the spreadsheet. As I iterate over the output I have to offset the row I'm working with by the count of headers in the sheet, witch in this example is 1. Also for this example, the extra row for a person over the age of 40 will not be bolded, and will span 2 cells.
for(row in qSheetOutput){
if(!len(row.age)){
spreadsheetFormatCell(sheet, {dataformat="@", alignment="center"}, qSheetOutput.currentRow + 1, 1);
spreadsheetFormatCell(sheet, qSheetOutput.currentRow + 1, qSheetOutput.currentRow + 1, 1, 2);
}
else{
spreadsheetFormatCell( sheet, bold, qSheetOutput.currentRow + 1, 1 );
}
}
如果看输出结果太难确定正确的行格式，您可以遍历需要特定格式的行号数组。再次注意，我再次使用标头计数作为偏移量。
If looking at the output is too difficult to determine the correct format/s needed for the row, you could iterate over an array/s of row numbers that require a specific format/s. Once again notice I'm using the header count as an offset again.
dataRows = [];
messageRows = [];
for(row in qReportData){
queryAddRow(qSheetOutput, {
name: row.name,
age: row.age
});
arrayAppend(dataRows, qSheetOutput.recordCount + 1);
if(row.age > 40){
queryAddRow(qSheetOutput, {
name: row.name & " is over 40"
});
arrayAppend(messageRows, qSheetOutput.recordCount + 1);
}
}
...
for(rowNumber in dataRows){
spreadsheetFormatCell( sheet, bold, rowNumber, 1 );
}
for(rowNumber in messageRows){
spreadsheetFormatCell(sheet, {dataformat="@", alignment="center"}, rowNumber, 1);
spreadsheetFormatCell(sheet, rowNumber, rowNumber, 1, 2);
}
这是 TryCF.com
这篇关于使用CFspreadsheet编写电子表格以避免错误，该文件可能已损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
