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

问题描述

我正在使用 ColdFusion 和 SpreadsheetNew、SpreadsheetAddRows、SpreadsheetFormatRows 等函数创建 Excel 文件.根据我阅读的文档位于

<块引用>

有没有办法为我的行颜色输入十六进制或 RGB 值?

上次我检查过它不受核心 CF 函数的支持.但是，您可以利用确实支持的底层 POI 库.假设您使用的是较新的 .XLSX 格式，可以通过创建一个 CellStyle 并应用所需的 XSSFColor.

这是一个如何通过 POI 设置字体和/或单元格背景颜色的示例(使用 CF11 测试).虽然在实际代码中，我建议将基本逻辑包装在可重用的函数中.

示例:

//用几个单元格创建 XLSX 工作簿//并抓取底层 POI 对象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, 仅背景颜色", 1, 1);poiSheet.getRow(0).getCell(0).setCellStyle(backgroundOnlyStyle);//将样式应用到单元格 A2SpreadSheetSetCellValue(cfSheet, 仅文本颜色", 2, 1);poiSheet.getRow(1).getCell(0).setCellStyle(textOnlyStyle);//将样式应用到单元格 A3SpreadSheetSetCellValue(cfSheet, 背景和文本颜色", 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屋！