Solved

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

Posted on 2014-04-19
9
714 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 15

Expert Comment

by:myselfrandhawa
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
 

Author Comment

by:DJPr0
ID: 40013258
CF 10
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now