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

Hide gridlines using CFSpreadSheet

Explorer ,
Jan 02, 2023 Jan 02, 2023

Copy link to clipboard

Copied

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?

Views

151

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

correct answers 2 Correct answers

Community Expert , Jan 03, 2023 Jan 03, 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 displ

...

Votes

Translate

Translate
Explorer , Jan 03, 2023 Jan 03, 2023

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.

Votes

Translate

Translate
Community Expert ,
Jan 03, 2023 Jan 03, 2023

Copy link to clipboard

Copied

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.

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

Copy link to clipboard

Copied

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

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

Copy link to clipboard

Copied

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!

 

BKBK_0-1672762799184.png

 

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

Copy link to clipboard

Copied

LATEST

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.

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