电子表格单元格格式 [英] Spreadsheet Cell Formatting
问题描述
最后编辑
任何人都看到我做错了吗？这是我的期望和观察：
我期望一个名为搜索参数的工作表具有粗体红色字体的单元格a1。我得到了我的期望。
我想要一个名为4D，CCCU，SDAU的工作表，其数据从第3行开始。 b
$ b
我希望第3行有文本bold font 3 green true，并以粗体绿色字体。我得到文本，但我得到大胆的红色字体，这匹配单元格a1在其他工作表。事实上，此单元格中的格式将始终匹配另一个表格中的单元格a1。
我希望剩余的单元格具有像normal font 4 blue true并以蓝色粗体字体。我得到的值，但没有格式化。
我的writedumps总是显示我期望的值。
代码如下。 AddNewRow是一个udf。
要重申，问题是，为什么单元格没有按我期望的方式格式化？
< cfscript>
FileName =d：\dw\dwweb\work\Antibiotics.xls;
SearchParameters = QueryNew（Item，Value，varchar，varchar）;
AddNewRow（SearchParameters，Item，Value，Date Range，＃DateRange＃）;
SearchParametersSheet = Spreadsheetnew（SearchParameters）;
SpreadSheetAddRows（SearchParametersSheet，SearchParameters）;
SheetNumber = 1;
DrugsByCategory = QueryNew（Item，font，varchar，varchar）;
format1 = StructNew（）;
format1.bold = true;
format1.color =red;
SpreadsheetFormatCell（SearchParametersSheet，format1，1，1）;
< / cfscript>
< cfspreadsheet action =writefilename =＃FileName＃
name =SearchParametersSheet
sheet = 1 sheetname =Search Parametersoverwrite = true>
< cfoutput query =AllDrugsgroup =CategoryName>
< cfset AddNewRow（DrugsByCategory，Item＃Chr（30）#font
，＃CategoryName ## Chr（30）#bold，Chr（30））&
< cfoutput>
< cfset AddNewRow（DrugsByCategory，Item＃Chr（30）#font
，＃StandardSpelling ## Chr（30）#normal，Chr（30））>
< / cfoutput>
< / cfoutput>
< cfquery name =unitsdbtype =query>
select distinct unit
from initialresults
< / cfquery>
< cfloop query =units>
< cfscript>
SheetNumber ++;
ThisSpreadSheet = SpreadSheetNew（unit）;
RowNumber = 3;
for（i = 1; i <= DrugsByCategory.recordcount; i ++）{
//注意数据可能包含逗号，因此我们必须使用两个命令
SpreadsheetAddRow（ThisSpreadSheet，，RowNumber，1）;
SpreadSheetSetCellValue（ThisSpreadSheet，DrugsByCategory.Item [i]，RowNumber，1）;
if（DrugsByCategory.font [i] ==bold）{
format1.bold = true;
format1.color =green;
writedump（var =＃format1＃label =＃RowNumber＃）;
SpreadSheetSetCellValue（ThisSpreadSheet
，bold font＃Rownumber＃＃format1.color＃＃format1.bold＃
，RowNumber，1）;
SpreadsheetFormatCell（ThisSpreadSheet，format1，RowNumber，1）;
}
else {
format1.color =blue;
format1.bold = true;
writedump（var =＃format1＃label =＃RowNumber＃）;
SpreadSheetSetCellValue
（ThisSpreadSheet，normal font
＃Rownumber＃＃format1.color＃＃format1.bold＃
，RowNumber，1）;
SpreadsheetFormatCell（ThisSpreadSheet，format1，RowNumber，1）;
}
RowNumber ++;
}
< / cfscript>
< cfspreadsheet action =updatefilename =＃FileName＃name =ThisSpreadSheet
sheet =＃SheetNumber＃sheetname =＃unit＃>
< / cfloop>
编辑此处开始
这是Leigh建议的自包含代码。 Travis的建议wrt格式方法被注释掉，但是当我使用它们时，结果没有改变。
< cfscript&
Sheet1 = Spreadsheetnew（Sheet1）;
SpreadSheetAddRow（Sheet1，fred）;
SheetNumber = 1;
Format = {};
format.bold = true;
format.color =blue;
MYfile =d：\dw\dwtest\dan\abc.xls;
writedump（format）;
SpreadsheetFormatCell（Sheet1，Format，1，1）;
Values =a，b，a，b;
< / cfscript>
< cfspreadsheet action =writefilename =＃MYFile＃name =Sheet1
sheet = 1 sheetname =fredoverwrite = true>
< cfloop list =aindex =letter>
< cfscript>
RowNumber = 1;
SheetNumber ++;
ThisSheet = SpreadSheetNew（letter）;
for（i = 1; i <= 4; i ++）{
SpreadsheetAddRow（ThisSheet，ListGetAt（Values，i））;
if（ListGetAt（Values，i）==a）{
format.color =green;
SpreadsheetFormatCell（ThisSheet，Format，RowNumber，1）;
// SpreadsheetFormatCell（ThisSheet，{bold =true，color =green}，RowNumber，1）;
}
else {
format.color =red;
SpreadsheetFormatCell（ThisSheet，Format，RowNumber，1）;
// SpreadsheetFormatCell（ThisSheet，{bold =true，color =green}，RowNumber，1）;
}
RowNumber ++;
}
< / cfscript>
< cfspreadsheet action =updatefilename =＃MYFile＃name =ThisSheet
sheet =＃sheetNumber＃sheetname =＃letter＃>
< / cfloop>
结果是：
表格fred是预期的，单元格a1中的粗体蓝色字体。
在表单a中，单元格a1和a3的字母a为粗体蓝色字体。我预计大胆的绿色。单元格a2和a4有字母b，未格式化。我期望大胆和红色。
我做一些愚蠢的东西，或者是错误的。我使用ColdFusion 9.01和Excel 2010.
我怀疑你可能遇到一个错误 < cfspreadsheet action =update..> 。
当CF执行更新时，它尝试从工作表a公式等）插入到您保存在磁盘上的工作簿中的新工作表中。显然CF不是复制一切。因此缺少格式。您可以通过将工作表a（仅 ）保存到单独的文件来证明这一点。注意，当您使用 action =write时，单元格格式正确（出现红色和绿色）？
...
<！---保存ThisSheet的单独副本--->
< cfspreadsheet action =writefilename =c：/thisSheetOnly.xlsname =ThisSheet....>
<！---试图合并表单 - >
< cfspreadsheet action =updatefilename =＃MyFile＃name =ThisSheet...>
老实说，创建整个工作表的完整副本是棘手的。有一个很多的移动件，它很容易错过的东西，这是在这里似乎发生了。就个人而言，如果可能的话，我会避免使用 action =update。太多的东西可能会出错。此外，在大多数情况下，你不需要它。您可以轻松创建工作簿，然后添加和填充多个工作表。这是一个简单的示例，创建和保存两个工作表，然后将它们保存到磁盘。 （用CF10测试）
< cfscript>
//创建新工作表并添加一行
Workbook = Spreadsheetnew（Sheet1）;
SpreadSheetAddRow（Workbook，fred）;
//将格式应用于新单元格
format = {bold = true，color =blue};
SpreadsheetFormatCell（Workbook，Format，1，1）;
WriteDump（format）;
//添加另一个工作表并使其活动
letter =a;
SpreadSheetCreateSheet（Workbook，letter）;
SpreadSheetSetActiveSheet（Workbook，letter）;
//向活动工作表添加行
RowNumber = 1;
Values =a，b，a，b;
for（i = 1; i <= 4; i ++）{
SpreadsheetAddRow（Workbook，ListGetAt（Values，i），RowNumber，1）;
if（ListGetAt（Values，i）==a）{
Format = {bold = true，color =green};
SpreadsheetFormatCell（Workbook，Format，RowNumber，1）;
WriteDump（var = format，label =RowNumber =& RowNumber）;
}
else {
Format = {bold = true，color =red};
SpreadsheetFormatCell（Workbook，Format，RowNumber，1）;
WriteDump（var = format，label =RowNumber =& RowNumber）;
}
RowNumber ++;
}
//将活动工作表设置为原始工作表。如果你不
//最后一个工作表名称将是电子表格的名称
//对象，在这种情况下，工作簿。
SpreadSheetSetActiveSheetNumber（Workbook，1）;
//最后，保存到磁盘
SpreadSheetWrite（Workbook，c：/path/to/yourFile.xls，true）;
< / cfscript>
Edit at the end
Can anyone see what I am doing wrong? These are my expectations and observations:
I expect a worksheet named Search Parameters to have cell a1 in bold red font. I get what I expect.
I expect a worksheet named "4D,CCCU,SDAU" which has data starting in row 3. I get this.
I expect row 3 to have the text "bold font 3 green true", and to be in bold green font. I get the text, but I get bold red font, which matches cell a1 in the other sheet. In fact, the formatting in this cell will always match cell a1 from the other sheet.
I expect the remaining cells to have a value like "normal font 4 blue true" and be in blue bold font. I get the values, but no formatting at all.
My writedumps always show me the values I expect.
The code is below. AddNewRow is a udf.
To reiterate, the question is, why are the cells not formatting the way I expect them to?
<cfscript>
FileName = "d:\dw\dwweb\work\Antibiotics.xls";
SearchParameters = QueryNew("Item,Value","varchar,varchar");
AddNewRow(SearchParameters, "Item,Value","Date Range,#DateRange#");
SearchParametersSheet = Spreadsheetnew("SearchParameters");
SpreadSheetAddRows(SearchParametersSheet, SearchParameters);
SheetNumber = 1;
DrugsByCategory = QueryNew("Item,font","varchar,varchar");
format1 = StructNew();
format1.bold = true;
format1.color = "red";
SpreadsheetFormatCell(SearchParametersSheet, format1, 1, 1);
</cfscript>
<cfspreadsheet action="write" filename="#FileName#"
name="SearchParametersSheet"
sheet=1 sheetname="Search Parameters" overwrite=true>
<cfoutput query="AllDrugs" group="CategoryName">
<cfset AddNewRow(DrugsByCategory,"Item#Chr(30)#font"
,"#CategoryName##Chr(30)#bold",Chr(30))>
<cfoutput>
<cfset AddNewRow(DrugsByCategory,"Item#Chr(30)#font"
,"#StandardSpelling##Chr(30)#normal",Chr(30))>
</cfoutput>
</cfoutput>
<cfquery name="units" dbtype="query">
select distinct unit
from initialresults
</cfquery>
<cfloop query="units">
<cfscript>
SheetNumber ++;
ThisSpreadSheet = SpreadSheetNew(unit);
RowNumber = 3;
for (i = 1; i <= DrugsByCategory.recordcount; i ++) {
// note that the data might contain commas, so we have to use two commands
SpreadsheetAddRow(ThisSpreadSheet, "", RowNumber, 1);
SpreadSheetSetCellValue(ThisSpreadSheet, DrugsByCategory.Item[i], RowNumber, 1);
if (DrugsByCategory.font[i] == "bold"){
format1.bold = true;
format1.color = "green";
writedump(var="#format1#" label="#RowNumber#");
SpreadSheetSetCellValue(ThisSpreadSheet
, "bold font #Rownumber# #format1.color# #format1.bold#"
, RowNumber, 1);
SpreadsheetFormatCell(ThisSpreadSheet, format1, RowNumber, 1);
}
else {
format1.color = "blue";
format1.bold = true;
writedump(var="#format1#" label="#RowNumber#");
SpreadSheetSetCellValue
(ThisSpreadSheet, "normal font
#Rownumber# #format1.color# #format1.bold#"
, RowNumber, 1);
SpreadsheetFormatCell(ThisSpreadSheet, format1, RowNumber, 1);
}
RowNumber ++;
}
</cfscript>
<cfspreadsheet action="update" filename="#FileName#" name="ThisSpreadSheet"
sheet=#SheetNumber# sheetname="#unit#" >
</cfloop>
Edit Starts Here
This is the self contained code Leigh suggested. Travis's suggestion wrt format methods are commented out, but when I used them, the results did not change.
<cfscript>
Sheet1 = Spreadsheetnew("Sheet1");
SpreadSheetAddRow(Sheet1, "fred");
SheetNumber = 1;
Format = {};
format.bold = true;
format.color = "blue";
MYfile = "d:\dw\dwtest\dan\abc.xls";
writedump(format);
SpreadsheetFormatCell(Sheet1, Format, 1, 1);
Values = "a,b,a,b";
</cfscript>
<cfspreadsheet action="write" filename="#MYFile#" name="Sheet1"
sheet=1 sheetname="fred" overwrite=true>
<cfloop list="a" index="letter">
<cfscript>
RowNumber = 1;
SheetNumber ++;
ThisSheet = SpreadSheetNew(letter);
for (i = 1; i <= 4; i ++) {
SpreadsheetAddRow(ThisSheet, ListGetAt(Values, i));
if (ListGetAt(Values, i) == "a") {
format.color = "green";
SpreadsheetFormatCell(ThisSheet, Format, RowNumber, 1);
//SpreadsheetFormatCell(ThisSheet, {bold="true",color="green"}, RowNumber, 1);
}
else {
format.color = "red";
SpreadsheetFormatCell(ThisSheet, Format, RowNumber, 1);
//SpreadsheetFormatCell(ThisSheet, {bold="true",color="green"}, RowNumber, 1);
}
RowNumber ++;
}
</cfscript>
<cfspreadsheet action="update" filename="#MYFile#" name="ThisSheet"
sheet="#sheetNumber#" sheetname="#letter#" >
</cfloop>
The results are: Sheet fred is as expected, bold blue font in cell a1.
In sheet a, cells a1 and a3 have the letter a in bold blue font. I expected bold green. Cells a2 and a4 have the letter b, unformatted. I expected bold and red.
Am I doing something stupid, or is something wrong. I am using ColdFusion 9.01 and Excel 2010.
I suspect you may have run into a bug with <cfspreadsheet action="update" ..>.
When CF performs the "update" it attempts to copy everything from sheet "a" (values, formats, formulas, etcetera) into a new sheet within the workbook you saved on disk. Apparently CF is not copying everything. Hence the missing formats. You can prove this by saving sheet "a"(only) to a separate file. Notice when you use action="write" the cell formats are correct (red and green appear)?
...
<!--- save separate copy of "ThisSheet" only --->
<cfspreadsheet action="write" filename="c:/thisSheetOnly.xls" name="ThisSheet" ....>
<!--- attempt to combine the sheets --->
<cfspreadsheet action="update" filename="#MyFile#" name="ThisSheet" ... >
Honestly, creating a complete copy of an entire worksheet is tricky. There are a lot of moving pieces and it is easy to miss something, which is what seems to be happening here. Personally, I would avoid using action="update" if at all possible. Too many things can go wrong. Besides, in most cases you do not need it. You can easily create a workbook, then add and populate multiple sheets. Here is a simplified example that creates and saves two sheets, then saves them to disk. (Tested with CF10)
<cfscript>
// Create new sheet and add one row
Workbook = Spreadsheetnew("Sheet1");
SpreadSheetAddRow(Workbook, "fred");
// Apply formatting to new cell
format = {bold = true, color = "blue"};
SpreadsheetFormatCell(Workbook, Format, 1, 1);
WriteDump(format);
//Add another worksheet and make it active
letter = "a";
SpreadSheetCreateSheet(Workbook, letter);
SpreadSheetSetActiveSheet(Workbook, letter);
//Add rows to the active worksheet
RowNumber = 1;
Values = "a,b,a,b";
for (i = 1; i <= 4; i ++) {
SpreadsheetAddRow(Workbook, ListGetAt(Values, i), RowNumber, 1);
if (ListGetAt(Values, i) == "a") {
Format = {bold = true, color = "green"};
SpreadsheetFormatCell(Workbook, Format, RowNumber, 1);
WriteDump(var=format, label="RowNumber="& RowNumber);
}
else {
Format = {bold = true, color = "red"};
SpreadsheetFormatCell(Workbook, Format, RowNumber, 1);
WriteDump(var=format, label="RowNumber="& RowNumber);
}
RowNumber++;
}
// Set the active worksheet back to the original. If you don't
// the last worksheet name will be the name of the spreadsheet
// object, in this case, workbook.
SpreadSheetSetActiveSheetNumber(Workbook, 1);
//Finally, save it to disk
SpreadSheetWrite(Workbook, "c:/path/to/yourFile.xls", true);
</cfscript>
这篇关于电子表格单元格格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
