Solved

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

Posted on 2014-04-19
9
726 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: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
 

Author Comment

by:DJPr0
ID: 40013258
CF 10
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Web Reply Form - PHP with Upload 4 53
rhino JavaScript import, load 25 66
Coldfusion - rename files in a folder 4 8
use lov values 2 18
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

932 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

13 Experts available now in Live!

Get 1:1 Help Now