Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-19
9
Medium Priority
?
813 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:DJPr0
ID: 40013258
CF 10
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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 2000 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

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!

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
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.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

886 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