Skip to main content
R
RobertAtGaN
Inspiring
January 2, 2023
解決済み

Hide gridlines using CFSpreadSheet

  • January 2, 2023
  • 返信数 1.
  • 462 ビュー

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?

      このトピックへの返信は締め切られました。
      解決に役立った回答 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

      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
      RobertAtGaN作成者
      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!

       

       

      契約条件Accessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices