cfspreadsheet : how to remove a background colours

[andrél93139241]

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

[haxtbh]

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

[andrél93139241]

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

<!--- Creation of the first sheet --->

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

<!--- Formatting... --->

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

<!--- Create and fill the page "New sheet" --->

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

<!--- Create and fill the page "Sheet 3" --->

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

<!--- The complete page come orange (value coming from the previous sheet) --->

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

... <!--- Rest of the code --->

[andrél93139241]

Hello,

Question: there is a limit of rows (in ColdFusion) to create an Excel document?

I ask that question, because, I have removed all codes of the page, reinstall bloc the code by bloc the code and tested.

Everything work fine till the row number 530. There start the problems with layout.

Colours come anywhere, some cells (from others sheets) come in bold (before, all cells are normal - not bold).

I loose the centring or the wrap text.

It reminds me of a limit of use... What do you thing?

Thank you

Best regards

Andre