CF Spreadsheet Header Row printing on every page

Update: as I noted in a later comment, I was incorrect about my assertions regarding the poi library, so I'm striking out the first two paragraphs of my original reply here.

You mention poi, but you seem to be using the built-in functions, which as far as I know have nothing to do with poi. That was a separate library used more before cf added its own spreadsheet functionality.

So before attacking your problem, are you modifying your cf setup to point to poi libraries? If you take them out (even in a local dev environment, if you "can't touch production"), does you code still work at all? And does your problem remain? It's important to know both.

Then back to your problem, have you created a very simple example, in just a few lines--using static data--that you could share and which demonstrates the problem? We may better see something, or be able to try something in our own with/for you (without our need to create a demo of our own from scratch). 

I appreciate that you likely hoped someone might just see the problem and offer the solution. Maybe they will. I can't, and I suspect others helping here may not work with the spreadsheet features regularly enough to propose it readily. So until then, I offer the above to help you move the ball down the field. 

I can put something together.....Im very new to the whole spreadsheet CF stuff...I could do pretty much anything in XML, although I am starting to become more of a fan of the built in functions and the output. I say POI, solely because I thought that's what I was using. Seems I still have alot to learn on this front. Let me throw something together and repost.

Here is my simplified code. Ive removed all formatting and things like that, and simply went for the data. I have a commented out line called //SpreadSheetSetPrintHeader(mySheet,0,1); but that doesnt work.

<cfquery name="Input" datasource="dc">
SELECT
SS.PROJ_NO   
FROM MCA.current_bulk_filter SS
ORDER BY proj_no
</CFQUERY>

<cfscript>
// Set the file path in the same location as this cfm
 myFile="f:\websites\mca\mca\Reports\testspreadsheet.xlsx";   
   
// create first spreadsheet object. Set xml to true as this is an xlsx file
 mySheet=SpreadsheetNew("All",true);      
   
// Set headers for the xlsx file
 SpreadSheetAddRow(mySheet,"Col1,Col2,Col3,Col4,Col5,Col6",1);
   
for(i=1; i LTE Input.RecordCount; i=i+1){      
SpreadSheetAddRow(mySheet,"'#Input.PROJ_NO[i]#'",2,1);}
      
//-------------------------------------------------------------------------------------------------      
//-------------------------------------------------------------------------------------------------      
//-------------------------------------------------------------------------------------------------      
   
SpreadSheetAddFreezePane(mySheet,0,1);   
SpreadSheetSetHeader(mySheet,"","All","");
SpreadSheetSetFooter(mySheet,"","&P of &N","");
//SpreadSheetSetPrintHeader(mySheet,0,1);

Sheet = mySheet.getWorkBook().getSheet("All");
Sheet.setMargin(Sheet.LeftMargin, 0.25);
Sheet.setMargin(Sheet.RightMargin, 0.25);

ps = Sheet.getPrintSetup();
ps.setPaperSize(17);
ps.setScale(67);
ps.setLandscape(true);
       
SpreadsheetWrite(mySheet,"#myFile#",true);
</cfscript>

Select allOpen in new window

I don't have time now to winnow that down to the stand-alone variant I was suggesting you offer. I only had a moment between calls to see and consider this note of yours.

Could you please consider doing that yourself, for your sake and ours? I mean like no more than several lines, that have NO data of your own (that we won't have) and which demonstrates what you want that you don't get, such that WE can run it ourselves, without any need to edit it?

Again, perhaps someone else seeing what you've shared will chime in that they already see what is amiss and how to fix it. I'm saying that short of that, you can help us help you by doing that.

Oh ok....let me see what I can do. I need to make sure there are enough rows created in the spreadsheet so that it will force a page two of the spreadsheet to try and carry the row one (header) row over to page two, which is why I just selected any table that I knew had enough records to force a second page.

Sure, but we don't have that data, of course.

As for how to create enough lines, there are many ways: again you could just manually create them line by line or via looping (whether over an array or just creating data/lines in an index loop), or you could use the sample datasources cf offers, or you could use cf's feature to build a query resultset in memory (without using a database at all), and so on.

Just pick something that is sufficient to demonstrate your problem, even if it may not be elegant or production-suitable. :-) Again I am writing between engagements, and on my phone, which is why I don't just code it up for you myself. 

So this creates the spreadsheet with no requirement to access a database. It just creates thirty rows and forces a page two.

<cfscript>
// Set the file path in the same location as this cfm
 myFile="f:\websites\mca\mca\Reports\testspreadsheet.xlsx";   
   
// create first spreadsheet object. Set xml to true as this is an xlsx file
 mySheet=SpreadsheetNew("All",true);      
   
// Set headers for the xlsx file
SpreadSheetAddRow(mySheet,"Col1,Col2,Col3,Col4,Col5,Col6",1);
   
myFormat=StructNew();
 myFormat.color="blue";
 myFormat.bold="true";
 myFormat.underline="true";
 myFormat.alignment="center";
 myFormat.font="Arial";
   
SpreadSheetAddFreezePane(mySheet,0,1);   


SpreadSheetAddRow(mySheet,'a',2,1);
SpreadSheetAddRow(mySheet,'a',3,1);
SpreadSheetAddRow(mySheet,'a',4,1);
SpreadSheetAddRow(mySheet,'a',5,1);
SpreadSheetAddRow(mySheet,'a',6,1);
SpreadSheetAddRow(mySheet,'a',7,1);
SpreadSheetAddRow(mySheet,'a',8,1);
SpreadSheetAddRow(mySheet,'a',9,1);
SpreadSheetAddRow(mySheet,'a',10,1);
SpreadSheetAddRow(mySheet,'a',11,1);
SpreadSheetAddRow(mySheet,'a',12,1);
SpreadSheetAddRow(mySheet,'a',13,1);
SpreadSheetAddRow(mySheet,'a',14,1);
SpreadSheetAddRow(mySheet,'a',15,1);
SpreadSheetAddRow(mySheet,'a',16,1);
SpreadSheetAddRow(mySheet,'a',17,1);
SpreadSheetAddRow(mySheet,'a',18,1);
SpreadSheetAddRow(mySheet,'a',19,1);
SpreadSheetAddRow(mySheet,'a',20,1);
SpreadSheetAddRow(mySheet,'a',21,1);
SpreadSheetAddRow(mySheet,'a',22,1);
SpreadSheetAddRow(mySheet,'a',23,1);
SpreadSheetAddRow(mySheet,'a',24,1);
SpreadSheetAddRow(mySheet,'a',25,1);
SpreadSheetAddRow(mySheet,'a',26,1);
SpreadSheetAddRow(mySheet,'a',27,1);
SpreadSheetAddRow(mySheet,'a',28,1);
SpreadSheetAddRow(mySheet,'a',29,1);
SpreadSheetAddRow(mySheet,'a',30,1);
SpreadSheetAddRow(mySheet,'a',31,1);
SpreadSheetAddRow(mySheet,'a',32,1);
SpreadSheetAddRow(mySheet,'a',33,1);
SpreadSheetAddRow(mySheet,'a',34,1);
SpreadSheetAddRow(mySheet,'a',35,1);
SpreadSheetAddRow(mySheet,'a',36,1);
SpreadSheetAddRow(mySheet,'a',37,1);
SpreadSheetAddRow(mySheet,'a',38,1);
SpreadSheetAddRow(mySheet,'a',39,1);
   
//-------------------------------------------------------------------------------------------------      
//-------------------------------------------------------------------------------------------------      
//-------------------------------------------------------------------------------------------------      
   
SpreadSheetSetHeader(mySheet,"","All","");
SpreadSheetSetFooter(mySheet,"","&P of &N","");
//SpreadSheetSetPrintHeader(mySheet,0,1);
//SpreadSheetSetPrintArea(mySheet,"1:1",0,1);


Sheet = mySheet.getWorkBook().getSheet("All");
Sheet.setMargin(Sheet.LeftMargin, 0.25);
Sheet.setMargin(Sheet.RightMargin, 0.25);


ps = Sheet.getPrintSetup();
ps.setLandscape(true);
SpreadsheetWrite(mySheet,"#myFile#",true);
</cfscript>

Select allOpen in new window

That was it!!! Thank you so much!! I appreciate your help and guidance. I need to dig into the POI thing more as it seems like that may bring some other useful things to me, but this was the last piece I needed to move on.

Glad to have helped, and thanks for both those kind regards and the additional testimonial you'd offered.

And yep, as you may find other things you wish to do that are not seemingly provided for in the cfml functions, the pattern of using poi objects as I showed does indeed open up many possibilities. And you will find ample docs from the poi project and discussions online where people may show Java code for doing something (like the headers), which may be rather simple to convert to cfml, like the above. (And cf2021 even supports using straight Java in cfml, which has pros and cons.)

Hope your path going forward is now far more productive. :-)