ColdFusion How can I make the header print out on every page for my Excel output?

Posted on 2014-04-19
Last Modified: 2014-04-22
I have a ColdFusion search and results page with an Excel download function.

How can I format the Excel download feature:
1. If the user decides to print the Excel document - print header row on each page.

2. Print page numbers.
Question by:DJPr0
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40010708
you decide your own header or the header which is shown when the windows show a print button.

Because if that is the case with the windows functionality, you have to use ActiveX Object which will only work in IE.

If the case is the CF way, you can use spreadsheet functionality

Follow this tute by ray

you can use this function to set the headers for the spreadsheet

<cfset spreadsheetSetHeader(SpreadsheetObj,"left header","center header","right header")>

for multiple sheets in a single excel, you have loop over the recordset and create the obj so that it should create the header and display it.

Try it and if your encounter issues, post the code here

Author Comment

ID: 40010749
The function:
<cfset spreadsheetSetHeader(SpreadsheetObj,"left header","center header","right header")>

Works as far as printing a header on every page for an Excel print out.

The problem is - the header I want to print is the header columns:
<cfset SpreadsheetAddRow(sObj, "FNum,ISP,QTY,QTY R,QTY SS,UI, Description,SN,Q,JD,SER,Source ,Vendor, Date,Remarks")>

The spreadsheetheader command doesn't display columns, just text.
LVL 52

Expert Comment

ID: 40013103
Yeah, setHeader isn't what you need.  What version of CF are you using?
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.


Author Comment

ID: 40013258
CF 10
LVL 52

Accepted Solution

_agx_ earned 500 total points
ID: 40013396
The feature you need is called Sheet.setRepeatingRows() . In newer versions, its the preferred way of repeating rows/columns when printing. But CF10's version of POI is very old, and doesn't support it.  Instead you'll have to use Workbook.setRepeatingRowsAndColumns(). Important: Index values are all 0 based.

setRepeatingRowsAndColumns(int sheetIndex,
                              int startColumn,
                              int endColumn,
                              int startRow,
                              int endRow)

<cfset yourSheet = SpreadSheetNew()>
.... add headers and data
<!--- get internal workbook --->
<cfset theWorkbook = yourSheet.getWorkbook()>

<!--  Translation
      0 - first sheet
     , 0 - Start Column => A
     , 7 - End Column => h
     , 0 - Start Row => Row 1
     , 0 - End Row => Row 1
<cfset theWorkbook.setRepeatingRowsAndColumns(0, 0, 7, 0, 0)>
LVL 52

Assisted Solution

_agx_ earned 500 total points
ID: 40013452
2. Print page numbers.

AFAIK, CF doesn't support it, but you can adapt this example from POI's docs:

<cfset yourSheet = SpreadSheetNew()>
<!--- substitute your sheet name for "Sheet1" --->
<cfset internalSheet = yourSheet.getWorkBook().getSheet("Sheet1")>
<cfset footer = internalSheet.getFooter()>
<cfset footer.setCenter( "Page "& & " of " & footer.numPages() )>

Author Comment

ID: 40013717
Worked Great! Thanks!

I did find the function: setRepeatingRowsAndColumns on the web, but I had a problem putting all together.

Author Closing Comment

ID: 40014689
Thanks again _agx_!
LVL 52

Expert Comment

ID: 40014720
Welcome :)

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP.NET data base connection 35 95
WordPress Page Content Lost 2 50
Attendance Tracking 3 24
type of website 13 53
Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
The viewer will learn how to count occurrences of each item in an array.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question