SpreadsheetFormatRows格式颜色ColdFusion [英] SpreadsheetFormatRows format color ColdFusion

问题描述

我使用ColdFusion和SpreadsheetNew，SpreadsheetAddRows，SpreadsheetFormatRows等函数创建Excel文件。根据我已阅读的文档

有没有任何方法可以为行颜色输入HEX或RGB值？

最后我检查它不支持核心CF功能。但是，您可以使用支持它的基础POI库。假设您使用较新的.XLSX格式，可以通过创建 CellStyle ，并应用所需的 XSSFColor 。

以下是通过POI设置字体和/或单元格背景颜色的示例（使用CF11测试）。虽然在实际代码中，我建议在可重用函数中包含基本逻辑。

示例：

  //创建具有几个单元
 //并抓取基础POI对象的XLSX工作簿
 cfSheet = Spreadsheetnew（Sheet1，true）; 
 poiWorkbook = cfSheet.getWorkBook（）; 
 poiSheet = poiWorkbook.getSheet（Sheet1）; 
 
 
 //创建可重用的样式对象
 //注意：Excel限制允许的最大样式数。所以不要为每个单元格创建一个新的
 //样式。一次创建不同的样式，并应用于多个单元格/行。 
 Color = createObject（java，java.awt.Color）; 
 
 //样式1：具有背景颜色的单元格（仅限）
 backgroundOnlyStyle = poiWorkbook.createCellStyle（）; 
 backgroundOnlyStyle.setFillPattern（backgroundOnlyStyle.SOLID_FOREGROUND）; 
 XSSFColor = createObject（java，org.apache.poi.xssf.usermodel.XSSFColor）; 
 backgroundOnlyStyle.setFillForegroundColor（XSSFColor.init（Color.decode（## 055910）））; 
 
 //样式2：具有字体颜色的单元格（仅）
 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）; 
 
 //样式3：具有背景和文本颜色的单元格
 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））））; 
 
 //对单元格A1应用样式。注意：POI索引是基于0的
 SpreadSheetSetCellValue（cfSheet，background color only，1,1）; 
 poiSheet.getRow（0）.getCell（0）.setCellStyle（backgroundOnlyStyle）; 
 
 //为单元格A2应用样式A2 
 SpreadSheetSetCellValue（cfSheet，text color only，2，1）; 
 poiSheet.getRow（1）.getCell（0）.setCellStyle（textOnlyStyle）; 
 
 //应用样式到单元格A3 
 SpreadSheetSetCellValue（cfSheet，background AND text color，3，1）; 
 poiSheet.getRow（2）.getCell（0）.setCellStyle（backgroundAndTextStyle）; 
 
 //保存到文件
 SpreadSheetWrite（cfSheet，c：/path/to/yourFile.xlsx，true）; 
  

I'm creating Excel files using ColdFusion and the SpreadsheetNew, SpreadsheetAddRows, SpreadsheetFormatRows, etc. functions. According to the docs that I have read located here their is a propery for color, and fgcolor. I'm a bit confused as to what the difference between the two are. Is one the text color and the other the background color? I've been using fgcolor to set the background color of rows.

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

My main question is, according to the docs I can supply any value in the org.apache.poi.hssf.util.HSSFColor color class as my color. However, I REALLY need to supply either a HEX value or RGB. I know Excel can handle it as you can enter either within excel's colorpicker. Is there ANY way to enter a HEX or RGB value for my row colors?

thank you!

UPDATE

<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)#">

解决方案

I'm a bit confused as to what the difference between the two are.

Understandably. The property names were modeled after the conventions used in POI (underlying java library) which are a bit confusing to begin with IMO. Since ColdFusion only implements a subset of the POI features, the names are taken out of context, making it even more confusing. To answer your question, in POI there are actually three (3) relevant color properties here:

1. Font Color - ie Font.setColor()

   The color of the cell text. In CF, this is controlled by the dataFormat.color property.

2. Cell Pattern Foreground Color - ie CellStyle.setFillForegroundColor

   Despite the name, this is what most people think of as the cell background color (yellow in the image below). In CF this is controlled by the dataFormat.fgColor property.

3. Cell Pattern Background Color - CellStyle.setFillBackgroundColor

   (Optional) Secondary color used in multi-color cell patterns (red in the image below). There is no ColdFusion equivalent.

Is there ANY way to enter a HEX or RGB value for my row colors?

Last I checked it is not supported by the core CF functions. However, you could tap into the underlying POI library which does support it. Assuming you are using the newer .XLSX format, it can be done by creating a CellStyle and applying the desired XSSFColor.

Here is an example (tested with CF11) of how to set the font and/or cell background colors via POI. Though in the real code, I would recommend wrapping up the basic logic in a reusable function.

Example:

// 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);

这篇关于SpreadsheetFormatRows格式颜色ColdFusion的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！