Is there a way to add Excel page numbers in the header/footer using ColdFusion SpreadsheetSetHeader/Footer functions

Question

We have a web app that exports reports to Excel using the Adobe ColdFusion Spreadsheet functions two of which are SpreadsheetSetHeader and SpreadsheetSetFooter. Has anyone figured out how to embed the page number of the excel file using these functions?

In Excel, if you go to Header / Footers you can place &[PAGE] to insert the page number when printed, but I haven't found a way to do this within ColdFusion.

Answer 1

POI is what CF uses beneath the hood for generating spreadsheets. POI has its own formatting codes, but they're similar. Use &P (current page) and &N (total pages) in the footer string.

<cfscript>

  // Create demo spreadsheet
  sheet = SpreadsheetNew("Sheet1", true);
  sheet.setCellValue("Test", 1,1);

  // Add footer with page numbers
  sheet.setFooter("", "&P of &N", "");

  // Download
  cfheader(name="Content-Disposition",value="attachment; filename=Sheet.xlsx"); 
  cfcontent(type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
      , variable="#SpreadsheetReadBinary( sheet )#");
</cfscript>

For more on formatting codes see POI's documentation for XSSHeaderFooter.