Inspiring

Question

Format part of a cell with CFSpreadSheet

Forum|Forum|3 years ago
January 9, 2023
3 replies
1327 views

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

This topic has been closed for replies.

R

RobertAtGaNAuthor

Inspiring

Using ColdFusion CFSpreadSheet to format part of a cell.

Charlie Arehart

Community Expert

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.

/Charlie (troubleshooter, carehart. org)

R

RobertAtGaNAuthor

Inspiring

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 of a cell.

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

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.

kglad

Community Expert

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.

Peru Bob

Community Expert

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded