Hide gridlines using CFSpreadSheet

[RobertAtGaN]

Is there a way to programmatically turn off grid lines like you can do from within an excel file by selecting “View” and uncheck gridlines?

[BKBK]

Great question. Someone once asked as far back as March 2011 if there is a way to remove the gridlines when creating....

I shall suggest one way to do it. Namely, by using the format attribute to set the borders of each cell to "none". You must therefore specify the cells whose borders are to be removed. The following example illustrates how.

<!--- The cfdocexamples ships with ColdFusion. So you can use it directly. --->

<!--- Get the spreadsheet data as a query. ---> 
<cfquery 
       name="courses" datasource="cfdocexamples" 
       cachedwithin="#createTimeSpan(0,1,0,0)#"> 
       SELECT CORNUMBER,DEPT_ID,CORLEVEL,COURSE_ID,CORNAME,CORDESC,LASTUPDATE 
       FROM COURSELIST 
</cfquery> 
 
<cfscript> 
    //Absolute path: the sheet will be saved in the current directory
    theFile=expandpath('courses.xls'); 
    //Create a new Excel spreadsheet object and add the query data. 
    theSheet = SpreadsheetNew("CourseData"); 
    SpreadsheetAddRows(theSheet,courses); 
    
    // Define a format. Each cell will have this same format. 
    myFormat=StructNew() ;
    myFormat.fgcolor="pale_blue"; 
    //'None' means no gridline
    myFormat.topborder="none"; 
    myFormat.bottomborder="none"; 
    myFormat.leftborder="none"; 
    myFormat.rightborder="none"; 
    
    /* The cell coordinates range from (1,1), (2,1),..., (rc, rc), where rc is the query's recordcount'*/
    for (i=1;i<=courses.recordcount;i++) {
    	for (j=1;j<=courses.recordcount;j++) {
    		 SpreadsheetFormatCell(theSheet,myFormat,i,j); 
   	    }
    }
   
</cfscript> 
 
<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheet=1 sheetname="courses" overwrite=true>
    
 Done!

The result is an Excel sheet, saved in the current directory. You will see that it contains no gridlines in the area where the query-data is displayed.

[RobertAtGaN]

That only greys them out.  To actually remove them, I found the answer by doing:

<cfset xl = spreadsheetNew("New Spreadsheet",true)>

<cfset mysheet = xl.getWorkBook().getSheetAt(javacast("int",0))>
<cfset mysheet.setDisplayGridlines(false)>

[BKBK · Accepted solution]

That only greys them out.  

By @RobertAtGaN

My guess is that you omitted the foreground color.

To actually remove them, I found the answer by doing:

<cfset xl = spreadsheetNew("New Spreadsheet",true)>

<cfset mysheet = xl.getWorkBook().getSheetAt(javacast("int",0))>
<cfset mysheet.setDisplayGridlines(false)>

By @RobertAtGaN

I think that that will remove all the gridlines. In the following example, with white foreground colour, there are no gridlines in the cells where the query-data is displayed:

<!--- The cfdocexamples ships with ColdFusion. So you can use it directly. --->

<!--- Get the spreadsheet data as a query. ---> 
<cfquery 
       name="courses" datasource="cfdocexamples" 
       cachedwithin="#createTimeSpan(0,1,0,0)#"> 
       SELECT CORNUMBER,DEPT_ID,CORLEVEL,COURSE_ID,CORNAME,CORDESC,LASTUPDATE 
       FROM COURSELIST 
</cfquery> 
 
<cfscript> 
    //Absolute path: the sheet will be saved in the current directory
    theFile=expandpath('courses.xlsx'); 
    //Create a new Excel spreadsheet object and add the query data. 
    theSheet = SpreadsheetNew("CourseData",true); 
    SpreadsheetAddRows(theSheet,courses); 
    
    // Define a format. Each cell will have this same format. 
    myFormat=StructNew() ;
    myFormat.fgcolor="white"; 
    //'none' means no gridline
    myFormat.topborder="none"; 
    myFormat.bottomborder="none"; 
    myFormat.leftborder="none"; 
    myFormat.rightborder="none"; 

    /* The cell coordinates range from (1,1), (2,1),..., (rc, rc), where rc is the query's recordcount'*/
    for (i=1;i<=courses.recordcount;i++) {
    	for (j=1;j<=courses.recordcount;j++) {
    		 SpreadsheetFormatCell(theSheet,myFormat,i,j); 
   	    }
    }
   
</cfscript> 
 
<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheet=1 sheetname="courses" overwrite=true>
    
 Done!

[RobertAtGaN · Accepted solution]

Your correct.  I left out the 

fgcolor="white"

However, instead of having to do this on each cell, you can do it on the entire spreadsheet all at once by doing:

<cfset xl = spreadsheetNew("New Spreadsheet",true)>

<cfset mysheet = xl.getWorkBook().getSheetAt(javacast("int",0))>
<cfset mysheet.setDisplayGridlines(false)>

Thanks for the help if I need to only do it on one cell in particular.