Solved

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

Posted on 2014-04-19
9
776 Views
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.
0
Comment
Question by:DJPr0
[X]
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
9 Comments
 
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

http://www.raymondcamden.com/index.cfm/2011/6/1/ColdFusion-Sample--Create-an-Excel-File

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
0
 

Author Comment

by:DJPr0
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40013103
Yeah, setHeader isn't what you need.  What version of CF are you using?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DJPr0
ID: 40013258
CF 10
0
 
LVL 52

Accepted Solution

by:
_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)>
...
0
 
LVL 52

Assisted Solution

by:_agx_
_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 "& footer.page() & " of " & footer.numPages() )>
...
0
 

Author Comment

by:DJPr0
ID: 40013717
Worked Great! Thanks!

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

Author Closing Comment

by:DJPr0
ID: 40014689
Thanks again _agx_!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40014720
Welcome :)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

626 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