Copy link to clipboard
Copied
I've create a excel file with CFSpreadSheet and now I want to change the color of a cell to a hexcolor that is not available with CFSpreadSheet. Can it be done with javascript?
<cfset xl = spreadsheetNew("Hazard Details",true)>
<cfset SpreadsheetAddRow(xl,"Test")>
<cfset SpreadsheetFormatCell(xl,{color="red",bold="true"},#xl.rowcount#,1)>
Will set it to red. But I want to use a color that is not available with cfspreadsheet like hexcolor #355f91
something like:
<cfset SpreadsheetFormatCell(xl,{color="#355f91",bold="true"},#xl.rowcount#,1)>
Copy link to clipboard
Copied
Robert, two things. First, no, you would NOT be able to control how any cfml tag works via Javascript. As you may know, that's a client-side language, and cfml is entirely a server-side language. Second, I realize you're just very much wanting that hex level of color control, and so you are open to any possibility. I just don't think there is one.
I know you asked about this prospect of using hex colors with cfspreadsheet a couple of weeks ago, and FWIW I didn't answer then as I didn't see that it was possible. I looked into it quite a bit, and tried things, all to no avail. I didn't say anything then as I hoped perhaps someone else might. They did not, I now see. So I am offering this now, lest you think no one is paying any attention. Sorry it's not the solution, but at least it's an answer. Perhaps someone else may chime in with a better one.
(BTW, you've opened this new question rather than adding your new js idea as a comment there. Perhaps you thought this might get better attention, but that's not my experience. Either would cause the thread to appear at the top of the web ui, and either would generate an email to those who choose to get such notifications--like I do.)
Copy link to clipboard
Copied
This StackOverflow post apparently shows how to do it in ColdFusion.
Copy link to clipboard
Copied
So there you go, Robert, on the "someone else may chime in with a better answer". And thanks, Eddie.
Though do read all the answers, Robert. You may need the last one. And it's all a pretty ugly way to achieve the goal, but if you must, it seems it should work.
Eddie, can you confirm that it worked for you? If so, what cf version? And Robert, if somehow it doesn't, what cf version are you running?
Copy link to clipboard
Copied
Charlie, I have not had a need to do this, so have not tried it. I leave it as an excercise for the OP. š
Copy link to clipboard
Copied
It does not work for me. I get an error:
when I used the modified code to change from:
backgroundOnlyStyle.setFillPattern( backgroundOnlyStyle.SOLID_FOREGROUND ); ... backgroundAndTextStyle.setFillPattern( backgroundAndTextStyle.SOLID_FOREGROUND );
to:
backgroundOnlyStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND ); ... backgroundAndTextStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
the entire code I used copied from the link https://stackoverflow.com/questions/37727645/spreadsheetformatrows-format-color-coldfusion
was:
<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">
Copy link to clipboard
Copied
@RobertAtGaN , I modified the stackoverflow code as follows, hopefully for the better.
<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();
/******************* Modified by BKBK **************************/
fillPatternType=backgroundOnlyStyle.getfillPattern();
backgroundOnlyStyle.setFillPattern( fillPatternType.SOLID_FOREGROUND );
/***************************************************************/
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
/******************* Modified by BKBK **************************/
backgroundOnlyStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##055910")).getIndex() );
/***************************************************************/
// 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)#">
Copy link to clipboard
Copied
I appreciate the input, but the origional is more to what I need. I finally got it working using the code:
<cfscript> spreadsheetData = SpreadSheetNew("Sheet1", true); SpreadsheetAddRow(spreadsheetData,"",1,1); wb = spreadsheetData.getWorkbook(); Colors = createObject("java", "org.apache.poi.ss.usermodel.IndexedColors"); greenFont = wb.createFont(); greenFont.setColor(Colors.GREEN.index); blueFont = wb.createFont(); blueFont.setColor(Colors.BLUE.index); // Using GREEN and BLUE for demo purposes richString = createObject("java", "org.apache.poi.xssf.usermodel.XSSFRichTextString").init(); richString.append("Text1: ", greenFont); richString.append("Text2", blueFont); cell = wb.getSheet("Sheet1").getRow( 0 ).getCell( 0 ); cell.setCellValue(richString); </cfscript> <cfheader name="content-disposition" value="attachment; filename=test.xlsx"> <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(spreadsheetData)#" reset="true">
but first I had to do the following:
1. Uninstall the Presentation Module
2. Stop ColdFusion service
3. Remove the poi-3.17.jar from: C:\ColdFusion2021\bundles\repo\
4. Delete the bundle files from: C:\ColdFusion2021\cfusion\bin\felix-cache
5. Restart ColdFusion
But now I have an issue with
Element BORDER_MEDIUM is undefined in XSSFCELLSTYLE.
Using the code:
wb = xl.getWorkbook(); XSSFCellStyle = createObject("java", "org.apache.poi.xssf.usermodel.XSSFCellStyle");
XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle.setBorderTop(XSSFBorderFormatting.BORDER_MEDIUM); cell = wb.getSheet("Custom Report").getRow(row-1).getCell(column-1);
cell.setCellStyle(cellStyle);
Where row and column are the ones I want to change.
Any ideas?
Copy link to clipboard
Copied
What happens when you use
as argument instead of XSSFCellStyle.BORDER_MEDIUM or XSSFBorderFormatting.BORDER_MEDIUM?
Copy link to clipboard
Copied
In my code where would I put the line:
ceateobject("java", "org.apache.poi.ss.usermodel.BorderStyle").MEDIUM
---My code below---
wb = xl.getWorkbook();
XSSFCellStyle = createObject("java", "org.apache.poi.xssf.usermodel.XSSFCellStyle");
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderTop(XSSFBorderFormatting.BORDER_MEDIUM);
cell = wb.getSheet("Custom Report").getRow(row-1).getCell(column-1);
cell.setCellStyle(cellStyle);
Copy link to clipboard
Copied
Put it as an argument to setBorderBottom() and setBorderTop().
Copy link to clipboard
Copied
This is what my code now looks like:
wb = xl.getWorkbook();
XSSFCellStyle = ceateobject("java", "org.apache.poi.ss.usermodel.BorderStyle").MEDIUM
cellStyle.setBorderTop(XSSFCellStyle);
cell = wb.getSheet("Custom Report").getRow(row-1).getCell(column-1);
cell.setCellStyle(cellStyle);
I'm getting an error:
Variable CEATEOBJECT is undefined.
Can you fix my code to what it should be? I'm not understanding how to change it.
Copy link to clipboard
Copied
There was a typing error. I have corrected the code to "createobject".
Copy link to clipboard
Copied
Now my code looks like:
wb = xl.getWorkbook();
XSSFCellStyle = createobject("java", "org.apache.poi.ss.usermodel.BorderStyle").MEDIUM
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderTop(XSSFCellStyle);
cell = wb.getSheet("Custom Report").getRow(row-1).getCell(column-1);
cell.setCellStyle(cellStyle);
and get an error:
can you give me the entire procedure?
Copy link to clipboard
Copied
Arghh! I am beginning to wonder whether ColdFusion has a problem with the POI-XSSF library.
Let's look into it some more.
Copy link to clipboard
Copied
Hi @RobertAtGaN ,
Did you find a workaround?
I have spent some time looking into this, and tried various combinations of BorderStyle. But I am unable to find a solution. I shall give you an example of the kinds of things I did.
The following code tells you that cellStyle has the method setBorderBottom(org.apache.poi.ss.usermodel.BorderStyle).
wb = spreadsheetData.getWorkbook();
cellStyle = wb.createCellStyle();
//writedump(cellStyle);
Furthermore, the following object is of type org.apache.poi.ss.usermodel.BorderStyle
mediumBorderStyle=createobject("java", "org.apache.poi.ss.usermodel.BorderStyle").MEDIUM;
//writedump(mediumBorderStyle);
However, if you run the following code you will get an error.
cellStyle.setBorderBottom(mediumBorderStyle);
The error is "The setBorderBottom method was not found.."
I would suggest that you report a bug. I made the same suggestion in a previous post on ColdFusion's use of the POI library.