Format part of a cell with CFSpreadSheet

[RobertAtGaN]

I would like to be able to format part of a cell so that the output looks like:

Updated for SSEWG: Yes Date: 8/31/2022

instead of having to put each section into a different cell like:

Updated for SSEWG:

Yes

Date:

31-Aug-2022

[Peru Bob]

This is the Using the Community forum (which is the forum for issues using the forums).
Please tell us what Adobe application you are using so that this can be moved to the proper forum for help.

[kglad]

in the future, to find the best place to post your message, use the list here, https://community.adobe.com/

p.s. i don't think the adobe website, and forums in particular, are easy to navigate, so don't spend a lot of time searching that forum list. do your best and we'll move the post if it helps you get responses.

<moved from using the community>

[RobertAtGaN]

Using ColdFusion CFSpreadSheet to format part of a cell.

[Charlie Arehart]

Robert, you can help us help you by showing what sort of code first puts those values into those cells. 

For instance, if it's using SpreadSheetAddRows(), which puts query results into a sheet, you could control a single column being comprised of text (formatted) and data by way of the sql SELECT statement.

Or if you have no such control over how the sheet is populated, note that there's a function to merge cells, spreadsheet merge cells.

Or perhaps in seeing your code, we may have still other suggestions.

[RobertAtGaN]

The problem is that I want to put all the values into one cell and have part of it bold and part not bold.  Currently the only way I found to do this, is to put each field into a different cell and format each cell separatly.  If you merge the cells, you lose the data from the other cells:

<cfset SpreadsheetAddRow(xl,"Log Number:")>
<cfset SpreadsheetFormatCell(xl,{font="Verdana",bold="true",fontsize="8"},#xl.rowcount#,1)>
<cfset SpreadsheetSetCellValue(xl,"#Details.LogNumber#",#xl.rowcount#,2,"string")>
<cfset SpreadsheetFormatCell(xl,{font="Verdana",fontsize="8"},#xl.rowcount#,2)>
<cfset SpreadsheetSetCellValue(xl,"Status:",#xl.rowcount#,3)>
<cfset SpreadsheetFormatCell(xl,{font="Verdana",bold="true",fontsize="8"},#xl.rowcount#,3)>

produces the output:

Log Number:

2022-008-0001

Status:

Active

I want to put the values into one cell like:

Log Number: 2022-008-0001

Status:  Active

[Charlie Arehart]

OK. So it turns out this is not only not supported by CF's spreadsheet functions, but it's also been a bane/challenge for Java developers (who may not know how to even spell CF) when using the underlying java library that CF uses to do spreadsheet processing, called POI.

And you can find discussions from them about the problem, such as here and here. And  some good news is that we in CFML can call upon Java objects as are shown there. Someone may get value in digging through those (and others like them) to find different possible solutions to your challenge.

But I can share still better news: someone did that. See this post for a discussion of how to use such java within CFML to use POI method calls to modify the  format in part....

I'm not saying it's "simple". Again, the problem is in the underlying Java library. But this should give you enough info to dig in and solve this. Let us know how it goes, or if you have follow-on questions.

(BTW, back to my first reply, I was hoping that perhaps using HTML formatting could solve the problem (in which case the SELECT statement control of things may have been a way to go. I didn't have a chance to test it before I replied. I have since confirmed that HTML formatting is simply shown as plain text if put into cells using CFML spreadsheet functions.)

[RobertAtGaN]

They show to use:

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

I don’t know how to put this code into coldfusion.

[EddieLotter]

That is ColdFusion code, however, be sure to put it in a cfscript block.

[RobertAtGaN]

I must be missing something.  Here is the code I'm using so far:

<cfset xl = spreadsheetNew("Hazard Details",true)>

<cfset SpreadsheetAddRow(xl,"")>

<cfscript>

wb = xl.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( xl.rowcount ).getCell( 1 );

cell.setCellValue(richString);

</cfscript>

I get an erorr:

The append method was not found.

And what would be the statement to change to BOLD instead of changing the color?

blueFont.setColor(Colors.BLUE.index); 

to

boldFont.setBold = True (is this correct?)

[RobertAtGaN]

I've tired numerous thing but when I try:

richString = createObject("java", "org.apache.poi.xssf.usermodel.XSSFRichTextString").init();

richString.append("Text1: ", greenFont);

richString.append("Text2", blueFont);

I get an error:

The append method was not found.

Am I missing something? Please help!

[BKBK]

I think you should report a bug. The documentation for XSSFRichTextString says that the append method accepts a String object and an XSSFFont object as arguments.