• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Set excel cell color with javascript created with cfspreadsheet

Explorer ,
Jan 26, 2023 Jan 26, 2023

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

Views

617

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 26, 2023 Jan 26, 2023

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

/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 26, 2023 Jan 26, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 26, 2023 Jan 26, 2023

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? 

/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 26, 2023 Jan 26, 2023

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 26, 2023 Jan 26, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 19, 2023 Feb 19, 2023

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 28, 2023 Feb 28, 2023

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?

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 06, 2023 Mar 06, 2023

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 06, 2023 Mar 06, 2023

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 07, 2023 Mar 07, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 07, 2023 Mar 07, 2023

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 07, 2023 Mar 07, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 07, 2023 Mar 07, 2023

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: 

Value must be initialized before use.

can you give me the entire procedure?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 08, 2023 Mar 08, 2023

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 20, 2023 Mar 20, 2023

Copy link to clipboard

Copied

LATEST

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.

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation