EasyXLS

How to set header and footer in ColdFusion

<!--
========================================================================
Tutorial 08

This tutorial shows how to create an Excel file in ColdFusion (1) having
multiple sheets (2). The first sheet is filled with data (3)
and the cells are formatted (4) and locked (5).
The column header has comments (6).
The first sheet has header & footer (7).
========================================================================
-->

<!-- Constants Classes -->
<cfobject type="java" class="EasyXLS.Constants.DataType" name="DataType" action="CREATE">
<cfobject type="java" class="EasyXLS.Constants.Border" name="Border" action="CREATE">
<cfobject type="java" class="EasyXLS.Constants.Alignment" name="Alignment" action="CREATE">
<cfobject type="java" class="EasyXLS.Constants.Header" name="Header" action="CREATE">
<cfobject type="java" class="EasyXLS.Constants.Footer" name="Footer" action="CREATE">
<cfobject type="java" class="java.awt.Color" name="Color" action="CREATE">

Tutorial 08<br>
----------<br>

<!-- Create an instance of the class that exports Excel files (1) -->
<cfobject type="java" class="EasyXLS.ExcelDocument" name="workbook" action="CREATE">

<!-- Create two sheets (2) -->
<cfset ret = workbook.easy_addWorksheet("First tab")>
<cfset ret = workbook.easy_addWorksheet("Second tab")>

<!-- Protect first sheet -->
<cfset workbook.easy_getSheetAt(0).setSheetProtected(true)>

<!-- Get the table of data for the first worksheet (3) -->
<cfset xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()>

<!-- Create the formatting style for the header -->
<cfobject type="java" class="EasyXLS.ExcelStyle" name="xlsStyleHeader" action="CREATE">
<cfset xlsStyleHeader.setFont("Verdana")>
<cfset xlsStyleHeader.setFontSize(8)>
<cfset xlsStyleHeader.setItalic(true)>
<cfset xlsStyleHeader.setBold(true)>
<cfset xlsStyleHeader.setForeground(Color.yellow)>
<cfset xlsStyleHeader.setBackground(Color.black)>
<cfset xlsStyleHeader.setBorderColors(Color.gray, Color.gray, Color.gray, Color.gray)>
<cfset xlsStyleHeader.setBorderStyles(Border.BORDER_MEDIUM, Border.BORDER_MEDIUM, 
                                      Border.BORDER_MEDIUM, Border.BORDER_MEDIUM)>
<cfset xlsStyleHeader.setHorizontalAlignment(Alignment.ALIGNMENT_CENTER)>
<cfset xlsStyleHeader.setVerticalAlignment(Alignment.ALIGNMENT_BOTTOM)>
<cfset xlsStyleHeader.setWrap(true)>
<cfset xlsStyleHeader.setDataType(DataType.STRING)>

<!-- Add data in cells for report header -->
<cfloop from="0" to="4" index="column">
    <cfset xlsFirstTable.easy_getCell(0, evaluate(column)).setValue("Column " & evaluate(column + 1))>
    <cfset xlsFirstTable.easy_getCell(0, evaluate(column)).setStyle(xlsStyleHeader)>

    <!-- Add comment for report header cells (6) -->
    <cfset xlsFirstTable.easy_getCell(0, evaluate(column)).setComment(
            "This is column no " & evaluate(column + 1))>
</cfloop>
<cfset ret = xlsFirstTable.easy_getRowAt(0).setHeight(30)>

<!-- Create a formatting style for cells (4) -->
<cfobject type="java" class="EasyXLS.ExcelStyle" name="xlsStyleData" action="CREATE">
<cfset xlsStyleData.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT)>
<cfset xlsStyleData.setForeground(Color.lightGray)>
<cfset xlsStyleData.setWrap(false)>
<!-- Protect cells (5) -->
<cfset xlsStyleData.setLocked(true)>
<cfset xlsStyleData.setDataType(DataType.STRING)>

<!-- Add data in cells for report values -->
<cfloop from="0" to="99" index="row">
    <cfloop from="0" to="4" index="column">
        <cfset xlsFirstTable.easy_getCell(evaluate(row + 1), evaluate(column)).setValue(
                "Data " & evaluate(row + 1) & ", " & evaluate(column + 1))>
        <cfset xlsFirstTable.easy_getCell(evaluate(row + 1), evaluate(column)).setStyle(xlsStyleData)>
    </cfloop>
</cfloop>

<!-- Add header on center section (7) --> 
<cfset xlsFirstTab = workbook.easy_getSheetAt(0)>
<cfset xlsFirstTab.easy_getHeaderAt(Header.POSITION_CENTER).InsertSingleUnderline()>
<cfset xlsFirstTab.easy_getHeaderAt(Header.POSITION_CENTER).InsertFile()>
<cfset xlsFirstTab.easy_getHeaderAt(Header.POSITION_CENTER).InsertValue(
                " - How to create header and footer")>

<!-- Add header on right section -->
<cfset xlsFirstTab.easy_getHeaderAt(Header.POSITION_CENTER).InsertDate()>
<cfset xlsFirstTab.easy_getHeaderAt(Header.POSITION_CENTER).InsertValue(" ")>
<cfset xlsFirstTab.easy_getHeaderAt(Header.POSITION_CENTER).InsertTime()>

<!-- Add footer on center section -->
<cfset xlsFirstTab.easy_getFooterAt(Footer.POSITION_CENTER).InsertPage()>
<cfset xlsFirstTab.easy_getFooterAt(Footer.POSITION_CENTER).InsertValue(" of ")>
<cfset xlsFirstTab.easy_getFooterAt(Footer.POSITION_CENTER).InsertPages()>

<!-- Set column widths -->
<cfset xlsFirstTable.setColumnWidth(0, 70)>
<cfset xlsFirstTable.setColumnWidth(1, 100)>
<cfset xlsFirstTable.setColumnWidth(2, 70)>
<cfset xlsFirstTable.setColumnWidth(3, 100)>
<cfset xlsFirstTable.setColumnWidth(4, 70)>

<!-- Export Excel file -->
Writing file C:\Samples\Tutorial08.xlsx<br>
<cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Tutorial08.xlsx")>

<!-- Confirm export of Excel file -->
<cfset sError = workbook.easy_getError()>
<cfif (sError is "")>
    <cfoutput>
        File successfully created.
    </cfoutput>
<cfelse>
    <cfoutput>
        Error encountered: #sError#
    </cfoutput>
</cfif>

<!-- Dispose memory -->
<cfset workbook.Dispose()>

EasyXLS Excel libraries:

Java
Excel Library for ColdFusion
full Java version to import, export or convert Excel files
Excel Writer for ColdFusion
Java version to create and export Excel files
Download EasyXLS™ Excel Library for ColdFusion

File formats:

MS Excel 97 - 2003
MS Excel 2007 - 2010
MS Excel 2013
MS Excel 2016
MS Excel 2019
XLSX XLSM XLSB XLS
XML HTML CSV TXT