andrél93139241

Inspiring

Question

cfspreadsheet : how to remove a background colours

Forum|Forum|8 years ago
September 26, 2017
1 reply
1281 views

Hello to everybody

I work with ColdFusion 10.

I must create a excel document containing some sheets. I use cfspreadsheet instructions and SpreadsheetFormatRows for modifying the style of my page.

The current problem that I have, if for the sheet one I use one colour for the background of some cells, the system keep the colour.

The next time I fill the cell, the background is equal of previous sheet.

I would like to remove it. I would like a transparent background.

How to do this?

I already search on this forum, on google but ... nothing. May be some one have a solution?

Thank you for your help

Best regards

Andre

This topic has been closed for replies.

Inspiring

You will need to show us your code so we can see what you are doing wrong.

andrél93139241Author

Inspiring

Hello,

Thank you for your quick reaction.

My code:

for info, the field name, title, etc are nick name (not the real).

Thank you for your support

Andre

<cfset FormatRows

= StructNew() >

<cfset FormatRows.verticalalignment = "vertical_center" >

<cfset frg_comment	= StructNew()>
<cfset frg_comment.anchor	= "4,8,6,11">

<cfset cellFormat	= StructNew() >
<cfset cellFormat.fgcolor	= "light_orange" >

<cfset s = SpreadsheetNew("Sheet 1")>

<cfset SpreadsheetSetCellValue(s, "Title", 1, 1) >

<cfset SpreadsheetSetCellValue(s, LSDateFormat(now(), "dd/mm/yyyy"), 1, 2) >

<cfset SpreadsheetSetCellValue(s, "Value of test.", 1, 3) >

<cfset SpreadsheetSetCellValue(s, "All descriptions of what is expected in tables and column.", 1, 4) >

<cfset ListWidthCol = "50,25,50,80" >

<cfset col

= 1 >

<cfloop list="#ListWidthCol#" index="widthcol">

	<cfset SpreadsheetSetColumnWidth(s,col,widthcol)>
	<cfset col = col + 1 >

</cfloop>

<cfset SpreadsheetFormatColumn(s, {textwrap=true}, 4)>

<cfset spreadsheetFormatRow(s, {bold="true"}, 2)>

<cfset spreadsheetFormatRow(s, {bold="true", fgColor="grey_25_percent"}, 8)>

<cfset SpreadsheetFormatCellRange (s, {bottomborder='thin',leftborder='thin',rightborder='thin',topborder='thin', alignment="left"}, 8,1, 9,4 ) >

<cfset SpreadsheetCreateSheet (s, "New sheet")>

<cfset SpreadsheetSetActiveSheet(s, "New sheet")>

<cfset curRow

= 1 >

<cfset headerText = "" >

<cfset headerText = ListAppend(headerText, "Title 1") >

<cfset headerText = ListAppend(headerText, "Title 2") >

<cfset headerText = ListAppend(headerText, "Title 3") >

<cfset headerText = ListAppend(headerText, "Title 4") >

<cfset headerText = ListAppend(headerText, "Title 5") >

<cfset SpreadsheetAddRow(s, headerText)>

<cfset spreadsheetFormatRow(s, {bold="true"}, curRow)>

<cfif ResultQuery.recordcount gt 0 >

	<cfset curRow = curRow + 1>
	<cfloop query="ResultQuery" >

<cfset SpreadsheetSetCellValue(s, "#ResultQuery.Field1#", curRow, 1)>

<cfset SpreadsheetSetCellValue(s, "#ResultQuery.Field2#", curRow, 2)>

<cfset SpreadsheetSetCellValue(s, "#ResultQuery.Field3#", curRow, 3)>

<cfset SpreadsheetSetCellValue(s, "#ResultQuery.Field4#", curRow, 4)>

<cfset SpreadsheetSetCellValue(s, "#ResultQuery.Field5#", curRow, 5)>

<cfif ResultQuery.fg_Error eq 1 >

	<cfset frg_comment.comment = "The length of this text is too long.#chr(10)##chr(13)##chr(10)##chr(13)#current length:" & #ResultQuery.Length#>
	<cfset SpreadsheetSetCellComment(s, frg_comment, curRow, 5)>
	<cfset SpreadsheetFormatcell( s, cellFormat, curRow, 5 ) >

</cfif>

<cfset curRow = curRow + 1>

</cfloop>

</cfif>

<cfset SpreadsheetFormatColumn(s, {textwrap=true}, 4)>

<cfset SpreadsheetFormatColumn(s, {textwrap=true}, 5)>

<cfset SpreadsheetFormatColumn(s, {alignment="center"},1)>

<cfset SpreadsheetFormatColumn(s, {alignment="center"},2)>

<cfset SpreadsheetFormatColumn(s, {alignment="left"},3)>

<cfset SpreadsheetFormatRows(s, cellFormat,"1-" & curRow-1) >

<cfset SpreadsheetFormatCellRange (s, {bottomborder='thin',leftborder='thin',rightborder='thin',topborder='thin'}, 1,1, curRow - 1,5 ) >

<cfset SpreadsheetCreateSheet (s, "Sheet 3")>

<cfset SpreadsheetSetActiveSheet(s, "Sheet 3")>

<cfset curRow

= 1 >

<cfset headerText = "" >

<cfset headerText = ListAppend(headerText, "Field 1") >

<cfset headerText = ListAppend(headerText, "Field 2") >

<cfset headerText = ListAppend(headerText, "Field 3") >

<cfif ResultQuery2.recordcount gt 0 >

<cfset curRow = curRow + 1>

<cfloop query="ResultQuery2" >

<cfset SpreadsheetSetCellValue(s, "#ResultQuery2.Field 1#", curRow, 1)>

<cfset SpreadsheetSetCellValue(s, "#ResultQuery2.Field 2#", curRow, 2)>

<cfset SpreadsheetSetCellValue(s, "#ResultQuery2.Field 3#", curRow, 3)>

<cfset curRow = curRow + 1>

</cfloop>

</cfif>

<cfset SpreadsheetFormatRows(s, formatRows,"1-" & curRow-1) >

<cfset SpreadsheetFormatCellRange (s, {bottomborder='thin',leftborder='thin',rightborder='thin',topborder='thin'}, 1,1, curRow - 1,5 ) >

...