电子表格格式行格式颜色ColdFusion

Question

我正在使用ColdFusion和SpreadsheetNew、SpreadsheetAddRow、SpreadsheetFormatRow等函数创建Excel文件。根据我在这里阅读的文档，它们是颜色和fgColor的属性。我有点困惑两者之间的区别。一个是文本颜色，另一个是背景颜色吗？我一直在使用fgColor来设置行的背景颜色。

// HEADER ROW FORMAT
formatHeaderRow = StructNew();
formatHeaderRow.fgcolor="royal_blue";

我的主要问题是，根据文档，我可以在组织中提供任何值。阿帕奇。波伊。hssf。util。HSSFColor颜色类作为我的颜色。然而，我真的需要提供一个十六进制值或RGB。我知道Excel可以处理它，就像你可以在Excel的颜色选择器中输入一样。有没有办法为我的行颜色输入十六进制或RGB值？

非常感谢。

使现代化

<cfscript>
// create XLSX workbook with a few cells
// and grab underlying POI objects
cfSheet = Spreadsheetnew("Sheet1", true);
poiWorkbook = cfSheet.getWorkBook();
poiSheet = poiWorkbook.getSheet("Sheet1");


// Create reusuable style objects 
// NOTE: Excel limits the maximum number of styles allowed. So do not create a new
// style for every cell. Create distinct styles once, and apply to multiple cells/rows.
Color = createObject("java", "java.awt.Color");

// Style 1: Cell with background color (only)
backgroundOnlyStyle = poiWorkbook.createCellStyle();
backgroundOnlyStyle.setFillPattern( backgroundOnlyStyle.SOLID_FOREGROUND );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundOnlyStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##055910")) );

// Apply styles to cell A1. Note: POI indexes are 0-based
SpreadSheetSetCellValue(cfSheet, "background color only", 1, 1);
poiSheet.getRow( 0 ).setRowStyle( backgroundOnlyStyle );


</cfscript>

<!--- stream it to the browser --->
<cfheader name="Content-Disposition" value="inline; filename=reportName.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#SpreadSheetReadBinary(cfSheet)#">

Answer 1

在周末，我们的组织从ACF11升级到ACF2018，我发现给出的已接受的答案不再有效，因为代码已被弃用，不再具有功能。ACF2018使用ACF11的Apache POI实用程序的更新版本。显然，SOLID_FOREGROUND属性已从CellStyle对象中删除，并移动到FillPatterType对象。我只是想提供@Leigh早在2016年给出的已接受答案的更新。顺便说一句，非常感谢@Leigh提供了一个出色的代码示例，工作了几年。希望这个答案将使某人在更新到较新版本的ACF时免于未来的悲伤。

根据3.17版本的文档，该字段已被删除。

Use FillPatternType.SOLID_FOREGROUND instead.

From source code of apache-poi 3.15 I can see:

/**
 * Fill Pattern: Solidly filled
 * @deprecated 3.15 beta 3. Use {@link FillPatternType#SOLID_FOREGROUND} instead.
 */
@Removal(version="3.17")
static final short SOLID_FOREGROUND = 1; //FillPatternType.SOLID_FOREGROUND;

我修改了上面@Leigh的代码，并添加了以下行

FillPatternType = createObject("java", "org.apache.poi.ss.usermodel.FillPatternType");

然后我修改了以下两行

backgroundOnlyStyle.setFillPattern( backgroundOnlyStyle.SOLID_FOREGROUND );
...
backgroundAndTextStyle.setFillPattern( backgroundAndTextStyle.SOLID_FOREGROUND );

然后把它们改成了

backgroundOnlyStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
...
backgroundAndTextStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );

我创建了@Leigh代码的工作示例和要点，并验证了它在从ACF10到ACF2018的所有版本的ACF中都有效。https://trycf.com/gist/cb4edf103a75b60e0d62259b0f9941ff/acf2018?theme=monokai

<cfscript>
// create XLSX workbook with a few cells
// and grab underlying POI objects
cfSheet = Spreadsheetnew("Sheet1", true);
poiWorkbook = cfSheet.getWorkBook();
poiSheet = poiWorkbook.getSheet("Sheet1");


// Create reusuable style objects 
// NOTE: Excel limits the maximum number of styles allowed. So do not create a new
// style for every cell. Create distinct styles once, and apply to multiple cells/rows.
Color = createObject("java", "java.awt.Color");
FillPatternType = createObject("java", "org.apache.poi.ss.usermodel.FillPatternType");

// Style 1: Cell with background color (only)
backgroundOnlyStyle = poiWorkbook.createCellStyle();
backgroundOnlyStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundOnlyStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##055910")) );

// Style 2: Cell with font color (only)
textOnlyStyle = poiWorkbook.createCellStyle();
textFont = poiWorkbook.createFont();
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
textFont.setColor( XSSFColor.init(Color.decode("##bd13be")) );
textOnlyStyle.setFont( textFont );

// Style 3: Cell with both backgound and Text color
backgroundAndTextStyle = poiWorkbook.createCellStyle();
textFont = poiWorkbook.createFont();
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
textFont.setColor( XSSFColor.init(Color.decode("##a20932")) );
backgroundAndTextStyle.setFont( textFont );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundAndTextStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
backgroundAndTextStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##192fda")) );

// Apply styles to cell A1. Note: POI indexes are 0-based
SpreadSheetSetCellValue(cfSheet, "background color only", 1, 1);
poiSheet.getRow( 0 ).getCell( 0 ).setCellStyle( backgroundOnlyStyle );

// Apply styles to cell A2
SpreadSheetSetCellValue(cfSheet, "text color only", 2, 1);
poiSheet.getRow( 1 ).getCell( 0 ).setCellStyle( textOnlyStyle );

// Apply styles to cell A3
SpreadSheetSetCellValue(cfSheet, "background AND text color", 3, 1);
poiSheet.getRow( 2 ).getCell( 0 ).setCellStyle( backgroundAndTextStyle );

</cfscript>

<!--- Now that spreadsheet is prepared, initiate download --->  
<cfheader name="Content-Disposition" value="attachment;filename=yourfile.xlsx">
<cfcontent variable="#spreadsheetReadBinary(cfSheet)#" type="application/vnd.ms-excel">

Answer 2

我有点困惑这两者之间有什么区别。

可以理解地属性名称是按照POI（底层java库）中使用的约定建模的，从IMO开始就有点混乱。由于ColdFusion只实现POI功能的一个子集，因此这些名称脱离了上下文，使其更加混乱。为了回答您的问题，POI中实际上有三（3）个相关的颜色属性：

>

字体颜色-ieFont.setColor（）

单元格文本的颜色。在CF中，这由数据格式控制。颜色属性。

单元格模式前景色-即CellStyle。setFillForegroundColor

尽管有名字，但这是大多数人认为的细胞背景色（下图中的黄色）。在CF中，这由数据格式控制。fgColor属性。

单元格模式背景色-CellStyle。setFillBackgroundColor

（可选）多色单元格图案中使用的次要颜色（下图中为红色）。没有等效的ColdFusion。

是否有任何方法可以为我的行颜色输入HEX或RGB值？

上次我检查了核心CF函数不支持它。但是，您可以访问支持它的底层POI库。假设你使用的是更新的。XLSX格式，可以通过创建CellStyle并应用所需的XSSFColor来实现。

下面是一个如何通过POI设置字体和/或单元格背景颜色的例子（用CF11测试）。尽管在真正的代码中，我建议将基本逻辑包装在一个可重用的函数中。

例：

// create XLSX workbook with a few cells
// and grab underlying POI objects
cfSheet = Spreadsheetnew("Sheet1", true);
poiWorkbook = cfSheet.getWorkBook();
poiSheet = poiWorkbook.getSheet("Sheet1");


// Create reusuable style objects 
// NOTE: Excel limits the maximum number of styles allowed. So do not create a new
// style for every cell. Create distinct styles once, and apply to multiple cells/rows.
Color = createObject("java", "java.awt.Color");

// Style 1: Cell with background color (only)
backgroundOnlyStyle = poiWorkbook.createCellStyle();
backgroundOnlyStyle.setFillPattern( backgroundOnlyStyle.SOLID_FOREGROUND );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundOnlyStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##055910")) );

// Style 2: Cell with font color (only)
textOnlyStyle = poiWorkbook.createCellStyle();
textFont = poiWorkbook.createFont();
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
textFont.setColor( XSSFColor.init(Color.decode("##bd13be")) );
textOnlyStyle.setFont( textFont );

// Style 3: Cell with both backgound and Text color
backgroundAndTextStyle = poiWorkbook.createCellStyle();
textFont = poiWorkbook.createFont();
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
textFont.setColor( XSSFColor.init(Color.decode("##a20932")) );
backgroundAndTextStyle.setFont( textFont );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundAndTextStyle.setFillPattern( backgroundAndTextStyle.SOLID_FOREGROUND );
backgroundAndTextStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##192fda")) );

// Apply styles to cell A1. Note: POI indexes are 0-based
SpreadSheetSetCellValue(cfSheet, "background color only", 1, 1);
poiSheet.getRow( 0 ).getCell( 0 ).setCellStyle( backgroundOnlyStyle );

// Apply styles to cell A2
SpreadSheetSetCellValue(cfSheet, "text color only", 2, 1);
poiSheet.getRow( 1 ).getCell( 0 ).setCellStyle( textOnlyStyle );

// Apply styles to cell A3
SpreadSheetSetCellValue(cfSheet, "background AND text color", 3, 1);
poiSheet.getRow( 2 ).getCell( 0 ).setCellStyle( backgroundAndTextStyle );

// Save to file
SpreadSheetWrite(cfSheet, "c:/path/to/yourFile.xlsx", true);