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

Format part of a cell with CFSpreadSheet

Explorer ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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:YesDate:31-Aug-2022                

Views

432

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 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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.

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 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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>

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 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

Using ColdFusion CFSpreadSheet to format part of a cell.

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 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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)

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 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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

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 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

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


/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
Explorer ,
Jan 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

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.

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 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

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

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 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

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

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 12, 2023 Jan 12, 2023

Copy link to clipboard

Copied

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!

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 15, 2023 Jan 15, 2023

Copy link to clipboard

Copied

LATEST

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.

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