Set excel cell color with javascript created with cfspreadsheet

[RobertAtGaN]

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

[Charlie Arehart]

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.) 

[EddieLotter]

This StackOverflow post apparently shows how to do it in ColdFusion.

[Charlie Arehart]

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? 

[EddieLotter]

Charlie, I have not had a need to do this, so have not tried it. I leave it as an excercise for the OP. 🙂

[RobertAtGaN]

It does not work for me.  I get an error:

The setFillPattern method was not found.

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

[BKBK]

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

[RobertAtGaN]

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?

[BKBK]

What happens when you use 

• createobject("java", "org.apache.poi.ss.usermodel.BorderStyle").MEDIUM

as argument instead of  XSSFCellStyle.BORDER_MEDIUM or XSSFBorderFormatting.BORDER_MEDIUM?

[RobertAtGaN]

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

[BKBK]

Put it as an argument to setBorderBottom() and setBorderTop().

[RobertAtGaN]

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.

[BKBK]

There was a typing error. I have corrected the code to "createobject".

[RobertAtGaN]

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: 

Value must be initialized before use.

can you give me the entire procedure?

[BKBK]

Arghh! I am beginning to wonder whether ColdFusion has a problem with the POI-XSSF library.  

Let's look into it some more.

[BKBK]

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.