Skip to main content
R
RobertAtGaN
Inspiring
January 2, 2023
Answered

Hide gridlines using CFSpreadSheet

  • January 2, 2023
  • 1 reply
  • 459 views

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?

      This topic has been closed for replies.
      Correct answer RobertAtGaN

      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.

      1 reply

      BKBK
      Community Expert
      BKBKCommunity Expert
      Community Expert
      January 3, 2023

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

       

      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.

      R
      RobertAtGaNAuthor
      Inspiring
      January 3, 2023

      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
      Community Expert
      BKBKCommunity Expert
      Community Expert
      January 3, 2023
      quote

      That only greys them out.  


      By @RobertAtGaN

      My guess is that you omitted the foreground color.

       

      quote

      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!

       

       

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices