tracker issue : CF-4198428

select a category, or use search below
(searches all categories and all time range)

Need formatting parameter for cfspreadsheeet

| View in Tracker

Status/Resolution/Reason: To Fix//Investigate

Reporter/Name(from Bugbase): Barry Friedland / Barry Friedland ()

Created: 03/22/2017

Components: Document Management, Office Integration

Versions: 2016

Failure Type: Others

Found In Build/Fixed In Build: /

Priority/Frequency: Normal /

Locale/System: / Windows 7 64-bit

Vote Count: 0

SpreadsheetFormatCell does not provide a way to color fill a cell background color.  The closest is fillpattern but that is not what I want.  The attached Excel file shows 3 cells all with different background colors for the cells.


  1. March 22, 2017 00:00:00: sample.xlsx


You can use fgcolor for that . Please let me know if this is what you wanted . Eg : <cfscript> myFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "myexcelfile1.xls"; mySheet=SpreadsheetNew("ColdFusion",false); Artists=QueryExecute("Select * from APP.ARTISTS where firstname='Aiden'",[],{datasource="cfartgallery"}); SpreadSheetAddRow(mySheet,"ARTISTID,First Name,Last Name,Address,CITY,STATE,POSTALCODE,EMAIL,PHONE,FAX,THEPASSWORD"); SpreadSheetAddRows(mySheet,Artists); // Define the structure for formatting a cell myFormat=StructNew(); myFormat.color="blue"; myFormat.fgColor="green"; myFormat2=StructNew(); myFormat2.fgColor="orange"; // Set formatting to cell SpreadsheetFormatCell(mySheet,myFormat,2,1); SpreadsheetFormatCell(mySheet,myFormat2,2,2); // Write spreadsheet to file SpreadsheetWrite(mySheet,"#myFile#",true); writeOutput("Created :" & #myFile#); </cfscript>
Comment by Nitin K.
1034 | March 24, 2017 08:57:47 AM GMT
Thank you. FgColor does work. It is a bit misleading as it should be called bgColor because fg refers to foreground and bg refers to background.
Comment by Barry F.
1035 | April 14, 2017 06:05:51 PM GMT
At first sight, the nomenclature seems misplaced but is intended. It becomes apparent, when you use a fill pattern in the cell. Though with 2016.0.04.302561, I don't see the same result as when I use the POI library directly. excel outputs attached. Looks like background color and fillpattern are not working in CF. Setting this to be fixed. -------------------------------- using CFML -------------------------------- myFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "myexcelfile.xlsx"; mySheet=SpreadsheetNew("ColdFusion", true); Artists = QueryExecute("Select * from APP.ARTISTS where firstname='Aiden'",[], {datasource="cfartgallery"} ); SpreadSheetAddRow(mySheet,"ARTISTID,First Name,Last Name,Address,CITY,STATE,POSTALCODE,EMAIL,PHONE,FAX,THEPASSWORD"); SpreadSheetAddRows(mySheet, artists); myFormat.fgColor="red"; myFormat.bgColor="yellow"; myFormat.fillpattern = "BIG_SPOTS"; SpreadsheetFormatCell(mySheet,myFormat,2,1); SpreadsheetWrite(mySheet,"#myFile#",true); writeOutput("Created :" & #myFile#); This outputs a cell with red background. No fill pattern is observed. -------------------------------- using java application -------------------------------- import; import; import; import; import; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class SpreadSheetTest { public static void main(String[] args) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Java Books"); String[][] _data = { {"cell text 11", "cell text 12"}, {"cell text 21", "cell text 22"}, }; int rowCount = 0; for (String[] _row : _data) { Row row = sheet.createRow(++rowCount); int columnCount = 0; XSSFCellStyle _cellstyle = workbook.createCellStyle(); _cellstyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 0, 0))); //RGB - Red foreground _cellstyle.setFillBackgroundColor(new XSSFColor(new java.awt.Color(255, 255, 0))); //RGB - Yellow background _cellstyle.setFillPattern(CellStyle.BIG_SPOTS); for (String field : _row) { Cell cell = row.createCell(++columnCount); cell.setCellStyle(_cellstyle); cell.setCellValue((String) field); } } try (FileOutputStream outputStream = new FileOutputStream("formattedCells.xlsx")) { workbook.write(outputStream); workbook.close(); } } } This outputs a cell with red and yellow dotted pattern (kind of hard to tell which color is the background and which foreground), as one would expect.
Comment by Piyush K.
1036 | August 07, 2017 01:25:16 PM GMT